When getting cell content using Apache-POI Library, I get both "Cannot get a numeric value from a text cell" and the reverse of that. How do I fix it?

Question

I realize the question is a little confusing, but I didn't know how else to word it. Anyway, here is the original code:

private void readFile(String excelFileName) throws FileNotFoundException, IOException {     XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(excelFileName));     if (workbook.getNumberOfSheets() > 1){         System.out.println("Please make sure there is only one sheet in the excel workbook.");     }     XSSFSheet sheet = workbook.getSheetAt(0);     int numOfPhysRows = sheet.getPhysicalNumberOfRows();     XSSFRow row;     XSSFCell num;     for(int y = 1;y < numOfPhysRows;y++){    //start at the 2nd row since 1st should be category names         row = sheet.getRow(y);         poNum = row.getCell(1);         item = new Item(Integer.parseInt(poNum.getStringCellValue());         itemList.add(item);         y++;     } }  private int poiConvertFromStringtoInt(XSSFCell cell){     int x = Integer.parseInt(Double.toString(cell.getNumericCellValue()));     return x; } 

I am getting the following error:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a numeric value from a text cell     at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:781)     at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:199) 

Even if I change it to get either a string using XSSFCell.getStringCellValue() or even XFFSCell.getRichTextValue, I get the reverse of the above error message (and I am making sure to ultimately make it an int using Integer.parseInt(XSSFCell.getStringCellValue()).

The error then reads:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell     at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:781)     at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:199) 

I know for a fact that the excel spreadsheet column is in fact a string. I can't change the excel sheet as it is uploaded else where always using the same format and formatting each column first takes up to much processing time.

Any suggestions?

[Solution] Here is the solution code I came up with from @Wivani's help:

private long poiGetCellValue(XSSFCell cell){     long x;     if(cell.getCellType() == 0)         x = (long)cell.getNumericCellValue();     else if(cell.getCellType() == 1)         x = Long.parseLong(cell.getStringCellValue());     else         x = -1;     return x; } 

Answer 1

Use This as reference  switch (cell.getCellType()) {                 case Cell.CELL_TYPE_STRING:                     System.out.println(cell.getRichStringCellValue().getString());                     break;                 case Cell.CELL_TYPE_NUMERIC:                     if (DateUtil.isCellDateFormatted(cell)) {                         System.out.println(cell.getDateCellValue());                     } else {                         System.out.println(cell.getNumericCellValue());                     }                     break;                 case Cell.CELL_TYPE_BOOLEAN:                     System.out.println(cell.getBooleanCellValue());                     break;                 case Cell.CELL_TYPE_FORMULA:                     System.out.println(cell.getCellFormula());                     break;                 default:                     System.out.println();             } 

Answer 2

You can get value as String using the format defined for this cell :

final DataFormatter df = new DataFormatter(); final XSSFCell cell = row.getCell(cellIndex); String valueAsString = df.formatCellValue(cell); 

Thanks to this answer.