This is a migrated thread and some comments may be shown as answers.

ExcelExportFormatString on GridViewDateTimeColumn does nothing

3 Answers 107 Views
GridView
This is a migrated thread and some comments may be shown as answers.
Johan
Top achievements
Rank 1
Johan asked on 30 Jan 2011, 07:11 PM
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

3 Answers, 1 is accepted

Sort by
0
Richard Slade
Top achievements
Rank 2
answered on 30 Jan 2011, 07:58 PM
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
0
Richard Slade
Top achievements
Rank 2
answered on 01 Feb 2011, 02:27 PM
Hi Ryno,

Did this help? If so please remember to mark as answer.
All the best
Richard
0
Johan
Top achievements
Rank 1
answered on 07 Feb 2011, 06:03 PM
Hi. I wrote a follow up reply solving my related issues I had with formatting etc. I hope this helps anyone experiencing similar problems.
Tags
GridView
Asked by
Johan
Top achievements
Rank 1
Answers by
Richard Slade
Top achievements
Rank 2
Johan
Top achievements
Rank 1
Share this question
or