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

Question

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();
        }
    }
}

Fahim Hossain · Accepted Answer · 2014-08-27 03:24:08Z

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");

Vignesh Paramasivam · Accepted Answer · 2014-08-27 06:36:02Z

0

You're missing out this,

XSSFWorkbook workbook_out = new XSSFWorkbook(fileOut);

answered Aug 27, 2014 at 6:36

Vignesh Paramasivam

2,5505 gold badges28 silver badges60 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related