【根据模板导出多sheet表格数据】

---

本文章主要是记录自己的一些心得和之前写过的一些功能，无意进行技术辩论，大神们留下足迹就好。

1. 根据模板导出多sheet表格数据

1. 先上代码

 @ApiOperation(value = "导出多个sheet数据接口")
    @GetMapping("/exportMulData")
    public void exportMulData(OaPurchaseContract oaPurchaseContract, HttpServletResponse response) throws Exception {
        //这里是你需要往里面添加数据的模板路径，放在templates下面即可
        try {
            //excel模板(其实这里使用缓冲流getResource或者getResourceAsStream()就行)
            String template = createNewFileByTemplate("/template/合同备案数据导出模板.xlsx");
            File file = ResourceUtils.getFile(template);
            try (FileInputStream fileInputStream = new FileInputStream(file); ByteArrayOutputStream bos = new ByteArrayOutputStream()) {
                //填充到sheet的数据
                List list1 = new ArrayList<Object>();
                List list22 = new ArrayList<Object>();
                // 获取需要导出的数据
                List<OaPurchaseContractVO> list = oaPurchaseContractService.selectContractFilingExportInfo(oaPurchaseContract);
                if(CollectionUtils.isNotEmpty(list)){
                    // 格式化日期
                    list= list.stream().map(oaPurchaseContractVO-> {
                        // 这里需要对导出的日期进行转换 不然会出现格式错误
                        oaPurchaseContractVO.setWriteDate(this.formatDate(oaPurchaseContractVO.getWriteDate()));
                        oaPurchaseContractVO.setStartDate(this.formatDate(oaPurchaseContractVO.getStartDate()));
                        oaPurchaseContractVO.setEndDate(this.formatDate(oaPurchaseContractVO.getEndDate()));
                        return oaPurchaseContractVO;
                    }).collect(Collectors.toList());
                }
                List<Map<String, Object>> maps = BeanUtil.objectList2ListMap(list);
                list1 = maps;

                // 合同结算数据
                List<OaPurchaseContractVOTwo> list2 = oaPurchaseContractService.selectContractEndCountExportInfo(oaPurchaseContract);
                List<Map<String, Object>> maps2 = BeanUtil.objectList2ListMap(list2);
                list22 = maps2;

                //原模板只有一个sheet，通过poi复制出需要的sheet个数的模板
                XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
                for (int i = 0; i < 1; i++) {
                    //复制模板，得到第i个sheet
                    workbook.cloneSheet(0, "合同备案数据(必填)");
                    workbook.cloneSheet(1, "合同结算数据(必填)");
                }
                // 最关键的操作就是这个 （将前面的sheet模板数据移除）
                workbook.removeSheetAt(0);
                workbook.removeSheetAt(0);
                //写到流里
                workbook.write(bos);
                byte[] bArray = bos.toByteArray();
                InputStream is = new ByteArrayInputStream(bArray);
                //输出文件路径
                String fileName = "采购合同" + System.currentTimeMillis() + ".xlsx";
                response.reset();
                // 设置文件对应后缀的ContentType
                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                // 返回的是流的形式
                response.setContentType("application/octet-stream; charset=utf-8");
                response.setHeader("Location", fileName);
                response.setHeader("Cache-Control", "max-age=0");
                // 这句的作用是会打开用户那边下载文件时的那个保存位置的框，如果文件名中有中文，建议编下码
                response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF8"));
                ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(is).build();
                for (int i = 0; i < 1; i++) {
                    WriteSheet writeSheet = EasyExcel.writerSheet("合同备案数据(必填)").build();
                    excelWriter.fill(list1, writeSheet);
                    WriteSheet writeSheet1 = EasyExcel.writerSheet("合同结算数据(必填)").build();
                    excelWriter.fill(list22, writeSheet1);
                }
                // 关闭流
                excelWriter.finish();
                
            } catch (IOException e) {
                e.printStackTrace();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 格式化日期得方法
     * @param writeDate
     * @return
     */
    private String formatDate(String writeDate) {
       return writeDate.replace("-","");
    }

    private String createNewFileByTemplate(String templateFilePath){
        // String templateFilePath = "/template/基金Call款通知书.docx";
        String originalFileName = IdGen.uuid() + "." + "xlsx";
        String originalFilePath = RuoYiConfig.getProfile() + File.separator + "temp";
        String fullFilePath = originalFilePath + File.separator + originalFileName;
        try(InputStream inputStream = this.getClass().getResourceAsStream(templateFilePath)){
            FileUtil.writeFromStream(inputStream, fullFilePath);

        }catch (IOException ioe){
            LogUtils.error("生成模板文件时异常!", ioe);
        }
        return fullFilePath;
    }

2. 模板展示

---

3. 数据构造

1.实体类创建

@ExcelProperty注解必须要加上，不然无法读取出来

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class OaPurchaseContractVO implements Serializable {
    @ExcelProperty("合同号")
    private String contractNumber;
    @ExcelProperty("备案公司")
    private String companyName;
    @ExcelProperty("签订部门代码")
    private String departmentCode;
    @ExcelProperty("签订部门名称")
    private String deptName;
    @ExcelProperty("签约客商代码")
    private String custerCode;
    @ExcelProperty("签约客商名称")
    private String custerName;
    @ExcelProperty("签订人工号")
    private String contractKey;
    @ExcelProperty("签订人名称")
    private String writerName;
    @ExcelProperty("合同起草人工号")
    private String startorKey;
    @ExcelProperty("合同起草人名称")
    private String startContractName;
    @ExcelProperty("合同末级审核人工号")
    private String endorKey;
    @ExcelProperty("合同末级审核人姓名")
    private String endReviewKey;
    @ExcelProperty("合同名称")
    private String contractName;
    @ExcelProperty("合同标的")
    private String contractBd;
    @ExcelProperty("合同大类")
    private String bigClass;
    @ExcelProperty("合同小类")
    private String smallClass;
    @JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd")
    @ExcelProperty(value = "合同签订日期")
    private String writeDate;
    @JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd")
    @ExcelProperty(value ="合同生效日期")
    private String startDate;
    @JsonFormat(locale="zh", timezone="GMT+8", pattern="yyyy-MM-dd")
    @ExcelProperty(value ="合同到期日")
    private String endDate;
    @ExcelProperty("是否多次结算")
    private String moreJs;
    @ExcelProperty("是否标准格式文本")
    private String sfBzText;
    @ExcelProperty("是否经过法审")
    private String wheatherFs;
    @ExcelProperty("是否阳光采购")
    private String sfYgCg;
    @ExcelProperty("未阳光采购理由")
    private String noYgCg;
    @ExcelProperty("未阳光采购其他理由")
    private String other;
    @ExcelProperty("是否招投标")
    private String sfZtb;
    @ExcelProperty("是否集团外贸易")
    private String sfJtw;
    @ExcelProperty("签约客商代码（多方）")
    private String one;
    @ExcelProperty("签约客商名称")
    private String oneName;
    @ExcelProperty("签约客商代码（多方）")
    private String two;
    @ExcelProperty("签约客商名称")
    private String twoName;
    @ExcelProperty("签约客商代码（多方）")
    private String three;
    @ExcelProperty("签约客商名称")
    private String threeName;
    @ExcelProperty("签约客商代码（多方）")
    private String four;
    @ExcelProperty("签约客商名称")
    private String fourName;
    @ExcelProperty("签约客商代码（多方）")
    private String five;
    @ExcelProperty("签约客商名称")
    private String fiveName;
    @ExcelProperty("签约客商代码（多方）")
    private String six;
    @ExcelProperty("签约客商名称")
    private String sixName;

}

---

2. sql数据构造(主要是需要把结果集别名映射到对应的实体类上)

select
                        a.contract_number as 'contractNumber',
                            (CASE a.investor_type
                                 WHEN 'fund' THEN
                                     ''
                        WHEN 'manager' THEN
                    (SELECT z_code FROM gp_base WHERE id=a.investor_id AND del_flag=0)
                ELSE
                    ''
                    END) as 'companyName',
            a.purchase_department_code as 'departmentCode',
            d.dept_name as 'deptName',
            a.custer_code as 'custerCode',
            a.custer_name as 'custerName',
            (SELECT ss.user_key FROM sys_user ss WHERE ss.id=a.contract_input_person) as 'contractKey',
            u.nick_name as 'writerName',
            (SELECT ss.user_key FROM sys_user ss WHERE ss.id=a.contract_input_person) as 'startorKey',
            u.nick_name as 'startContractName',
            (SELECT ss.user_key FROM sys_user ss WHERE ss.id=a.contrat_end_reviewer) as 'endorKey',
            s.nick_name as 'endReviewKey',
            a.contract_name as 'contractName',
            a.purchase_content as 'contractBd',
            t2.dict_label as 'bigClass',
            t3.dict_label as 'smallClass',
            date_format(a.contract_sign_date,'%Y-%m-%d') as 'writeDate',
            date_format(a.contract_using_date,'%Y-%m-%d') as 'startDate',
            date_format(a.contract_deadline,'%Y-%m-%d') as 'endDate',
            (CASE a.multiple_settlement
                WHEN 'Y' THEN
                    '是'
                WHEN 'N' THEN
                    '否'
                ELSE
                    ''
            END)as 'moreJs',
            (CASE a.standard_not_text
                WHEN 'Y' THEN
                    '是'
                WHEN 'N' THEN
                    '否'
                ELSE
                    ''
            END)as 'sfBzText',
            '是' as 'wheatherFs',
            (CASE a.whether_sunshine_purchase
                WHEN 'Y' THEN
                    '是'
                WHEN 'N' THEN
                    '否'
                ELSE
                    ''
            END)as 'sfYgCg',
            a.not_purchase_reason as 'noYgCg',
            a.not_purchase_other_reason as 'other',
            (CASE a.whether_bidding
                WHEN 'Y' THEN
                    '是'
                WHEN 'N' THEN
                    '否'
                ELSE
                    ''
            END)as 'sfZtb',
            (CASE a.whether_trade_outsite
                WHEN 'Y' THEN
                    '是'
                WHEN 'N' THEN
                    '否'
                ELSE
                    ''
            END)as 'sfJtw',
            (select custer_code FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 0,1)AS 'one',
            (select custer_name FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 0,1)AS 'oneName',
            (select custer_code FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 1,1)AS 'two',
            (select custer_name FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 1,1)AS 'twoName',
            (select custer_code FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 2,1)AS 'three',
            (select custer_name FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 2,1)AS 'threeName',
            (select custer_code FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 3,1)AS 'four',
            (select custer_name FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 3,1)AS 'fourName',
            (select custer_code FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 4,1)AS 'five',
            (select custer_name FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 4,1)AS 'fiveName',
            (select custer_code FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 5,1)AS 'six',
            (select custer_name FROM oa_purchase_contract_son WHERE purchase_contract_id=a.id and del_flag=0 limit 5,1)AS 'sixName'
            from oa_purchase_contract a
            LEFT JOIN custer_manage c on c.custer_code = a.custer_code and c.del_flag='0'
            LEFT JOIN sys_user u on u.id = a.contract_input_person
            LEFT JOIN sys_user s on s.id = a.contrat_end_reviewer
            left join sys_dept d on a.purchase_department = d.id
            left join sys_dict_data t1 on a.act_status = t1.dict_value and t1.dict_type = 'work_flow_status'
            left join sys_dict_data t2 on a.contract_big_class = t2.dict_value and t2.dict_type = 'contract_big_type'
            left join sys_dict_data t3 on a.contract_small_class = t3.dict_value and t3.dict_type = concat('contract_big_type_',a.contract_big_class)
            <where>
                a.del_flag = '0'
                <if test="id != null  and id != ''"> and a.id = #{id}</if>
                <if test="contractNumber != null  and contractNumber != ''"> and a.contractNumber like concat('%',#{contractNumber},'%')</if>
                <if test="contractName != null  and contractName != ''"> and a.contract_name like concat('%',#{contractName},'%') </if>
                <if test="contractSmallClass != null  and contractSmallClass != ''"> and a.contract_small_class like concat('%',#{contractSmallClass},'%')</if>
                <if test="custerCode != null  and custerCode != ''"> and a.custer_code = #{custerCode}</if>
                <if test="investorType != null and investorType != ''">and a.investor_type =#{investorType}</if>
                <if test="contractYear != null  and contractYear != ''"> and LEFT(RIGHT(a.contract_number,5),2)=#{contractYear} </if>
                <if test="investorId != null and investorId != ''">and a.investor_id =#{investorId}</if>
                <if test="busId != null  and busId != ''">
                    <choose>
                        <when test='_parameter.busId == "HT_GL"'>
                            and a.bus_id is null OR a.act_status='W'
                        </when>
                        <otherwise>
                            and a.bus_id = #{busId}
                        </otherwise>
                    </choose>
                </if>
            </where>
            order by a.update_time desc

---

上面主要是针对有模板的情况下进行多sheet导出

下面介绍无模块，手动构造模块进行多sheet导出（适合字段少的）

---

2. 无模板多sheet导出

1. code部分

// 方式一(自定义导出,无模板,字段多不建议)
    @ApiOperation(value="导出多个sheet数据接口")
    @GetMapping("/exportMulData")
    public void exportMulData(OaPurchaseContract oaPurchaseContract,HttpServletResponse response) throws Exception {
        oaPurchaseContractService.exportMulData(oaPurchaseContract,response);
    }

public void exportMulData(OaPurchaseContract oaPurchaseContract, HttpServletResponse response) throws Exception {
        String tableName = "合同备案";
        // 获取需要导出的数据
        List<OaPurchaseContractVO> list = oaPurchaseContractDao.selectContractFilingExportInfo(oaPurchaseContract);
        List<Map<String, Object>> maps = BeanUtil.objectList2ListMap(list);
        List<Map<String, Object>> dataList = maps;
        // 合同结算数据
        List<OaPurchaseContractVOTwo> list2 = oaPurchaseContractDao.selectContractEndCountExportInfo(oaPurchaseContract);
        List<Map<String, Object>> maps2 = BeanUtil.objectList2ListMap(list2);
        List<Map<String, Object>> dataList2 = maps2;
        List<String[]> titleList = new ArrayList<>();
        List<List<List<String>>> contentList = new ArrayList<>();
        // 需要展示的列
        List<String> fieldList1 = Lists.newArrayList("contractNumber", "companyName", "departmentCode", "deptName", "custerCode", "custerName",
                "contractKey", "writerName", "startorKey", "startContractName", "endorKey", "endReviewKey", "contractName", "contractBd",
                "bigClass", "smallClass", "writeDate", "startDate", "endDate", "moreJs",
                "sfBzText", "wheatherFs", "sfYgCg", "noYgCg", "other", "sfZtb", "sfJtw",
                "one", "oneName", "two", "twoName", "three", "threeName",
                "four", "fourName",
                "five", "fiveName", "six", "sixName"
        );
        List<String> fieldList2 = Lists.newArrayList("contractNumber", "weatherPay", "isTmpMoney", "custerType", "custerCode", "custerName",
                "bz", "sfHs", "contractAllMoney", "preMoney", "endMoney", "suiRail", "ydzBl", "jsgys",
                "jsgysAmount", "jsYhName", "fkType", "isHasMoney", "hasPost", "hasSomePost",
                "allPost", "allPostEnd", "allPostEndSome", "allEnd", "remark"
        );
        // 匹配数据
        List<List<String>> results1 = matchFieldData(dataList, fieldList1);
        List<List<String>> results2 = matchFieldData(dataList2, fieldList2);
        contentList.add(results1);
        contentList.add(results2);
        // 设置表题
        String[] title1 = {"合同号*", "备案公司*", "签订部门代码*", "签订部门名称", "签约客商代码*", "签约客商名称",
                "签订人工号*", "签订人名称*", "合同起草人工号*", "合同起草人名称*", "合同末级审核人工号*",
                "合同末级审核人姓名", "合同名称*", "合同标的*", "合同大类*", "合同小类*", "合同签订日期(yyyyMMdd)*",
                "合同生效日期(yyyyMMdd)*", "合同到期日(yyyyMMdd)*", "是否多次结算(Y-是，N-否)", "是否标准格式文本(Y-是，N-否)", "是否经过法审(Y-是，N-否)", "是否阳光采购(Y-是，N-否)",
                "未阳光采购理由", "未阳光采购其他理由(未阳光采购理由=QT时必填)", "是否招投标(Y-是，N-否)", "是否集团外贸易(Y-是，N-否)", "签约客商代码（多方）*", "签约客商名称",
                "签约客商代码（多方）", "签约客商名称", "签约客商代码（多方）", "签约客商名称", "签约客商代码（多方）", "签约客商名称",
                "签约客商代码（多方）", "签约客商名称", "签约客商代码（多方）", "签约客商名称"
        };
        String[] title2 = {"合同号*", "是否转付款", "是否暂估合同金额", "结算供应商类型（K-供应商/L-临时客商/Y-员工）*", "结算供应商代码*", "结算供应商名称", "币种*", "是否含税*", "合同总金额*", "预付款金额",
                "尾款金额", "税率（%）", "溢短装比例（%）", "结算供应商税号", "结算供应商账号", "结算供应商银行名称", "付款方式", "是否已有执行金额(Y-是，N-否)", "已报支金额(含税)",
                "已报支金额(不含税)", "已报支预付款金额", "已核销预付款金额", "已预留尾款金额", "已核销尾款金额", "备注"
        };
        titleList.add(title1);
        titleList.add(title2);
        try {
            ExcelExportUtil.exportExcel(tableName, titleList, contentList, response, "oaPurchase");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private List<List<String>> matchFieldData(List<Map<String, Object>> dataList, List<String> fieldList) {
        return ListUtils
                .emptyIfNull(dataList).stream().filter(Objects::nonNull).map(e -> ListUtils.emptyIfNull(fieldList)
                        .stream().map(f -> this.getString(e, f)).collect(Collectors.toList()))
                .collect(Collectors.toList());
    }

    public static <K> String getString(final Map<? super K, ?> map, final K key) {
        if (map != null) {
            final Object answer = map.get(key);
            return answer != null ? answer.toString() : "";
        }
        return null;
    }


    public List<OaPurchaseContractVO> getFillData(OaPurchaseContract oaPurchaseContract) {
        List<OaPurchaseContractVO> fillDataList = new ArrayList<>();

        List<OaPurchaseContractVO> list = oaPurchaseContractDao.selectContractFilingExportInfo(oaPurchaseContract);
        for (int i = 0; i < list.size(); i++) {
            // 构建数据，这里是每个字段赋值然后放到一个集合里，
            OaPurchaseContractVO fillData = OaPurchaseContractVO.builder()
                    .contractNumber(list.get(i).getContractNumber())
                    .companyName(list.get(i).getCompanyName())
                    .departmentCode(list.get(i).getDepartmentCode())
                    .deptName(list.get(i).getDeptName())
                    .custerCode(list.get(i).getCusterCode())
                    .custerName(list.get(i).getCusterName())
                    .contractKey(list.get(i).getContractKey())
                    .writerName(list.get(i).getWriterName())
                    .startorKey(list.get(i).getStartorKey())
                    .startContractName(list.get(i).getStartContractName())
                    .endorKey(list.get(i).getEndorKey())
                    .endReviewKey(list.get(i).getEndReviewKey())
                    .contractName(list.get(i).getContractName())
                    .contractBd(list.get(i).getContractBd())
                    .bigClass(list.get(i).getBigClass())
                    .smallClass(list.get(i).getSmallClass())
                    .writeDate(list.get(i).getWriteDate())
                    .startDate(list.get(i).getStartDate())
                    .endDate(list.get(i).getEndDate())
                    .moreJs(list.get(i).getMoreJs())
                    .sfBzText(list.get(i).getSfBzText())
                    .wheatherFs(list.get(i).getWheatherFs())
                    .sfYgCg(list.get(i).getSfYgCg())
                    .noYgCg(list.get(i).getNoYgCg())
                    .other(list.get(i).getOther())
                    .sfZtb(list.get(i).getSfZtb())
                    .sfJtw(list.get(i).getSfJtw())
                    .one(list.get(i).getOne())
                    .oneName(list.get(i).getOneName())
                    .two(list.get(i).getTwo())
                    .twoName(list.get(i).getTwoName())
                    .three(list.get(i).getThree())
                    .threeName(list.get(i).getThreeName())
                    .four(list.get(i).getFour())
                    .fourName(list.get(i).getFourName())
                    .five(list.get(i).getFive())
                    .fiveName(list.get(i).getFiveName())
                    .six(list.get(i).getSix())
                    .sixName(list.get(i).getSix())
                    .build();
            fillDataList.add(fillData);
        }
        return fillDataList;
    }

    public List<OaPurchaseContractVO> selectContractFilingExportInfo(OaPurchaseContract oaPurchaseContract) {
        return oaPurchaseContractDao.selectContractFilingExportInfo(oaPurchaseContract);
    }

    public List<OaPurchaseContractVOTwo> selectContractEndCountExportInfo(OaPurchaseContract oaPurchaseContract) {
        return oaPurchaseContractDao.selectContractEndCountExportInfo(oaPurchaseContract);
    }

---

3. 效果演示

结言

导出的案例很多，但是当自己在用的时候还是自己熟悉的代码比较舒服。