NPOI does not evaluate 3d formula

Question

I'm using NPOI to read data from Excel 2003 files. These files contains formulas like this SUM('1:2'!$C$17). NPOI recognized such formulas like SUM('1'!$C$17) (w/o sheet 2) and evaluate invalid result. I'm using a regular code from NPOI examples, like

    foreach (var row in docRows)
            {
                sb.AppendFormat("{0}\t", SomeCode);
                rowCounter += 1;
                sb.AppendFormat("{0}\t", rowCounter);
                foreach (var col in docColumns)
                {
                    ICell cell = sheet.GetRow(row).GetCell(col);

                    sb.AppendFormat("{0}\t", GetExcelCellValue(cell));
                }
                sb.AppendLine();
            }
private string GetExcelCellValue(ICell cell)
        {
            string cellValue = string.Empty;
            IFormulaEvaluator evaluator = _hssfworkbook.GetCreationHelper().CreateFormulaEvaluator();
            evaluator.Evaluate(cell);

            switch (evaluator.EvaluateInCell(cell).CellType)
            {
                case CellType.BLANK:
                    cellValue = string.Empty;
                    break;
                case CellType.BOOLEAN:
                    cellValue = string.Empty;
                    break;
                case CellType.NUMERIC:
                    cellValue = Convert.ToString(cell.NumericCellValue);    //This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number.
                    break;
                case CellType.STRING:
                    throw new ArgumentNullException();
                    cellValue = cell.StringCellValue;
                    break;
                case CellType.ERROR:
                    cellValue = string.Empty;
                    break;
                case CellType.FORMULA:

                    break;

            }
            return cellValue;
        }

William H William H · Accepted Answer · 2017-05-26T12:54:03

I have just encountered this problem and I solved it by

switch (cell.CellType)
                {
                    case CellType.Blank:
                        cellValue = "";
                        break;
                    case CellType.Boolean:
                        cellValue = cell.BooleanCellValue.ToString();
                        break;
                    case CellType.Formula:

                        cellValue = cell.NumericCellValue.ToString();
                        break;
                    case CellType.Numeric:
                        cellValue = cell.NumericCellValue.ToString();
                        break;
                    case CellType.String:
                        cellValue = cell.StringCellValue;
                        break;
                }

NPOI does not evaluate 3d formula

1 Answers