0

I want to match all formulas from my ArrayList<String> cellListofFormula to my sheet and to get the cell content out of this specified cells. Basically my celllistofFormula contains the cells like that B1; B43; D66 etc.

However, when iterating over my sheet with:

    for (int i = 0; i < cellListofFormula.size(); i++) {

        CellReference ref = new CellReference(cellListofFormula.get(i).toString());
        Row r = sheet.getRow(ref.getRow());
        if (r != null) {
            Cell c = r.getCell(ref.getCol());
            FormulaEvaluator eval = workbook.getCreationHelper().createFormulaEvaluator();
            eval.evaluateInCell(c);
            String value = c.getStringCellValue(); // I am getting the exception here
            System.out.println(c.getStringCellValue());
        }
    }

The exception I am getting:

Exception in thread "main" java.lang.IllegalStateException: Cannot get a text value from a numeric cell
    at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:648)
    at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:725)
    at org.apache.poi.hssf.usermodel.HSSFCell.getStringCellValue(HSSFCell.java:708)
    at com.excelParser.service.ExcelParser.readExcelData(ExcelParser.java:63)
    at com.excelParser.service.ExcelParser.main(ExcelParser.java:118)

The problem is that some cell values are numeric and some are string and some have formulas inside which give either a formula or a string value back. I just want to have the String value of the cell.

I appreciate your reply!

Carol.Kar
  • 4,581
  • 36
  • 131
  • 264
  • 3
    May be [This helps](http://stackoverflow.com/questions/6508203/when-getting-cell-content-using-apache-poi-library-i-get-both-cannot-get-a-num) – Jens Jun 13 '14 at 21:23
  • Use the `HSSFCell#getCellType()` method to determine what type of data the cell contains and then call the appropriate method(s) to get the value. This is clearly spelled out in the API Javadocs. – Jim Garrison Jun 13 '14 at 21:56

0 Answers0