POI读写大数据量excel，解决超过几万行而导致内存溢出的问题

1. Excel2003与Excel2007

两个版本的最大行数和列数不同，2003版最大行数是65536行，最大列数是256列，2007版及以后的版本最大行数是1048576行，最大列数是16384列。

excel2003是以二进制的方式存储，这种格式不易被其他软件读取使用；而excel2007采用了基于XML的ooxml开放文档标准，ooxml使用XML和ZIP技术结合进行文件存储，XML是一个基于文本的格式，而且ZIP容器支持内容的压缩，所以其一大优势是可以大大减小文件的尺寸。

2. 大批量数据读写

2.1 大批量数据写入

对于大数据的Xlsx文件的写入，POI3.8提供了SXSSFSXSSFWorkbook类，采用缓存方式进行大批量写文件。

详情可以查看poi官网示例：http://poi.apache.org/spreadsheet/how-to.html#sxssf 或 http://blog.csdn.net/daiyutage/article/details/53010491

2.2 大批量数据读取

POI读取Excel有两种模式，一种是用户模式，一种是SAX事件驱动模式，将xlsx格式的文档转换成CSV格式后进行读取。用户模式API接口丰富，使用POI的API可以很容易读取Excel，但用户模式消耗的内存很大，当遇到很大sheet、大数据网格，假空行、公式等问题时，很容易导致内存溢出。POI官方推荐解决内存溢出的方式使用CVS格式解析，即SAX事件驱动模式。下面主要是讲解如何读取大批量数据：

2.2.1 pom.xml所需jar包

 1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 2   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
 3   <modelVersion>4.0.0</modelVersion>
 4   <groupId>POIExcel</groupId>
 5   <artifactId>POIExcel</artifactId>
 6   <packaging>war</packaging>
 7   <version>1.0-SNAPSHOT</version>
 8   <name>POIExcel Maven Webapp</name>
 9   <url>http://maven.apache.org</url>
10   <dependencies>
11     <dependency>
12       <groupId>junit</groupId>
13       <artifactId>junit</artifactId>
14       <version>3.8.1</version>
15       <scope>test</scope>
16     </dependency>
17 
18     <dependency>
19       <groupId>org.apache.poi</groupId>
20       <artifactId>poi</artifactId>
21       <version>3.17</version>
22     </dependency>
23 
24     <dependency>
25       <groupId>org.apache.poi</groupId>
26       <artifactId>poi-ooxml</artifactId>
27       <version>3.17</version>
28     </dependency>
29 
30     <dependency>
31       <groupId>org.apache.poi</groupId>
32       <artifactId>poi-ooxml-schemas</artifactId>
33       <version>3.17</version>
34     </dependency>
35 
36     <dependency>
37       <groupId>com.syncthemall</groupId>
38       <artifactId>boilerpipe</artifactId>
39       <version>1.2.1</version>
40     </dependency>
41 
42     <dependency>
43       <groupId>xerces</groupId>
44       <artifactId>xercesImpl</artifactId>
45       <version>2.11.0</version>
46     </dependency>
47 
48     <dependency>
49       <groupId>xml-apis</groupId>
50       <artifactId>xml-apis</artifactId>
51       <version>1.4.01</version>
52     </dependency>
53 
54     <dependency>
55       <groupId>org.apache.xmlbeans</groupId>
56       <artifactId>xmlbeans</artifactId>
57       <version>2.6.0</version>
58     </dependency>
59 
60     <dependency>
61       <groupId>sax</groupId>
62       <artifactId>sax</artifactId>
63       <version>2.0.1</version>
64     </dependency>
65 
66     <dependency>
67       <groupId>org.apache.commons</groupId>
68       <artifactId>commons-lang3</artifactId>
69       <version>3.7</version>
70     </dependency>
71 
72   </dependencies>
73   <build>
74     <finalName>POIExcel</finalName>
75   </build>
76 </project>

