Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

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,$_); }

Comment on bg_color color change in xls cells
Download Code
Replies are listed 'Best First'.
Re: bg_color color change in xls cells
by kcott (Abbot) 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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (11)
As of 2015-07-28 10:08 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 (254 votes), past polls