使用POI-OOXML 4.1.2拆分XLSX时样式复制不一致问题求助

问题分析 #

Apache POI 4.1.2中直接复制单元格样式时，容易出现填充色映射错误，这是因为POI在处理跨工作簿的样式引用时，会默认复用目标工作簿中已有的相似样式，而非完全克隆源样式的RGB值，导致颜色被错误替换。

解决方案 #

不要直接使用cloneStyleFrom，而是手动克隆样式的所有属性，尤其是填充色部分，并且要在目标工作簿中创建新的CellStyle对象，避免样式引用冲突。

完整修复代码 #

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class XlsxSplitter {
    public static void main(String[] args) throws IOException {
        try (Workbook sourceWorkbook = new XSSFWorkbook(new FileInputStream("source.xlsx"))) {
            for (int sheetIndex = 0; sheetIndex < sourceWorkbook.getNumberOfSheets(); sheetIndex++) {
                Sheet sourceSheet = sourceWorkbook.getSheetAt(sheetIndex);
                try (Workbook targetWorkbook = new XSSFWorkbook()) {
                    Sheet targetSheet = targetWorkbook.createSheet(sourceSheet.getSheetName());
                    copySheetWithStyle(sourceSheet, targetSheet, targetWorkbook);
                    try (FileOutputStream fos = new FileOutputStream("split_" + sourceSheet.getSheetName() + ".xlsx")) {
                        targetWorkbook.write(fos);
                    }
                }
            }
        }
    }

    private static void copySheetWithStyle(Sheet sourceSheet, Sheet targetSheet, Workbook targetWorkbook) {
        // 复制行和单元格
        for (int rowIndex = 0; rowIndex <= sourceSheet.getLastRowNum(); rowIndex++) {
            Row sourceRow = sourceSheet.getRow(rowIndex);
            if (sourceRow == null) continue;
            Row targetRow = targetSheet.createRow(rowIndex);
            targetRow.setHeight(sourceRow.getHeight());

            for (int cellIndex = 0; cellIndex < sourceRow.getLastCellNum(); cellIndex++) {
                Cell sourceCell = sourceRow.getCell(cellIndex);
                if (sourceCell == null) continue;
                Cell targetCell = targetRow.createCell(cellIndex, sourceCell.getCellType());

                // 复制单元格值
                switch (sourceCell.getCellType()) {
                    case STRING:
                        targetCell.setCellValue(sourceCell.getStringCellValue());
                        break;
                    case NUMERIC:
                        targetCell.setCellValue(sourceCell.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        targetCell.setCellValue(sourceCell.getBooleanCellValue());
                        break;
                    case FORMULA:
                        targetCell.setCellFormula(sourceCell.getCellFormula());
                        break;
                    default:
                        targetCell.setCellValue(sourceCell.getStringCellValue());
                }

                // 克隆样式（核心修复）
                CellStyle sourceStyle = sourceCell.getCellStyle();
                CellStyle targetStyle = targetWorkbook.createCellStyle();
                cloneCellStyle(sourceStyle, targetStyle, targetWorkbook);
                targetCell.setCellStyle(targetStyle);
            }
        }

        // 复制列宽
        for (int colIndex = 0; colIndex < sourceSheet.getRow(0).getLastCellNum(); colIndex++) {
            targetSheet.setColumnWidth(colIndex, sourceSheet.getColumnWidth(colIndex));
        }
    }

    private static void cloneCellStyle(CellStyle sourceStyle, CellStyle targetStyle, Workbook targetWorkbook) {
        // 复制基础样式属性
        targetStyle.setAlignment(sourceStyle.getAlignment());
        targetStyle.setVerticalAlignment(sourceStyle.getVerticalAlignment());
        targetStyle.setBorderTop(sourceStyle.getBorderTop());
        targetStyle.setBorderBottom(sourceStyle.getBorderBottom());
        targetStyle.setBorderLeft(sourceStyle.getBorderLeft());
        targetStyle.setBorderRight(sourceStyle.getBorderRight());
        targetStyle.setTopBorderColor(sourceStyle.getTopBorderColor());
        targetStyle.setBottomBorderColor(sourceStyle.getBottomBorderColor());
        targetStyle.setLeftBorderColor(sourceStyle.getLeftBorderColor());
        targetStyle.setRightBorderColor(sourceStyle.getRightBorderColor());
        targetStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 关键：手动复制填充色RGB值
        if (sourceStyle instanceof org.apache.poi.xssf.usermodel.XSSFCellStyle) {
            XSSFCellStyle xssfSourceStyle = (XSSFCellStyle) sourceStyle;
            XSSFColor sourceColor = xssfSourceStyle.getFillForegroundColorColor();
            if (sourceColor != null) {
                byte[] rgb = sourceColor.getRGB();
                if (rgb != null) {
                    XSSFColor targetColor = new XSSFColor(rgb, null);
                    ((org.apache.poi.xssf.usermodel.XSSFCellStyle) targetStyle).setFillForegroundColorColor(targetColor);
                }
            }
        } else {
            // 针对HSSF的兼容处理（如果需要）
            targetStyle.setFillForegroundColor(sourceStyle.getFillForegroundColor());
            targetStyle.setFillBackgroundColor(sourceStyle.getFillBackgroundColor());
        }

        // 复制字体
        Font sourceFont = sourceStyle.getFont();
        Font targetFont = targetWorkbook.createFont();
        cloneFont(sourceFont, targetFont);
        targetStyle.setFont(targetFont);
    }

    private static void cloneFont(Font sourceFont, Font targetFont) {
        targetFont.setFontName(sourceFont.getFontName());
        targetFont.setFontHeight(sourceFont.getFontHeight());
        targetFont.setBold(sourceFont.getBold());
        targetFont.setItalic(sourceFont.getItalic());
        targetFont.setUnderline(sourceFont.getUnderline());
        targetFont.setColor(sourceFont.getColor());
    }
}

Maven依赖（保持原版本） #

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

关键修复点 #

• 避免使用cloneStyleFrom，改为手动创建目标工作簿的CellStyle，防止跨工作簿样式引用冲突
• 针对XSSFCellStyle，直接提取源样式的RGB字节数组，创建新的XSSFColor赋值给目标样式，绕过POI的颜色映射逻辑
• 同时克隆字体等其他样式属性，保证样式完全一致

内容的提问来源于stack exchange，提问作者Austinu