2.2.2 POI以SAX解析excel2007文件

解决思路：通过继承DefaultHandler类，重写process()，startElement()，characters()，endElement()这四个方法。process()方式主要是遍历所有的sheet，并依次调用startElement()、characters()方法、endElement()这三个方法。startElement()用于设定单元格的数字类型（如日期、数字、字符串等等）。characters()用于获取该单元格对应的索引值或是内容值（如果单元格类型是字符串、INLINESTR、数字、日期则获取的是索引值；其他如布尔值、错误、公式则获取的是内容值）。endElement()根据startElement()的单元格数字类型和characters()的索引值或内容值，最终得出单元格的内容值，并打印出来。

  1 package org.poi;
  2 
  3 import org.apache.poi.openxml4j.opc.OPCPackage;
  4 import org.apache.poi.ss.usermodel.BuiltinFormats;
  5 import org.apache.poi.ss.usermodel.DataFormatter;
  6 import org.apache.poi.xssf.eventusermodel.XSSFReader;
  7 import org.apache.poi.xssf.model.SharedStringsTable;
  8 import org.apache.poi.xssf.model.StylesTable;
  9 import org.apache.poi.xssf.usermodel.XSSFCellStyle;
 10 import org.apache.poi.xssf.usermodel.XSSFRichTextString;
 11 import org.xml.sax.Attributes;
 12 import org.xml.sax.InputSource;
 13 import org.xml.sax.SAXException;
 14 import org.xml.sax.XMLReader;
 15 import org.xml.sax.helpers.DefaultHandler;
 16 import org.xml.sax.helpers.XMLReaderFactory;
 17 
 18 import java.io.InputStream;
 19 import java.util.ArrayList;
 20 import java.util.List;
 21 
 22 /**
 23  * @author y
 24  * @create 2018-01-18 14:28
 25  * @desc POI读取excel有两种模式，一种是用户模式，一种是事件驱动模式
 26  * 采用SAX事件驱动模式解决XLSX文件，可以有效解决用户模式内存溢出的问题，
 27  * 该模式是POI官方推荐的读取大数据的模式，
 28  * 在用户模式下，数据量较大，Sheet较多，或者是有很多无用的空行的情况下，容易出现内存溢出
 29  * <p>
 30  * 用于解决.xlsx2007版本大数据量问题
 31  **/
 32 public class ExcelXlsxReader extends DefaultHandler {
 33 
 34     /**
 35      * 单元格中的数据可能的数据类型
 36      */
 37     enum CellDataType {
 38         BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
 39     }
 40 
 41     /**
 42      * 共享字符串表
 43      */
 44     private SharedStringsTable sst;
 45 
 46     /**
 47      * 上一次的索引值
 48      */
 49     private String lastIndex;
 50 
 51     /**
 52      * 文件的绝对路径
 53      */
 54     private String filePath = "";
 55 
 56     /**
 57      * 工作表索引
 58      */
 59     private int sheetIndex = 0;
 60 
 61     /**
 62      * sheet名
 63      */
 64     private String sheetName = "";
 65 
 66     /**
 67      * 总行数
 68      */
 69     private int totalRows=0;
 70 
 71     /**
 72      * 一行内cell集合
 73      */
 74     private List<String> cellList = new ArrayList<String>();
 75 
 76     /**
 77      * 判断整行是否为空行的标记
 78      */
 79     private boolean flag = false;
 80 
 81     /**
 82      * 当前行
 83      */
 84     private int curRow = 1;
 85 
 86     /**
 87      * 当前列
 88      */
 89     private int curCol = 0;
 90 
 91     /**
 92      * T元素标识
 93      */
 94     private boolean isTElement;
 95 
 96     /**
 97      * 判断上一单元格是否为文本空单元格
 98      */
 99     private boolean startElementFlag = true;
100     private boolean endElementFlag = false;
101     private boolean charactersFlag = false;
102 
103     /**
104      * 异常信息，如果为空则表示没有异常
105      */
106     private String exceptionMessage;
107 
108     /**
109      * 单元格数据类型，默认为字符串类型
110      */
111     private CellDataType nextDataType = CellDataType.SSTINDEX;
112 
113     private final DataFormatter formatter = new DataFormatter();
114 
115     /**
116      * 单元格日期格式的索引
117      */
118     private short formatIndex;
119 
120     /**
121      * 日期格式字符串
122      */
123     private String formatString;
124 
125     //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
126     private String prePreRef = "A", preRef = null, ref = null;
127 
128     //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
129     private String maxRef = null;
130 
131     /**
132      * 单元格
133      */
134     private StylesTable stylesTable;
135 
136     /**
137      * 遍历工作簿中所有的电子表格
138      * 并缓存在mySheetList中
139      *
140      * @param filename
141      * @throws Exception
142      */
143     public int process(String filename) throws Exception {
144         filePath = filename;
145         OPCPackage pkg = OPCPackage.open(filename);
146         XSSFReader xssfReader = new XSSFReader(pkg);
147         stylesTable = xssfReader.getStylesTable();
148         SharedStringsTable sst = xssfReader.getSharedStringsTable();
149         XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
150         this.sst = sst;
151         parser.setContentHandler(this);
152         XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
153         while (sheets.hasNext()) { //遍历sheet
154             curRow = 1; //标记初始行为第一行
155             sheetIndex++;
156             InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
157             sheetName = sheets.getSheetName();
158             InputSource sheetSource = new InputSource(sheet);
159             parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
160             sheet.close();
161         }
162         return totalRows; //返回该excel文件的总行数，不包括首列和空行
163     }
164 
165     /**
166      * 第一个执行
167      *
168      * @param uri
169      * @param localName
170      * @param name
171      * @param attributes
172      * @throws SAXException
173      */
174     @Override
175     public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
176         //c => 单元格
177         if ("c".equals(name)) {
178 
179             //前一个单元格的位置
180             if (preRef == null) {
181                 preRef = attributes.getValue("r");
182 
183             } else {
184                 //中部文本空单元格标识 ‘endElementFlag’ 判断前一次是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串跳过把空字符串的位置赋予preRef
185                 if (endElementFlag){
186                     preRef = ref;
187                 }
188             }
189 
190             //当前单元格的位置
191             ref = attributes.getValue("r");
192             //首部文本空单元格标识 ‘startElementFlag’ 判断前一次，即首部是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串, 且已知当前格，即第二格带“B”标志，则ref赋予preRef
193             if (!startElementFlag && !flag){ //上一个单元格为文本空单元格，执行下面的，使ref=preRef；flag为true表明该单元格之前有数据值，即该单元格不是首部空单元格，则跳过
194                 // 这里只有上一个单元格为文本空单元格，且之前的几个单元格都没有值才会执行
195                 preRef = ref;
196             }
197 
198             //设定单元格类型
199             this.setNextDataType(attributes);
200             endElementFlag = false;
201             charactersFlag = false;
202             startElementFlag = false;
203         }
204 
205         //当元素为t时
206         if ("t".equals(name)) {
207             isTElement = true;
208         } else {
209             isTElement = false;
210         }
211 
212         //置空
213         lastIndex = "";
214     }
215 
216 
217 
218     /**
219      * 第二个执行
220      * 得到单元格对应的索引值或是内容值
221      * 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
222      * 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
223      * @param ch
224      * @param start
225      * @param length
226      * @throws SAXException
227      */
228     @Override
229     public void characters(char[] ch, int start, int length) throws SAXException {
230         startElementFlag = true;
231         charactersFlag = true;
232         lastIndex += new String(ch, start, length);
233     }
234 
235     /**
236      * 第三个执行
237      *
238      * @param uri
239      * @param localName
240      * @param name
241      * @throws SAXException
242      */
243     @Override
244     public void endElement(String uri, String localName, String name) throws SAXException {
245         //t元素也包含字符串
246         if (isTElement) {//这个程序没经过
247             //将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
248             String value = lastIndex.trim();
249             cellList.add(curCol, value);
250             endElementFlag = true;
251             curCol++;
252             isTElement = false;
253             //如果里面某个单元格含有值，则标识该行不为空行
254             if (value != null && !"".equals(value)) {
255                 flag = true;
256             }
257         } else if ("v".equals(name)) {
258             //v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
259             String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
260 
261             //补全单元格之间的空单元格
262             if (!ref.equals(preRef)) {
263                 int len = countNullCell(ref, preRef);
264                 for (int i = 0; i < len; i++) {
265                     cellList.add(curCol, "");
266                     curCol++;
267                 }
268             } else if (ref.equals(preRef) && !ref.startWith("A")){ //ref等于preRef，且以B或者C...开头，表明首部为空格
271                 int len = countNullCell(ref, "A");
272                 for (int i = 0; i <= len; i++) {
273                     cellList.add(curCol, "");
274                     curCol++;
275                 }
276             }
277             cellList.add(curCol, value);
278             curCol++;
279             endElementFlag = true;
280             //如果里面某个单元格含有值，则标识该行不为空行
281             if (value != null && !"".equals(value)) {
282                 flag = true;
283             }
284         } else {
285             //如果标签名称为row，这说明已到行尾，调用optRows()方法
286             if ("row".equals(name)) {
287                 //默认第一行为表头，以该行单元格数目为最大数目
288                 if (curRow == 1) {
289                     maxRef = ref;
290                 }
291                 //补全一行尾部可能缺失的单元格
292                 if (maxRef != null) {
293                     int len = -1;
294                     //前一单元格，true则不是文本空字符串，false则是文本空字符串
295                     if (charactersFlag){
296                         len = countNullCell(maxRef, ref);
297                     }else {
298                         len = countNullCell(maxRef, preRef);
299                     }
300                     for (int i = 0; i <= len; i++) {
301                         cellList.add(curCol, "");
302                         curCol++;
303                     }
304                 }
305 
306                 if (flag&&curRow!=1){ //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
307                     ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow, cellList);
308                     totalRows++;
309                 }
310 
311                 cellList.clear();
312                 curRow++;
313                 curCol = 0;
314                 preRef = null;
315                 prePreRef = null;
316                 ref = null;
317                 flag=false;
318             }
319         }
320     }
321 
322     /**
323      * 处理数据类型
324      *
325      * @param attributes
326      */
327     public void setNextDataType(Attributes attributes) {
328         nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字
329         formatIndex = -1;
330         formatString = null;
331         String cellType = attributes.getValue("t"); //单元格类型
332         String cellStyleStr = attributes.getValue("s"); //
333         String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1
334 
335         if ("b".equals(cellType)) { //处理布尔值
336             nextDataType = CellDataType.BOOL;
337         } else if ("e".equals(cellType)) {  //处理错误
338             nextDataType = CellDataType.ERROR;
339         } else if ("inlineStr".equals(cellType)) {
340             nextDataType = CellDataType.INLINESTR;
341         } else if ("s".equals(cellType)) { //处理字符串
342             nextDataType = CellDataType.SSTINDEX;
343         } else if ("str".equals(cellType)) {
344             nextDataType = CellDataType.FORMULA;
345         }
346 
347         if (cellStyleStr != null) { //处理日期
348             int styleIndex = Integer.parseInt(cellStyleStr);
349             XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
350             formatIndex = style.getDataFormat();
351             formatString = style.getDataFormatString();
352             if (formatString.contains("m/d/yyyy") || formatString.contains("yyyy/mm/dd")|| formatString.contains("yyyy/m/d") ) {
353                 nextDataType = CellDataType.DATE;
354                 formatString = "yyyy-MM-dd hh:mm:ss";
355             }
356 
357             if (formatString == null) {
358                 nextDataType = CellDataType.NULL;
359                 formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
360             }
361         }
362     }
363 
364     /**
365      * 对解析出来的数据进行类型处理
366      * @param value   单元格的值，
367      *                value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
368      *                SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
369      * @param thisStr 一个空字符串
370      * @return
371      */
372     @SuppressWarnings("deprecation")
373     public String getDataValue(String value, String thisStr) {
374         switch (nextDataType) {
375             // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
376             case BOOL: //布尔值
377                 char first = value.charAt(0);
378                 thisStr = first == '0' ? "FALSE" : "TRUE";
379                 break;
380             case ERROR: //错误
381                 thisStr = "\"ERROR:" + value.toString() + '"';
382                 break;
383             case FORMULA: //公式
384                 thisStr = '"' + value.toString() + '"';
385                 break;
386             case INLINESTR:
387                 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
388                 thisStr = rtsi.toString();
389                 rtsi = null;
390                 break;
391             case SSTINDEX: //字符串
392                 String sstIndex = value.toString();
393                 try {
394                     int idx = Integer.parseInt(sstIndex);
395                     XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
396                     thisStr = rtss.toString();
397                     System.out.println(thisStr);
398                     //有些字符串是文本格式的，但内容却是日期
399 
400                     rtss = null;
401                 } catch (NumberFormatException ex) {
402                     thisStr = value.toString();
403                 }
404                 break;
405             case NUMBER: //数字
406                 if (formatString != null) {
407                     thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
408                 } else {
409                     thisStr = value;
410                 }
411                 thisStr = thisStr.replace("_", "").trim();
412                 break;
413             case DATE: //日期
414                 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
415                 // 对日期字符串作特殊处理，去掉T
416                 thisStr = thisStr.replace("T", " ");
417                 break;
418             default:
419                 thisStr = " ";
420                 break;
421         }
422         return thisStr;
423     }
424 
425     public int countNullCell(String ref, String preRef) {
426         //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
427         String xfd = ref.replaceAll("\\d+", "");
428         String xfd_1 = preRef.replaceAll("\\d+", "");
429 
430         xfd = fillChar(xfd, 3, '@', true);
431         xfd_1 = fillChar(xfd_1, 3, '@', true);
432 
433         char[] letter = xfd.toCharArray();
434         char[] letter_1 = xfd_1.toCharArray();
435         int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
436         return res - 1;
437     }
438 
439     public String fillChar(String str, int len, char let, boolean isPre) {
440         int len_1 = str.length();
441         if (len_1 < len) {
442             if (isPre) {
443                 for (int i = 0; i < (len - len_1); i++) {
444                     str = let + str;
445                 }
446             } else {
447                 for (int i = 0; i < (len - len_1); i++) {
448                     str = str + let;
449                 }
450             }
451         }
452         return str;
453     }
454 
455     /**
456      * @return the exceptionMessage
457      */
458     public String getExceptionMessage() {
459         return exceptionMessage;
460     }
461 }

