Exception when writing to the xlsx document several times using apache poi 3.7

Question

I am getting the following exception while trying to write an .xlsx file using Apache POI: org.apache.xmlbeans.impl.values.XmlValueDisconnectedException

It seems the problem is using the method write () second time. When working with a HSSFWorkbook of this problem does not arise.

Here's the Code:

public class SomeClass{

XSSFWorkbook workbook;

public SomeClass() throws IOException{
    File excelFile = new File("workbook.xlsx");

    InputStream inp = new FileInputStream(excelFile);
    workbook = new XSSFWorkbook(inp);
    inp.close();
}

void method(int i) throws InvalidFormatException, IOException {

    XSSFSheet sheet = workbook.getSheetAt(0);
    XSSFRow row = sheet.getRow(i);
    if (row == null) {
        row = sheet.createRow(i);
    }
    XSSFCell cell = row.getCell(i);
    if (cell == null)
        cell = row.createCell(i);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    cell.setCellValue("a test");

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
    workbook.write(fileOut);
    fileOut.close();

}

public static void main(String[] args) throws Exception {
    SomeClass sc = new SomeClass();

    sc.method(1);
    sc.method(2);
}
}

Answer 1

I've had the same problem today. I've noticed many people asking the same question on many different forums, but I haven't seen an answer anywhere. So, here is what I came up with. It is far from ideal (I can think of at least two scenarios where this might be a bad idea), and may not suite every need, but it works!

After every save operation inside the class which the workbook object is a property of, I reload the workbook from the file I just saved it to.

Using your code example above, I would modify the method like so:

void method(int i) throws InvalidFormatException, IOException {
    ...

    // Write the output to a file
    FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
    workbook.write(fileOut);
    fileOut.close();

    // Reload the workbook, workaround for bug 49940
    // https://issues.apache.org/bugzilla/show_bug.cgi?id=49940
    workbook = new XSSFWorkbook(new FileInputStream("workbook.xlsx"));
}

I tested this in my code, and it resolved the issue nicely. Just make sure that you read it back in from the same file you saved it to, and not an earlier or different version.

Answer 2

This is most likely a bug.

https://issues.apache.org/bugzilla/show_bug.cgi?id=49940

I suggest you subscribe to that ticket to get notified about current improvements / alternatives.

If I find a workaround I will let you know.

Answer 3

The solution I've found for this, and I've been looking for a while, is to make sure you don't open your Workbook with the File which you use to open the FileOutputStream to save the Workbook. Instead, use a FileInputStream to open the Workbook.

Something like this will work flawlessly

        File inputFile = new File("Your-Path");
        this.inputStream = new FileInputStream(inputFile);
        this.opc = OPCPackage.open(this.inputStream);
        this.workbook = WorkbookFactory.create(opc);

...

        this.outputStream = new FileOutputStream(inputFile);
        this.workbook.write(this,outputStream);

Don't forget to close every opened stream and the OPCPackage.

Answer 4

This occurs only when we try to write more than once to the same file that too for a .xlsx file. I came accross the same issue and got it resolved by..

1. Previously i was writing twice
2. Now removed the first write call
3. Passed the same workbook instance to the method and set values to the new cell
4. finally did some more changes to the workbook by writing few more columns and cells
5. Then wrote using the file output stream.

It was working

Answer 5

I too faced the same issue when using apache poi 3.10. But after adding latest apache poi jar files, it worked for me. Please try after updating the jars to latest.

Answer 6

I had the same problem too. Later, I tried another method and it solved.
In this case, we can move the code:

FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
workbook.write(fileOut);
fileOut.close();

out of the method(int i), and then in the main method, we can use:

 sc.method(1); 
 sc.method(2); 
 FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
 workbook.write(fileOut);
 fileOut.close();

Then, the workbook.write is only used for once. Also the data could be modified several times.

Answer 7

This seems to be indeed a bug in XSSFSheet.createRow(int index). As long as the bug is not fixed, using this class as a workaround should do the trick :

import java.util.Iterator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

public class PoiHacks
{
    // Fix of XSSFSheet.createRow(int index)
    public static Row createRow(Sheet sheet, int index) {
        Row row = sheet.getRow(index);
        if(row==null) return sheet.createRow(index);

        Iterator it = row.iterator();
        while(it.hasNext()) {
            it.next();
            it.remove();
        }
        return row;
    }
}

Use :

PoiHacks.createRow(sheet, 0);