SpringBoot整合EasyExcel 3.x

1 EasyExcel 3.x

1.1 简介

EasyExcel 是一个基于 Java 的、快速、简洁、解决大文件内存溢出的 Excel 处理工具。它能让你在不用考虑性能、内存的等因素的情况下,快速完成 Excel 的读、写等功能。

EasyExcel文档地址:https://easyexcel.opensource.alibaba.com/

1.2 引入依赖

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

注意:如果使用 3.3.0 版本可能会报错:com.alibaba.excel.support.util.collectionutils,可以升级到 3.3.3 即可

1.3 简单导出

1.3.1 定义实体类

EasyExcel中,以面向对象思想来实现导入导出,无论是导入数据还是导出数据都可以想象成具体某个对象的集合,所以为了实现导出用户信息功能,首先创建一个用户对象UserDO实体类,用于封装用户信息:

@Data
public class UserDO {
    @ExcelProperty("用户编号")
    @ColumnWidth(20)
    private Long id;

    @ExcelProperty("用户名")
    @ColumnWidth(20)
    private String username;

    @ExcelIgnore
    private String password;

    @ExcelProperty("昵称")
    @ColumnWidth(20)
    private String nickname;

    @ExcelProperty("生日")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date birthday;

    @ExcelProperty("手机号")
    @ColumnWidth(20)
    private String phone;

    @ExcelProperty("身高(米)")
    @NumberFormat("#.##")
    @ColumnWidth(20)
    private Double height;

    @ExcelProperty(value = "性别", converter = GenderConverter.class)
    @ColumnWidth(10)
    private Integer gender;
}

上面代码中类属性上使用了EasyExcel核心注解:

  • @ExcelProperty:核心注解,value属性可用来设置表头名称,converter属性可以用来设置类型转换器;
  • @ColumnWidth:用于设置表格列的宽度;
  • @DateTimeFormat:用于设置日期转换格式;
  • @NumberFormat:用于设置数字转换格式。

1.3.2 自定义转换器

EasyExcel中,如果想实现枚举类型到字符串类型转换(例如gender属性:1 -> 男,2 -> 女),需实现Converter接口来自定义转换器,下面为自定义GenderConverter性别转换器代码实现:

public class GenderConverter implements Converter<Integer> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return Integer.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public Integer convertToJavaData(ReadConverterContext<?> context) {
        return GenderEnum.convert(context.getReadCellData().getStringValue()).getValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) {
        return new WriteCellData<>(GenderEnum.convert(context.getValue()).getDescription());
    }
}

性别枚举

@Getter
@AllArgsConstructor
public enum GenderEnum {

    /**
     * 未知
     */
    UNKNOWN(0, "未知"),

    /**
     * 男性
     */
    MALE(1, "男性"),

    /**
     * 女性
     */
    FEMALE(2, "女性");

    private final Integer value;

    @JsonFormat
    private final String description;

    public static GenderEnum convert(Integer value) {
        return Stream.of(values())
                .filter(bean -> bean.value.equals(value))
                .findAny()
                .orElse(UNKNOWN);
    }

    public static GenderEnum convert(String description) {
        return Stream.of(values())
                .filter(bean -> bean.description.equals(description))
                .findAny()
                .orElse(UNKNOWN);
    }
}

1.3.3 定义接口

@RestController
@RequestMapping("/excel")
public class ExcelController {

    @GetMapping("/export/user")
    public void exportUserExcel(HttpServletResponse response) {
        try {
            this.setExcelResponseProp(response, "用户列表");
            List<UserDO> userList = this.getUserList();
            EasyExcel.write(response.getOutputStream())
                    .head(UserDO.class)
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("用户列表")
                    .doWrite(userList);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 设置响应结果
     */
    private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }
    
    /**
     * 读取用户列表数据
     */
    private List<UserDO> getUserList() throws IOException {
        ObjectMapper objectMapper = new ObjectMapper();
        ClassPathResource classPathResource = new ClassPathResource("mock/users.json");
        InputStream inputStream = classPathResource.getInputStream();
        return objectMapper.readValue(inputStream, new TypeReference<List<UserDO>>() {
        });
    }
}

1.4 简单导入

@RestController
@RequestMapping("/excel")
@Api(tags = "EasyExcel")
public class ExcelController {
    
