Trying to retrieve data from excel sheet using apache POI

1
votes

I am new to Java programming and I am writing a method to retrieve all the rows from excel worksheet using apache POI. I have created a separate class called ' ExcelDataProvider' where I am calling the method to pull values from the worksheet. The method that I wrote is working fine for the first row but it's not processing all the rows. Here is the code that I wrote...

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

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

Here is the code from dataprovider class

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

This is how my spreadsheet looks like..

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
3
javaselenium-webdriverapache-poi

3 Answers

0
votes

I think I see the issue. The for loop below should not end there. It should cover the below if-else ladder as well.

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

It should be:

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());
    }
0
votes

You can use the below code to read all rows from the excel sheet using Apache POI library. I just wrote directly in the main method. please change parameters/path accordingly.


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Sample Java program to read Excel file in Java using Apache POI
 *
 */
public class ReadAllExcelRow {

    public static void main(String[] args) {

        try {
            File excel = new File("C://temp/test.xlsx");
            FileInputStream fis = new FileInputStream(excel);
            XSSFWorkbook book = new XSSFWorkbook(fis);
            XSSFSheet sheet = book.getSheet("Sheet1");

            Iterator<Row> rowIterator = sheet.iterator();
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                Iterator<Cell> cellIterator = row.cellIterator();

                while (cellIterator.hasNext()) {
                    Cell cell = cellIterator.next();
                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                    }
                }
                System.out.println("");
            }
            book.close();
        } catch (FileNotFoundException fe) {
            fe.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }
}

0
votes

first possible solution:

for (int rownum = 1 ; rownum < sheet.rowcount; ++rownum) {
   /* some code */
}

second: you can try and use sheet.rowIterator