0

I'm creating a program that takes an Excel file and parses it and utilizes the info and creates a text file. In one of the columns in Excel it has number separated by a dash. (123-23). When I get the values from the cell, I get weird info in most of the cells.

CONCATENATE(LEFT(RIGHT(H639,5),3),"-",RIGHT(H639,2) is coming out instead of 123-23.

for (Row r : s) {
    Cell c = r.getCell(locIdColumnNumber);
    Cell d = r.getCell(lidColumnNumber);
    DataFormatter formatter = new DataFormatter();
    String val = formatter.formatCellValue(r.getCell(rsidColumnNumber));
    System.out.println(val);
}
Olaf Kock
  • 46,930
  • 8
  • 59
  • 90
Shawn
  • 15
  • 1
  • 3
  • Could it be a `Cell.CELL_TYPE_FORMULA`, for [example](https://stackoverflow.com/a/2079316/230513)? – trashgod Mar 07 '22 at 18:25
  • The "weird string" is a Excel formula. If you want the `DataFormatter` to evaluate formulas instead of simple printing them, it needs a `FormulaEvaluator`. `... FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); ... String val = formatter.formatCellValue(r.getCell(rsidColumnNumber), evaluator); ...` – Axel Richter Mar 07 '22 at 18:52
  • with POI 5.2.0 or 5.2.1, you can set a flag on the DataFormatter to prefer the cached values to the formulas - https://github.com/apache/poi/blob/trunk/poi/src/main/java/org/apache/poi/ss/usermodel/DataFormatter.java#L301 – PJ Fanning Mar 08 '22 at 00:57
  • See https://stackoverflow.com/questions/7608511/java-poi-how-to-read-excel-cell-value-and-not-the-formula-computing-it/71390145#71390145 – Axel Richter Mar 08 '22 at 04:48

0 Answers0