Java利用EasyExcel实现合并单元格_Java教程

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文件后如下，可以看到单元格没有合并。现在打算将第一列字符串标题相同的合并

Java利用EasyExcel实现合并单元格

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文件后如下，可以看到第一列有相同值的单元格已经合并了，成功实现

Java利用EasyExcel实现合并单元格

同理若要合并第三列的数据，则可以在注册一个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打开如下：

Java利用EasyExcel实现合并单元格

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了

Java利用EasyExcel实现合并单元格

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

Java利用EasyExcel实现合并单元格