POI excel转pdf

package com.example.demo.controller;

import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.Phrase;
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 lombok.experimental.UtilityClass;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;


@UtilityClass
public class ExcelToPdfUtil {

    /**
     * 日志输出
     */
    private static final Logger logger = Logger.getLogger(ExcelToPdfUtil.class);

    /**
     * 单元格队列
     */
    private static Set<String> cellSet = new HashSet<>();

    /**
     * Excel转PDF
     *
     * @param excelPath   Excel文件路径
     * @param pdfPath     PDF文件路径
     * @param excelSuffix Excel文件后缀
     */
    public static void excelToPdf(String excelPath, String pdfPath, String excelSuffix) {
        try (InputStream in = Files.newInputStream(Paths.get(excelPath));
             OutputStream out = Files.newOutputStream(Paths.get(pdfPath))) {
            ExcelToPdfUtil.excelToPdf(in, out, excelSuffix);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }
    }

    private static Map<String, int[]> mergedRegionCache = new HashMap<>();

    /**
     * Excel转PDF并写入输出流
     *
     * @param inStream    Excel输入流
     * @param outStream   PDF输出流
     * @param excelSuffix Excel类型 .xls 和 .xlsx
     * @throws Exception 异常信息
     */
    public static void excelToPdf(InputStream inStream, OutputStream outStream, String excelSuffix) throws Exception {
        Sheet sheet = getPoiSheetByFileStream(inStream, 0, excelSuffix);

        // 预加载合并区域到缓存（优化合并查询）
        preloadMergedRegions(sheet);

        // 获取列宽占比（修复sum=0问题）
        float[] widths = getColWidth(sheet);
        PdfPTable table = new PdfPTable(widths);
        table.setWidthPercentage(100);
        int colCount = widths.length;

        // 设置字体
        BaseFont baseFont = loadEmbeddedFont("/fonts/simsun.ttc");
        com.itextpdf.text.Font font = new com.itextpdf.text.Font(baseFont, 13);

        // 遍历行
        for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                // 插入空行（确保列数一致）
                for (int i = 0; i < colCount; i++) {
                    table.addCell(createEmptyCell(font));
                }
            } else {
                // 遍历所有列（修复列越界问题）
                for (int columnIndex = 0; columnIndex < colCount; columnIndex++) {
                    // 处理单元格（自动填充空值）
                    Cell cell = row.getCell(columnIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    PdfPCell pdfCell = excelCellToPdfCell(sheet, cell, baseFont);

                    // 检查合并区域（从缓存读取）
                    String key = rowIndex + "," + columnIndex;
                    if (mergedRegionCache.containsKey(key)) {
                        int[] span = mergedRegionCache.get(key);
                        // 跳过非起始单元格
                        if (span[0] == 0 && span[1] == 0) {
                            continue;
                        }
                        pdfCell.setRowspan(span[0]);
                        pdfCell.setColspan(span[1]);
                    }
                    table.addCell(pdfCell);
                }
            }
        }

