bblustein has asked for the wisdom of the Perl Monks concerning the following question:

Ok, I've RTFM several times, and still I'm not quite grasping how to get the background color (shading) of a cell in an Excel worksheet with Spreadsheet::ParseExcel. I can get the data from the individual cells, taking nearly directly from the well-cited example included in the documentation: 
$oBook = Spreadsheet::ParseExcel::Workbook->Parse("$strExcelPath");
  foreach my $oWkS (@{$oBook->{Worksheet}}) {
  print "--------- SHEET:", $oWkS->{Name}, "\n";
  for(my $iR = $oWkS->{MinRow} ; 
  defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ; 
  $iR++) {
   for(my $iC = $oWkS->{MinCol} ;
                            defined $oWkS->{MaxCol} && $iC <= $oWkS->{
+MaxCol} ; $iC++) {
                $oWkC = $oWkS->{Cells}[$iR][$iC];
                print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC)
+;

[download]
But I need not only the cell value, defined in $oWkC->Value, but the background color of the cell. The documentation provides that "Format" is a cell property, the same way "Value" is. So... 
$oFormat = $oWkC->Format;

[download]
Should return the format information to the $oFormat object. Now, Fill is a property of the Format object, so... 
$oFill = $oFormat->Fill;

[download]
Should return, according to the documentation: Array ref of fill pattern and color indexes : [Pattern, Front Color, Back Color] But this is where I'm stuck. No matter how many different ways I've tried writing it, I am unable to get the program to spit out the Pattern, front color, or back color. Can anyone help me out with the proper format of this?

Update - Thank you very much, ptum and jmcnamara - your replies not only helped me reach my solution, but have helped me learn a little bit more about this sort of form (and how the documentation may appear behind it!). As usual, you more experienced monks are knowledgeable and helpful!

Replies are listed 'Best First'.
Re: ParseExcel - need background color info
by jmcnamara (Monsignor) on Sep 07, 2006 at 16:47 UTC

    Here is a small example. Uncomment the Dumper line to see the full Cell data structure. 
        #!/usr/bin/perl -wl

    use strict;
    use Spreadsheet::ParseExcel;
    use Data::Dumper;

    my $parse_excel = Spreadsheet::ParseExcel->new();

    my $workbook = $parse_excel->Parse('parse.xls');


    for my $worksheet (@{$workbook->{Worksheet}}) {
        if (defined $worksheet->{MaxRow}) {
            for (my $row =  $worksheet->{MinRow};
                    $row <= $worksheet->{MaxRow};
                    $row++)
            {
                for (my $col =  $worksheet->{MinCol};
                        $col <= $worksheet->{MaxCol};
                        $col++)
                {
                    my $cell     = $worksheet->{Cells}[$row][$col];
                    my $pattern  = $cell->{Format}->{Fill}->[0];
                    my $fg_color = $cell->{Format}->{Fill}->[1];
                    my $bg_color = $cell->{Format}->{Fill}->[2];

                    print "Row, Col = ($row, $col)";
                    print "Value    = ", $cell->Value();
                    print "Fill     = [$pattern, $fg_color, $bg_color]
+\n";
                    #print Dumper $cell;
                }
            }
        }
    }

    [download]

    --
    John.

[reply]
[d/l]
Re: ParseExcel - need background color info
by ptum (Priest) on Sep 07, 2006 at 16:30 UTC

    As I recall, it takes a little trial and error. I found that the format fill index I was after was 1, so that you may want something like this:

      my $cell_obj = $sheet_obj->{Cells}[$row][$col];
  my $format_obj = $cell_obj->{Format};
  my $fill_color = $format_obj->{Fill}[1];

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom