1

I am trying to read data from an excel sheet and write on my application.But shows an error while trying to pass a numeric value using string. Please suggest me how can I resolve the issue. Please see my code and error screenshot below:

package ReadExcelData;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.Locale;

import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {

public static void main(String[] args) throws Exception {
    File src=new File("E:\\QA_Automation\\TestData_Excel\\TestInputData.xlsx");

    FileInputStream fis=new FileInputStream(src);

    XSSFWorkbook wb=new XSSFWorkbook(fis);

    XSSFSheet sheet1=wb.getSheetAt(0);
    int rowcount=sheet1.getLastRowNum();
    System.out.println("Total Row" + rowcount);

    for(int i=0;i<rowcount;i++) {
        String data0= sheet1.getRow(i).getCell(0).getStringCellValue();
        System.out.println("Test Data From Excel"+data0);
    }

    wb.close();


}

}

Please check below image for the exception :

enter image description here

  • Please check given code in My answer. If you found any errors let me know – Sagar007 Oct 26 '17 at 9:27
  • In Excel sheet before entering a numeric value inside a cell ,enter ~ symbol(tild) and enter the value. For Example: ~123. This might help you. – shanila Nov 1 '17 at 9:25
2

This may be helpful to you..

Open the Excel sheet and select all (Ctrl+a) then right click and click Format cells then in number tab change the category to Text and click Ok...

  • you are welcome @ShirinAkter – Sachintha Nov 1 '17 at 2:45
1

This exception is occurred when code try to read Numeric value as string. Apache POI will not convert from Numeric to String.


There are two solution :

1. First convert cell type to string and Read it. Please check below code :

import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.util.Locale;

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

    public class ReadExcel {

    public static void main(String[] args) throws Exception {
        File src=new File("C:\\Users\\Sagar\\Desktop\\TestInputData.xlsx");

        FileInputStream fis=new FileInputStream(src);

        XSSFWorkbook wb=new XSSFWorkbook(fis);

        XSSFSheet sheet1=wb.getSheetAt(0);
        int rowcount=sheet1.getLastRowNum();
        System.out.println("Total Row" + rowcount);

        for(int i=0;i<rowcount+1;i++) {

            System.out.println(i);
            //GET CELL
            Cell cell1 = sheet1.getRow(i).getCell(0);   
            //SET AS STRING TYPE
            cell1.setCellType(Cell.CELL_TYPE_STRING);
            String data0= cell1.getStringCellValue();
            System.out.println("Test Data From Excel : "+data0);
        }

        wb.close();
      }
    }

2. Get type first and print by its option. Please see the code:

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.util.Locale;

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

    public class ReadExcel {

    public static void main(String[] args) throws Exception {
        File src=new File("C:\\Users\\Sagar\\Desktop\\TestInputData.xlsx");

        FileInputStream fis=new FileInputStream(src);

        XSSFWorkbook wb=new XSSFWorkbook(fis);

        XSSFSheet sheet1=wb.getSheetAt(0);
        int rowcount=sheet1.getLastRowNum();
        System.out.println("Total Row " + rowcount);

        for(int i=0;i<rowcount+1;i++) {


            Cell cell1 = sheet1.getRow(i).getCell(0);   

            switch (cell1.getCellType()) {

            case Cell.CELL_TYPE_STRING:
                System.out.println(cell1.getRichStringCellValue().getString());
                break;

            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell1)) {
                    System.out.println(cell1.getDateCellValue());
                } else {
                    System.out.println(cell1.getNumericCellValue());
                }
                break;

            case Cell.CELL_TYPE_BOOLEAN:
                System.out.println(cell1.getBooleanCellValue());
                break;

            case Cell.CELL_TYPE_FORMULA:
                System.out.println(cell1.getCellFormula());
                break;

            default:
                System.out.println();
        }

            //String data0= cell1.getStringCellValue();
            //System.out.println("Test Data From Excel : "+data0);
        }

        wb.close();


    }

    }

Note : Please check loop condition.It is also wrong. My code will not skip last row.

  • Above both code will print any type data which cover by apache poi formats. – Sagar007 Oct 26 '17 at 9:26
1

You Can Check if cell is numeric or String through this code

     if(MytempCell.getCellType() == Cell.CELL_TYPE_NUMERIC)
       your code ...
     else if(MytempCell.getCellType() == Cell.CELL_TYPE_STRING)
      your code ..
0

Assuming there is the column holding only numeric cells you can approach with this:

for(int i=0;i<rowcount;i++) {
    String data0 = String.valueOf(sheet1.getRow(i).getCell(0).getNumericCellValue());
    System.out.println("Test Data From Excel" + data0);
}

The approach above is a pure java way to display data of double type. However if you need the formatting like you would have in Excel you should check this doc: https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html

0
*CELLNAME*.setCellType(CellType.NUMERIC);

This works wonderfully and converts the data in the cell as numeric.

For operating on that value use:

*CELLNAME*.getNumericCellValue()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.