Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

c#

excel

npoi

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 dislplay value IF(B2,J2=J1,FALSE)

how can get the result (value) not formula?

like image 802
user7099027 Avatar asked Mar 23 '17 09:03

user7099027


3 Answers

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;
                }
            }
like image 174
ajd.nas Avatar answered Oct 19 '22 20:10

ajd.nas


Try to use:

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

You can use several different properties based on column type.

like image 20
Przemysław Kleszcz Avatar answered Oct 19 '22 21:10

Przemysław Kleszcz


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?

like image 2
kumar chandraketu Avatar answered Oct 19 '22 21:10

kumar chandraketu