Background:
I am developing an application in C# that uses Open XML to read information from several sheets belonging to an Excel file.
The Excel file (named Book1.xlsx) contains two sheets, one of these sheets has a column that shows date type values:
Example:
However, when using Open XML following the code provided on this page , the visible results in a GridView
(whose DataSource is a DataTable) , displaying the values of the "Period" column in numeric format:
Visible result in the GridView with autogenerated columns:
How do I convert these numbers to dates?
After consulting the Stack Overflow chat in Spanish, as well as continuing my search, I found this answer on Stack Overflow in English where it indicates that the numerical value is a value in OLE Automation format.
So, to convert the numeric value in OLE Automation format to DateTime, the method is used
FromOADate
:Example:
To convert a variable of type DateTime to OLE Automation, use the method
ToOADate
:Example:
You need to convert the obtained numbers into a date using
DateTime.FromOADate(value);
Try the following code in the method
GetCellValue
:You need to determine inside if it is a simple number or in this case if it is a date.
I suppose that when evaluating
if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
enters your Date data type and they parse it. You should do another if condition before this if where you cache the Date type and return it as such and to the next if (the one that parses int) you put an else if