Worksheet ImportData with dates become custom

[Pieter van Kampen]

Hi,

To import different ranges of data, I have made a generic formatting and importing method. However, when it sets the Column format to "yyyy-mm-dd", it becomes a Custom format in Excel. In order for it to import properly in SPSS, I need it to be a DateTime format. 

 public void PrepExcel<T>(List<T> allRows, List<string> headers, int column)

        {

            

            //Apply BodyStyle

            var properties = typeof(T).GetProperties();

            if (allRows.Count > 0) _worksheet.Range[2, column, allRows.Count + 1, column + properties.Length - 1].CellStyle = _bodyStyle;

            // apply column formatting

            for (var i = 0; i < properties.Length; i++)

            {

                var property = properties[i];

                _worksheet.Range[1, column + i].Text = headers[i];

                if (property.PropertyType == typeof(int) || property.PropertyType == typeof(int?))

                    _worksheet.Columns[i + column - 1].NumberFormat = "0";

                if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))

                    _worksheet.Columns[i + column - 1].NumberFormat = "yyyy-mm-dd";  //this gets applied as Custom Format, not as Date Time

                if (property.Name.IndexOf("Credit") >= 0 || property.Name.IndexOf("Prijs") >= 0)

                    _worksheet.Columns[i + column - 1].NumberFormat = "€#,##0.00";

            }

            //Import data to worksheet.

            _worksheet.ImportData(allRows, 2, column, false);

        }

The above is used with a List of items, of which at least one is a DateTime. The data gets imported in Excel as 2020-09-05 as expected, but as mentioned as Custom format, which is not recognized by SPSS.

Attached a small subset of the output data file. It does not contain sensitive data . It also contains a screenshot of the format setting Date, with Type 2012-03-14 that does what I need. 
But I don't know how to achieve that. 

Attachment: syncfusion_date_format_custom_110469b3.zip

[Shamini Kiruba Sobers]

Hi Pieter, 

Greetings from Syncfusion. 

Sorry, XlsIO can set the format only as custom if you set any date format using NumberFormat property other than the built-in date format of your system. If you set the date "2020-02-26" using Text property, it will be in General format. This is the behavior of XlsIO. 

Kindly ensure that your system date format matches this “yyyy-mm-dd” format to get it as Date instead of custom format even if you set the value with General format. 

Regards, 

Shamini 

[Pieter van Kampen]

Thanks,

so if I tried using your clue:

  public void PrepExcel<T>(List<T> allRows, List<string> headers, int column)
        {
            
            //Apply BodyStyle
            var properties = typeof(T).GetProperties();
            if (allRows.Count > 0) _worksheet.Range[2, column, allRows.Count + 1, column + properties.Length - 1].CellStyle = _bodyStyle;

            // apply column formatting
            for (var i = 0; i < properties.Length; i++)
            {
                var property = properties[i];
                _worksheet.Range[1, column + i].Text = headers[i];
                if (property.PropertyType == typeof(int) || property.PropertyType == typeof(int?))
                    _worksheet.Columns[i + column - 1].NumberFormat = "0";
                //if (property.PropertyType == typeof(DateTime) || property.PropertyType == typeof(DateTime?))
                //    _worksheet.Columns[i + column - 1].NumberFormat = "yyyy-mm-dd";  //this gets applied as Custom Format, not as Date Time
                if (property.Name.IndexOf("Credit") >= 0 || property.Name.IndexOf("Prijs") >= 0)
                    _worksheet.Columns[i + column - 1].NumberFormat = "€#,##0.00";
            }

            System.Threading.Thread.CurrentThread.CurrentCulture = 
                new System.Globalization.CultureInfo("ja-JP");
            //Import data to worksheet.
            _worksheet.ImportData(allRows, 2, column, false);
        }

the ja-JP culture set the system to natively support the yyyy-mm-dd format, I have not set it using the NumberFormat . 
However, it still comes out a Custom. 

Any sugestions?

[Shamini Kiruba Sobers]

Hi Pieter, 

Thanks for the update. 

We have tried to set the date with “ja-JP” culture whose default short format date is “yyyy-mm-dd” and we got the output in Date format. Kindly look into the following video screenshot that shows the same. 

https://www.syncfusion.com/downloads/support/forum/157589/ze/SCREEN~11708103745 

Please let us know if it helps. 

Regards, 

Shamini