easyexcel解析excel文件原理解析

easyexcel是对poi基于事件机制解析excel文件的封装，poi又对sax解析xml文件进行了封装，所以要理解easyexcel解析的过程，就要先从sax开始。

• sax解析xml文件
  使用sax解析xml文件时只要注册ContentHandler就行，sax是按行解析的，解析时会调用ContentHandler接口的钩子函数。

        SAXParserFactory factory = SAXParserFactory.newInstance();
        SAXParser saxParser = factory.newSAXParser();
        XMLReader xmlReader = saxParser.getXMLReader();
        xmlReader.setContentHandler(new DefaultHandler(){
            @Override
            public void startElement(String uri, String localName, String qName, Attributes attributes){
                log.info("startElement-->>uri:{},localName:{},qName:{}",uri, localName,qName);
            }
            @Override
            public void endElement(String uri, String localName, String qName){
                log.info("endElement-->>uri:{},localName:{},qName:{}",uri, localName,qName);
            }
            @Override
            public void characters(char[] ch, int start, int length){
                log.info("characters-->>ch:{}, start:{}, length:{}", new String(ch,start,length), start, length);
            }
        });
        InputStream inputStream = new FileInputStream(Var.xmlFile);
        xmlReader.parse(new InputSource(inputStream));
        inputStream.close();

• poi解析excel文件
  poi基于事件解析excel文件是对sax技术的封装，打开excel文件后，获取到所有sheet页，然后再类似xml逐行解析。

        OPCPackage opcPackage = OPCPackage.open(Var.excelFile, PackageAccess.READ);
        XSSFReader reader = new XSSFReader(opcPackage);
        XMLReader xmlReader = XMLReaderFactory.createXMLReader();
        xmlReader.setContentHandler(new SheetHandler());
        Iterator<InputStream> sheetsData = reader.getSheetsData();
        while (sheetsData.hasNext()){
            InputStream next = sheetsData.next();
            InputSource inputSource = new InputSource(next);
            xmlReader.parse(inputSource);
            next.close();
        }

在poi解析大excel文件的基础上出现了easyexcel，先看下easyexcel的用法，真的很easy啊，调静态方法read，传文件路径就行，获取到每行数据时，会帮我们封装成实体类，我们在invoke中进行处理即可。

EasyExcel.read(Var.excelFile, WfqData.class,
                    new AnalysisEventListener<WfqData>() {
                        @Override
                        public void invoke(WfqData data, AnalysisContext analysisContext) {
                            // 按行读文件
                        }

                        @Override
                        public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                            log.info("处理文件结束");
                        }
                    }).headRowNumber(2)// 表头第二行，第一行是免责申明
                    .doReadAll();

接下来开始看easyexcel的源码。read()方法中会构造ReadWorkbook对象，然后设置excel文件的File对象，注册读监听器。

    public static ExcelReaderBuilder read(String pathName, Class head, ReadListener readListener) {
        ExcelReaderBuilder excelReaderBuilder = new ExcelReaderBuilder();
        excelReaderBuilder.file(pathName);
        if (head != null) {
            excelReaderBuilder.head(head);
        }
        if (readListener != null) {
            excelReaderBuilder.registerReadListener(readListener);
        }
        return excelReaderBuilder;
    }

然后构造ExcelReader对象用来读取excel文件。

    public void doReadAll() {
        ExcelReader excelReader = build();
        excelReader.readAll();
        excelReader.finish();
    }

接下来根据excel文件类型确定解析器，

private void choiceExcelExecutor(ReadWorkbook readWorkbook) throws Exception {
        ExcelTypeEnum excelType = ExcelTypeEnum.valueOf(readWorkbook);
        switch (excelType) {
            case XLS:
                ......
                break;
            case XLSX:
                XlsxReadContext xlsxReadContext = new DefaultXlsxReadContext(readWorkbook, ExcelTypeEnum.XLSX);
                analysisContext = xlsxReadContext;
                excelReadExecutor = new XlsxSaxAnalyser(xlsxReadContext, null);
                break;
            default:
                break;
        }
    }

然后设置一堆的配置，在解析的时候会用到，这些配置都保存在DefaultXlsxReadContext中，使用默认的解析事件处理器DefaultAnalysisEventProcessor，在解析excel的过程中会通过这个事件处理器调用我们注册进来的监听器方法。

    public AnalysisContextImpl(ReadWorkbook readWorkbook, ExcelTypeEnum actualExcelType) {
        if (readWorkbook == null) {
            throw new IllegalArgumentException("Workbook argument cannot be null");
        }
        switch (actualExcelType) {
            case XLS:
                readWorkbookHolder = new XlsReadWorkbookHolder(readWorkbook);
                break;
            case XLSX:
                readWorkbookHolder = new XlsxReadWorkbookHolder(readWorkbook);
                break;
            default:
                break;
        }
        currentReadHolder = readWorkbookHolder;
        analysisEventProcessor = new DefaultAnalysisEventProcessor();
        if (LOGGER.isDebugEnabled()) {
            LOGGER.debug("Initialization 'AnalysisContextImpl' complete");
        }
    }

