1

I have posed a question for copying Excel worksheet contents to a different workbook using Apache POI, while preserving formatting. I have received a good suggestion, which I have gone ahead and implemented (see below). The idea is to copy from the source worksheet to the destination worksheet, row by row.

Initially I got 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?

I have tried to fix it but I am now getting the following exception:

java.lang.IllegalArgumentException: Can only clone from one XSSFCellStyle to
another, not between HSSFCellStyle and XSSFCellStyle

This exception is a little weird because I am not using HSSFCellStyle.

Here is my source code:

import java.io.File;
import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExternalXSSFSheetCopier {
    
    // 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);
    
    // Instantiate CellCopyPolicy object and set all policies to true
    CellCopyPolicy copyPolicy = new CellCopyPolicy();
    copyPolicy.setCondenseRows(true);
    copyPolicy.setCopyCellFormula(true);
    copyPolicy.setCopyCellStyle(true);
    copyPolicy.setCopyCellValue(true);
    copyPolicy.setCopyHyperlink(true);
    copyPolicy.setCopyMergedRegions(true);
    copyPolicy.setCopyRowHeight(true);
    copyPolicy.setMergeHyperlink(true);
    
    // Iterate over the source sheet, row by row, and copy into the destination sheet
    int destRowNum = 0;
    for (Row srcRow: srcSheet) {
        XSSFRow srcXSSFRow = (XSSFRow) srcRow;
        
        XSSFRow destXSSFRow = destSheet.createRow(destRowNum++);
        
        // Introduced the following block of code, as suggested by 
        //     https://stackoverflow.com/questions/10773961/apache-poi-apply-one-style-to-different-workbooks
        // to get past 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?
        XSSFCellStyle srcStyle = srcXSSFRow.getRowStyle();
        XSSFCellStyle destStyle = new XSSFCellStyle(new StylesTable());
        destStyle.cloneStyleFrom(srcStyle);
        destXSSFRow.setRowStyle(destStyle);
        // With this block of code we now get the following exception:
        //    java.lang.IllegalArgumentException: Can only clone from one XSSFCellStyle to
        //    another, not between HSSFCellStyle and XSSFCellStyle
        
        destXSSFRow.copyRowFrom(srcXSSFRow, copyPolicy);
    }
    
    // Final cleanup
    srcWorkbook.close();
    
    FileOutputStream fos = new FileOutputStream(new File(destFilename));
    destWorkbook.write(fos);
    destWorkbook.close();
    fos.close();        
}
Xin
  • 31
  • 4
  • 1
    `java.lang.IllegalArgumentException: Can only clone from one XSSFCellStyle to another, not between HSSFCellStyle and XSSFCellStyle` is thrown by XSSFCellStyle cloneStyleFrom - can you set a breakpoint in there to see why this error is happening? – PJ Fanning Jul 23 '21 at 23:16
  • 1
    `XSSFWorkbook destWorkbook = new XSSFWorkbook(new File(srcFilename));` in your code above - should be destFilename – PJ Fanning Jul 23 '21 at 23:18
  • @PJFanning good catch but still the same exceptions are getting thrown. – Xin Jul 23 '21 at 23:51
  • 1
    The message of the exception is confusing. But that exception wil also be thrown if `srcStyle` is `null`. And `srcStyle` is `null` if `srcXSSFRow` does not have a row style at all. So make sure `srcStyle` is not `null`. But `destXSSFRow.copyRowFrom(srcXSSFRow, copyPolicy);` will never work if `destXSSFRow` and `srcXSSFRow` coming from different workbooks. They can be from different sheets in same workbook but not from different workbooks. – Axel Richter Jul 24 '21 at 08:31
  • @AxelRichter do you have any suggestion for copying worksheets across workbooks, while preserving formatting? – Xin Jul 24 '21 at 14:10
  • 1
    No, in my opinion this is impossible. In file storage worksheets are related to their workbooks in so many ways. No chance to separate a worksheet from its workbook preserving all file storage structures. `Excel`'s GUI does this by using special binary objects in RAM which are created while opening the workbooks. So structures in RAM and structures in file storage are totally separated things. `Apache poi` only maps file storage structures (XML or binary records) into objects. Maybe `Aspose.Cells` will be closer to `Excel` in this case, but I have no experience using `Aspose.Cells`. – Axel Richter Jul 24 '21 at 14:45

0 Answers0