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

Hi everyone! I'm running into a problem trying to read an existing sheet, formatting some of the cells differently (font, cell fill color, etc.) and then writing to a new spreadsheet. I'm reading in each cell's properties, adjusting some of the properties of some of the cells, and reproducing the cell in the new spreadsheet. To begin this effort, I simply wanted to read in an existing sheet, save the properties of each cell, then write them out to the new spreadsheet. However, I'm noticing that some of the cells are not being reproduced with the same properties as the original cells. Here's my code: 
 

#!c:\perl64\bin\perl.exe

use strict;
use warnings;
use Spreadsheet::WriteExcel;
use Spreadsheet::ParseExcel;

my $result_file = "new.xls";

my $property_file = "properties.txt";
open (PROP, ">$property_file");

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

if ( !defined $workbook_parse ) {
      die $parser->error(), "Error processing workbook parse.\n";
}

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();

my $workbook   = Spreadsheet::WriteExcel->new( $result_file );
my $worksheet  = $workbook->addworksheet( 'Sheet1' );


for my $row ( $row_min .. $row_max ) {

    for my $col ( $col_min .. $col_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];
        my $wrap       = $format->{Wrap};       
        my $font       = $format->{Font};
        my $fontcolor  = $font->{Color};
        my $bold       = $font->{Bold};
        my $alignH     = $format->{AlignH};
        my $alignV     = $format->{AlignV};
        
        ## Apply format to cell per previous findings in above logic
        
        my $updformat = $workbook->add_format( 
                     pattern => $pattern,
                     fg_color => $color1,
                     bg_color => $color2,
                     align => 'left',
                     valign => 'top',
                     text_wrap => $wrap,
                     border => 1,
                     color => $fontcolor,
                     bold => $bold
        );
        
        $worksheet->write( $row, $col, $value, $updformat);
        
        ## Write out cell properties to our properties.txt document
        
        print PROP "\nRow, Col = ($row, $col)\n";
        print PROP "Format is:   $format\n";
        print PROP "Pattern    = $pattern\n";
        print PROP "Value      = $value\n";
        print PROP "Fill       = $pattern $color1 $color2\n";
        print PROP "Wrap       = $wrap\n";
        print PROP "Font       = $fontcolor\n";
        print PROP "Bold       = $bold\n";
        print PROP "AlignH     = $alignH\n";
        print PROP "AlignV     = $alignV\n";
    }
}

## Housekeeping

$workbook->close() or die "Error closing file: $!";

close(PROP);

exit;

[download]
You can use this with any Excel 97-2003 spreadsheet, just name it: old.xls and the resulting spreadsheet will be named: new.xls When you view the new.xls you will see what I mean. I'm sure it's something quite simple. Your help is much appreciated! Thank you, in advance.

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel / WriteExcel question ....
by roboticus (Chancellor) on Nov 29, 2017 at 03:56 UTC

    jhalbrook:

    Without a description of what the difference in the appearance, it may be difficult to get people motivated to help. After all, someone could try your code and not see any differences at all because perhaps the workbook they're using happens to not trigger the problem. Is it a problem in font? column size? color? what? Looking at nearly identical things trying to find the difference (assuming there *is* one) is error prone.

    Having said that, is the visual property one of the ones you're actually trying to capture and edit? if so, I'd suspect the vertical or horizontal alignment, since you're not actually using the value you read from the source workbook...

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

[reply]
Re: Spreadsheet::ParseExcel / WriteExcel question ....
by pryrt (Abbot) on Nov 29, 2017 at 15:35 UTC

    As roboticus said, since we don't have the same old.xls that you do, and we don't know what attributes you are looking for in new.xls, we can't necessarily see what you mean: we might be seeing missing attributes you don't care about, and we might be not-seeing changed attributes that you do care about (either because it's not obvious, or our old.xls didn't include the attributes you cared about).

    Some things I've noticed:

    • to expand on ++roboticus's mentioning hardcoded align => 'left', valign => 'top': make use of $alignH and $alignV; maybe something like 
          align  => (undef, qw(left center right fill justify center_across)
+, undef)[$alignH],    # Spreadsheet::WriteExcel doesn't say what to d
+o with Spreadsheet:ParseExcel's 0=>NoAlignment or 7=>Distributed, so 
+I assume undef
    valign => (qw(top vcenter bottom vjustify), undef)[$alignV],  # Sp
