POI导入导出百万级数据Excel

　　一、导入pom依赖4.0.0      org.apache.poi     poi     ${poi-version}        org.apache.poi     poi-ooxml     ${poi-version}        org.apache.poi     poi-scratchpad     ${poi-version}        org.apache.poi     poi-ooxml-schemas     ${poi-version} 
　　二、导入
　　1、导入工具类import com.base.infrastructure.exception.ServiceException; import com.base.infrastructure.utility.MapUtility; import com.base.infrastructure.utility.ServiceCheckUtility; import com.base.infrastructure.utility.StringUtility; import com.base.infrastructure.utility.excel.analysis.ExcelXlsReader; import com.base.infrastructure.utility.excel.analysis.ExcelXlsxReader; import org.springframework.web.multipart.MultipartFile;  import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map;  /**  * excel2003、2007大数据量解析  */ public class BigExcelReaderUtil {      //excel2003扩展名     private static final String XLS_LOWER_CASE = ＂xls＂;      private static final String XLSX_LOWER_CASE = ＂xlsx＂;      //excel2007扩展名     private static final String XLS_UPPER_CASE = ＂XLS＂;      private static final String XLSX_UPPER_CASE = ＂XLSX＂;      /**      * 根据文件路径解析      *      * @param path      * @return      * @throws Exception      */     public static Map readExcelPath(String path, int headerIncex, int dataIndex)             throws Exception {         //解析数据返回map         Map map = new HashMap<>();         //表头内容         List header = new ArrayList<>();         //数据内容         //List> data = new ArrayList<>();         Map>> listMap = new HashMap<>();          //处理excel2003文件         if (path.endsWith(XLS_LOWER_CASE) || path.endsWith(XLS_UPPER_CASE)) {             ExcelXlsReader excelXls = new ExcelXlsReader() {                 @Override                 public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {                     //ServiceCheckUtility.errorCheck(sheetIndex > 1, ＂请删除或清除多余的sheet页，只保留一个正确的sheet页＂);                     header.addAll(cellList);                 }                  @Override                 public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {                     List tempList = new ArrayList<>();                     tempList.addAll(cellList);                     if (listMap.get(sheetIndex) == null) {                         List> data = new ArrayList<>();                         data.add(tempList);                         listMap.put(sheetIndex, data);                     } else {                         listMap.get(sheetIndex).add(tempList);                     }                 }             };             excelXls.process(path, headerIncex, dataIndex);         }         //处理excel2007文件         else if (path.endsWith(XLSX_LOWER_CASE) || path.endsWith(XLSX_UPPER_CASE)) {             ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader() {                 @Override                 public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {                     //ServiceCheckUtility.errorCheck(sheetIndex > 1, ＂请删除或清除多余的sheet页，只保留一个正确的sheet页＂);                     header.addAll(cellList);                 }                  @Override                 public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {                     List tempList = new ArrayList<>();                     tempList.addAll(cellList);                     if (listMap.get(sheetIndex) == null) {                         List> data = new ArrayList<>();                         data.add(tempList);                         listMap.put(sheetIndex, data);                     } else {                         listMap.get(sheetIndex).add(tempList);                     }                 }             };             excelXlsxReader.process(path, headerIncex, dataIndex);         } else {             throw new Exception(＂文件格式错误，上传的excel的扩展名只能是xls或xlsx。＂);         }         map.put(＂data＂, listMap);         map.put(＂head＂, header);         return map;     }      /**      * 根据MultipartFile文件对象解析      *      * @param file      * @return      * @throws Exception      */     public static Map readExcelFile(MultipartFile file, int headerIncex, int dataIndex)             throws Exception {          //文件判空         if (file == null) {             return new HashMap<>();         }          //获取后缀名判断是否为excel         String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(＂.＂) + 1);         if (isNotExcelPath(suffix)) {             return new HashMap<>();         }          //解析数据返回map         Map map = new HashMap<>();         //表头内容         List header = new ArrayList<>();         //数据内容         List> data = new ArrayList<>();         InputStream inputStream = null;         try {             inputStream = file.getInputStream();             if (suffix.endsWith(XLS_LOWER_CASE) || suffix.endsWith(XLS_UPPER_CASE)) {                 //处理excel2003文件                 ExcelXlsReader excelXls = new ExcelXlsReader() {                     @Override                     public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow,                                                List cellList) {                         ServiceCheckUtility.errorCheck(sheetIndex > 1, ＂请删除或清除多余的sheet页，只保留一个正确的sheet页＂);                         header.addAll(cellList);                     }                      @Override                     public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow,                                              List cellList) {                         List tempList = new ArrayList<>();                         tempList.addAll(cellList);                         data.add(tempList);                     }                 };                 excelXls.process(inputStream, headerIncex, dataIndex);             } else if (suffix.endsWith(XLSX_LOWER_CASE) || suffix.endsWith(XLSX_UPPER_CASE)) {                 //处理excel2007文件                 ExcelXlsxReader excelXlsx = new ExcelXlsxReader() {                     @Override                     public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow,                                                List cellList) {                         ServiceCheckUtility.errorCheck(sheetIndex > 1, ＂请删除或清除多余的sheet页，只保留一个正确的sheet页＂);                         header.addAll(cellList);                     }                      @Override                     public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow,                                              List cellList) {                         List tempList = new ArrayList<>();                         tempList.addAll(cellList);                         data.add(tempList);                     }                 };                 excelXlsx.process(inputStream, headerIncex, dataIndex);             } else {                 throw new ServiceException(＂文件格式错误，上传的excel的扩展名只能是xls或xlsx。＂);             }         } finally {             if (inputStream != null) {                 inputStream.close();             }         }         map.put(＂data＂, data);         map.put(＂head＂, header);         return map;      }      /**      * excel路劲检查      *      * @param path      * @return      */     private static boolean isNotExcelPath(String path) {         if (StringUtility.isEmpty(path)) {             return true;         }         return !path.endsWith(XLS_LOWER_CASE) && !path.endsWith(XLSX_LOWER_CASE) && !path.endsWith(XLS_UPPER_CASE)                 && !path.endsWith(XLSX_UPPER_CASE);     } }
　　2、2003版本解析import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.List;  import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder; 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.HSSFDataFormatter; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.poifs.filesystem.POIFSFileSystem;  /**  * 用于解决.xls2003版本大数据量问题  */ public class ExcelXlsReader implements HSSFListener {          private int minColums = -1;          private POIFSFileSystem fs;          /**      * 总行数      */     private int totalRows = 0;          /**      * 上一行row的序号      */     private int lastRowNumber;          /**      * 上一单元格的序号      */     private int lastColumnNumber;          /**      * 是否输出formula，还是它对应的值      */     private boolean outputFormulaValues = true;          /**      * 用于转换formulas      */     private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener;          //excel2003工作簿     private HSSFWorkbook stubWorkbook;          private SSTRecord sstRecord;          private FormatTrackingHSSFListener formatListener;          private final HSSFDataFormatter formatter = new HSSFDataFormatter();          /**      * 文件的绝对路径      */     private String filePath = ＂＂;          //表索引     private int sheetIndex = 0;          private BoundSheetRecord[] orderedBSRs;          @SuppressWarnings({＂rawtypes＂})     private ArrayList boundSheetRecords = new ArrayList();          private int nextRow;          private int nextColumn;          private boolean outputNextStringRecord;          //当前行     private int curRow = 0;          //存储一行记录所有单元格的容器     private List cellList = new ArrayList();          /**      * 判断整行是否为空行的标记      */     private boolean flag = false;          //定义列名的行索引     private int headerIncex = 1;          //定义数据的行索引     private int dataIndex = 2;          private String sheetName;          /**      * 根据文件流解析      *       * @param inputStream      * @param headerIncex      * @param dataIndex      * @return      * @throws Exception      */     public int process(InputStream inputStream, int headerIncex, int dataIndex)         throws Exception {         this.fs = new POIFSFileSystem(inputStream);         return init(headerIncex, dataIndex);     }          /**      * 根据文件路径解析      * @param path      * @param headerIncex      * @param dataIndex      * @return      * @throws Exception      */     public int process(String path, int headerIncex, int dataIndex)         throws Exception {         this.fs = new POIFSFileSystem(new FileInputStream(path));         return init(headerIncex, dataIndex);     }          /**      * 遍历excel下所有的sheet      *       * @param headerIncex      * @param dataIndex      * @return      * @throws Exception      */     private int init(int headerIncex, int dataIndex)         throws Exception {         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 EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);             request.addListenerForAllRecords(workbookBuildingListener);         }         if (headerIncex > 0)             //设置表头行             this.headerIncex = headerIncex;         if (dataIndex > 0)             //设置数据行             this.dataIndex = dataIndex;         factory.processWorkbookEvents(request, fs);         //返回该excel文件的总行数，不包括首列和空行         return totalRows;     }          /**      * HSSFListener 监听方法，处理Record      * 处理每个单元格      */     @SuppressWarnings(＂unchecked＂)     public void processRecord(Record record) {         int thisRow = -1;         int thisColumn = -1;         String thisStr = null;         String value = null;                  switch (record.getSid()) {             case BoundSheetRecord.sid:                 boundSheetRecords.add(record);                 break;             case BOFRecord.sid:                 //开始处理每个sheet                 BOFRecord br = (BOFRecord)record;                 if (br.getType() == BOFRecord.TYPE_WORKSHEET) {                     //如果有需要，则建立子工作簿                     if (workbookBuildingListener != null && stubWorkbook == null) {                         stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();                     }                                          if (orderedBSRs == null) {                         orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);                     }                     sheetName = orderedBSRs[sheetIndex].getSheetname();                     sheetIndex++;                 }                 break;             case SSTRecord.sid:                 sstRecord = (SSTRecord)record;                 break;             case BlankRecord.sid:                 //单元格为空白                 BlankRecord brec = (BlankRecord)record;                 thisRow = brec.getRow();                 thisColumn = brec.getColumn();                 thisStr = ＂＂;                 cellList.add(thisColumn, thisStr);                 break;             case BoolErrRecord.sid:                 //单元格为布尔类型                 BoolErrRecord berec = (BoolErrRecord)record;                 thisRow = berec.getRow();                 thisColumn = berec.getColumn();                 thisStr = berec.getBooleanValue() + ＂＂;                 cellList.add(thisColumn, thisStr);                 //如果里面某个单元格含有值，则标识该行不为空行                 checkRowIsNull(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 = ＂＂＂ + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + ＂＂＂;                     }                 }                 else {                     thisStr = ＂＂＂ + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + ＂＂＂;                 }                 cellList.add(thisColumn, thisStr);                 //如果里面某个单元格含有值，则标识该行不为空行                 checkRowIsNull(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(＂＂) ? ＂＂ : value;                 cellList.add(thisColumn, value.replaceAll(＂ ＂, ＂＂));                 //如果里面某个单元格含有值，则标识该行不为空行                 checkRowIsNull(value);                 break;             case LabelSSTRecord.sid:                 //单元格为字符串类型                 LabelSSTRecord lsrec = (LabelSSTRecord)record;                 curRow = thisRow = lsrec.getRow();                 thisColumn = lsrec.getColumn();                 if (sstRecord == null) {                     cellList.add(thisColumn, ＂＂);                 }                 else {                     value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();                     value = value.equals(＂＂) ? ＂＂ : value;                     cellList.add(thisColumn, value.replaceAll(＂ ＂, ＂＂));                     //如果里面某个单元格含有值，则标识该行不为空行                     checkRowIsNull(value);                 }                 break;             case NumberRecord.sid:                 //单元格为数字类型                 NumberRecord numrec = (NumberRecord)record;                 curRow = thisRow = numrec.getRow();                 thisColumn = numrec.getColumn();                                  //第一种方式 这个被写死，采用的m/d/yy h:mm格式，不符合要求                 //value = formatListener.formatNumberDateCell(numrec).trim();                                  //第二种方式，参照formatNumberDateCell里面的实现方法编写                 Double valueDouble = ((NumberRecord)numrec).getValue();                 String formatString = formatListener.getFormatString(numrec);                 /*if (formatString.contains(＂m/d/yy＂)) {                     formatString = ＂yyyy-MM-dd hh:mm:ss＂;                 }*/                 int formatIndex = formatListener.getFormatIndex(numrec);                 value = formatter.formatRawCellContents(valueDouble, formatIndex, formatString).trim();                                  value = value.equals(＂＂) ? ＂＂ : value;                 //向容器加入列值                 cellList.add(thisColumn, value.replaceAll(＂ ＂, ＂＂));                 //如果里面某个单元格含有值，则标识该行不为空行                 checkRowIsNull(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();             cellList.add(thisColumn, ＂＂);         }                  //更新行和列的值         if (thisRow > -1)             lastRowNumber = thisRow;         if (thisColumn > -1)             lastColumnNumber = thisColumn;                  //行结束时的操作         if (record instanceof LastCellOfRowDummyRecord) {             if (minColums > 0) {                 //列值重新置空                 if (lastColumnNumber == -1) {                     lastColumnNumber = 0;                 }             }             lastColumnNumber = -1;                          if (curRow == headerIncex - 1) {                 //表头行结束时，调用sendHeaderRows()方法                 sendHeaderRows(filePath, sheetName, sheetIndex, curRow + 1, cellList);             }                          //该行不为空行且该行为数据行             if (flag && curRow >= dataIndex - 1) {                 //每行数据结束时，调用sendDataRows()方法                 sendDataRows(filePath, sheetName, sheetIndex, curRow + 1, cellList);                 totalRows++;             }             //清空容器             cellList.clear();             flag = false;         }     }          /**      * 如果里面某个单元格含有值，则标识该行不为空行      * @param value      */     public void checkRowIsNull(String value) {         if (value != null && !＂＂.equals(value)) {             flag = true;         }     }          /**      * 表头数据      * @param filePath      * @param sheetName      * @param sheetIndex      * @param curRow      * @param cellList      */     public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {              }          /**      * 解析数据      * @param filePath      * @param sheetName      * @param sheetIndex      * @param curRow      * @param cellList      */     public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {              } }
　　3、2007及以上版本import java.io.InputStream; import java.util.ArrayList; import java.util.List;  import com.base.infrastructure.log.LoggerManager; import org.apache.poi.openxml4j.opc.OPCPackage; import org.apache.poi.ss.usermodel.BuiltinFormats; import org.apache.poi.ss.usermodel.DataFormatter; import org.apache.poi.xssf.eventusermodel.XSSFReader; import org.apache.poi.xssf.model.SharedStringsTable; 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; import org.xml.sax.helpers.XMLReaderFactory;  /**  * 2007版本  */ public class ExcelXlsxReader extends DefaultHandler {      /**      * 单元格中的数据可能的数据类型      */     enum CellDataType {         BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL     }      /**      * 共享字符串表      */     private SharedStringsTable sst;      /**      * 上一次的索引值      */     private String lastIndex;      /**      * 文件的绝对路径      */     private String filePath = ＂＂;      /**      * 工作表索引      */     private int sheetIndex = 0;      /**      * sheet名      */     private String sheetName = ＂＂;      /**      * 总行数      */     private int totalRows = 0;      /**      * 一行内cell集合      */     private List cellList = new ArrayList();      /**      * 判断整行是否为空行的标记      */     private boolean flag = false;      /**      * 当前行      */     private int curRow = 1;      /**      * 前一个列      */     private int lastCurCol = -1;      /**      * 当前列      */     private int curCol = 0;      //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格     private int maxcurCol = 0;      // 定义当前读到的列与上一次读到的列中是否有空值（即该单元格什么也没有输入，连空格都不存在）默认为false     private boolean curflag = false;      /**      * T元素标识      */     private boolean isTElement;      /**      * 异常信息，如果为空则表示没有异常      */     private String exceptionMessage;      /**      * 单元格数据类型，默认为字符串类型      */     private CellDataType nextDataType = CellDataType.SSTINDEX;      private final DataFormatter formatter = new DataFormatter();      /**      * 单元格日期格式的索引      */     private short formatIndex;      /**      * 日期格式字符串      */     private String formatString;      //定义列名的行索引     private int headerIncex = 1;      //定义数据的行索引     private int dataIndex = 2;      /**      * 单元格      */     private StylesTable stylesTable;      /**      * 根据文件路径解析      *      * @param path      * @param headerIncex      * @param dataIndex      * @return      * @throws Exception      */     public int process(String path, int headerIncex, int dataIndex)             throws Exception {         OPCPackage pkg = OPCPackage.open(path);         return init(pkg, headerIncex, dataIndex);     }      /**      * 根据文件流解析（占用内存消耗比文件路径解析大）      *      * @param inputStream      * @param headerIncex      * @param dataIndex      * @return      * @throws Exception      */     public int process(InputStream inputStream, int headerIncex, int dataIndex)             throws Exception {         OPCPackage pkg = OPCPackage.open(inputStream);         return init(pkg, headerIncex, dataIndex);     }      /**      * 遍历工作簿中所有的电子表格      * 并缓存在mySheetList中      *      * @param pkg      * @param headerIncex      * @param dataIndex      * @return      * @throws Exception      */     private int init(OPCPackage pkg, int headerIncex, int dataIndex)             throws Exception {         XSSFReader xssfReader = new XSSFReader(pkg);         stylesTable = xssfReader.getStylesTable();         SharedStringsTable sst = xssfReader.getSharedStringsTable();         XMLReader parser = XMLReaderFactory.createXMLReader(＂org.apache.xerces.parsers.SAXParser＂);         this.sst = sst;         parser.setContentHandler(this);         XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();         while (sheets.hasNext()) { //遍历sheet             curRow = 1; //标记初始行为第一行             if (headerIncex > 0)                 this.headerIncex = headerIncex; //设置表头行             if (dataIndex > 0)                 this.dataIndex = dataIndex; //设置数据行             sheetIndex++;             InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错             sheetName = sheets.getSheetName();             InputSource sheetSource = new InputSource(sheet);             parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行             sheet.close();         }         pkg.close();         return totalRows; //返回该excel文件的总行数，不包括首列和空行     }      /**      * 第一个执行      *      * @param uri      * @param localName      * @param name      * @param attributes      * @throws SAXException      */     @Override     public void startElement(String uri, String localName, String name, Attributes attributes)             throws SAXException {         //c => 单元格         if (＂c＂.equals(name)) {             //当前单元格的位置             String r = attributes.getValue(＂r＂);             int firstDigit = -1;             for (int c = 0; c < r.length(); ++c) {                 if (Character.isDigit(r.charAt(c))) {                     firstDigit = c;                     break;                 }             }             curCol = nameToColumn(r.substring(0, firstDigit));//获取当前读取的列数             //设定单元格类型             this.setNextDataType(attributes);         }          //当元素为t时         if (＂t＂.equals(name)) {             isTElement = true;         } else {             isTElement = false;         }          //置空         lastIndex = ＂＂;     }      /**      * 第二个执行      * 得到单元格对应的索引值或是内容值      * 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值      * 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值      *      * @param ch      * @param start      * @param length      * @throws SAXException      */     @Override     public void characters(char[] ch, int start, int length)             throws SAXException {         lastIndex += new String(ch, start, length);     }      /**      * 第三个执行      *      * @param uri      * @param localName      * @param name      * @throws SAXException      */     @Override     public void endElement(String uri, String localName, String name)             throws SAXException {          //t元素也包含字符串         if (isTElement) {//这个程序没经过             //将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符             String value = lastIndex.trim();             cellList.add(curCol, value.replaceAll(＂ ＂, ＂＂));             isTElement = false;             //如果里面某个单元格含有值，则标识该行不为空行             if (value != null && !＂＂.equals(value)) {                 flag = true;             }         } else if (＂v＂.equals(name)) {             //v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引             String value = this.getDataValue(lastIndex.trim(), ＂＂);//根据索引值获取对应的单元格值             //补全单元格之间的空单元格             if (curCol - lastCurCol > 1) {                 curflag = true;             }             for (int i = lastCurCol; i < curCol; ++i) {                 if (curflag && i > lastCurCol) {                     cellList.add(i, ＂＂);                 }             }             if (curCol > -1) {                 lastCurCol = curCol;             }             cellList.add(curCol, value.replaceAll(＂ ＂, ＂＂));              //如果里面某个单元格含有值，则标识该行不为空行             if (value != null && !＂＂.equals(value.trim())) {                 flag = true;             }         } else {             //如果标签名称为row，这说明已到行尾，调用optRows()方法             if (＂row＂.equals(name)) {                 //默认第一行为表头，以该行单元格数目为最大数目                 if (curRow == headerIncex) {                     sendHeaderRows(filePath, sheetName, sheetIndex, curRow, cellList);                     maxcurCol = cellList.size() - 1;                 }                 //补全一行尾部可能缺失的单元格                 if (maxcurCol > lastCurCol) {                     curflag = true;                 }                  for (int i = lastCurCol; i < maxcurCol; ++i) {                     if (curflag && flag) {                         cellList.add(i, ＂＂);                     }                 }                  if (flag && curRow >= dataIndex) { //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）                     sendDataRows(filePath, sheetName, sheetIndex, curRow, cellList);                     totalRows++;                 }                  cellList.clear();                 curRow++;                 curCol = 0;                 lastCurCol = -1;                 flag = false;             }         }     }      /**      * 处理数据类型      *      * @param attributes      */     public void setNextDataType(Attributes attributes) {         nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字         formatIndex = -1;         formatString = null;         String cellType = attributes.getValue(＂t＂); //单元格类型         String cellStyleStr = attributes.getValue(＂s＂); //         //String columnData = attributes.getValue(＂r＂); //获取单元格的位置，如A1,B1          if (＂b＂.equals(cellType)) { //处理布尔值             nextDataType = CellDataType.BOOL;         } else if (＂e＂.equals(cellType)) { //处理错误             nextDataType = CellDataType.ERROR;         } else if (＂inlineStr＂.equals(cellType)) {             nextDataType = CellDataType.INLINESTR;         } else if (＂s＂.equals(cellType)) { //处理字符串             nextDataType = CellDataType.SSTINDEX;         } else if (＂str＂.equals(cellType)) {             nextDataType = CellDataType.FORMULA;         }          if (cellStyleStr != null) { //处理日期             int styleIndex = Integer.parseInt(cellStyleStr);             XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);             formatIndex = style.getDataFormat();             formatString = style.getDataFormatString();             if (formatString == null) {                 nextDataType = CellDataType.NULL;                 formatString = BuiltinFormats.getBuiltinFormat(formatIndex);             } else if (formatString.contains(＂m/d/yy＂)) {                 nextDataType = CellDataType.DATE;                 formatString = ＂yyyy-MM-dd hh:mm:ss＂;             }         }     }      /**      * 对解析出来的数据进行类型处理      *      * @param value   单元格的值，      *                value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，      *                SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值      * @param thisStr 一个空字符串      * @return      */     public String getDataValue(String value, String thisStr) {         switch (nextDataType) {             // 这几个的顺序不能随便交换，交换了很可能会导致数据错误             case BOOL: //布尔值                 char first = value.charAt(0);                 thisStr = first == ＂0＂ ? ＂FALSE＂ : ＂TRUE＂;                 break;             case ERROR: //错误                 thisStr = ＂＂ERROR:＂ + value.toString() + ＂＂＂;                 break;             case FORMULA: //公式                 thisStr = ＂＂＂ + value.toString() + ＂＂＂;                 break;             case INLINESTR:                 XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());                 thisStr = rtsi.toString();                 rtsi = null;                 break;             case SSTINDEX: //字符串                 String sstIndex = value.toString();                 try {                     int idx = Integer.parseInt(sstIndex);                     XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值                     thisStr = rtss.toString();                     rtss = null;                 } catch (NumberFormatException ex) {                     thisStr = value.toString();                 }                 break;             case NUMBER: //数字                 if (formatString != null) {                     thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();                 } else {                     thisStr = value;                 }                 thisStr = thisStr.replace(＂_＂, ＂＂).trim();                 break;             case DATE: //日期                 thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);                 // 对日期字符串作特殊处理，去掉T                 thisStr = thisStr.replace(＂T＂, ＂ ＂);                 break;             default:                 thisStr = ＂ ＂;                 break;         }         return thisStr;     }      public int countNullCell(String ref, String preRef) {         //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD         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;     }      public 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;     }      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;     }      /**      * @return the exceptionMessage      */     public String getExceptionMessage() {         return exceptionMessage;     }      /**      * 表头数据      *      * @param filePath      * @param sheetName      * @param sheetIndex      * @param curRow      * @param cellList      */     public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {      }      /**      * 解析数据      *      * @param filePath      * @param sheetName      * @param sheetIndex      * @param curRow      * @param cellList      */     public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List cellList) {      } }
　　三、导出import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFFont;  import javax.servlet.http.HttpServletResponse; import java.io.ByteArrayOutputStream; import java.io.OutputStream; import java.net.URLEncoder; import java.util.List; import java.util.Map;  /**  * EXCEL导出  */ public class BigExcelExportUtil {     /**      * 私有构造函数      */     private BigExcelExportUtil() {     }      /**      * 构造EXCEL,并响应客户端      * <功能详细描述>      *      * @param listValues      * @param fileName      * @param sheetName      * @param cellTitle      * @param cellValue      * @param res      * @see [类、类#方法、类#成员]      */     public static void buildXSLXExcel(List> listValues, String fileName, String sheetName, String[] cellTitle,                                       String[] cellValue, HttpServletResponse res) {         byte[] bytes = buildXSLXExcelWithSheetName(listValues, sheetName, cellTitle, cellValue);         if (0 == bytes.length) {             return;         }         writeDocResponse(res, bytes, fileName);     }      /**      * 构建EXCEL      * <功能详细描述>      *      * @param listValues      * @param sheetName      * @param cellTitle      * @param cellValue      * @see [类、类#方法、类#成员]      */     public static byte[] buildXSLXExcelWithSheetName(List> listValues, String sheetName, String[] cellTitle,                                                      String[] cellValue) {         try (ByteArrayOutputStream outStream = new ByteArrayOutputStream();              SXSSFWorkbook workBook = createExcelBook(listValues, sheetName, cellTitle, cellValue);) {             workBook.write(outStream);             outStream.flush();             workBook.dispose();             return outStream.toByteArray();         } catch (Exception e) {             e.printStackTrace();             return new byte[0];         }     }      /**      * 生成WorkBook      * <功能详细描述>      *      * @param listValues      * @param sheetName      * @param cellTitle      * @param cellValue      * @return      * @see [类、类#方法、类#成员]      */     private static SXSSFWorkbook createExcelBook(List> listValues, String sheetName, String[] cellTitle,                                                  String[] cellValue) {         //创建工作薄         SXSSFWorkbook workBook = new SXSSFWorkbook();         Sheet sheet = workBook.createSheet();          //工作簿名称         workBook.setSheetName(0, sheetName);          //字体设置         Font font = workBook.createFont();         font.setColor(XSSFFont.COLOR_NORMAL);         //font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);         font.setBold(true);         //创建格式         XSSFCellStyle cellStyle = (XSSFCellStyle) workBook.createCellStyle();         cellStyle.setFont(font);         //cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);         cellStyle.setAlignment(HorizontalAlignment.CENTER);         //cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);         cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);          //创建第一行标题         Row titleRow = sheet.createRow((short) 0);         for (int i = 0; i < cellTitle.length; i++) {             //创建第1行标题单元格             sheet.setColumnWidth(i, 20 * 256);             //XSSFCell cell = titleRow.createCell(i, 0);             Cell cell = titleRow.createCell(i, CellType.STRING);             cell.setCellStyle(cellStyle);             //cell.setCellType(XSSFCell.CELL_TYPE_STRING);             cell.setCellType(CellType.STRING);             cell.setCellValue(cellTitle[i]);         }          //第二行开始写入数据         if (listValues != null && listValues.size() > 0) {             //创建格式             XSSFCellStyle style = (XSSFCellStyle) workBook.createCellStyle();             //遍历列表数据             for (int i = 0; i < listValues.size(); i++) {                 Row row = sheet.createRow(i + 1);                 setRow(cellTitle, cellValue, workBook, sheet, style, listValues.get(i), i, row);             }         }         return workBook;     }      private static void setRow(String[] cellTitle, String[] cellValue,                                SXSSFWorkbook workBook, Sheet sheet, XSSFCellStyle style, Map map, int line, Row row) {         for (int j = 0; j < cellTitle.length; j++) {             // 在上面行索引0的位置创建单元格             //XSSFCell cell = titleRow.createCell(i, 0);             Cell cell = row.createCell(j, CellType.STRING);             // 定义单元格为字符串类型             //cell.setCellType(XSSFCell.CELL_TYPE_STRING);             cell.setCellType(CellType.STRING);             //取出列表值             cell.setCellValue(getMapString(map, cellValue[j]));             //style.setAlignment(XSSFCellStyle.ALIGN_CENTER);             style.setAlignment(HorizontalAlignment.CENTER);             cell.setCellStyle(style);         }     }      /**      * 输出文件到客户端      * <功能详细描述>      *      * @param rsp      http响应对象      * @param bytes      * @param fileName      * @see [类、类#方法、类#成员]      */     public static void writeDocResponse(HttpServletResponse rsp, byte[] bytes, String fileName) {         try (OutputStream out = rsp.getOutputStream();) {             //扩展名获取ContentType             rsp.setContentType(＂application/vnd.ms-excel＂);             String fileNameURL = URLEncoder.encode(fileName, ＂UTF-8＂);             String contentHeader = ＂attachment; filename=＂ + fileNameURL + ＂;＂ + ＂filename*=utf-8＂＂＂ + fileNameURL;             rsp.setHeader(＂Content-disposition＂, contentHeader);             //文件写入             out.write(bytes, 0, bytes.length);         } catch (Exception e) {             e.printStackTrace();         }     }      public static String getMapString(Map map, String key) {         return map.get(key) == null ? null : String.valueOf(map.get(key));     } }