ExcelExportFormatString on GridViewDateTimeColumn does nothing

[Johan]

Ok,

Yet again, another issue that I am facing with the GridView control... :-)

Here are the steps for creating and populating my grid:

1. I construct my columns (mostly, GridViewTextBoxColumn, GridViewComboBoxColumn, GridViewDecimalColumn and GridViewDateTimeColumn) programmatically on form load. First of all, I construct these columns using xml data for the Fieldname properties etc.

Here is an example:

foreach (XmlNode mapNode in mappingXmlDoc.SelectSingeNode("Mappings"))
{
// Maps to GridViewDateTimeColumn
if (elementPrefix == "dt")
{
GridViewDateTimeColumn col = new GridViewDateTimeColumn();
col.Name = mapNode.Name;
col.FieldName = mapNode.Name;
col.HeaderText = friendlyName;
radGridView1.Columns.Add(col);
}

}

2. After I constructed these columns, I go ahead and initialize the grid by assigning it a datasource. The DataSet in turn is populated with xml data:

DataSet dsData = new DataSet();
dsData.ReadXml(xmlFile);

radGridView.DataSource = dsData.Tables[0];

The xml data that I read into the DataSet looks like this:

<assets>
<asset>
<tb_Desc></tb_Desc>
<dt_MoveDate><dt_MoveDate>
</asset>
</assets>

3. When setting following properties on the GridViewDateTimeColumn as follows:

foreach (GridViewDataColumn in radGridView1.Columns)
{
string[] strArr = col.FieldName.Split(new char[] {'_'});

// This way I know its a GridViewDateTimeColumn
if (strArr[0] == "dt")
{
((GridViewDateTimeColumn)col).ExcelExportType = DisplayFormatType.Custom;
((GridViewDateTimeColumn)col).ExcelExportFormatString = "dd.mm.YYYY";
((GridViewDateTimeColumn)col).FormatString = "{0:dd.mm.YYYY}";
}
}

nothing happens... I've tried to set the properties both before and after I set the gridview's datasource with no success.

Also, I'm using the ExportToExcelML class for exporting and I always end up with data for the date field looking like this: 1/27/2011 12:00:00 AM in both the grid and exported excel spreadsheet which is wrong.

Is there anything I'm doing wrong somewhere?

I appreciate your help.

Thanks

[Richard Slade]

Hi Ryno,

It looks like your format string is not a valid format. You'll need to set the format on the column and for the export. Remember as well that Excel will format the data itself and this can be changed within excel ..

Please try this exmaple that will format to a standard short date.

Designer File

namespace RadGridView_Export_C
{
    partial class Form1
    {
        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components;

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows Form Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.radButton1 = new Telerik.WinControls.UI.RadButton();
            this.radGridView1 = new Telerik.WinControls.UI.RadGridView();
            ((System.ComponentModel.ISupportInitialize)(this.radButton1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.radGridView1)).BeginInit();
            this.SuspendLayout();
            //
            // radButton1
            //
            this.radButton1.Location = new System.Drawing.Point(224, 295);
            this.radButton1.Name = "radButton1";
            this.radButton1.Size = new System.Drawing.Size(130, 24);
            this.radButton1.TabIndex = 0;
            this.radButton1.Text = "Export";
            this.radButton1.Click += new System.EventHandler(this.radButton1_Click);
            //
            // radGridView1
            //
            this.radGridView1.Location = new System.Drawing.Point(13, 13);
            this.radGridView1.Name = "radGridView1";
            this.radGridView1.Size = new System.Drawing.Size(341, 265);
            this.radGridView1.TabIndex = 1;
            this.radGridView1.Text = "radGridView1";
            //
            // Form1
            //
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(366, 331);
            this.Controls.Add(this.radGridView1);
            this.Controls.Add(this.radButton1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.Form1_Load);
            ((System.ComponentModel.ISupportInitialize)(this.radButton1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.radGridView1)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private Telerik.WinControls.UI.RadButton radButton1;
        private Telerik.WinControls.UI.RadGridView radGridView1;
    }
}

Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Telerik.WinControls.UI;
using Telerik.WinControls.Data;
using Telerik.WinControls.UI.Export;

namespace RadGridView_Export_C
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            GridViewDateTimeColumn dateColumn = new GridViewDateTimeColumn();
            dateColumn.Name = "MyDate";
            dateColumn.HeaderText = "MyDate";
            dateColumn.FieldName = "MyDate";
            dateColumn.Format = DateTimePickerFormat.Custom;
            dateColumn.FormatString = "{0:d}";
            dateColumn.ExcelExportFormatString = "{0:d}";
            dateColumn.NullValue = DateTime.Today;
            this.radGridView1.Columns.Add(dateColumn);

            this.radGridView1.Rows.Add(DateTime.Now);
            this.radGridView1.Rows.Add(DateTime.Now.AddMinutes(10));
            this.radGridView1.Rows.Add(DateTime.Now.AddSeconds(100));

        }

        private void radButton1_Click(object sender, EventArgs e)
        {
            ExportToExcelML exporter = new ExportToExcelML(this.radGridView1);
            exporter.RunExport(@"File Name Goes here");
        }
    }
}

There is also an MSDN link here on custom date and time format strings
Regards,
Richard

[Richard Slade]

Hi Ryno,

Did this help? If so please remember to mark as answer.
All the best
Richard

[Johan]

Hi. I wrote a follow up reply solving my related issues I had with formatting etc. I hope this helps anyone experiencing similar problems.