EasyExcel根据模板动态导出一个Excel多个sheet

模板

模板放在项目中的位置：template/excel/certificateTemplate.xlsx

代码

  @Test
    public void test06() throws IOException {
        File rootPath = new File(ResourceUtils.getURL("classpath:").getPath());
        File templatePath = new File(rootPath.getAbsolutePath(), "template/excel/certificateTemplate.xlsx");
        String outsFileName = "预付款凭证-" + System.currentTimeMillis() + ".xlsx";

        String modelFileName = "tempFile.xlsx";
        File outModelFile = new File(modelFileName);

        File outsFile = new File(rootPath + "/" + outsFileName);

        String name = "张三1,张三2";
        List<String> taskNoList = Arrays.asList(name.split(","));

        EasyExcelUtil.createModel(taskNoList, outModelFile, templatePath);
       ExcelWriter excelWriter = EasyExcel.write(outsFile).withTemplate(outModelFile).build();
       // ExcelWriter excelWriter = EasyExcelUtil.setStyle(outModelFile, outsFile);
        for (String taskNo : taskNoList) {
            WriteSheet writeSheet = EasyExcel.writerSheet(taskNo).build();
            FillData stocktakeTaskVo = getInfo(taskNo);

            if (stocktakeTaskVo != null) {
                Map<String, Object> map = MapUtils.newHashMap();
                map.put("nowDate", DateUtil.nowDateTime(Constants.DATE_FORMAT_DATE)); //当前日期
                map.put("payerName", stocktakeTaskVo.getName()); //申请公司  付款方
                map.put("payeeName", "收款公司"); //账户名称 收款方
                map.put("bank", "6227999988889999"); //账号开户行
                map.put("bankAccount", "中国银行"); //银行账号
                map.put("amount", 10000); //金额

                excelWriter.fill(map, writeSheet);
            }
        }
        excelWriter.finish();
        outModelFile.delete();
    }

工具类：EasyExcelUtil

public class EasyExcelUtil {

    /**
     * 生成多sheet模板
     * @param taskNoList
     * @param outModelFile
     * @param templateFileName
     */
    public static void createModel(List<String> taskNoList, File outModelFile, File templateFileName) {
        try (FileOutputStream fileOutputStream = new FileOutputStream(outModelFile)) {
            FileInputStream inputStream = new FileInputStream(templateFileName);
            ZipSecureFile.setMinInflateRatio(-1.0d);
            //读取excel模板
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            workbook.cloneSheet(0);

            // 获取要复制的工作表索引
            int sourceSheetIndex = 0;
            Sheet sourceSheet = workbook.getSheetAt(sourceSheetIndex);

            Workbook targetWorkbook = new XSSFWorkbook();
            for (int i = 0; i < taskNoList.size(); i++) {
                String sheetName = taskNoList.get(i);
                Sheet targetSheet = targetWorkbook.createSheet(sheetName);
                // 复制原始工作表的内容到目标工作表
                copySheet(sourceSheet, targetSheet);
            }
            // 保存目标文件
            targetWorkbook.write(fileOutputStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取模板内容和格式
     *
     * @param sourceSheet
     * @param targetSheet
     */
    private static void copySheet(Sheet sourceSheet, Sheet targetSheet) {
        Workbook targetWorkbook = targetSheet.getWorkbook();
        CellStyle targetCellStyle;

        // 复制合并的单元格区域
        for (int i = 0; i < sourceSheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sourceSheet.getMergedRegion(i);
            targetSheet.addMergedRegion(mergedRegion);
        }

        for (Row sourceRow : sourceSheet) {
            Row targetRow = targetSheet.createRow(sourceRow.getRowNum());
            for (Cell sourceCell : sourceRow) {
                Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());

                // 复制单元格的值
                switch (sourceCell.getCellType()) {
                    case STRING:
                        targetCell.setCellValue(sourceCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                }

                // 复制单元格的样式
                CellStyle sourceCellStyle = sourceCell.getCellStyle();
                targetCellStyle = targetWorkbook.createCellStyle();
                targetCellStyle.cloneStyleFrom(sourceCellStyle);
                targetCellStyle.setWrapText(true);//自动换行
                targetCell.getRow().setHeight(sourceCell.getRow().getHeight()); //行高设置
                targetSheet.setColumnWidth(0, 10 * 256 );//设置列宽 第一列
                targetSheet.setColumnWidth(2, 10 * 256 );//设置列宽 第三列
                targetSheet.setColumnWidth(4, 3451 );//设置列宽 第四列
                targetSheet.setColumnWidth(5, 2958 );//设置列宽 第六列  11号字 每个汉字占位493
                targetCell.setCellStyle(targetCellStyle);



            }
        }
    }
    public static ExcelWriter setStyle(File outModelFile, File outsFile) {
        // 内容
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 标题
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 字体策略
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 88);
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setBold(true);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        headWriteCellStyle.setWriteFont(contentWriteFont);
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        ExcelWriter excelWriter = EasyExcel.write(outsFile)
//                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle))
//                .registerWriteHandler(new ExcelWidthS()) // 自动设置列宽
                .withTemplate(outModelFile)
                .build();
        return excelWriter;
    }

    /**
     * 初始化响应体
     *
     * @param response 请求头
     * @param fileName 导出名称
     */
    public static void initResponse(HttpServletResponse response, String fileName) {
        // 最终文件名：文件名_(截止yyyy-MM-dd)  --> 这块地方得根据你们自己项目做更改了
        String finalFileName = fileName + "_(截止" + DateUtil.dateToStr(new Date(), Constants.DATE_FORMAT_HHMM) + ")";
        // 设置content—type 响应类型
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
            // 这里URLEncoder.encode可以防止中文乱码
            finalFileName = URLEncoder.encode(finalFileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setHeader("Content-disposition", "attachment;filename=" + finalFileName + ".xlsx");
    }


}

最终导出结果

以上动态导出的excel还存在一个解决得不是很理想得地方，设置单元格的宽度，其实模板中，每个单元格的宽度都不一样，但是动态导出后，只能按照整列设置列的宽度。
如果大家有好的解决办法，请给我留言！！！