Good afternoon,
I was wondering if anyone could help me with a GridView export issue I am having.
I am populating a GridView from an SQL query. This table has two DateTime columns that I am formatting after the DataBindingComplete to show the milliseconds portion of the DateTime field like so:
private void dgvTransTable_DataBindingComplete(object sender, GridViewBindingCompleteEventArgs e){ for (int j = 0; j < this.dgvTransTable.Columns.Count; j++) { if (this.dgvTransTable.Columns[j].GetType() == typeof(Telerik.WinControls.UI.GridViewDateTimeColumn)) { ((GridViewDateTimeColumn)this.dgvTransTable.Columns[j]).FormatString = "{0:MM/dd/yyyy hh:mm:ss.fff}"; } }}
Works great. Column displays just like I want. Now the problem is with the Excel Export. Normally with the format cell options, you can use .000 for milliseconds. I just can't seem to get it correct with the GridViewSpreadExport. This is what I am trying, note I've also tried using the .fff but it both cases, all that is written to the Excel cell is the short date/time string, and the formatting looks like I want, except either just the literal .fff or .000 show up in the visual cell.
I'd even be happy if someone knows how to make this field export as a straight text field as long as the string is formatted like the gridview cell.
foreach( GridViewDataColumn col in dgvTransTable.Columns ){ switch( col.DataType.Name ) { case nameof(DateTime): col.ExcelExportType = Telerik.WinControls.UI.Export.DisplayFormatType.Custom; col.ExcelExportFormatString = "MM/dd/yyyy hh:mm:ss.000"; break; }}
Hello, Michael,
Indeed, the date format is kept but the value for the milliseconds is lost when exported.
I have logged it in our feedback portal by making this thread public on your behalf. You can track its progress, subscribe for status changes and add your comments on the following link - feedback item.
I have also updated your Telerik points.
Currently, the possible solution that I can suggest is to use the CellFormating event of the exporter:
private void radButton1_Click(object sender, EventArgs e)
{
string fileName = @"..\..\" + DateTime.Now.ToLongTimeString().Replace(":", "_") + ".xlsx";
GridViewSpreadExport spreadExporter = new GridViewSpreadExport(this.radGridView1);
SpreadExportRenderer exportRenderer = new SpreadExportRenderer();
spreadExporter.CellFormatting+=spreadExporter_CellFormatting;
spreadExporter.RunExport(fileName, exportRenderer);
Process.Start(fileName);
}
private void spreadExporter_CellFormatting(object sender, Telerik.WinControls.Export.CellFormattingEventArgs e)
{
if (e.GridCellInfo.ColumnInfo.Name=="OrderDate")
{
CellSelection cellSelection = e.CellSelection as CellSelection;
DateTime dt;
if (cellSelection != null && DateTime.TryParse(e.GridCellInfo.Value+"",out dt))
{
cellSelection.SetValueAsText(((DateTime)e.GridCellInfo.Value).ToString("MM/dd/yyyy hh:mm:ss.fff", CultureInfo.GetCultureInfo("en-US")));
}
}
}
I hope this information helps. If you need any further assistance please don't hesitate to contact me.
Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik
Our thoughts here at Progress are with those affected by the outbreak.
Your date/time datasource does not have milliseconds in them though. The excel formatting is still just putting the literal string '.000' into the field and not displaying the actual time values.
Since I honestly do not know how to update the Jet mdb files to a date that includes milliseconds. I took your sample, added a button to the form for saving on demand instead of form_shown. And added the Custome format field so I could edit the radGridView after it was populated. like so:
((GridViewDateTimeColumn)this.radGridView1.Columns["OrderDate"]).Format = DateTimePickerFormat.Custom;
((GridViewDateTimeColumn)this.radGridView1.Columns["OrderDate"]).CustomFormat = "MM/dd/yyyy hh:mm:ss.fff";
If I update the values in the gridview to have any non-zero values for milliseconds. The export still exports the time with no millisecond value. I attached a zip of your modified project here as well.
Hello, Michael,
Indeed, the ExcelExportFormatString property of the column allows you to specify what format to be used when exporting the cells in this column.
Following the provided information, I was unable to reproduce the issue you are facing. Please refer to the below screenshot illustrating the behavior on my end with the specified version.
I have attached my sample project. Feel free to modify it in a way to reproduce the experienced issue and get back to me with it so I can investigate the precise case. Thank you in advance.
I am looking forward to your reply.
I am converting this forum thread into a support ticket in order to allow attachments. You can find the ticket in Your Telerik Account.
Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik
Our thoughts here at Progress are with those affected by the outbreak.