Example of Java reading excel table documents using Apache POI Library

Apache POI is a free, open source, cross platform Java API written in Java. Apache POI provides Java programs with the ability to read and write files in Microsoft Office format. Project download page: http://poi.apache.org/download.html

Apache POI is a Java API for creating, maintaining and operating various Excel files that comply with the office open XML (OOXML) standard and Microsoft's ole 2 Composite Document Format (ole2). With it, you can use java to read, create and modify MS Excel files. In addition, you can also use java to read and create ms word and mspowerpoint files. Apache POI provides solutions for Java operating excel.

Reading excel document example we use HSSF workbook in POI to read Excel data.

Code above, There is no problem reading Excel 2003 (XLS) files, but once you read Excel 2007 (xlsx) files, you will report an exception: "the supplied data appears to be in the Office 2007 + XML. You are calling the part of POI that deals with ole2 office documents. You need to call a different part of POI to process this data (eg xssf instead of HSSF)" After consulting the data, Excel files of Excel 2007 version need to be read using xssfworkbook, as follows:

Note: the xssf workbook needs to import poi-ooxml-3.9-sources.xml additionally Jar and poi-ooxml-schemas-3.9 jar。 In this way, the import of Excel 2007 is OK, but the import of Excel 2003 reports an exception.

Therefore, when importing excel, try to judge the version of imported excel and call different methods. I thought of using the file suffix to judge the type, but if someone changes the suffix of xlsx to XLS, if they use the function of xlsx to read, the result is an error; Although the suffix name is correct, the file content coding is wrong. Finally, it is recommended to use workbookfactory. XML in poi OOXML Create (InputStream) to create a workbook, because both HSSF workbook and xssf workbook implement the workbook interface. The code is as follows:

It is conceivable that in workbookfactory In the create() function, you must have judged the file type. Let's take a look at how the source code judges:

You can see that appropriate workbook objects are created according to the file type. It is judged by comparing the header information of the file. At this time, even if the suffix is changed, it still doesn't pass.