Time duration using Excel::Writer::XLSX

dr.jekyllandme has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I am trying to write a column on duration( hh:mm:ss ) to an excel file. My only problem is figuring out what format I should use. I am using the Excel::Writer::XLSX module. When I did a quick web search, I found that the correct way to define a duration is to use []( http://www.myonlinetraininghub.com/calculating-time-in-excel ). This works correctly when I do this in excel. But I need to use Perl to generate my spreadsheet because I have lots of data. This is a snippet of my code:

    my $workbook = Excel::Writer::XLSX->new( 'build_results.xlsx' );
    my $worksheet = $workbook->add_worksheet();
    
    my $format = $workbook->add_format( num_format => '[hh]:mm:ss' );
    $worksheet->write_date_time( "A$col", '12:20:12', $format ); # i n
+eed this to be considered 12 hours 20 mins and 12 secs.
[download]

I am new to Excel::Writer::XLSX and I am not even sure If I should be using write_date_time. If anyone can enlighten me, it would be greatly appreciated. Thanks.

Comment on Time duration using Excel::Writer::XLSX Download Code

Replies are listed 'Best First'.
Re: Time duration using Excel::Writer::XLSX by Anonymous Monk on Dec 06, 2012 at 01:08 UTC
Hi, According to the docs, you need to put 'T' in front os the time string you are sending to the sub. 'T12:20:12' rather than '12:20:12'. J.C.	[reply]
Re: Time duration using Excel::Writer::XLSX by rpnoble419 (Pilgrim) on Dec 07, 2012 at 01:09 UTC
I create reports using this module all the time. I create a text format for the date and time if you are not going to use it in a formula. This forces excel to display as I want the date and time not as Excel wants when the use has set the local date and time format on their computer. The code below is from my database while loop.. `$worksheet1->write_string($row, $col++,$data->{$dataElement});` [download]	[reply] [d/l]