Searching all Excel files in a directory

[starchild]

This is a snippet of code that searches all Excel files in a directory for certain pieces of data. If any matches are found, their location is saved in a log file. It uses the technique described here to reduce memory consumption. This has come in quite handy at work, where we receive Excel files on a daily basis and occasionally need to find out which file some piece of information resides in.

Update: Based on a suggestion, I changed the code to use the qr// operator, so that the regular expressions are compiled only once, instead of every time a cell is searched.

use strict;
use warnings;
use File::Basename;
use Spreadsheet::ParseExcel;

$| = 1;

my $excelDir = "c:/documents and settings/administrator/my documents/m
+iddle_earth";

my @searchTerms = ( qr/FRODO/, qr/SAM/, qr/ARWEN/, qr/ARAGORN/ );

#maps column number to column letter as used by Excel
my @columnMap = ('A'..'ZZ');

#any matches found are printed to log file
my $logName = "excel_search.log";
open(my $fh, ">$logName") or die "can't open $logName: $!";

my $parse_excel = Spreadsheet::ParseExcel->new(
  CellHandler => \&cell_handler,
  NotSetCell => 1
);

#escape blanks in the excel directory path
$excelDir =~ s/ /\\ /g;

my $fileMatches;
my $totalMatches = 0;

foreach my $file (glob("$excelDir/*.xls")) {
  $fileMatches = 0;
  print "Searching " . basename($file) . "... ";

  $parse_excel->Parse($file);

  print $fileMatches, " matches found.\n\n";
  $totalMatches += $fileMatches;
}

print $totalMatches, " total matches were found.  See $logName for det
+ails\n";

close($fh);


sub cell_handler {
  my ($workbook, $sheet_index, $row, $col, $cell) = @_;

  foreach my $re (@searchTerms) {
    if ($cell->Value =~ $re) {
      #found a match; print out details
      print $fh "File:  ", basename($workbook->{File}), "\n",
        "Sheet: ", $workbook->{Worksheet}[$sheet_index]->{Name}, "\n",
        "Cell: ", $columnMap[$col], ":", $row, "\n",
        "Cell Contents: ", $cell->Value, "\n\n";

      $fileMatches++;
    } #end if
  } #end foreach

}