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

Source: Internet
Author: User
Tags deprecated

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.