解析前最重要的一环就是打开excel文档OPCPackage.open(xlsxReadWorkbookHolder.getFile());，这里就是我们熟悉的poi解析excel文件做的事情，然后获取每个要解析的sheet页，使用ReadSheet包装。

    public XlsxSaxAnalyser(XlsxReadContext xlsxReadContext, InputStream decryptedStream) throws Exception {
        this.xlsxReadContext = xlsxReadContext;
        // Initialize cache
        XlsxReadWorkbookHolder xlsxReadWorkbookHolder = xlsxReadContext.xlsxReadWorkbookHolder();

        OPCPackage pkg = readOpcPackage(xlsxReadWorkbookHolder, decryptedStream);
        xlsxReadWorkbookHolder.setOpcPackage(pkg);

        ArrayList<PackagePart> packageParts = pkg.getPartsByContentType(XSSFRelation.SHARED_STRINGS.getContentType());

        if (!CollectionUtils.isEmpty(packageParts)) {
            PackagePart sharedStringsTablePackagePart = packageParts.get(0);

            // Specify default cache
            defaultReadCache(xlsxReadWorkbookHolder, sharedStringsTablePackagePart);

            // Analysis sharedStringsTable.xml
            analysisSharedStringsTable(sharedStringsTablePackagePart.getInputStream(), xlsxReadWorkbookHolder);
        }

        XSSFReader xssfReader = new XSSFReader(pkg);
        analysisUse1904WindowDate(xssfReader, xlsxReadWorkbookHolder);

        xlsxReadWorkbookHolder.setStylesTable(xssfReader.getStylesTable());
        sheetList = new ArrayList<ReadSheet>();
        sheetMap = new HashMap<Integer, InputStream>();
        commentsTableMap = new HashMap<Integer, CommentsTable>();
        XSSFReader.SheetIterator ite = (XSSFReader.SheetIterator)xssfReader.getSheetsData();
        int index = 0;
        if (!ite.hasNext()) {
            throw new ExcelAnalysisException("Can not find any sheet!");
        }
        while (ite.hasNext()) {
            InputStream inputStream = ite.next();
            sheetList.add(new ReadSheet(index, ite.getSheetName()));
            sheetMap.put(index, inputStream);
            if (xlsxReadContext.readWorkbookHolder().getExtraReadSet().contains(CellExtraTypeEnum.COMMENT)) {
                CommentsTable commentsTable = ite.getSheetComments();
                if (null != commentsTable) {
                    commentsTableMap.put(index, commentsTable);
                }
            }
            index++;
        }
    }

解析是在XlsxSaxAnalyser#execute方法中进行的，依次遍历获取的每个sheet页，保存下来后进行解析，就是poi解析excel文件的过程了。

    public void execute() {
        for (ReadSheet readSheet : sheetList) {
            readSheet = SheetUtils.match(readSheet, xlsxReadContext);
            if (readSheet != null) {
                xlsxReadContext.currentSheet(readSheet);
                parseXmlSource(sheetMap.get(readSheet.getSheetNo()), new XlsxRowHandler(xlsxReadContext));
                // Read comments
                readComments(readSheet);
                // The last sheet is read
                xlsxReadContext.analysisEventProcessor().endSheet(xlsxReadContext);
            }
        }
    }

        private void parseXmlSource(InputStream inputStream, ContentHandler handler) {
        InputSource inputSource = new InputSource(inputStream);
        try {
            SAXParserFactory saxFactory;
            String xlsxSAXParserFactoryName = xlsxReadContext.xlsxReadWorkbookHolder().getSaxParserFactoryName();
            if (StringUtils.isEmpty(xlsxSAXParserFactoryName)) {
                saxFactory = SAXParserFactory.newInstance();
            } else {
                saxFactory = SAXParserFactory.newInstance(xlsxSAXParserFactoryName, null);
            }
            ...
            SAXParser saxParser = saxFactory.newSAXParser();
            XMLReader xmlReader = saxParser.getXMLReader();
            xmlReader.setContentHandler(handler);
            xmlReader.parse(inputSource);
            inputStream.close();
        }
    }

