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" );
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" );
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 ) );
}
}
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.
|