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

Spread the love

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.

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;

}

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;

}

}

Don't miss out!
Subscribe To Newsletter

Receive top technical news, lesson ideas, travel tips and more!

Invalid email address
Give it a try. You can unsubscribe at any time.


Spread the love
  •  
    1
    Share
  • 1
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Comment

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

Scroll to Top