搜索

效率爆表！EasyExcel合并单元格这样实现才是yyds

小哈学Java

共 25740字，需浏览 52分钟

 ·

2024-08-19 10:30

来源：juejin.cn/post/7322156759443095561

👉 欢迎加入小哈的星球，你将获得: 专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17..., 点击查看项目介绍；
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2期已完结,演示链接：http://116.62.199.48/;

截止目前，累计输出 54w+ 字，讲解图 2330+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，Spring Cloud Alibaba 等等，戳我加入学习，解锁全部项目，已有1900+小伙伴加入

一、引入

通过阅读本文，你将了解到：

  • 使用EasyExcel写出Excel时，如何一步步提升性能；
  • 写出有合并单元格的页签时，如何在更短的时间内写出更多的数据。

如果你第一次接触EasyExcel，可以先访问官网，按照指引掌握EasyExcel读写操作，然后再阅读本文。

如果你已经掌握了使用EasyExcel读写Excel，那么当你需要写出有合并单元格的大页签时，你会如何实现？

<!-- 本文引用的版本 -->
<dependency>  
    <groupId>com.alibaba</groupId>  
    <artifactId>easyexcel</artifactId>  
    <version>3.1.1</version>  
</dependency>

假设有个需求

假设你所在的公司需要开发一个功能：将数据库中票据表写出到Excel中，而且想在尽可能短的时间内（如30秒）写出几个月甚至一年内的数据（可能有几十万、上百万条记录），你会如何实现？

我们先来看看票据的一个简单模型：由一个头信息区、多条明细两部分组成，写出到Excel时样式如下。

图片

图片 
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import java.math.BigDecimal;
import java.util.Date;

/**
 * 一张票据下有多项费用科目
 */
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class BillExpenseDetail {
    @ExcelProperty("票据编号")
    private String number;
    @ExcelProperty("创建时间")
    private Date createDate;
    @ExcelProperty("收支方向")
    private String direction ;
    @ExcelProperty("总金额")
    private BigDecimal totalAmount;

    @ExcelProperty("名称规格")
    private String subject;
    @ExcelProperty("单价")
    private String price;
    @ExcelProperty("数量")
    private String quantity;
    @ExcelProperty("单位")
    private String unit;
    @ExcelProperty("金额")
    private BigDecimal amount;
}

二、无合并单元格时

2.1 一次性查询写出

不考虑单元格合并时，你可能会这样实现：一次性查询所有数据，然后一次性写出。

// 查询所有数据
private static List<BillExpenseDetail> queryAll() {
  return new ArrayList<>();
}

public static void simpleWrite() {
  String fileName = "/bill/simpleWrite.xlsx";
  try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {
    WriteSheet writeSheet = EasyExcel.writerSheet("票据").build();
    excelWriter.write(queryAll(), writeSheet);
  }
}

这样实现有问题吗？数据量较少时没问题。

可是，当一次需要写出的数据有数万条甚至更多时，将所有数据一次性查询到内存中，当所有数据写出后，才能释放内存。这样可能导致很大的内存压力，甚至服务OOM。

有什么更好的办法吗？有。

2.2 分页查询写出

EasyExcel支持重复多次写单个或者多个Sheet页，我们可以多次分页查库获取数据，循环写入到一个Excel页签中。

  // 查询数据总量
  private int count() {
    // 假设为100万
    return 1000000;
  }

  // 分页查询
  private List<BillExpenseDetail> pageQuery(int startIndex, int limit) {
    return new ArrayList<>();
  }

  public void repeatedWrite() {
    int count = count();
    int pageSize = 1000;
    int pageCount = count / pageSize;
    pageCount = pageCount * pageSize < count ? pageCount + 1 : pageCount;
    
    String fileName = "/bill/repeatedWrite.xlsx";
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {
      // 写出到一个sheet页中，因此在for外面创建WriteSheet
      WriteSheet writeSheet = EasyExcel.writerSheet("票据").build();
      // 逐页查询，追加写出
      for (int i = 0; i < pageCount; i++) {
        List<BillExpenseDetail> detailList = pageQuery(i * pageSize, pageSize);
        excelWriter.write(detailList, writeSheet);
        // help gc
        detailList.clear();
      }
    }
  }

现在，查询数据时内存压力减小了。可是等上线后，发现导出一个月的数据可能需要10秒，导出半年内数据时可能需要50秒或更长时间。如果是离线导出，耗时久点也能接受；如果是在线导出，可能接口响应超时。

有更高效的办法吗？

2.3 并发查询依次写出

