Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re^3: Not sure why perl-generated spreadsheet has formatting issues

by Athanasius (Abbot)
on Sep 18, 2012 at 09:06 UTC ( #994243=note: print w/ replies, xml ) Need Help??


in reply to Re^2: Not sure why perl-generated spreadsheet has formatting issues
in thread Not sure why perl-generated spreadsheet has formatting issues

Hello poopay,

I can think of only two ways to write formatted dates into an Excel spreadsheet using Excel::Writer::XLSX:

  1. The ‘correct’ way is to convert the date string into Excel’s internal date format, namely a floating-point number. For example:

    use Excel::Writer::XLSX::Utility; ... my $ndate = xl_parse_date($date); $worksheet->write_date_time($row, $col, $ndate, $format);

    (Note that Excel::Writer::XLSX::Utility is installed along with Excel::Writer::XLSX, but its use may require the further installation of Date::Manip.)

    Unfortunately, application of the appropriate $format here leads to the problem you describe: the formatting is present, but is not applied to the date until the user double-clicks the cell and presses Enter. I don’t know why this is, and I haven’t found any way to make the formatting appear directly. Possibly a bug in Excel::Writer::XLSX?

  2. Bypass Excel’s formatting by entering the date as a string. This is the method I suggested in my previous post. You say, “I’ve tried the code but the output is still the same.” Well, I don’t see how that’s possible, since a date written as a string is just a string, as far as Excel is concerned. The code I gave is a kludge, but it works correctly for me, as I showed.

Is it possible for me to send you the output file I was able to generate?

No, but I don’t see how that would help anyway. It would be much more useful for you to show the code you used, and just describe the resulting output.

...if I generate a pivot table...

According to the documentation, Excel::Writer::XLSX does not (yet) support the generation of pivot tables — this is mentioned in the TO DO section. So, how are you generating pivot tables?

Perhaps the best advice I can give is that you investigate alternative CPAN modules for writing Excel. :-(

Athanasius <°(((><contra mundum


Comment on Re^3: Not sure why perl-generated spreadsheet has formatting issues
Download Code

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://994243]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (13)
As of 2015-07-06 21:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (83 votes), past polls