http://www.perlmonks.org?node_id=11106304

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

I am looking for ways to speed up the searching for words in Excel. Currently, I simply loop through the used cells range and examine each cell's contents. For some spreadsheets this can take almost a minute if they have hunreds of columns and thousands of rows and dozens of sheets. When I have thousands of documents, this is too long.

I had a similar problem in Word where I had to search thousands of paragraphs in thousands of documents. What I did when I had only words to search for (not regexen) is use Word's Find function. This sped things up by an order of magnitude. I was wondering if anyone knew the syntax for Excel Finds. It is not the same as Word's. I have tried to translate the VBA that does work into Perl, but have failed thus far.

I currently search with something like this:

use strict; use Win32::OLE; use Win32::OLE::Const 'Microsoft Excel'; my $Excel = Win32::OLE->new('Excel.Application','Quit') or die "Cannot + open Excel\n"; my $Book = $Excel->workbooks->Open(FileName => "foo.xlsx", ReadOnly => + 1) or die "Cannot open Spreadsheet\n"; my $num_sheets = $Book->Sheets->Count; for my $sheet_num ( 1 .. $num_sheets ) { my $Sheet = $Book->worksheets( $sheet_num ); my $max_col = $Sheet->UsedRange->SpecialCells( xlCellTypeLastCell +)->Column; my $max_row = $Sheet->UsedRange->SpecialCells( xlCellTypeLastCell +)->Row; for my $row ( 1 .. $max_row ) { for my $col ( 1 .. $max_col ) { print "I found it in Sheet $sheet_num Cell $row,$col\n" if + ( $Sheet->Cells( $row, $col )->Value =~ /cabbage/ ); } } }

The code that uses Excel's Find in VBA is

Set Loc = ThisWorkBook.Sheets(n).UsedRange.Cells.Find(What:="cabbage")

Any Ideas? I have tried things like my $loc = $Sheet->UsedRange->Cells->Find({What}=>"cabbage") and variations to no avail.

As always, I hope I have typed this in correctly. Please excuse any typos. And I cannot use any CPAN.