DEV Community

Masui Masanori
Masui Masanori

Posted on

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

#csharp #closedxml #dotnetcore

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
Enter fullscreen mode Exit fullscreen mode

Display on Excel 

2020/09/27 23:40:30
Enter fullscreen mode Exit fullscreen mode

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 ""
Enter fullscreen mode Exit fullscreen mode

Good :)

MM/dd

Actual value 

9/22/2020
Enter fullscreen mode Exit fullscreen mode

Display on Excel 

9/22
Enter fullscreen mode Exit fullscreen mode

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   ""
Enter fullscreen mode Exit fullscreen mode

yyyy年MM月dd日(Japanese)

Actual value 

9/22/2020
Enter fullscreen mode Exit fullscreen mode

Display on Excel 

2020年9月22日
Enter fullscreen mode Exit fullscreen mode

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  ""
Enter fullscreen mode Exit fullscreen mode

Great!

Japanese Calendar

Actual value 

9/22/2020
Enter fullscreen mode Exit fullscreen mode

Display on Excel 

令和2年9月22日
Enter fullscreen mode Exit fullscreen mode

Result 

JapaneseCalendar_FormatString   "[$]ggge\"年\"m\"月\"d\"日\";@"
JapaneseCalendar_CachedValue    "9/22/2020 12:00:00 AM"
JapaneseCalendar_ValueCached    ""
JapaneseCalendar_DataType   "DateTime"
JapaneseCalendar_FormulaAi  ""
Enter fullscreen mode Exit fullscreen mode

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());
            }
...
Enter fullscreen mode Exit fullscreen mode

Result 

JapaneseCalendar_DateTime   "9/22/2020 12:00:00 AM"
Enter fullscreen mode Exit fullscreen mode

Date(Long)

Actual value 

9/22/2020
Enter fullscreen mode Exit fullscreen mode

Display on Excel 

Tuesday, September 22, 2020
Enter fullscreen mode Exit fullscreen mode

Result 

Date(Long)_FormatString "44096"
Date(Long)_CachedValue  "44096"
Date(Long)_ValueCached  ""
Date(Long)_DataType "Number"
Date(Long)_FormulaAi    ""
Enter fullscreen mode Exit fullscreen mode

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());
            }
...
Enter fullscreen mode Exit fullscreen mode

Result 

Date(Long)_DateTime "9/22/2020 12:00:00 AM"
Enter fullscreen mode Exit fullscreen mode

I want to get more better way.

Top comments (0)