Handle #REF! cell from excel using apache poi

Question

I need to handle #REF! cell from my excel, where ever I get #REF! I need to return 0 or Empty quotes to the datastructure. I tried doing this but I'm not able to succeed, here is the snippet:-

if (valueCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                if (valueCell.getCellStyle().getDataFormatString().contains("%")) {
                    // Detect Percent Values
                      values.put(valueHeader, String.valueOf(Double.valueOf(valueCell.getNumericCellValue() * 100).intValue()) + "%");

                }else if (valueCell.getCellType() == Cell.CELL_TYPE_ERROR) {
                    values.put(valueHeader,"");
                } else {
                      values.put(valueHeader,String.valueOf(valueCell.getNumericCellValue()));

                }
            }

It's not going into that else if condition. And it throws

Cannot get a NUMERIC value from a STRING cell

Please help me with this.

Answer 1

This worked for me!

if (valueCell.getCellType() == Cell.CELL_TYPE_FORMULA) {
                switch (valueCell.getCachedFormulaResultTypeEnum()) {
                   case STRING:
                       values.put(valueHeader,"");
                       break;
                      case NUMERIC:
                          if (valueCell.getCellStyle().getDataFormatString().contains("%")) {
                                // Detect Percent Values
                                  values.put(valueHeader, String.valueOf(Double.valueOf(valueCell.getNumericCellValue() * 100).intValue()) + "%");

                            }else{
                          values.put(valueHeader,String.valueOf(valueCell.getNumericCellValue()));
                            }
                       break;
                      case ERROR:
                          values.put(valueHeader,"");
                       break;
                      default:
                       System.out.println("default"); //should never occur
                     }
                     }

where values is the object defined for the data-structure.