Here in this article we learn about how to write data in excel file in selenium using JAVA. As we discussed in last article about how to read from excel file , so must go through this first about how to read from excel file.Writing data in excel file is also very similar to read data from excel file. Read and writing data from excel in selenium is mainly used in automation frameworks and it is the key feature of the frameworks.
Steps to write in excel file in selenium
1) Download the Apache POI library and configure it with eclipse IDE – complete tutorial.
2) First create the file class object.
3) Create the File inputstream class object and pass the file as parameter.
4) create the workbook object and initialize it to null.
5) Check for the .xlsx or xls extension.
6) Get the sheet name.
7) create the cell and set the value.
8) create the output stream class object and pass the file as parameter.
9) call the workbook.write function to write the file.
Note : This error ” Exception in thread “main” java.lang.NullPointerException ” is thrown while writing or reading in excel file when there is no row or cell is initialized and we still performing read and write operations in excel.
So it is suggested that always use create cell or create row method while writing in the new cell or new row.
Excel File before writing data
Excel File after writing data
Java code for writing in excel file
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.xssf.usermodel.XSSFWorkbook;
public class Write_Excel_Padhle {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
//get the excel file path by right click on file
// properties and get file path
String excelfilePath = System.getProperty("user.dir");
String ExcelfileName = "Read_Write_Excel_Padhle.xlsx";
String sheetName = "Padhle_Write_Excel";
WriteExcel(excelfilePath,ExcelfileName,sheetName);
}
public static void WriteExcel(String excelfilePath
,String ExcelfileName,String sheetName)
throws IOException{
//Create an object of File class to open excel file
File file = new File(excelfilePath+"\\"+ExcelfileName);
//Create an object of FileInputStream class to read excel file
FileInputStream inputStream = new FileInputStream(file);
// Create the workbook class object and initialize
Workbook PadhleWorkbook =null;
//get the file extension by splitting file name using substring function
String fileExtension = ExcelfileName.substring(ExcelfileName.indexOf("."));
//Check condition if the file is XLSX file or XLS
if(fileExtension.equals(".xlsx")){
//If it is xlsx file then create object of XSSFWorkbook class
PadhleWorkbook = new XSSFWorkbook(inputStream);
}
//if the file is xls file
else if(fileExtension.equals(".xls")){
//If it is xls file then create object of HSSFWorkbook class
PadhleWorkbook = new HSSFWorkbook(inputStream);
}
//Read sheet inside the workbook by its name
Sheet padhleSheet = PadhleWorkbook.getSheet(sheetName);
//Find number of rows in excel file
int numberofrow = padhleSheet.getLastRowNum()-padhleSheet.getFirstRowNum();
//Create a loop reading all rows
for (int i = 1; i <= numberofrow; i++) {
// create the Row class object
Row row = padhleSheet.getRow(i);
// create the cell class object
// note : always use create cell else
//you will get null pointer exception error
// write the value "paas" in cell 4 -> last cell num
Cell cell = row.createCell(row.getLastCellNum());
cell.setCellValue("fail");
// write the value "fail" in cell 3
cell = row.createCell(3);
cell.setCellValue("pass");
}
// close the input steam
inputStream.close();
// create the file outstream object to write on the file
FileOutputStream outputStream = new FileOutputStream(file);
// pass the outputstream object to write function
PadhleWorkbook.write(outputStream);
// close the output stream
outputStream.close();
}
}
Hope now you are clear with how to write the data in excel file and its functions . Now you can implemented this logic in automation keyword frameworks to read and write data from excel. If you have any issue while writing the excel ,leave the comment below.