RE: Writing to Excel corrupts the Excel file

Hi,

I have a script that uses the data-driven framework using Apachi POI.  The ExcelUtils.java file that I use is identical to the file in your tutorials (http://www.toolsqa.com/selenium-webdriver/data-driven-testing-excel-poi/)

My script is able to read from the Excel file without any problems, but writing to Excel is another matter.

I have the following code in my script:


for (int j = 1; j < 10; j++) {
boolean multiples = driver.findElements(By.xpath("//table[@id='Table4']/tbody/tr/td/table/tbody/tr[3]/td/table/tbody/tr[3]/td")).size()>0;
if (multiples == false) {
ExcelUtils.setCellData("Successful", j, 6);

System.out.println(j);
}else {

ExcelUtils.setCellData("Multiple", j, 6);

System.out.println(j);

}
}

If I remove the loop and just run the script once, the script runs successfully and the result is sent to the spreadsheet.  However, when I try to iterate the script with the for loop, it runs successfully for the first iteration, but in the second iteration, at the setCellData, the Excel spreadsheet is corrupted and the script then fails.

While trying to debug the issue I would watch the Excel file size (in Windows Explorer).  The file size before the test was run was 75kb.  After the first iteration the file size would drop to 62kb, after the second iteration the file size would drop to 0kb

I have had the same problem with trying to write to different cells in one row.

Does anyone know why Selenium corrupts Excel files, and how I can avoid it while still writing results to Excel?

Thanks

Simon

SimonM Train Asked on September 14, 2014 in Selenium WebDriver.
Add Comment
3 Answers

Hi,

I am resurrecting this thread because it has happened again only with a different script.

To explain in more detail.

In my script I have the following line of code near the end of the script:

 ExcelUtils.setCellData("Done", i, 6);
 

It is calling this bit of code from ExcelUtils

 //This method is to write in the Excel cell, Row num and Col num are the parameters
 public static void setCellData(String Result,  int RowNum, int ColNum) throws Exception    {
 try{
 Row  = ExcelWSheet.getRow(RowNum);
 Cell = Row.getCell(ColNum, Row.RETURN_BLANK_AS_NULL);
 if (Cell == null) {
 Cell = Row.createCell(ColNum);
 Cell.setCellValue(Result);
 } else {
 Cell.setCellValue(Result);
 }
 // Constant variables Test Data path and Test Data file name
 FileOutputStream fileOut = new FileOutputStream(Constant.Path_TestData + Constant.File_TestData);
 ExcelWBook.write(fileOut);
 fileOut.flush();
 fileOut.close();
 }catch(Exception e){
 throw (e);
 }
 }
 

I have run the script about a dozen times without any issues.  Suddenly, with no changes to the script being made, it is corrupting the Excel file.  I have observed the following when I run the script:
The file that the script is referencing is made up of approx 800 rows of data.  The file size before I run the script is 34 KB.  When I run the script, I notice at the end of the first iteration (after the script has passed the ExcelUtils.setCellData) the file size of the Excel spreadsheet drops down to 26KB.  At the end of the second iteration, it drops to 0 KB and the file is no longer accessible.  The console provides the following error:

l

 Exception in thread "main" org.apache.xmlbeans.impl.values.XmlValueDisconnectedException
 at org.apache.xmlbeans.impl.values.XmlObjectBase.check_orphaned(XmlObjectBase.java:1213)
 at org.apache.xmlbeans.impl.values.XmlObjectBase.newCursor(XmlObjectBase.java:243)
 at org.apache.xmlbeans.impl.values.XmlComplexContentImpl.arraySetterHelper(XmlComplexContentImpl.java:1073)
 at org.openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTDefinedNamesImpl.setDefinedNameArray(Unknown Source)
 at org.apache.poi.xssf.usermodel.XSSFWorkbook.saveNamedRanges(XSSFWorkbook.java:1288)
 at org.apache.poi.xssf.usermodel.XSSFWorkbook.commit(XSSFWorkbook.java:1309)
 at org.apache.poi.POIXMLDocumentPart.onSave(POIXMLDocumentPart.java:322)
 at org.apache.poi.POIXMLDocument.write(POIXMLDocument.java:173)
 at utility.ExcelUtils.setCellData(ExcelUtils.java:63)
 at com.test.updateUserAcc.main(updateUserAcc.java:80)

The only way I can get the script to run successfully is to remove the ExcelUtils.setCellData(“Done”, i, 6); from the script.

Has anyone got any idea why this is happening?

Train Answered on December 11, 2014.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.