Error in writing data to existing Excel file after validating some values from the Excel file

Question

Table with testData with various customer details

I have to validated the value from the table in the first column with the input and if the value in the Excel equals the value given in the input. Then it should write order number in the column X of that same row.

The code I am using is as follows:

try
{
    FileInputStream fileInputStream = new FileInputStream(path);
    Workbook workbook = new XSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheet("Customers");
    Row firstRow = sheet.getRow(0);

    for (int i = 1; i <= sheet.getLastRowNum(); i++) {
        Row dataRow = sheet.getRow(i);
        String cellValue = stringValueOf(dataRow.getCell(0));

        if (cellValue.equalsIgnoreCase(TestId)) 
        {
            Cell cell = dataRow.getCell(CellReference.convertColStringToIndex("X"));
            cell.setCellValue(order);

            break;
        }
    }

    fileInputStream.close();

    FileOutputStream fileOutputStream = new FileOutputStream(path);
    workbook.write(fileOutputStream);

    workbook.close();
    fileOutputStream.close();
} 
catch (IOException e) 
{
     e.printStackTrace();
}

I expected it should write the value in the Excel for the given column.

But instead I get an "IOException" and the code goes into the catch block and the value is not written.

Answer 1

Your problem is that you're trying to write to the same file. The write method is documented thus:

if the Document was opened from a File rather than an InputStream, you must write out to a different file, overwriting via an OutputStream isn't possible.

So create a new file, write to it, close the output stream, and then delete the old file and rename the new file to the old file name.

Link to documentation