CODEWITHSUNDEEP
javaexcelexcel-formulaapache-poi
user3662369
user3662369

Reputation: 21

fetch excel cell display value using Apache POI

Below shows the highlighted cell which display as - (hyphen) but actually has 0 value inside it.

enter image description here

It's a formula cell with following custom formatting.

enter image description here

It's a formula cell but when the result is 0, it displays as -.

My requirement is to fetch - instead of 0.

cell2Update = sheet.getRow(4).getCell(3);
cell2Update.setCellType(Cell.CELL_TYPE_NUMERIC);
System.out.println(cell2Update.getStringCellValue());//Exception 
System.out.println(cell2Update.getNumericCellValue());//Display as 0

Please help me with this doubt. I want to display the value which is displayed in Spreadsheet (-).

Upvotes: 2

Views: 688

Answers (1)

Axel Richter
Axel Richter

Reputation: 61852

Do using a DataFormatter as shown in Getting the cell contents. And because the value might be result of a formula. Do using using a DataFormatter together with a FormulaEvaluator:

...
DataFormatter formatter = new DataFormatter(); 
FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator(); 
...
cell2Update = sheet.getRow(4).getCell(3);
...
String value = formatter.formatCellValue(cell2Update, evaluator);
System.out.println(value);
...

From Apache POI version 5.2.0 on the DataFormatter can set to use cached values for formula cells. Thus cell values resulting from formulas can be got without evaluating all formulas again.

...
DataFormatter formatter = new DataFormatter(); 
//from 5.2.0 on the DataFormatter can set to use cached values for formula cells, 
//thus no FormulaEvaluator needed
dataFormatter.setUseCachedValuesForFormulaCells(true);
...
cell2Update = sheet.getRow(4).getCell(3);
...
String value = formatter.formatCellValue(cell2Update,);
System.out.println(value);
...

Upvotes: 3

Related Questions