Reading excel tables with Java

Poi technology is generally used to read excel tables, but what is this technology?

What is Apache POI?

Apache POI is a popular API that allows programmers to create, modify and display MS Office files using Java programs. This open source library developed by the Apache Software Foundation uses Java to distribute the design or modification of Microsoft Office files. It contains classes and methods to decode user input data or files into MS Office documents.

Apache POI

Apache POI is a 100% open source library provided by the Apache Software Foundation. Most small and medium-sized application development mainly relies on Apache POI (HSSF + XSSF). It supports all basic functions of the Excel library; however, rendering and text extraction are its main features.

Java reads excel

Workbook

This is the super interface for all classes that create or maintain Excel workbooks. It belongs to the org.apache.poi.ss.usermodel package. There are two classes that implement this interface, as follows:

HSSFWorkbook: This class has methods for reading and writing Microsoft Excel files in .xls format. It is compatible with Microsoft Office97-2003 version.
XSSFWorkbook: This class has methods for reading and writing Microsoft Excel and OpenOffice XML files in the format .xls or .xlsx. It is compatible with MS-Office version 2007 or higher.

HSSFWorkbook

It is a high-level class in the org.apache.poi.hssf.usermodel package. It implements the Workbook interface for .xls format in Excel files. Listed below are some methods and constructors under this class.

Constructor of class

234##5##6HSSFWorkbook(POIFSFileSystem fs)7HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes) Commonly used parameters within these constructs:

S.No.	Constructor and description
1	##HSSFWorkbook() When creating a new HSSFWorkbook object from scratch.
	HSSFWorkbook(DirectoryNode directory, boolean preserveNodes) Create a specific directory Create a new HSSFWworkbook object.
	HSSFWorkbook(DirectoryNode directory, POIFSFileSystem fs, boolean preserveNodes) Given A POIFSFileSystem object and a specific directory, it creates an SSFWorkbook object to read the specified workbook.
	HSSFWorkbook(java.io.InputStream s) Create an input stream using when creating a new HSSFWorkbook object.
	HSSFWorkbook(java.io.InputStream s, boolean preserveNodes) Build The POI file system of the input stream.
	A new HSSFWorkbook object constructed using the POIFSFileSystem object hour.
	Given a POIFSFileSystem object When reading the specified workbook, it creates a new HSSFWorkbook object.

directory : This is the directory processed from the POI file system.

fs: It is the file system that contains the POI of the book stream.
preservenodes : This is an optional parameter that determines whether to preserve other nodes like macros. It consumes a lot of memory because it stores all POIFileSystem in memory (if set).
Note: The HSSFWorkbook class contains a number of methods; however, they are only compatible with the XLS format. In this tutorial, the focus is on the latest version of the Excel file format. Therefore, the methods of the HSSFWorkbook class are not listed here. If you need the methods of these classes, please refer to the POI-HSSFWorkbook class API at https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFWorkbook.html.

XSSFWorkbook

It is a class used to represent high- and low-level Excel file formats. It belongs to the org.apache.xssf.usemodel package and implements the Workbook interface. Listed below are the methods and constructors of this class.

Constructor of class

S.No.Constructor and description1

##XSSFWorkbook()

Create a new XSSFworkbook object from scratch.

XSSFWorkbook(java.io.File file)

Constructs from the given XSSFWorkbook object in the file.

XSSFWorkbook(java.io.InputStream is)

Construct an XSSFWorkbook object , by buffering the entire input stream into memory and then opening an OPCPackage object for it.

XSSFWorkbook(java.lang.String path)

Build a given The full path to the file in the XSSFWorkbook object.

Sheet

Sheet is an interface in the org.apache.poi.ss.usermodel package. It is used to create high or low level files with specific names. Superinterface for all classes of spreadsheets. The most common type of spreadsheet is a worksheet, which is represented as a grid of cells.

HSSFSheet

This is a class in the org.apache.poi.hssf.usermodel package. It can create Excel spreadsheets, which allows in sheet mode and table data format.

Constructor of class

S.No.

Constructor and description

##HSSFSheet(HSSFWorkbook workbook)

Create a new HSSFSheet by calling HSSFWorkbook to create a table from scratch.

HSSFSheet(HSSFWorkbook workbook, InternalSheet sheet)

Create HSSFSheet to represent the given Table object.

XSSFSheet

This is a category that represents a high-level representation of Excel spreadsheets. This is under the org.apache.poi.hssf.usermodel package.

Constructor of class

S.No.Constructor and description12##XSSFSheet(PackagePart part, PackageRelationship rel)Class Method

##XSSFSheet()

Creates a new XSSFSheet - calls XSSFWorkbook to create a table from scratch.

Create XSSFSheet to represent the given Parts of packages and relationships.

S.No.Method and Description1addMergedRegion(CellRangeAddress region)2autoSizeColumn(int column)3iterator()4addHyperlink(XSSFHyperlink hyperlink)# For the remaining methods of this class, see the full API at: https:// poi.apache.org/apidocs/org/apache/poi/xssf/usermodel/XSSFSheet.html.


	Add the merged region of cells (so these cells merge to form one ).
	Adjust the column width to fit the content .
	This method is an alias for rowIterator() , to allow foreach loop
	Register hyperlink The collection of hyperlinks in this worksheet is

line

This is the interface in the org.apache.poi.ss.usermodel package . It is a high-level representation of a spreadsheet for a row. It is a super interface that represents all classes in the POI library.

XSSFRow

This is a class in the org.apache.poi.xssf.usermodel package. It implements the Row interface so it can create rows in a spreadsheet. Listed below are the methods and constructors of this class.

Class method

S.No.Description1createCell(int columnIndex)2setHeight(short height)For the remaining methods of this class, refer to the following link: https://poi.apache.org/apidocs/org/apache/poi /xssf/usermodel/XSSFRow.html

Create a new cell row and return.

Set the height of the short unit.

Cell

This is the interface in the org.apache.poi.ss.usermodel package. It is a superinterface for all classes that represent cells in a row in a spreadsheet.

Cells can use various attributes such as blank, number, date, error, etc. Cells should have their own (0-based) number before being added to a row.

XSSFCell

This is a class in the org.apache.poi.xssf.usermodel package. It implements the cell interface. It is a high-level representation of cells as rows in a spreadsheet.

Now I use Java to read the excel table

Code implementation

public static void excel() throws Exception {        //用流的方式先读取到你想要的excel的文件
        FileInputStream fis=new FileInputStream(new File(System.getProperty("user.dir")+"/src/excel.xls"));        //解析excel
        POIFSFileSystem pSystem=new POIFSFileSystem(fis);        //获取整个excel
        HSSFWorkbook hb=new HSSFWorkbook(pSystem);
        System.out.println(hb.getNumCellStyles());        //获取第一个表单sheet
        HSSFSheet sheet=hb.getSheetAt(0);        //获取第一行
    int firstrow=    sheet.getFirstRowNum();    //获取最后一行
    int lastrow=    sheet.getLastRowNum();    //循环行数依次获取列数
        for (int i = firstrow; i  list=new ArrayList();            for (int j = firstcell; j <lastcell>0) {
                user.setUsername(list.get(1));
                user.setPassword(list.get(2));
            }
            BaseDAO dao=new BaseDAO();
            dao.save(user);
            System.out.println();
            }
        }
        fis.close();
    }</lastcell>

Copy after login

The above is the detailed content of Java reads excel. For more information, please follow other related articles on the PHP Chinese website!