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

古城微笑少年丶 2024-03-23 09:59 61阅读 0赞

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

*  应用场景
 *  一、pom引入
 *  二、业务代码
 *   *   *  1.根据模板生成多个工作簿，并填充数据
         *  2.根据模板导出单个工作簿

--------------------

## 应用场景 ##

根据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`

--------------------