1

網友 Danny 在舊文留言提問關於 NPOI 讀取 Excel 日期，"2017/9/23"被轉成"23-九月-2017"的問題，我已棄用 NPOI 投向新歡 ClosedXML 多年，沒打算再花時間研究，於是題目改成: 面對相同文件，ClosedXML 能否順利過關?

取得 Danny 提供的測試樣本，挺有趣的，共有四欄(F、G、N、AS)包含日期資料，第一列有欄名分別為A6、A7、A14與A45:

四欄儲存格格式各有千秋，A6 設成自訂 yyyy/mm/dd:

A7 設為文字，其中包含 1953.9.12、1968/1/13、1953/09/12 甚至 1953912 ，格式很混亂:

A14、A45 設為日期，取格式 2012/3/14:

測試程式如下，為因應 Excel 中五花八門的日期格式，我寫了一個 ParseDateValue() 接收 IXLCell 進行解析。若 cell.DataType == XLCellValues.DateTime 就直接回傳 cell.GetDateTime() 並將儲存格改為藍字；若否，則取得 cell.GetString() 再以 DateTime.TryParseExact() 配合預先定義的格式嘗試解析。(註: yyyyMd 格式存在爭議，例如: 2012111 可能是 1/11 也可能是 11/1，視為無效)

    class Program
    {
        static void Main(string[] args)
        {
            var path = "D:\\日期格式.xlsx";
            using (var wb = new XLWorkbook(path))
            {
                var ws = wb.Worksheets.First();
                var r = 2;
                while (!ws.Row(r).Cell("F").IsEmpty())
                {
                    var row = ws.Row(r);
                    var a6 = ParseDateValue(row.Cell("F"));
                    var a7 = ParseDateValue(row.Cell("G"));
                    var a14 = ParseDateValue(row.Cell("N"));
                    var a45 = ParseDateValue(row.Cell("AS"));
                    Console.WriteLine(
$"A6:{a6:yyyy-MM-dd} A7:{a7:yyyy-MM-dd} A14:{a14:yyyy-MM-dd} A45:{a45:yyyy-MM-dd}");
                    r++;
                }
                Console.ReadLine();
                using (FileStream fs = new FileStream("d:\\Output.xlsx", FileMode.Create))
                {
                    wb.SaveAs(fs);
                }
            }
        }
 
        static DateTime? ParseDateValue(IXLCell cell)
        {
            if (cell.DataType == XLCellValues.DateTime)
            {
                cell.Style.Font.FontColor = XLColor.Blue;
                return cell.GetDateTime();
            }
            var dateString = cell.GetString();
            DateTime d;
            var dateFormats = "yyyy/M/d,yyyy/M/d,yyyy/MM/dd,yyyy.M.d".Split(',');
            foreach (var fmt in dateFormats)
            {
                if (DateTime.TryParseExact(dateString, fmt, null,
                    DateTimeStyles.None, out d))
                {
                    Debug.WriteLine($"Custom Format: {fmt} for {dateString}");
                    return d;
                }
            }
            Debug.WriteLine($"無法識別:{dateString}");
            return null;
        }
    }

執行結果如下:

 

除 1953912 之外，其餘日期值均被正確解讀。A3 欄儲存格採自訂格式 yyyy/mm/dd、A14 及 A45 欄採日期格式，如上圖所示，ClosedXML 均視為 XLCellValues.DateTime 故變成藍字，唯一的例外是 A14 欄第一筆 1985/7/26，原因是它前方加了單引號(如下圖所示)將其宣告為字串，故要改由 DateTime.TryParseExact() 解析。

至於 A7 儲存格格式為文字，需取回字串再自行轉換。

實驗完畢，證實 ClosedXML 可正確解讀日期格式不一的 Excel 文件，我則學到透過 DataType 屬性偵測資料型別的技巧。若想進一步 DataType 與 DateFormat/NumberFormat 的應用，可參考 ClosedXML 官方範例: Data Types · ClosedXML-ClosedXML Wiki · GitHub。


Comments

# by Danny

謝謝黑暗大大的解析 我了解了 我會試試看的 感恩 ^O^

Post a comment