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