快捷導(dǎo)航

Java利用EasyExcel實(shí)現(xiàn)合并單元格

更新時(shí)間：2022年06月09日 15:14:12 作者：默念x

在某些業(yè)務(wù)場(chǎng)景中可能會(huì)有合并單元格的需求，本文將詳細(xì)為大家講解Java如何利用EasyExcel實(shí)現(xiàn)合并單元格，感興趣的小伙伴可以了解一下

pom版本

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>2.2.7</version>
    </dependency>

1.自定義合并單元格

在某些業(yè)務(wù)場(chǎng)景中可能會(huì)有合并單元格的需求，下面具體來(lái)說(shuō)明如何實(shí)現(xiàn)

1.1 不合并單元格

先來(lái)看下不合并單元格的代碼寫法，簡(jiǎn)單復(fù)習(xí)下

public static void writeExcel() {
    // 寫excel的路徑，當(dāng)前項(xiàng)目路徑下
    String fileName = getPath();
    // 構(gòu)建ExcelWriter
    ExcelWriter excelWriter = EasyExcel.write(fileName).excelType(ExcelTypeEnum.XLSX).build();

    // 構(gòu)建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文件后如下，可以看到單元格沒(méi)有合并。現(xiàn)在打算將第一列字符串標(biāo)題相同的合并

1.2 合并單元格

// 自定義合并策略 該類繼承了AbstractMergeStrategy抽象合并策略，需要重寫merge()方法
  public static class CustomMergeStrategy extends AbstractMergeStrategy {

    /**
     * 分組，每幾行合并一次
     */
    private List<Integer> exportFieldGroupCountList;

    /**
     * 目標(biāo)合并列index
     */
    private Integer targetColumnIndex;

    // 需要開始合并單元格的首行index
    private Integer rowIndex;

    // exportDataList為待合并目標(biāo)列的值 
    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();
      }
      // 僅從首行以及目標(biāo)列的單元格開始合并，忽略其他
      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;
      }
    }

    // 該方法將目標(biāo)列根據(jù)值是否相同連續(xù)可合并，存儲(chǔ)可合并的行數(shù) 
    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的時(shí)候注冊(cè)相應(yīng)的自定義合并單元格策略
    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文件后如下，可以看到第一列有相同值的單元格已經(jīng)合并了，成功實(shí)現(xiàn)

同理若要合并第三列的數(shù)據(jù)，則可以在注冊(cè)一個(gè)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 寫多個(gè)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可以看到已經(jīng)有兩個(gè)sheet了

1.4 WriteTable

若業(yè)務(wù)需求要求在同一個(gè)sheet中寫多個(gè)表，就需要用到WriteTable了。只定義一個(gè)WriteSheet，有幾個(gè)表就定義幾個(gè)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();
    // 需要表頭設(shè)置為true，WriteTable一些屬性會(huì)繼承自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表格如下