DEV Community

Masui Masanori
Masui Masanori

Posted on

1

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

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.

Image of Datadog

How to Diagram Your Cloud Architecture

Cloud architecture diagrams provide critical visibility into the resources in your environment and how they’re connected. In our latest eBook, AWS Solution Architects Jason Mimick and James Wenzel walk through best practices on how to build effective and professional diagrams.

Download the Free eBook

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs