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

c# excel npoi
user7099027 picture user7099027 · Mar 23, 2017 · Viewed 11.5k times · Source

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?

Answer

ajd.nas picture ajd.nas · Nov 13, 2018 
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;
                }
            }

Related questions

How to read file using NPOI

I found NPOI is very good to write Excel files with C#. But I want to open, read and modify Excel files in C#. How can I do this?

Read More
Trying to create a new .xlsx file using NPOI and write to it

Edit: I'm trying to write a small console application that will read in lines from an excel spreadsheet, parse the lines and write the fielded data to a new excel file. I'm using .NET and the NPOI library. I finally …

Read More
Does NPOI have support to .xlsx format?

Will NPOI DLL recognize .xlsx file? Currently I'm using NPOI 1.2.5 version DLL for Microsoft Excel 97-2003, but I need to access Excel sheets of extension .xlsx also. Will NPOI support the above? Code snippet: static void Main(string[] args) { XSSFWorkbook …

Read More