Use Spreadsheet::ParseExcel.

Start by reading in the contents of your first spreadsheet. Let's assume you're using worksheet titled "gendata".

  use strict;
  use Spreadsheet::ParseExcel;

  my $oBook = Spreadsheet::ParseExcel::Workbook->Parse('first.xls');

  if ( ( ! $oBook ) || 
       ( ! defined( $oBook->{Worksheet} ) ) )
  {
    die( "Cannot parse first spreadsheet" );
  }
  my $sheetone = $oBook->Worksheet('gendata') ||
    die( "No such worksheet gendata" );

[download]
Then read in the contents of the second spreadsheet (let's assume you're using worksheet titled "gensearch"):
  my $oBook2nd=Spreadsheet::ParseExcel::Workbook->Parse('second.xls');

  if ( ( ! $oBook2nd ) || 
       ( ! defined( $oBook2nd->{Worksheet} ) ) )
  {
    die( "Cannot parse second spreadsheet" );
  }
  my $sheettwo = $oBook->Worksheet('gensearch') ||
    die( "No such worksheet gensearch" );

[download]

Next let's assume that you want to search all the strings in the first 10 rows of column A in workbook two against the same column/row in workbook one.

  for ( my $row = 0; $row < 10; $row++ ) {
    my $search = qr/$sheettwo->{Cells}[$row][0]->{Val}/;
    my $data = $sheetone->{Cells}[$row][0]->{Val};
    if ( $data =~ m/$search/ ) {
      printf( "Column A Row %d matches\n",
              ( $row + 1 ) );
    }
  }

[download]

In reply to Re: compare two Excel spreadsheets by monarch
in thread compare two Excel spreadsheets by juergenkemeter

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.