PeopleSoft集成EasyExcel

需求背景

很多系统都有将数据批量导入的需求，Excel是最常见的导入数据格式。PeopleSoft早期版本是不支持Excel的读写的，在8.55之后新增了PSSpreadsheet Api，但这个Api也不提供读取Excel数据的方法，只支持写入。所以相当一部分的PS系统还是使用基于POI编写的一套读取和写入Excel的Java方法集成到PS系统的。
但基于POI方式去实现的读写Excel方法有个致命的缺陷，它特别吃内存。我这边测试发现POI写入一个3M的xlsx文件大概会消耗掉1.6G的内存，这很容易导致系统产生OOM报错。之前就有程序员研究过为什么POI会消耗掉这么多的内存，主要原因是POI默认采用了用户模式来解析Excel，这种模式会一次性将所有数据加载到内存并形成一颗DOM树，这种实现资源利用太低，太消耗内存。
现在有一种新的事件驱动模式来解析文件，这种方式内存占用小、效率高， 但是 API 太过繁琐，开发者必须在熟知文档规范的前提下才能使用，而且 xls 和 xlsx 使用的是完全不同的两套 API，实际项目中必须针对不同文件类型分别实现。好在EasyExcel对API进行了一些高级封装，这也是我选择EasyExcel而不用POI的SAX模式的原因。

效果对比

这是基于POI读取Excel文件的情况，OOM。

这是基于EasyExcel读取Excel文件的情况，能正常读取数据。

代码实现

package com.seq.easyExcel;import com.alibaba.excel.EasyExcel;import java.io.File;
import java.util.List;
import java.util.Map;public class ExcelReader {File xlsxFile;List<Map<Integer,String>> dataList;public ExcelReader() {}public ExcelReader(String filePath) {this.xlsxFile = new File(filePath);this.dataList = EasyExcel.read(this.xlsxFile).sheet(0).doReadSync();}public void loadFile(String filePath,int sheet){this.xlsxFile = new File(filePath);this.dataList = EasyExcel.read(this.xlsxFile).sheet(sheet).doReadSync();}public int getRowCount(int sheet) {return this.dataList.size();}public String getCellValue(int row, int column) {return this.dataList.get(row).get(column);}
}

