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 Virender,

It is identical to the setdata method that is detailed in the Tutorials on this site:

 //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 since created a new script from scratch and it is working without corrupting the file so I am putting it down to just “one of those things”.

Thanks

Simon

Train Answered on September 16, 2014.
Add Comment

Your Answer

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