Free Support Forum - aspose.com

Question concerning DateTime column formatting in Excel using Aspose Excel

Hello,

I have a question concerning the DateTime formatting of columns in Excel documents created with Aspose (v3.0.1.4 & v3.5.0.1).

Background:
We have an ASP.NET web application with which users can download a generated Excel document, containing, amongst other data, columns with DateTime fields.
The application runs on a Windows 2003 Server production machine (English/US OS install), but with Dutch regional settings. In the application the columns that contain datetime fields are explicitly set to display format 22 (m/d/yy h:mm).
The Excel is filled from a dataset, that has datetime columns with the dd/mm/yyyy hh:mm datetime format, we also want the Excel columns to have this datetime format.

Questions:
1.) We want the datetime fields to be formatted in the dd/mm/yyyy hh:mm style. Is there a display format that forces this style?
2.) With the current display format (22), on our development machines (which have Dutch OS and regional settings), we get the desired dd/mm/yyyy hh:mm output in our Excel documents.
When we run it on the production machine however, the formatting is different; as long as the 'days' segment of the datetime in the source dataset is lower than 13, the format is mm/dd/yyyy hh:mm. When it is 13 or higher
the datetime formatting switches to the dd/mm/yyyy hh:mm format. My guess is that Aspose detects an overflow and switches formatting. Example:

8-10-2007 9:40 -> mm/dd/yyyy format
8-10-2007 9:40
13-08-2007 11:35 -> dd/mm/yyyy format
13-08-2007 11:35

My question is, how does Aspose decide which formatting to use? The explicit formatting style 'm/d/yyyy hh:mm' doesn't seem to force itself on the Dutch OS development machine (it gives us the desired dd/mm/yyyy hh:mm format), and behaves
strangely on the English/US production machine (switching formatting when there is an overflow, and apparantly not adhering to the regional settings, which are set to Dutch).

If anyone can help us find a solution we would be very grateful.

Thanks in advance.

Hi,

Thanks for considering Aspose.

It looks strange to us. Since you are using some older versions of Aspose.Cells for which I am not pretty much sure. Could you try to utilize some coding lines if it works fine. e.g., workbook.Language = CountryCode.Germany; and workbook.Region = CountryCode.Germany; Also you may try to utilize the ........Style.Custom to your required format like "dd/mm/yyyy hh:mm" or other. If the problem still persists could you give a try with the latest release of Aspose.Cells (4.4.0.0) downloading @: http://www.aspose.com/Community/Files/51/aspose.cells/default.aspx

Thank you.

If you use Style.Number to set a datetime format, the format will show differently on different OS. That's the routine of MS Excel.

To make it consistent on different OS, please use Style.Custom property to explicitly set the date format.