How To Write A Function That Will Read Data From Excel Using POI Jar

Share and Enjoy !

Those coder who are working on data driven testing or working a project where they dont want a dedicated database but use Excel as data source, this post is for them.

In the college level if you are using java and excel , this post will also help you to get values from Excel.

Ads code goes here

This holds good for stratup project, concept project or just demo projects.

Well how to use poi? I will cover that in different post. But for now we will concentrate on Apache POI-the Java API for Microsoft Documents. It can be downloaded from here.http://poi.apache.org/download.html

Download it and attach this to your editor. This is a Jar file so once you add this jar you will be able to access the methods.

This is the deceleration portion

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

The command to get User defined / specified excel file..

InputStream myxls = new FileInputStream("E:\Eclipse\My Projects\FirstProject\bin\link.xls");

You can also check out here to know how to get user input here

Let us write the function that will read values from Excel.

//this function will take excel book as input ,open the first sheet and reads value
public static String getData(InputStream myxls) throws IOException
{
String abc="";
//taking a temp variable to calculate values
HSSFWorkbook workbook = new HSSFWorkbook(myxls);
//Createing object for workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Create object of sheet which is at position 0
Iterator<row> rowIterator = sheet.iterator();
// Iterate for each for
while(rowIterator.hasNext()) {
Row row = rowIterator.next();

//For each row, iterate through each columns
Iterator<cell> cellIterator = row.cellIterator();
//get data by cell by cell
while(cellIterator.hasNext()) {

Cell cell = cellIterator.next();
abc=abc "," + cell.getStringCellValue() ;

}
}
myxls.close();
//Close the excel
return abc;

}
READ  Class StringTokenizer in Java

How to read values returned by the above function.

String urllist = getData(myxls);
String arr[]=urllist.split(",");
for(int i=0;i<arr.length;i ++)
{
if(arr[i].equals(""))
{

}
else{
System.out.println(arr[i]) ;
}

}

The entire code looks like-

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;





public class ReadExcel {

FileInputStream fileIn = null;
FileOutputStream fileOut = null;
HSSFWorkbook wb;
HSSFSheet sheet;
static String ExcelName = "";

/**
* @param <HSSFWorkbook>
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
// TODO Auto-generated method stub


InputStream myxls = new FileInputStream("E:\Eclipse\My Projects\FirstProject\bin\link.xls");

String urllist = getData(myxls);
String arr[]=urllist.split(",");
for(int i=0;i<arr.length;i++ )
{
if(arr[i].equals(""))
{

}
else{
System.out.println(arr[i]) ;
}

}

}
public static String getData(InputStream myxls) throws IOException
{
String abc="";
//taking a temp variable to calculate values
HSSFWorkbook workbook = new HSSFWorkbook(myxls);
//Createing object for workbook
HSSFSheet sheet = workbook.getSheetAt(0);
//Create object of sheet which is at position 0
Iterator<Row> rowIterator = sheet.iterator();
// Iterate for each for
while(rowIterator.hasNext()) {
Row row = rowIterator.next();

//For each row, iterate through each columns
Iterator<Cell> cellIterator = row.cellIterator();
//get data by cell by cell
while(cellIterator.hasNext()) {

Cell cell = cellIterator.next();
abc=abc "," +cell.getStringCellValue() ;

}
}
myxls.close();
//Close the excel
return abc;

}

}

Share and Enjoy !

Leave a Comment

Your email address will not be published. Required fields are marked *