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.
|
|---|
| 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 | |
by thanos1983 (Parson) on Apr 19, 2017 at 22:28 UTC | |
|
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 | |
by thanos1983 (Parson) on Apr 19, 2017 at 09:52 UTC |