MiniExcel Custom formatted Datetime is saved as string

Excel Type

[X] XLSX
[ ] XLSM
[ ] CSV
[ ] OTHER

Upload Excel File

MiniExcel Version

1.26.5

Description

When providing a custom format for a datetime it is saved as a string instead of a number making it impractical to work with in Excel.

new DynamicExcelColumn("HourUTC") {Width=20, Index = 0, Format = "yyyy-MM-dd hh:mm"}

Aug 26 '22 07:08 MadsSvejstrup

Been noticed, a cheap workaround I've come up with is to leverage a template forcing a date format for the relevant cells, but writing numbers converting the DateTime / DateOnly to relevant Excel serial number. I didn't bother Google and asked ChatGpt:

Question:

How excel converts the date 01/01/2023 to the number 44927

Answer:

Excel stores dates as serial numbers, with the number representing the number of days since January 1, 1900 (this date is considered to be the serial number 1). This system makes it easier to perform calculations with dates, such as finding the difference between two dates or adding a number of days to a given date.
[...]

I'm using F# to impl the conversion stuff:

[<RequireQualifiedAccess>]
module Excel =
    // Define a function to convert a DateOnly value to an Excel date serial number
    let dateToExcelSerial (date: DateOnly) : int =

        // Calculate the number of days since January 1, 1900, considering that it's 1-indexed, hence the + 1
        let daysSince1900 = (date.DayNumber - DateOnly(1900, 1, 1).DayNumber) + 1

        // Check if the date is after the incorrectly assumed Excel leap year (February 29, 1900)
        if date > DateOnly(1900, 2, 28) then
            // Add one day to account for Excel's incorrect leap year assumption
            daysSince1900 + 1
        else
            daysSince1900

Note: or just use the DateTime.ToOADate() method right from the std library https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tooadate?view=net-7.0

A bit of explanation about where the problem stems from in the source code. When MiniExcel hits the WriteSheetXml method, and, in particular, this chunk:

else if (type == typeof(DateTime))
{
    cellValueStr = ConvertToDateTimeString(propInfo, cellValue);
}
// ...

rowXml.Replace(key, cellValueStr);

If you have say a template something like below:

In my use case, the rowXml above will show as:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<row collapsed="false" customFormat="false" customHeight="false" hidden="false" ht="12.8" outlineLevel="0" r="10">
    <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="A10" s="8" t="str">
        <v>{{accrual.Empty}}</v>
    </c>
    <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="B10" s="10" t="str">
        <v>Meow</v>
    </c>
    <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="C10" s="10" t="str">
        <v>AD</v>
    </c>
    <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="D10" s="11" t="str">
        <v>{{accrual.Date}}</v>
    </c>
    <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="E10" s="11" t="str">
        <v>{{accrual.Date}}</v>
    </c>
    <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="F10" s="10" t="str">
        <v>{{accrual.HeaderText}}</v>
    </c>
    <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="G10" s="10" t="str">
        <v>{{accrual.Currency}}</v>
    </c>
    <c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="H10" s="11"/>
</row>

as you can see, this is where we have the issue, the t="str":

<c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="D10" s="11" t="str">
    <v>{{accrual.Date}}</v>
</c>
<c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="E10" s="11" t="str">
    <v>{{accrual.Date}}</v>
</c>

and post replacement looks like:

<c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="D10" s="11" t="str">
    <v>2023/01/01</v>
</c>
<c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="E10" s="11" t="str">
    <v>2023/01/01</v>
</c>

Will show up with a single quotation mark prefix once displayed in Excel.

The underlying issue, imho, is a design flaw, there is no proper type matching between:

the Excel format of a cell on one hand which can be effectively numeric
and, the data you wanna input that can be deemed as numeric but the function ConvertToDateTimeString called when bumping into a DateTime field returns a string and not a number (i.e., Int32) as it should.

Going back to my workaround, changing the mapping (leveraging a Int32 instead of DateTime or DateOnly) results in this instead in rowXml:

<c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="D10" s="11" t="n">
    <v>{{accrual.Date}}</v>
</c>
<c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="E10" s="11" t="n">
    <v>{{accrual.Date}}</v>
</c>

Which will be properly interpreted as a date in Excel (because of the cell format) if you use the proper number (The date 2023/01/01 is equivalent to the 44927 Excel serial date-number-date-representation-thingy), e.g.,

<c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="D10" s="11" t="n">
    <v>44927</v>
</c>
<c xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" r="E10" s="11" t="n">
    <v>44927</v>
</c>

I think someone has already noticed that flaw because there is a TODO in the relevant code section:

private static string ConvertToDateTimeString(KeyValuePair<string, PropInfo> propInfo, object cellValue)
{
    //TODO:c.SetAttribute("t", "d"); and custom format
//[...]
}

Apr 10 '23 10:04 natalie-o-perret

The reason is because there is a first step in the impl which is all about changing the type xml attribute accordingly to the type mapping:

private void UpdateDimensionAndGetRowsInfo(Dictionary<string, object> inputMaps, ref XmlDocument doc, ref XmlNodeList rows, bool changeRowIndex = true)
// [...]
  switch (isMultiMatch)
  {
      case true:
          c.SetAttribute("t", "str");
          break;
      default:
      {
          if (TypeHelper.IsNumericType(type))
          {
              c.SetAttribute("t", "n");
          }
          else switch (Type.GetTypeCode(type))
          {
              case TypeCode.Boolean:
                  c.SetAttribute("t", "b");
                  break;
              case TypeCode.DateTime:
                  c.SetAttribute("t", "str");
                  break;
          }

          break;
      }
  }
// [...]

I've changed:

case TypeCode.DateTime: 
    c.SetAttribute("t", "str");

and

private static string ConvertToDateTimeString(KeyValuePair<string, PropInfo> propInfo, object cellValue)
{
    //TODO:c.SetAttribute("t", "d"); and custom format
    string format;
    if (propInfo.Value.PropertyInfo == null)
    {
        format = "yyyy-MM-dd HH:mm:ss";
    }
    else
    {
        format = propInfo.Value.PropertyInfo.GetAttributeValue((ExcelFormatAttribute x) => x.Format)
                     ?? propInfo.Value.PropertyInfo.GetAttributeValue((ExcelColumnAttribute x) => x.Format)
                     ?? "yyyy-MM-dd HH:mm:ss";
    }

    var cellValueStr = (cellValue as DateTime?)?.ToString(format);
    return cellValueStr;
}

to:

case TypeCode.DateTime: 
    c.SetAttribute("t", "n");

and

private static string ConvertToDateTimeString(object cellValue)
        => (cellValue as DateTime?)?.ToOADate().ToString(CultureInfo.InvariantCulture);

Tbs, it probably brings its own set of regressions, though. Really depends how the library is being used.

Apr 10 '23 11:04 natalie-o-perret

thanks for DancePanda42 pr,the question be solved please use new DynamicExcelColumn("HourUTC") {Width=20, Index = 0, Format = "yyyy-MM-dd hh:mm"}

Jun 13 '24 14:06 jiaguangli