Fetch the data from Excel using Apache poi using j

Question

I need to fetch the Test data from the excel by passing the test case id. If i pass the Test case id as "TC005" - i need to get all the column values like Lab name, Lab Address, City, State , Pincode, Collection center Name, CC Address, City, State & Pincode.

Can anyone tel me how to do it ??

I have worked on it, but I can get only one field.

package com.utils;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;
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;
import org.apache.poi.ss.usermodel.Row;


public class ReadExcel {
    static int colTestcase;
     static int rowTestcase;
     static int rownumber ;
     static int colnumber;
     static InputStream input = null;
     static HSSFWorkbook workbook;
     static FileInputStream file ;
     static HSSFSheet sheet;
     static Properties prop = new Properties();


     public static boolean openFile(String fileName) {
            try {
                file = new FileInputStream(fileName);
                workbook = new HSSFWorkbook(file);
                return true;
            }
            catch(Exception e) {
                e.printStackTrace();
                return false;
            }
        }
        public static void main(String args[])
    {
        long startTime = System.currentTimeMillis();
        System.out.println("startTime: "+startTime);
        int rowvalue=readTestCase("TC03");
        int columnvalue=readHeader("Service Title");
        String value=getValuefromExcel(rowvalue,columnvalue);
        System.out.println("Row Value: "+rowvalue);
        System.out.println("Col Value: "+columnvalue);
        System.out.println("Text: "+value);
        long endTime = System.currentTimeMillis();
        System.out.println("endTime"+endTime);
        System.out.println("Took "+(endTime - startTime) + " milliseconds");
    }


    public static String getInputData(String Testcase,String header)
    {
        int rowvalue=readTestCase(Testcase);
        int columnvalue=readHeader(header);
        String value = getValuefromExcel(rowvalue, columnvalue);
        return value;
    }

    public static String getValuefromExcel(int row, int col)
    {
        String value =null;
        String field="Wrongvalue";
        if(!(row==-1||col==-1))
        {
            try {
                System.out.println("Entered");
                input = new FileInputStream("src\\com\\config\\config.properties");
                // load a properties file
                prop.load(input);
                openFile(prop.getProperty("excelTestData_FilePath"));
                //Get first sheet from the workbook
                sheet = workbook.getSheet(prop.getProperty("excelTestData_Sheet"));
                HSSFCell cell = null;
                //Update the value of cell
                cell = sheet.getRow(row).getCell(col);
                value = cell.toString();
                file.close();
            }
            catch(Exception e){
                e.printStackTrace();
            }
            System.out.println("value Done");
            return value;
        }
        else {
            System.out.println("Given data was wrong");
        }
    return field;
    }

    public static int readTestCase(String Testcase)
    {
    try {
        FileInputStream file = new FileInputStream(new File("C:\\Thyagu\\Workspace\\PMModule\\test-input\\PM_ModuleTestData.xls"));

        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheet("Data");

        int rowNum = sheet.getLastRowNum()+1;
        int colNum = sheet.getRow(0).getLastCellNum();
        for (int i=1; i<rowNum; i++){
            HSSFRow row = sheet.getRow(i);
            int j=0;
                        HSSFCell cell = row.getCell(j);
                        String value = cell.toString();
                                if(value.equalsIgnoreCase(Testcase))
                                {
                                    rowTestcase=row.getRowNum();
                                    colTestcase=cell.getCellNum();
                                    return rowTestcase;
                                }
                            }
        file.close();
    }
    catch(Exception e){
        e.printStackTrace();
    }
    System.out.println("rownumber Done: " +rowTestcase);
    return -1;
    }

    public static int readHeader(String header)
    {
    try {
        FileInputStream file = new FileInputStream(new File("C:\\Thyagu\\Workspace\\PMModule\\test-input\\PM_ModuleTestData.xls"));

        //Get the workbook instance for XLS file 
        HSSFWorkbook workbook = new HSSFWorkbook(file);

        //Get first sheet from the workbook
        HSSFSheet sheet = workbook.getSheet("Data");

        int rowNum = sheet.getLastRowNum()+1;
        int colNum = sheet.getRow(0).getLastCellNum();
        int i=0;
            HSSFRow row = sheet.getRow(i);
            rownumber =row.getRowNum();
               for (int j=0; j<colNum; j++){
                        HSSFCell cell = row.getCell(j);
                        String value = cell.toString();
                                if(value.equalsIgnoreCase(header))
                                {
                                    rownumber=row.getRowNum();
                                    colnumber=cell.getCellNum();
                                    return colnumber;
                                }
                            }
        file.close(); 
    }
    catch(Exception e){
        e.printStackTrace();
    }
    System.out.println("colnumber Done");
    return -1;
    }
}

Thanks in Advance

Answer 1

Thyagu: I am proposing one solution... first you should create a method that find the row having desired first cell value (in your case desired TestCaseID I suppose) and return a RowObject for the same.

public static HSSFRow findRow(String stringToFind){
            //`hswb` is HSSFWorkbook
    HSSFSheet sheet = hswb.getSheetAt(0);
    Iterator <Row> rowItr = sheet.iterator();
    while(rowItr.hasNext()){
        HSSFRow row = (HSSFRow) rowItr.next();
        if(row.getCell(0).toString().equalsIgnoreCase(stringToFind)){
            return row;
        }
    }
  // If no such row found need to handle NullPointerException for that
    return null;
}

Once you have the correct RowObject you can extract any non empty cell value from that or can do whatever you want, like in this case I have just printed the same.

    public static void getAllColumnFromRow(HSSFRow RowObject){
    Iterator<Cell> itr = RowObject.iterator();
    HSSFRow headerRow = RowObject.getSheet().getRow(0);
    String cellValue = "";
    String information = "";
    int headerCellCount = 0;
    //to avoid first column
    itr.next();

    while(itr.hasNext()){
        Cell cell = itr.next();
        Cell headerValue = headerRow.getCell(headerCellCount++);
        switch(cell.getCellType()) {
        case HSSFCell.CELL_TYPE_BOOLEAN:
            cellValue = cell.getBooleanCellValue() +"";
            information = information + " " +headerValue+" - "+cellValue+ "; ";
            break;
        case HSSFCell.CELL_TYPE_NUMERIC:
            cellValue = cell.getNumericCellValue() + "";
            information = information + " " +headerValue+" - "+cellValue+ "; ";
            break;
        case HSSFCell.CELL_TYPE_STRING:
            cellValue = cell.getStringCellValue();
            information = information + " " +headerValue+" - "+cellValue+ "; ";
            break;
        case HSSFCell.CELL_TYPE_BLANK:
            break;
        }
    }
    System.out.println(information);
}

In case if any possibility to get a null cell within the last cell values of any row, you need to use MissingCellPolicy.