How to read excel file In Java- 100% Working code

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 files in java, and read .xlsx files in java.

On this page we have a list of functions for reading the capability of Excel files using Java such as Read Excel sheets, Read data from Excel with formulas, Read using Apache POI etc.

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.

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);

            //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();
    }

Read data from Excel using Apache POI

/**
* 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();
	} 

}