RadGridView ExportToExcelML

[Al]

We are having two different issues with exporting using the Export To ExcelML.

First I'll show the code that we're using:

Me.RadGridView1.Columns("Date").ExcelExportType = DisplayFormatType.Custom           
Me.RadGridView1.Columns("Date").ExcelExportFormatString = " mm/dd/yyyy "           
Dim exporter As Export.ExportToExcelML = New Export.ExportToExcelML(Me.RadGridView1)           
exporter.FileExtension = "xls"        
exporter.HiddenColumnOption = HiddenOption.DoNotExport            
exporter.ExportVisualSettings = True         
exporter.RunExport(fileName)

The first issue. In the database we have the Date column as a DateTime datatype.  I have the Custom ExcelExportType set to only show "mm/dd/yyyy"  When it exports it will show up with that format - but the data still has the time included with it.  We need it to only export the date, not the time.


The second issue comes from trying to open the exported file.  Every time we try to open the .xls file, a warning comes up
"The file that you are trying to open, 'Filename.xls' is in a different format than specified by the file extension"
Everything still worked after that - but it is a little annoying have to go through that each time.

Please let me know of any suggestions.

Thanks.

[Ivan Petrov]

Hello Allen,

Thank you for writing.

The DateTime structure holds the time part no matter if you use it or not. To make excel not show it you can subtract the time part making a data with time part 0:00:00. When such a date is entered in excel, if the format string does not explicitly show the time part, it will not show the time. You can achieve this by subscribing for the exporter ExcelCellFormatting event and modifying the date value. Here is an example:

private void exporter_ExcelCellFormatting(object sender, ExcelCellFormattingEventArgs e)
{
  if (e.GridCellInfo.Value is DateTime)
  {
    DateTime dt = (DateTime)e.GridCellInfo.Value;
    e.ExcelCellElement.Data.DataItem = dt.Subtract(dt - dt.Date);
  }
}

On the second question. The message appears because the file extension of the exported file is xls while the inner structure is in the Excel ML format. The proper extension for a file with that inner structure is xml, but since xml is not associated by users with Excel and because Excel can open such a file (with inner structure and file extension not matching) we have decided that the message is price worth paying for a more widely recognized file extension and a nice looking file icon. You can change your file extension to xml and the message will not appear. The file that is produced when the extension is associated with excel and has an excel-XML icon. If, however, the user has explicitly associated xml files with another program the file will be treated as any other xml file by the OS.

I hope this is useful. Should you have further questions, I would be glad to help.
 
All the best,
Ivan Petrov
the Telerik team