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);
}
}
}
}
