There are two modes for POI to read Excel, one is user mode and the other is SAX mode. The well-known and commonly used user mode of POI is converted into CVS format before processing. Using the API of POI, we can easily read Excel, but the user mode consumes a lot of memory, and it is easy to cause memory overflow when encountering many problems such as worksheets, big data grids, fake blank lines, formulas and so on. POI officially recommends using CVS format to solve memory overflow. It is impossible for us to manually convert Excel files into CVS format and upload them. It's too much trouble to do so. So POI gives an example of converting xlsx into CVS, and based on this example, it is reformed to solve the memory overflow problem of reading Excel in user mode. Attach the following code:
[java] View Plain Text
Authorized to Apache Software Foundation (ASF) according to one or more agreements.
Contributor license agreement. Please refer to the notification file distributed with.
This is additional information about copyright ownership.
ASF licenses you to use this file under the Apache license (version 2.0).
(hereinafter referred to as the "Permit"); This document shall not be used unless it is observed.
License. You can get a copy of the license at the following address
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable laws or agreed in writing, the Software
Distribution unde a license is distributed on an "as is" basi,
There are no express or implied warranties or conditions.
For specific languages of administrative rights and, see License.
Restrictions under the license.
==================================================================== */
Import java.io.ioexception;
Import java.io.inputstream;
Import java.io.printstream;
Import java.text.simpledateformat;
Import java.util.ArrayList;
Import java.util.date;
Import java.util.list;
import javax . XML . parsers . parser configuration exception；
Import javax.xml.parsers.saxparser;
Import javax.xml.parsers.saxparserfactory;
Import org.apache.poi.hssf.usermodel.hssfdateutil;
import org . Apache . poi . open XML 4j . exceptions . open XML 4j exception；
Import org.apache.poi.openxml4j.opc.opcpackage;
Import org.apache.poi.openxml4j.opc.packageaccess;
Import org.apache.poi.ss.usermodel.builtinformats;
Import org.apache.poi.ss.usermodel.dataformatter;
Import org.apache.poi.xssf.eventusermodel.readonlysharedstringtable;
Import org.apache.poi.xssf.eventusermodel.xssreader;
Import org.apache.poi.xssf.model.stylestable;
Import org.apache.poi.xssf.usermodel.xssfcellstyle;
Import org.apache.poi.xssf.usermodel.xssfrichtextstring;
Import org.xml.sax.attributes;
Import org.xml.sax.inputsource;
Import org.xml.sax.saxexception;
Import org.xml.sax.xmlreader;
Import org.xml.sax.helpers.defaulthandler;
/**
* Using CVS mode to solve XLSX files can effectively solve the memory overflow problem in user mode.
* This mode is recommended by POI officials to read big data. In user mode, there are a lot of data, more worksheets, or many useless blank lines.
*, prone to memory overflow. The typical code for reading Excel in user mode is as follows: FileInputStream file=new.
* file inputstream(" c:\ \ test . xlsx ")； workbook WB = new xssf workbook(file)；
*
*
* @ Author Ren Shan
*/
Public class XLSXCovertCSVReader {
/**
* The type of data value is indicated by the attribute on the cell. this
* The value is usually in the cell of a "V" element.
*/
Enumeration xssfDataType {
BOOL，ERROR，FORMULA，INLINESTR，SSTINDEX，NUMBER，
}
/**
* using xssf_sax_API to process Excel, please refer to: http://poi.apache.org/spreadsheet/how-to.html # xssf _ sax _ API.
* & ltp/>；
* see also standard ECMA-376, 1 1st edition, part 4, pages 1928 ff. at.
* http://www . Ecma-international . org/publications/standards/Ecma-376 . htm
* & ltp/>；
* A web-friendly version is http://openiso.org/Ecma/376/Part4.
*/
The MyXSSFSheetHandler class extends DefaultHandler {
/**
* Form with style
*/
Private StylesTable stylesTable
/**
* tables with unique strings
*/
private ReadOnlySharedStringsTable sharedStringsTable；
/**
* The destination of the data
*/
Private final print stream output;
/**
* Number of columns read from the leftmost side.
*/
private final int minColumnCount
//Set when you see the V start element.
Private boolean vIsOpen
//Set when the cell start element is seen;
//Used when you see a cell closing an element.
Private xssfDataType nextDataType
//Used to format numeric cell values.
Private short format index;
Private string formatString
Special final data formatter formatter;
private int this column =- 1；
//Print to the last column of the output stream
private int last column number =- 1；
//Collect the characters you see.
Private StringBuffer value;
Private string [] record;
Private distribution list & lt string [] > rows = new ArrayList & ltstring[]& gt； ();
Private boolean value isCellNull = false
/**
* accept the object needed for parsing.
*
* @param style
* style sheet
* @param string
* Shared string table
* @param column
* Minimum number of columns to display
* @param target
* Output receiver
*/
Public MyXSSFSheetHandler(style stable style,
ReadOnlySharedStringsTable strings，int cols，PrintStream target) {
this.stylesTable = styles
this . sharedstringstable = strings；
this.minColumnCount = cols
this.output = target
this . value = new string buffer()；
this.nextDataType = xssfDataType。 Number;
this . formatter = new data formatter()；
record = new String[this . mincolumncount]；
rows . clear()； //Clear the lines set for each read.
}
/*
* (non-Javadoc)
*
* @ See
* org . XML . sax . helpers . default handler # startElement(Java . lang . string，
* java.lang.String，java.lang.String，org.xml.sax.Attributes)
*/
Public void startElement (string uri, string localName, string Name,
Attributesartributes) throws SAXException {
if ("inlineStr "。 equals(name) || "v "。 Equal to (name)) (
vIsOpen = true
//Clear the content cache
value . setlength(0)；
}
//c = & gt； cell
else if ("c "。 Equal to (name)) (
//Get cell reference
string r = attributes . getvalue(" r ")；
int first digit =- 1；
for(int c = 0； c & ltr . length()； ++c) {
if(character . is digit(r . charat(c))){
first digit = c；
Break;
}
}
this column = nameToColumn(r . substring(0，first digit))；
//Set the default value.
this.nextDataType = xssfDataType。 Number;
this . format index =- 1；
this.formatString = null
string cellType = attributes . getvalue(" t ")；
string cellStyleStr = attributes . getvalue(" s ")；
if ("b "。 Equal to (cell type))
nextDataType = xssfDataType。 BOOL
else if ("e "。 Equal to (cell type))
nextDataType = xssfDataType。 Mistakes;
else if ("inlineStr "。 Equal to (cell type))
nextDataType = xssfDataType。 INLINESTR
else if ("s "。 Equal to (cell type))
nextDataType = xssfDataType。 SSTINDEX
else if ("str "。 Equal to (cell type))
nextDataType = xssfDataType。 Formula;
else if (cellStyleStr！ = null) {
//This is a number, but it is almost certainly one.
//has a special style or format.
int style index = integer . parse int(cellStyleStr)；
XSSFCellStyle style = style stable . getstyleat(style index)；
this . format index = style . getdata format()；
this . format string = style . getdata formatstring()；
if (this.formatString == null)
this . format string = builtin formats
. getBuiltinFormat(this . format index)；
}
}
}
/*
* (non-Javadoc)
*
* @ see org.xml.sax.helpers.defaulthandler # endelement (java.lang.string,
* java.lang.String，java.lang.String)
*/
Public void endElement (string uri, string localName, string name)
Throw SAXException {
String thisStr = null
//v = & gt； Contents of cells
if ("v "。 Equal to (name)) (
//Process the value content as needed.
//Execute immediately, because characters () may be called many times.
Switch (next datatype) (
Uppercase and lowercase Boolean values:
char first = value . charat(0)；
thisStr = first = = ' 0 '？ "false": "true";
Break;
Case error:
ThisStr = ""Error: "+value. tostring ()+'"';
Break;
Case formula:
//Formulas may produce string values,
//So always put double quotation marks.
thistr = ' " '+value . tostring()+' " '；
Break;
Example INLINESTR:
// TODO: I have seen such an example, so I haven't tested it.
XSSFRichTextString rtsi = new XSSFRichTextString(
value . tostring())；
thistr = ' " '+rtsi . tostring()+' " '；
Break;