2.2.3 POI通过继承HSSFListener类来解决Excel2003文件

解决思路：重写process()，processRecord()两个方法，其中processRecord是核心方法，用于处理sheetName和各种单元格数字类型。

  1 package org.poi;
  2 
  3 import org.apache.poi.hssf.eventusermodel.*;
  4 import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
  5 import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
  6 import org.apache.poi.hssf.model.HSSFFormulaParser;
  7 import org.apache.poi.hssf.record.*;
  8 import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
  9 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 10 import org.apache.poi.poifs.filesystem.POIFSFileSystem;
 11 
 12 import java.io.FileInputStream;
 13 import java.util.ArrayList;
 14 import java.util.List;
 15 
 16 /**
 17  * @author y
 18  * @create 2018-01-19 14:18
 19  * @desc 用于解决.xls2003版本大数据量问题
 20  **/
 21 public class ExcelXlsReader implements HSSFListener {
 22 
 23     private int minColums = -1;
 24 
 25     private POIFSFileSystem fs;
 26 
 27     /**
 28      * 总行数
 29      */
 30     private int totalRows=0;
 31 
 32     /**
 33      * 上一行row的序号
 34      */
 35     private int lastRowNumber;
 36 
 37     /**
 38      * 上一单元格的序号
 39      */
 40     private int lastColumnNumber;
 41 
 42     /**
 43      * 是否输出formula，还是它对应的值
 44      */
 45     private boolean outputFormulaValues = true;
 46 
 47     /**
 48      * 用于转换formulas
 49      */
 50     private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener;
 51 
 52     //excel2003工作簿
 53     private HSSFWorkbook stubWorkbook;
 54 
 55     private SSTRecord sstRecord;
 56 
 57     private FormatTrackingHSSFListener formatListener;
 58 
 59     private final HSSFDataFormatter formatter = new HSSFDataFormatter();
 60 
 61     /**
 62      * 文件的绝对路径
 63      */
 64     private String filePath = "";
 65 
 66     //表索引
 67     private int sheetIndex = 0;
 68 
 69     private BoundSheetRecord[] orderedBSRs;
 70 
 71     @SuppressWarnings("unchecked")
 72     private ArrayList boundSheetRecords = new ArrayList();
 73 
 74     private int nextRow;
 75 
 76     private int nextColumn;
 77 
 78     private boolean outputNextStringRecord;
 79 
 80     //当前行
 81     private int curRow = 0;
 82 
 83     //存储一行记录所有单元格的容器
 84     private List<String> cellList = new ArrayList<String>();
 85 
 86     /**
 87      * 判断整行是否为空行的标记
 88      */
 89     private boolean flag = false;
 90 
 91     @SuppressWarnings("unused")
 92     private String sheetName;
 93 
 94     /**
 95      * 遍历excel下所有的sheet
 96      *
 97      * @param fileName
 98      * @throws Exception
 99      */
100     public int process(String fileName) throws Exception {
101         filePath = fileName;
102         this.fs = new POIFSFileSystem(new FileInputStream(fileName));
103         MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
104         formatListener = new FormatTrackingHSSFListener(listener);
105         HSSFEventFactory factory = new HSSFEventFactory();
106         HSSFRequest request = new HSSFRequest();
107         if (outputFormulaValues) {
108             request.addListenerForAllRecords(formatListener);
109         } else {
110             workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);
111             request.addListenerForAllRecords(workbookBuildingListener);
112         }
113         factory.processWorkbookEvents(request, fs);
114 
115         return totalRows; //返回该excel文件的总行数，不包括首列和空行
116     }
117     
118     /**
119      * HSSFListener 监听方法，处理Record
120      * 处理每个单元格
121      * @param record
122      */
123     @SuppressWarnings("unchecked")
124     public void processRecord(Record record) {
125         int thisRow = -1;
126         int thisColumn = -1;
127         String thisStr = null;
128         String value = null;
129         switch (record.getSid()) {
130             case BoundSheetRecord.sid:
131                 boundSheetRecords.add(record);
132                 break;
133             case BOFRecord.sid: //开始处理每个sheet
134                 BOFRecord br = (BOFRecord) record;
135                 if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
136                     //如果有需要，则建立子工作簿
137                     if (workbookBuildingListener != null && stubWorkbook == null) {
138                         stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
139                     }
140 
141                     if (orderedBSRs == null) {
142                         orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
143                     }
144                     sheetName = orderedBSRs[sheetIndex].getSheetname();
145                     sheetIndex++;
146                 }
147                 break;
148             case SSTRecord.sid:
149                 sstRecord = (SSTRecord) record;
150                 break;
151             case BlankRecord.sid: //单元格为空白
152                 BlankRecord brec = (BlankRecord) record;
153                 thisRow = brec.getRow();
154                 thisColumn = brec.getColumn();
155                 thisStr = "";
156                 cellList.add(thisColumn, thisStr);
157                 break;
158             case BoolErrRecord.sid: //单元格为布尔类型
159                 BoolErrRecord berec = (BoolErrRecord) record;
160                 thisRow = berec.getRow();
161                 thisColumn = berec.getColumn();
162                 thisStr = berec.getBooleanValue() + "";
163                 cellList.add(thisColumn, thisStr);
164                 checkRowIsNull(thisStr);  //如果里面某个单元格含有值，则标识该行不为空行
165                 break;
166             case FormulaRecord.sid://单元格为公式类型
167                 FormulaRecord frec = (FormulaRecord) record;
168                 thisRow = frec.getRow();
169                 thisColumn = frec.getColumn();
170                 if (outputFormulaValues) {
171                     if (Double.isNaN(frec.getValue())) {
172                         outputNextStringRecord = true;
173                         nextRow = frec.getRow();
174                         nextColumn = frec.getColumn();
175                     } else {
176                         thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
177                     }
178                 } else {
179                     thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
180                 }
181                 cellList.add(thisColumn, thisStr);
182                 checkRowIsNull(thisStr);  //如果里面某个单元格含有值，则标识该行不为空行
183                 break;
184             case StringRecord.sid: //单元格中公式的字符串
185                 if (outputNextStringRecord) {
186                     StringRecord srec = (StringRecord) record;
187                     thisStr = srec.getString();
188                     thisRow = nextRow;
189                     thisColumn = nextColumn;
190                     outputNextStringRecord = false;
191                 }
192                 break;
193             case LabelRecord.sid:
194                 LabelRecord lrec = (LabelRecord) record;
195                 curRow = thisRow = lrec.getRow();
196                 thisColumn = lrec.getColumn();
197                 value = lrec.getValue().trim();
198                 value = value.equals("") ? "" : value;
199                 cellList.add(thisColumn, value);
200                 checkRowIsNull(value);  //如果里面某个单元格含有值，则标识该行不为空行
201                 break;
202             case LabelSSTRecord.sid: //单元格为字符串类型
203                 LabelSSTRecord lsrec = (LabelSSTRecord) record;
204                 curRow = thisRow = lsrec.getRow();
205                 thisColumn = lsrec.getColumn();
206                 if (sstRecord == null) {
207                     cellList.add(thisColumn, "");
208                 } else {
209                     value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
210                     value = value.equals("") ? "" : value;
211                     cellList.add(thisColumn, value);
212                     checkRowIsNull(value);  //如果里面某个单元格含有值，则标识该行不为空行
213                 }
214                 break;
215             case NumberRecord.sid: //单元格为数字类型
216                 NumberRecord numrec = (NumberRecord) record;
217                 curRow = thisRow = numrec.getRow();
218                 thisColumn = numrec.getColumn();
219 
220                 //第一种方式
221                 //value = formatListener.formatNumberDateCell(numrec).trim();//这个被写死，采用的m/d/yy h:mm格式，不符合要求
222 
223                 //第二种方式，参照formatNumberDateCell里面的实现方法编写
224                 Double valueDouble=((NumberRecord)numrec).getValue();
225                 String formatString=formatListener.getFormatString(numrec);
226                 if (formatString.contains("m/d/yy")){
227                     formatString="yyyy-MM-dd hh:mm:ss";
228                 }
229                 int formatIndex=formatListener.getFormatIndex(numrec);
230                 value=formatter.formatRawCellContents(valueDouble, formatIndex, formatString).trim();
231 
232                 value = value.equals("") ? "" : value;
233                 //向容器加入列值
234                 cellList.add(thisColumn, value);
235                 checkRowIsNull(value);  //如果里面某个单元格含有值，则标识该行不为空行
236                 break;
237             default:
238                 break;
239         }
240 
241         //遇到新行的操作
242         if (thisRow != -1 && thisRow != lastRowNumber) {
243             lastColumnNumber = -1;
244         }
245 
246         //空值的操作
247         if (record instanceof MissingCellDummyRecord) {
248             MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
249             curRow = thisRow = mc.getRow();
250             thisColumn = mc.getColumn();
251             cellList.add(thisColumn, "");
252         }
253 
254         //更新行和列的值
255         if (thisRow > -1)
256             lastRowNumber = thisRow;
257         if (thisColumn > -1)
258             lastColumnNumber = thisColumn;
259 
260         //行结束时的操作
261         if (record instanceof LastCellOfRowDummyRecord) {
262             if (minColums > 0) {
263                 //列值重新置空
264                 if (lastColumnNumber == -1) {
265                     lastColumnNumber = 0;
266                 }
267             }
268             lastColumnNumber = -1;
269 
270             if (flag&&curRow!=0) { //该行不为空行且该行不是第一行，发送（第一行为列名，不需要）
271                 ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow + 1, cellList); //每行结束时，调用sendRows()方法
272                 totalRows++;
273             }
274             //清空容器
275             cellList.clear();
276             flag=false;
277         }
278     }
279 
280     /**
281      * 如果里面某个单元格含有值，则标识该行不为空行
282      * @param value
283      */
284     public void checkRowIsNull(String value){
285         if (value != null && !"".equals(value)) {
286             flag = true;
287         }
288     }
289 }

