Reading list Switch to dark mode

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

    Updated 28 January 2020

    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:

    Start your headless eCommerce
    now.
    Find out More
    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.

    . . .

    Leave a Comment

    Your email address will not be published. Required fields are marked*


    17 comments

  • Manohar
  • Disha
    • vrinda sharma (Moderator)
      • Disha
        • vrinda sharma (Moderator)
          • Disha
          • vrinda sharma (Moderator)
  • Rose
    • vrinda sharma (Moderator)
  • Denis
    • vrinda sharma (Moderator)
  • Shoumita
    • vrinda sharma (Moderator)
  • Srikanth A R
    • vrinda sharma (Moderator)
  • Aaakash Raaj
    • vrinda sharma (Moderator)
  • Back to Top

    Message Sent!

    If you have more details or questions, you can reply to the received confirmation email.

    Back to Home