    @PostMapping("/import/user")
    public ResponseVO importUserExcel(@RequestPart(value = "file") MultipartFile file) {
        try {
            List<UserDO> userList = EasyExcel.read(file.getInputStream())
                    .head(UserDO.class)
                    .sheet()
                    .doReadSync();
            return ResponseVO.success(userList);
        } catch (IOException e) {
            return ResponseVO.error();
        }
    }
}

1.5 复杂导出

1.5.1 引言

由于 EasyPoi 支持嵌套对象导出,直接使用内置 @ExcelCollection 注解即可实现,遗憾的是 EasyExcel 不支持一对多导出,只能自行实现,通过此issues了解到,项目维护者建议通过自定义合并策略方式来实现一对多导出。
图片
解决思路:只需把订单主键相同的列中需要合并的列给合并了,就可以实现这种一对多嵌套信息的导出

1.5.2 自定义注解

创建一个自定义注解,用于标记哪些属性需要合并单元格,哪个属性是主键,用于判断是否需要合并以及合并的主键

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelMerge {
    /**
     * 是否合并单元格
     *
     * @return true || false
     */
    boolean merge() default true;

    /**
     * 是否为主键(即该字段相同的行合并)
     *
     * @return true || false
     */
    boolean isPrimaryKey() default false;
}

1.5.3 定义实体类

在需要合并单元格的属性上设置 @ExcelMerge 注解,二级表头通过设置 @ExcelProperty 注解中 value 值为数组形式来实现该效果:

@Data
public class OrderBO {
    @ExcelProperty(value = "订单主键")
    @ColumnWidth(16)
    @ExcelMerge(merge = true, isPrimaryKey = true)
    private String id;

    @ExcelProperty(value = "订单编号")
    @ColumnWidth(20)
    @ExcelMerge(merge = true)
    private String orderId;

    @ExcelProperty(value = "收货地址")
    @ExcelMerge(merge = true)
    @ColumnWidth(20)
    private String address;

    @ExcelProperty(value = "创建时间")
    @ColumnWidth(20)
    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelMerge(merge = true)
    private Date createTime;

    @ExcelProperty(value = {"商品信息", "商品编号"})
    @ColumnWidth(20)
    private String productId;

    @ExcelProperty(value = {"商品信息", "商品名称"})
    @ColumnWidth(20)
    private String name;

    @ExcelProperty(value = {"商品信息", "商品标题"})
    @ColumnWidth(30)
    private String subtitle;

    @ExcelProperty(value = {"商品信息", "品牌名称"})
    @ColumnWidth(20)
    private String brandName;

    @ExcelProperty(value = {"商品信息", "商品价格"})
    @ColumnWidth(20)
    private BigDecimal price;

    @ExcelProperty(value = {"商品信息", "商品数量"})
    @ColumnWidth(20)
    private Integer count;
}

1.5.4 数据映射与平铺

导出之前,需要对数据进行处理,将订单数据进行平铺,orderList为平铺前格式,exportData为平铺后格式:
在这里插入图片描述

1.5.5 自定义单元格合并策略

当 Excel 中两列主键相同时,合并被标记需要合并的列:

public class ExcelMergeStrategy implements RowWriteHandler {

    /**
     * 主键下标
     */
    private Integer primaryKeyIndex;

    /**
     * 需要合并的列的下标集合
     */
    private final List<Integer> mergeColumnIndexList = new ArrayList<>();

    /**
     * 数据类型
     */
    private final Class<?> elementType;

    public ExcelMergeStrategy(Class<?> elementType) {
        this.elementType = elementType;
    }

    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        // 判断是否为标题
        if (isHead) {
            return;
        }
        // 获取当前工作表
        Sheet sheet = writeSheetHolder.getSheet();
        // 初始化主键下标和需要合并字段的下标
        if (primaryKeyIndex == null) {
            this.initPrimaryIndexAndMergeIndex(writeSheetHolder);
        }
        // 判断是否需要和上一行进行合并
        // 不能和标题合并,只能数据行之间合并
        if (row.getRowNum() <= 1) {
            return;
        }
        // 获取上一行数据
        Row lastRow = sheet.getRow(row.getRowNum() - 1);
        // 将本行和上一行是同一类型的数据(通过主键字段进行判断),则需要合并
        if (lastRow.getCell(primaryKeyIndex).getStringCellValue().equalsIgnoreCase(row.getCell(primaryKeyIndex).getStringCellValue())) {
            for (Integer mergeIndex : mergeColumnIndexList) {
                CellRangeAddress cellRangeAddress = new CellRangeAddress(row.getRowNum() - 1, row.getRowNum(), mergeIndex, mergeIndex);
                sheet.addMergedRegionUnsafe(cellRangeAddress);
            }
        }
    }