查询一页数据写出后，再查询下一页，读与写是串行的。而且分页查询时页码越大，一次查询耗时越久。那么我们能否并发查询同时写出呢？当然可以。

  public static final ExecutorService EXECUTOR_SERVICE = Executors.newFixedThreadPool(4);

  public void repeatedWrite() {
    // 并发分页查询数据
    int count = count();
    int pageSize = 1000;
    int pageCount = count / pageSize;
    pageCount = pageCount * pageSize < count ? pageCount + 1 : pageCount;
    List<Future<List<BillExpenseDetail>>> futureList = new ArrayList<>(pageCount);
    for (int i = 0; i < pageCount; i++) {
      int index = i;
      Future<List<BillExpenseDetail>> submit = EXECUTOR_SERVICE.submit(
        () -> pageQuery(index * pageSize, pageSize));
      futureList.add(submit);
    }

    String fileName = "/bill/repeatedWrite.xlsx";
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {
      // 写出到一个sheet页中
      WriteSheet writeSheet = EasyExcel.writerSheet("票据").build();
      // 追加写
      for (Future<List<BillExpenseDetail>> future : futureList) {
        try {
          List<BillExpenseDetail> detailList = future.get();
          excelWriter.write(detailList, writeSheet);
          // help gc
          detailList.clear();
        } catch (InterruptedException | ExecutionException e) {
          throw new RuntimeException(e);
        }
      }
    }
  }

三、合并单元格写出Excel

现在我们考虑如何实现合并单元格。因为一个票据有多个明细行（数量不确定），导出Excel时要将“票据编号”、“创建时间”等列跨行合并。该如何实现呢？

3.1 EasyExcel中实现

EasyExcel提供了两个创建合并单元格的注解，以及与注解等效的WriteHandler接口实现。定义下面的数据类，我们来试用一下。

@Getter
@Setter
public class DemoMergeData {
    @ExcelProperty("字符串")
    private String string;
    @ExcelProperty("日期")
    private Date date;
    @ExcelProperty("数字")
    private Double doubleData;
}

3.1.1 @ContentLoopMerge

图片

先不使用@ContentLoopMerge，生成的Excel如下：

图片

对第一列使用@ContentLoopMerge后，生成的Excel如下：

// 每两行合并一次，跨两列
@ContentLoopMerge(eachRow = 2, columnExtend = 2)
@ExcelProperty("字符串")
private String string;

图片

3.1.2 @OnceAbsoluteMerge

该注解通过指定合并区域行列索引，用来创建一个合并区域（不是循环创建）；单元格值取左上角单元格的。

图片 
@Getter
@Setter
// 将第2-6行的2-3列合并
@OnceAbsoluteMerge(firstRowIndex =1, lastRowIndex = 5, firstColumnIndex = 1, lastColumnIndex = 2)
public class DemoMergeData {
    @ExcelProperty("字符串")
    private String string;
    @ExcelProperty("日期")
    private Date date;
    @ExcelProperty("数字")
    private Double doubleData;
}

效果如下：

图片

3.1.3 WriteHandler实现

EasyExcel提供了与上面两个注解等效的WriteHandler实现，分别是OnceAbsoluteMergeStrategy和LoopMergeStrategy。使用方式如下：

public static void mergeWrite() {
  String fileName = "/excel/mergeWrite.xlsx";
  // 对第一列每隔2行合并一次，不跨列（第二个参数）
  LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 1, 0);
  // 创建合并区：将第2-6行的2-3列合并
  OnceAbsoluteMergeStrategy absoluteMergeStrategy = new OnceAbsoluteMergeStrategy(1, 5, 1, 2);
  EasyExcel.write(fileName, DemoMergeData.class)
      .registerWriteHandler(loopMergeStrategy)
      .registerWriteHandler(absoluteMergeStrategy)
      .sheet("模板")
      .doWrite(data());
}

3.2 自定义合并策略

3.2.1 网络上的常规实现

票据导出时因为每个票据的明细行数量不定，@ContentLoopMerge就不适用了。此时，我们自然想到去网上找找方案。比如，我找到了这篇博客《EasyExcel导出自定义合并单元格策略》：

  • https://cloud.tencent.com/developer/article/1671316。

它的实现方式如下，核心逻辑为：

实现CellWriteHandler接口，在Cell层面，每写一行数据，将合并列的单元格数据，与上一行的单元格数据比较。如果数据相同，就将当前行与上一行合并；如果上一行已被合并，则将当前行加入到合并区。

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import java.util.List;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

public class ExcelFillCellMergeStrategy implements CellWriteHandler {

  // 需要创建合并区的列
  private int[] mergeColumnIndex;
  // 从第几行后开始合并，取列头行
  private int mergeRowIndex;

