Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Not sure why perl-generated spreadsheet has formatting issues

by poopay (Initiate)
on Sep 14, 2012 at 09:11 UTC ( #993689=perlquestion: print w/ replies, xml ) Need Help??
poopay has asked for the wisdom of the Perl Monks concerning the following question:

I have recently created a perl script that aims to collect information from different spreadsheets and combine the data into one spreadsheet.

I've set 2 columns to display data in a date format. The spreadsheet was indeed generated; however, I noticed that the date in the formatted column treat the dates as string.

However, I noticed that if I double-click the cell with the dates then press "Enter", the data is then converted to the format I wanted it to be.

This is a snippet of the code I created

#create your destination spreadsheet my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' ); my $worksheet = $workbook->add_worksheet(); #set date format for rows H and I my $format = $workbook->add_format( num_format => 'yyyy-mm-dd' ); #create some formatting for dates $worksheet->set_column('H:I', undef, $format); # for every spreadsheet... foreach $file (@files) { do { #Read the input file $excel = new Spreadsheet::BasicRead($file); #Get the data from each row while ($dataref = $excel->getNextRow()) { #Write the data into your destination spreadsheet and incr +ement row my @data = @$dataref; $worksheet->write_row($row, 0, \@data); $row++; } } }

Is there a way for my output to be displayed properly in the correct format without me doing this? Hope someone can help.

Comment on Not sure why perl-generated spreadsheet has formatting issues
Download Code
Re: Not sure why perl-generated spreadsheet has formatting issues
by Athanasius (Abbot) on Sep 14, 2012 at 15:59 UTC

    Hello poopay, and welcome to the Monastery!

    As explained in Excel::Writer::XLSX#DATES_AND_TIME_IN_EXCEL, Excel dates are stored internally as real numbers, but Spreadsheet::BasicRead and its relatives read dates in as strings. This means you have to manually extract the date from your input data, then convert it to a more convenient form. Here is one way to do this (adapted from the example given in the module documentation just referenced):

    #! perl use strict; use warnings; use Excel::Writer::XLSX; use Spreadsheet::BasicRead; my $workbook = Excel::Writer::XLSX->new('perl.xlsx') or die "$!"; my $worksheet = $workbook->add_worksheet(); my @files = ('data.xlsx'); foreach my $file (@files) { my $excel = new Spreadsheet::BasicRead($file) or die "$!"; my $row = 0; while (my $dataref = $excel->getNextRow()) { foreach my $date (@$dataref[7 .. 8]) # Columns H and I { $date =~ s[ ^ (\d{1,2}) / (\d{1,2}) / (\d{4}) $ ] [ sprintf("%4d-%02d-%02d", $3, $2, $1) ]ex; } $worksheet->write_row($row++, 0, $dataref); } }

    Note: this approach will work only if you know the format(s) of the dates in your input files — or if you are prepared to expand the regex to allow for all the date formats you may encounter.

    Update 1: I tested the above on an input file data.xlxs with Row 1 populated as follows:

    1.1 2.1 3.3 4.4 5.5 6.6 7.7 9/15/2012 7/18/196 +0 8.8 9.9

    The output file, perl.xlxs, had as Row 1:

    1.1 2.1 3.3 4.4 5.5 6.6 7.7 2012-15-09 1960-18 +-07 8.8 9.9

    as required.

    Hope that helps,

    Update 2: Why did you have a do {} block in your code snippet? As far as I can see, it serves no purpose there.

    Athanasius <°(((><contra mundum

      Thank you for the suggestion; I'll try this in a while. Will give feedback regarding the results. :)

      Regarding the "do" block - I wasn't able to clean up the script before posting here. My bad. Will remove it in a while.

      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 :(

        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: perlquestion [id://993689]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (6)
As of 2015-07-04 09:34 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 (59 votes), past polls