How to Write Excel Files Using Apache POI In Selenium WebDriver
Let’s see how to Write excel files using Apache POI in Selenium WebDriver:
Assuming that you have already downloaded and configured Apache POI jars in your project. If not, follow the below steps:
Step 1– Download Apache POI jar file
Download link of Apache POI Jars.
Step 2– Add download jar files
Select Project and Right click on the Project – Go to ‘Build path’ – Go to ‘Configure build path’ – Click on ‘lib’ section – Add external jar
Complete Guide: Handling Excel Files Using Apache POI
Precondition:
- Create an xlsx file and save it at particular location. Enter some data to read using Selenium. Close the created excel file before executing the script. (I have placed an excel file ‘Test.xlsx’ in my D Driver and my sheet name is TestData.)
- 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.
Must Read: How To Install Apache ANT
Below mentioned script shows how to write excel files in Selenium using Apache POI.
package softwareTestingMaterial; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WriteExcel { public static void main (String [] args) throws IOException{ //create an object of Workbook and pass the FileInputStream object into it to create a pipeline between the sheet and eclipse. FileInputStream fis = new FileInputStream("D:\\Test.xlsx"); XSSFWorkbook workbook = new XSSFWorkbook(fis); //call the getSheet() method of Workbook and pass the Sheet Name here. //In this case I have given the sheet name as “TestData” //or if you use the method getSheetAt(), you can pass sheet number starting from 0. Index starts with 0. XSSFSheet sheet = workbook.getSheet("TestData"); //XSSFSheet sheet = workbook.getSheetAt(0); //Now create a row number and a cell where we want to enter a value. //Here im about to write my test data in the cell B2. It reads Column B as 1 and Row 2 as 1. Column and Row values start from 0. //The below line of code will search for row number 2 and column number 2 (i.e., B) and will create a space. //The createCell() method is present inside Row class. Row row = sheet.createRow(1); Cell cell = row.createCell(1); //Now we need to find out the type of the value we want to enter. //If it is a string, we need to set the cell type as string //if it is numeric, we need to set the cell type as number cell.setCellType(cell.CELL_TYPE_STRING); cell.setCellValue("SoftwareTestingMaterial.com"); FileOutputStream fos = new FileOutputStream("D:\\Test.xlsx"); workbook.write(fos); fos.close(); System.out.println("END OF WRITING DATA IN EXCEL"); } }
Console Output:
END OF WRITING DATA IN EXCEL
Also Read: How to Read Excel Files Using Apache POI In Selenium WebDriver
If you are not regular reader of my blog then I highly recommend you to signup for the free email newsletter using the below link.
Well explained . Keep it up
Thanks Tanaya.
Hi i learned from your post.it’s very helpful .
Note: Could u pls post new reg appium tutorial.
Thanks
Mahesh
Sure Mahesh. We have a plan. We will do it ASAP.
Hey, I am getting null pointer exception while writing to excel. I tried saving file as text / number. Nothing helps.
Hi Mithun, are you still facing the same issue