Can't read a value of the cell with a formula inside properly by NPOI

Question

I write in C#, and use NPOI.SS.UserModel to write a program to get value from the cell on Excel.

The problem is the cell with formula. I am trying to get the result value from the formula cell, but I can't do it properly.

The formula is:

=LOOKUP(CA36,{0,40,50,60,70,80,90;"Ｄ","Ｃ","Ｂ2","Ｂ1","Ａ2","Ａ1","Ｓ"})

And, the cell's format is:

Format Cells > Number > Category=General

To read the value from the formula cell, there are 2 patterns. 1 is when read the excel as it is. 2 is when read the excel after edit the excel(e.g. fill in some letters in A1 cell, etc..). The ways to read the value between 1 and 2 are different.

In addition, once I make the excel as the 2 state, all readings the formula cell are successfull.

This program has to get correct result value from the formula cell when the 1 state. But I get 0 in number.

After I make the excell state as the 2 state, I can get correct valuein string (e.g. "D").

I have no idea why this difference have been seen.

For your information, when the state of 1:

cell.CellType==CellType.Formula
cell.CachedFormulaResultType==CellType.Numeric
cell.NumericCellValue=0

when the stete of 2:

cell.CellType==CellType.Formula
cell.CachedFormulaResultType==CellType.String
cell.StringCellValue="Ｄ"

the code is as follows: var book = WorkbookFactory.Create(this._fileFullPath);

//1枚目のシートを取得
var sheet = book.GetSheetAt(0);

int target_row_count = 50;
int target_col_count = 100;

List<CellData> cellDatas = new List<CellData>();

for (int i = 0; i < target_row_count; i++)
{
    int rowPosi = i + 1;
    for (int j = 0; j < target_col_count; j++)
    {
        int colPosi = j + 1;

        //テスト
        if (rowPosi == 36 && colPosi == 82) {
            Console.WriteLine("");
        }

        string value = "";

        var row = sheet.GetRow(i);
        if (row != null) {
            ICell cell = row.GetCell(j);
            if (cell != null) {
                switch (cell.CellType)
                {
                    case CellType.String:
                        value = cell.StringCellValue;
                        break;
                    case CellType.Numeric:
                        value = cell.NumericCellValue.ToString();
                        break;
                    case CellType.Boolean:
                        value = cell.BooleanCellValue.ToString();
                        break;
                    case CellType.Formula:
                        switch (cell.CachedFormulaResultType) {
                            case CellType.String:
                                value = cell.StringCellValue;
                                break;
                            case CellType.Numeric:
                                value = cell.NumericCellValue.ToString();
                                break;
                            case CellType.Blank:
                                break;
                            default:
                                StringBuilder sb = new StringBuilder();
                                sb.AppendLine($"セルタイプ({cell.CellType})ですが、");
                                sb.AppendLine($"cell.CachedFormulaResultType({cell.CachedFormulaResultType})に該当する処理がありません。");
                                throw new Exception(sb.ToString());
                        }
                        break;
                    case CellType.Blank:
                        break;
                    default:
                        throw new Exception(
                            $"セルタイプ({cell.CellType})に該当する処理がありません。");
                }
                
                CellData cellData = new CellData(
                    new CellPosition(
                        rowPosi,
                        colPosi),
                    value);

                cellDatas.Add(cellData);
            }
        }
    }
}

Answer 1

Let me get straight to the point, this issue was solved by changing the module from ClosedXML to NPOI for the process of editting the Excel file .

I'll explain what happened:

1. Prepared a template Excel file(hereinafter referred to as TEMP). TEMP has a cell which has the Lookup formula that I explaned in OP.
2. Copied the TEMP and create new Excel file(hereinafter referred to as NEW). Editted some cells on the NEW by ClosedXML.
3. Read the NEW by NPOI. Couldn't read the cell value of the cell with Lookup formula properly. (This is the OP.)
4. Changed the program for the process of the step 2. Made the process done by NPOI not by ClosedXML.
5. With new modified program, created NEW file and read the NEW file. Read cellvalue successfully from the cell with Lookup formula.

The reason that ClosedXML and NPOI was mixed up in the program. It used to use only ClosedXML module for both editing and reading Excel files. But, when "another" issue(couln't read the cellvalue from the cell with Lookup formula) than this OP occurred, I changed the module for ClosedXML to NPOI only for the process of reading Excel files. Because I found the article pointing out on the internet that there are un-supported formulas by ClosedXML and Lookup formula is so. But, for the process of editting Excel files, it looked it doesn't have any problem, so I continued using ClosedXML for editting Excel files.

However, in the issue this time, when you use ClosedXML to edit Excel file that has a cell with un-supported formula like Lookup formula, somehow the formula hasn't been calculated or evaluated. And when you read it, you can't read the calculated cellvalue properly.