Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

ParseExcel::SaveParser, colors & google docs

by irDanR (Novice)
on Aug 05, 2010 at 18:52 UTC ( #853217=perlquestion: print w/replies, xml ) Need Help??

irDanR has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to combine a single worksheet from each of a dozen different workbooks into a single new workbook. I can do this perfectly with ParseExcel and WriteExcel. However I need to retain the formatting of the original sheets.

Enter ParseExcel::SaveParser.

use Spreadsheet::ParseExcel; use Spreadsheet::ParseExcel::SaveParser; use Spreadsheet::WriteExcel; my $parser = Spreadsheet::ParseExcel::SaveParser->new(); my $template = $parser->Parse('test.xls'); $template->SaveAs('test.xls');
(Basically copied from the synopsis.) Running it on an example workbook gives me almost perfect results. The cell size, border, font, etc. are all correct. However, the font color as well as background color change. As in, black font turns into impossible to read light blue or pink.

I notice that once the worksheet has been run through SaveParser, the colors stay the same in subsequent passes. This got some gears turning and I found that it only happens to an xls once after downloading from google docs.

Just to be painfully clear:
1) Downloaded test.xls from google docs.
2) Run through SaveParser with the above code. (changes the colors)
3) Run through same code, color stays the same.
4) Upload to google docs then download from google docs.
5) Run through code again, colors change.

My situation requires that I download the docs from google and then combine into a new doc and then upload back to google weekly. Does anyone have any experience with this or have any suggestions for making the colors stay the same? I foresee this requiring me to become much more familiar with the google doc API...

Update: An interesting find, using:
my $parser = Spreadsheet::ParseExcel::SaveParser->new(); my $template = $parser->Parse('test.xls'); my $line = 0; my $file = "color.txt"; tie my @array, 'Tie::File', $file or die "Cannot open $file: $!"; for my $worksheet ($template->worksheets() ) { my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); for my $row ( $row_min .. $row_max ) { for my $col ( $col_min .. $col_max ) { $line++; my $cell = $worksheet->get_cell( $row, $col ); next unless $cell; my $format = $cell->get_format(); my $font = $format->{Font}; my $color = $font->{Color}; $array[$line] .= "\t $color"; } } } untie @array;
to get a number for the color - when I output the number to a file and then compare it before the color change and after the change, gives me the same number.

Replies are listed 'Best First'.
Re: ParseExcel::SaveParser, colors & google docs
by jmcnamara (Monsignor) on Aug 06, 2010 at 09:10 UTC

    Excel uses a palette of 64 colours. These colours are referred to internally using an index number.

    In general a standard palette is used but it can be redefined by the user. As such, colour index 42 may be red in one workbook and green in another.

    SaveParser doesn't try to map the colour palette between the original and target workbooks (which is a bug) so this may be causing the effect that you are experiencing.

    You can try work around the issue as follows:

    #!/usr/bin/perl use strict; use warnings; use Spreadsheet::ParseExcel; use Spreadsheet::ParseExcel::SaveParser; my $parser = Spreadsheet::ParseExcel::SaveParser->new(); my $template = $parser->Parse('color01.xls'); # Get the underlying Spreadsheet::WriteExcel workbook. my $swe_workbook = $template->SaveAs('color02.xls'); # Map the original color palette to the new workboook. for my $i ( 8 .. 63 ) { $swe_workbook->set_custom_color( $i, '#' . $parser->ColorIdxTo +RGB($i) ); }

    See the Spreadsheet::WriteExcel docs for more information on Colours in Excel.

    Update: I've logged this issue on rt.cpan.org.

    --
    John.

      Your code works exactly as expected! Oops, I probably would have found that documentation had I thought to try my search with colour instead of color. Thanks for the help!

Re: ParseExcel::SaveParser, colors & google docs
by dasgar (Priest) on Aug 05, 2010 at 19:21 UTC

    If you're doing this on a Windows box with Excel installed, I'd recommend using Excel directly with Win32::OLE. That would allow you to copy a worksheet from one file to another without losing formatting in the process.

    However, if you're doing this from *nix, it might be possible to do this through a module that uses Open Office's API. Aside from that, I'm out of ideas.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (2)
As of 2021-04-15 03:06 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?