loading...

【.NET Core】【ClosedXML】Getting cell values 2

masanori_msl profile image Masui Masanori ・2 min read

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.

Discussion

pic
Editor guide