samkool143 samkool143 - 4 months ago 20
Java Question

Not able to write output in the excel file using poi in selenium code

I am writing a code to fetch data from an

excel sheet
and run
selenium
tests on it and write the output in a different excel file. But I am not able to view the output in the output file. I am getting error as excel found unreadable content in .xlsx message after java code.

package new_excel_package;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.eclipse.debug.core.model.MemoryByte;
import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.firefox.FirefoxDriver;

public class PoiReadExcelFile {
public static void main(String[] args) {
try {
WebDriver driver = new FirefoxDriver();
FileInputStream fileInputStream = new FileInputStream("D://new.xlsx");
XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
XSSFSheet worksheet = workbook.getSheet("check");


FileOutputStream fileOut = new FileOutputStream("D://test.xlsx",true);
XSSFWorkbook workbook_out = new XSSFWorkbook();
XSSFSheet worksheet_out = workbook_out.createSheet("Worksheet");

MemoryByte ms = new MemoryByte();

for(int i = 0; i < worksheet.getLastRowNum()+1;i++)
{
XSSFRow row = worksheet.getRow(i);
//System.out.println(row.toString());
int r = worksheet_out.getLastRowNum();
XSSFRow row1 = worksheet_out.createRow(r+1);

XSSFCell cell_user = row.getCell(0);

String user_names = cell_user.getStringCellValue();
CharSequence[] user_name = {cell_user.getStringCellValue()};
System.out.println("fetched username");
XSSFCell cell_mail = row.getCell(1);

String e_mails = cell_mail.getStringCellValue();
CharSequence[] e_mail = {cell_mail.getStringCellValue()};
System.out.println("fetched email");

driver.get("file:///D:/SANDEEP/html%20sample.html");
driver.findElement(By.name("Name")).sendKeys(user_name);
driver.findElement(By.name("Email")).sendKeys(e_mail);
driver.findElement(By.name("Submit")).click();

//String status = "done";
System.out.println("authenticated for" + user_names);

/*XSSFCell cell_user_out = row1.createCell(0);
cell_user_out.setCellValue(user_names.toString());
XSSFCell cell_mail_out = row1.createCell(1);
cell_user_out.setCellValue(e_mails.toString());
XSSFCell cell_stat_out = row1.createCell(2);
cell_user_out.setCellValue("done");*/

row1.createCell(0,i).setCellValue(user_names.toString());
row1.createCell(1,i).setCellValue(e_mails.toString());
row1.createCell(2,i).setCellValue("done");
System.out.println("user updated");

/*workbook_out.write(fileOut);
System.out.println("elements updated2");*/
}
//workbook.Save("D://test.xlsx",FileFormatType.Excel2007Xlsx);

workbook.write(fileOut);
fileOut.flush();
fileOut.close();
driver.close();
System.out.println("elements updated");
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}

Answer

I am also reading the inputs from an excel document and writing the output in a separate excel(.xls) document. Unlike you, I have used HSSFWorkbook which only allows my output to be written in .xls but that should not impact the implementation.

The notable difference I can see is you are writing the workbook before closing the file as shown below: workbook.write(fileOut); fileOut.flush(); fileOut.close();

This is how I implemented it:

        HSSFWorkbook workbook = new HSSFWorkbook();
        String fileName = "excelDoc\\" +(new SimpleDateFormat("dd-MM-yy--hh-mm-ss").format(Calendar.getInstance().getTime()))+ ".xls"; //relative location of file + Time stamp based file name (.xls)
        System.out.println(fileName);       
        FileOutputStream  file1 = new FileOutputStream (new File(fileName));    
        HSSFSheet spreadSheet = workbook.createSheet("Result Document");
        HSSFRow row = spreadSheet.createRow((short) 0);
        HSSFCell cell;
        //Creating rows and filling them with data 
        cell = row.createCell(0);
        cell.setCellValue(new HSSFRichTextString("Test No"));
        cell = row.createCell(1);
        cell.setCellValue(new HSSFRichTextString("Test Result"));
        //Please see below and compare
        file1.close();  //Closing the file       
        FileOutputStream outFile =new FileOutputStream(new File(fileName));  //Creating new file
        workbook.write(outFile);   //printing the data in the new file
        outFile.close();           //closing the new file
        System.out.println("The Result are now printed in the excel sheet");