This tutorial describes to read data from Excel file in Java.
I’m using Apache POI to read the excel file. To download and install Apache POI, refer here.
The two mainly used classes for managing Excel Workbooks are:
- HSSFWorkbook- These class methods are used to read/write data to Microsoft Excel file in .xls format. It is compatible with MS-Office versions 97–2003.
- XSSFWorkbook- These class methods are used to read-write data to Microsoft Excel in .xls or .xlsx format. It is compatible with MS-Office versions 2007 or later.
How to read Excel (.xlsx) from specific cell value?
Step 1 – To locate the location of file.
File file = new File("C:\\Selenium_ReadTestData.xlsx");
Import File from package
import java.io.File;
Step 2 – Instantiate FileInputStream to read from the file specified.
FileInputStream fis = new FileInputStream(file);
Step 3 – Create object of XSSFWorkbook class
XSSFWorkbook wb = new XSSFWorkbook(fis);
Step 4 – Import XSSFWorkbook from package
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
Step 5 – To read excel sheet by sheet name
XSSFSheet sheet1 = wb.getSheet("Read_TestData");
Step 6 – Import XSSFSheetfrom package
import org.apache.poi.xssf.usermodel.XSSFSheet;
Step 7 – To access data from the XLSX file, use of the following methods:
getRow(int rownum)
getCell(int cellnum)
getStringCellValue()
getNumericCellValue()
Step 8 – To get the cell value from column A and Row 1
System.out.println(sheet1.getRow(0).getCell(0).getStringCellValue());
Here getRow(0) will look into the first row, and getCell(0) will look into the first column, i.e. A1.
To retrieve the String value we are making use of getStringCellValue in the end.
Below is the Excel which I’m using to read the data as mentioned below:-
We want to get the value of Row 1, Cell B
We want to get the value of Row 3, Cell A
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadSpecificData {
public static void main(String[] args) {
String path = "C:\\Selenium_ReadTestData.xlsx";
try {
//Create an object of FileInputStream class to read excel file
FileInputStream fis = new FileInputStream(path);
//Create object of XSSFWorkbook class
XSSFWorkbook wb = new XSSFWorkbook(fis);
//Read excel sheet by sheet name
XSSFSheet sheet1 = wb.getSheet("Read_TestData");
//Get data from specified cell
System.out.println(sheet1.getRow(1).getCell(1).getStringCellValue());
System.out.println(sheet1.getRow(3).getCell(0).getStringCellValue());
} catch (IOException e) {
e.printStackTrace();
}
}
}
Output
Tom
302AC
How to read the entire Excel sheet?
To read the complete data from Excel, you can iterate over each cell of the row, present in the sheet.
To get the last and first-row number, there are two methods in the sheet class:
- getLastRowNum()
- getFirstRowNum()
int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();
Once you get the row, you can iterate over the cells present in the row by using the total number of cells, that we can calculate using getLastCellNum() method:
int cellcount=sheet.getRow(1).getLastCellNum();
Below is the entire program to read complete excel.
import java.io.FileInputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelFile {
public static void main(String args[]) throws IOException {
String path = "C:\\Selenium_ReadTestData.xlsx";
//Create an object of FileInputStream class to read excel file
FileInputStream fis = new FileInputStream(path);
//Create object of XSSFWorkbook class
Workbook workbook = new XSSFWorkbook(fis);
//Read excel sheet by sheet name
Sheet sheet = workbook.getSheet("Read_TestData");
//Find number of rows in excel file
int rowCount=sheet.getLastRowNum()-sheet.getFirstRowNum();
System.out.println("row count:"+rowCount);
//iterate over all the row to print the data present in each cell.
for(int i=0;i<=rowCount;i++){
//get cell count in a row
int cellcount=sheet.getRow(i).getLastCellNum();
//iterate over each cell to print its value
for(int j=0;j<cellcount;j++){
System.out.print(sheet.getRow(i).getCell(j).getStringCellValue().toString() +"||");
}
System.out.println();
}
}
}
That’s it! We are done.
Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!
QA Automation Expert 