    /**
     * 初始化主键下标和需要合并字段的下标
     *
     * @param writeSheetHolder WriteSheetHolder
     */
    private void initPrimaryIndexAndMergeIndex(WriteSheetHolder writeSheetHolder) {
        // 获取当前工作表
        Sheet sheet = writeSheetHolder.getSheet();
        // 获取标题行
        Row titleRow = sheet.getRow(0);
        // 获取所有属性字段
        Field[] fields = this.elementType.getDeclaredFields();
        // 遍历所有字段
        for (Field field : fields) {
            // 获取@ExcelProperty注解,用于获取该字段对应列的下标
            ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
            // 判断是否为空
            if (null == excelProperty) {
                continue;
            }
            // 获取自定义注解,用于合并单元格
            ExcelMerge excelMerge = field.getAnnotation(ExcelMerge.class);
            // 判断是否需要合并
            if (null == excelMerge) {
                continue;
            }
            for (int i = 0; i < fields.length; i++) {
                Cell cell = titleRow.getCell(i);
                if (null == cell) {
                    continue;
                }
                // 将字段和表头匹配上
                if (excelProperty.value()[0].equalsIgnoreCase(cell.getStringCellValue())) {
                    if (excelMerge.isPrimaryKey()) {
                        primaryKeyIndex = i;
                    }
                    if (excelMerge.merge()) {
                        mergeColumnIndexList.add(i);
                    }
                }
            }
        }

        // 没有指定主键,则异常
        if (null == this.primaryKeyIndex) {
            throw new IllegalStateException("使用@ExcelMerge注解必须指定主键");
        }
    }
}

1.5.6 定义接口

将自定义合并策略 ExcelMergeStrategy 通过 registerWriteHandler 注册上去

@RestController
@RequestMapping("/excel")
public class ExcelController {

    @GetMapping("/export/order")
    public void exportOrderExcel(HttpServletResponse response) {
        try {
            this.setExcelResponseProp(response, "订单列表");
            List<OrderDO> orderList = this.getOrderList();
            List<OrderBO> exportData = this.convert(orderList);
            EasyExcel.write(response.getOutputStream())
                    .head(OrderBO.class)
                    .registerWriteHandler(new ExcelMergeStrategy(OrderBO.class))
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("订单列表")
                    .doWrite(exportData);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 设置响应结果
     */
    private void setExcelResponseProp(HttpServletResponse response, String rawFileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode(rawFileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
    }
}

1.6 合并单元格写出Excel

现在我们考虑如何实现合并单元格。假如一个票据有多个明细行(数量不确定),导出Excel时要将“票据编号”、“创建时间”等列跨行合并。该如何实现呢?
在这里插入图片描述

1.6.1 EasyExcel中实现

1.6.1.1 数据类

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

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

1.6.1.2 @ContentLoopMerge

在这里插入图片描述

@Target({ElementType.FIELD}) 用于类属性上@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ContentLoopMerge {
	int eachRow() default 1; //每几行合并一次,默认为1即不合并
	int columnExtend() default 1; //向右跨几列,默认为1即当前列
}

先不使用@ContentLoopMerge,生成的Excel如下:
在这里插入图片描述

对第一列使用@ContentLoopMerge后,生成的Excel如下:

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

在这里插入图片描述

1.6.1.3 @OnceAbsoluteMerge

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

@Target({ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface OnceAbsoluteMerge{
	int firstRowIndex() default -1;//第一行索引
	int lastRowIndex() default -1;//最后一行索引
	int firstColumnlndex() default -1;//第一列索引
	int lastColumnIndex() default -1;//最后一列索引
}

使用示例

@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;
}

效果如下:
在这里插入图片描述

1.6.1.3 WriteHandler实现

EasyExcel 提供了与上面两个注解等效的WriteHandler实现,分别是OnceAbsoluteMergeStrategyLoopMergeStrategy。使用方式如下:

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());
}

1.6.2 自定义合并策略

1.6.2.1 常规实现

票据导出时因为每个票据的明细行数量不定,@ContentLoopMerge就不适用了。此时,去网上找找方案。
实现方式如下,核心逻辑为:
实现 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接口的实现,也存在上面指出的性能问题。

1.6.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;
  }
posted @   上善若泪  阅读(677)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示