How to read excel file In Java

Any type of testing requires test data in Test automation Let’s see how we can read Excel file in Java and use that in Selenium automation projects. The most common excel file types to store test data are .xlsx and .xls format.

Using APACHE POI, we can read excel file in java, and read .xlsx files in java. In this post let’s see how to do that.All the methods are written by utilizing the Apache POI library.

If you are not sure, “How to add APACHE POI library to your Selenium Automation Project?”, Please check out this guide on How to add Apache POI library in Selenium Automation Project.

Steps to read excel file in Java using Apache POI

In order to read excel file in Java make sure to remember and follow below steps. Also, if you are not aware, first familiarise yourself with 3 kind of keywords in Java. i.e. FileInputStream, XSSFWorkbook, Iterator, loops in order to read excel file in java.

Now let’s see how to implement these steps with the help of detail. code. Please remember that XSSFWorkbook, XSSFSheet, cell etc have many useful methods associated with them. This needs proper understanding and practice.

Example code to read data from excel

try
        {
            FileInputStream file = new FileInputStream(new File("writeTestDataFile.xlsx"));

            //Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(file);

            //Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);
            //Use below method to get sheet by name
            //	Sheet trackSheet = trackWorkbook.getSheet(sheetName);

            //Iterate through each rows one by one
            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext())
            {
                Row row = rowIterator.next();
                //For each row, iterate through all the columns
                Iterator<Cell> cellIterator = row.cellIterator();
                 
                while (cellIterator.hasNext())
                {
                    Cell cell = cellIterator.next();
                    //Check the cell type and format accordingly
                    switch (cell.getCellType())
                    {
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue() + "t");
                            break;
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue() + "t");
                            break;
                    }
                }
                System.out.println("");
            }
            file.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

Example code to read excel file with formula

try
    {
        FileInputStream file = new FileInputStream(new File("writeTestDataFormulaFile.xlsx"));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
         
        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();
             
            while (cellIterator.hasNext())
            {
                Cell cell = cellIterator.next();
                //Check the cell type after eveluating formulae
                //If it is formula cell, it will be evaluated otherwise no change will happen
                switch (evaluator.evaluateInCell(cell).getCellType())
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "tt");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "tt");
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        //Not again
                        break;
                }
            }
            System.out.println("");
        }
        file.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }

A ready to use method to Read Excel file in java using Apache POI

Below we have a method properly written to read excel file in java. You can just copy and paste it in your class.

/**
* This method reads data from an excel file named fileName located at filePath and with sheetname =sheetName
* @param filePath 	Location of the file
* @param fileName 	Name of the file
* @param sheetName Name of the sheet
* @throws IOException
*/
public void readExcel(String filePath,String fileName,String sheetName) throws IOException{

	File file =    new File(filePath+"\\"+fileName);
	FileInputStream inputStream = new FileInputStream(file);
	Workbook trackWorkbook = null;
	String fileExtensionName = fileName.substring(fileName.indexOf("."));

	if(fileExtensionName.equals(".xlsx")){
	       trackWorkbook = new XSSFWorkbook(inputStream);
	}

	Sheet trackSheet = trackWorkbook.getSheet(sheetName);
	int rowCount = trackSheet.getLastRowNum()-trackSheet.getFirstRowNum();

	for (int i = 0; i < rowCount+1; i++) {
		Row row = trackSheet.getRow(i);
		for (int j = 0; j < row.getLastCellNum(); j++) {
			System.out.print(row.getCell(j).getStringCellValue()+"|| ");

		}
		System.out.println();
	} 

}