How To Write in An Excel File Using Apache POI in Selenium WebDriver

In the previous post we learnt how to read an excel file in Selenium WebDriver. The writing in the excel sheet is as easy as reading from an excel sheet. For writing into an excel sheet using selenium web driver you need to have Apache POI which we had for reading from an excel sheet. The POI library of Apache provides the facility to web driver for read and write from an excel sheet. Follow these simple steps :

1) Download and configure Apache POI jar files in your eclipse.

2) Add the following lines of code to your script :

FileInputStream fis=new FileInputStream(“D:\\Selenium\\Data.xlsx”);
Workbook wb=WorkbookFactory.create(fis);
Sheet sh=wb.getSheet(“Input”); 

Here, in the first line we have to give the path of the file where you have created it in your local drive folder. Here it is created in D driver under selenium folder and so I have given that path. In the excel sheet you need to enter some inputs and rename the sheet name. When you enter the inputs make sure that the type is of text. Select your data of the sheet and right click. Go to option “Format Cells” and under Number Tab option, select Text and click on OK. By default it will be general, you need to make it as Number. If you don’t make it as text, there is a chance of NullPointerException error.

Then we create an object of Workbook and pass the FileInputStream object into it to create a pipeline between the sheet and eclipse.

3) Then we can call the getSheet() method of Workbook and pass the Sheet Name here. In this case I have given the sheet name as “Input”.

4) Now we will get the row number and we will create a cell where we want to enter a value. In case of reading, we din’t create cell but here we have to because we have to write something.

Row row=sh.getRow(8);
Cell cell=row.createCell(1);

The above line of code will search for row number 8 and cell number 1 and will create a space. The createCell() method is present inside Row class.

5) Now we need to find out the type of the value we want to enter. If it is a string, then we need to set the cell type as string and if it is numeric, then we need to set the cell type as numberic.

cell.setCellType(cell.CELL_TYPE_STRING);

This line set the cell type as String. Now the next step is to enter the value or you can say set the value. To do it write the below line of code :

cell.setCellValue(“Selenium”);

But here we have only defined the value and the place where we want to display this value. To display the value we need to add extra line of code which is :

FileOutputStream fos=new FileOutputStream(“D:\\Selenium\\Data.xlsx”);
wb.write(fos);

You need to give the same path in FileOutputStream object where we have kept our xlsx file. Once you done till here you can display a message saying that the file has been written. Then run the program.

6) Before running the program make sure that you have not opened the same excel file, otherwise, an error will come saying that the file is being used by another process. So close the file and run your script. And then open and check your file.

package home;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class writingInExcel {

public static void main(String[] args) throws InvalidFormatException, IOException{
FileInputStream fis=new FileInputStream(“D:\\Selenium\\Data.xlsx”);
Workbook wb=WorkbookFactory.create(fis);
Sheet sh=wb.getSheet(“Input”);
Row row=sh.getRow(8);
Cell cell=row.createCell(1);
cell.setCellType(cell.CELL_TYPE_STRING);
cell.setCellValue(“Selenium”);
FileOutputStream fos=new FileOutputStream(“D:\\Selenium\\Data.xlsx”);
wb.write(fos);
fos.close();
System.out.println(“Excel File Written.”);
}}

When you run this script the message will be displayed saying that the file has been written.

a

That’s all in writing to an excel sheet using Apache POI…!!!

Add Comment