Java_poi ms-excel2003 (extension. xls) upgrade to ms-excel2007 and later (extension. xlsx) Technical Process Overview

Java_poi ms-excel2003 (extension. xls) upgrade to ms-excel2007 and later (extension. xlsx) Technical Process Overview

Eric.zhang (Nickname: No. 7th of the Traveler)

Date: November 11, 2015

A. Preface

POI technology is the Apache Foundation's Open Source Library, which provides APIs to Java programs to read and write to Microsoft Office format archives. Detailed explanation see Baidu Encyclopedia http://baike.baidu.com/link?url=CaOC3cXmdxU-2fjV-VfSZjoKvrKEpONcQiWypy0HwbpBTHZNLLrVKFL6BnOR-mQ1_ Xpbunllgvo5ccg01hzcldm-eew7um8gw2vgjxrawx3

two. Hssfworkbook upgrade to Xssfworkbook process

1. Hssfworkbook is an obsolete version of the exported Excel method that provides the ability to read and write Microsoft Excel format files.

2. The Xssfworkbook version compares the new export Excel method, providing the ability to read and write Microsoft Excel ooxml format archives.

3. Hssfworkbook base class and Xssfworkbook base class jar package distinguish and use method:

Reference: http://blog.csdn.net/szwangdf/article/details/39053859

① when we just use the XLS format, just import Poi-version-yyyymmdd.jar.

② when we also want to use XLSX format, also import Poi-ooxml-version-yyyymmdd.jar.

③ As for Poi-ooxml-schemas-version-yyyymmdd.jar, this jar is less likely to be used.

④ we need to use Poi-scratchpad-version-yyyymmdd.jar when we need to manipulate word, PPT, viso, Outlook, and so on.

Friendly tip jar Package: http://poi.apache.org/download.html

4. About the difference in performance (reference: http://poi.apache.org/spreadsheet/index.html)
If you are just reading the spreadsheet data, then use the Eventmodel API ORG.APACHE.POI.HSSF. Eventusermodel bag, or ORG.APACHE.POI.XSSF. Eventusermodel scenario, depending on your file format.
If you modify the spreadsheet data then use USERMODELAPI. You can also generate spreadsheets.
Note Usermodel system memory consumption is higher than Eventusermodel, but the main advantage is much simpler. Also note that the new XSSF Excel 2007 support for Ooxml (. xlsx) files is XML-based and handles their memory consumption higher than the old HSSF support (XLS) binaries. Here is a performance comparison chart from the official table:

5. Upgrade process considerations:

(1) about the base class and subclass method name (HSSF replaced by XSSF)

(2) With regard to cell merging, there are many projects with Hssfworkbook as the base class that contain the export function, some use the

The region class (which has been deprecated in POI3.8), where the region class is a new version of the deprecated class, has a different parameter list than the newer Cellrangeaddress class, with the following specific differences:

Difference	Region	Cellrangeaddress
Parameter list	(int firstrow, int Firstcol, int LastRow, int Lastcol )	(int firstrow, int LastRow, int Firstcol, int Lastcol )
Parameter list details	Parameter 1: line number parameter 2: Starting column number parameter 3: line number parameter 4: terminating column number	Parameter 1: Starting line parameter 2: terminating row parameter 3: Starting column parameter 4: terminating column

(3) about the construction parameters of the base class (ref. http://poi.apache.org/apidocs/)

Both Hssfworkbook and Xssfworkbook can receive file input streams but are slightly different:

①hssfworkbook (New Poifsfilesystem (FileURL)) can receive the stream Yes

②xssfworkbook (New Poifsfilesystem (FileURL)) can not receive the stream X

③ but both can receive the new FileInputStream (fileURl) file input stream Yes

For details, please refer to Apache official website http://poi.apache.org/apidocs/

6. Simple rough summary of the upgrade process

(1) Introduction of JAR Package

(2) BuildPath

(3) Copy a Hssfworkbook-based class

(4) Ctrl+f Find Replacement (find HSSF replaced by XSSF)

(5) Modify some special methods and parameter list as required (e.g. cellrangeaddress)

(6) Due to the different methods used in different projects, it is possible that the upgrade process will result in errors such as this or that, and only for individual methods according to the higher version of the parameter list and functional requirements to make adjustments.

Tips: General Common errors: Excel error after export (hint that you have deleted some merged cells and so on), you need to control the generation of Excel Process code, for the repeated merge of the logical rewrite, xssfworkbook for the duplicate merge is not allowed.

(7) Probably so write, special circumstances, special treatment.

Java_poi ms-excel2003 (extension. xls) upgrade to ms-excel2007 and later (extension. xlsx) Technical Process Overview