Read Excel file from Java

We use Excel file in our day to day work, for creating daily reports or maintaining expense sheet at home. Excel can also be used as a simple database, we can read entries from an excel file and can use them further in our program.

We are using excel file to store Test execution reports, such as validation points, whether the test case is passed or not, true or false, and later using that cell information to decide whether the Test Case was passed or not. Excel automation plays an important role when using in Automation testing. We are using TestNG framework for our Test Case execution and stores the result in either Excel file(using java) or we use emailable reports from TestNG.

We need to add external jar in our project to read and write excel file, POI jar is used to do this. You can download the jars from below link.

Download POI Jars

Follow below steps in eclipse to add the POI jars to your project

• After downloading the jars unzip the file
• Right click on your project >> Build Path >> Configure Build Path
• Click Java Build Path >> Libraries >> Add External JARs

This will open below window. Make sure you add all the jars visible below and also from "ooxml-lib" folder.

Below is the code I simplified for you. You can read the cell data using below code. Also comments section describes the code.

--------------------------------------------------------------------------------

import java.io.File;

import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 ReadExcel

{
     public void readExcel(String filePath, String fileName, String sheetName) throws   IOException
{
    //Create an object of File class to open xlsx file
    File file = new File(filePath+"\\"+fileName);

    //create an object FileInputStream to read excel file.

    FileInputStream inputStream = new FileInputStream(file);
    Workbook demoWorkbook = null;

    //find the file extension by splitting file name in substring
    //and getting only extension name
   String fileExtensionName = fileName.substring(fileName.indexOf("."));

   //check condition if the file is xlsx file
   if(fileExtensionName.equals(".xlsx"))
   {
        //if it is xlsx file then create object of XSSFWorkbook class
       demoWorkbook = new XSSFWorkbook(inputStream);
   }

else if(fileExtensionName.equals(".xls"))

{
       //if it is xls file then create object of HSSFWorkbook class
      demoWorkbook = new HSSFWorkbook(inputStream);
}
      //read sheet inside the workbook by its name
      Sheet demoSheet = demoWorkbook.getSheet(sheetName);

      //find number of rows in excel file

      int rowCount = demoSheet.getLastRowNum()-demoSheet.getFirstRowNum();

      //create a loop over all the rows of excel file to read it
      for(int i=0; i<rowCount+1;i++)
     {
           Row row = demoSheet.getRow(i);

          //create a loop to print cell values in a row

          for(int j = 0; j<row.getLastCellNum();j++)
         {
              //print excel data in console
              System.out.print(row.getCell(j).getStringCellValue()+"|| ");
         }
            System.out.println();
      }
}
public static void main(String args[]) throws IOException
{
      //create object of ReadExcel class
      ReadExcel objExcelFile = new ReadExcel();

      String filePath = System.getProperty("user.dir")+\\src\\;

      objExcelFile.readExcel(filePath, "ExportExcel.xlsx", "ExcelSheet");
}
}