每次解析时都会回调XlsxRowHandler中的startElement、characters、endElement方法。
RowTagHandler#startElement()方法中会先获取当前要解析的行，然后与之前解析到的行进行对比，防止遗漏。

    public void startElement(XlsxReadContext xlsxReadContext, String name, Attributes attributes) {
        XlsxReadSheetHolder xlsxReadSheetHolder = xlsxReadContext.xlsxReadSheetHolder();
        int rowIndex = PositionUtils.getRowByRowTagt(attributes.getValue(ExcelXmlConstants.ATTRIBUTE_R),
            xlsxReadSheetHolder.getRowIndex());
        Integer lastRowIndex = xlsxReadContext.readSheetHolder().getRowIndex();
        while (lastRowIndex + 1 < rowIndex) {
            xlsxReadContext.readRowHolder(new ReadRowHolder(lastRowIndex + 1, RowTypeEnum.EMPTY,
                xlsxReadSheetHolder.getGlobalConfiguration(), new LinkedHashMap<Integer, Cell>()));
            xlsxReadContext.analysisEventProcessor().endRow(xlsxReadContext);
            xlsxReadSheetHolder.setColumnIndex(null);
            xlsxReadSheetHolder.setCellMap(new LinkedHashMap<Integer, Cell>());
            lastRowIndex++;
        }
        xlsxReadSheetHolder.setRowIndex(rowIndex);
    }

解析完一行后，在AbstractCellValueTagHandler#endElement中会将当前行的列数和对应的值保存到cellMap(private Map<Integer, Cell> cellMap;)中。

public void endElement(XlsxReadContext xlsxReadContext, String name) {
        XlsxReadSheetHolder xlsxReadSheetHolder = xlsxReadContext.xlsxReadSheetHolder();
        CellData tempCellData = xlsxReadSheetHolder.getTempCellData();
        StringBuilder tempData = xlsxReadSheetHolder.getTempData();
        CellDataTypeEnum oldType = tempCellData.getType();
        switch (oldType) {
            case DIRECT_STRING:
            case STRING:
            case ERROR:
                tempCellData.setStringValue(tempData.toString());
                break;
            case BOOLEAN:
                tempCellData.setBooleanValue(BooleanUtils.valueOf(tempData.toString()));
                break;
            case NUMBER:
            case EMPTY:
                tempCellData.setType(CellDataTypeEnum.NUMBER);
                tempCellData.setNumberValue(new BigDecimal(tempData.toString()));
                break;
            default:
                throw new IllegalStateException("Cannot set values now");
        }

        // set string value
        setStringValue(xlsxReadContext);

        if (tempCellData.getStringValue() != null
            && xlsxReadContext.currentReadHolder().globalConfiguration().getAutoTrim()) {
            tempCellData.setStringValue(tempCellData.getStringValue());
        }

        tempCellData.checkEmpty();
        xlsxReadSheetHolder.getCellMap().put(xlsxReadSheetHolder.getColumnIndex(), tempCellData);
    }

然后在RowTagHandler#endElement中调用xlsxReadContext.analysisEventProcessor().endRow(xlsxReadContext);方法进行处理，这里会调用DefaultAnalysisEventProcessor#dealData处理每行的实际数据，其实就是调用读监听器的invoke方法，也就是我们在EasyExcel.read中传的第三个参数。

    private void dealData(AnalysisContext analysisContext) {
        ReadRowHolder readRowHolder = analysisContext.readRowHolder();
        Map<Integer, CellData> cellDataMap = (Map)readRowHolder.getCellMap();
        readRowHolder.setCurrentRowAnalysisResult(cellDataMap);
        int rowIndex = readRowHolder.getRowIndex();
        int currentHeadRowNumber = analysisContext.readSheetHolder().getHeadRowNumber();

        boolean isData = rowIndex >= currentHeadRowNumber;

        // Last head column
        if (!isData && currentHeadRowNumber == rowIndex + 1) {
            buildHead(analysisContext, cellDataMap);
        }
        // Now is data
        for (ReadListener readListener : analysisContext.currentReadHolder().readListenerList()) {
            try {
                if (isData) {
                    readListener.invoke(readRowHolder.getCurrentRowAnalysisResult(), analysisContext);
                } else {
                    readListener.invokeHead(cellDataMap, analysisContext);
                }
            } catch (Exception e) {
                onException(analysisContext, e);
                break;
            }
            if (!readListener.hasNext(analysisContext)) {
                throw new ExcelAnalysisStopException();
            }
        }
    }

完成一个sheet页的解析，就会调用每个监听的doAfterAllAnalysed()方法。

    public void endSheet(AnalysisContext analysisContext) {
        for (ReadListener readListener : analysisContext.currentReadHolder().readListenerList()) {
            readListener.doAfterAllAnalysed(analysisContext);
        }
    }

至此完成了对excel文件的解析。
总结下，easyexcel解析excel文件，先初始化用来解析excel文件用到的参数，比如文件路径、表头行、行数据class等，然后使用poi基于事件的机制进行解析。获取到数据后会把列索引和cell数据保存到map，然后调用每个监听器的invoke方法进行处理。有不对的地方请大神指出，欢迎大家一起讨论交流，共同进步。