Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

bg_color color change in xls cells

by bimleshsharma (Beadle)
on May 24, 2012 at 06:10 UTC ( #972173=perlquestion: print w/replies, xml ) Need Help??
bimleshsharma has asked for the wisdom of the Perl Monks concerning the following question:

I have xls file. I want to change bg_color of particular cells as per some conditions and keeping rest format as it is. For that i use xls rewrite module and it is working fine as per requirement but still doing some extra which i not required. What it is doing extra- some cells in original have pink bg_color these all are getting change with green (which is not required). Here is code snippet. Please help.
use Spreadsheet::WriteExcel; use Spreadsheet::ParseExcel; use Spreadsheet::ParseExcel::SaveParser; my $parser = new Spreadsheet::ParseExcel::SaveParser; my $sheet=1; my $template = $parser->Parse('compare_reports/38_HMI1071_cre_cdo_G.xl +s'); my $worksheet = $template->worksheet($sheet); my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); for my $col ( $col_min+2 .. $col_max ) { for my $row ( $row_min+2 .. $row_max ) { #print "\n row, col=$row,$col"; my $cell = $worksheet->get_cell($row, $col); if(!$cell){ next; } elsif($worksheet->get_cell($row,$col)->value() ne "" ){ my $cell_val=$worksheet->get_cell($row,$col)->value(); my $cell_format = $worksheet->get_cell($row,$col)->{Fo +rmatNo}; print "\n row, col=$row,$col cell_format= $cell_format, valu +e= $cell_val"; if($cell_format eq 26){ my $left_val=$worksheet->get_cell(0,$col)->value() +; my $right_val=$worksheet->get_cell(0,$col)->value( +); #Format my $iF1 = $template->AddFont( Name => 'Arial', Height => 11, Bold => 0, #Bold Italic => 0, #Italic Underline => 0, Strikeout => 0, Super => 0, ); my $iFmt = $template->AddFormat( Font => $template->{Font}[$iF1], Fill => [1, 10, 0], # Filled with Red # cf. ParseExcel ( +@aColor) #BdrStyle => [0, 1, 1, 0], #Border Right, To +p #BdrColor => [0, 11, 0, 0], # Right->Green ); $template->AddCell($sheet, 0, $col,$left_val , $iF +mt); $template->AddCell($sheet, 1, $col,$right_val , $i +Fmt); last; } } else { next; } } } my $workbook; { local $^W = 0; $workbook = $template->SaveAs($com_path); } $workbook->close(); push(@temp_files,$_); }

Replies are listed 'Best First'.
Re: bg_color color change in xls cells
by kcott (Chancellor) on May 24, 2012 at 06:26 UTC
    "For that i use xls rewrite module ..."

    What module are you referring to here?

    I wondered if you had meant rewrite function (or something similar) but the word rewrite only appears once (as shown in the quote above).

    -- Ken

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://972173]
Approved by kcott
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (6)
As of 2017-05-26 07:54 GMT
Find Nodes?
    Voting Booth?