CODEWITHSUNDEEP
javaapache-poi
Jaffar Lone
Jaffar Lone

Reputation: 73

Unable to write to Excel file, getting OpenXML4JRuntimeException

I am attempting to read test data from excel and update the same sheet with the test result. Problem is that on the second iteration Eclipse IDE generates an exception and the status is not updated in excel either. Also the file appears to be damaged. Here is the code:

File src = new File("C:\\Users\\Sajid\\Desktop\\SeleniumData.xlsx");
FileOutputStream fos = new FileOutputStream(src, true);
FileInputStream fis = new FileInputStream(src);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(0);

for (int i = 1; i <= sheet.getLastRowNum(); i++)
{
    // Import data for Email.
    cell = sheet.getRow(i).getCell(0);
    cell.setCellType(Cell.CELL_TYPE_STRING);
    new WebDriverWait(driver, 50).until(ExpectedConditions.elementToBeClickable(By.xpath("//*[@id=\"email\"]")));
    driver.findElement(By.xpath("//*[@id=\"email\"]")).sendKeys(cell.getStringCellValue());

    String message = "Pass";
    sheet.getRow(i).createCell(15).setCellValue(message);
    workbook.write(fos);
}

The Exception:

org.apache.poi.openxml4j.exceptions.OpenXML4JRuntimeException: Fail to save: an error occurs while saving the package: The part /docProps/app.xml failed to be saved in the stream with marshaller org.apache.poi.openxml4j.opc.internal.marshallers.DefaultMarshaller@2e62e227

Updated Code

File src = new File("C:\\Users\\Sajid\\Desktop\\SeleniumData.xlsx");
FileOutputStream fos = new FileOutputStream(src, true);
FileInputStream fis = new FileInputStream(src);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheetAt(0);

for (int i = 1; i <= sheet.getLastRowNum(); i++)
{
    // Import data for Email.
    cell = sheet.getRow(i).getCell(0);
    cell.setCellType(Cell.CELL_TYPE_STRING);

    String message = "Pass";
    sheet.getRow(i).createCell(15).setCellValue(message);
    workbook.write(fos);

    driver.findElement(By.cssSelector("b.hidden-xs")).click();
    Thread.sleep(500);
    driver.findElement(By.cssSelector("a.sign-out")).click();
    Thread.sleep(1000);
}

fos.close();
fis.close();

However i am getting the null pointer exception for the cell.setCellType(Cell.CELL_TYPE_STRING); line

java.lang.NullPointerException

Upvotes: 0

Views: 10794

Answers (2)

Joop Eggen
Joop Eggen

Reputation: 109613

One cannot at the same time open a file for input and output if it is not a random access file.

Also then the output should not append to an existing file, but rather replace the file.

Also write the workbook once.

FileInputStream fis = new FileInputStream(src);
workbook = new XSSFWorkbook(fis);
fis.close();

FileOutputStream fos = new FileOutputStream(src); // Without append true.
...
workbook.write(fos);
fos.close();

Upvotes: 5

Hamza Torjmen
Hamza Torjmen

Reputation: 270

did you take a lool at this post OpenXML4JRuntimeException: Fail to save ?

there are two solutions explained that might help you :

1- You are opening the file multiple times so before opening a file, check if it is closed.

2- The version of your jar file is getting override so add the dependency in your pom.xml : 

<dependency>
<groupId>org.apache.xmlgraphics</groupId>
<artifactId>batik-dom</artifactId>
<version>1.8</version>
</dependency>

Upvotes: 3

Related Questions