Java利用EasyExcel实现合并单元格

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

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;

    
    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表格如下