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