  public ExcelFillCellMergeStrategy() {
  }

  public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
    this.mergeRowIndex = mergeRowIndex;
    this.mergeColumnIndex = mergeColumnIndex;
  }

  @Override
  public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex,
      Integer relativeRowIndex, Boolean isHead) {

  }

  @Override
  public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex,
      Boolean isHead) {

  }

  @Override
  public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> list, Cell cell,
      Head head,
      Integer integer, Boolean aBoolean) {
    int curRowIndex = cell.getRowIndex();
    int curColIndex = cell.getColumnIndex();
    if (curRowIndex > mergeRowIndex) {
      for (int i = 0; i < mergeColumnIndex.length; i++) {
        // 需合并的列
        if (curColIndex == mergeColumnIndex[i]) {
          mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
          break;
        }
      }
    }
  }

  /**
   * 当前单元格向上合并
   *
   * @param writeSheetHolder
   * @param cell             当前单元格
   * @param curRowIndex      当前行
   * @param curColIndex      当前列
   */
  private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
    Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue();
    Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
    Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue();
    // 将当前单元格数据与上一个单元格数据比较
    Boolean dataBool = preData.equals(curData);
    //此处需要注意：因为我是按照序号确定是否需要合并的，所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并
    Boolean bool = cell.getRow().getCell(0).getNumericCellValue() == cell.getSheet().getRow(curRowIndex - 1).getCell(0).getNumericCellValue();
    if (dataBool && bool) {
      Sheet sheet = writeSheetHolder.getSheet();
      List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
      boolean isMerged = false;
      for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
        CellRangeAddress cellRangeAddr = mergeRegions.get(i);
        // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
        if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
          sheet.removeMergedRegion(i);
          cellRangeAddr.setLastRow(curRowIndex);
          sheet.addMergedRegion(cellRangeAddr);
          isMerged = true;
        }
      }
      // 若上一个单元格未被合并，则新增合并单元
      if (!isMerged) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex);
        sheet.addMergedRegion(cellRangeAddress);
      }
    }
  }
}

能实现我们导出票据的需求吗？能。但是试用后将会发现，这个实现性能不佳：

  • 每写入一个单元格，都需要读取上一行，一边写入一边读取；
  • 当上一行已经合并过了，本次写入需要修改合并区域，而且会反复修改；
  • 比如，写出下图中第一个票据，写出3行，将读取3次，修改合并区域两次。

图片

此外，网上还有一些基于RowWriteHandler接口的实现，也存在上面指出的性能问题。

3.2.2 我的实现

当我们分页查询票据记录后，可以按照合并自动进行分组，每组数量就是合并区域大小，合并区域位置可以通过行数累加来定位。

因此，写出Excel前就可以预知那些合并区域。如果在创建sheet页时就将这些区域一并创建，写出时就不用关注单元格合并了。岂不美哉！

预创建合并区：实现SheetWriteHandler接口，重写afterSheetCreate()，将合并区域加入到sheet中。

图片 
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import java.util.Collections;
import java.util.List;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 添加合并区Handler
 */
public class AddCellRangeWriteHandler implements SheetWriteHandler {

  private final List<CellRangeAddress> rangeCellList;

  public AddCellRangeWriteHandler(List<CellRangeAddress> rangeCellList) {
    this.rangeCellList = (rangeCellList == null) ? Collections.emptyList() : rangeCellList;
  }

