easyExcel模板填充数据并合并sheet

最近给了一个新的需求，就是选择多个发货记录后导出发货记录及发货详情，要求导出的Excel格式如下

一个表格表示一条发货记录，里面的详情是不确定条数的。
因为我之前的其他的导出都是使用easyExcel的模板填充做的，所以我想这个能不能也用模板填充实现。
但存在一个问题就是发货记录是不确定个数的，但模板是写死的。
后面看到一些文章，发现一个思路：可以将多条发货记录使用模板生成多个sheet，然后再将多个sheet合并成一个。

一、首先是实现将一个模板填充多个sheet（参考 https://blog.csdn.net/once10/article/details/119616071）

1、实体对象

TrackingdeliveryExcelVO

package com.hx.admin.erp.response;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 * 物流发货单查询返回VO
 * </p>
 *
 * @author wxb
 * @since 2021-10-18
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class TrackingdeliveryExcelVO implements Serializable {

    private static final long serialVersionUID = -2334403249810086977L;
    /**
     * id
     */
    @ApiModelProperty("id")
    private Integer id;

    /**
     * 物流公司名称
     */
    @ApiModelProperty("物流公司名称")
    private String logisticsName;

    /**
     * 物流公司联系人
     */
    @ApiModelProperty("物流公司联系人")
    private String logisticsUserName;

    /**
     * 物流公司联系电话
     */
    @ApiModelProperty("物流公司联系电话")
    private String logisticsPhone;

    /**
     * 物流公司所在区域
     */
    @ApiModelProperty("物流公司所在区域")
    private String logisticsArea;

    /**
     * 保价
     */
    @ApiModelProperty("保价")
    private BigDecimal parcel;

    /**
     * 总件数
     */
    @ApiModelProperty("总件数")
    private Integer snum;

    /**
     * 总运费
     */
    @ApiModelProperty("总运费")
    private BigDecimal totel;

    /**
     * 收货人
     */
    @ApiModelProperty("收货人")
    private String consigneeName;

    /**
     * 收货人电话
     */
    @ApiModelProperty("收货人电话")
    private String consigneeMobile;

    /**
     * 收货地址
     */
    @ApiModelProperty("收货地址")
    private String address;

    /**
     * 详情列表
     */
    @ApiModelProperty("详情列表")
    private List<TrackingdeliveryDetailExcelVO> detailList;

}

TrackingdeliveryDetailExcelVO

package com.hx.admin.erp.response;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.math.BigDecimal;

/**
 * 物流发货单详情查询返回VO
 *
 * @author wxb
 * @since 2021-10-18
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class TrackingdeliveryDetailExcelVO implements Serializable {

    private static final long serialVersionUID = 332783842414319355L;

    /**
     * 发货单ID
     */
    @ApiModelProperty("标识")
    private Integer id;

    /**
     * 工厂-仓库名称
     */
    @ApiModelProperty("工厂-仓库名称")
    private String warehouseName;

    /**
     * 仓库地址
     */
    @ApiModelProperty("仓库地址")
    private String warehouseAddress;

    /**
     * 商品名称
     */
    @ApiModelProperty("商品名称")
    private String goodsName;

    /**
     * 规格
     */
    @ApiModelProperty("规格")
    private String specif;

    /**
     * 销售单位
     */
    @ApiModelProperty("单位")
    private String unit;

    /**
     * 数量
     */
    @ApiModelProperty("数量")
    private BigDecimal num;

    /**
     * 客户名称
     */
    @ApiModelProperty("客户名称")
    private String consigneeName;

}

2、导出的模板(记得将sheet删到只剩一个，不然合并sheet时会有问题)

3、导出工具类

package com.hx.qypfs.excel.utils;

import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.hx.qypfs.excel.converter.LocalDateTimeConverter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

@Slf4j
public class ExcelUtils {

