IText实现Excel转PDF

引入依赖包

<!--poi实现Excel导入导出-->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.0</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.0</version>
</dependency>
<!--itextpdf依赖注入包-->
<!-- https://mvnrepository.com/artifact/com.itextpdf/itextpdf -->
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itextpdf</artifactId>
    <version>5.5.13.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.itextpdf/itext7-core -->
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itext7-core</artifactId>
    <version>7.2.5</version>
    <type>pom</type>
</dependency>

构建表格内容

//宽度占比
private static final float width = 60f;
private static final float fitWidthRatio = 0.9f;
//高度占比
private static final float height = 60f;
private static final float fitHeightRatio = 0.9f;

/**
 * 构建表格内容
 *
 * @param workbook   workbook对象
 * @param fileName   文件名称
 * @param sheetIndex sheet页坐标
 * @param totalWidth 表格宽度
 * @return PdfPTable-表格对象
 */
public static PdfPTable structureTable(Workbook workbook, String fileName, int sheetIndex, float totalWidth) {
    try {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        //获取表格宽度占比情况
        float[] widths = ExcelUtils.getColWidthRatio(sheet);
        //初始化表格对象111
        PdfPTable table = new PdfPTable(widths);
        //设置表格占PDF文档百分比宽度(未设置绝对宽度时才会生效)
        table.setWidthPercentage(90);
        //设置表格绝对宽度
        table.setTotalWidth(totalWidth);
        //宽度设置生效
        table.setLockedWidth(true);
        //获取列数
        int colNumber = widths.length;
        for (int rowIndex = 0; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row != null) {
                for (int colIndex = 0; colIndex < colNumber; colIndex++) {
                    Cell cell = row.getCell(colIndex);
                    String value = "";
                    //当列为空时候,重新生成该列对象
                    if (cell == null) {
                        cell = row.createCell(colIndex);
                    } else {
                        //获取该列值
                        value = ExcelUtils.getCellValue(cell);
                    }
                    //获取表字体信息
                    org.apache.poi.ss.usermodel.Font excelFont = ExcelUtils.getExcelFont(workbook, cell, fileName);
                    //获取字号大小
                    short fontSize = excelFont.getFontHeightInPoints();
                    //创建字体对象
                    Font font = createFont(excelFont.getFontName(), fontSize, excelFont.getBold());
                    PdfPCell pdfPCell = new PdfPCell(new Phrase(value, font));
                    //设置边框
                    pdfPCell.setBorder(0);
                    //设置对齐
                    pdfPCell.setUseAscender(true);
                    //设置水平对齐
                    pdfPCell.setHorizontalAlignment(PdfPCell.ALIGN_CENTER);
                    //设置垂直对齐
                    pdfPCell.setVerticalAlignment(PdfPCell.ALIGN_MIDDLE);
                    //设置单元格高度
                    pdfPCell.setMinimumHeight(row.getHeightInPoints());
                    //当文件存在图片资源时,设置图片
                    List<PictureInfo> pictureInfos = ExcelUtils.getPictureInfo(sheet, rowIndex, rowIndex, colIndex, colIndex, true);
                    if (!pictureInfos.isEmpty()) {
                        for (PictureInfo pictureInfo : pictureInfos) {
                            Image image = Image.getInstance(pictureInfo.getPictureData());
                            计算图片宽度和高度
                            //float fitWidth = 0;
                            //float fitHeight = 0;
                            计算该图片占用列宽度
                            //for (int i = pictureInfo.getStartCol(); i <= pictureInfo.getEndCol(); i++) {
                            //    fitWidth += widths[i];
                            //}
                            计算该图片占用行高度
                            //for (int i = pictureInfo.getStartRow(); i <= pictureInfo.getEndRow(); i++) {
                            //    fitHeight += sheet.getRow(i).getHeightInPoints();
                            //}
                            //image.scaleAbsolute(fitWidth * totalWidth / 100 * fitWidthRatio, fitHeight * fitHeightRatio);
                            //设置图片宽度和高度
                            image.scaleToFit(width, height);
                            pdfPCell = new PdfPCell(image);
                            //设置对齐
                            pdfPCell.setUseAscender(true);
                            //设置水平对齐
                            pdfPCell.setHorizontalAlignment(PdfPCell.ALIGN_CENTER);
                            //设置垂直对齐
                            pdfPCell.setVerticalAlignment(PdfPCell.ALIGN_MIDDLE);
                        }
                    }
                    //判断是否存在跨行跨列单元格
                    if (ExcelUtils.ifMergedRegions(sheet, rowIndex, colIndex)) {
                        List<Integer> mergedRegions = ExcelUtils.getMergedRegions(sheet, rowIndex, colIndex);
                        if (mergedRegions.get(0) == rowIndex) {
                            pdfPCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft().getCode());
                            pdfPCell.setBorderWidthTop(cell.getCellStyle().getBorderTop().getCode());
                        }
                        if (mergedRegions.get(1) == sheet.getPhysicalNumberOfRows() - 1) {
                            pdfPCell.setBorderWidthBottom(sheet.getRow(mergedRegions.get(1)).getCell(mergedRegions.get(2)).getCellStyle().getBorderBottom().getCode());
                        }
                        //获取跨行数及跨列数
                        int rowspan = mergedRegions.get(1) - mergedRegions.get(0) + 1;
                        int colspan = mergedRegions.get(3) - mergedRegions.get(2) + 1;
                        //当表格存在跨行及跨列时,跳过当前行及列
                        if (mergedRegions.get(0) < rowIndex && rowIndex <= mergedRegions.get(1) && mergedRegions.get(2) <= colIndex && colIndex <= mergedRegions.get(3)) {
                            continue;
                        }
                        //设置单元格合并行数及列数
                        pdfPCell.setRowspan(rowspan);
                        pdfPCell.setColspan(colspan);
                        //跳过已经合并的列
                        colIndex = colIndex + colspan - 1;
                    } else {
                        pdfPCell.setBorderWidthTop(cell.getCellStyle().getBorderTop().getCode());
                        pdfPCell.setBorderWidthLeft(cell.getCellStyle().getBorderLeft().getCode());
                    }
                    //当最后一行时设置下边框
                    if (rowIndex == sheet.getPhysicalNumberOfRows() - 1) {
                        pdfPCell.setBorderWidthBottom(cell.getCellStyle().getBorderBottom().getCode());
                    }
                    //当最后一列时设置右边框
                    if (colIndex == row.getPhysicalNumberOfCells() - 1) {
                        pdfPCell.setBorderWidthRight(cell.getCellStyle().getBorderRight().getCode());
                    }
                    table.addCell(pdfPCell);
                }
            } else {
                PdfPCell pdfPCell = new PdfPCell(new Phrase(""));
                pdfPCell.setBorder(0);
                pdfPCell.setMinimumHeight(13);
                table.addCell(pdfPCell);
            }
        }
        return table;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

