Getting error when editing Excel file using Apache POI

Question

I am trying to edit an excel file which contains data using Apache POI. I have written the below code:

    FileInputStream fileInputStream = new FileInputStream(new File("file.xlsx"));
    Workbook workbook = WorkbookFactory.create(fileInputStream);
    Sheet sheet = workbook.getSheet("sheet");

    Row row = sheet.getRow(1);
    Cell cell = (row.getCell(1) == null) ? row.createCell(1) : row.getCell(1);
    cell.setCellType(CellType.STRING);
    cell.setCellValue("something here");.
    fileInputStream.close();

    try(FileOutputStream fileOut = new FileOutputStream("file.xlsx")) {
        workbook.write(fileOut);
        workbook.close();
    }

When I run the code, I get the following error when I try to open the Excel file: "We found a problem with some content in 'file.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes."

If I click on yes,the Excel does get updated with the values that I have specified; however, I do not want this error to appear. How can I solve this?

Answer 1

I tried to create a similar situation, but could not get it to work with try with resources. However, the following code shows a working read-write sequence using the same workbook. Have a look at the comments.

public static void main(String[] args) {
    /*
     * Setup:
     * An existing xlsx file with a first sheet containing 6 columns and 1 row.
     * The row has 6 filled cells with the values
     * cell 1 (index 0): There
     * cell 2 (index 1): is
     * cell 3 (index 2): a
     * cell 4 (index 3): house
     * cell 5 (index 4): in
     * cell 6 (index 5): New Orleans
     * 
     * Task:
     * Write the words "they", "call", "it", "the", "rising", "sun"
     * in the cells below.
     */

    // define the (correct) path to the workbook
    String pathToFile = "U:\\temp\\TestFiles\\Test-Workbook.xlsx";
    // create a Path object
    Path filePath = Paths.get(pathToFile);
    // declare a workbook
    XSSFWorkbook workbook;

    try {

        /*
         * READING from the .xlsx file:
         */
        FileInputStream in = new FileInputStream(filePath.toFile());
        workbook = XSSFWorkbookFactory.createWorkbook(in);
        XSSFSheet sheet = workbook.getSheetAt(0);

        // read all the cells of the first row and print their content
        for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
            XSSFRow row = sheet.getRow(i);
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                XSSFCell cell = row.getCell(j);
                System.out.println(cell.getStringCellValue());
            }
        }

        /*
         * WRITING to the .xlsx file already read
         */

        // create some meaningful words to be added to some cells in the workbook
        List<String> wordsToBeWritten
                = Arrays.asList("they", "call", "it", "the", "rising", "sun");

        FileOutputStream out = new FileOutputStream(filePath.toAbsolutePath().toString());
        sheet = workbook.getSheetAt(0);
        XSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows());
        // create new cells and write the words into them
        for (int i = 0; i < wordsToBeWritten.size(); i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(wordsToBeWritten.get(i));
        }
        // close the FileInputStream
        in.close();
        // write the workbook using the FileOutputStream
        workbook.write(out);
        // force the FileOutputStream to write everything until it is empty
        out.flush();
        // close the FileOutputStream
        out.close();
        // close the workbook.
        workbook.close();
    } catch (FileNotFoundException e) {
        System.err.println("The file \"" + filePath.toAbsolutePath().toString() 
                + "\" could not be found.");
        e.printStackTrace();
    } catch (IOException e) {
        System.err.println("Error while reading the file \"" 
                + filePath.toAbsolutePath().toString() + "\"");
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        System.out.println("The file \"" + filePath.toAbsolutePath().toString() 
                + "\" has an invalid format(ting)");
        e.printStackTrace();
    } catch (EmptyFileException e) {
        System.err.println("The supplied file \"" 
                + filePath.toAbsolutePath().toString() + "\" is empty.");
        e.printStackTrace();
    }
}

EDIT

The only dependency I am using for this is version 4.0.0 of poi-ooxml:

<dependencies>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.0.0</version>
    </dependency>
</dependencies>