in reply to Getting the background color of a cell in an existing Excel spreadsheet

Hello venky1937,

When posting your question on multiple forums it would be nice to mention that. (Getting the background color of a cell in an existing Excel spreadsheet (stackoverflow question)).

The answer is simple as mentioned on your question, from the documentation (Spreadsheet::ParseExcel/$font->{Color}):

$font->{Color} Returns the color index for the font. The mapping to an RGB color is d +efined by each workbook. The index can be converted to a RGB string using the $workbook-ColorId +xToRGB()> Parser method. (Older versions of Spreadsheet::ParseExcel provided the ColorIdxToRGB +class method, which is deprecated.)

Keep these notes next time that you post a question, we are more than happy to help but it would be nice to know the duplicated questions so not to wast time when the answer is already there.

Update: I read your comment, on the other forum:

Borodin:I tried using ColorIdxToRGB and it gives me error: Can't locat +e object method "ColorIdxToRGB" via package "Spreadsheet::ParseExcel: +:Workbook"

Did a bit of research and I found the set_color() from the module (Spreadsheet::WriteExcel). From the documentation:

Default state: Excels default color, usually black Default action: Set the default color Valid args: Integers from 8..63 or the following strings: 'black' 'blue' 'brown' 'cyan' 'gray' 'green' 'lime' 'magenta' 'navy' 'orange' 'pink' 'purple' 'red' 'silver' 'white' 'yellow'

Based on the integer that you are getting you should be able to set the font colour.

Update2: I just tested with a sample of code that I created and seems to work for me:

#!/usr/bin/perl use strict; use warnings; use Spreadsheet::WriteExcel; use Spreadsheet::ParseExcel; sub create_new_worksheet { my ($row, $col, $value, $backgroundcolor) = @_; # Create a new Excel workbook my $workbook = Spreadsheet::WriteExcel->new('perl.xls'); # Add a worksheet my $worksheet = $workbook->add_worksheet(); # Add and define a format my $format = $workbook->add_format(bg_color => $backgroundcolor); $format->set_align('center'); # Write a formatted and unformatted string, row and column notatio +n. $worksheet->write($row, $col, $value, $format); } my $parser = Spreadsheet::ParseExcel->new(); my $workbook_parse = $parser->Parse( 'Report.xls' ); my $worksheet_parse = $workbook_parse->Worksheet("Sheet1"); my ( $col_min, $col_max ) = $worksheet_parse->col_range(); my ( $row_min, $row_max ) = $worksheet_parse->row_range(); for my $col ( $col_min .. $col_max ) { for my $row ( $row_min .. $row_max ) { # Return the cell object at $row and $col my $cell = $worksheet_parse->get_cell( $row, $col ); next unless $cell; my $value = $cell->value(); my $format = $cell->get_format(); my $backgroundcolor = $format->{Fill}->[2]; print "Row, Col = ($row, $col) "; print "Value = $value\n"; print "Format = $backgroundcolor\n"; create_new_worksheet($row, $col, $value, $backgroundcolor); } } __END__ $ perl excel.pl Row, Col = (0, 0) Value = Test Format = 34

Update3: removed not necessary code.

Hope this helps.

Seeking for Perl wisdom...on the process of learning...not there...yet!

Replies are listed 'Best First'.
Re^2: Getting the background color of a cell in an existing Excel spreadsheet
by duyet (Friar) on Apr 19, 2017 at 11:54 UTC
    There is no "ColorIdxToRGB" ... the correct syntax is:
    $workbook->color_idx_to_rgb( $format->{Fill}[1] )

      Hello duyet,

      Just to add both methods can be used, I just test it:

      my $sRGB = $parser->ColorIdxToRGB($format->{Fill}->[2]); my $sRGB_2 = $workbook->color_idx_to_rgb($format->{Fill}->[2]);

      Also from the source code of the Spreadsheet::ParseExcel:

      #--------------------------------------------------------------------- +--------- # ColorIdxToRGB (for Spreadsheet::ParseExcel) # # Returns for most recently opened book for compatibility, use # Workbook::color_idx_to_rgb instead # #--------------------------------------------------------------------- +--------- sub ColorIdxToRGB { my ( $sPkg, $iIdx ) = @_; unless( defined $currentbook ) { return ( ( defined $aColor[$iIdx] ) ? $aColor[$iIdx] : $aColor[0] +); } return $currentbook->color_idx_to_rgb( $iIdx ); }
      Seeking for Perl wisdom...on the process of learning...not there...yet!
Re^2: Getting the background color of a cell in an existing Excel spreadsheet
by venky1937 (Initiate) on Apr 19, 2017 at 09:29 UTC
    Hello Thanos, Thanx for the answer.But I am not looking for setting the font color. I am looking for to read a background color of a cell and apply the same background color to the cell which is in new Spreadsheet.Background color of a cell can be set through set_bg_color() of Spreadsheet::WriteExcel.The thing is when I am reading the background color throuh $format->{Fill}2 method I am getting 65(if no bg color) or 64 irrespective of the background color.

      Hello venky1937,

      Take a look on my updated answer it looks better now. It works for me, let me know if it also works for you.

      Hope this helps.

      Seeking for Perl wisdom...on the process of learning...not there...yet!