java 根据Excel固定模板导出单个或多个工作簿

---

应用场景

根据Excel固定模板导出单个或多个工作簿

废话不多说，上代码

---

一、pom引入

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

从EasyExcel 3.0.0版本开始，已经不再需要手动引入Apache POI库的poi和poi-ooxml这两个依赖了。EasyExcel 3.0.0及以上版本已经将这两个依赖打包进了自己的jar包中，因此只需要引入EasyExcel的依赖即可

如业务只需要根据模板生成Excel，可只导入以下依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

二、业务代码

以下代码中有详细说明，请根据自身业务进行修改优化使用

1.根据模板生成多个工作簿，并填充数据

public void exportExpert(HttpServletRequest request, HttpServletResponse response) {
		
        //工作簿名称
        List<String> getTalentClassifys = new ArrayList<>();
		//列表数据
        List<HashMap<String, List<ExpertUserDeclare>>> list = new ArrayList<>();

   
        ExcelWriter excelWriter = null;
        try {
            // 打开excel模板,获取第一个工作表。
            FileInputStream templateFile = new FileInputStream(new File(pfTemporaryTemplate));//pfTemporaryTemplate:模板路径
            Workbook templateWorkbook = new XSSFWorkbook(templateFile);
            Sheet templateSheet = templateWorkbook.getSheetAt(0);

            // 读取需要填充数据的单元格
            Row row = templateSheet.getRow(0);
            Cell cell1 = row.getCell(0);
            Cell cell2 = row.getCell(1);

            // 创建新的excel文件
            Workbook workbook = new XSSFWorkbook();

            // 生成多个工作簿
            for (int i = 0; i < getTalentClassifys.size(); i++) {
                //新的工作簿名称
                String talentClassify = getTalentClassifys.get(i);
                // 将工作簿模板添加到新的excel文件中
                Sheet worksheet = workbook.createSheet(talentClassify);
                for (Row templateRow : templateSheet) {
                    Row newRow = worksheet.createRow(templateRow.getRowNum());
                    //设置行高
                    newRow.setHeight(templateRow.getHeight());
                    // 复制模板中的数据到新的工作簿中
                    for (Cell templateCell : templateRow) {
                        Cell newCell = newRow.createCell(templateCell.getColumnIndex());
                        newCell.setCellValue(templateCell.getStringCellValue());
                        CellStyle newCellStyle = workbook.createCellStyle();
                        newCellStyle.cloneStyleFrom(templateCell.getCellStyle());
                        newCell.setCellStyle(newCellStyle);
                        // 复制列宽、字体、边框、背景色等信息
                        // 获取列宽
                        int columnWidth = templateSheet.getColumnWidth(templateCell.getColumnIndex());
                        // 设置列宽
                        worksheet.setColumnWidth(newCell.getColumnIndex(), columnWidth);
                        // 复制字体-略
                        // 复制边框
                        newCellStyle.setBorderTop(templateCell.getCellStyle().getBorderTop());
                        newCellStyle.setBorderBottom(templateCell.getCellStyle().getBorderBottom());
                        newCellStyle.setBorderLeft(templateCell.getCellStyle().getBorderLeft());
                        newCellStyle.setBorderRight(templateCell.getCellStyle().getBorderRight());
                        // 复制背景色
                        newCellStyle.setFillForegroundColor(templateCell.getCellStyle().getFillForegroundColor());
                        newCellStyle.setFillPattern(templateCell.getCellStyle().getFillPattern());
                        // 获取模板单元格中的超链接
                        Hyperlink templateHyperlink = templateCell.getHyperlink();
                        if (templateHyperlink != null) {
                            // 创建新的超链接
                            Hyperlink newHyperlink = workbook.getCreationHelper().createHyperlink(templateHyperlink.getType());
                            newHyperlink.setAddress(templateHyperlink.getAddress());
                            newCell.setHyperlink(newHyperlink);
                        }
                    }
                }

                // 复制合并单元格的信息
                for (int j = 0; j < templateSheet.getNumMergedRegions(); j++) {
                    CellRangeAddress mergedRegion = templateSheet.getMergedRegion(j);
                    if (mergedRegion.getFirstRow() != -1 && mergedRegion.getLastRow() != -1) {
                        if (mergedRegion.getFirstRow() >= templateSheet.getFirstRowNum() && mergedRegion.getLastRow() <= templateSheet.getLastRowNum()) {
                            CellRangeAddress newMergedRegion = new CellRangeAddress(
                                    mergedRegion.getFirstRow() - templateSheet.getFirstRowNum(),
                                    mergedRegion.getLastRow() - templateSheet.getFirstRowNum(),
                                    mergedRegion.getFirstColumn(),
                                    mergedRegion.getLastColumn()
                            );
                            worksheet.addMergedRegion(newMergedRegion);
                        }
                    }
                }

                // 将数据填充到对应的单元格中
                Row newRow = worksheet.getRow(0);
                Cell newCell1 = newRow.createCell(0);
                Cell newCell2 = newRow.createCell(1);
                newCell1.setCellValue(cell1.getStringCellValue());
                CellStyle newCellStyle1 = workbook.createCellStyle();
                newCellStyle1.cloneStyleFrom(cell1.getCellStyle());
                newCell1.setCellStyle(newCellStyle1);
                newCell2.setCellValue(cell2.getStringCellValue());
                CellStyle newCellStyle2 = workbook.createCellStyle();
                newCellStyle2.cloneStyleFrom(cell2.getCellStyle());
                newCell2.setCellStyle(newCellStyle2);
            }
            //生成文件路径
            String templatePath = temporaryFile + "pfTemporaryTemplate.xlsx";

            // 保存excel文件
            FileOutputStream outputStream = new FileOutputStream(templatePath);
            workbook.write(outputStream);
            outputStream.close();
            workbook.close();

            // 关闭excel模板
            templateFile.close();
            templateWorkbook.close();

            //到此,根据模板生成工作簿业务结束
            //------------------------


            String percentEncodedFileName = URLEncodeUtil.encode("xxx.xlsx");
            StringBuilder contentDispositionValue = new StringBuilder();
            contentDispositionValue.append("attachment; filename=")
                    .append(percentEncodedFileName)
                    .append(";")
                    .append("filename*=")
                    .append("utf-8''")
                    .append(percentEncodedFileName);

            response.addHeader("Access-Control-Allow-Origin", "*");
            response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
            response.setHeader("Content-disposition", contentDispositionValue.toString());
            response.setHeader("download-filename", percentEncodedFileName);

//            EasyExcel.write(response.getOutputStream(), ResumeCountDto.class).withTemplate(companyTemplate).sheet().doFill(list);
            //构建excel的sheet
            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(templatePath).build();


            for (int i = 0; i < getTalentClassifys.size(); i++) {
                String talentClassify = getTalentClassifys.get(i);
                WriteSheet writeSheet = EasyExcel.writerSheet(talentClassify).build();
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                List<ExpertUserDeclare> expertUserDeclareList1 = list.get(i).get(talentClassify);
//                excelWriter.fill(declareList, writeSheet);
                excelWriter.fill(expertUserDeclareList1, fillConfig, writeSheet);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {// 千万别忘记finish
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }

    }

2.根据模板导出单个工作簿

 public void exportExcel2(HttpServletResponse response) {
		//HTTP响应,此部分可以提取出来
        String percentEncodedFileName = URLEncodeUtil.encode("xxx.xlsx");
        StringBuilder contentDispositionValue = new StringBuilder();
        contentDispositionValue.append("attachment; filename=")
                .append(percentEncodedFileName)
                .append(";")
                .append("filename*=")
                .append("utf-8''")
                .append(percentEncodedFileName);
        response.addHeader("Access-Control-Allow-Origin", "*");
        response.addHeader("Access-Control-Expose-Headers", "Content-Disposition,download-filename");
        response.setHeader("Content-disposition", contentDispositionValue.toString());
        response.setHeader("download-filename", percentEncodedFileName);

        ExcelWriter excelWriter = null;
        try {
            //构建excel的sheet
//            EasyExcel.write(response.getOutputStream(), ResumeCountDto.class).withTemplate(companyTemplate).sheet().doFill(list);
            excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate("/data/rcsb/upload/PDFtemplate/评分综合排序表.xlsx").build();

            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            //设置forceNewRow属性为true，表示在填充数据时，如果当前行已经有数据了，就强制换行填充
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            //填充数据
            List<HashMap<String, String>> list = new ArrayList<>();
            HashMap<String, String> map = new HashMap<>();
            map.put("expertUserName", "姓名");
            list.add(map);
//                excelWriter.fill(declareList, writeSheet);
            excelWriter.fill(list, fillConfig, writeSheet);


        } catch (Exception e) {
            e.printStackTrace();
        } finally {// 千万别忘记finish
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

模板创建说明：
{字段名} 表示普通字段
{.字段名} 表示集合中字段

end

---