Excel-Boot是一款Excel导入导出解决方案组成的轻量级开源组件

/* * Copyright 2018 NingWei (ningww1@126.com) * <p> * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * </p> */ package com.excel.poi.excel; import com.excel.poi.common.Constant; import static com.excel.poi.common.DateFormatUtil.parse; import static com.excel.poi.common.RegexUtil.isMatch; import com.excel.poi.common.StringUtil; import static com.excel.poi.common.StringUtil.convertNullTOZERO; import com.excel.poi.entity.ErrorEntity; import com.excel.poi.entity.ExcelEntity; import com.excel.poi.entity.ExcelPropertyEntity; import com.excel.poi.exception.AllEmptyRowException; import com.excel.poi.exception.ExcelBootException; import com.excel.poi.function.ImportFunction; import java.io.IOException; import java.io.InputStream; import java.lang.reflect.Field; import java.math.BigDecimal; import java.text.ParseException; import java.util.ArrayList; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.concurrent.ExecutionException; import lombok.extern.slf4j.Slf4j; import org.apache.poi.openxml4j.exceptions.OpenXML4JException; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; 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; import org.xml.sax.helpers.XMLReaderFactory; /** * @author NingWei */ @Slf4j public class ExcelReader extends DefaultHandler { private Integer currentSheetIndex = -1; private Integer currentRowIndex = 0; private Integer excelCurrentCellIndex = 0; private ExcelCellType cellFormatStr; private String currentCellLocation; private String previousCellLocation; private String endCellLocation; private SharedStringsTable mSharedStringsTable; private String currentCellValue; private Boolean isNeedSharedStrings = false; private ExcelEntity excelMapping; private ImportFunction importFunction; private Class excelClass; private List<String> cellsOnRow = new ArrayList<String>(); private Integer beginReadRowIndex; private Integer dataCurrentCellIndex = -1; public ExcelReader(Class entityClass, ExcelEntity excelMapping, ImportFunction importFunction) { this(entityClass, excelMapping, 1, importFunction); } public ExcelReader(Class entityClass, ExcelEntity excelMapping, Integer beginReadRowIndex, ImportFunction importFunction) { this.excelClass = entityClass; this.excelMapping = excelMapping; this.beginReadRowIndex = beginReadRowIndex; this.importFunction = importFunction; } public void process(InputStream in) throws IOException, OpenXML4JException, SAXException { OPCPackage opcPackage = null; InputStream sheet = null; InputSource sheetSource; try { opcPackage = OPCPackage.open(in); XSSFReader xssfReader = new XSSFReader(opcPackage); XMLReader parser = this.fetchSheetParser(xssfReader.getSharedStringsTable()); Iterator<InputStream> sheets = xssfReader.getSheetsData(); while (sheets.hasNext()) { currentRowIndex = 0; currentSheetIndex++; try { sheet = sheets.next(); sheetSource = new InputSource(sheet); try { log.info("开始读取第{}个Sheet!", currentSheetIndex + 1); parser.parse(sheetSource); } catch (AllEmptyRowException e) { log.warn(e.getMessage()); } catch (Exception e) { throw new ExcelBootException(e, "第{}个Sheet,第{}行,第{}列,系统发生异常! ", currentSheetIndex + 1, currentRowIndex + 1, dataCurrentCellIndex + 1); } } finally { if (sheet != null) { sheet.close(); } } } } finally { if (opcPackage != null) { opcPackage.close(); } } } /** * 获取sharedStrings.xml文件的XMLReader对象 * * @param sst * @return * @throws SAXException */ private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException { XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser"); this.mSharedStringsTable = sst; parser.setContentHandler(this); return parser; } /** * 开始读取一个标签元素 * * @param uri * @param localName * @param name * @param attributes */ @Override public void startElement(String uri, String localName, String name, Attributes attributes) { if (Constant.CELL.equals(name)) { String xyzLocation = attributes.getValue(Constant.XYZ_LOCATION); previousCellLocation = null == previousCellLocation ? xyzLocation : currentCellLocation; currentCellLocation = xyzLocation; String cellType = attributes.getValue(Constant.CELL_T_PROPERTY); isNeedSharedStrings = (null != cellType && cellType.equals(Constant.CELL_S_VALUE)); setCellType(cellType); } currentCellValue = ""; } /** * 加载v标签中间的值 * * @param chars * @param start * @param length */ @Override public void characters(char[] chars, int start, int length) { currentCellValue = currentCellValue.concat(new String(chars, start, length)); } /** * 结束读取一个标签元素 * * @param uri * @param localName * @param name * @throws SAXException */ @Override public void endElement(String uri, String localName, String name) { if (Constant.CELL.equals(name)) { if (isNeedSharedStrings && !StringUtil.isBlank(currentCellValue) && StringUtil.isNumeric(currentCellValue)) { int index = Integer.parseInt(currentCellValue); currentCellValue = new XSSFRichTextString(mSharedStringsTable.getEntryAt(index)).toString(); } if (!currentCellLocation.equals(previousCellLocation) && currentRowIndex != 0) { for (int i = 0; i < countNullCell(currentCellLocation, previousCellLocation); i++) { cellsOnRow.add(excelCurrentCellIndex, ""); excelCurrentCellIndex++; } } if (currentRowIndex != 0 || !"".equals(currentCellValue.trim())) { String value = this.getCellValue(currentCellValue.trim()); cellsOnRow.add(excelCurrentCellIndex, value); excelCurrentCellIndex++; } } else if (Constant.ROW.equals(name)) { if (currentRowIndex == 0) { endCellLocation = currentCellLocation; int propertySize = excelMapping.getPropertyList().size(); if (cellsOnRow.size() != propertySize) { throw new ExcelBootException("Excel有效列数不等于标注注解的属性数