6

Using the following code, cell value changes made to my Excel Spreadsheet are not saved:

OPCPackage pkg = OPCPackage.open(inputFile);
XSSFWorkbook wb = new XSSFWorkbook(pkg);

ModifyWorkbook();

pkg.close();

The following is a workaround I wrote, but I don't understand why it's necessary.

OPCPackage pkg = OPCPackage.open(inputFile);
XSSFWorkbook wb = new XSSFWorkbook(pkg);

ModifyWorkbook();

File tmp = File.createTempFile("tempSpreadsheet", ".xlsx");
FileOutputStream newExcelFile = new FileOutputStream(tmp);
wb.write(newExcelFile);
newExcelFile.close();
tmp.deleteOnExit();
pkg.close();

The javadocs and guides on the subject indicate that the .close() method should save and close. Checks on the values of the cells modified, prior to closing, indicate that the changes ARE made, but the pkg.close() method alone is not sufficient to write those changes to the file when closed.

DanielReid
  • 63
  • 1
  • 4

3 Answers3

2

A call to OPCPackage.close() will close the underlying OOXML file structure. What it won't do is cause XSSF (or any of the X??F formats) to write their changes out. So, you'll write out an un-changed file....

If you're doing low level modifications, you can open the OPCPackage, make changes, then call close to write them out. If you're doing high level stuff, you open the OPCPackage, work with the UserModel code to make changes, then ask the usermodel to write out its changes. It's this last step that's important.

Your current code is somewhat like:

File f = new File("test.txt");
Sting s = readFileCompletely(f);
s = s.replaceAll("foo", "bar");
f.close();
// Why hasn't the file contents changed?

Reading the file in and modifying the high level objects isn't enough, you also need to tell the high level objects to write out the changes. (The high level objects cache things in memory for performance reasons)

Gagravarr
  • 47,320
  • 10
  • 111
  • 156
1

Calling close on an (XSSF)Workbook will call OPCP.close which javadoc states:

Calling myWorkbook.close() should write the changes to file it was open from.

However, the current version (3.15) seems to have problems doing so. The reason itself is unknown to me, but I discovered that calling myWorkbook.write() will commit not only the changes to the output stream, but also to your current instance after calling close.

A reasonable workaround could therefore be:

try (OutputStream bos = ByteStreams.nullOutputStream()){
    workbook.write(bos);
    workbook.close();
} catch (IOException e) {
    log.error("Could not write Output File", e);
}

Here done with Guavas ByteStreams. Feel free to use other null output stream implementations.

Community
  • 1
  • 1
Dennis Rippinger
  • 408
  • 3
  • 8
0

In latest version of POI we dont need to do it manually if we are creating object of org.apache.poi.xssf.usermodel.XSSFWorkbook by default constructor. Read its default constructor method

public XSSFWorkbook() {
        super(newPackage());
        onWorkbookCreate();
    }
protected static OPCPackage newPackage() {
        try {
            OPCPackage pkg = OPCPackage.create(new ByteArrayOutputStream());
            // Main part
            PackagePartName corePartName = PackagingURIHelper.createPartName(XSSFRelation.WORKBOOK.getDefaultFileName());
            // Create main part relationship
            pkg.addRelationship(corePartName, TargetMode.INTERNAL, PackageRelationshipTypes.CORE_DOCUMENT);
            // Create main document part
            pkg.createPart(corePartName, XSSFRelation.WORKBOOK.getContentType());

            pkg.getPackageProperties().setCreatorProperty(DOCUMENT_CREATOR);

            return pkg;
        } catch (Exception e){
            throw new POIXMLException(e);
        }
    }
Jigyasu
  • 1