POIXMLException when updating excel file with apache-poi

When I try overwrite existing excel file, I get this error message:

Exception in thread "main" org.apache.poi.ooxml.POIXMLException: OOXML file structure broken/invalid - no core document found!
at org.apache.poi.ooxml.POIXMLDocumentPart.getPartFromOPCPackage(POIXMLDocumentPart.java:783)
at org.apache.poi.ooxml.POIXMLDocumentPart.<init>(POIXMLDocumentPart.java:175)
at org.apache.poi.ooxml.POIXMLDocumentPart.<init>(POIXMLDocumentPart.java:165)
at org.apache.poi.ooxml.POIXMLDocument.<init>(POIXMLDocument.java:61)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:282)
at Test.main(Test.java:16)

By the way, there is no problem if I try write a new excel file.So Its working correctly but I can’t update existing file. What am I doing wrong? There is my code:

public static void main(String[] args) throws InvalidFormatException, IOException {
    File file = new File("C:/Users/yavuz/IdeaProjects/inspection/src/main/java/inspection.xlsx");
    OPCPackage pkg = OPCPackage.open(file);
    FileOutputStream outputStream = new FileOutputStream(file);
    XSSFWorkbook wb = new XSSFWorkbook(pkg);

    int finding = 445;

    DataFormatter formatter = new DataFormatter();
    for(Sheet sheet : wb) {
        for(Row row : sheet){
            if(row.getCell(0)!=null && !formatter.formatCellValue(row.getCell(0)).equals("")){
                Cell cell = row.getCell(0);
                String text = formatter.formatCellValue(cell);
                if('0'<=text.charAt(0) && text.charAt(0)<='9') {
                    int id = Integer.parseInt(text);
                    if (id == finding) {
                        System.out.println(sheet.getSheetName());
                        System.out.println(sheet.getRow(row.getRowNum()).getCell(1));
                        Cell cellCurrent = row.getCell(2);
                        if (cellCurrent == null){
                            cellCurrent = row.createCell(2);
                        }
                        cellCurrent.setCellValue("X");

                        wb.write(outputStream);
                        outputStream.close();
                    }
                }
            }
        }
    }
}

Answer

Multiple issues in your code.

If you are creating an OPCPackage or a XSSFWorkbook from a File, you cannot have a FileOutputStream to the same file as long as the OPCPackage or XSSFWorkbook is not closed. This is because OPCPackage or XSSFWorkbook which are opened from a File get its data from that file directly. So the memory footprint is lower because not all data is in random access memory. But the file is locked.

If the need is reading from a File and writing into that same File, then using FileInputStream for reading and FileOutputStream for writing is necessary.

And you cannot write out the workbook after each changing. After Workbook.write the workbook is not more ready for getting data out of it. So the workbook needs to be written out once after all changes are made.

And the whole creating the OPCPackage is not necessary. The better way is creating the workbook directly from the FileInputStream.

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));

Even better is using WorkbookFactory.create as this is able creating HSSF or XSSF Workbook depenent on the given file.

Workbook wb = WorkbookFactory.create(new FileInputStream(file));

Following code got tested and works using apache poi 4.1.2.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class ExcelFromOPC {

  public static void main(String[] args) throws Exception {
    File file = new File("./inspection.xlsx");

    //OPCPackage pkg = OPCPackage.open(file);
    OPCPackage pkg = OPCPackage.open(new FileInputStream(file));
    XSSFWorkbook wb = new XSSFWorkbook(pkg);
    
    //XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));
    //Workbook wb = WorkbookFactory.create(new FileInputStream(file));

    //wb -> sheets -> rows -> cols
    int finding = 445;

    DataFormatter formatter = new DataFormatter();
    boolean write = false;
    for(Sheet sheet : wb) {
      for(Row row : sheet) {
        if(row.getCell(0)!=null && !formatter.formatCellValue(row.getCell(0)).equals("")) {
          Cell cell = row.getCell(0);
          String text = formatter.formatCellValue(cell);
          if('0'<=text.charAt(0) && text.charAt(0)<='9') {
            int id = Integer.parseInt(text);
            if (id == finding) {
              System.out.println(sheet.getSheetName());
              System.out.println(sheet.getRow(row.getRowNum()).getCell(1));
              Cell cellCurrent = row.getCell(2);
              if (cellCurrent == null) {
                cellCurrent = row.createCell(2);
              }
              cellCurrent.setCellValue("X");
              write = true;
            }
          }
        }
      }
    }

    if (write) {
        System.out.println("writing");
        FileOutputStream outputStream = new FileOutputStream(file);
        wb.write(outputStream);
        outputStream.close();
        wb.close();
    }
  }

}