unable to get numeric values from excel sheet in selenium webdriver with java

Question

My Java

public class Readexcel {
public void readfile(String filepath, String filename, String sheetname) throws IOException
{
    File file = new File(filepath+"\\"+filename);
    FileInputStream fis = new FileInputStream(file);

    // for creating .xlsx workbook
    Workbook wb = new XSSFWorkbook(fis);

    // for reading the sheet by its name
    Sheet sh = wb.getSheet(sheetname);

    //find the total rows in sheet

    int rowcount = sh.getLastRowNum()-sh.getFirstRowNum();

    // create a loop to create 

    for(int i=0;i<rowcount+1;i++)
    {
        Row row= sh.getRow(i);

        // create a loop to print cell values

        for(int j=0;j<row.getLastCellNum();j++)
        {
            Cell cell= row.getCell(j);
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                    System.out.print(row.getCell(j).getStringCellValue() + " ");
                    break;

            case Cell.CELL_TYPE_NUMERIC:
                    System.out.print(row.getCell(j).getNumericCellValue() + " ");
                    break;
            }
            System.out.print(row.getCell(j).getStringCellValue()+"||");

        }


    System.out.println();
    }


    }

   public static void main(String...strings) throws IOException
     {
         Readexcel re = new Readexcel();
         String filepath = "F://Excelsheet";
         re.readfile(filepath,"Book1.xlsx","Sheet1");
      }

}

By using above code I am getting an error "cannot get text value from numeric cell". Any Help? Also My Output is not properly alligned. All the String are showing one under one. output should be like

Username Password
john     123
rambo    456

but i am getting output like

Username
password
john

Gelbi · Accepted Answer · 2016-08-17 14:13:17Z

2

Change your for loop after // create a loop to print cell values comment for this:

for (int j = 0; j < row.getLastCellNum(); j++) {
    Cell cell = row.getCell(j);
    switch (cell.getCellType()) {
    case Cell.CELL_TYPE_STRING:
            System.out.print(row.getCell(j).getStringCellValue() + " ");
            break;

    case Cell.CELL_TYPE_NUMERIC:
            System.out.print((int)row.getCell(j).getNumericCellValue() + " ");
            break;

            }

}

Switch is to recognise type of cell. For numeric cells you have to use getNumericCellValue() instead of getStringCellValue()

For the second problem use System.out.print() instead System.out.println() which is used to print what is between the double quotes and move the printing cursor to the next line.

EDIT:

This how my readFile() function looks:

public void readfile(String filepath, String filename, String sheetname)    throws IOException {


    File file = new File(filepath+"\\"+filename);
    FileInputStream fis = new FileInputStream(file);

    // for creating .xlsx workbook
    Workbook wb = new XSSFWorkbook(fis);

    // for reading the sheet by its name
    Sheet sh = wb.getSheet(sheetname);

    // find the total rows in sheet

    int rowcount = sh.getLastRowNum() - sh.getFirstRowNum();

    // create a loop to create

    for (int i = 0; i < rowcount + 1; i++) {
        Row row = sh.getRow(i);

        // create a loop to print cell values

        for (int j = 0; j < row.getLastCellNum(); j++) {
            Cell cell = row.getCell(j);
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                System.out.print(row.getCell(j).getStringCellValue() + " ");
                break;

            case Cell.CELL_TYPE_NUMERIC:
                System.out.print((int)row.getCell(j).getNumericCellValue() + " ");
                break;

            }

        }

        System.out.println();
    }

}

EDIT 2

Changed System.out.print(row.getCell(j).getNumericCellValue() + " "); for System.out.print((int)row.getCell(j).getNumericCellValue() + " "); in case Cell.CELL_TYPE_NUMERIC

edited Aug 17, 2016 at 14:13

answered Aug 17, 2016 at 13:24

Gelbi

1514 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ab123 Over a year ago

Username Username|| password password|| john john|| 12345.0 getting output like this(rambo is unable to print)

Gelbi Over a year ago

I pasted my readfile function in my answer. My output looks like this:

Gelbi Over a year ago

Delete that line: System.out.print(row.getCell(j).getStringCellValue()+"||");

Gelbi Over a year ago

I have pasted how my readfile function looks like in my answer

Ab123 Over a year ago

it is very near, the numeric values are 123 but it is showing 123.0

|

Collectives™ on Stack Overflow

unable to get numeric values from excel sheet in selenium webdriver with java

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related