I am trying to copy a sheet from one workbook into another along with the styles (Cell color and merge) I am using the below code, it is copying but it is filling the already colored cells with black by default. I am trying to connect the dots for this issue but unable to. What am i missing ?

Apache poi - 4.1.1 The test1 method calls the SheetUtil (using copysheet method from that)

public class SheetUtil {

private static void removeRows(Sheet destSheet) {
    if (null != destSheet) {
        for (int i = destSheet.getFirstRowNum(); i <= destSheet.getLastRowNum(); i  ) {
            Row row = destSheet.getRow(i);
            if (null != row) {
                destSheet.removeRow(row);
            }
        }
    }
}

private static void addRows(Sheet destSheet, int totalRowCount) {
    if (null != destSheet) {
        for (int i = 0; i <= totalRowCount; i  ) {
            destSheet.createRow(i);
        }
    }
}

static void copyMergedRegion(Sheet srcSheet, Sheet destSheet) {
    for (int i = 0; i < srcSheet.getNumMergedRegions(); i  ) {
        destSheet.addMergedRegion(srcSheet.getMergedRegion(i));
    }
}

private static void copyCell(Cell srcCell, Cell destCell, Map<Integer, CellStyle> styleMap) {
    if (styleMap != null) {
        int srcCellHashCode = srcCell.getCellStyle().hashCode();
        CellStyle newCellStyle = styleMap.get(srcCellHashCode);
        if (null == newCellStyle) {
            newCellStyle = destCell.getSheet().getWorkbook().createCellStyle();
            newCellStyle.setAlignment(srcCell.getCellStyle().getAlignment());
            newCellStyle.setBorderBottom(srcCell.getCellStyle().getBorderBottom());
            newCellStyle.setBorderLeft(srcCell.getCellStyle().getBorderLeft());
            newCellStyle.setBorderRight(srcCell.getCellStyle().getBorderRight());
            newCellStyle.setBorderTop(srcCell.getCellStyle().getBorderTop());
            newCellStyle.setDataFormat(srcCell.getCellStyle().getDataFormat());
            newCellStyle.setFillBackgroundColor(srcCell.getCellStyle().getFillBackgroundColor());
            newCellStyle.setFillForegroundColor(srcCell.getCellStyle().getFillForegroundColor());
            newCellStyle.setFillPattern(srcCell.getCellStyle().getFillPattern());
            newCellStyle.setVerticalAlignment(srcCell.getCellStyle().getVerticalAlignment());
            newCellStyle.setWrapText(srcCell.getCellStyle().getWrapText());
            styleMap.put(srcCellHashCode, newCellStyle);
        }
        destCell.setCellStyle(newCellStyle);
    }

    if (srcCell.getCellType() == CellType.BLANK) {
        destCell.setBlank();
    } else if (srcCell.getCellType() == CellType.STRING) {
        destCell.setCellValue(srcCell.getStringCellValue());
    } else if (srcCell.getCellType() == CellType.NUMERIC) {
        destCell.setCellValue(srcCell.getNumericCellValue());
    } else if (srcCell.getCellType() == CellType.BOOLEAN) {
        destCell.setCellValue(srcCell.getBooleanCellValue());
    } else if (srcCell.getCellType() == CellType.FORMULA) {
        destCell.setCellFormula(srcCell.getCellFormula());
    } else if (srcCell.getCellType() == CellType.ERROR) {
        destCell.setCellErrorValue(srcCell.getErrorCellValue());
    }
}

private static void copyRow(Row srcRow, Row destRow, Map<Integer, CellStyle> styleMap) {
    destRow.setHeight(srcRow.getHeight());
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j  ) {
        Cell srcCell = srcRow.getCell(j);
        if (srcCell != null) {
            Cell destCell = destRow.createCell(j);
            copyCell(srcCell, destCell, styleMap);
        }
    }
}

/**
 * 
 * Copy a sheet from one workbook to another workbook. 
 * 
 * @param srcSheet
 * @param destSheet
 */
public static void copySheet(Sheet srcSheet, Sheet destSheet) {
    removeRows(destSheet);
    addRows(destSheet, srcSheet.getLastRowNum());
    copyMergedRegion(srcSheet, destSheet);
    Map<Integer, CellStyle> styleMap = new HashMap<Integer, CellStyle>();
    for (int i = srcSheet.getFirstRowNum(); i <= srcSheet.getLastRowNum(); i  ) {
        Row srcRow = srcSheet.getRow(i);
        if (null == srcRow) {
            destSheet.createRow(i);
        } else {
            Row destRow = destSheet.createRow(i);
            copyRow(srcRow, destRow, styleMap);
        }
    }
  
}

public void test1() {
    try {
        System.out.println(" test1() : "   new Date(System.currentTimeMillis()));
        File templateFile = new File("C:/poiTest/Template_V2.xlsx");
        InputStream inputStream = new FileInputStream(templateFile);
        Workbook merWorkBook = WorkbookFactory.create(inputStream);
        inputStream.close();
        Sheet destPdrSheet = merWorkBook.getSheet("PDR");

        File pdrFile = new File("C:/poiTest/P23163.xlsx");
        InputStream pdrInputStream = new FileInputStream(pdrFile);
        Workbook pdrWorkBook = WorkbookFactory.create(pdrInputStream);
        pdrInputStream.close();
        Sheet srcPdrSheet = pdrWorkBook.getSheetAt(0);

        SheetUtil.copySheet(srcPdrSheet, destPdrSheet);

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        merWorkBook.setForceFormulaRecalculation(true);
        merWorkBook.write(byteArrayOutputStream);

        FileOutputStream resultFile = new FileOutputStream(new File("C:/poiTest/outputXLSX1.xlsx"));
        byteArrayOutputStream.writeTo(resultFile);
        System.out.println(" test1() : "   new Date(System.currentTimeMillis()));
    } catch (Exception e) {
        e.printStackTrace();
    }
}



public static void main(String[] args) {
    SheetUtil obj = new SheetUtil();
    obj.test1();

}

}

CodePudding user response：

There is a main difference between *.xls and *.xlsx when using cell interior colors. The former binary *.xls only uses indexed colors out of a color palette. The current Office Open XML *.xlsx uses RGB colors directly. These colors are not indexed.

CellStyle of Apache POI tries to support both. But it lacks the possibility to set RGB colors directly. There is Color getFillBackgroundColorColor() and Color getFillForegroundColorColor() to get the Colors . But there is only void setFillBackgroundColor(short bg) and void setFillForegroundColor(short bg) to set color indexes. There are no methods to set Colors.

If in Office Open XML *.xlsx RGB colors are used in cell interior, then there are no indexes. That's why CellStyle.getFillBackgroundColor() and CellStyle.getFillForegroundColor() return 0 in that case. And CellStyle.setFillBackgroundColor(0) and CellStyle.getFillForegroundColor(0) then set indexed colors having index 0. This is black.

I don't know a good workaround except giving up using org.apache.poi.ss.usermodel and explicitly using org.apache.poi.xssf.usermodel. XSSFCellStyle provides XSSFColor getFillBackgroundColorColor() and XSSFColor getFillForegroundColorColor() as well as void setFillBackgroundColor(XSSFColor color) and void setFillForegroundColor(XSSFColor color). So one should use only this for Office Open XML *.xlsx and not use the methods which are using indexed colors.

There is already a bug report about this.