java - 尝试使用 apache POI 从 Excel 工作表检索数据

Question

我是 Java 编程新手，正在编写一种使用 apache POI 从 Excel 工作表中检索所有行的方法。我创建了一个名为“ExcelDataProvider”的单独类，我在其中调用方法从工作表中提取值。我编写的方法对于第一行运行良好，但它没有处理所有行。这是我编写的代码...

public static String getCellDataNew(String sheetName, String colName, int rowNum) {
    try {
        int col_Num = 0;
        ExcelWSheet = ExcelWBook.getSheet(sheetName);
        Row = ExcelWSheet.getRow(0);
        int rowcount = ExcelWSheet.getLastRowNum();
        for (int i = 0; i < Row.getLastCellNum(); i++) {
            if (Row.getCell(i).getStringCellValue().trim().equals(colName.trim()))
                col_Num = i;
        }

        for (rowNum = 2; rowNum <= rowcount; rowNum++) {
            Row = ExcelWSheet.getRow(rowNum - 1);
            Cell = Row.getCell(col_Num);
        }

        if (Cell.getCellType() == CellType.STRING)
            return Cell.getStringCellValue();
        else if (Cell.getCellType() == CellType.NUMERIC || Cell.getCellType() == CellType.FORMULA) {
            String cellValue = String.valueOf(Cell.getNumericCellValue());
            return cellValue;
        } else if (Cell.getCellType() == CellType.BLANK)
            return "";
        else
            return String.valueOf(Cell.getBooleanCellValue());
    } catch (Exception e) {
        e.printStackTrace();
        return "row " + rowNum + " or column  does not exist  in Excel";
    }
}

................................................................

这是 dataprovider 类的代码

package utility;

public class ExcelDataProvider {

    private String sDealer;
    private String sBranchCode;
    private String sBranchName;
    private String sAccountType;
    private String sProductSelection;
    private String sTaxResidence;
    private String sJointOwnershipOption;
    private String sJointOwnershipType;
    private String sJointSignatureOption;
    private String sJOSelectionLastName;
    private String sJOSelectionFirstName;

    public ExcelDataProvider() throws Exception {
        this.setsDealer(Excelutil.getCellDataNew("OpenAccount", "Dealer", 2));
        this.setsBranchCode(Excelutil.getCellDataNew("OpenAccount", "BranchCode", 2));
        this.setsBranchName(Excelutil.getCellDataNew("OpenAccount", "BranchName", 2));
        this.setsAccountType(Excelutil.getCellDataNew("OpenAccount", "AccountType", 2));
        this.setsProductSelection(Excelutil.getCellDataNew("OpenAccount", "ProductSelection", 2));
        this.setsTaxResidence(Excelutil.getCellDataNew("OpenAccount", "TaxResidence", 2));
        this.setsJointOwnershipOption(Excelutil.getCellDataNew("OpenAccount", "JointOwnership", 2));
        this.setsJointOwnershipType(Excelutil.getCellDataNew("OpenAccount", "JointOwnershipType", 2));

        this.setsJointSignatureOption(Excelutil.getCellDataNew("OpenAccount", "JointSignature", 2));
        this.setsJOSelectionLastName(Excelutil.getCellDataNew("OpenAccount", "JointOwnerSelectionLastName", 2));
        this.setsJOSelectionFirstName(Excelutil.getCellDataNew("OpenAccount", "JointOwnerSelectionFirstName", 2));
    }

    public String getsDealer() {
        return this.sDealer;
    }

    public String getsBranchCode() {
        return this.sBranchCode;
    }

    public String getsBranchName() {
        return this.sBranchName;
    }

    public String getsAccountType() {
        return this.sAccountType;
    }

    public String getsProductSelection() {
        return this.sProductSelection;
    }

    public String getsTaxResidence() {
        return sTaxResidence;
    }

    public String getsJointOwnershipOption() {
        return sJointOwnershipOption;
    }

    public String getsJointOwnershipType() {
        return sJointOwnershipType;
    }

    public String getsJointSignatureOption() {
        return sJointSignatureOption;
    }

    public String getsJOSelectionLastName() {
        return sJOSelectionLastName;
    }

    public String getsJOSelectionFirstName() {
        return sJOSelectionFirstName;
    }

    public void setsDealer(String sDealer) {
        this.sDealer = sDealer;
    }

    public void setsBranchCode(String sBranchCode) {
        this.sBranchCode = sBranchCode;
    }

    public void setsBranchName(String sBranchName) {
        this.sBranchName = sBranchName;
    }

    public void setsAccountType(String sAccountType) {
        this.sAccountType = sAccountType;
    }

    public void setsProductSelection(String sProductSelection) {
        this.sProductSelection = sProductSelection;
    }

    public void setsTaxResidence(String sTaxResidence) {
        this.sTaxResidence = sTaxResidence;
    }

    public void setsJointOwnershipOption(String sJointOwnershipOption) {
        this.sJointOwnershipOption = sJointOwnershipOption;
    }

    public void setsJointOwnershipType(String sJointOwnershipType) {
        this.sJointOwnershipType = sJointOwnershipType;
    }

    public void setsJointSignatureOption(String sJointSignatureOption) {
        this.sJointSignatureOption = sJointSignatureOption;
    }

    public void setsJOSelectionLastName(String sJOSelectionLastName) {
        this.sJOSelectionLastName = sJOSelectionLastName;
    }

    public void setsJOSelectionFirstName(String sJOSelectionFirstName) {
        this.sJOSelectionFirstName = sJOSelectionFirstName;
    }
}

这就是我的电子表格的样子..

Testcase|   ClientName| Dealer| BranchCode| BranchName| AccountType|ProductSelection|   TaxResidence|   JointOwnership |    JointOwnershipType |    JointSignature| JointOwnerSelectionLastName|    JointOwnerSelectionFirstName


1   |Test, GR|  NBIN Introducing|   HOUS|   NBIN Intro| Non Registered| Cash |  Ontario|    Yes|    Joint WROS| Yes|    Test|   ABC


2   |Test, GR|  NBIN Introducing|   HOUS|   NBIN Intro| Non Registered| Cash |  Ontario|    Yes|    Joint WROS| Yes|    Test|   ABC

Answer 1

我想我看到了这个问题。下面的 for 循环不应就此结束。它也应该覆盖下面的 if-else 梯子。

for (rowNum = 2; rowNum <= rowcount; rowNum++) {
        Row = ExcelWSheet.getRow(rowNum - 1);
        Cell = Row.getCell(col_Num);
    }

应该是:

for (rowNum = 2; rowNum <= rowcount; rowNum++) {
        Row = ExcelWSheet.getRow(rowNum - 1);
        Cell = Row.getCell(col_Num);

        if (Cell.getCellType() == CellType.STRING)
           return Cell.getStringCellValue();
        else if (Cell.getCellType() == CellType.NUMERIC || Cell.getCellType() == CellType.FORMULA) {
           String cellValue = String.valueOf(Cell.getNumericCellValue());
           return cellValue;
         } else if (Cell.getCellType() == CellType.BLANK)
           return "";
         else
           return String.valueOf(Cell.getBooleanCellValue());
    }