使用java使用Apache poi从Excel中获取数据

Fetch the data from Excel using Apache poi using java

提问by Thyagu

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.

我需要通过传递测试用例 ID 从 excel 中获取测试数据。如果我将测试用例 ID 传递为“TC005” - 我需要获取所有列值，例如实验室名称、实验室地址、城市、州、密码、收集中心名称、抄送地址、城市、州和密码。

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

提前致谢

采纳答案by Sankumarsingh

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 RowObjectfor the same.

Thyagu：我提出了一个解决方案......首先你应该创建一个方法来找到具有所需第一个单元格值的行（在你的情况下我认为需要 TestCaseID）并返回RowObject相同的 a 。

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 RowObjectyou 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.

一旦你有正确的RowObject你可以从中提取任何非空单元格值或者可以做任何你想做的事情，就像在这种情况下我刚刚打印的一样。

    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.

如果有可能在任何行的最后一个单元格值中获得空单元格，则需要使用MissingCellPolicy.