How to read cell text when formula is attached to cell.

May 23, 2013 at 12:29 PM
Hello,

I am reading a excel file using NPOI dll. This excel file have few cells which have formulas associated with it. now when i read the cell value it return the formula instead of actual text.

Here is my code.

ICell cell = row.GetCell(i);
            if (cell == null)
            {
                dr[i] = null;
            }
            else
            {
                dr[i] = cell.ToString(); // This line returning the "M16" instead of 18.00
            }

Please let me know if anyone knows how to read the actual text of the cell.
May 24, 2013 at 2:31 PM
I am also facing the same problem. When the Excel is converted to DataTable, the columns holds the formulas, but I need the value evaluated from the formulas.

Is there any way in NPOI, which gives us the value and not the formula.

Please help. Thanks
Jul 22, 2013 at 4:46 PM
In that case you can go with 2 options, or either use the ICell.CachedFormulaResultType which gives you the CellType of the actual result of that Formula and then parse that or you can go with Workbook.GetCreationHelper().CreateFormulaEvaluator().EvaluateInCell(ICell) which basically evaluates the formula and replaces that with the ACTUAL Value in the cell.

If you go with the first approach, you can create an extension method and do something like :
public static object GetValue(this ICell cell, CellType cellType)
        {
            object value = null;

            switch (cellType)
            {
                case CellType.STRING:
                {
                    value = cell.StringCellValue;
                    break;
                }
                case CellType.FORMULA:
                {   //If its a Formula, get the Actual Value not the formula itself.
                    value = cell.GetValue(cell.CachedFormulaResultType);
                    break;
                }
---- rest of code