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.

Why not use ifferror() in the cell in excel before you call it from outside? — Solar Mike, Apr 17 '18 at 14:02
@Solar Mike I can't edit the excel file it's coming as an input. — Sudhanva c, Apr 17 '18 at 14:11
Well if you have the file, you can duplicate it: Just use an `=IFERROR(A1,0)` and drag it as wide and high as you need. — Luuklag, Apr 17 '18 at 14:43
Then why tag this question with excel? It is not an excel problem, but you might consider getting the producer of the file to do it. — Solar Mike, Apr 17 '18 at 15:03
"It's not going into that else if condition." Why should it? If the cell contains a formula and this formula results in an error, then the data format string does not changing though. You could try the approach in my answer here: https://stackoverflow.com/questions/45325694/unable-to-read-date-value-as-a-string-from-excel-sheet-calculated-by-formula-usi/45332083#45332083. — Axel Richter, Apr 17 '18 at 15:04
@Solar Mike The `excel` tag is correct since it is combined with `java` and `apache poi`. So it is a `java` and `apache poi` problem with `excel`. — Axel Richter, Apr 17 '18 at 15:07

score 1 · Accepted Answer · answered May 03 '18 at 15:25

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.

Handle #REF! cell from excel using apache poi

1 Answers1