Hello Infragistics Support,

I have taken over an ASP.NET web project for an application which exports data
from a SQL Server to Excel Spreadsheets using Infragistics for ASP.Net.

The technique which is currently used is that GridViews are bound to the
SQL Data Source (sourced from a stored procedure).
The content of the GridViews is then exported to an Infragistics.Excel.Worksheet.

Here a short extract of the code:

Infragistics.Excel.Workbook exportWB = new Infragistics.Excel.Workbook();       //creates workbook        
Infragistics.Excel.Worksheet CRWrks = exportWB.Worksheets.Add("CR");  //creates worksheet
this.UltraWebGridExcelExporter1.Export(GridView_CR, CRWrks);   //Export of GrdiView data to worksheet
....

//the download part of the web application
Guid guid = Guid.NewGuid();
string exportFileName = "DataExport_" + guid.ToString();
UltraWebGridExcelExporter1.DownloadName = exportFileName;
exportWB.Save(Server.MapPath(@"..\Exports") + @"\" + exportFileName);
....

The Spreadsheet is sent to the client browser without problems and
the data are filled into the worksheets, however the problem we have can be described as follows:

The data in the SQL database GridView contain dates in the format "DD/MM/YYYY".
The user wishes to sort the data in the Spreadsheet by date chronologically.
The exported Excel Spreadsheet (*.xls) displays the dates only as cell format "General".
Even when a manual change of the column's cell format to "Date" is applied, the
column cannot be sorted chronologically.
Excel still sees it as a string/Text and can only sort it alphanumerically.
Various attempts to "value"-copy data into new columns in the same workbook
and to change date formats (as delivered from SQL Server & complying to regional settings)
did not solve the problem.

Only copying the data out to a text editor and pasting the data back into a
new workbook with a new worksheet, allows changing the date columns to "Date"
whereas the sort filter works fine now.

This process anyway is absolutely inconvenient for the user and cannot be accepted.

Questions:
Is this problem known and does a solution exist?
How can we force on creation of the worksheet that the column-cell format is set to Date? (Would this solve the problem anyway?)

Thank you very much in advance for your support

Kind regards,

CD