Reading Excel file using JAVA and Apache POI API

 

Reading Excel file using JAVA and Apache POI API

Hello friends! In my previous post we have seen how to export data to an excel file using Apache POI API in JAVA. Here, we will learn how to read data present in an excel sheet. After reading the data that can be persisted to database. Here also I am using the same POI API from Apache (this is one of the widely used API available).

Resources required:
Apache POI JARs

Files:
1) ExcelReader.java
Here I have taken a standalone application to show you how the code works. But the same technique can be used in web applications also. We have two methods here:

a. readExcel(String filename) : This method takes a string argument, i.e. the fully qualified name of the file(fine name along with the full path) ex – “D:/test/student.xlsx”.

In this method we are creating Workbook object like shown below. Workbook objects represents an excel sheet in java.

Workbook workbook = new XSSFWorkbook(fileInputStream);
WorkBook workbook = new HSSFWorkbook(fileInputStream);

XSSFWorkbook is used for the excel 2007 and above having the .xlsx extension and HSSFWorkbook is used for the excel 2003 and lower having the extension .xls. So, we create different Workbook object based on the file simply by checking the file extension. Here I have used XSSFWorkbook as I am using excel version 2007.

Workbook is the interface and XSSFWorkbook, HSSFWorkbook are its implementation classes. In POI various overloaded versions of constructors are available for the above two class. Here I have used the constructor which takes a String argument, i.e. the fully qualified name of the excel file. After getting the Workbook object we can get Sheet object by calling getSheetAt(intsheetNumber). Here I have only one sheet that’s why I have hardcoded ‘0’ as the sheet number starts from zero. If multiple sheets are there then we can call getNumberOfSheets() on Workbook object to get the total no of sheets.

b. getCellValue(Cell cell): This method takes the Cell as an argument. A Cell object represents a particular cell in the excel file in a specific row. In this method we are retrieving the cell values by using getxxxCellValue(). For different types of cell different versions of this methods are available. After retrieving these values we are returning those to the calling method, where I am printing those values to the console.

Folder structure:
Reading Excel file using JAVA and Apache POI API

Please check the below code for further understanding.

package com.excelreaderproject;

import java.io.FileNotFoundException;
import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader {
	
	public void readExcel(String fileName){
		try {
			Workbook workbook = newXSSFWorkbook(fileName);//XSSFWorkbook is for Excel 2007 and above (.xlsx)
			//WorkBook workbook = new HSSFWorkbook(fileName);//HSSFWorkbook is for Excel 2003 (.xls)
			
			//accessing the particular sheet
			//here the parameter indicates the sheet number. 0 means first sheet, 1 means second and so on.
			Sheet sheet = workbook.getSheetAt(0);
			
			/* sheet can also be accessed using the sheet name like shown below 
			Sheet sheet = workbook.getSheet("first sheet");*/
			
			//getting the rows
			
			//following code will work with empty cells
			Row row = null;
			Cell cell = null;
			//int noOfRows = getPhysicalNumberOfRows();// returns the total no of rows in the selected sheet
			int noOfRows = sheet.getLastRowNum();
			for(int i = 0; i<= noOfRows; i++) {
				row = sheet.getRow(i);
				//int noOfCells = row.getPhysicalNumberOfCells();// returns the total no of cells in the selected row
				int noOfCells = row.getLastCellNum();//returns the no of the last cell in the row
				for(int j = 0; j <noOfCells; j++) {
					cell = row.getCell(j);
					
					if(cell != null){
						System.out.print(getCellValue(cell)+"\t");
					}else{
						Cell blanckCell = row.createCell(j);
						blanckCell.setCellValue("");
						System.out.print(getCellValue(blanckCell)+"\t");
					}
				}
				System.out.println();
			}
		} catch (FileNotFoundException e) {
			System.out.println("File is not available.");
			e.printStackTrace();
		} catch (IOException e){
			System.out.println("Problem reading file from directory.");
			e.printStackTrace();
		}
	}
	
	public Object getCellValue(Cell cell){
		Object cellValue = null;
		if(cell.getCellType() == Cell.CELL_TYPE_STRING){
			cellValue = cell.getStringCellValue();
		}elseif(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			cellValue = cell.getNumericCellValue();
		}elseif(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
			cellValue = cell.getBooleanCellValue();
		}elseif(cell.getCellType() == Cell.CELL_TYPE_BLANK){
			cellValue = "";
		}
		return cellValue;
	}
	
	public static void main(String[] args) {
		new ExcelReader().readExcel("D:/abc.xlsx");
	}
}

Excel File:
Excel File

Output:
Reading Excel FIle Output

COMMENTS ( 3 )

continuously i employed to read smaller articles or
reviews that at the same time clear their motive, and that is also happening with this
particular post that i am reading around this place.

Reply

Leave a comment

SUBSCRIBE TO NEWSLETTER

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Categories