Selenium Webdriver: Read and Write Data from Excel Sheet using Apache POI Libraries

In the real word automation script, the test data shouldn’t be hardcoded. Some external resources (excel sheet, properties file, xml file, json file etc.) should be used to read or write data.

In Selenium, Java provides some different classes or interfaces to perform file manipulation. Apache POI libraries are used to perform such operations. Some of the interfaces are given below to read or write data from the external resources:

1. POIFS (Poor Obfuscation Implementation File System)
2. HSSF(Horrible Spreadsheet Format)
3. XSSF(XML Spreadsheet Format)
4. HPSF(Horrible Property Set Format)
5. HWPF(Horrible Word Processor Format)
6. XWPF(XML Word Processor Format)
7. HSLF(Horrible Slide Layout Format)
8. HGDF(Horrible Diagram Format)
9. HDBF(Horrible PuBlisher Format)

To create or maintain Excel Workbooks , Apache POI provides ” Workbook” as super-interface of all classes. It belongs to org.apache.poi.ss.usermodel package. It uses WorkbookFactory class for creating the appropriate kind of Workbook (i.e. HSSFWorkbook or XSSFWorkbook). The two classes which implements “Workbook” interface are given below:

1. HSSFWorkbook- This class methods are used to read/write data to Microsoft Excel file in .xls format.
2. XSSFWorkbook- This class methods are used to read write data to Microsoft Excel and OpenOffice xml files in .xls or .xlsx format.

Note: For Apache POI Libraries installation, please refer to the blog– https://webkul.com/blog/apache-poi-installation-to-project-library/

Read Data From Excel Sheet:

Below Code is written to read the data from the excel sheet:

package testng;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.testng.annotations.Test;

public class excelsheet 
{
  @Test
  public void excel() throws EncryptedDocumentException, InvalidFormatException, FileNotFoundException, IOException { 
                //get the excel sheet file location               
                String filepath=".\\excelsheet\\testdata.xlsx";
	              Workbook wb= new WorkbookFactory().create(new FileInputStream(new 
                              File(filepath)));
                //get the sheet which needs read operation
		            Sheet sh = wb.getSheet("sheet1");
                //get the total row count in the excel sheet	
		            int rowcount = sh.getLastRowNum();
		              for (int i = 0; i <= rowcount; i++) 
                  {
                    // get the total cell count in the excel sheet
			               int cellcount = sh.getRow(i).getLastCellNum();
			                  for (int j = 0; j < cellcount; j++) 
                        {                         
                            //get cell value at the given position [i][j]
		                        String value = sh.getRow(i).getCell(j).getStringCellValue();
                            //print the cell value
				                    System.out.println(value);
				
			                   }			
		                }	
		          }
}

“Workbook” interface throws some exceptions which needs to be handled i.e. EncryptedDocumentException, InvalidFormatException, FileNotFoundException & IOException.

Note: In Excel Sheet, there shouldn’t be any voided cell in-between the data fields, else the code will throw an error message at the time of execution & the data after the voided cell will be skipped.

 Workbook wb= new WorkbookFactory().create(new FileInputStream(new File(filepath)));

The above code can also be written as below for better understanding:

File file= new File(".\\excelsheet\\testdata.xlsx");
	  FileInputStream fis= new FileInputStream(file);
	  Workbook wb= WorkbookFactory.create(fis);

Write Data to Excel Sheet:

Below code is written to write the code to the excel sheet:

package testng;
import java.io.File;
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.CellType;
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;
import org.testng.annotations.Test;

public class writeexcel {
	@Test
	public void write() throws IOException
	{
        //define the file path where excel sheet will be generated
	String filepath= (".\\excelsheet\\write.xlsx");
	File file= new File(filepath);
	FileOutputStream fos=new FileOutputStream(file);
        //create the blank workbook
	Workbook wb= new XSSFWorkbook();
        //create the blank excel sheet
	Sheet sh= wb.createSheet("Webkul");
	//define the position in the excel sheet where write operation needs to perform
        Row rw= sh.createRow(4);
	Cell cl=rw.createCell(1);
        //define input type
	cl.setCellType(CellType.STRING);
        //define the data value
	cl.setCellValue("Webkul Software");
        //write the workbook
	wb.write(fos);
	}
}

In case you have any queries then feel free to ask in the comment section below.