Preserving worksheet formatting during cell-by-cell copy across workbooks using Apache POI

Question

This is my third post on the topic of style/format preservation while copying worksheets, across workbooks, using Apache POI (see first and second).

Having failed to preserve source worksheet formatting during row-by-row copy, I have proceeded to attempt a cell-by-cell copy. Although I am getting the content from the source worksheet, all formatting is lost.

Any suggestions?

Source Code:

    public static void copyXSSFSheet2(String srcFilename, String srcSheetname, String destFilename, String destSheetname)
            throws IOException, InvalidFormatException {
        
        // Create source and destination workbook objects, given the filenames
        XSSFWorkbook srcWorkbook = new XSSFWorkbook(new File(srcFilename));
        
        XSSFWorkbook destWorkbook = new XSSFWorkbook(new FileInputStream(destFilename));
        // Destination workbook instantiated differently to get past the following exception:
        //        org.apache.poi.ooxml.POIXMLException: java.io.EOFException:
        //        Unexpected end of ZLIB input stream
        // As per https://stackoverflow.com/a/54695626
        
        // Instantiate the sheet objects
        XSSFSheet srcSheet = srcWorkbook.getSheet(srcSheetname);
        XSSFSheet destSheet = destWorkbook.createSheet(destSheetname);
        
        // Iterate over the source sheet, row by row, and copy into the destination sheet
        // cell by cell
        int destRowNum = 0;
        for (Row srcRow: srcSheet) {
            XSSFRow srcXSSFRow = (XSSFRow) srcRow;
            XSSFRow destXSSFRow = destSheet.createRow(destRowNum++);
            
            int srcColNum = srcXSSFRow.getFirstCellNum();
            for (Cell srcCell : srcXSSFRow) {
                Cell destCell = destXSSFRow.createCell(srcColNum++);
                
//              CellStyle srcCellStyle = srcCell.getCellStyle();
//              destCell.setCellStyle(srcCellStyle);
                // Gives the following exception:
                //     java.lang.IllegalArgumentException: This Style does not belong to the supplied
                //     Workbook Styles Source. Are you trying to assign a style from one workbook to
                //     the cell of a different workbook?
                
//              CellStyle srcCellStyle = srcCell.getCellStyle();
//              CellStyle destCellStyle = new XSSFCellStyle(new StylesTable());;
//              destCellStyle.cloneStyleFrom(srcCellStyle);
//              destCell.setCellStyle(srcCellStyle);
                // Gives the following exception:
                //     java.lang.IllegalArgumentException: This Style does not belong to the supplied
                //     Workbook Styles Source. Are you trying to assign a style from one workbook to
                //     the cell of a different workbook?
                
                switch (srcCell.getCellType()) {
                    case STRING:
                        destCell.setCellValue(srcCell.getRichStringCellValue().getString());
                        break;
                        
                    case NUMERIC:
                        if (DateUtil.isCellDateFormatted(srcCell)) {
                            destCell.setCellValue(srcCell.getDateCellValue());
                        }
                        else {
                            destCell.setCellValue(srcCell.getNumericCellValue());
                        }
                        break;
                        
                    case BOOLEAN:
                        destCell.setCellValue(srcCell.getBooleanCellValue());
                        break;
                        
                    case FORMULA:
                        destCell.setCellValue(srcCell.getCellFormula());
                        break;
                        
                    case BLANK:
                        destCell.setCellValue("");
                        break;
                        
                    default:
                        destCell.setCellValue("");
                }
            }
        }
        
        // Final cleanup
        srcWorkbook.close();
        
        FileOutputStream fos = new FileOutputStream(new File(destFilename));
        destWorkbook.write(fos);
        destWorkbook.close();
        fos.close();
    }

GreyBeardedGeek · Answer 1 · 2021-07-27T02:23:21.607

0

You need to create the style in the destination workbook, something like this:

CellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle);

But this will create a new CellStyle for each cell, even if they are the same. So you may want to look into trying to detect when they are the same, and reuse them

edited Jul 27 '21 at 02:23

answered Jul 27 '21 at 00:05

GreyBeardedGeek

29,460
2
47
67

1

I've been experimenting in this PR - https://github.com/apache/poi/pull/240 - I use a CellCopyContext to track the new styles in the destination workbook to avoid creating too many. – PJ Fanning Jul 27 '21 at 00:34

Preserving worksheet formatting during cell-by-cell copy across workbooks using Apache POI

1 Answers1