EasyExcel

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文档

商匡云商
Logo
对比商品
  • 合计 (0)
对比
0
购物车