【答客問】ClosedXML 日期資料解析測試

網友 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

歡迎推文分享:
Published 23 September 2017 04:29 PM 由 Jeffrey
Filed under:
Views: 2,767



意見

# Danny said on 01 October, 2017 08:33 PM

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

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<September 2017>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication