【答客問】ClosedXML 日期資料解析測試
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^