c# npoi excel How to get a cell's formula VALUE?

Question

I set a formula on some cells in a loop like this:

System.String fm = "IF(B2,J2=J1,FALSE)"
another.GetRow(0).CreateCell(28).SetCellFormula(fm);

I always wondered how to get the result (value) of this formula and not copy the entire formula.

MessageBox.Show(another.GetRow(0).GetCell(28).ToString);

it displays value IF(B2,J2=J1,FALSE)

how can get the result (value), not the formula itself?

score 7 · Answer 1 · answered Nov 13 '18 at 09:23

HSSFFormulaEvaluator formula = new HSSFFormulaEvaluator(workBook);

then you can use this formula for all cells in your excel file by using

formula.EvaluateAll();

or you can use it for specific cell like this

var cell = sheet.GetRow(row).GetCell(column);
            string Res = "";
if (cell != null)
            {
                formula.EvaluateInCell(cell);

                switch (cell.CellType)
                {
                    case NPOI.SS.UserModel.CellType.Numeric:
                        Res = sheet.GetRow(row).GetCell(column).NumericCellValue.ToString();
                        break;
                    case NPOI.SS.UserModel.CellType.String:
                        Res = sheet.GetRow(row).GetCell(column).StringCellValue;
                        break;
                }
            }

Przemysław Kleszcz · Answer 2 · 2017-03-23T09:52:46.127

4

Try to use:

another.GetRow(0).GetCell(28).NumericCellValue;

You can use several different properties based on column type.

edited Mar 23 '17 at 09:52

answered Mar 23 '17 at 09:47

Przemysław Kleszcz

536
1
7
13

cannot work, it display value 0,but in excel dispaly FALSE – user7099027 Mar 23 '17 at 10:00
2

So try to use BooleanCellValue property – Przemysław Kleszcz Mar 23 '17 at 11:42

score 2 · Answer 3 · answered Aug 31 '17 at 14:06

2

Use "EvaluateAllFormulaCells(workbook)" method to evaluate all formulas after setting it into excel file. see this link How to re-calculate a cell's formula?

answered Aug 31 '17 at 14:06

kumar chandraketu

2,232
2
20
25

score 2 · Answer 4 · answered Aug 19 '19 at 07:33

2

Use StringCellValue to get the value from cell your formula will not read

MessageBox.Show(another.GetRow(0).GetCell(28).StringCellValue

answered Aug 19 '19 at 07:33

Saravanan Seenivasan

139
1
4
16

score 2 · Answer 5 · answered Nov 20 '22 at 11:40

You can set the cell type to a string, it worked for me as its getting the calculated value:

ICell cell = sheet.GetRow(i).GetCell(1);
cell.SetCellType(NPOI.SS.UserModel.CellType.String);
var value  = sheet.GetRow(i).Cells[1].StringCellValue.ToString();

score 0 · Answer 6 · answered Feb 28 '18 at 11:47

0

try this https://poi.apache.org/spreadsheet/eval.html

example

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
CellValue cellValue = evaluator.evaluate(cell);

answered Feb 28 '18 at 11:47

Muflix

6,192
17
77
153

c# npoi excel How to get a cell's formula VALUE?

6 Answers6