ParseExcel - need background color info

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!

Comment on ParseExcel - need background color info Select or Download Code

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]


Perl Monk, Perl Meditation
	PerlMonks