Surfing The Keybord ⌨⌨⌨

NPOI是一套十分強大的Excel轉換套件

他可以讀也可以寫

雖然組裝及讀取表格的時候稍嫌麻煩

但也是因為這樣所以自由度比其他套件大上許多

---

這次是因為遇到了匯入的需求

使用者需要將Excel表格匯入

但欄位的屬性卻非常多種

有基本的文字、數值、日期、公式

以下就簡單分享我所寫的程式

 

處理公式的時候需要用到FormulaEvaluator

但他會隨著傳入的Excel格式不一樣而要使用不同的物件

所以要在一開始判斷傳入格式宣告Workbook物件的時候一起宣告起來

#region 讀取Excel檔案
IWorkbook workbook;
IFormulaEvaluator formulaEvaluator;
using (FileStream filex = new FileStream(path, FileMode.Open, FileAccess.Read))
{
    if (Path.GetExtension(path) == ".xls")
    {   // xls
        workbook = new HSSFWorkbook(filex);
        formulaEvaluator = new HSSFFormulaEvaluator(workbook); // Important!! 取公式值的時候會用到
    }
    else if (Path.GetExtension(path) == ".xlsx")
    {   // xlsx
        workbook = new XSSFWorkbook(filex);
        formulaEvaluator = new XSSFFormulaEvaluator(workbook); // Important!! 取公式值的時候會用到
    }
    else
    {
        return msg = "檔案格式錯誤。";
    }
}
var st = workbook.GetSheetAt(0);
#endregion

 

foreach (var cell in row)
{
    string columnStr = string.Empty;

    switch (cell.CellType)
    {
        case CellType.Numeric:  // 數值格式
            if (DateUtil.IsCellDateFormatted(cell))
            {   // 日期格式
                columnStr = cell.DateCellValue.ToString();
            }
            else
            {   // 數值格式
                columnStr = cell.NumericCellValue.ToString();
            }
            break;
        case CellType.String:   // 字串格式
            columnStr = cell.StringCellValue;
            break;
        case CellType.Formula:  // 公式格式
            var formulaValue = formulaEvaluator.Evaluate(cell);
            if (formulaValue.CellType == CellType.String) columnStr = formulaValue.StringValue.ToString();          // 執行公式後的值為字串型態
            else if (formulaValue.CellType == CellType.Numeric) columnStr = formulaValue.NumberValue.ToString();    // 執行公式後的值為數字型態
            break;
        default:
            break;
    }
}

 

字串格式就很基本不講了

數值格式的話

因為日期與數字都會被判斷為數值格式

所以我在內部加入了DateUtil.IsCellDateFormatted(cell)這個判斷式

他的說明是Check if a cell Contains a date Since dates are stored internally in Excel as double values we infer it Is a date if it Is formatted as such.

簡單來說就是他會去判斷那個值是否為精確日期的刻度(就是全部都是數字的那個格式啦)

如果是的話他就會回傳true

這樣就可以判斷要轉成DateCellValue(日期)還是NumericCellValue(數字)了

有時候User上傳怎麼傳就是傳不上去

打開檔案乍看之下格式也都正確

點進去才發現他原來是公式

那公式要如何取值？

其實就是把他判斷為NumericCellValue就好了

不過這應該只是針對值是數值型的公式

若是字串型的就要另外再試了

因為我也還沒遇到這樣的需求

所以就待之後遇到補上吧

2017/02/21編輯：

前一天才剛打完就遇到需求了

其實當時也有找到類似的資料(忘記在哪看到了)

如果有這樣的需求時就必須加入一些判斷

首先依照不同的Excel不同而建立不同的公式計算物件(一樣就是XSSF跟HSSF的差別)

再來就是公式的部分

formulaEvaluator.Evaluate(cell)先用這個把公式的值計算出來

計算後他其實就會賦予這個值一個CellType

再用這個CellType來取值(就跟Step2的取值方式一樣了)

這樣就可以取到正確的值了

 

2017/02/23編輯：

若使用公式計算會出現一個問題

如果公式內有參考其他Excel

就會因為找不到檔案所以報錯

所以我又找了另外一個方法

if (cell.CachedFormulaResultType == CellType.String) columnStr = cell.StringCellValue;
else if (cell.CachedFormulaResultType == CellType.Numeric) columnStr = cell.NumericCellValue.ToString();
else columnStr = "";

當這個儲存格格式為Formula時

我可以去取他的CachedFormulaResultType

也就是這個公式計算後的暫存值的資料型別

Excel會將公式計算的結果暫存在檔案內

所以即使收到檔案的對方沒有參考來源檔

也可以讀到正確的數值

那麼我們就可以利用判斷暫存值型別

來去針對型別取他的暫存值

利用StringCellValue及NumericCellValue

不用經過計算即可正確取得暫存於檔案的數值

 

https://dotblogs.com.tw/mis2000lab/2011/06/09/npoi_excel_formula_value

2017/02/21 修改公式取值部分

2017/02/23 修改公式取值若資料內容為參考其他Excel會錯誤

Write By Charley Chang 

---

新手發文，若有錯誤還請指教，
歡迎留言或Mail✉給我

本著作係採用創用 CC 姓名標示-非商業性-相同方式分享 4.0 國際 授權條款授權.