java工具类1-ExcelUtils

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.io.FileInputStream;
import java.io.InputStream;

public class ExcelUtils {
    private Workbook workbook;

    // 构造方法：初始化Workbook对象
    public ExcelUtils(String filePath) throws Exception {
        FileInputStream fileInputStream = new FileInputStream(filePath);
        this.workbook = new XSSFWorkbook(fileInputStream);
    }

    // 写入数据到指定的单元格
    public void writeCellData(int sheetIndex, int rowIndex, int colIndex, String value, String fileName) throws Exception {
        Sheet sheet = workbook.getSheetAt(sheetIndex);
        Row row = sheet.getRow(rowIndex);
        if (row == null) row = sheet.createRow(rowIndex);
        Cell cell = row.getCell(colIndex);
        if (cell == null) cell = row.createCell(colIndex);

        cell.setCellValue(value);

        saveWorkbookToFile(fileName);
    }

    /**
     * 合并单元格并设置边框和字体样式
     *
     * @param startRow            起始行号
     * @param endRow              结束行号
     * @param startCol            起始列号
     * @param endCol              结束列号
     * @param fileName            文件路径
     * @param horizontalAlignment 水平对齐方式
     * @param fontName            字体名称：例如 "Arial", "Calibri"
     * @param fontSize            字体大小：单位为磅，如 12
     * @param fontColor           字体颜色：可以使用 short 类型的颜色值，使用 IndexedColors 中的颜色，例如 IndexedColors.RED.getIndex() 或 new XSSFColor(new java.awt.Color(255, 0, 0))
     * @param isBold              是否加粗：true 表示加粗，false 表示不加粗
     * @param isItalic            是否斜体：true 表示斜体，false 表示不斜体
     * @throws Exception
     */
    public void mergeCells(int startRow, int endRow, int startCol, int endCol,
                           String fileName, String horizontalAlignment, String fontName,
                           int fontSize, short fontColor, boolean isBold, boolean isItalic, BorderStyle borderTop, BorderStyle borderBottom, BorderStyle borderLeft, BorderStyle borderRight) throws Exception {
        Sheet sheet = workbook.getSheetAt(0);

        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol, endCol));

        // 创建字体并设置字体属性
        Font font = workbook.createFont();
        font.setFontName(fontName);  // 设置字体
        font.setFontHeightInPoints((short) fontSize);  // 设置字体大小
        font.setColor(fontColor);  // 设置字体颜色
        font.setBold(isBold);  // 是否加粗
        font.setItalic(isItalic);  // 是否斜体

        // 创建CellStyle，设置对齐方式和字体
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setBorderTop(borderTop);
        cellStyle.setBorderBottom(borderBottom);
        cellStyle.setBorderLeft(borderLeft);
        cellStyle.setBorderRight(borderRight);

        // 设置垂直对齐样式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置水平对齐样式
        switch (horizontalAlignment) {
            case "GENERAL":
                // 默认对齐方式，通常是根据单元格内容自动决定对齐方式。文本居左，数字居右，混合内容时，可能居中
                cellStyle.setAlignment(HorizontalAlignment.GENERAL);
                break;
            case "LEFT":
                // 左对齐：内容从单元格的左边开始排列
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                break;
            case "CENTER":
                // 分布对齐
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                break;
            case "RIGHT":
                // 右对齐
                cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                break;
            case "FILL":
                // 填充对齐
                cellStyle.setAlignment(HorizontalAlignment.FILL);
                break;
            case "CENTER_SELECTION":
                // 选择区域居中对齐
                cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
                break;
            case "DISTRIBUTED":
                // 分布对齐
                cellStyle.setAlignment(HorizontalAlignment.DISTRIBUTED);
                break;
            default:
                // 默认情况，使用通用对齐方式
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
        }

        // 设置字体
        cellStyle.setFont(font);

        // 将样式应用到合并的单元格区域
        for (int rowNum = startRow; rowNum <= endRow; rowNum++) {
            Row row = sheet.getRow(rowNum);
            for (int colNum = startCol; colNum <= endCol; colNum++) {
                //判断row不为空
                if (row != null) {
                    Cell cell = row.getCell(colNum);
                    if (cell == null) {
                        cell = row.createCell(colNum);
                    }
                    cell.setCellStyle(cellStyle);
                }

            }
        }
        // 保存文件
        saveWorkbookToFile(fileName);
    }


    /**
     * 设置指定单元格样式，包括四周边框线
     *
     * @param rowIndex            单元格所在行索引
     * @param colIndex            单元格所在列索引
     * @param fileName            保存文件的路径
     * @param borderTop           上边框样式
     * @param borderBottom        下边框样式
     * @param borderLeft          左边框样式
     * @param borderRight         右边框样式
     * @param fontName            字体名称
     * @param fontSize            字体大小
     * @param fontColor           字体颜色
     * @param isBold              是否加粗
     * @param isItalic            是否斜体
     * @param horizontalAlignment 水平对齐方式
     * @throws Exception 如果处理出现问题
     */
    public void setCellStyleWithBorders(int rowIndex, int colIndex, String fileName,
                                        BorderStyle borderTop, BorderStyle borderBottom,
                                        BorderStyle borderLeft, BorderStyle borderRight,
                                        String fontName, int fontSize, short fontColor,
                                        boolean isBold, boolean isItalic, String horizontalAlignment) throws Exception {
        Sheet sheet = workbook.getSheetAt(0);

        // 获取或创建指定的单元格
        Row row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        Cell cell = row.getCell(colIndex);
        if (cell == null) {
            cell = row.createCell(colIndex);
        }

        // 创建字体并设置字体属性
        Font font = workbook.createFont();
        font.setFontName(fontName);
        font.setFontHeightInPoints((short) fontSize);
        font.setColor(fontColor);
        font.setBold(isBold);
        font.setItalic(isItalic);

        // 创建单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);

        // 设置四周边框样式
        cellStyle.setBorderTop(borderTop);
        cellStyle.setBorderBottom(borderBottom);
        cellStyle.setBorderLeft(borderLeft);
        cellStyle.setBorderRight(borderRight);

        // 设置水平对齐方式
        switch (horizontalAlignment) {
            case "GENERAL":
                cellStyle.setAlignment(HorizontalAlignment.GENERAL);
                break;
            case "LEFT":
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                break;
            case "CENTER":
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                break;
            case "RIGHT":
                cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                break;
            case "FILL":
                cellStyle.setAlignment(HorizontalAlignment.FILL);
                break;
            case "CENTER_SELECTION":
                cellStyle.setAlignment(HorizontalAlignment.CENTER_SELECTION);
                break;
            case "DISTRIBUTED":
                cellStyle.setAlignment(HorizontalAlignment.DISTRIBUTED);
                break;
            default:
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
        }

        // 设置垂直对齐方式
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 应用样式到单元格
        cell.setCellStyle(cellStyle);

        // 保存文件
        saveWorkbookToFile(fileName);
    }


    //插入图片并水平垂直嵌入单元格。
    public void insertImageToCellCentered(int sheetIndex, int rowIndex, int colIndex, String imagePath, String fileName) throws Exception {
        Sheet sheet = workbook.getSheetAt(sheetIndex);

        // 获取图片流，使用 try-with-resources 确保资源关闭
        try (InputStream imageStream = new FileInputStream(imagePath);
             ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) {

            byte[] buffer = new byte[1024];
            int bytesRead;
            while ((bytesRead = imageStream.read(buffer)) != -1) {
                byteArrayOutputStream.write(buffer, 0, bytesRead);
            }
            byte[] imageBytes = byteArrayOutputStream.toByteArray();

            // 添加图片到Excel文件
            int pictureIndex = workbook.addPicture(imageBytes, Workbook.PICTURE_TYPE_PNG);

            // 创建绘图对象
            Drawing<?> drawing = sheet.createDrawingPatriarch();
            CreationHelper helper = workbook.getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();

            // 获取单元格宽度和高度
            float cellWidth = sheet.getColumnWidthInPixels(colIndex); // 单元格宽度（像素）
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            float cellHeight = row.getHeightInPoints() / 72 * 96; // 单元格高度（像素）

            // 获取图片原始宽度和高度
            BufferedImage bufferedImage = ImageIO.read(new ByteArrayInputStream(imageBytes));
            int imageWidth = bufferedImage.getWidth();
            int imageHeight = bufferedImage.getHeight();

            // 计算缩放比例（以宽度和高度的最小比例为准）
            double scaleWidth = cellWidth / imageWidth;
            double scaleHeight = cellHeight / imageHeight;
            double scale = Math.min(scaleWidth, scaleHeight);

            // 计算图片实际显示大小
            int scaledWidth = (int) (imageWidth * scale);
            int scaledHeight = (int) (imageHeight * scale);

            // 计算图片在单元格中的偏移量（用于居中）
            int dx1 = (int) ((cellWidth - scaledWidth) / 2);
            int dy1 = (int) ((cellHeight - scaledHeight) / 2);

            // 设置图片的锚点（居中对齐）
            anchor.setCol1(colIndex);
            anchor.setRow1(rowIndex);
            anchor.setDx1(dx1 * Units.EMU_PER_PIXEL); // 偏移量转换为EMU单位
            anchor.setDy1(dy1 * Units.EMU_PER_PIXEL);

            anchor.setCol2(colIndex + 1);
            anchor.setRow2(rowIndex + 1);

            // 插入图片
            Picture picture = drawing.createPicture(anchor, pictureIndex);
        }

        // 保存文件
        saveWorkbookToFile(fileName);
    }

    // 将修改后的Workbook保存到指定文件
    private void saveWorkbookToFile(String fileName) throws Exception {
        try (FileOutputStream fileOutputStream = new FileOutputStream(fileName)) {
            workbook.write(fileOutputStream);
        } catch (Exception e) {
            throw new Exception("保存文件失败: " + fileName, e);
        }
    }


}