+readsheet::WriteExcel doesn't say what to do with Spreadsheet:ParseEx
+cel's 4=>Distributed, so I assume undef

      [download]
    • border => 1 is manually setting the border to 1px black, instead of using information from ::ParseExcel
    • You are only grabbing the bold and color attributes of the font, so will be missing font size, font type, and possibly other attributes important to you
    • You never grab or set row height or column width
      ⇒ I don't immediately see how to grab them from ::ParseExcel's docs; if you figure out how, then ::WriteExcel's ->set_row() and ->set_column() will set them for new.xls
[reply]
[d/l]
[select]
      Thank you so much for your kind remarks regarding the issue I was referring to. Let me explain a bit further. I'm really only going to be changing some of the values of a cell, as well as font color and the fill color of the cell - that's all I'll ever need to change.

      I would love to upload the example old.xls file. Being a newbie, I didn't see a way to upload a file to a post. If that's possible, please let me know. Until then, you may obtain the file here: upload file

      Notice the nuances in row 8 (missing vertical borders) Column 8 (J) is missing fill color, rows 17-20 loses all gray fill, and cell F30 loses font color. I don't understand why it's losing some formatting only sporadically. Especially when you can see in the properties.txt debug file the values appear correct.

      Thanks for the extra eyeballs, guys and gals!

      Joe
[reply]

        If you just want to modify a few cells on an existing spreadsheet then consider using Win32::OLE

        #!perl
use strict;
use warnings;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # Die on Errors.

# config
my $dir  = 'c:\\temp\\';
my $file = 'test.xls'; 

my $Excel = Win32::OLE->new('Excel.Application', 'Quit');  
$Excel->{DisplayAlerts} = 0;  
#$Excel->{Visible} = 1;  

my $wb  = $Excel->Workbooks->Open($dir.$file);
my $ws = $wb->sheets(1);

my $cell = $ws->Cells(1,1);
$cell->{Value} = '567';
$cell->Interior->{Color} = rgbLightGreen; 
$cell->Font->{Color}     = rgbDarkGreen; 

$wb->SaveAs($dir.'copy_'.$file);
$wb->Close;

        [download]
        poj
[reply]
[d/l]

        Sorry, I don't download random files, especially spreadsheets -- Excel just doesn't have enough security.

        poj++ is probably right: if you want to copy one spreadsheet to another, and then just change a couple of things, OLE is probably the way to go.

        However, if you want to understand what you're missing, and what's going wrong:

        • Since you're hardcoding border => 1, as I pointed out, that possibly explains the "missing vertical borders" (well, maybe not, since that should presumably set all borders the same...)
        • My gut reaction to why some cells are copying formats correctly, and others not: the Spreadsheet::WriteExcel ->add_format() method creates a new ::Format object, even if all the parameters match. I am wondering if maybe there is a limit to the number of ::Format objects that can be added before there is loss of information.
        • For a full comparison of what's different between the two spreadsheets, after your $workbook->close(), I would instantiate another parser, this time for 'new.xls', and then do a cell-by-cell comparison of the properties for old vs new, using something like Data::Dump's dd() make sure you aren't omitting critical parameters (example code not tested):
          ... # your code above here
## Housekeeping

$workbook->close() or die "Error closing file: $!";

close(PROP);

{   ## COMPARISON
    use Data::Dump qw/dd dump/;
    my $old_ws_parse = $worksheet_parse;  # copy to new name for symme
+try, below
    my $new_wb_parse = $parser->parse( 'new.xls' ) or die $parser->err
+or(), "Error processing new workbook parse.\n";
    my $new_ws_parse = $new_wb_parse->worksheet( 'Sheet1' );
    open(my $compare_fh, '>', 'compare_formats.txt') or die "compare_f
+ormats.txt: $!";  # get in the habit of using lexical filehandles
    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {
            my $oldcell = $old_ws_parse->get_cell($row,$col) or next;
            my $newcell = $new_ws_parse->get_cell($row,$col) or next;
            # to STDERR
            dd 'oldformat => ', my $oldformat = $oldcell->get_format()
+;  # you could use Data::Dump::dump() instead, if you want to print t
+o your own file (similar to
            dd 'newformat => ', my $newformat = $newcell->get_format()
+;
            # to file handle
            print $compare_fh 'oldformat => ', dump($oldformat), "\n";
            print $compare_fh 'newformat => ', dump($newformat), "\n";
        }
    }
}
exit;

          [download]
          (or, if you used a separate filehandle for old and new for this output, you could use diff or similar utility to quickly find the differences
        • At some point, you may need to dd on the Spreadsheet::WriteExcel object or sub-objects as well, to see if you can figure out when data is getting corrupted while doing the writing.

        But, really, based on your statement "I'm really only going to be changing some of the values of a cell, as well as font color and the fill color of the cell - that's all I'll ever need to change.", I really think you should just follow poj's advice.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom