hi， I exported UltraGrid data to excel file. UltraGrid contains 3 columns containing datetime. One is short date such as 22/4/2009,the other two are long time such as 18:00:00. I wrote this code to export to excel file: me.UltraGridExcelExporter1.Export(me.UltraGrid1,"C:\\test.xls")when I open the excel file, the date column will show as "22/4/2009 12:00:00", and the time column will show as "1/0/1900 18:00:00". what should I do to make this correct?
Hi,
There's no such thing as a short date data type in DotNet. So what you probably have in your grid is a column of DateTime objects and either you or the grid is formatting the column to just show the date.
When you export to Excel, the DotNet date formats are not the same as the ones in Excel, so the grid cannot export the format along with the data. The UltraGridExcelExporter has an event called InitializeColumn to allow you to get the format from the grid column and apply an appropriate format to the Excel cells. A lot of the time you can simply set the ExcelFormatStr to the grid's format string.
Hi Mike,
I just found this thread, because I was looking for a solution for some similar problems:
According to your advise, I added this code to the ExcelExporter_InitializeColumn event:
Select Case e.FrameworkFormatStr Case "dd-MM-yyyy" e.ExcelFormatStr = "dd-MM-yyyy" Case "HH:mm" e.ExcelFormatStr = "HH:mm" End Select
That does the trick
What I am wondering is this: I guess, we, the users, expect the column formatting in an exported Excel file - by default - to be the same as the column formatting in an UltraGrid. Therefore I do not understand why we have to add addition code to accomplish this.
Additional question:
After reading my statement, that I expect Excel column formatting always being the same as Ultra Grid column formatting, is there any reason why I should not use this line of code in the ExcelExporter_InitializeColumn event:
e.ExcelFormatStr = e.FrameworkFormatStr
Thanks in advance,
Willem van den BroekThe Netherlands
Hi William,
Yes, the line of code you have here will work in most cases.
The reason the grid does not simply do this for you automatically is that DotNet Framework format strings are not always the same as Excel Format strings. There are strings you could use in DotNet that would not work in Excel and there's really no way for the grid to detect this.
So if the grid automatically set the format on the Excel cells, there is a possibility that the Excel worksheet would appear with an invalid format and the data would appear corrupt.
So we had to choose between the Excel sheet not containing any formatting, or the Excel sheet possibly containing a bad format which could result in the appearance of corrupted data or possibly even an error. We chose to make sure the data is always correct and not formatted, and to allow you, the developer to translate the format as needed.
Hi Boris, Mike,
Thanks for the thorough explanation. Long time ago I developed a generic form, I call (passing a WinGrid) to let the end user export the grid to XLS, PDF or XPS. Since I do not use very exotic formatting strings, I'll stick with e.ExcelFormatStr = e.FrameworkFormatStr in the InitializeColumn event for the time being.
Thanks again and best regards,
Willem