Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

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 (Monsignor) 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?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://993689]
Approved by marto
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (9)
As of 2014-09-18 13:21 GMT
Find Nodes?
    Voting Booth?

    How do you remember the number of days in each month?

    Results (115 votes), past polls