EasyExcel多个sheet页里多行表头,解决:使用table去写入

需求的效果:

多个sheet,并且每个sheet里是多行表头,这是使用EasyExcel的table去写入

 

1.接口试例

    /**
     * 预付款导出
     */
    @GetMapping("/export/excel")
    public Result<Void, Void> export(HttpServletRequest request, String ids, HttpServletResponse response) throws IOException {

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        LocalDate now = LocalDate.now();
        //设置表名称,日期+表名
        String fileName = now + "测试";
        // 这里URLEncoder.encode可以防止中文乱码 和easyexcel没有关系
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");


        //这里是接收到前端传来的id的字符串,和前端规定用逗号分隔
        List<String> strings = Arrays.asList(ids.split(","));
        if (strings.size() <= 0) {
            return FtResultUtil.error(ExceptionEnum.PARAM_ERROR);
        }

        //查询数据库后需要导出的集合
        List<ExportPrepaymentsVO> list = purchaseContractPrepaymentsService.getProductContractDetails(ids);
        ExcelWriter excelWriter = null;


        try {
            //定义 ExcelWriter
            excelWriter = null;

            //写到那里,这里是转成输出流,写到响应里
            excelWriter = EasyExcel.write(response.getOutputStream()).build();

            //多sheet页,list的每个元素就是一个sheet页
            for (int i = 1; i <= list.size(); i++) {

                //取到第一行数据
                ExportPrepaymentsVO exportPrepaymentsVO1 = list.get(i - 1);

                //第一行数据
                List<DownloadExportPrepaymentsVO> result1 = new ArrayList<>();
                DownloadExportPrepaymentsVO purchaseContractPrepayments1 = new DownloadExportPrepaymentsVO();
                purchaseContractPrepayments1.setContractName(exportPrepaymentsVO1.getContractName());
                purchaseContractPrepayments1.setContractNumber(exportPrepaymentsVO1.getContractNumber());
                purchaseContractPrepayments1.setSupplierName(exportPrepaymentsVO1.getSupplierName());
                purchaseContractPrepayments1.setUpdateUser(exportPrepaymentsVO1.getUpdateUser());
                purchaseContractPrepayments1.setUpdateTime(exportPrepaymentsVO1.getUpdateTime());
                result1.add(purchaseContractPrepayments1);


                //每个sheet页第二行的数据
                List<PurchaseContractPrepayments> purchaseContractPrepayments = exportPrepaymentsVO1.getPurchaseContractPrepayments();

                List<DownloadPurchaseContractPrepaymentsVO> result2 = purchaseContractPrepayments.stream().map(x -> {
                    DownloadPurchaseContractPrepaymentsVO downloadPurchaseContractPrepaymentsVO = new DownloadPurchaseContractPrepaymentsVO();
                    BeanUtils.copyProperties(x, downloadPurchaseContractPrepaymentsVO);

                    return downloadPurchaseContractPrepaymentsVO;

                }).collect(Collectors.toList());

                //TODO 第三行对账数据  还没有



                //创建一个sheet页,参数一sheet页的下标 参数2sheet的名字,名字重复无法创建sheet,会覆盖
                WriteSheet writeSheet = EasyExcel.writerSheet(i, purchaseContractPrepayments1.getContractName()+i).build();

                //使用table去写入多行表头
                WriteTable writeTable = EasyExcel.writerTable(0).head(DownloadExportPrepaymentsVO.class).needHead(true).build();
                WriteTable writeTable2 = EasyExcel.writerTable(1).head(DownloadPurchaseContractPrepaymentsVO.class).needHead(true).build();


                //写
                excelWriter.write(result1, writeSheet, writeTable);
                excelWriter.write(result2, writeSheet, writeTable2);

            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            // 放进finally里  就算跑异常 也会关流
            excelWriter.finish();
        }


        return null;
    }

 2.导出的table模板

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;



@Data
public class DownloadExportPrepaymentsVO {
    /**
     * 合同名称
     */
    @ExcelProperty(value = {"采购合同名称"},index = 1)
    private String contractName;
    /**
     * 合同编号
     */
    @ExcelProperty(value = {"合同编号"},index = 0)
    private String contractNumber;
    /**
     * 供应商名称
     */
    @ExcelProperty(value = {"供应商名称"},index = 2)
    private String supplierName;
    /**
     * 最后操作人
     */
    @ExcelProperty(value = {"最后操作人"},index = 3)
    private String updateUser;
    /**
     * 最后操作时间
     */
    @ExcelProperty(value = {"最后操作时间"},index = 4)
    private String updateTime;

}

import java.math.BigDecimal;

@Data
public class DownloadPurchaseContractPrepaymentsVO {

    /**
     * 预付款编号
     */

    @ColumnWidth(25)
    @ExcelProperty(value = {"预付款编号"},index = 0)
    private String prepaymentsNumnber;

    /**
     * 预付款金额
     */

    @ColumnWidth(25)
    @ExcelProperty(value = {"预付款金额"},index = 1)
    private BigDecimal prepaymentsAmount;

    /**
     * 已使用金额
     */

    @ColumnWidth(25)
    @ExcelProperty(value = {"已使用金额"},index = 2)
    private BigDecimal usedAmount;

    /**
     * 未使用金额
     */

    @ColumnWidth(25)
    @ExcelProperty(value = {"待付款金额"},index = 3)
    private BigDecimal unusedAmount;

    /**
     * 预付款状态
     */

    @ColumnWidth(25)
    @ExcelProperty(value = {"预付款状态"},index = 4)
    private String prepaymentsStatus;

    /**
     * 申请人
     */

    @ColumnWidth(25)
    @ExcelProperty(value = {"申请人"},index = 5)
    private String applicant;

    /**
     * 最后操作人
     */

    @ColumnWidth(25)
    @ExcelProperty(value = {"最后操作人"},index = 6)
    private String updateUser;
    /**
     * 最后操作时间
     */
    @ColumnWidth(25)
    @ExcelProperty(value = {"最后操作时间"},index = 7)
    private String updateTime;


}

3.查完数据库需要导出的list的Class类

@Data
public class ExportPrepaymentsVO {
    /**
     * 合同名称
     */
    private String contractName;
    /**
     * 合同编号
     */
    private String contractNumber;
    /**
     * 供应商名称
     */
    private String supplierName;
    /**
     * 最后操作人
     */
    private String updateUser;
    /**
     * 最后操作时间
     */
    private String updateTime;

    /**
     * 预付款详情
     */
    private List<PurchaseContractPrepayments> purchaseContractPrepayments;
    /**
     * @Todo 使用记录
     */

}