package com.seq.easyExcel;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.merge.OnceAbsoluteMergeStrategy;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.alibaba.excel.write.style.row.SimpleRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.*;import java.util.ArrayList;
import java.util.List;
import java.util.Map;public class ReportDefinition {private String filePath;private List<ReportRow> dataList;private List<List<String>> header;private List<OnceAbsoluteMergeStrategy> onceAbsoluteMergeStrategies;@ExcelPropertyMap<String, String> parameter;private String sheetName;public ReportDefinition(String filePath) {this.dataList = new ArrayList<>();this.header = new ArrayList<>();this.onceAbsoluteMergeStrategies = new ArrayList<>();this.filePath = filePath;}public void setSheetName(String sheetName) {this.sheetName = sheetName;}public void build() {if (this.sheetName == null) {this.sheetName = "Sheet1";}ExcelWriterSheetBuilder builder = EasyExcel.write(this.filePath).sheet(this.sheetName);builder.head(this.header);builder.registerWriteHandler(new SimpleColumnWidthStyleStrategy(20));builder.registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 15, (short) 15));builder.registerWriteHandler(getHorizontalCellStyleStrategy((short) 11));for (int i = 0; i < this.onceAbsoluteMergeStrategies.size(); i++) {builder.registerWriteHandler(this.onceAbsoluteMergeStrategies.get(i));}builder.registerConverter(new CustomDateStringConverter());List<List<Object>> lists = new ArrayList<>();for (int i = 0; i < this.dataList.size(); i++) {lists.add(this.dataList.get(i).getCellList());}builder.doWrite(lists);}public void addRow(ReportRow row) {this.dataList.add(row);}public void addParameter(String name, String value) {this.parameter.put(name, value);}public void addHeader(ReportRow header) {List<Object> list = header.getCellList();//纵向转横向for (int i = 0; i < list.size(); i++) {List<String> temp = new ArrayList<>();temp.add(list.get(i).toString());this.header.add(temp);}}// 合并单元格（在一行中合并相同的单元格）public void mergeSameCellsInRow(int theRow, int firstCol, int lastCol) {this.onceAbsoluteMergeStrategies.add(new OnceAbsoluteMergeStrategy(theRow, theRow, firstCol, lastCol));}// 合并单元格（在一列中合并相同的单元格）public void mergeSameCellsInColumn(int theCol, int firstRow, int lastRow) {this.onceAbsoluteMergeStrategies.add(new OnceAbsoluteMergeStrategy(firstRow, lastRow, theCol, theCol));}// 合并单元格（不同行，不同列）public void mergeCells(int firstRow, int lastRow, int firstCol, int lastCol) {this.onceAbsoluteMergeStrategies.add(new OnceAbsoluteMergeStrategy(firstRow, lastRow, firstCol, lastCol));}/*** 单元格样式*/public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy(Short fontHeightInPoints) {//表头单元格格式WriteCellStyle headerWriteCellStyle = new WriteCellStyle();headerWriteCellStyle.setBorderBottom(BorderStyle.THIN);headerWriteCellStyle.setBorderLeft(BorderStyle.THIN);headerWriteCellStyle.setBorderRight(BorderStyle.THIN);headerWriteCellStyle.setBorderTop(BorderStyle.THIN);WriteFont headerWriteFont = new WriteFont();headerWriteFont.setFontName("等线");headerWriteFont.setFontHeightInPoints((short) (fontHeightInPoints + 1));headerWriteFont.setBold(true);headerWriteCellStyle.setWriteFont(headerWriteFont);headerWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);headerWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);headerWriteCellStyle.setWrapped(true);headerWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());headerWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);// 表体单元格格式WriteCellStyle contentWriteCellStyle = new WriteCellStyle();contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderTop(BorderStyle.THIN);WriteFont contentWriteFont = new WriteFont();contentWriteFont.setFontName("等线");contentWriteFont.setFontHeightInPoints(fontHeightInPoints);contentWriteFont.setBold(false);contentWriteCellStyle.setWriteFont(contentWriteFont);contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);contentWriteCellStyle.setWrapped(true);return new HorizontalCellStyleStrategy(headerWriteCellStyle, contentWriteCellStyle);}
}

package com.seq.easyExcel;import java.sql.Date;
import java.util.ArrayList;
import java.util.List;public class ReportRow {private List<Object> cellList;public ReportRow() {this.cellList = new ArrayList<>();}public void addString(String s) {if (s == "" || s == " ") {this.cellList.add(null);} else {this.cellList.add(s);}}public void addNumber(double n) {this.cellList.add(n);}public void addDate(Date date) {if (date == null) {this.cellList.add(null);} else {java.util.Date tempDate = new java.util.Date(date.getTime());this.cellList.add(tempDate);}}public void addDatetim(java.util.Date date) {this.cellList.add(date);}public List<Object> getCellList() {return this.cellList;}
}

package com.seq.easyExcel;import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.property.ExcelContentProperty;import java.text.SimpleDateFormat;
import java.util.Date;public class CustomDateStringConverter implements Converter<Date> {@Overridepublic Class supportJavaTypeKey() {return Date.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic Date convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {return null;}@Overridepublic CellData convertToExcelData(Date date, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
//        System.out.println("ExcelContentProperty:"+excelContentProperty.toString());System.out.println("GlobalConfiguration:"+globalConfiguration.toString());SimpleDateFormat sdf=new SimpleDateFormat("yyyy/MM/dd");return new CellData(sdf.format(date));}
}

遗留问题：写入Excel发现easyexcel想要很灵活的设置单元格的数字格式需要在对应对象的类中使用@Excelproperty注解来实现。但我这里是给PS系统用的需要灵活生成Excel，所以使用的非对象模板生成excel的方式，但这样就没办法使用注解了。想问问有没有大佬给个解决方案。