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