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工具也是各有特点，非常值得一试。

本文代码较多，受限于作者水平，难免有缺漏之处。如果有疑问可以在评论区交流，如果对大家有所帮助就再好不过了。