Reading from Excel using POI API

In the previous post, we have learnt how to create and write to Excel Files using Apache POI APIs. We have used XSSF API to write data which is used for xlsx formats.In this post, we will see how to read the data from Excel file.

Let’s understand this step by step.

We will read the same excel sheet data, which we have created in the previous post.

Step#1 Create File object and pass the file path as an argument. This file object will be passed to FileInputStream object as given below.

FileInputStream fileIS = new FileInputStream(new File("C:\\Users\\Shekar Sharma\\Documents\\Sample1.xlsx"));

Step#2 Pass this FileInputStream object to the XSSFWorkbook  objects to get the instance of Excel workbook. Need to access the desired sheet by using method ‘getSheet’.

		//Get sheet access
		workbook = new XSSFWorkbook(fileIS);
		sheet = workbook.getSheet("Data");

Step#3 Count the total rows and columns present in the excel sheet.

		//Row count
		int rowsCount = sheet.getPhysicalNumberOfRows();
		//Column count
		int ColCount = sheet.getRow(0).getPhysicalNumberOfCells();

Step#4 Get the access of Row and Column by using the below statements.

//Get each row 
row = sheet.getRow(intValue);
//get Column
cell = row.getCell(intValue);

Step#5 The data in the Excel sheet can be in different form like String, Numeric,boolean etc. We need to verify which kind of data it is and on that basis we can print the data.

		//Read numeric value
		if(Cell.CELL_TYPE_NUMERIC==cell.getCellType()){
			System.out.println(cell.getNumericCellValue());
		//read String value
		}else if(Cell.CELL_TYPE_STRING==cell.getCellType()){
			System.out.println(cell.getStringCellValue());
		//Read boolean value
		}else if(Cell.CELL_TYPE_BOOLEAN==cell.getCellType()){
			System.out.println(cell.getCellType());
	        }

Step#6 Close the File.

		//Close file
		fileIS.close();

Full code to Read the data from Excel Files using POI API:

Here is the full code you can use. You just need to replace the File path ,rest it will take care.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadingExcelExample {
	private static XSSFWorkbook workbook;
	private static XSSFSheet sheet;
	private static XSSFCell cell;
	private static XSSFRow row;
	
	public static void main(String[] args) throws Exception {
		FileInputStream fileIS = new FileInputStream(new File("C:\\Users\\Shekar Sharma\\Documents\\Sample1.xlsx"));
		
		//Get sheet access
		workbook = new XSSFWorkbook(fileIS);
		sheet = workbook.getSheet("Data");
				
		//Row count
		int rowsCount = sheet.getPhysicalNumberOfRows();
		//Column count
		int ColCount = sheet.getRow(0).getPhysicalNumberOfCells();
		
		for(int i=0;i<rowsCount;i++){
			  //Get each row 
			  row = sheet.getRow(i);
			for(int j=0;j<ColCount;j++){
				//get Column
				cell = row.getCell(j);
				//Read numeric value
				if(Cell.CELL_TYPE_NUMERIC==cell.getCellType()){
					System.out.println(cell.getNumericCellValue());
					//read String value
				}else if(Cell.CELL_TYPE_STRING==cell.getCellType()){
					System.out.println(cell.getStringCellValue());
					//Read boolean value
				}else if(Cell.CELL_TYPE_BOOLEAN==cell.getCellType()){
					System.out.println(cell.getCellType());
			    }
		}
		//Close file
		fileIS.close();	
     }

	}
}