I would like to export a grid content to excel using the ultragrid excel exporter.
One of my column is a datetime, but the excel column needs to be a small date.
I am aware I can change the format of the excel column during the export ( http://www.infragistics.com/community/forums/t/24639.aspx ) but changing the format doesn't change the value.
Is there any way to change the values inside the excel column to be small dates using the ultragrid excel exporter ?
Hello Matthieu,
You can handle the exporter's CellExporting event to convert the datetimes to short dates using ToShortDateString method.
eg code.
void ultraGridExcelExporter1_CellExporting(object sender, Infragistics.Win.UltraWinGrid.ExcelExport.CellExportingEventArgs e) { if (e.Value != e.GridColumn.Header.Caption) { e.Value = ((DateTime)e.Value).ToShortDateString(); } }
Let me know if you have any questions regarding this matter.
Hi Michael,
Thanks for your answer, I reworked your proposed solution to something that can handle a more complex file with different types of column :
private void ultraGridExcelExporter_CellExporting(object sender, CellExportingEventArgs e) { if (e.Value != null) { var strValue = e.Value.ToString(); if (strValue != e.GridColumn.Header.Caption) { DateTime date; if (DateTime.TryParse(strValue, out date)) { e.Value = date.ToShortDateString(); } } } }
But the issue remains as the value still holds all the data concerning the time, even after I set the e.Value with the Short Date String. (Column Période is a DateTime in the DataSource, Column PériodeStr is a String)
Watching the e.value in Debug let me see that only the Date part has been modified by this operation.
I still get a few cast errors with this method, that I did not spend time tracking down, as the solution doesn't seem to give a correct result anyway.
I kept active the code to format the columns, as I need it for my other columns :
private void ultraGridExcelExporter_InitializeColumn(object sender, InitializeColumnEventArgs e) { if (((Option & ExportToExcelOption.WithFormatting) == ExportToExcelOption.WithFormatting) && !string.IsNullOrEmpty(e.Column.Format)) { e.ExcelFormatStr = e.Column.Format; } }
I'm using Infragistics controls v. 10.3.20103.1000, so the behavior may be different in newer version ?
I ended up hacking together my own excel exporter with epplus ( http://epplus.codeplex.com/ ) , which gives me more control on the result, and the type mapping between .net types and excel types is more accurate. That buys me time to test other possibilities with Infragistics Excel Exporter, if there are any other to achieve the desired result.