0

Is it possible to use same workbook to read and write into an Excel file (.xlsx) ?

I try to use the same workbook to improve performance.

Indeed, opening, closing, opening, closing,... is not very efficient.

So I keep the workbook in a "global variable".

this.FileInputStream = null
this.Workbook = null

public void before() {
     this.FileInputStream = new FileInputStream (new File('File.xlsx'))
     this.Workbook = new XSSFWorkbook(this.FileInputStream)
}

public void read() {
     XSSFSheet sheet = this.Workbook.getSheet(sheetName) 
     ...
}

public void write() {
     XSSFSheet sheet = this.Workbook.getSheet(sheetName) 
     ...
     FileOutputStream outFile = new FileOutputStream(new File('File.xlsx'))
     this.Workbook.write(outFile)
     outFile.close()
}    

public void after() {
     this.Workbook = null
     this.FileInputStream.close()
     this.FileInputStream = null 
}

Error

org.apache.xmlbeans.impl.values.XmlValueDisconnectedException

on line

this.Workbook.write(outFile)

Can you help me to improve this ?

Thank you in advance,

Regards,

Royce
  • 1,557
  • 5
  • 19
  • 44
  • Possible duplicate of [org.apache.xmlbeans.impl.values.XmlValueDisconnectedException when write workbook twice](https://stackoverflow.com/questions/18261152/org-apache-xmlbeans-impl-values-xmlvaluedisconnectedexception-when-write-workboo) – Joakim Danielson Aug 26 '19 at 07:29
  • I already saw this post but no change... – Royce Aug 26 '19 at 07:30
  • I've found that there used to be a bug related to writing more than once: https://bz.apache.org/bugzilla/show_bug.cgi?id=49940 - which version of POI are you using? Maybe all you need to do is used the latest/newer version? – Amongalen Aug 26 '19 at 07:33
  • I use apache POI 3.09, I don't have the choice of the version. – Royce Aug 26 '19 at 07:36
  • 2
    The bug was most likely fixed in version 3.10-FINAL (2014-02-08). If you can't get the newer version then your best bet probably would be not to write multiple times to the same workbook. Either do all the changes at once after opening it and write once or reopen the file after each write. – Amongalen Aug 26 '19 at 07:59

1 Answers1

2

Moving my answer from the comments:

I've found that there used to be a bug related to writing more than once: bz.apache.org/bugzilla/show_bug.cgi?id=49940. It was probably fixed in the version 3.10-FINAL (2014-02-08).

If you are using the older version and can't get the newer one then your best bet probably would be not to write multiple times to the same workbook. Either do all the changes at once after opening it and write once or reopen the file after each write.

Amongalen
  • 3,101
  • 14
  • 20