pom版本
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.7</version> </dependency>1.自定义合并单元格
在某些业务场景中可能会有合并单元格的需求，下面具体来说明如何实现
1.1 不合并单元格
先来看下不合并单元格的代码写法，简单复习下
public static void writeExcel() { // 写excel的路径，当前项目路径下 String fileName = getPath(); // 构建ExcelWriter ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build(); // 构建sheet WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class).build(); // 写sheet excelWriter.write(data1(), writeSheet); excelWriter.finish(); } private static String getPath() { return System.getProperty("user.dir") + "/" + System.currentTimeMillis() + ".xlsx"; } private static List<DemoData> data1() { List<DemoData> list = Lists.newArrayList(); for (int i = 0; i < 3; i++) { DemoData data = new DemoData(); data.setString("字符串" + 1); data.setDate(new Date()); data.setDoubleData(0.56); list.add(data); } for (int i = 0; i < 3; i++) { DemoData data = new DemoData(); data.setString("字符串" + 2); data.setDate(new Date()); data.setDoubleData(0.56); list.add(data); } for (int i = 0; i < 4; i++) { DemoData data = new DemoData(); data.setString("字符串" + 3); data.setDate(new Date()); data.setDoubleData(0.57); list.add(data); } return list; } public static void main(String[] args) { writeExcel(); }打开输出的excel文件后如下，可以看到单元格没有合并。现在打算将第一列字符串标题相同的合并
1.2 合并单元格
// 自定义合并策略 该类继承了AbstractMergeStrategy抽象合并策略，需要重写merge()方法 public static class CustomMergeStrategy extends AbstractMergeStrategy { /** * 分组，每几行合并一次 */ private List<Integer> exportFieldGroupCountList; /** * 目标合并列index */ private Integer targetColumnIndex; // 需要开始合并单元格的首行index private Integer rowIndex; // exportDataList为待合并目标列的值 public CustomMergeStrategy(List<String> exportDataList, Integer targetColumnIndex) { this.exportFieldGroupCountList = getGroupCountList(exportDataList); this.targetColumnIndex = targetColumnIndex; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) { if (null == rowIndex) { rowIndex = cell.getRowIndex(); } // 仅从首行以及目标列的单元格开始合并，忽略其他 if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) { mergeGroupColumn(sheet); } } private void mergeGroupColumn(Sheet sheet) { int rowCount = rowIndex; for (Integer count : exportFieldGroupCountList) { if(count == 1) { rowCount += count; continue ; } // 合并单元格 CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex); sheet.addMergedRegionUnsafe(cellRangeAddress); rowCount += count; } } // 该方法将目标列根据值是否相同连续可合并，存储可合并的行数 private List<Integer> getGroupCountList(List<String> exportDataList){ if (CollectionUtils.isEmpty(exportDataList)) { return new ArrayList<>(); } List<Integer> groupCountList = new ArrayList<>(); int count = 1; for (int i = 1; i < exportDataList.size(); i++) { if (exportDataList.get(i).equals(exportDataList.get(i - 1))) { count++; } else { groupCountList.add(count); count = 1; } } // 处理完最后一条后 groupCountList.add(count); return groupCountList; } } // 修改WriteSheet的代码如下 public static void writeExcel() { String fileName = getPath(); ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build(); List<DemoData> demoDataList = data1(); // 写sheet的时候注册相应的自定义合并单元格策略 WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class) .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0)) .build(); excelWriter.write(demoDataList, writeSheet); excelWriter.finish(); }打开输出的excel文件后如下，可以看到第一列有相同值的单元格已经合并了，成功实现
同理若要合并第三列的数据，则可以在注册一个sheet写处理器，代码如下
public static void writeExcel() { String fileName = getPath(); ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build(); List<DemoData> demoDataList = data1(); WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class) .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0)) .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(o -> o.getDoubleData().toString()).collect(Collectors.toList()), 2)) .build(); excelWriter.write(demoDataList, writeSheet); excelWriter.finish(); }excel打开如下：
1.3 写多个sheet
public static void writeExcel() { String fileName = getPath(); ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build(); List<DemoData> demoDataList = data1(); WriteSheet writeSheet = EasyExcel.writerSheet("模板1").head(DemoData.class) .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0)) .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(o -> o.getDoubleData().toString()).collect(Collectors.toList()), 2)) .build(); excelWriter.write(demoDataList, writeSheet); WriteSheet writeSheet1 = EasyExcel.writerSheet("模板2").head(DemoData.class).build(); excelWriter.write(data1(), writeSheet1); excelWriter.finish(); }输出excel可以看到已经有两个sheet了
1.4 WriteTable
若业务需求要求在同一个sheet中写多个表，就需要用到WriteTable了。只定义一个WriteSheet，有几个表就定义几个WriteTable
public static void writeExcel01() { String fileName = getPath(); ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build(); WriteSheet writeSheet = EasyExcel.writerSheet("模板").needHead(Boolean.FALSE).build(); List<DemoData> demoDataList = data1(); // 需要表头设置为true，WriteTable一些属性会继承自WriteSheet WriteTable writeTable = EasyExcel.writerTable(1).head(DemoData.class).needHead(Boolean.TRUE) .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(DemoData::getString).collect(Collectors.toList()), 0)) .registerWriteHandler(new CustomMergeStrategy(demoDataList.stream().map(o -> o.getDoubleData().toString()).collect(Collectors.toList()), 2)) .build(); excelWriter.write(demoDataList, writeSheet, writeTable); WriteTable writeTable1 = EasyExcel.writerTable(2).head(DemoData.class).needHead(Boolean.TRUE).build(); excelWriter.write(data1(), writeSheet, writeTable1); excelWriter.finish(); }打开excel表格如下
结语
如有错误或者优化点欢迎指出，本文参考自EasyExcel文档