        // 输出PDF
        Document document = new Document();
        PdfWriter.getInstance(document, outStream);
        document.open();
        document.add(table);
        document.close();
    }


    private static BaseFont loadEmbeddedFont(String fontPath) {
        InputStream fontStream = null;
        try {
            fontStream = ExcelToPdfUtil.class.getResourceAsStream(fontPath);
            if (fontStream == null) {
                throw new IOException("Font file not found: " + fontPath);
            }

            ByteArrayOutputStream buffer = new ByteArrayOutputStream();
            byte[] data = new byte[4096];
            int nRead;
            while ((nRead = fontStream.read(data)) != -1) {
                buffer.write(data, 0, nRead);
            }
            byte[] fontData = buffer.toByteArray();

            return BaseFont.createFont(
                    "simsun.ttc,0",
                    BaseFont.IDENTITY_H,
                    BaseFont.EMBEDDED,
                    true,
                    fontData,
                    null
            );
        } catch (Exception e) {
            throw new RuntimeException("Failed to load font: " + fontPath, e);
        } finally {
            if (fontStream != null) {
                try {
                    fontStream.close();
                } catch (IOException e) {
                    // 日志记录
                }
            }
        }
    }

    /**
     * 单元格转换，poi cell 转换为 itext cell
     *
     * @param sheet     poi sheet页
     * @param excelCell poi 单元格
     * @param baseFont  基础字体
     * @return PDF单元格
     */
    private static PdfPCell excelCellToPdfCell(Sheet sheet, Cell excelCell, BaseFont baseFont) throws Exception {
        if (Objects.isNull(excelCell)) {
            return createPdfPCell(null);
        }

        PdfPCell pCell;

        Font excelFont = getExcelFont(sheet, excelCell);
        //设置单元格字体
        com.itextpdf.text.Font pdFont = new com.itextpdf.text.Font(baseFont, excelFont.getFontHeightInPoints(), excelFont.getBold() ? 1 : 0, BaseColor.BLACK);
        Integer border = hasBorder(excelCell) ? null : 0;
        String excelCellValue = getExcelCellValue(excelCell);
        pCell = createPdfPCell(excelCellValue, border, excelCell.getRow().getHeightInPoints(), pdFont);

        // 水平居中
        pCell.setHorizontalAlignment(getHorAlign(excelCell.getCellStyle().getAlignment().getCode()));
        // 垂直对齐
        pCell.setVerticalAlignment(getVerAlign(excelCell.getCellStyle().getVerticalAlignment().getCode()));
        return pCell;
    }



    /**
     * Excel文档输入流转换为对应的workbook及获取对应的sheet
     *
     * @param inputStream Excel文档输入流
     * @param sheetNo     sheet编号，默认0 第一个sheet
     * @param excelSuffix 文件类型 .xls和.xlsx
     * @return poi sheet
     * @throws IOException 异常
     */
    public static Sheet getPoiSheetByFileStream(InputStream inputStream, int sheetNo, String excelSuffix) throws IOException {
        Workbook workbook;
        if (excelSuffix.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(inputStream);
        } else {
            workbook = new HSSFWorkbook(inputStream);
        }
        return workbook.getSheetAt(sheetNo);
    }

    /**
     * 创建itext pdf 单元格
     *
     * @param content       单元格内容
     * @param border        边框
     * @param minimumHeight 高度
     * @param pdFont        字体
     * @return pdf cell
     */
