POI事件模式指北（三）- 读取Excel实战
1、简介
这篇文章将介绍一个完整的利用POI事件模式读取Excel的实例，目的是贴近实际使用中的情况，并且考虑了细节的问题而不仅仅满足于实现简单功能。
1.1、业务背景
系统中使用了POI的用户模式读取Excel文件，某一次用户上传了一个超过1M的文件，导致系统内存很快耗光，最后出现了OOM异常，最后分析决定使用事件模式进行改造以提高性能。
1.2、版本和依赖
下面列出来的是实例代码实际使用的情况，您可以选择其他版本，绝大多数情况下可以正常运行。
- JDK6
- poi-3.9.jar
- poi-ooxml-3.9.jar
- dom4j-1.6.1.jar
- xmlbeans-2.3.0.jar
以上是实现读取Excel所需的最小jar包；如果你还需要或者想要学习POI的其他部分，请自行导入其他包；强烈建议导入POI全部jar包。
2、示例代码
下面的导入针对比较典型的导入场景，主要有以下假定条件：
- 导入的文件列数是确定的
- 需要导入的内容在第一个sheet中
- 文件中所有数据都是有意义的且不包含无效数据
上面的假设代表一种典型场景，如果实际场景有所不同，只需要对下面代码进行简单改造即可。
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
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;
public class ExcelEventParser {
enum xssfDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
}
class XLSReader implements HSSFListener {
//POIFS文件流
private POIFSFileSystem fs;
//当前行数
private int lastRowNumber;
//当前列数
private int lastColumnNumber;
//是否计算公式的值
private boolean outputFormulaValues = true;
private SheetRecordCollectingListener workbookBuildingListener;
private HSSFWorkbook stubWorkbook;
//共享字符集
private SSTRecord sstRecord;
private FormatTrackingHSSFListener formatListener;
private int sheetIndex = -1;
private List<Record> boundSheetRecords = new ArrayList<Record>();
//公式单元格结果所在行
private int nextRow;
//公式单元格结果所在列
private int nextColumn;
//公式单元格是否存在计算值
private boolean outputNextStringRecord;
//当前行数
private int curRow = 0;
//当前最大列数
private int maxRef = 0;
//空单元格使用的填充字符
private String NULL = "-";
private List<String> row = new ArrayList<String>();
private List<List<String>> rows = new ArrayList<List<String>>();
/**
* 读取Excel2003文件
* @param filePath 文件路径
* @return
* @throws IOException
*/
public List<List<String>> process(String filePath) throws IOException {
//使用POIFS读取文件流
this.fs = new POIFSFileSystem(new FileInputStream(filePath));
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
formatListener = new FormatTrackingHSSFListener(listener);
HSSFEventFactory factory = new HSSFEventFactory();
HSSFRequest request = new HSSFRequest();
if (outputFormulaValues) {
//添加全部监听器
request.addListenerForAllRecords(formatListener);
} else {
workbookBuildingListener = new SheetRecordCollectingListener(formatListener);
request.addListenerForAllRecords(workbookBuildingListener);
}
//处理文件流
factory.processWorkbookEvents(request, fs);
return rows;
}
/**
* 依次处理每个监听的record
* @param record
* @return
* @throws IOException
*/
public void processRecord(Record record) {
//只处理第一个sheet，其他全部忽略
if (sheetIndex > 0) {
return;
}
if (maxRef > MAX_COLUMN) {
return;
}
int thisRow = -1;
int thisColumn = -1;
String thisStr = null;
String value = null;
//根据record的sid匹配类型分别进行处理
//可以在这里添加业务逻辑
switch (record.getSid()) {
// 记录了sheetName
case BoundSheetRecord.sid:
boundSheetRecords.add(record);
break;
// Workbook、Sheet的开始
case BOFRecord.sid:
BOFRecord br = (BOFRecord) record;
if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
if (workbookBuildingListener != null && stubWorkbook == null) {
stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
}
sheetIndex++;
}
break;
//共享字符集
case SSTRecord.sid:
sstRecord = (SSTRecord) record;
break;
// 存在单元格样式的空单元格
case BlankRecord.sid:
BlankRecord brec = (BlankRecord) record;
thisRow = brec.getRow();
thisColumn = brec.getColumn();
thisStr = NULL;
row.add(thisColumn, thisStr);
break;
// 布尔或错误单元格
case BoolErrRecord.sid:
BoolErrRecord berec = (BoolErrRecord) record;
thisRow = berec.getRow();
thisColumn = berec.getColumn();
thisStr = berec.getBooleanValue() + "";
row.add(thisColumn, thisStr);
break;
// 公式单元格
case FormulaRecord.sid:
//调用方法计算单元格的值
FormulaRecord frec = (FormulaRecord) record;
thisRow = frec.getRow();
thisColumn = frec.getColumn();
if (outputFormulaValues) {
if (Double.isNaN(frec.getValue())) {
outputNextStringRecord = true;
nextRow = frec.getRow();
nextColumn = frec.getColumn();
} else {
thisStr = formatListener.formatNumberDateCell(frec);
}
} else {
thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
row.add(thisColumn, thisStr);
break;
// 公式的计算结果单元格
case StringRecord.sid:
if (outputNextStringRecord) {
StringRecord srec = (StringRecord) record;
thisStr = srec.getString();
thisRow = nextRow;
thisColumn = nextColumn;
outputNextStringRecord = false;
}
break;
// 文本单元格
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord) record;
curRow = thisRow = lrec.getRow();
thisColumn = lrec.getColumn();
value = lrec.getValue().trim();
value = value.equals("") ? NULL : value;
this.row.add(thisColumn, value);
break;
// 共用的文本单元格
case LabelSSTRecord.sid:
LabelSSTRecord lsrec = (LabelSSTRecord) record;
curRow = thisRow = lsrec.getRow();
thisColumn = lsrec.getColumn();
if (sstRecord == null) {
row.add(thisColumn, NULL);
} else {
value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
value = value.equals("") ? NULL : value;
row.add(thisColumn, value);
}
break;
// 数值单元格：数字单元格和日期单元格
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
curRow = thisRow = numrec.getRow();
thisColumn = numrec.getColumn();
//处理时间类型的数字
value = formatListener.formatNumberDateCell(numrec).trim();
value = value.equals("") ? NULL : value;
row.add(thisColumn, value);
break;
default:
break;
}
if (thisRow != -1 && thisRow != lastRowNumber) {
lastColumnNumber = -1;
}
if (record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
curRow = thisRow = mc.getRow();
thisColumn = mc.getColumn();
row.add(thisColumn, NULL);
}
if (thisRow > -1)
lastRowNumber = thisRow;
if (thisColumn > -1)
lastColumnNumber = thisColumn;
//进入行结束处理
if (record instanceof LastCellOfRowDummyRecord) {
if (maxRef > row.size()) {
int len = maxRef - row.size();
for (int i = 0; i < len; i++) {
lastColumnNumber++;
row.add(lastColumnNumber, NULL);
}
}
if (curRow == 0) {
maxRef = row.size();
}
rows.add(row);
lastColumnNumber = -1;
row = new ArrayList<String>();
}
}
}
class XLSXReader extends DefaultHandler {
//样式数据
private StylesTable stylesTable;
//共享字符集
private ReadOnlySharedStringsTable sharedStringsTable;
//是否存在内联字符
private boolean vIsOpen;
//记录数据类型
private xssfDataType nextDataType;
private short formatIndex;
private String formatString;
//格式化类
private final DataFormatter formatter;
//当前所在列
private int thisColumn = -1;
//当前单元格值
private StringBuffer value;
//储存一列的数据
private List<String> record = new ArrayList<String>();
//储存全部数据
private List<List<String>> rows = new ArrayList<List<String>>();
//判断当前行有没有空单元格
private boolean isCellNull = false;
//前一个单元格和当前单元格
private String preRef = null, ref = null;
//避免同一个单元格重复计数
private String Newcell = null;
//计数当前行中的空格
private int rowNull = 0;
//当前单元格对应的序号
private int curCol = 0;
//当前行对应的序号
private int curRow = 0;
//空单元格使用的填充字符
private String NULL = "-";
/**
* XLSXReader构造函数
* @param styles 单元格样式数据
* @param strings 共享字符集
*/
public XLSXReader(StylesTable styles, ReadOnlySharedStringsTable strings) {
this.stylesTable = styles;
this.sharedStringsTable = strings;
this.value = new StringBuffer();
this.nextDataType = xssfDataType.NUMBER;
this.formatter = new DataFormatter();
record = new ArrayList<String>();
rows.clear();
}
/**
* 每当遇到开始标签时进入
* @param uri 命名空间路径或空
* @param localName 本地名称或空
* @param name 标签名称
* @param attributes 标签内容
* @return
* @throws SAXException
*/
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
if ("inlineStr".equals(name) || "v".equals(name)) {
vIsOpen = true;
value.setLength(0);
//每当遇到<c>表示新的单元格
} else if ("c".equals(name)) {
//记录上一个单元格和当前单元格用于填充空格
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
preRef = ref;
}
ref = attributes.getValue("r");
String r = ref;
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
//获取当前列数
thisColumn = nameToColumn(r.substring(0, firstDigit));
//判断是否超过最大列数
if (thisColumn > MAX_COLUMN) {
return;
}
//将单元格类型翻译成设定名称
this.nextDataType = xssfDataType.NUMBER;
this.formatIndex = -1;
this.formatString = null;
String cellTpye = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
if ("b".equals(cellTpye))
nextDataType = xssfDataType.BOOL;
else if ("e".equals(cellTpye))
nextDataType = xssfDataType.ERROR;
else if ("inlineStr".equals(cellTpye))
nextDataType = xssfDataType.INLINESTR;
else if ("s".equals(cellTpye))
nextDataType = xssfDataType.SSTINDEX;
else if ("str".equals(cellTpye))
nextDataType = xssfDataType.FORMULA;
else if (cellStyleStr != null) {
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null) {
this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
}
}
}
}
/**
* 每当遇到结束标签时进入
* @param uri 命名空间路径或空
* @param localName 本地名称或空
* @param name 标签名称
* @return
* @throws SAXException
*/
public void endElement(String uri, String localName, String name) throws SAXException {
//当遇到</v>标签时，表面存在一个需要处理的值
if ("v".equals(name)) {
if (thisColumn > MAX_COLUMN) {
return;
}
String thisStr = NULL;
//根据当前单元格和上一个单元格判断需要补充的空单元格
if (null != ref && !ref.equals(Newcell)) {
if (!ref.equals(preRef)) {
int num = Integer.parseInt(ref.replaceAll("[A-Z]", ""));
int num_1 = Integer.parseInt(preRef.replaceAll("[A-Z]", ""));
int len = 0;
//取出两个单元格所在行数，如果行数不同则进行换行，从A1开始填充
if (num > num_1) {
len = countNullCell(ref, "A1")+1;
}else{
len = countNullCell(ref, preRef);
}
//填充空单元格
for (int i = 0; i < len; i++) {
record.add(curCol, thisStr);
curCol++;
Newcell = ref;
rowNull++;
}
}
}
//选择合适的方式处理单元格
switch (nextDataType) {
case BOOL:
char first = value.charAt(0);
thisStr = first == '0' ? "否" : "是";
break;
case ERROR:
thisStr = value.toString();
break;
case FORMULA:
thisStr = value.toString();
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
thisStr = rtsi.toString();
break;
case SSTINDEX:
String sstIndex = value.toString();
try {
//查询字符索引对应的字符值
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
thisStr = rtss.toString();
} catch (NumberFormatException ex) {
System.out.println("未找到对应的SST索引 '" + sstIndex + "': " + ex.toString());
}
break;
case NUMBER:
String n = value.toString();
//判断该数字是不是时间
if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) {
Double d = Double.parseDouble(n);
Date date = HSSFDateUtil.getJavaDate(d);
thisStr = formatDateToString(date);
} else if (this.formatString != null)
thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex,
this.formatString);
else
thisStr = n;
break;
//其他特殊类型可以自己扩展
default:
thisStr = "(尚未支持的类型: " + nextDataType + ")";
break;
}
if (thisStr == null || thisStr.equals(NULL)) {
isCellNull = true;
}
//这里可以根据实际业务进行改造，以单元格为单位处理
record.add(curCol, thisStr);
curCol++;
} else if ("row".equals(name)) {
if (isCellNull == false && record.size() != rowNull) {
if (MAX_COLUMN >= record.size()) {
int len = MAX_COLUMN - record.size();
for (int i = 0; i < len; i++) {
record.add(curCol, NULL);
curCol++;
}
}
//这里可以根据实际业务进行改造，以行为单位处理
rows.add(curRow, record);
isCellNull = false;
//这里必须新建对象，不能为了节约清空之前的对象
record = new ArrayList<String>();
curRow++;
curCol = 0;
rowNull = 0;
}
}
}
/**
* 计算两个单元格中间的空单元格数量
* <p>Excel2007文件最大行数为1048576，最大列数为16284，列名为XFD</P>
* @param ref 当前单元格名称
* @param preRef 之前一个单元格名称
* @return
*/
private int countNullCell(String ref, String preRef) {
String xfd = ref.replaceAll("\\d+", "");
String xfd_1 = preRef.replaceAll("\\d+", "");
xfd = fillChar(xfd, 3, '@', true);
xfd_1 = fillChar(xfd_1, 3, '@', true);
char[] letter = xfd.toCharArray();
char[] letter_1 = xfd_1.toCharArray();
int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
return res - 1;
}
/**
* 用特定字符填充字符串到指定长度
* @param str 原字符串
* @param len 目标串的长度
* @param let 填充使用的字符
* @param isPre 是否在前面填充字符
* @return
*/
private String fillChar(String str, int len, char let, boolean isPre) {
int len_1 = str.length();
if (len_1 < len) {
if (isPre) {
for (int i = 0; i < (len - len_1); i++) {
str = let + str;
}
} else {
for (int i = 0; i < (len - len_1); i++) {
str = str + let;
}
}
}
return str;
}
/**
* 每遇到一个标签调用，用于记录标签类型
* @param ch 文件流字符数组
* @param start 开始位置
* @param length 读取长度
* @return
* @throws SAXException
*/
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
if (vIsOpen)
value.append(ch, start, length);
}
/**
* 根据单元格名称计算列数
* @param name 单元格名称(如:A1)
* @return
*/
private int nameToColumn(String name) {
int column = -1;
for (int i = 0; i < name.length(); ++i) {
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}
/**
* 将日期类型转变为String格式
* @param date 日期
* @return
*/
private String formatDateToString(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return sdf.format(date);
}
}
//事先定义的最大列数
private int MAX_COLUMN = 7;
private static String XLS = ".xls";
private static String XLSX = ".xlsx";
public ExcelEventParser() {
}
/**
* 构造XLSXReader读取Excel内容
* @param styles 样式信息
* @param strings 共享字符集
* @param sheetInputStream sheet输入流
* @return
* @throws IOException
* @throws ParserConfigurationException
* @throws SAXException
*/
private List<List<String>> XLSXProcessSheet(StylesTable styles, ReadOnlySharedStringsTable strings,
InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
//将输入流包装成XML源
InputSource sheetSource = new InputSource(sheetInputStream);
//构造基于标签回调的XMLReader
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.getXMLReader();
//构造XLSXReader
XLSXReader handler = new XLSXReader(styles, strings);
//添加管理者
sheetParser.setContentHandler(handler);
//读入XML源
sheetParser.parse(sheetSource);
return handler.rows;
}
/**
* 读取Excel2007文件
* @param filePath 文件路径
* @return
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
private List<List<String>> XLSXprocess(String filePath)
throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
//将文件以压缩包的形式读入
OPCPackage opcPackage = OPCPackage.open(filePath, PackageAccess.READ);
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opcPackage);
XSSFReader xssfReader = new XSSFReader(opcPackage);
List<List<String>> sheet = null;
//读入样式信息
StylesTable styles = xssfReader.getStylesTable();
//构造sheet迭代器
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
//这里只读入了第一个sheet，如果需要读取多个可以遍历这个迭代器
if (iter.hasNext()) {
InputStream stream = iter.next();
//构造XLSXReader读取内容
sheet = XLSXProcessSheet(styles, strings, stream);
//关闭流
stream.close();
}
opcPackage.close();
return sheet;
}
/**
* 读取Excel2003文件
* @param filePath 文件路径
* @return
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
private List<List<String>> XLSprocess(String filePath)
throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
XLSReader xls = new XLSReader();
List<List<String>> sheet = xls.process(filePath);
return sheet;
}
/**
* 读取Excel文件内容并返回包含文件内容的List
* @param filePath 文件路径
* @return
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
public static List<List<String>> readerExcel(String filePath)
throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
// 将文件数据储存起来
List<List<String>> sheet = null;
// 获取文件后缀名，判断文件类型
int index = filePath.lastIndexOf(".");
String excelVer = filePath.substring(index, filePath.length());
ExcelEventParser csvReader = new ExcelEventParser();
if (XLS.equals(excelVer)) {
sheet = csvReader.XLSprocess(filePath);
} else if (XLSX.equals(excelVer)) {
sheet = csvReader.XLSXprocess(filePath);
}
return sheet;
}
public static void main(String[] args) {
try {
// 要导入的文件地址（例：D:/新建 Microsoft Excel 工作表 .xlsx）
String filePath = "D:/新建 Microsoft Excel 工作表 .xls";
List<List<String>> sheet = readerExcel(filePath);
for (List<String> row : sheet) {
for (String cell : row) {
System.out.print(cell + ",");
}
System.out.println();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
代码中按照惯例，将文件内容全部读出储存起来了，其实并不必要；使用时可以结合实际情况修改。
2.1、读取行数
读取Excel时常常会需要在读取之前先判断行数，虽然用户模式直接提供了获取行数的方法，但显然这里是不能使用的；下面读取行数的方法就是简单简化而来，主要考虑资源占用和速度。
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RowRecord;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;
public class MaxRowHandler {
public static final String Excel_03 = ".xls";
public static final String Excel_07 = ".xlsx";
class XLSXMaxRowHandler extends DefaultHandler {
//最大行数
private int maxRow = 0;
private int cellNum = 0;
public void endElement(String uri, String localName, String name)
throws SAXException {
if ("row".equals(name)) {
if (cellNum > 0) {
maxRow++;
}
cellNum = 0;
}
}
}
class XLSMaxRowHandler implements HSSFListener {
private POIFSFileSystem fs;
//最大行数
private int maxRow = 0;
public int process(String filePath) throws IOException {
this.fs = new POIFSFileSystem(new FileInputStream(filePath));
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
HSSFEventFactory factory = new HSSFEventFactory();
HSSFRequest request = new HSSFRequest();
//只监听RowRecord一种
request.addListener(listener, RowRecord.sid);
factory.processWorkbookEvents(request, fs);
return maxRow;
}
/**
* 处理Excel2003文件并计算行数
* @param fileName 文件路径
* @return
* @throws IOException
*/
public void processRecord(Record record) {
if (record.getSid() == RowRecord.sid) {
maxRow++;
}
}
}
public MaxRowHandler() {
}
/**
* 读取Excel2003文件最大行数
* @param filePath 文件路径
* @return
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
public int maxXLSprocess(String filePath) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
XLSMaxRowHandler xls = new XLSMaxRowHandler();
int num = xls.process(filePath);
return num;
}
/**
* 读取Excel2007文件最大行数
* @param filePath 文件路径
* @return
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
private int maxXLSXprocess(String filePath) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
//将文件以压缩包的形式读入
OPCPackage opcPackage = OPCPackage.open(filePath, PackageAccess.READ);
XSSFReader xssfReader = new XSSFReader(opcPackage);
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
InputStream stream = iter.next();
InputSource sheetSource = new InputSource(stream);
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.getXMLReader();
XLSXMaxRowHandler handler = new XLSXMaxRowHandler();
sheetParser.setContentHandler(handler);
sheetParser.parse(sheetSource);
int num = handler.maxRow;
stream.close();
opcPackage.close();
return num;
}
/**
* 获取最大行数不再区分sheet和空行等
* @param filePath 文件路径
* @return
*/
public static int getMaxRow(String filePath) {
int index = filePath.lastIndexOf(".");
String excelVer = filePath.substring(index, filePath.length());
int num = 0;
try {
MaxRowHandler csvReader = new MaxRowHandler();
if (".xls".equals(excelVer)) {
num = csvReader.maxXLSprocess(filePath);
} else if (".xlsx".equals(excelVer)) {
num = csvReader.maxXLSXprocess(filePath);
}
return num;
}catch(Exception e) {
e.printStackTrace();
}
return 0;
}
public static void main(String[] args) {
try {
// 要导入的文件地址（例：D:/新建 Microsoft Excel 工作表 .xlsx）
String filePath = "D:/新建 Microsoft Excel 工作表 .xlsx";
int max = getMaxRow(filePath);
System.out.println(max);
} catch (Exception e) {
e.printStackTrace();
}
}
}3、后记
至此POI事件模式读取Excel文件就介绍完了，但提高读取性能还远不止这些；POI的SXSSF(流用户模式)，阿里巴巴开源的easyexcel工具也是各有特点，非常值得一试。
本文代码较多，受限于作者水平，难免有缺漏之处。如果有疑问可以在评论区交流，如果对大家有所帮助就再好不过了。
