Java POI How to read Excel cell value and not the formula computing it

Question

I am using Apache POI API to getting values from an Excel file   Everything is working great except with cells containing formulas  In fact  the cell getStringCellValue   is returning the formula used in the cell and not the value of the cell    I tried to use evaluateFormulaCell   method but it s not working because I am using GETPIVOTDATA Excel formula and this formula is not implemented in the API   Exception in thread  main  org apache poi ss formula eval NotImplementedException  Error evaluating cell Landscape K11     at org apache poi ss formula WorkbookEvaluator addExceptionInfo WorkbookEvaluator java 321      at org apache poi ss formula WorkbookEvaluator evaluateAny WorkbookEvaluator java 288      at org apache poi ss formula WorkbookEvaluator evaluate WorkbookEvaluator java 221      at org apache poi hssf usermodel HSSFFormulaEvaluator evaluateFormulaCellValue HSSFFormulaEvaluator java 320      at org apache poi hssf usermodel HSSFFormulaEvaluator evaluateFormulaCell HSSFFormulaEvaluator java 213      at fromExcelToJava ExcelSheetReader unAutreTest ExcelSheetReader java 193      at fromExcelToJava ExcelSheetReader main ExcelSheetReader java 224  Caused by  org apache poi ss formula eval NotImplementedException  GETPIVOTDATA     at org apache poi hssf record formula functions NotImplementedFunction evaluate NotImplementedFunction java 42

User · Answer

There is an alternative command where you can get the raw value of a cell where formula is put on  It s returns type is String  Use   cell getRawValue

User · Answer

If you want to extract a raw-ish value from a HSSF cell  you can use something like this code fragment   CellBase base    CellBase  cell  CellType cellType   cell getCellType    base setCellType CellType STRING   String result   cell getStringCellValue    base setCellType cellType     At least for strings that are completely composed of digits  and automatically converted to numbers by Excel   this returns the original string  e g   12345   instead of a fractional value  e g   12345 0    Note that setCellType is available in interface Cell as of v  4 1  but deprecated and announced to be eliminated in v 5 x  whereas this method is still available in class CellBase  Obviously  it would be nicer either to have getRawValue in the Cell interface or at least to be able use getStringCellValue on non STRING cell types  Unfortunately  all replacements of setCellType mentioned in the description won t cover this use case  maybe a member of the POI dev team reads this answer

User · Answer

Previously posted solutions did not work for me  cell getRawValue   returned the same formula as stated in the cell  The following function worked for me    public void readFormula   throws IOException       FileInputStream fis   new FileInputStream  Path of your file        Workbook wb   new XSSFWorkbook fis       Sheet sheet   wb getSheetAt 0       FormulaEvaluator evaluator   wb getCreationHelper   createFormulaEvaluator         CellReference cellReference   new CellReference  C2       pass the cell which contains the formula     Row row   sheet getRow cellReference getRow         Cell cell   row getCell cellReference getCol          CellValue cellValue   evaluator evaluate cell        switch  cellValue getCellType              case Cell CELL TYPE BOOLEAN              System out println cellValue getBooleanValue                 break          case Cell CELL TYPE NUMERIC              System out println cellValue getNumberValue                 break          case Cell CELL TYPE STRING              System out println cellValue getStringValue                 break          case Cell CELL TYPE BLANK              break          case Cell CELL TYPE ERROR              break              CELL TYPE FORMULA will never happen         case Cell CELL TYPE FORMULA              break

User · Answer

SelThroughJava s answer was very  helpful I had to modify a bit to my code to be worked   I used https   mvnrepository com artifact org apache poi poi  and  https   mvnrepository com artifact org testng testng as dependencies   Full code is given below with exact imports    import java io FileInputStream      import java io FileNotFoundException      import java io IOException       import org apache poi hssf usermodel HSSFCell      import org apache poi hssf util CellReference      import org apache poi sl usermodel Sheet      import org apache poi ss usermodel Cell      import org apache poi ss usermodel CellType      import org apache poi ss usermodel CellValue      import org apache poi ss usermodel FormulaEvaluator      import org apache poi ss usermodel Row      import org apache poi ss usermodel Workbook      import org apache poi ss usermodel WorkbookFactory      import org apache poi xssf usermodel XSSFWorkbook        public class ReadExcelFormulaValue            private static final CellType NUMERIC   null          public static void main String   args                try                   readFormula                  catch  IOException e                       TODO Auto-generated catch block                 e printStackTrace                                    public static void readFormula   throws IOException               FileInputStream fis   new FileInputStream  C eclipse-workspace  sam-webdbriver-diaries  resources  tUser WS xls                org apache poi ss usermodel Workbook workbook   WorkbookFactory create fis               org apache poi ss usermodel Sheet sheet   workbook getSheetAt 0                FormulaEvaluator evaluator   workbook getCreationHelper   createFormulaEvaluator                 CellReference cellReference   new CellReference  G2       pass the cell which contains the formula             Row row   sheet getRow cellReference getRow                 Cell cell   row getCell cellReference getCol                  CellValue cellValue   evaluator evaluate cell               System out println  Cell type month  is   cellValue getCellTypeEnum                 System out println  getNumberValue month is    cellValue getNumberValue                        System out println  getStringValue   cellValue getStringValue                   cellReference   new CellReference  H2       pass the cell which contains the formula              row   sheet getRow cellReference getRow                  cell   row getCell cellReference getCol                  cellValue   evaluator evaluate cell               System out println  getNumberValue DAY is    cellValue getNumberValue

User · Answer

For formula cells  excel stores two things  One is the Formula itself  the other is the  cached  value  the last value that the forumla was evaluated as   If you want to get the last cached value  which may no longer be correct  but as long as Excel saved the file and you haven t changed it it should be   you ll want something like    for Cell cell   row         if cell getCellType      Cell CELL TYPE FORMULA            System out println  Formula is     cell getCellFormula             switch cell getCachedFormulaResultType                  case Cell CELL TYPE NUMERIC                  System out println  Last evaluated as      cell getNumericCellValue                     break              case Cell CELL TYPE STRING                  System out println  Last evaluated as       cell getRichStringCellValue                            break

[java] Java POI : How to read Excel cell value and not the formula computing it?

Examples related to java

Examples related to excel

Examples related to apache-poi

Examples related to notimplementedexception