  public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    Sheet sheet = writeSheetHolder.getSheet();
    for (CellRangeAddress cellRangeAddress : this.rangeCellList) {
      sheet.addMergedRegionUnsafe(cellRangeAddress);
    }
  }
}
  public static final ExecutorService EXECUTOR_SERVICE = Executors.newFixedThreadPool(4);

  public void repeatedWrite() {
    // 并发分页查询数据
    int count = count();
    int pageSize = 1000;
    int pageCount = count / pageSize;
    pageCount = pageCount * pageSize < count ? pageCount + 1 : pageCount;
    List<Future<List<BillExpenseDetail>>> futureList = new ArrayList<>(pageCount);
    for (int i = 0; i < pageCount; i++) {
      int index = i;
      Future<List<BillExpenseDetail>> submit = EXECUTOR_SERVICE.submit(
        () -> pageQuery(index * pageSize, pageSize));
      futureList.add(submit);
    }

    // 追加写
    String fileName = "/bill/repeatedWrite.xlsx";
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, BillExpenseDetail.class).build()) {
      // 行计数，初始值取列头行数
      int lineCount = 1;
      // sheet中需要合并的列的索引
      final int[] mergeColumnIndex = {0, 1, 2, 3};
      WriteSheet writeSheet;
      for (Future<List<BillExpenseDetail>> future : futureList) {
        try {
          List<BillExpenseDetail> detailList = future.get();
          List<CellRangeAddress> rangeCellList = createCellRange(detailList, mergeColumnIndex, lineCount);
          lineCount += detailList.size();
          // 写出到一个sheet页中，sheetName固定
          writeSheet = EasyExcel.writerSheet("票据").registerWriteHandler(new AddCellRangeWriteHandler(rangeCellList)).build();
          excelWriter.write(detailList, writeSheet);
          // 及时释放内存
          detailList.clear();
        } catch (InterruptedException | ExecutionException e) {
          throw new RuntimeException(e);
        }
      }
    }
  }

  /**
   * 生成合并区
   *
   * @param detailList       票据
   * @param mergeColumnIndex sheet 中需要合并的列的索引
   * @param lineCount        行计数（包括列头行）
   * @return 合并区
   */
  private List<CellRangeAddress> createCellRange(List<BillExpenseDetail> detailList, int[] mergeColumnIndex, int lineCount) {
    if (detailList.isEmpty()) {
      return Collections.emptyList();
    }

    List<CellRangeAddress> rangeCellList = new ArrayList<>();
    Map<String, Long> groupMap = detailList.stream().collect(Collectors.groupingBy(BillExpenseDetail::getNumber, Collectors.counting()));
    for (Map.Entry<String, Long> entry : groupMap.entrySet()) {
      int count = entry.getValue().intValue();
      int startRowIndex = lineCount;
      // 如合并第2到4行，共3行，行索引从1到3
      int endRowIndex = lineCount + count - 1;
      for (int columnIndex : mergeColumnIndex) {
        rangeCellList.add(new CellRangeAddress(startRowIndex, endRowIndex, columnIndex, columnIndex));
      }
      lineCount += count;
    }
    return rangeCellList;
  }

该方式我已在工作中使用，性能确实有较大提升。感兴趣的小伙伴，也不妨一试。

👉 欢迎加入小哈的星球，你将获得: 专属的项目实战 / 1v1 提问 / Java 学习路线 / 学习打卡 / 每月赠书 / 社群讨论

  • 新项目:《从零手撸：仿小红书（微服务架构）》 正在持续爆肝中，基于 Spring Cloud Alibaba + Spring Boot 3.x + JDK 17..., 点击查看项目介绍；
  • 《从零手撸：前后端分离博客项目（全栈开发）》 2期已完结,演示链接：http://116.62.199.48/;

截止目前，累计输出 54w+ 字，讲解图 2330+ 张，还在持续爆肝中.. 后续还会上新更多项目，目标是将 Java 领域典型的项目都整一波，如秒杀系统, 在线商城, IM 即时通讯，Spring Cloud Alibaba 等等，戳我加入学习，解锁全部项目，已有1900+小伙伴加入


    
       

        

         

          

           
1. 我的私密学习小圈子~

           
2. 船新 IDEA 2024.2 正式发布，新特性真香！

           
3. 使用 Java + WebSocket 实现简单实时双人协同 pk 答题

           
4. Java8 中一个极其强悍的新接口，很多人没用过

           

          

         

        

       
最近面试BAT，整理一份面试资料《Java面试BATJ通关手册》，覆盖了Java核心技术、JVM、Java并发、SSM、微服务、数据库、数据结构等等。
获取方式：点“在看”，关注公众号并回复 Java 领取，更多内容陆续奉上。
PS：因公众号平台更改了推送规则，如果不想错过内容，记得读完点一下“在看”，加个“星标”，这样每次新文章推送才会第一时间出现在你的订阅列表里。
点“在看”支持小哈呀，谢谢啦 

浏览 123
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
效率爆表深夜
效率爆表深夜
0
Excel百万级数据导入导出,EasyExcel 才是 yyds
我是程序汪
0
4月，如何成为效率爆表的人？
人生不止有技术
0
Animated-TableViewCell实现动画单元格
Animated-TableViewCell 是一个实现了动画单元格的示例项目。
Animated-TableViewCell实现动画单元格
Animated-TableViewCell是一个实现了动画单元格的示例项目。
程序猿听歌时码字效率爆表~
moon聊技术
0
Python批量合并Excel，实现数据透视表
蚂蚁学Python
0
颜值爆表！堪称开发者的瑞士军刀！YYDS！！！
我是程序汪
0
再见 Postman！Apifox 才是 YYDS！
java团长
0
扔掉 Postman，Apifox 才是 YYDS！
公众号程序猿DD
0
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报