Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks, I am trying to print some data onto excel file from my perl script.
Now when i am trying to print column containing date, excel consider it as a sting not the date field.
Is there any way to format a cell data in date format. Is there a x:date tag or something like that?.
Thanks.

Replies are listed 'Best First'.
Re: Date column in excel
by hakkr (Chaplain) on Jun 08, 2004 at 11:39 UTC
    It depends what module you are using to write the excel file with. For the excellent Spreadsheet::WriteExcel module you would say 
    
$format->set_num_format('mmm d yyyy hh:mm AM/PM');
$worksheet->write( $date , $format);

    [download]
[reply]
[d/l]
      I am not using any module as such i am using this code in html
      xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet
      and using x:str to convert into string is there anyway without using any module where i could specify a date cell.
      Is there any way where i could do that without changing much of my code.
      Thanks
[reply]

        If I ever need to figure out how to do this, I start by creating a simple Excel sheet, do "Save as HTML" or whatever, and see how Excel has saved it.

        Just did a quick test and it looks like the HTML format is:

        <td height=17 class=xl24 align=right style='height:12.75pt'
    x:num="38018">01/02/2004</td>

        [download]

        So you probably need to convert the date into numeric form (shouldn't be too difficult - but you'll need to check Excel's documentation to find its epoch, as that will most likely not be the same as perl's) for the x:num attribute...

        Update: I should add that if you're doing lots of this and you want it to be robust, you ought to look into a ready-made solution, such as Spreadsheet::WriteExcel. I've only ever needed to do this very occasionally, and so I've never actually looked properly at what modules are available...

        s^^unp(;75N=&9I<V@`ack(u,^;s|\(.+\`|"$`$'\"$&\"\)"|ee;/m.+h/&&print$&;
[reply]
[d/l]
[select]
        well I'd say a date is more a number than a string at least it will be to excel internally
[reply]
Re: Date column in excel
by jmcnamara (Monsignor) on Jun 08, 2004 at 23:28 UTC
[reply]
[d/l]

Back to Seekers of Perl Wisdom