compare excel sheets

[einstein]

Hi,
I'm new to perl.

I want to compare two excel sheets the second excel file is just the revised version of the first one. There are only two columns each file with a certain number of Rows

The following code is just printing all the values in it

use strict;
use warnings;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
     my $parser   = Spreadsheet::ParseExcel->new();
     my $workbook = $parser->parse('C:\\perl\\Data dictionary\\first.xls');
 
     print $workbook ,"\n";
     my $parser1   = Spreadsheet::ParseExcel->new();
     my $workbook1 = $parser->parse('C:\\perl\\Data dictionary\\second.xls');
 
     print $workbook ,"\n";
     print $workbook1 ,"\n";
 
     if ( !defined $workbook ) {
         die $parser->error(), ".\n";
     }
 
    for my $worksheet ( $workbook1->worksheets())
          {
         my ( $row_min, $row_max ) = $worksheet->row_range();
         my ( $col_min, $col_max ) = $worksheet->col_range();
 
         for my $worksheet ( $workbook->worksheets())
          {
         my ( $row_min, $row_max ) = $worksheet->row_range();
         my ( $col_min, $col_max ) = $worksheet->col_range();
 
 
            for my $row ( $row_min .. $row_max ) {
             for my $col ( $col_min .. $col_max ) {
 
                 my $cell = $worksheet->get_cell( $row, $col );
 
                 my $cell1 = $worksheet->get_cell( $row, $col );
                 next unless $cell and $cell1;
 
         my $data = $cell->value();
         #print $data;
         my $search = $cell1->value();
 print $search;
 
 
 
                }
            }
        }
    }

[miller]

You have a bug in your script because you reuse the $worksheet variable.

It honestly looks like you just copy and pasted this code from somewhere without actually reading it.

The following will get you closer to where you want to be, but I haven't not yet finished the script. It will be up to you to put the final touches on it:

use strict;
use warnings;
 
use Spreadsheet::ParseExcel;
use List::Util qw(max min);
 
my $file1 = 'C:\\perl\\Data dictionary\\first.xls';
my $file2 = 'C:\\perl\\Data dictionary\\second.xls';
 
my $parser   = Spreadsheet::ParseExcel->new();
 
my $book1 = $parser->parse($file1) // die $parser->error();
my $book2 = $parser->parse($file2) // die $parser->error();
 
my @sheets1 = $book1->worksheets();
my @sheets2 = $book2->worksheets();
 
if (@sheets1 != @sheets2) {
    warn "Unequal number of sheets: " . @sheets1 . " vs " . @sheets2 . "\n";
}
 
for my $i (0..min($#sheets1, $#sheets2)) {
    my $sheet1 = $sheets1[$i];
    my $sheet2 = $sheets2[$i];
 
    my ( $sheet1_row_min, $sheet1_row_max ) = $sheet1->row_range();
    my ( $sheet2_row_min, $sheet2_row_max ) = $sheet2->row_range();
 
    warn "Sheet $i: Unequal minimum row: $sheet1_row_min vs $sheet2_row_min\n"
        if ($sheet1_row_min != $sheet2_row_min;
    warn "Sheet $i: Unequal maximum row: $sheet1_row_max vs $sheet2_row_max\n"
        if ($sheet1_row_max != $sheet2_row_max;
 
    for my $row (max($sheet1_row_min, $sheet2_row_min) .. min($sheet1_row_max, $sheet2_row_max)) {
        my ( $sheet1_col_min, $sheet1_col_max ) = $sheet1->col_range();
 
        ...
    }
}
 

- Miller

[einstein]

sorry for the late reply Miller, but i was messed up pretty bad in the same stuff. Now I'm through and thanks to you I got the initiation atleast.

Do you by any chance how to use perl to fetch tables from microsoft access database. I ve been thinking to advance this a level up now. rather than comparing the excel if i directly fetch from access database and compare. Any ideas you can share would be very useful.

[RonB]

See:
DBI - Database independent interface for Perl
DBD::ODBC - ODBC Driver for DBI

[Rabbit]

We try to limit threads to one question each. If you need further assistance with perl, please post a new thread in that forum.