记Excel模板导出并转为PDF，支持批量打包下载。
最近项目用到了PDF导出，主要是后端完成，主要是个人不会设计PDF模板，效果太丑了，就想到了用Excel转PDF，且不用再设置样式，在模板中设置好了就可以直接套用了，记录下简单思路：
- 用excel设计好模板，因为涉及到多个表格需要动态插入数据，所以需要注意表格行列的计算。
- 将excel转为PDF文档。
- 若是存在多个PDF，则进行ZIP打包再提供下载，基于spring boot提供下载。
首先是excel模板样式预览
PDF预览
下载预览
首先引入依赖
<!-- https://mvnrepository.com/artifact/com.itextpdf/itextpdf --> <dependency> <groupId>com.itextpdf</groupId> <artifactId>itextpdf</artifactId> <version>5.5.13.2</version> </dependency> <!-- https://mvnrepository.com/artifact/com.itextpdf/itext-asian --> <dependency> <groupId>com.itextpdf</groupId> <artifactId>itext-asian</artifactId> <version>5.2.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.0.0</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.0.0</version> </dependency>
服务层代码
/** * PDF导出 * * @param form 表单 * @param response 响应 * @throws DocumentException 异常 * @throws IOException 异常 */ public void pdfExport(BaseForm form, HttpServletResponse response) throws DocumentException, IOException { //将zip以流的形式输出到前台 response.setContentType("application/octet-stream"); String keyword = form.getKeyword(); ApiRegisterVo vo = null; if (StringUtils.isNotBlank(keyword)) { vo = JSON.parseObject(keyword, ApiRegisterVo.class); } // 数据列表 List<ApiRegisterVo> vos = findList(vo); if (ObjectUtils.isNotEmpty(vos)) { // 不为空才导出 if (vos.size() == 1) { // 单文件导出 ApiRegisterVo temp = vos.get(0); ApiRegisterDetailVo detailVo = detail(temp.getId(), vo.getGatewayAddress()); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(detailVo.getName() + System.currentTimeMillis(), "UTF-8") + ".pdf"); Workbook workbook = excelService.openGatewayApiWorkbook(detailVo); byte[] bytes = ExcelToPdfUtil.excelToPdf(workbook, false, 0.8f); response.getOutputStream().write(bytes); } else { // 批量压缩导出 // pdf数据包列表 List<Map<String, byte[]>> dataPackage = new ArrayList<>(); // 单个数据，key为文件名 Map<String, byte[]> map; // 文件设置，附件形式打开 response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.code("GatewayInfo" + System.currentTimeMillis(), "UTF-8") + ".zip"); for (ApiRegisterVo registerVo : vos) { ApiRegisterDetailVo detailVo = detail(registerVo.getId(), vo.getGatewayAddress()); Workbook workbook = excelService.openGatewayApiWorkbook(detailVo); byte[] bytes = ExcelToPdfUtil.excelToPdf(workbook, false, 0.8f); map = new HashMap<>(); map.put(detailVo.getName() + System.currentTimeMillis() + ".pdf", bytes); dataPackage.add(map); } byte[] zipByte = ZipUtil.batchZipByte(dataPackage); response.getOutputStream().write(zipByte); } response.flushBuffer(); } }
excel导出方法
/** * 根据模板导出excel * * @param vo 数据列表 * @return 工作簿 */ public Workbook openGatewayApiWorkbook(ApiRegisterDetailVo vo) throws IOException { String templatePath = "template/openGatewayApi.xlsx"; Workbook workbook = ExcelUtils.getWorkBook(templatePath); // 字体信息 assert workbook != null; Font font = workbook.createFont(); font.setFontName("宋体"); font.setBold(true); font.setFontHeightInPoints((short) 12); CellStyle workbookCellStyle = workbook.createCellStyle(); workbookCellStyle.setWrapText(true); workbookCellStyle.setFont(font); workbookCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 写入第一页sheet中 Sheet sheet0 = workbook.getSheetAt(0); // 名称 setMergedCellValue(sheet0, 0, 0, vo.getName()); // 基本信息,固定行信息优先填写好 setMergedCellValue(sheet0, 2, 1, vo.getName()); setMergedCellValue(sheet0, 3, 1, vo.getMethods()); setMergedCellValue(sheet0, 4, 1, vo.getPath()); setMergedCellValue(sheet0, 5, 1, vo.getRequestUrl() == null ? "" : vo.getRequestUrl()); setMergedCellValue(sheet0, 6, 1, vo.getVersion() == null ? "" : vo.getVersion()); setMergedCellValue(sheet0, 7, 1, vo.getNote() == null ? "" : vo.getNote()); setMergedCellValue(sheet0, 15, 0, vo.getRequestParamsExample() == null ? "" : vo.getRequestParamsExample().getExample() == null ? "" : vo.getRequestParamsExample().getExample()); setMergedCellValue(sheet0, 22, 0, vo.getPathParamsExample() == null ? "" : vo.getPathParamsExample().getExample() == null ? "" : vo.getPathParamsExample().getExample()); setMergedCellValue(sheet0, 30, 0, vo.getResponseParamsExample() == null ? "" : vo.getResponseParamsExample().getExample() == null ? "" : vo.getResponseParamsExample().getExample()); setMergedCellValue(sheet0, 34, 2, vo.getMoreStateCodeUrl() == null ? "" : vo.getMoreStateCodeUrl()); // 请求头 List<ApiParams> requestHeaderParams = vo.getRequestHeaderParams(); // 记录请求头动态添加的行数 int requestHeaderParamsAddRow = 0; // 请求参数 List<ApiParams> requestParams = vo.getRequestParams(); // 记录请求参数动态添加的行数 int requestParamsAddRow = 0; // 路径参数 List<ApiParams> pathParams = vo.getPathParams(); // 记录路径参数动态添加的行数 int pathParamsAddRow = 0; // 响应头 List<ApiParams> responseHeaderParams = vo.getResponseHeaderParams(); // 记录响应头动态添加的行数 int responseHeaderParamsAddRow = 0; // 响应参数 List<ApiParams> responseParams = vo.getResponseParams(); // 记录响应参数动态添加的行数 int responseParamsAddRow = 0; // 状态码 List<ApiErrcode> apiErrcodes = vo.getApiErrcodes(); // 记录状态码动态添加的行数 int apiErrcodesAddRow = 0; // 表格数据填充 if (ObjectUtils.isNotEmpty(requestHeaderParams)) { // 请求头 // 计算起始行和获取样式行 int startRow = 11; // 样式行 requestHeaderParamsAddRow = getParamsAddRow(sheet0, requestHeaderParams, requestHeaderParamsAddRow, startRow); } if (ObjectUtils.isNotEmpty(requestParams)) { // 请求参数 // 计算起始行,原14+请求头添加行 int startRow = 14 + (Math.max(requestHeaderParamsAddRow - 1, 0)); // 样式行 Row styleRow = sheet0.getRow(startRow - 1); if (requestParams.size() > 1) { // 动态插入行 sheet0.shiftRows(startRow, sheet0.getLastRowNum(), requestParams.size() - 1, true, false); } requestParamsAddRow = getParamsAddRow(sheet0, requestParams, requestParamsAddRow, startRow, styleRow); } if (ObjectUtils.isNotEmpty(pathParams)) { // 路径参数 // 计算起始行，原21+请求头添加行+请求参数添加行 int startRow = 21 + (Math.max(requestHeaderParamsAddRow - 1, 0)) + (Math.max(requestParamsAddRow - 1, 0)); // 样式行 Row styleRow = sheet0.getRow(startRow - 1); pathParamsAddRow = getParamsAddRow(sheet0, pathParams, pathParamsAddRow, startRow, styleRow); } if (ObjectUtils.isNotEmpty(responseHeaderParams)) { // 响应头 // 计算起始行，原26+请求头添加行+请求参数添加行+路径参数添加行 int startRow = 26 + (Math.max(requestHeaderParamsAddRow - 1, 0)) + (Math.max(requestParamsAddRow - 1, 0)) + (Math.max(pathParamsAddRow - 1, 0)); // 样式行 Row styleRow = sheet0.getRow(startRow - 1); if (responseHeaderParams.size() > 1) { // 动态插入行 sheet0.shiftRows(startRow, sheet0.getLastRowNum(), pathParams.size() - 1, true, false); } for (ApiParams apiParams : responseHeaderParams) { // 插入的行号 int rowNum = responseHeaderParamsAddRow + (startRow - 1); Row newRow = sheet0.getRow(rowNum) == null ? sheet0.createRow(rowNum) : sheet0.getRow(rowNum); newRow.setRowStyle(styleRow.getRowStyle()); newRow.setHeight(styleRow.getHeight()); Cell cell0 = getCell(newRow, 0); cell0.setCellStyle(getCell(styleRow, 0).getCellStyle()); cell0.setCellValue(apiParams.getName()); Cell cell1 = getCell(newRow, 1); cell1.setCellStyle(getCell(styleRow, 1).getCellStyle()); cell1.setCellValue(apiParams.getExample() == null ? "" : apiParams.getExample()); Cell cell2 = getCell(newRow, 2); cell2.setCellStyle(getCell(styleRow, 2).getCellStyle()); Cell cell3 = getCell(newRow, 3); cell3.setCellStyle(getCell(styleRow, 3).getCellStyle()); cell3.setCellValue(apiParams.getNote() == null ? "" : apiParams.getNote()); Cell cell4 = getCell(newRow, 4); cell4.setCellStyle(getCell(styleRow, 4).getCellStyle()); if (!ExcelUtils.isMergedRegion(sheet0, newRow.getRowNum(), cell1.getColumnIndex())) { // 合并单元格 CellRangeAddress cellAddresses1 = new CellRangeAddress(newRow.getRowNum(), newRow.getRowNum(), cell1.getColumnIndex(), cell2.getColumnIndex()); sheet0.addMergedRegion(cellAddresses1); } if (!ExcelUtils.isMergedRegion(sheet0, newRow.getRowNum(), cell3.getColumnIndex())) { // 合并单元格 CellRangeAddress cellAddresses2 = new CellRangeAddress(newRow.getRowNum(), newRow.getRowNum(), cell3.getColumnIndex(), cell4.getColumnIndex()); sheet0.addMergedRegion(cellAddresses2); } responseHeaderParamsAddRow++; } } if (ObjectUtils.isNotEmpty(responseParams)) { // 响应参数 // 计算起始行，原29+请求头添加行+请求参数添加行+路径参数添加行+响应头参数添加行 int startRow = 29 + (Math.max(requestHeaderParamsAddRow - 1, 0)) + (Math.max(requestParamsAddRow - 1, 0)) + (Math.max(pathParamsAddRow - 1, 0)) + (Math.max(responseHeaderParamsAddRow - 1, 0)); // 样式行 responseParamsAddRow = getParamsAddRow(sheet0, responseParams, responseParamsAddRow, startRow); } if (ObjectUtils.isNotEmpty(apiErrcodes)) { // API状态码 // 计算起始行，原37+请求头添加行+请求参数添加行+路径参数添加行+响应头参数添加行+响应参数添加行 int startRow = 37 + (Math.max(requestHeaderParamsAddRow - 1, 0)) + (Math.max(requestParamsAddRow - 1, 0)) + (Math.max(pathParamsAddRow - 1, 0)) + (Math.max(responseHeaderParamsAddRow - 1, 0)) + (Math.max(responseParamsAddRow - 1, 0)); Row styleRow = sheet0.getRow(startRow - 1); if (apiErrcodes.size() > 1) { // 动态插入行,此表格最后行为空行，会无法识别到，所以从上一行开始下移插入 sheet0.shiftRows(startRow - 1, sheet0.getLastRowNum(), apiErrcodes.size() - 1, true, false); } for (ApiErrcode errcode : apiErrcodes) { // 插入的行号 int rowNum = apiErrcodesAddRow + (startRow - 1); Row newRow = sheet0.getRow(rowNum) == null ? sheet0.createRow(rowNum) : sheet0.getRow(rowNum); newRow.setRowStyle(styleRow.getRowStyle()); newRow.setHeight(styleRow.getHeight()); Cell cell0 = getCell(newRow, 0); cell0.setCellStyle(getCell(styleRow, 0).getCellStyle()); cell0.setCellValue(errcode.getCode()); Cell cell1 = getCell(newRow, 1); cell1.setCellStyle(getCell(styleRow, 1).getCellStyle()); cell1.setCellValue(errcode.getNote() == null ? "" : errcode.getNote()); Cell cell2 = getCell(newRow, 2); cell2.setCellStyle(getCell(styleRow, 2).getCellStyle()); Cell cell3 = getCell(newRow, 3); cell3.setCellStyle(getCell(styleRow, 3).getCellStyle()); apiErrcodesAddRow = getCodeAddRow(sheet0, apiErrcodesAddRow, styleRow, newRow, cell1); } } workbook.close(); return workbook; } /** * 合并单元格 * * @param sheet0 工作簿 * @param addRow 添加行 * @param styleRow 样式行 * @param newRow 插入行 * @param cell1 单元格 * @return 添加行 */ private int getCodeAddRow(Sheet sheet0, int addRow, Row styleRow, Row newRow, Cell cell1) { Cell cell4 = getCell(newRow, 4); cell4.setCellStyle(getCell(styleRow, 4).getCellStyle()); if (!ExcelUtils.isMergedRegion(sheet0, newRow.getRowNum(), cell1.getColumnIndex())) { // 合并单元格 CellRangeAddress cellAddresses = new CellRangeAddress(newRow.getRowNum(), newRow.getRowNum(), cell1.getColumnIndex(), cell4.getColumnIndex()); sheet0.addMergedRegion(cellAddresses); } addRow++; return addRow; } /** * 提取重复项 * * @param sheet0 工作簿 * @param paramsList 参数列表 * @param addRow 添加行 * @param startRow 样式行 * @param styleRow 样式行 * @return 参数添加行 */ private int getParamsAddRow(Sheet sheet0, List<ApiParams> paramsList, int addRow, int startRow, Row styleRow) { for (ApiParams apiParams : paramsList) { // 插入的行号 int rowNum = addRow + (startRow - 1); Row newRow = sheet0.getRow(rowNum) == null ? sheet0.createRow(rowNum) : sheet0.getRow(rowNum); newRow.setRowStyle(styleRow.getRowStyle()); newRow.setHeight(styleRow.getHeight()); // 插入数据 Cell cell0 = getCell(newRow, 0); cell0.setCellStyle(getCell(styleRow, 0).getCellStyle()); cell0.setCellValue(apiParams.getName()); Cell cell1 = getCell(newRow, 1); cell1.setCellStyle(getCell(styleRow, 1).getCellStyle()); cell1.setCellValue(apiParams.getRequired()); Cell cell2 = getCell(newRow, 2); cell2.setCellStyle(getCell(styleRow, 2).getCellStyle()); cell2.setCellValue(apiParams.getDataType()); Cell cell3 = getCell(newRow, 3); cell3.setCellStyle(getCell(styleRow, 3).getCellStyle()); cell3.setCellValue(apiParams.getExample() == null ? "" : apiParams.getExample()); Cell cell4 = getCell(newRow, 4); cell4.setCellStyle(getCell(styleRow, 4).getCellStyle()); cell4.setCellValue(apiParams.getNote() == null ? "" : apiParams.getNote()); addRow++; } return addRow; } /** * 提取重复项 * * @param sheet0 工作簿 * @param paramsList 参数列表 * @param addRow 添加行 * @param startRow 开始行 * @return 参数添加行 */ private int getParamsAddRow(Sheet sheet0, List<ApiParams> paramsList, int addRow, int startRow) { Row styleRow = sheet0.getRow(startRow - 1); if (paramsList.size() > 1) { // 动态插入行 sheet0.shiftRows(startRow, sheet0.getLastRowNum(), paramsList.size() - 1, true, false); } for (ApiParams apiParams : paramsList) { // 插入的行号 int rowNum = addRow + (startRow - 1); Row newRow = sheet0.getRow(rowNum) == null ? sheet0.createRow(rowNum) : sheet0.getRow(rowNum); newRow.setRowStyle(styleRow.getRowStyle()); newRow.setHeight(styleRow.getHeight()); Cell cell0 = getCell(newRow, 0); cell0.setCellStyle(getCell(styleRow, 0).getCellStyle()); cell0.setCellValue(apiParams.getName()); Cell cell1 = getCell(newRow, 1); cell1.setCellStyle(getCell(styleRow, 1).getCellStyle()); cell1.setCellValue(apiParams.getRequired()); Cell cell2 = getCell(newRow, 2); cell2.setCellStyle(getCell(styleRow, 2).getCellStyle()); cell2.setCellValue(apiParams.getExample() == null ? "" : apiParams.getExample()); Cell cell3 = getCell(newRow, 3); cell3.setCellStyle(getCell(styleRow, 3).getCellStyle()); cell3.setCellValue(apiParams.getNote() == null ? "" : apiParams.getNote()); addRow = getCodeAddRow(sheet0, addRow, styleRow, newRow, cell3); } return addRow; } /** * 设置合并单元格值 * * @param sheet 工作表 * @param row 行 * @param column 列 * @param value 值 */ private void setMergedCellValue(Sheet sheet, int row, int column, Object value) { Cell cell = ExcelUtils.getMergedRegion(sheet, row, column); if (cell != null && value != null) { cell.setCellValue(ExcelUtils.getCellValue(cell) + value); } }
excel工具类
package com.edp.gateway.open.utils; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFPicture; import org.apache.poi.hssf.usermodel.HSSFShape; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ooxml.POIXMLDocumentPart; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellType; import org.apache.poi.ss.usermodel.PictureData; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFClientAnchor; import org.apache.poi.xssf.usermodel.XSSFDrawing; import org.apache.poi.xssf.usermodel.XSSFPicture; import org.apache.poi.xssf.usermodel.XSSFShape; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker; import org.springframework.core.io.ClassPathResource; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; public class ExcelUtils { /** * 获取excel * * @param filePath 文件路径 * @return Workbook */ public static Workbook getWorkBook(String filePath) { if (StringUtils.isBlank(filePath)) { return null; } Workbook workbook = null; try { ClassPathResource cpr = new ClassPathResource(filePath); try (InputStream is = cpr.getInputStream()) { if (filePath.endsWith("xls")) { // 2003 workbook = new HSSFWorkbook(is); } else if (filePath.endsWith("xlsx")) { // 2007 workbook = new XSSFWorkbook(is); } } } catch (IOException e) { e.printStackTrace(); } return workbook; } /** * 读取excel中的图片和位置 * * @param workbook excel * @param sheet 工作表 * @return Map<String, PictureData> */ public static Map<String, PictureData> getPictures(Workbook workbook, Sheet sheet) { Map<String, PictureData> map = new HashMap<>(); if (workbook instanceof HSSFWorkbook) { map = getPictures2003((HSSFSheet) sheet); } else if (workbook instanceof XSSFWorkbook) { map = getPictures2007((XSSFSheet) sheet); } return map; } /** * 读取excel中的图片和位置（xls） * * @param sheet 工作表 * @return Map<String, PictureData> */ public static Map<String, PictureData> getPictures2003(HSSFSheet sheet) { Map<String, PictureData> map = new HashMap<>(); List<HSSFShape> list = sheet.getDrawingPatriarch().getChildren(); for (HSSFShape shape : list) { if (shape instanceof HSSFPicture) { HSSFPicture picture = (HSSFPicture) shape; HSSFClientAnchor cAnchor = (HSSFClientAnchor) picture.getAnchor(); // 行号-列号 String key = cAnchor.getRow1() + "-" + cAnchor.getCol1(); map.put(key, picture.getPictureData()); } } return map; } /** * 读取excel中的图片和位置（xlsx） * * @param sheet 工作表 * @return Map<String, PictureData> */ public static Map<String, PictureData> getPictures2007(XSSFSheet sheet) { Map<String, PictureData> map = new HashMap<>(); List<POIXMLDocumentPart> list = sheet.getRelations(); for (POIXMLDocumentPart part : list) { if (part instanceof XSSFDrawing) { XSSFDrawing drawing = (XSSFDrawing) part; List<XSSFShape> shapes = drawing.getShapes(); for (XSSFShape shape : shapes) { if (shape instanceof XSSFPicture) { XSSFPicture picture = (XSSFPicture) shape; XSSFClientAnchor anchor = picture.getPreferredSize(); CTMarker marker = anchor.getFrom(); String key = marker.getRow() + "-" + marker.getCol(); map.put(key, picture.getPictureData()); } } } } return map; } /** * 获取excel中每列宽度的占比 * * @param sheet 工作表 * @return float[] */ public static float[] getColWidth(Sheet sheet) { int rowNum = getMaxColRowNum(sheet); Row row = sheet.getRow(rowNum); int cellCount = row.getPhysicalNumberOfCells(); int[] colWidths = new int[cellCount]; int sum = 0; for (int i = row.getFirstCellNum(); i < cellCount; i++) { Cell cell = row.getCell(i); if (cell != null) { colWidths[i] = sheet.getColumnWidth(i); sum += sheet.getColumnWidth(i); } } float[] colWidthPer = new float[cellCount]; for (int i = row.getFirstCellNum(); i < cellCount; i++) { //对数字进行操作前先校验是否为0 if (sum != 0) { colWidthPer[i] = (float) colWidths[i] / sum * 100; } } return colWidthPer; } /** * 获取合并单元格 * * @param sheet 工作表 * @param row 行 * @param column 列 * @return Cell */ public static Cell getMergedRegion(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { Row fRow = sheet.getRow(firstRow); return fRow.getCell(firstColumn); } } } return null; } /** * 获取单元格的值 * * @param cell 单元格 * @return String */ public static String getCellValue(Cell cell) { if (cell == null) { return ""; } if (cell.getCellType() == CellType.STRING) { return cell.getStringCellValue(); } else if (cell.getCellType() == CellType.BOOLEAN) { return String.valueOf(cell.getBooleanCellValue()); } else if (cell.getCellType() == CellType.FORMULA) { return cell.getCellFormula(); } else if (cell.getCellType() == CellType.NUMERIC) { double value = cell.getNumericCellValue(); return String.valueOf((long) value); } return ""; } /** * 判断指定的单元格是否是合并单元格 * * @param sheet 工作表 * @param row 行下标 * @param column 列下标 * @return boolean */ public static boolean isMergedRegion(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { return true; } } } return false; } /** * 获取合并单元格宽高 * * @param sheet 工作表 * @param row 行 * @param column 列 * @return int[] */ public static float[] getMergedRegionSize(Sheet sheet, int row, int column) { int[] matrix = getMergedRegionMatrix(sheet, row, column); float[] size = new float[2]; // 合并单元格高 float height = 0; // 合并单元格宽 float width = 0; for (int y = matrix[0]; y <= matrix[2]; y++) { Row rowY = sheet.getRow(y); if (rowY != null) { height += sheet.getRow(y).getHeightInPoints(); } } for (int x = matrix[1]; x <= matrix[3]; x++) { width += sheet.getColumnWidthInPixels(x); } size[0] = height; size[1] = width; return size; } /** * 获取合并单元格矩阵 * * @param sheet 工作表 * @param row 行 * @param column 列 * @return int[] */ public static int[] getMergedRegionMatrix(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); int[] matrix = new int[4]; for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if (row >= firstRow && row <= lastRow) { if (column >= firstColumn && column <= lastColumn) { matrix[0] = firstRow; matrix[1] = firstColumn; matrix[2] = lastRow; matrix[3] = lastColumn; break; } } } return matrix; } /** * 计算合并单元格合并的跨行跨列数 * * @param sheet 工作表 * @param row 行 * @param column 列 * @return int[] */ public static int[] getMergedSpan(Sheet sheet, int row, int column) { int sheetMergeCount = sheet.getNumMergedRegions(); int[] span = {1, 1}; for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress range = sheet.getMergedRegion(i); int firstColumn = range.getFirstColumn(); int lastColumn = range.getLastColumn(); int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); if (firstColumn == column && firstRow == row) { span[0] = lastRow - firstRow + 1; span[1] = lastColumn - firstColumn + 1; break; } } return span; } /** * 获取excel中列数最多的行号 * * @param sheet 工作表 * @return int */ private static int getMaxColRowNum(Sheet sheet) { int rowNum = 0; int maxCol = 0; for (int r = sheet.getFirstRowNum(); r < sheet.getPhysicalNumberOfRows(); r++) { Row row = sheet.getRow(r); if (row != null && maxCol < row.getPhysicalNumberOfCells()) { maxCol = row.getPhysicalNumberOfCells(); rowNum = r; } } return rowNum; } }
excel转pdf工具类
package com.edp.gateway.open.utils; import com.itextpdf.text.BaseColor; import com.itextpdf.text.Document; import com.itextpdf.text.DocumentException; import com.itextpdf.text.Element; import com.itextpdf.text.Font; import com.itextpdf.text.Image; import com.itextpdf.text.PageSize; import com.itextpdf.text.Phrase; import com.itextpdf.text.Rectangle; import com.itextpdf.text.pdf.BaseFont; import com.itextpdf.text.pdf.PdfPCell; import com.itextpdf.text.pdf.PdfPTable; import com.itextpdf.text.pdf.PdfWriter; import org.apache.poi.ss.usermodel.BorderStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.PictureData; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.util.Map; public class ExcelToPdfUtil { /** * 用excel生成PDF * * @param workbook excel工作表 * @param isRotate 横向或纵向显示 * @param zoom 缩放比例 * @return byte[] */ public static byte[] excelToPdf(Workbook workbook, boolean isRotate, float zoom) throws DocumentException, IOException { Sheet sheet = workbook.getSheetAt(0); // 获取excel中的图片 Map<String, PictureData> pictureDataMap = ExcelUtils.getPictures(workbook, sheet); ByteArrayOutputStream os = new ByteArrayOutputStream(); // 创建PDF Document document = new Document(); Rectangle pageSize; if (isRotate) { // 横向显示 pageSize = new Rectangle(PageSize.A4.getHeight(), PageSize.A4.getWidth()); } else { // 纵向显示 pageSize = new Rectangle(PageSize.A4.getWidth(), PageSize.A4.getHeight()); } document.setPageSize(pageSize); PdfWriter.getInstance(document, os); //设置页边距 document.setMargins(0, 0, 15, 15); document.open(); // Table获取每列所占百分比 float[] widths = ExcelUtils.getColWidth(sheet); int colCount = widths.length; // 创建Table PdfPTable pTable = new PdfPTable(widths); // 设置Table占页面的百分比 pTable.setWidthPercentage(92); // 设置基本字体 BaseFont baseFont = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED); // 遍历Excel的行和列读取数据 for (int r = sheet.getFirstRowNum(); r < sheet.getPhysicalNumberOfRows(); r++) { Row row = sheet.getRow(r); // 为空时创建默认单元格 if (row == null) { PdfPCell pCell = new PdfPCell(new Phrase("")); pCell.setBorder(0); pCell.setMinimumHeight(13); pTable.addCell(pCell); } else { for (int c = row.getFirstCellNum(); (c < row.getLastCellNum() || c < colCount); c++) { if (c > row.getPhysicalNumberOfCells()) { PdfPCell pCell = new PdfPCell(new Phrase("")); pCell.setBorder(0); pTable.addCell(pCell); continue; } Cell cell = row.getCell(c); if (cell == null) { continue; } // 获取单元格的值 String value = ExcelUtils.getCellValue(cell); // 获取Excel单元格的样式 CellStyle cellStyle = cell.getCellStyle(); // 获取Excel单元格的字体 org.apache.poi.ss.usermodel.Font excelFont = workbook.getFontAt(cellStyle.getFontIndex()); // 设置PDF单元格字体 Font pFont = new Font(baseFont, excelFont.getFontHeightInPoints() * zoom, excelFont.getBold() ? Font.BOLD : Font.NORMAL, BaseColor.BLACK); // 处理图片 // 拼接图片的key String pictureKey = r + "-" + c; PdfPCell pCell; if (pictureDataMap.containsKey(pictureKey)) { // 如果是图片则给PDF单元格设置图片 PictureData pictureData = pictureDataMap.get(pictureKey); Image image = Image.getInstance(pictureData.getData()); float percent = 0.8f; if (ExcelUtils.isMergedRegion(sheet, r, c)) { float[] size = ExcelUtils.getMergedRegionSize(sheet, r, c); image.scaleToFit(size[0] * percent, size[1] * percent); } else { image.scaleToFit(row.getHeightInPoints() * percent, sheet.getColumnWidthInPixels(cell.getColumnIndex()) * percent); } pCell = new PdfPCell(image, true); } else { pCell = new PdfPCell(new Phrase(value, pFont)); } // 设置对齐方式 pCell.setHorizontalAlignment(getHorAlignment(cellStyle.getAlignment().getCode())); pCell.setVerticalAlignment(getVerAlignment(cellStyle.getVerticalAlignment().getCode())); // 设置最小高度 pCell.setMinimumHeight(row.getHeightInPoints() * zoom); // 判断是否合并单元格 if (ExcelUtils.isMergedRegion(sheet, r, c)) { int[] span = ExcelUtils.getMergedSpan(sheet, r, c); // 忽略合并过的单元格 if (span[0] == 1 && span[1] == 1) { continue; } pCell.setRowspan(span[0]); pCell.setColspan(span[1]); // 跳过合并过的单元格 c = c + span[1] - 1; } // 设置边框 setBorder(pCell, sheet, r, c); pTable.addCell(pCell); } } } document.add(pTable); document.close(); byte[] pdfByte = os.toByteArray(); os.close(); return pdfByte; } /** * 设置PDF边框 * * @param pCell pdf单元格 * @param sheet 工作表 * @param row 行 * @param column 列 */ private static void setBorder(PdfPCell pCell, Sheet sheet, int row, int column) { Cell cell; boolean isMergedRegion = ExcelUtils.isMergedRegion(sheet, row, column); if (isMergedRegion) { cell = ExcelUtils.getMergedRegion(sheet, row, column); } else { cell = sheet.getRow(row).getCell(column); } assert cell != null; CellStyle cellStyle = cell.getCellStyle(); if (BorderStyle.NONE.equals(cellStyle.getBorderTop())) { // 隐藏上边框 pCell.disableBorderSide(1); } if (BorderStyle.NONE.equals(cellStyle.getBorderBottom())) { // 隐藏下边框 pCell.disableBorderSide(2); } if (BorderStyle.NONE.equals(cellStyle.getBorderLeft())) { // 隐藏左边框 pCell.disableBorderSide(4); } if (isMergedRegion) { // 如果是合并单元格获取最后一个单元格的右边框样式 int[] matrix = ExcelUtils.getMergedRegionMatrix(sheet, row, column); Cell endCell = sheet.getRow(matrix[0]).getCell(matrix[3]); if (BorderStyle.NONE.equals(endCell.getCellStyle().getBorderRight())) { // 隐藏右边框 pCell.disableBorderSide(8); } } else { if (BorderStyle.NONE.equals(cellStyle.getBorderRight())) { // 隐藏右边框 pCell.disableBorderSide(8); } } } /** * excel垂直对齐方式映射到pdf对齐方式 * * @param alignment 对齐方式 * @return int */ private static int getVerAlignment(int alignment) { switch (alignment) { case 2: return Element.ALIGN_BOTTOM; case 3: return Element.ALIGN_TOP; case 1: default: return Element.ALIGN_MIDDLE; } } /** * excel水平对齐方式映射到pdf水平对齐方式 * * @param alignment 对齐方式 * @return int */ private static int getHorAlignment(int alignment) { switch (alignment) { case 2: return Element.ALIGN_CENTER; case 3: return Element.ALIGN_RIGHT; case 1: default: return Element.ALIGN_LEFT; } } }
zip打包工具类
package com.edp.gateway.open.utils; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.util.Base64; import java.util.List; import java.util.Map; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; public class ZipUtil { /** * 文件流压缩 * * @param basisByte 需要压缩的字节输出流(ByteArrayOutputStream)的字节数组 * @param fileName 需要压缩的文件名 * @return 压缩后字节数组输出流转为的字符串 * @throws IOException 异常 */ public static String zipByteToString(byte[] basisByte, String fileName) throws IOException { // 将需要压缩的字节输出流，转为字节数组输入流， ByteArrayOutputStream byteArrayOutputStream; // 创建压缩输出流 ZipOutputStream zipOutputStream; try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(basisByte)) { // 创建字节数组输出流，用于返回压缩后的输出流字节数组 byteArrayOutputStream = new ByteArrayOutputStream(); zipOutputStream = new ZipOutputStream(byteArrayOutputStream); // zipOutputStream.setMethod(ZipOutputStream.DEFLATED);// 进行压缩存储 // zipOutputStream.setLevel(Deflater.BEST_COMPRESSION);// 压缩级别值为0-9共10个级别(值越大，表示压缩越利害) // 设置ZipEntry对象，并对需要压缩的文件命名 zipOutputStream.putNextEntry(new ZipEntry(fileName)); // 读取要压缩的字节输出流，进行压缩 int temp; while ((temp = byteArrayInputStream.read()) != -1) { zipOutputStream.write(temp); // 压缩输出 } } zipOutputStream.close(); byteArrayOutputStream.close();// 关闭流 return Base64.getEncoder().encodeToString(byteArrayOutputStream.toByteArray()); } /** * 文件流压缩 * * @param basisByte 需要压缩的字节输出流(ByteArrayOutputStream)的字节数组 * @param fileName 需要压缩的文件名 * @return 压缩后字节数组 * @throws IOException 异常 */ public static byte[] zipByte(byte[] basisByte, String fileName) throws IOException { // 将需要压缩的字节输出流，转为字节数组输入流， ByteArrayOutputStream byteArrayOutputStream; // 创建压缩输出流 ZipOutputStream zipOutputStream; try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(basisByte)) { // 创建字节数组输出流，用于返回压缩后的输出流字节数组 byteArrayOutputStream = new ByteArrayOutputStream(); zipOutputStream = new ZipOutputStream(byteArrayOutputStream); // zipOutputStream.setMethod(ZipOutputStream.DEFLATED);// 进行压缩存储 // zipOutputStream.setLevel(Deflater.BEST_COMPRESSION);// 压缩级别值为0-9共10个级别(值越大，表示压缩越利害) // 设置ZipEntry对象，并对需要压缩的文件命名 zipOutputStream.putNextEntry(new ZipEntry(fileName)); // 读取要压缩的字节输出流，进行压缩 int temp; while ((temp = byteArrayInputStream.read()) != -1) { zipOutputStream.write(temp); // 压缩输出 } } zipOutputStream.close(); byteArrayOutputStream.close();// 关闭流 return byteArrayOutputStream.toByteArray(); } /** * 批量文件流压缩 * * @param basisByteList List<Map<被压缩压缩文件名, 被压缩的文件流>> * @return 压缩后字节数组输出流转为的字符串 * @throws IOException 异常 */ public static String batchZipByteToString(List<Map<String, byte[]>> basisByteList) throws IOException { // 创建字节数组输出流，用于返回压缩后的输出流字节数组 try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) { // 创建压缩输出流 try (ZipOutputStream zipOutputStream = new ZipOutputStream(byteArrayOutputStream)) { // 遍历要批量压缩的集合文件流 compress(basisByteList, zipOutputStream); } // 关闭流 byteArrayOutputStream.close(); return Base64.getEncoder().encodeToString(byteArrayOutputStream.toByteArray()); } } /** * 批量文件流压缩 * * @param basisByteList List<Map<被压缩压缩文件名, 被压缩的文件流>> * @return 压缩后字节数组 * @throws IOException 异常 */ public static byte[] batchZipByte(List<Map<String, byte[]>> basisByteList) throws IOException { // 创建字节数组输出流，用于返回压缩后的输出流字节数组 try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) { // 创建压缩输出流 try (ZipOutputStream zipOutputStream = new ZipOutputStream(byteArrayOutputStream)) { // 遍历要批量压缩的集合文件流 compress(basisByteList, zipOutputStream); } // 关闭流 byteArrayOutputStream.close(); return byteArrayOutputStream.toByteArray(); } } /** * 提取重复项 * * @param basisByteList 字节数组列表 * @param zipOutputStream 输出流 * @throws IOException 异常 */ private static void compress(List<Map<String, byte[]>> basisByteList, ZipOutputStream zipOutputStream) throws IOException { ByteArrayInputStream byteArrayInputStream; Map<String, byte[]> tempMap; String fileName; int temp; for (Map<String, byte[]> stringMap : basisByteList) { tempMap = stringMap; fileName = tempMap.keySet().iterator().next(); // 将需要压缩的字节输出流，转为字节数组输入流， byteArrayInputStream = new ByteArrayInputStream(tempMap.get(fileName)); // zipOut.setMethod(ZipOutputStream.DEFLATED);// 进行压缩存储 // zipOut.setLevel(Deflater.BEST_COMPRESSION);// 压缩级别值为0-9共10个级别(值越大，表示压缩越利害) // 设置ZipEntry对象，并对需要压缩的文件命名 zipOutputStream.putNextEntry(new ZipEntry(fileName)); // 读取要压缩的字节输出流，进行压缩 while ((temp = byteArrayInputStream.read()) != -1) { zipOutputStream.write(temp); // 压缩输出 } // 关闭流 byteArrayInputStream.close(); } } }
到此基本问题解决，工具类有一些是网友提供的，但是我没找到连接，在此就不贴原文引用链接了，知道的朋友也可提醒我补上，代码中还有不少地方可以优化，若对大家有帮助，可自行根据需求调整。
PS：
1.excel的动态插入行在转pdf的时候，空值单元格需要用空串占位，否则在转pdf时会发生数据错位现象。
2.因为全程都是字节数组和流式处理，对内存要求比较高，数据量大的注意控制内存，不要溢出。
3.字节数组文件直接压缩可能会导致，各个文件位置不统一，如效果图所示，自行定义文件位置后再打包。
4.打包中文乱码需要更改编码格式。
浙公网安备 33010602011771号