Ad Home

Theme images by kelvinjay. Powered by Blogger.

Travel

Header Ads

Java

Selenium

UFT

Framework

General QA Concept

BDD

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

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;
  
 }
 
}

Hope you like the post . If you like this please consider it for sharing also please comment below about your thought regarding the same.

«
Next
Newer Post
»
Previous
Older Post

No comments:

Leave a Reply