2.2.4 辅助类ExcelReaderUtil

调用ExcelXlsReader类和ExcelXlsxReader类对excel2003和excel2007两个版本进行大批量数据读取：

 1 package org.poi;
 2 
 3 import java.util.List;
 4 
 5 /**
 6  * @author y
 7  * @create 2018-01-19 0:13
 8  * @desc
 9  **/
10 public class ExcelReaderUtil {
11     //excel2003扩展名
12     public static final String EXCEL03_EXTENSION = ".xls";
13     //excel2007扩展名
14     public static final String EXCEL07_EXTENSION = ".xlsx";
15 
16     /**
17      * 每获取一条记录，即打印
18      * 在flume里每获取一条记录即发送，而不必缓存起来，可以大大减少内存的消耗，这里主要是针对flume读取大数据量excel来说的
19      * @param sheetName
20      * @param sheetIndex
21      * @param curRow
22      * @param cellList
23      */
24     public static void sendRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
25             StringBuffer oneLineSb = new StringBuffer();
26             oneLineSb.append(filePath);
27             oneLineSb.append("--");
28             oneLineSb.append("sheet" + sheetIndex);
29             oneLineSb.append("::" + sheetName);//加上sheet名
30             oneLineSb.append("--");
31             oneLineSb.append("row" + curRow);
32             oneLineSb.append("::");
33             for (String cell : cellList) {
34                 oneLineSb.append(cell.trim());
35                 oneLineSb.append("|");
36             }
37             String oneLine = oneLineSb.toString();
38             if (oneLine.endsWith("|")) {
39                 oneLine = oneLine.substring(0, oneLine.lastIndexOf("|"));
40             }// 去除最后一个分隔符
41 
42             System.out.println(oneLine);
43     }
44 
45     public static void readExcel(String fileName) throws Exception {
46         int totalRows =0;
47         if (fileName.endsWith(EXCEL03_EXTENSION)) { //处理excel2003文件
48             ExcelXlsReader excelXls=new ExcelXlsReader();
49             totalRows =excelXls.process(fileName);
50         } else if (fileName.endsWith(EXCEL07_EXTENSION)) {//处理excel2007文件
51             ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();
52             totalRows = excelXlsxReader.process(fileName);
53         } else {
54             throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
55         }
56         System.out.println("发送的总行数：" + totalRows);
57     }
58 
59     public static void main(String[] args) throws Exception {
60         String path="C:\\Users\\y****\\Desktop\\TestSample\\H_20171226_***_*****_0430.xlsx";
61         ExcelReaderUtil.readExcel(path);
62     }
63 }