原文链接：https://juejin.cn/post/7023649189196201992
阿里EasyExcel让你彻底告别easypoi
为什么说EasyExcel可以让你告别easypoi呢？在说这个问题之前我们先来了解下easypoi
easypoi
这是easypoi官方给出的定义，使用这个工具后发现在进行excel的导入导出时，的确很方便。特别是一些简单的excel
比如这种简单的excel，easypoi的确是不二选择，只需要引入mavn依赖，添加一个pojo,加一个注解，然后就可以导出。 但是在遇到一些比较复杂的excel，比如下面这种：
类似于这种比较复杂的表头，一个sheet多张表，多个sheet,合并单元格各种复杂的情况下，easypoi处理起来就比较复杂了,反观easyExcel就比较拿手。
easyExcel处理简单的excel和easypoi一样简单，处理复杂的excel也完全可以通过注解的方式一步到位。开发者只需要编写很少的style代码就能直接达到自己想要的效果，下面就让我们一起来看看easyExcel的强大之处
引入maven依赖
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.10</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.0</version></dependency><dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.15</version></dependency>复制代码新建实体
@Data@Accessors(chain = true)@FieldNameConstants@HeadRowHeight(value = 25)@ContentRowHeight(value = 18)@ColumnWidth(value = 20)@HeadStyle(fillBackgroundColor = 64)@HeadFontStyle(bold = false)@ContentStyle(borderTop= BorderStyle.THIN,borderLeft = BorderStyle.THIN,borderRight = BorderStyle.THIN,borderBottom = BorderStyle.THIN)public class ComplexSubjectEasyExcel { @ExcelProperty(value = {"科目余额表","编制单位： 测试单位321412","科目编码","科目编码"},index = 0) private String subjectId; @ExcelProperty(value = {"科目余额表","编制单位： 测试单位321412","科目名称","科目名称"},index = 1) private String subjectName; @HeadFontStyle(bold = true) @ExcelProperty(value = {"科目余额表","编制单位： 测试单位321412","期初余额","借方"},index = 2) private BigDecimal firstBorrowMoney; @HeadFontStyle(bold = true) @ExcelProperty(value = {"科目余额表","编制单位： 测试单位321412","期初余额","贷方"},index = 3) private BigDecimal firstCreditMoney; @HeadFontStyle(bold = true) @ExcelProperty(value = {"科目余额表","2021年9月至2021年9月","本期发生额","借方"},index = 4) private BigDecimal nowBorrowMoney; @HeadFontStyle(bold = true) @ExcelProperty(value = {"科目余额表","2021年9月至2021年9月","本期发生额","贷方"},index = 5) private BigDecimal nowCreditMoney; @HeadFontStyle(bold = true) @ExcelProperty(value = {"科目余额表","2021年9月至2021年9月","本年累计发生额","借方"},index = 6) private BigDecimal yearBorrowMoney; @HeadFontStyle(bold = true) @ExcelProperty(value = {"科目余额表","2021年9月至2021年9月","本年累计发生额","贷方"},index = 7) private BigDecimal yearCreditMoney; @HeadFontStyle(bold = true) @ExcelProperty(value = {"科目余额表","单位：元","期末余额","借方"},index = 8) private BigDecimal endBorrowMoney; @HeadFontStyle(bold = true) @ExcelProperty(value = {"科目余额表","单位：元","期末余额","贷方"},index = 9) private BigDecimal endCreditMoney;}复制代码@ExcelProperty 注解的value是个数组，按照index从上到下，相同的值头部会进行合并。这种合并头部的方式相比easypoi的实体嵌套显得直观多了，更加方便。我们对页面列表的数据查询后，也不用进行数组对象嵌套组装，省了很多的工作量，如果希望头部的标题是动态的也可以设置成#{title}的方式(当然这是我自己封装的)
导出工具
下面我封装的easyExcel导出工具，使用的话可以直接复制，并自己做些适当的调整
import com.alibaba.excel.EasyExcel;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;import com.alibaba.excel.write.builder.ExcelWriterTableBuilder;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.handler.WriteHandler;import com.alibaba.excel.write.metadata.WriteSheet;import com.alibaba.excel.write.metadata.WriteTable;import org.springframework.util.Assert;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.io.InputStream;import java.lang.reflect.Field;import java.lang.reflect.InvocationHandler;import java.lang.reflect.Proxy;import java.util.*;/** * easyExcel工具 */public class EasyExcelUtilsV1 { public static final String FILE_PATH = "/home/easy/excel/"; public static final Map<String,List<ExcelAnnotationValue>> annotationValues = new HashMap<>(); private static String outputStream(String fileName){ try { String path = FILE_PATH+new Date().getTime() +"/"; String filePath = path+fileName+".xls"; File dir = new File(path); if(!dir.exists()){ dir.mkdirs(); } File file = new File(filePath); if(file.exists()){ file.deleteOnExit(); } file.createNewFile(); return filePath; } catch (Exception e) { e.printStackTrace(); } return null; } /** * 默认导出方式 单个sheet */ public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, String fileName, String sheetName, Map<String,String> vars){ resetCLassAnnotationValue(pojoClass); setExportClassAnnotation(pojoClass,vars); String filePath = outputStream(fileName); EasyExcel.write(filePath,pojoClass) .sheet(sheetName) .registerWriteHandler(new CustomCellWriteHandler()) .doWrite(list); return getExcelOssUrl(filePath,fileName); } /** * 默认导出方式 单个sheet */ public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, CellWriteHandler handler, String fileName, String sheetName, Map<String,String> vars){ resetCLassAnnotationValue(pojoClass); setExportClassAnnotation(pojoClass,vars); String filePath = outputStream(fileName); EasyExcel.write(filePath,pojoClass) .sheet(sheetName) .registerWriteHandler(new CustomCellWriteHandler()) .registerWriteHandler(handler) .doWrite(list); return getExcelOssUrl(filePath,fileName); } /** * 默认导出excel 单个sheet */ public static String defaultExportOssUrl(List<?> list, Class<?> pojoClass, List<WriteHandler> handlers, String fileName, String sheetName, Map<String,String> vars) { resetCLassAnnotationValue(pojoClass); setExportClassAnnotation(pojoClass,vars); String filePath = outputStream(fileName); ExcelWriterSheetBuilder builder = EasyExcel.write(filePath,pojoClass) .sheet(sheetName); if(!ObjectUtils.isEmpty(handlers)){ for(WriteHandler handler : handlers){ builder.registerWriteHandler(handler); } } builder.doWrite(list); return getExcelOssUrl(filePath,fileName); } /** * 默认导出excel 单个sheet 多个table */ public static String defaultExportOssUrl(EasyExcelMoreSheetMoreTableEntity entity, String fileName, Map<String,String> vars) { String filePath = outputStream(fileName); ExcelWriter excelWriter = EasyExcel.write(filePath).build(); String sheetName = entity.getSheetName(); List<WriteHandler> handlers = entity.getHandlers(); List<EasyExcelMoreSheetEntity> list = entity.getList(); try { WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).build(); for (int i = 0; i < list.size(); i++) { EasyExcelMoreSheetEntity sheetEntity = list.get(i); List date = sheetEntity.getList(); Class clazz = sheetEntity.getClazz(); resetCLassAnnotationValue(clazz); setExportClassAnnotation(clazz,vars); ExcelWriterTableBuilder tableBuilder = EasyExcel.writerTable(i); if (!ObjectUtils.isEmpty(handlers)) { for (WriteHandler handler : handlers) { tableBuilder.registerWriteHandler(handler); } } WriteTable table = tableBuilder.head(clazz).needHead(true).build(); excelWriter.write(date, writeSheet, table); } }catch (Exception e){ e.printStackTrace(); }finally { excelWriter.finish(); } return getExcelOssUrl(filePath,fileName); } /** * 多个sheet页导出 */ public static String moreSheetExportOssUrl(List<EasyExcelMoreSheetEntity> entities,String fileName){ String filePath = outputStream(fileName); ExcelWriter excelWriter = EasyExcel.write(filePath).build(); try { for (int i = 0; i < entities.size(); i++) { EasyExcelMoreSheetEntity entity = entities.get(i); Class clazz = entity.getClazz(); List list = entity.getList(); Map<String,String> vars = entity.getVars(); resetCLassAnnotationValue(clazz); setExportClassAnnotation(clazz,vars); String sheetName = entity.getSheetName(); List<WriteHandler> handlers = entity.getHandlers(); ExcelWriterSheetBuilder builder = EasyExcel.writerSheet(i, sheetName); if(!ObjectUtils.isEmpty(handlers)){ for(WriteHandler handler :handlers){ builder.registerWriteHandler(handler); } } WriteSheet writeSheet = builder.head(clazz).build(); excelWriter.write(list, writeSheet); } }catch (Exception e){ e.printStackTrace(); }finally { excelWriter.finish(); } return getExcelOssUrl(filePath,fileName); } @SuppressWarnings("unchecked") public static String moreSheetMoreTableExportOssUrl(List<EasyExcelMoreSheetMoreTableEntity> entities,String fileName){ String filePath = outputStream(fileName); ExcelWriter excelWriter = EasyExcel.write(filePath).build(); try { for (int i = 0; i < entities.size(); i++) { EasyExcelMoreSheetMoreTableEntity entity = entities.get(i); List<EasyExcelMoreSheetEntity> list = entity.getList(); String sheetName = entity.getSheetName(); List<WriteHandler> handlers = entity.getHandlers(); ExcelWriterSheetBuilder sheetBuilder = EasyExcel.writerSheet(i, sheetName); if(!ObjectUtils.isEmpty(handlers)){ for(WriteHandler handler :handlers){ sheetBuilder.registerWriteHandler(handler); } } //创建sheet WriteSheet writeSheet = sheetBuilder.build(); //创建table Assert.isTrue(!ObjectUtils.isEmpty(list),"缺少table数据"); for(int j = 0 ; j < list.size() ; j++){ EasyExcelMoreSheetEntity tableEntity = list.get(j); Map<String,String> vars = tableEntity.getVars(); List<?> date = tableEntity.getList(); Class<?> clazz = tableEntity.getClazz(); resetCLassAnnotationValue(clazz); setExportClassAnnotation(clazz, vars); ExcelWriterTableBuilder tableBuilder = EasyExcel.writerTable(j); if(j > 0){ tableBuilder.relativeHeadRowIndex(2); } WriteTable table = tableBuilder.head(clazz).needHead(true).build(); excelWriter.write(date,writeSheet,table); } } }catch (Exception e){ e.printStackTrace(); }finally { excelWriter.finish(); } return getExcelOssUrl(filePath,fileName); } public static void defaultExport(List<?> list, Class<?> pojoClass, String filePath, String sheetName) { EasyExcel.write(filePath,pojoClass) .sheet(sheetName) .registerWriteHandler(new CustomCellWriteHandler()) .doWrite(list); } private static String getExcelOssUrl(String filePath,String fileName) { InputStream in = null; try{ //临时缓冲区 in = new FileInputStream(filePath); } catch (Exception e){ e.printStackTrace(); } // 此处可以调用腾讯云的cos 或者阿里云的oss todo String url = ""; return url; } public static void setExportClassAnnotation(Class<?> clazz,Map<String,String> map){ Field[] fields = clazz.getDeclaredFields(); for(Field field : fields){ ExcelProperty property = field.getAnnotation(ExcelProperty.class); if(property != null){ List<String> newValues = new ArrayList<>(); String[] values = property.value(); for(String value : values){ value = replace(value,map); newValues.add(value); } InvocationHandler h = Proxy.getInvocationHandler(property); try { Field annotationField = h.getClass().getDeclaredField("memberValues"); annotationField.setAccessible(true); Map memberValues = (Map) annotationField.get(h); memberValues.put("value",newValues.toArray(new String[]{})); } catch (Exception e) { e.printStackTrace(); } } } } private static void resetCLassAnnotationValue(Class<?> clazz){ String className = clazz.getSimpleName(); List<ExcelAnnotationValue> values = annotationValues.get(className); if(ObjectUtils.isEmpty(values)){ //如果静态资源是空的，保存 Field[] fields = clazz.getDeclaredFields(); values = new ArrayList<>(); for(Field field : fields){ ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class); if(!ObjectUtils.isEmpty(excelProperty)) { String[] vs = excelProperty.value(); ExcelAnnotationValue value = new ExcelAnnotationValue() .setFieldName(field.getName()) .setValues(vs); values.add(value); } } annotationValues.put(className,values); return; } Field[] fields = clazz.getDeclaredFields(); for(Field field : fields){ String fieldName = field.getName(); ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class); if(!ObjectUtils.isEmpty(excelProperty)){ ExcelAnnotationValue value = values.stream().filter(v->v.getFieldName().equals(fieldName)).findFirst().orElse(null); if(!ObjectUtils.isEmpty(value)){ String[] oldValues = value.getValues(); InvocationHandler handler = Proxy.getInvocationHandler(excelProperty); try { Field annotationField = handler.getClass().getDeclaredField("memberValues"); annotationField.setAccessible(true); Map memberValues = (Map) annotationField.get(handler); memberValues.put("value",oldValues); } catch (Exception e) { e.printStackTrace(); } } } } } public static String replace(String el,Map<String,String> map){ if(map == null){ return el; } String evl = el; for(Map.Entry<String,String> m : map.entrySet()){ String key = m.getKey(); String value = m.getValue(); el = el.replaceAll("#\{"+key+"\}",value); if(!evl.equals(el)) { return el; } } return el; }}复制代码表格合并配置
import com.alibaba.excel.metadata.CellData;import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.handler.CellWriteHandler;import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;import com.alibaba.excel.write.metadata.holder.WriteTableHolder;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.util.CellRangeAddress;import java.util.List;/** * 合并单元格handler * * @author zl */public class CustomCellMergeStrategy implements CellWriteHandler { private int[] mergeColumnIndex; private int mergeRowIndex; public CustomCellMergeStrategy() { } public CustomCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) { int curRowIndex = cell.getRowIndex(); int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.length; i++) { if (curColIndex == mergeColumnIndex[i]) { mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); break; } } } } /** * 当前单元格向上合并 * * @param writeSheetHolder * @param cell 当前单元格 * @param curRowIndex 当前行 * @param curColIndex 当前列 */ private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); // 将当前单元格数据与上一个单元格数据比较 Boolean dataBool = preData.equals(curData); //此处需要注意：因为我是按照序号确定是否需要合并的，所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并 Boolean bool = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue()); if (dataBool && bool) { Sheet sheet = writeSheetHolder.getSheet(); List<CellRangeAddress> mergeRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergeRegions.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } }}复制代码表格的高度默认设置
public class CustomCellWriteHandler extends AbstractRowHeightStyleStrategy { @Override protected void setHeadColumnHeight(Row row, int i) { if(i == 0){ row.setHeight((short) (1000)); }else if(i == 1){ row.setHeight((short) 300); }else{ row.setHeight((short) 500); } } @Override protected void setContentColumnHeight(Row row, int i) { row.setHeight((short) 500); }}复制代码@Data@Accessors(chain = true)public class ExcelAnnotationValue { /** * 字段名称 */ private String fieldName; /** * ExcelProperty注解 属性value数组 */ private String[] values;}复制代码多sheet导出对象参数
@Data@Accessors(chain = true)public class EasyExcelMoreSheetEntity { /** * 实体类 */ private Class<?> clazz; /** * 数据 */ private List<?> list; /** * sheet名称 */ private String sheetName; /** * 样式 */ private List<WriteHandler> handlers; /** * head 参数 */ private Map<String,String> vars;}复制代码多表多sheet导出对象参数
@Data@Accessors(chain = true)public class EasyExcelMoreSheetMoreTableEntity { /** * 数据 */ private List<EasyExcelMoreSheetEntity> list; /** * sheet名称 */ private String sheetName; /** * 样式 */ private List<WriteHandler> handlers; /** * head 参数 */ private Map<String,String> vars;}复制代码下面的是最简单的导出
ComplexSubjectEasyExcel excel = new ComplexSubjectEasyExcel() .setSubjectId("1001") .setSubjectName("库存现金") .setFirstBorrowMoney(BigDecimal.valueOf(100)) .setNowBorrowMoney(BigDecimal.valueOf(105)) .setNowCreditMoney(BigDecimal.valueOf(100)) .setYearBorrowMoney(BigDecimal.valueOf(200)) .setYearCreditMoney(BigDecimal.valueOf(205)) .setEndBorrowMoney(BigDecimal.valueOf(240));List<ComplexSubjectEasyExcel> excels = new ArrayList<>();excels.add(excel);String url = EasyExcelUtils.defaultExportOssUrl(excels,ComplexSubjectEasyExcel.class,"科目余额表","科目余额表",new HashMap<>());System.out.println(url);复制代码多sheet导出
public void moreSheetTest(){ ComplexSubjectEasyExcel excel = new ComplexSubjectEasyExcel() .setSubjectId("1001") .setSubjectName("库存现金") .setFirstBorrowMoney(BigDecimal.valueOf(100)) .setNowBorrowMoney(BigDecimal.valueOf(105)) .setNowCreditMoney(BigDecimal.valueOf(100)) .setYearBorrowMoney(BigDecimal.valueOf(200)) .setYearCreditMoney(BigDecimal.valueOf(205)) .setEndBorrowMoney(BigDecimal.valueOf(240)); List<ComplexSubjectEasyExcel> excels = new ArrayList<>(); excels.add(excel); List<EasyExcelMoreSheetEntity> entities = new ArrayList<>(); for(int i=0 ; i< 2; i++){ EasyExcelMoreSheetEntity entity = new EasyExcelMoreSheetEntity() .setClazz(ComplexSubjectEasyExcel.class) .setList(excels) .setSheetName("科目余额表"+i); entities.add(entity); } String url = EasyExcelUtils.moreSheetExportOssUrl(entities,"科目余额表"); System.out.println(url);}复制代码多sheet，多表的导出
public void moreSheetMoreTableTest(){ ComplexSubjectEasyExcel excel = new ComplexSubjectEasyExcel() .setSubjectId("1001") .setSubjectName("库存现金") .setFirstBorrowMoney(BigDecimal.valueOf(100)) .setNowBorrowMoney(BigDecimal.valueOf(105)) .setNowCreditMoney(BigDecimal.valueOf(100)) .setYearBorrowMoney(BigDecimal.valueOf(200)) .setYearCreditMoney(BigDecimal.valueOf(205)) .setEndBorrowMoney(BigDecimal.valueOf(240)); List<ComplexSubjectEasyExcel> excels = new ArrayList<>(); excels.add(excel); List<EasyExcelMoreSheetMoreTableEntity> entities = new ArrayList<>(); for(int i=0 ; i< 2; i++){ EasyExcelMoreSheetMoreTableEntity tableEntity = new EasyExcelMoreSheetMoreTableEntity() .setSheetName("科目余额表"+i) .setHandlers(Arrays.asList(new CustomCellWriteHandler())); List<EasyExcelMoreSheetEntity> tables = new ArrayList<>(); EasyExcelMoreSheetEntity table = new EasyExcelMoreSheetEntity() .setClazz(ComplexSubjectEasyExcel.class) .setList(excels); if(i== 1){ tables.add(table); } tables.add(table); tableEntity.setList(tables); entities.add(tableEntity); } String url = EasyExcelUtils.moreSheetMoreTableExportOssUrl(entities,"科目余额表"); System.out.println(url);}复制代码到此为止！ 使用之后你就会发现easyExcel的便捷和强大
内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/123717.html