通过模板将 excel 分页转换成 pdf 文档

通过模板将 excel 分页转换成 pdf 文档

总体思路：通过excle模板控制excle界面样式，然后把数据按照需要分成多个list，填充到各个sheet页，生成好excle后，再调用excle转pdf工具，完成动态多页excle转pdf。主要理清思路，代码比较简单。
注意：使用前，先安装下载的aspose-cells-8.5.2.jar包，然后把excel-license.xml放在resources目录下就行了。

excle转pdf方法

 	/**
     * excel转pdf
     */
    public static void excel2pdf(Workbook workbook, String fileName) {
        if (!getLicense()) { // 验证License 若不验证则转化出的pdf文档会有水印产生
            return;
        }
        try {
            //缩放到一个页面
            PdfSaveOptions pdfSaveOptions = new PdfSaveOptions();
            pdfSaveOptions.setOnePagePerSheet(true);
            // 打开显示所有sheet
            int sheetCount = workbook.getWorksheets().getCount();
            int[] showSheets = new int[sheetCount];
            for (int i = 0; i < sheetCount; i++) {
                showSheets[i] = i;
            }
            //自动缩放比列
            int[] autoDrawSheets = {3};
            //等比缩放
            for (int i = 0; i < autoDrawSheets.length; i++) {
                Worksheet worksheet = workbook.getWorksheets().get(i);
                worksheet.getHorizontalPageBreaks().clear();
                worksheet.getVerticalPageBreaks().clear();
            }
            //隐藏不需要sheet页
            for (int i = 1; i < workbook.getWorksheets().getCount(); i++) {
                workbook.getWorksheets().get(i).setVisible(false);
            }
            if (showSheets.length == 0) {
                workbook.getWorksheets().get(0).setVisible(true);
            } else {
                for (int i = 0; i < showSheets.length; i++) {
                    workbook.getWorksheets().get(i).setVisible(true);
                }
            }
            ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = Objects.requireNonNull(requestAttributes).getResponse();
            Objects.requireNonNull(response).setHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(fileName + ".pdf", "UTF-8"));
            ServletOutputStream outputStream = response.getOutputStream();
            workbook.save(outputStream, pdfSaveOptions);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取license 去除水印
     *
     * @return
     */
    public static boolean getLicense() {
        boolean result = false;
        try {
            InputStream is = ExcelToPdfUtil.class.getClassLoader().getResourceAsStream("\\license.xml");
            License aposeLic = new License();
            aposeLic.setLicense(is);
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

下载pdf方法

 public void exportAdmissionPdf(LoadUserInfo loadUserInfo, SearchAdmissionEntity searchAdmissionEntity, HttpServletResponse response) {
        try {
            searchAdmissionEntity.setIsDelete(Constants.Status.NO);
            searchAdmissionEntity.setUserId(loadUserInfo.getUserId());
            List<VolunteerAdmissionResultDto> volunteerAdmissionResultDtos = volunteerAdmissionResultMapper.queryByVolunteerAdmissionResult(searchAdmissionEntity);
            //excle模板
            InputStream is = this.getClass().getResourceAsStream("/template/admission/export_pdf.xls");
            //根据模板创建Excel工作簿
            com.aspose.cells.Workbook workbook = new com.aspose.cells.Workbook(is);
            //填充业务数据
            setReportData(workbook, volunteerAdmissionResultDtos);
            //excel合并转换pdf
            String fileName = "录取明细";
            ExcelToPdfUtil.excel2pdf(workbook, fileName);
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage());
        }
    }

填充业务数据

	/**
     * 填充业务数据
     */
    @SneakyThrows
    private void setReportData(com.aspose.cells.Workbook workbook, List<VolunteerAdmissionResultDto> list) {
        List<List<VolunteerAdmissionResultDto>> tmpList = new ArrayList<>();

        //获取sheet模板行数
        int maxRowNum = 50;
        //总分页数，向上取整
        int size = list.size();
        int totalSheets = (int) Math.ceil((double) size / maxRowNum);
        //业务数据按照总页数进行分组
        int count = 0;
        while (count < size) {
            tmpList.add(list.subList(count, Math.min((count + maxRowNum), size)));
            count += maxRowNum;
        }
        //根据模板复制sheet页
        for (int i = 0; i < totalSheets; i++) {
            if (i > 0) {
                // 将模板中的内容复制到新建的Sheet页中
                workbook.getWorksheets().addCopy(i - 1);
                workbook.getWorksheets().get(i).setName("Sheet" + (i + 1));
            }
        }
        //计划数
        Integer planNum = 0;
        //招生数
        Integer num = 0;
        //已分组数据按照对应sheet页填充数据
        for (int j = 0; j < totalSheets; j++) {
            List<VolunteerAdmissionResultDto> mapList = tmpList.get(j);
            //初始化
            int len = mapList.size();
            Worksheet worksheet = workbook.getWorksheets().get(j);
            Cells cells = worksheet.getCells();
            //头部填充
            int year = TimeUtil.getYear();
            cells.checkRow(0).getCellOrNull(0).putValue(year + "年新生录取名册");
            cells.checkRow(4).get(6).putValue(mapList.get(0).getBatch() != null ? mapList.get(0).getBatch() : "");
            cells.checkRow(4).get(10).putValue(mapList.get(0).getNatureOfPlan() != null ? mapList.get(0).getNatureOfPlan() : "");
            cells.checkRow(4).get(14).putValue(planNum);
            cells.checkRow(4).get(18).putValue(num);
            //中间内容填充
            for (int i = 0; i < len; i++) {
                cells.checkRow(i + 7).getCellOrNull(0).putValue(mapList.get(i).getAdmissionCertificateNo() != null ? mapList.get(i).getAdmissionCertificateNo() : "");
                cells.checkRow(i + 7).getCellOrNull(1).putValue(mapList.get(i).getUsername() != null ? mapList.get(i).getUsername() : "");
                cells.checkRow(i + 7).getCellOrNull(2).putValue(mapList.get(i).getSex() != null ? mapList.get(i).getSex() : "");
                cells.checkRow(i + 7).getCellOrNull(3).putValue(mapList.get(i).getBirth() != null ? mapList.get(i).getBirth() : "");
                cells.checkRow(i + 7).getCellOrNull(4).putValue(mapList.get(i).getNationality() != null ? mapList.get(i).getNationality() : "");
                cells.checkRow(i + 7).getCellOrNull(5).putValue(mapList.get(i).getPoliticalAppearance() != null ? mapList.get(i).getPoliticalAppearance() : "");
                cells.checkRow(i + 7).getCellOrNull(6).putValue(mapList.get(i).getGraduationSchool() != null ? mapList.get(i).getGraduationSchool() : "");
                cells.checkRow(i + 7).getCellOrNull(7).putValue(mapList.get(i).getChinese() != null ? mapList.get(i).getChinese() : "");
                cells.checkRow(i + 7).getCellOrNull(8).putValue(mapList.get(i).getMath() != null ? mapList.get(i).getMath() : "");
                cells.checkRow(i + 7).getCellOrNull(9).putValue(mapList.get(i).getEnglish() != null ? mapList.get(i).getEnglish() : "");
                cells.checkRow(i + 7).getCellOrNull(10).putValue(mapList.get(i).getPhysical() != null ? mapList.get(i).getPhysical() : "");
                cells.checkRow(i + 7).getCellOrNull(11).putValue(mapList.get(i).getChemistry() != null ? mapList.get(i).getChemistry() : "");
                cells.checkRow(i + 7).getCellOrNull(12).putValue(mapList.get(i).getScienceComprehensive() != null ? mapList.get(i).getScienceComprehensive() : "");
                cells.checkRow(i + 7).getCellOrNull(13).putValue(mapList.get(i).getHistory() != null ? mapList.get(i).getHistory() : "");
                cells.checkRow(i + 7).getCellOrNull(14).putValue(mapList.get(i).getPolitics() != null ? mapList.get(i).getPolitics() : "");
                cells.checkRow(i + 7).getCellOrNull(15).putValue(mapList.get(i).getLiberalArtsComprehensive() != null ? mapList.get(i).getLiberalArtsComprehensive() : "");
                cells.checkRow(i + 7).getCellOrNull(16).putValue(mapList.get(i).getTotalScoreOfCultureTest() != null ? mapList.get(i).getTotalScoreOfCultureTest() : "");
                cells.checkRow(i + 7).getCellOrNull(17).putValue(mapList.get(i).getSportsPerformance() != null ? mapList.get(i).getSportsPerformance() : "");
                cells.checkRow(i + 7).getCellOrNull(18).putValue(mapList.get(i).getPolicyBonus() != null ? mapList.get(i).getPolicyBonus() : "");
                cells.checkRow(i + 7).getCellOrNull(19).putValue(mapList.get(i).getTotalScore() != null ? mapList.get(i).getTotalScore() : "");
            }
            //尾部填充页码
            cells.getRows().get(66).get(17).putValue("第" + (j + 1) + "页，共" + totalSheets + "页");
        }
    }