引用到的工具方法

1.构建字体对象

private static final String FONT_ARIAL = "arial.ttf";
private static final String FONT_ARIAL_NAME = "arial";
private static final String FONT_YH = "msyh.ttc,0";
private static final String FONT_YH_NAME = "微软雅黑";
private static final String FONT_HT = "simhei.ttf";
private static final String FONT_HT_NAME = "黑体";
private static final String FONT_FANG_SONG = "simfang.ttf";
private static final String FONT_FANG_SONG_NAME = "仿宋";
private static final String FONT_SONG = "simsun.ttc,0";
private static final String FONT_SONG_NAME = "宋体";
private static final String FONT_DENG = "Deng.ttf";
private static final String FONT_DENG_NAME = "等线";
private static final String FONT_KAI = "simkai.ttf";
private static final String FONT_KAI_NAME = "楷体";

/**
* 创建字体对象
*
* @param fontName 字体名称
* @param fontSize 字号大小
* @param fontBold 是否加粗
* @return Font
*/
public static Font createFont(String fontName, int fontSize, boolean fontBold) {
   BaseFont baseFont = createBaseFont(fontName);
   if (baseFont == null) {
       baseFont = createBaseFont(FONT_YH_NAME);
   }
   return new Font(baseFont, fontSize, fontBold ? Font.BOLD : Font.NORMAL, BaseColor.BLACK);
}

