Date and time format

Jan 19, 2010 at 2:04 PM

I have a Excel Table with one column for Short Date and one for time values. The date column is populated with DateTime values and the time column is populated with TimeSpan values. Neither of these is formatted as I've formatted them in my template. Is there a way to fix this?

 

Coordinator
Jan 19, 2010 at 2:41 PM

Assuming I have understood your question properly, the problem is that the required date and time formatting is not being correctly applied to data injected into a table?

If so, the solution is to apply the formatting to the template worksheet column behind the table, not to the cells within the table. To do this in Excel, simply click the worksheet column header so that the entire column is highlighted, then right-click and apply the required formatting. When the worksheet expands because new rows are inserted by ExtremeML, the column formatting will be automatically applied to all new cells in the column.

Jan 19, 2010 at 2:47 PM

Thanks for the fast reply!

That's exactly what I've done but I still get an unformatted value in Excel. Dates is integers and timespans are decimal number.

Talkning about TimeSpan... You need to set the CurrentCulture on the thread to en-US to get decimal number. Otherwise Excel thinks the workbook is corrupt and just extracts the value as a string.

 

Coordinator
Jan 19, 2010 at 3:25 PM

I am a bit confused by both of these comments!

The sample code (included with the source distribution) contains an example that uses this technique, as demonstrated in the following tutorial:

http://www.extrememl.com/index.php/knowledge-center/tutorials/working-with-excel-tables/populating-a-table-from-an-array

If you could send me a copy of your template file and the code used to fill it, I'll take a look and advise you further.

Regarding the culture issue, I recently added some patches (supplied by a European user) to fix problems of this nature. The goal of the patches was to ensure that all conversion is carried out using the invariant culture. It's possible that we missed something, so I'll look again.

Jan 19, 2010 at 3:54 PM

I read your answer to quickly. Now that I applied the formatting to the entire sheet column it works. Not the most elegant solution, but it works. :)

 

Coordinator
Jan 19, 2010 at 4:07 PM

I understand that this approach has its critics. It is also likely to need improvement when implementing the enhancements proposed by andrej_ ...

http://extrememl.codeplex.com/Thread/View.aspx?ThreadId=76826

... but you should bear in mind that ExtremeML is currently only a proof-of-concept, so it is likely to undergo various improvements as a result of user feedback.