Intro
This time, I will try getting Datetime values.
Get cell values
yyyy-MM-dd HH:mm:ss
Actual value
9/27/2020 11:40:30 PM
Display on Excel
2020/09/27 23:40:30
Result
Date(yyyy/MM/dd HH:mm:ss)_FormatString "2020/09/27 23:40:30"
Date(yyyy/MM/dd HH:mm:ss)_CachedValue "9/27/2020 11:40:30 PM"
Date(yyyy/MM/dd HH:mm:ss)_ValueCached ""
Date(yyyy/MM/dd HH:mm:ss)_DataType "DateTime"
Date(yyyy/MM/dd HH:mm:ss)_FormulaAi ""
Good :)
MM/dd
Actual value
9/22/2020
Display on Excel
9/22
Result
Date(MM/dd)_FormatString "9/22"
Date(MM/dd)_CachedValue "9/22/2020 12:00:00 AM"
Date(MM/dd)_ValueCached ""
Date(MM/dd)_DataType "DateTime"
Date(MM/dd)_FormulaAi ""
yyyy年MM月dd日(Japanese)
Actual value
9/22/2020
Display on Excel
2020年9月22日
Result
Date(JP)_FormatString "2020年9月22日"
Date(JP)_CachedValue "9/22/2020 12:00:00 AM"
Date(JP)_ValueCached ""
Date(JP)_DataType "DateTime"
Date(JP)_FormulaAi ""
Great!
Japanese Calendar
Actual value
9/22/2020
Display on Excel
令和2年9月22日
Result
JapaneseCalendar_FormatString "[$]ggge\"年\"m\"月\"d\"日\";@"
JapaneseCalendar_CachedValue "9/22/2020 12:00:00 AM"
JapaneseCalendar_ValueCached ""
JapaneseCalendar_DataType "DateTime"
JapaneseCalendar_FormulaAi ""
In this case, I can't use FormatString value.
But because the datatype is "DateTime", I can get value as DateTime.
...
if (cell.TryGetValue<DateTime>(out var dateValue))
{
dictionary.Add($"{name}_DateTime", dateValue.ToString());
}
...
Result
JapaneseCalendar_DateTime "9/22/2020 12:00:00 AM"
Date(Long)
Actual value
9/22/2020
Display on Excel
Tuesday, September 22, 2020
Result
Date(Long)_FormatString "44096"
Date(Long)_CachedValue "44096"
Date(Long)_ValueCached ""
Date(Long)_DataType "Number"
Date(Long)_FormulaAi ""
Because DataType is "Number", so the result is far from the value on Excel.
I think if the cell has alphabetic values like "September", "AM", etc. ClosedXML will treat as Number.
In this case, I can't get value by TryGetValue.
But if I force set DataType as DateTime, I can get DateTime value by TryGetValue.
...
var format = cell.Style.DateFormat.Format;
if(cell.DataType == XLDataType.Number &&
(format.Contains(@"mmmm") || format.Contains(@"AM/PM")))
{
cell.DataType = XLDataType.DateTime;
}
if (cell.TryGetValue<DateTime>(out var dateValue))
{
dictionary.Add($"{name}_DateTime", dateValue.ToString());
}
...
Result
Date(Long)_DateTime "9/22/2020 12:00:00 AM"
I want to get more better way.
Top comments (0)