How to read data from Excel in Java

HOME

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!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s