// 常量定义
    private static final com.itextpdf.text.Font DEFAULT_FONT = new com.itextpdf.text.Font();
    public static final int NO_BORDER = PdfPCell.NO_BORDER;
    public static final float DEFAULT_MIN_HEIGHT = 13f;

    // 最简版本
    private static PdfPCell createPdfPCell(String content) {
        return createPdfPCell(content, NO_BORDER, DEFAULT_MIN_HEIGHT, DEFAULT_FONT);
    }

    // 完整版本
    private static PdfPCell createPdfPCell(
            String content,
            int border,
            float minimumHeight,
            com.itextpdf.text.Font pdFont
    ) {
        if (minimumHeight < 0) {
            throw new IllegalArgumentException("minimumHeight 不能为负数");
        }

        String contentValue = content == null ? "" : content;
        com.itextpdf.text.Font font = pdFont == null ? DEFAULT_FONT : pdFont;

        PdfPCell cell = new PdfPCell(new Phrase(contentValue, font));
        cell.setBorder(border);
        cell.setMinimumHeight(minimumHeight);

        return cell;
    }


    /**
     * excel垂直对齐方式映射到pdf对齐方式
     *
     * @param align 对齐
     * @return 结果
     */
    private static int getVerAlign(int align) {
        switch (align) {
            case 2:
                return com.itextpdf.text.Element.ALIGN_BOTTOM;
            case 3:
                return com.itextpdf.text.Element.ALIGN_TOP;
            default:
                return com.itextpdf.text.Element.ALIGN_MIDDLE;
        }
    }

    /**
     * excel水平对齐方式映射到pdf水平对齐方式
     *
     * @param align 对齐
     * @return 结果
     */
    private static int getHorAlign(int align) {
        switch (align) {
            case 1:
                return com.itextpdf.text.Element.ALIGN_LEFT;
            case 3:
                return com.itextpdf.text.Element.ALIGN_RIGHT;
            default:
                return com.itextpdf.text.Element.ALIGN_CENTER;
        }
    }

    /*============================================== POI获取图片及文本内容工具方法 ==============================================*/

    /**
     * 获取字体
     *
     * @param sheet excel 转换的sheet页
     * @param cell  单元格
     * @return 字体
     */
    private static Font getExcelFont(Sheet sheet, Cell cell) {
        // xls
        if (sheet instanceof HSSFSheet) {
            Workbook workbook = sheet.getWorkbook();
            return ((HSSFCell) cell).getCellStyle().getFont(workbook);
        }
        // xlsx
        return ((XSSFCell) cell).getCellStyle().getFont();
    }

    /**
     * 判断excel单元格是否有边框
     *
     * @param excelCell 单元格
     * @return 结果
     */
    private static boolean hasBorder(Cell excelCell) {
        short top = excelCell.getCellStyle().getBorderTop().getCode();
        short bottom = excelCell.getCellStyle().getBorderBottom().getCode();
        short left = excelCell.getCellStyle().getBorderLeft().getCode();
        short right = excelCell.getCellStyle().getBorderRight().getCode();
        return top + bottom + left + right > 2;
    }


    /**
     * 获取excel中每列宽度的占比
     *
     * @param sheet 表
     * @return 结果
     */
    private static float[] getColWidth(Sheet sheet) {
        int maxColIndex = getMaxColumnIndex(sheet);
        if (maxColIndex == 0) return new float[0];

        int[] colWidths = new int[maxColIndex];
        int sum = 0;
        for (int i = 0; i < maxColIndex; i++) {
            colWidths[i] = sheet.getColumnWidth(i);
            sum += colWidths[i];
        }

        float[] colWidthPer = new float[maxColIndex];
        if (sum == 0) {
            // 默认均分列宽
            float equalWidth = 100f / maxColIndex;
            Arrays.fill(colWidthPer, equalWidth);
        } else {
            for (int i = 0; i < maxColIndex; i++) {
                colWidthPer[i] = (float) colWidths[i] / sum * 100;
            }
        }
        return colWidthPer;
    }

    // 预加载合并区域（优化性能）
    private static void preloadMergedRegions(Sheet sheet) {
        mergedRegionCache.clear();
        for (CellRangeAddress range : sheet.getMergedRegions()) {
            int firstRow = range.getFirstRow();
            int firstCol = range.getFirstColumn();
            int rowSpan = range.getLastRow() - firstRow + 1;
            int colSpan = range.getLastColumn() - firstCol + 1;

            // 标记合并区域的起始单元格
            String startKey = firstRow + "," + firstCol;
            mergedRegionCache.put(startKey, new int[]{rowSpan, colSpan});

            // 标记非起始单元格（后续跳过）
            for (int r = firstRow; r <= range.getLastRow(); r++) {
                for (int c = firstCol; c <= range.getLastColumn(); c++) {
                    if (r != firstRow || c != firstCol) {
                        String key = r + "," + c;
                        mergedRegionCache.put(key, new int[]{0, 0}); // 0表示非起始
                    }
                }
            }
        }
    }

    // 创建空单元格
    private static PdfPCell createEmptyCell(com.itextpdf.text.Font font) {
        PdfPCell cell = new PdfPCell(new Phrase("", (com.itextpdf.text.Font) font));
        cell.setBorder(PdfPCell.NO_BORDER);
        return cell;
    }

    /**
     * 获取excel中列数最多的行号
     *
     * @param sheet 表
     * @return 结果
     */
    private static int getMaxColumnIndex(Sheet sheet) {
        int maxCol = 0;
        for (Row row : sheet) {
            if (row != null) {
                int lastCellNum = row.getLastCellNum(); // 返回最后单元格索引 +1
                maxCol = Math.max(maxCol, lastCellNum);
            }
        }
        return maxCol;
    }

    /**
     * poi 根据单元格类型获取单元格内容
     *
     * @param excelCell poi单元格
     * @return 单元格内容文本
     */
    public static String getExcelCellValue(Cell excelCell) {
        if (excelCell == null) {
            return "";
        }
        // 判断数据的类型
        CellType cellType = excelCell.getCellType();

        if (cellType == CellType.STRING) {
            return excelCell.getStringCellValue();
        }
        if (cellType == CellType.BOOLEAN) {
            return String.valueOf(excelCell.getBooleanCellValue());
        }
        DataFormatter formatter = new DataFormatter();
//        if (cellType == CellType.FORMULA) {
//            FormulaEvaluator evaluator = excelCell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
//            return formatter.formatCellValue(excelCell, evaluator);
//        }
        if (cellType == CellType.NUMERIC) {
            if (DateUtil.isCellDateFormatted(excelCell)) {
                // 处理日期
                return formatter.formatCellValue(excelCell);
            } else if (excelCell.getCellStyle().getDataFormat() == 0) {
                // 处理通用数值
                return formatter.formatCellValue(excelCell);
            }
        }
        if (cellType == CellType.ERROR) {
            return "非法字符";
        }
        return "";
    }

}