Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

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

by poopay (Initiate)
on Sep 17, 2012 at 03:35 UTC ( #993950=note: print w/ replies, xml ) Need Help??


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

Hi Athanasius, I've tried the code but the output is still the same. Is it possible for me to send you the output file I was able to generate?

If I highlight the cell, it shows the formatting I want. But if I generate a pivot table from that, it will still cause me problems when I try to group the information.

The only solution for me at this time is to double-click each cell, then press "Enter" in order for the data to be displayed and treated correctly :(


Comment on Re^2: Not sure why perl-generated spreadsheet has formatting issues
Re^3: Not sure why perl-generated spreadsheet has formatting issues
by Athanasius (Abbot) on Sep 18, 2012 at 09:06 UTC

    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

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others perusing the Monastery: (13)
As of 2015-07-03 11:15 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 (51 votes), past polls