Searching...
Monday, 21 October 2013

How to read Excel file using java and Apache poi API ?

11:13

Apache POI

The Apache POI Project classes are very useful for generating/accessing data from MS Excel files containing text (numeric/non-numeric), lists, rows and images. The library is especially useful in combination with Java technology-based classes. The Apache POI API enables a Java developer to access Microsoft Excel data files programmatically. See more about poi

Maven Dependency

Below maven dependency required to get poi jars

 <dependency>
		<groupId>org.apache.poi</groupId>
		<artifactId>poi</artifactId>
		<version>3.7</version>
    </dependency>

Creating the HSSFWorkbook Object


HSSFWorkbook is the layout of the entire data to be accessed in the form of various objects. All kinds of text elements can be accessed via HSSFWorkbook. We can create an instance of the HSSFWorkbook object as follows.

HSSFWorkbook workbook =  new HSSFWorkbook (new FileInputStream(String filename)); 
//filename  is the name of the MS Excel file which will be accessed/read/ written.
// Constructor of HSSFWorkbook takes name of the file. 

Accessing data from HSSFWorkbook instance


Once the HSSFWorkbook instance is created as mentioned in the first step, then we are ready to access data from given excel file as follows.

HSSFSheet sheet = workbook.getSheetAt (int sheetindex); 
//sheetindex is the index of the sheet of Excel file (starts from 0). 
HSSFSheet sheet = workbook.getSheet(String sheetname); 
//sheetname is the name of the sheet of Excel file 
HSSFRow row = sheet.getRow(int rowno);
//rowno is the row number. 
Java.util.Iterator iterator = sheet.rowIterator(); 
HSSFRow row = (HSSFRow) rowIterator.next();  
HSSFSheet.rowIterator() 
//returns java.util.Iterator, which fetches all rows of the sheet to HSSFRow sequentially.  
HSSFCell cell = row.getCell(short cellno); 
HSSFRow.getCell() // returns the cell of a particular row to HSSFCell. 
String stringValue = cell.getStringCellValue();
double numericValue  = cell.getNumericCellValue(); 
//Method HSSFCell.getStringCellValue() helps us to read string data and method
//HSSFCell.getNumericCellValue helps us to read numeric data from the given cell.

Example


In this example we will see how to read excel cell using poi. Create a maven project with above poi dependency in pom.xml  file and create an excel file with below data (This file should be placed in project folder as Student.xls)


image


package com.pretech;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class ReadExcelExample {
	public static void main(String[] args) throws Exception {
		readExcel("Student.xls");
	}
	public static void readExcel(String filename) throws Exception {
		try {
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(filename));
			HSSFSheet sheet = wb.getSheetAt((int) 0);
			if (sheet != null) {
				Iterator<?> rowIterator = sheet.rowIterator();
				while (rowIterator.hasNext()) {
					HSSFRow row = (HSSFRow) rowIterator.next();
					HSSFCell cellStudentID = row.getCell(0);
					HSSFCell cellStudentName = row.getCell(1);
					try {
						String studentId = cellStudentID.getStringCellValue();
						String studentName = cellStudentName
								.getStringCellValue();
						System.out
								.println(studentId + "        " + studentName);
					} catch (NullPointerException e) {
						continue;
					}
				}
			} else {
				System.out.println("Sheet not found");
			}
		} catch (FileNotFoundException fne) {
			System.out.println("File not found");
		}
	}
}

Output



Student_name        Student_Address
Santhosh        Bangalore
Raju        Chennai
Raghav        Hydrabad
Dinesh        Pune
Suraj        Mumbai

0 comments: