Visit all cells in an Excel worksheet

# We need this for xlUp.

use Win32::OLE::Const 'Microsoft.Excel';

# Given an operation, apply it to each cell of the worksheet.

my $max_excel_column = 256;
my $max_excel_row    = 65536;

sub apply_operation_to_all_cells
{
    # $excel is the Win32::OLE Excel object.

    # $sheet is the worksheet you want to operate on.

    # $operation is a reference to a subroutine. This subroutine
    # should return either the transformed value to put into the cell,
    # or a false value if the cell is to be left unchanged.

    # $data is for any data to pass through to $operation.

    my ($excel, $sheet, $operation, $data) = @_;
    for (my $col = 1; $col <= $max_excel_column; $col++) {
        # See Turn a number into an Excel column name for ntol
        my $range = ntol ($col).$max_excel_row;
        # Find the bottommost row for this column.
        my $last_row = $sheet->Range ($range)->End (xlUp)->Row;
        for (my $row = 1; $row <= $last_row; $row++) {
            my $value = $sheet->cells ($row, $col)->{Value};
            if (defined $value) {
                my $transformed = &{$operation} ($data, $value, $row, $col);
                if ($transformed) {
                    $sheet->cells ($row, $col)->{Value} = $transformed;
                }
            }
        }
    }
}

Example

#! perl
use warnings;
use strict;
use Win32::OLE;

#<

do "excel-all-cells.pl";

#>

sub silly_transform
{
    my (undef, $value, $row, $col) = @_;
    if ($value =~ /^\d+/) {
        return $value*99;
    }
    return;
}

my $excel = Win32::OLE->GetActiveObject('Excel.Application') ||
    Win32::OLE->new('Excel.Application', 'Quit')
    or die "Could not start Excel: ".Win32::OLE->LastError()."\n";
$excel->{Visible} = 1;
my $file = 'C:\\Users\\ben\\Desktop\\test.xls';
die "File '$file' not found" unless -f $file;
$excel->Workbooks->Open ($file)
    or die "Could not open '$file': ".Win32::OLE->LastError()."\n";
my $sheet = $excel->ActiveSheet;
die "No sheet" unless $sheet;
apply_operation_to_all_cells ($excel, $sheet, \&silly_transform);