XSSF not able to copy style (POI)

Question

Not able to copy "STYLE" from .xlsx file to another.

Here is the code that I am using.

 public static void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {     
    if(styleMap != null) {     
        if(oldCell.getSheet().getWorkbook() .equals( newCell.getSheet().getWorkbook())){     
            newCell.setCellStyle(oldCell.getCellStyle());

        } else{     
            int stHashCode = oldCell.getCellStyle().hashCode();     
            XSSFCellStyle newCellStyle = styleMap.get(stHashCode);

            if(newCellStyle == null){     
                newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();     
                newCellStyle.cloneStyleFrom(oldCell.getCellStyle());     
                styleMap.put(stHashCode, newCellStyle);     
            }     
            newCell.setCellStyle(newCellStyle);     
        }     
    }     
    switch(oldCell.getCellType()) {     
        case XSSFCell.CELL_TYPE_STRING:     
            newCell.setCellValue(oldCell.getStringCellValue());     
            break;     
      case XSSFCell.CELL_TYPE_NUMERIC:     
            newCell.setCellValue(oldCell.getNumericCellValue());     
            break;     
        case XSSFCell.CELL_TYPE_BLANK:     
            newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);     
            break;     
        case XSSFCell.CELL_TYPE_BOOLEAN:     
            newCell.setCellValue(oldCell.getBooleanCellValue());     
            break;     
        case XSSFCell.CELL_TYPE_ERROR:     
            newCell.setCellErrorValue(oldCell.getErrorCellValue());     
            break;     
        case XSSFCell.CELL_TYPE_FORMULA:     
            newCell.setCellFormula(oldCell.getCellFormula());     
            break;     
        default:     
            break;     
    }     

}     

Same works with HSSF i.e for .xls file but doesn't work with XSSF (.xlsx)

Please give some suggestions or sample code to solve this.

Answer 1

It`s a bug in cloneStyleFrom

After spending much time I ended up with this ugly piece of code:

 private static void copyCellStyle(HSSFCell cell, HSSFCellStyle newCellStyle) {
    newCellStyle.setAlignment(cell.getCellStyle().getAlignment());
    newCellStyle.setBorderBottom(cell.getCellStyle().getBorderBottom());
    newCellStyle.setBorderLeft(cell.getCellStyle().getBorderLeft());
    newCellStyle.setBorderRight(cell.getCellStyle().getBorderRight());
    newCellStyle.setBorderTop(cell.getCellStyle().getBorderTop());
    newCellStyle.setBottomBorderColor(cell.getCellStyle().getBottomBorderColor());
    newCellStyle.setDataFormat(cell.getCellStyle().getDataFormat());
    newCellStyle.setFillBackgroundColor(cell.getCellStyle().getFillBackgroundColor());
    newCellStyle.setFillForegroundColor(cell.getCellStyle().getFillForegroundColor());
    newCellStyle.setFillPattern(cell.getCellStyle().getFillPattern());
    newCellStyle.setFont(cell.getCellStyle().getFont(cell.getSheet().getWorkbook()));
    newCellStyle.setHidden(cell.getCellStyle().getHidden());
    newCellStyle.setIndention(cell.getCellStyle().getIndention());
    newCellStyle.setLeftBorderColor(cell.getCellStyle().getLeftBorderColor());
    newCellStyle.setLocked(cell.getCellStyle().getLocked());
    newCellStyle.setRightBorderColor(cell.getCellStyle().getRightBorderColor());
    newCellStyle.setRotation(cell.getCellStyle().getRotation());
    newCellStyle.setShrinkToFit(cell.getCellStyle().getShrinkToFit());
    newCellStyle.setTopBorderColor(cell.getCellStyle().getTopBorderColor());
    // newCellStyle.setUserStyleName(cell.getCellStyle().getUserStyleName()); -> ignore
    newCellStyle.setVerticalAlignment(cell.getCellStyle().getVerticalAlignment());
    newCellStyle.setWrapText(cell.getCellStyle().getWrapText());
}

Answer 2

I believe the issue is produce by this statement:

    XSSFCellStyle newCellStyle = styleMap.get(stHashCode);

With this statement, you are basically saying newCellStyle = oldCellStyle. However, in this case oldCellStyle is linked to another workbook and you will have an error when you open your file because the link is broken.

Just using your code, deleting the that statement and the test, it should work fine:

    newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();     
    newCellStyle.cloneStyleFrom(oldCell.getCellStyle());     
    styleMap.put(stHashCode, newCellStyle);