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.)
[download]

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"
[download]

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'
[download]

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
[download]

Update3: removed not necessary code.

Hope this helps.

There is no "ColorIdxToRGB" ... the correct syntax is:

$workbook->color_idx_to_rgb( $format->{Fill}[1] )
[download]

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]);
[download]

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 );
}
[download]

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

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!

fill is a combination of pattern and 2 colors so for a solid pattern the color you want is probably in the first color
$format->{Fill}->[1]. You also need to create a new format object for each color rather that re-using the same one with set_bg_color

Try this

#!perl
use strict;
use Data::Dumper;

use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;

my $parser          = Spreadsheet::ParseExcel->new();
my $workbook_parse  = $parser->parse( 'Report.xls' );

my $worksheet_parse = $workbook_parse->Worksheet( "Health_Report" );
my ( $col_min, $col_max ) = $worksheet_parse->col_range();
my ( $row_min, $row_max ) = $worksheet_parse->row_range();

my $workbook   = Spreadsheet::WriteExcel->new( "Report_new.xls" );
my $worksheet  = $workbook->addworksheet( "Health_Report" );
my %bkgd_color = ();

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 $pattern = $format->{Fill}->[0];         
        my $color1  = $format->{Fill}->[1];
        my $color2  = $format->{Fill}->[2];

        print "Row, Col = ($row, $col) ";
        print "Value    = $value\n";
        print "Fill     = $pattern $color1 $color2\n";

        ### Here trying to rewrite into Excel and apply the
        ### same background color which the cell had previously
       
        if ($pattern == 1 ){
          if ( ! exists $bkgd_color{$color1} ){
            $bkgd_color{$color1} =  $workbook->addformat(
              pattern  => $pattern,
              bg_color => $color1
            );
          } 
          $worksheet->write( $row, $col, $value, $bkgd_color{$color1})
        } else {
          $worksheet->write( $row, $col, $value);
        }        
    }
}
[download]

Hello poj,

The code that you provided looks good, I just tested it on my sample spreadsheet.

I have one question, is it also possible to detect from format the alignment? I have center alignment and the new xls file does not produce the alignment.

Thanks in advance for the time effort reading and replying to my question.

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

All the properties are described here Format Properties. I guess you want $format->{AlignH}

poj

    ...
    system ('C:\Program Files (x86)\OpenOffice 4\program\scalc.exe', 
        '-headless', 
        'macro:///Standard.Module1.ConvertToHtml("' . $xlsx . '","' . 
+$file->canonpath . '")') if ! $file->exists;
    ...
[download]

<shameless plug>Use Spreadsheet::Read. In this example I also use Data::Peek</shameless plug>

$ perl -MSpreadsheet::Read -MDP -wE'DDumper (ReadData("attr.xls",attr=
+>1)->[1]{attr}[4][3])'
{   bgcolor          => '#0000ff',
    bold             => 0,
    enc              => undef,
    fgcolor          => '#008000',
    font             => 'Arial',
    format           => undef,
    formula          => undef,
    halign           => undef,
    hidden           => 0,
    italic           => 0,
    locked           => 1,
    merged           => 0,
    size             => 10,
    type             => 'text',
    uline            => 0,
    valign           => 'bottom',
    wrap             => 0
    }
[download]