    /**
     * 模板填充导出
     * 这里的逻辑是将列表中的数据分表生成多个sheet表，再将多个sheet合并成一个
     *
     * @param list             列表数据
     * @param excelPath        要导出的表格的路径
     * @param excelName        要导出的表格名称
     * @param templateName     templates目录下的模板名称（ex: 模板.xlsx）
     * @param <T>
     */
    public static <T> void downloadExcelTemplate(List<TrackingdeliveryExcelVO> list,
                                                 String excelPath,
                                                 String excelName,
                                                 String templateName) throws Exception {

        Assert.isTrue(StrUtil.isNotEmpty(excelName), "表格名称不能为空");
        Assert.isTrue(StrUtil.isNotEmpty(templateName), "模板名称不能为空");
        Assert.isTrue(templateName.endsWith(".xlsx"), "模板名称后缀名不为.xlsx");

        ExcelWriter excelWriter = null;

        try (ByteArrayOutputStream bos = new ByteArrayOutputStream();
             // 模板文件 使用这种方式才不会在打包成jar后获取不到resources中的模板资源
             InputStream resourceAsStream = ExcelUtils.class.getResourceAsStream(StrUtil.format("/templates/{}", templateName))) {

            File file = new File(excelPath);
            while (!file.exists()) {
                file.mkdirs();
            }
            int sheetNum = list.size();
            // 原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook xssWorkbook = new XSSFWorkbook(resourceAsStream);
            // 设置模板的第一个sheet的名称
            xssWorkbook.setSheetName(0, "sheet1");
            for (int i = 1; i < sheetNum; i++) {
                //复制模板，得到第i个sheet
                int num = i + 1;
                xssWorkbook.cloneSheet(0, "sheet" + num);
            }

            //写到流里
            xssWorkbook.write(bos);
            byte[] bArray = bos.toByteArray();
            // 不需要调用close()关闭流，在调用ExcelWriter.finish()会被关闭
            InputStream is = new ByteArrayInputStream(bArray);
            FileOutputStream fileOutputStream = new FileOutputStream(StrUtil.format("{}{}{}", excelPath, File.separator, excelName));

            // 读取模板并进行填充
            excelWriter = EasyExcel.write(fileOutputStream).registerConverter(new LocalDateTimeConverter()).withTemplate(is).build();
            Assert.notNull(excelWriter, "模板文件不存在");
            // 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            // 循环插入每个sheet
            for (int i = 0; i < sheetNum; i++) {
                int num = i + 1;
                WriteSheet writeSheet = EasyExcel.writerSheet("sheet" + num).build();
                excelWriter.fill(list.get(i).getDetailList(), fillConfig, writeSheet);
                excelWriter.fill(list.get(i), writeSheet);
            }
            // 这里必须要写finish，不然只会导出空文件
            excelWriter.finish();
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
}

这样就基本能将列表数据导出成一个有多个sheet的Excel表格

4、导出效果：

二、多个sheet合并成一个（参考https://blog.csdn.net/weixin_44009447/article/details/116708514）

1、合并sheet工具类

package com.hx.qypfs.excel.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;

/**
 * 来源 https://blog.csdn.net/weixin_44009447/article/details/116708514
 *
 * @Author csdn 博主:Keson Z
 * @Description POI导出excel工具类
 * @Date 17:16 2021/5/11
 * @Param
 * @return
 **/
public class POIUtil {
    /**
     * @return void
     * @Author csdn 博主:Keson Z
     * @Description 拷贝sheet（表）
     * @Date 17:16 2021/5/11
     * @Param [targetSheet, sourceSheet, targetWork, sourceWork, startRow, cellRangeAddressExcelVoList]
     **/
    public static void copySheet(Sheet targetSheet, XSSFSheet sourceSheet,
                                 Workbook targetWork, Workbook sourceWork, int startRow) {
        if (targetSheet == null || sourceSheet == null || targetWork == null || sourceWork == null) {
            throw new IllegalArgumentException("调用PoiUtil.copySheet()方法时，targetSheet、sourceSheet、targetWork、sourceWork都不能为空，故抛出该异常！");
        }

        // 设置单元格默认宽度
        targetSheet.setDefaultColumnWidth(25);
        // 复制源表中的行
        for (int i = sourceSheet.getFirstRowNum(); i <= sourceSheet.getLastRowNum(); i++) {
            Row sourceRow = sourceSheet.getRow(i);
            // 创建新的row
            Row targetRow = targetSheet.createRow(i + startRow);
            if (sourceRow != null) {
                copyRow(targetRow, sourceRow,
                        targetWork, sourceWork);
            }
        }
    }


    /**
     * @return void
     * @Author csdn 博主:Keson Z
     * @Description 拷贝row(行)
     * @Date 17:17 2021/5/11
     * @Param [targetRow, sourceRow, targetWork, sourceWork]
     **/
    public static void copyRow(Row targetRow, Row sourceRow,
                               Workbook targetWork, Workbook sourceWork) {
        if (targetRow == null || sourceRow == null || targetWork == null || sourceWork == null) {
            throw new IllegalArgumentException("调用PoiUtil.copyRow()方法时，targetRow、sourceRow、targetWork、sourceWork、targetPatriarch都不能为空，故抛出该异常！");
        }

        //设置行高
        targetRow.setHeight(sourceRow.getHeight());

        for (int i = sourceRow.getFirstCellNum(); i < sourceRow.getLastCellNum(); i++) {
            Cell sourceCell = sourceRow.getCell(i);
            Cell targetCell = null;
            if (sourceCell != null) {
                if (targetCell == null) {
                    targetCell = targetRow.createCell(i);
                }
                //拷贝单元格，包括内容和样式
                copyCell(targetCell, sourceCell, targetWork, sourceWork);
            }
        }
    }

    /**
     * @return void
     * @Author csdn 博主:Keson Z
     * @Description 拷贝cell（单元格）
     * @Date 17:18 2021/5/11
     * @Param [targetCell, sourceCell, targetWork, sourceWork]
     **/
    public static void copyCell(Cell targetCell, Cell sourceCell, Workbook targetWork, Workbook sourceWork) {
        if (targetCell == null || sourceCell == null || targetWork == null || sourceWork == null) {
            throw new IllegalArgumentException("调用PoiUtil.copyCell()方法时，targetCell、sourceCell、targetWork、sourceWork都不能为空，故抛出该异常！");
        }

        CellStyle targetCellStyle = targetWork.createCellStyle();
        // 拷贝样式
        targetCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
        targetCell.setCellStyle(targetCellStyle);
        try {
            targetCell.setCellValue(sourceCell.getStringCellValue());
        } catch (Exception e) {
            targetCell.setCellValue(sourceCell.getNumericCellValue());
        }
    }
}

2、导出工具类（和上面导出工具类是同一个，只是添加了部分合并sheet的代码）

package com.hx.qypfs.excel.utils;

import cn.hutool.core.lang.Assert;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.hx.qypfs.excel.converter.LocalDateTimeConverter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;

@Slf4j
public class ExcelUtils {

    /**
     * 模板填充导出
     * 这里的逻辑是将列表中的数据分表生成多个sheet表，再将多个sheet合并成一个
     *
     * @param list             列表数据
     * @param excelPath        要导出的表格的路径
     * @param excelName        要导出的表格名称
     * @param templateName     templates目录下的模板名称（ex: 模板.xlsx）
     * @param <T>
     */
    public static <T> void downloadExcelTemplate(List<TrackingdeliveryExcelVO> list,
                                                 String excelPath,
                                                 String excelName,
                                                 String templateName) throws Exception {

        Assert.isTrue(StrUtil.isNotEmpty(excelName), "表格名称不能为空");
        Assert.isTrue(StrUtil.isNotEmpty(templateName), "模板名称不能为空");
        Assert.isTrue(templateName.endsWith(".xlsx"), "模板名称后缀名不为.xlsx");

        ExcelWriter excelWriter = null;

        try (ByteArrayOutputStream bos = new ByteArrayOutputStream();
             // 模板文件 使用这种方式才不会在打包成jar后获取不到resources中的模板资源
             InputStream resourceAsStream = ExcelUtils.class.getResourceAsStream(StrUtil.format("/templates/{}", templateName))) {

            File file = new File(excelPath);
            while (!file.exists()) {
                file.mkdirs();
            }
            int sheetNum = list.size();
            // 原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
            XSSFWorkbook xssWorkbook = new XSSFWorkbook(resourceAsStream);
            // 设置模板的第一个sheet的名称
            xssWorkbook.setSheetName(0, "sheet1");
            for (int i = 1; i < sheetNum; i++) {
                //复制模板，得到第i个sheet
                int num = i + 1;
                xssWorkbook.cloneSheet(0, "sheet" + num);
            }

            //写到流里
            xssWorkbook.write(bos);
            byte[] bArray = bos.toByteArray();
            // 不需要调用close()关闭流，在调用ExcelWriter.finish()会被关闭
            InputStream is = new ByteArrayInputStream(bArray);
            FileOutputStream fileOutputStream = new FileOutputStream(StrUtil.format("{}{}{}", excelPath, File.separator, excelName));
		
            // 读取模板并进行填充
            excelWriter = EasyExcel.write(fileOutputStream).registerConverter(new LocalDateTimeConverter()).withTemplate(is).build();
            Assert.notNull(excelWriter, "模板文件不存在");
            // 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            // 循环插入每个sheet
            for (int i = 0; i < sheetNum; i++) {
                int num = i + 1;
                WriteSheet writeSheet = EasyExcel.writerSheet("sheet" + num).build();
                excelWriter.fill(list.get(i).getDetailList(), fillConfig, writeSheet);
                excelWriter.fill(list.get(i), writeSheet);
            }
			// ======================== 新添加代码 start ==========================
            // 合并多个sheet
            Workbook sourceWorkbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
            Workbook targeWorkbook = new SXSSFWorkbook();
            Workbook workbook1 = ExcelUtils.mergeWorkSheet(targeWorkbook, sourceWorkbook, sheetNum);
            // 合并单元格并设置标题
            mergedCellAndSetTitle(list, workbook1);
            excelWriter.writeContext().writeWorkbookHolder().setWorkbook(workbook1);
            // ======================== 新添加代码 end ==========================

            // 这里必须要写finish，不然只会导出空文件
            excelWriter.finish();
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
// ======================== 新添加代码 start ==========================
    /**
     * @Description 合并sheet
     * @Date 10:39 2021/5/11
     * @Param [targetWorkbook, sourceWorkbook]
     * @return org.apache.poi.ss.usermodel.Workbook
     **/
    public static Workbook mergeWorkSheet(Workbook targetWorkbook, Workbook sourceWorkbook,int size){
        try{
            //表合并后新表名称
            Sheet targetSheet = targetWorkbook.createSheet("sheet1");
            int secondSourceSheetLen = 0;
            SXSSFWorkbook sw = (SXSSFWorkbook) sourceWorkbook;
            for (int i = 0; i < size; i++) {
                XSSFSheet sheetAt1 = sw.getXSSFWorkbook().getSheetAt(i);
                POIUtil.copySheet(targetSheet, sheetAt1, targetWorkbook, sourceWorkbook,secondSourceSheetLen);
                secondSourceSheetLen += sheetAt1.getPhysicalNumberOfRows();
            }
            return targetWorkbook;
        }catch (Exception e){
            log.error("Workbook合并出错",e);
            return null;
        }
    }
    /**
     * 合并单元格并设置标题
     *
     * @param list
     * @param workbook1
     * @return void
     * @author weixiaobin
     * @date 2021/10/19 9:44
     */
    private static void mergedCellAndSetTitle(List<TrackingdeliveryExcelVO> list, Workbook workbook1) {
        Sheet sheetAt = workbook1.getSheetAt(0);
        //
        CellStyle cellStyle = workbook1.createCellStyle();
        // 居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 字体
        Font font = workbook1.createFont();
        font.setFontName("宋体");
        font.setBold(true);
        font.setFontHeightInPoints((short) 28);
        cellStyle.setFont(font);
        // 添加标题
        sheetAt.addMergedRegion(new CellRangeAddress(0, 0, 1, 8));
        Cell cell = sheetAt.createRow(0).createCell(1);
        cell.setCellValue("物流发货记录");
        cell.setCellStyle(cellStyle);
        // 修改固定列宽
        sheetAt.setColumnWidth(6,  7 * 256);
        sheetAt.setColumnWidth(7,  14 * 256);

        int initRow = 2;
        for (int i = 0; i < list.size(); i++) {
            // 当前详情列表大小
            int currentSize = list.get(i).getDetailList().size();
            if (0 != i) {
                int nextSize = list.get(i - 1).getDetailList().size();
                initRow = initRow + 8 + nextSize;
            }
            sheetAt.addMergedRegion(new CellRangeAddress(initRow, initRow, 1, 3));
            sheetAt.addMergedRegion(new CellRangeAddress(initRow, initRow, 4, 5));
            sheetAt.addMergedRegion(new CellRangeAddress(initRow, initRow, 6, 7));
            sheetAt.addMergedRegion(new CellRangeAddress(initRow + 1, initRow + 1, 1, 3));
            sheetAt.addMergedRegion(new CellRangeAddress(initRow + 3 + currentSize, initRow + 3 + currentSize, 1, 2));
            sheetAt.addMergedRegion(new CellRangeAddress(initRow + 3 + currentSize, initRow + 3 + currentSize, 6, 7));
            sheetAt.addMergedRegion(new CellRangeAddress(initRow + 4 + currentSize, initRow + 4 + currentSize, 1, 2));
            sheetAt.addMergedRegion(new CellRangeAddress(initRow + 4 + currentSize, initRow + 4 + currentSize, 4, 8));
        }
    }
    // ======================== 新添加代码 end ==========================
}

3、效果

https://blog.csdn.net/once10/article/details/119616071 (一个模板动态多个sheet填充)
https://blog.csdn.net/weixin_44009447/article/details/116708514(多个sheet合并成一个)