/**
* 构建字体类
*
* @param fontName 字体名称
* @return BaseFont
*/
private static BaseFont createBaseFont(String fontName) {
   //获取字体存放路径
   String FONT_PATH = Objects.requireNonNull(PdfUtils.class.getResource("/fonts/")).getPath();
   try {
       Map<String, BaseFont> fontMap = new HashMap<String, BaseFont>() {{
           put(FONT_ARIAL_NAME, BaseFont.createFont(FONT_PATH + FONT_ARIAL, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
           put(FONT_YH_NAME, BaseFont.createFont(FONT_PATH + FONT_YH, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
           put(FONT_HT_NAME, BaseFont.createFont(FONT_PATH + FONT_HT, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
           put(FONT_SONG_NAME, BaseFont.createFont(FONT_PATH + FONT_SONG, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
           put(FONT_FANG_SONG_NAME, BaseFont.createFont(FONT_PATH + FONT_FANG_SONG, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
           put(FONT_DENG_NAME, BaseFont.createFont(FONT_PATH + FONT_DENG, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
           put(FONT_KAI_NAME, BaseFont.createFont(FONT_PATH + FONT_KAI, BaseFont.IDENTITY_H, BaseFont.EMBEDDED));
       }};
       return fontMap.get(fontName);
   } catch (Exception e) {
       e.printStackTrace();
   }
   return null;
}

2.获取表格字体格式

/**
 * 获取表格字体格式
 *
 * @param workbook  表格对象
 * @param cell      列对象
 * @param excelName 表格文件名称
 * @return Font-字体
 */
public static Font getExcelFont(Workbook workbook, Cell cell, String excelName) {
    if (excelName.endsWith("xls")) {
        return ((HSSFCell) cell).getCellStyle().getFont(workbook);
    }
    return ((XSSFCell) cell).getCellStyle().getFont();
}

3.获取表格中列宽占比情况

/**
 * 获取列宽占比
 *
 * @param sheet sheet页对象
 * @return float[]
 */
public static float[] getColWidthRatio(Sheet sheet) {
    //获取表格中列数最多的行数下标
    int maxRow = getMaxRow(sheet);
    Row row = sheet.getRow(maxRow);
    int cellNumber = row.getPhysicalNumberOfCells();
    int[] colWidths = new int[cellNumber];
    //宽度总数
    int sum = 0;
    for (int i = 0; i < cellNumber; i++) {
        Cell cell = row.getCell(i);
        if (cell != null) {
            colWidths[i] = sheet.getColumnWidth(i);
            sum += sheet.getColumnWidth(i);
        }
    }
    //计算每一列占比值
    float[] colWidthPer = new float[cellNumber];
    for (int i = 0; i < cellNumber; i++) {
        colWidthPer[i] = (float) colWidths[i] / sum * 100;
    }
    return colWidthPer;
}

/**
 * 获取表格中列数最多的行数下标
 *
 * @param sheet sheet页对象
 * @return int-下标
 */
public static int getMaxRow(Sheet sheet) {
    int maxCell = 0;
    int maxRow = 0;
    for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
        Row row = sheet.getRow(i);
        if (row != null && maxCell < row.getPhysicalNumberOfCells()) {
            maxCell = row.getPhysicalNumberOfCells();
            maxRow = i;
        }
    }
    return maxRow;
}

4.读取表格<列>属性值

/**
 * 获取列值
 *
 * @param cell 列对象
 * @return String-值
 */
public static String getCellValue(Cell cell) {
    String value = "";
    if (!isEmptyCell(cell)) {
        switch (cell.getCellType()) {
            case STRING:
                value = cell.getStringCellValue().trim();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    //表格时间类读取默认格式,可自行调整
                    value = DateUtils.dateToString(date, "yyyy-MM-dd HH:mm:ss");
                } else {
                    value = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            default:
                value = "";
                break;
        }
    }
    return value;
}

5.判断表格是否存在跨行跨列情况，并获取跨行跨列单元格基础属性

/**
 * 判断是否存在跨行跨列单元格
 *
 * @param sheet     sheet页对象
 * @param rowIndex  行坐标
 * @param cellIndex 列坐标
 * @return true-存在,false-不存在
 */
public static boolean ifMergedRegions(Sheet sheet, int rowIndex, int cellIndex) {
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        int firstRow = mergedRegion.getFirstRow();
        int lastRow = mergedRegion.getLastRow();
        int firstColumn = mergedRegion.getFirstColumn();
        int lastColumn = mergedRegion.getLastColumn();
        if (firstRow <= rowIndex && rowIndex <= lastRow) {
            if (firstColumn <= cellIndex && cellIndex <= lastColumn) {
                return true;
            }
        }
    }
    return false;
}

/**
 * 返回跨行跨列单元格基础属性
 *
 * @param sheet     sheet页对象
 * @param rowIndex  行坐标
 * @param cellIndex 列坐标
 * @return List<Integer> - 起始行,终止行,起始列,终止列
 */
public static List<Integer> getMergedRegions(Sheet sheet, int rowIndex, int cellIndex) {
    List<Integer> merge = null;
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        int firstRow = mergedRegion.getFirstRow();
        int lastRow = mergedRegion.getLastRow();
        int firstColumn = mergedRegion.getFirstColumn();
        int lastColumn = mergedRegion.getLastColumn();
        if (firstRow <= rowIndex && rowIndex <= lastRow) {
            if (firstColumn <= cellIndex && cellIndex <= lastColumn) {
                merge = Arrays.asList(firstRow, lastRow, firstColumn, lastColumn);
                break;
            }
        }
    }
    return merge;
}

6.1读取表格中图片对象

/**
 * 获取表格中图片文件对象
 *
 * @param sheet           sheet对象
 * @param startCurrentRow 当前起始行
 * @param endCurrentRow   当前终止行
 * @param startCurrentCol 当前起始列
 * @param endCurrentCol   当前终止列
 * @param isInterval      是否存在跨行跨列情况
 * @return List<PictureInfo>
 */
public static List<PictureInfo> getXLSPictureInfo(HSSFSheet sheet, Integer startCurrentRow, Integer endCurrentRow, Integer startCurrentCol, Integer endCurrentCol, Boolean isInterval) {
    List<PictureInfo> pictureInfos = new ArrayList<>();
    try {
        HSSFPatriarch drawingPatriarch = sheet.getDrawingPatriarch();
        if (drawingPatriarch != null) {
            List<HSSFShape> shapeList = drawingPatriarch.getChildren();
            for (HSSFShape hssfShape : shapeList) {
                if (hssfShape instanceof HSSFPicture && hssfShape.getAnchor() instanceof HSSFClientAnchor) {
                    //获取表格文件对象
                    HSSFPicture picture = (HSSFPicture) hssfShape;
                    //获取表格文件属性
                    HSSFClientAnchor anchor = (HSSFClientAnchor) hssfShape.getAnchor();
                    //根据当前读取进度判断图片是否需要加载
                    if (isLoadPicture(startCurrentRow, endCurrentRow, startCurrentCol, endCurrentCol,
                            anchor.getRow1(), anchor.getRow2(), anchor.getCol1(), anchor.getCol2(), isInterval)) {
                        pictureInfos.add(new PictureInfo(anchor.getRow1(), anchor.getRow2(), anchor.getCol1(), anchor.getCol2(),
                                picture.getPictureData().getData(), picture.getPictureData().getMimeType()));
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return pictureInfos;
}

/**
 * 获取表格中图片文件对象
 *
 * @param sheet           sheet对象
 * @param startCurrentRow 当前起始行
 * @param endCurrentRow   当前终止行
 * @param startCurrentCol 当前起始列
 * @param endCurrentCol   当前终止列
 * @param isInterval      是否存在跨行跨列情况
 * @return List<PictureInfo>
 */
public static List<PictureInfo> getXSSFSPictureInfo(XSSFSheet sheet, Integer startCurrentRow, Integer endCurrentRow, Integer startCurrentCol, Integer endCurrentCol, Boolean isInterval) {
    List<PictureInfo> pictureInfos = new ArrayList<>();
    try {
        XSSFDrawing drawingPatriarch = sheet.getDrawingPatriarch();
        if (drawingPatriarch != null) {
            List<XSSFShape> shapeList = drawingPatriarch.getShapes();
            for (XSSFShape hssfShape : shapeList) {
                if (hssfShape instanceof XSSFPicture && hssfShape.getAnchor() instanceof XSSFClientAnchor) {
                    //获取表格文件对象
                    XSSFPicture picture = (XSSFPicture) hssfShape;
                    //获取表格文件属性
                    XSSFClientAnchor anchor = (XSSFClientAnchor) hssfShape.getAnchor();
                    //根据当前读取进度判断图片是否需要加载
                    if (isLoadPicture(startCurrentRow, endCurrentRow, startCurrentCol, endCurrentCol,
                            anchor.getRow1(), anchor.getRow2(), anchor.getCol1(), anchor.getCol2(), isInterval)) {
                        pictureInfos.add(new PictureInfo(anchor.getRow1(), anchor.getRow2(), anchor.getCol1(), anchor.getCol2(),
                                picture.getPictureData().getData(), picture.getPictureData().getMimeType()));
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return pictureInfos;
}

/**
 * 根据当前读取进度判断图片是否需要加载
 *
 * @param startCurrentRow 当前起始行
 * @param endCurrentRow   当前终止行
 * @param startCurrentCol 当前起始列
 * @param endCurrentCol   当前终止列
 * @param startPictureRow 文件资源起始行
 * @param endPictureRow   文件资源终止行
 * @param startPictureCol 文件资源起始列
 * @param endPictureCol   文件资源终止列
 * @param isInterval      是否存在跨行跨列情况
 * @return true-允许加载,false-不允许加载
 */
public static boolean isLoadPicture(Integer startCurrentRow, Integer endCurrentRow, Integer startCurrentCol, Integer endCurrentCol,
                                    int startPictureRow, int endPictureRow, int startPictureCol, int endPictureCol, Boolean isInterval) {
    int _startCurrentRow = startCurrentRow == null ? startPictureRow : startCurrentRow;
    int _endCurrentRow = endCurrentRow == null ? endPictureRow : endCurrentRow;
    int _startCurrentCol = startCurrentCol == null ? startPictureCol : startCurrentCol;
    int _endCurrentCol = endCurrentCol == null ? endPictureCol : endCurrentCol;
    if (!isInterval) {
        return (_startCurrentRow <= startPictureRow && _endCurrentRow >= endPictureRow &&
                _startCurrentCol <= startPictureCol && _endCurrentCol >= endPictureCol);
    } else {
        return ((Math.abs(_endCurrentRow - _startCurrentRow) + Math.abs(endPictureRow - startPictureRow) >= Math.abs(_endCurrentRow + _startCurrentRow - endPictureRow - startPictureRow)) &&
                (Math.abs(_endCurrentCol - _startCurrentCol) + Math.abs(endPictureCol - startPictureCol) >= Math.abs(_endCurrentCol + _startCurrentCol - endPictureCol - startPictureCol)));
    }
}

6.2创建图片实体类对象

/**
 * 图片对象
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PictureInfo {
    /**
     * 起始行
     */
    private int startRow;

    /**
     * 终止行
     */
    private int endRow;

    /**
     * 起始列
     */
    private int startCol;

    /**
     * 终止列
     */
    private int endCol;

    /**
     * 图片字节数据
     */
    private byte[] pictureData;

    /**
     * 图片文件类型
     */
    private String type;

    @Override
    public String toString() {
        return "PictureInfo{" +
                "startRow=" + startRow +
                ", endRow=" + endRow +
                ", startCol=" + startCol +
                ", endCol=" + endCol +
                '}';
    }
}

开始测试

public static void main(String[] args) {
    //设置页面大小
    RectangleReadOnly rectangleReadOnly = new RectangleReadOnly(PageSize.A4);
    try {
        File file = new File("D:/test/1/1.xls");
        Workbook workbook = new HSSFWorkbook(new FileInputStream(file));
        //创建document实例
        Document document = new Document(rectangleReadOnly);
        //写出pdf
        long l = System.currentTimeMillis();
        PdfWriter.getInstance(document, new FileOutputStream("D:/test/1/" + l + ".pdf"));
        //打开document
        document.open();
        //设置页边距(第二页生效)
        document.setMargins(20, 20, 30, 30);
        //生成表格
        PdfPTable pdfPTable = structureTable(workbook, file.getName(), 0, rectangleReadOnly.getWidth() - 40);
        //添加表格
        document.add(pdfPTable);
        //关闭document
        document.close();
        //打开本地文件
        Desktop.getDesktop().open(new File("D:/test/1/" + l + ".pdf"));
    } catch (Exception e) {
        e.printStackTrace();
    }
}