in reply to Accessing values in a XSL file.

The problem is that using a cell handler calls your cell handler as each cell is read. You don't really need to do that under normal circumstances, but if you do, then try something like:

#!/usr/bin/perl -w
use strict;
use warnings;
use Spreadsheet::ParseExcel;

my %CellData; # Key1=workbook, Key2=Row, Key3=column

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

my $workbook = $parse_excel->Parse('file.xls');

# Now that we've parsed the sheet, we can access any data we like,
# So let's print cell C1 on sheet "FOO"
print $CellData{FOO}{C}{1}, "\n";

sub cell_handler {
    # Cell information passed to us
    my ($WB, $IDX, $R, $C, $Cell) = @_;

    # Build our hash keys
    my $SheetName = $WB->worksheet($IDX)->get_name();
    my $ColName = numToColumnName($C);
    my $RowName = $R;
    $CellData{$SheetName}{$ColName}{$RowName} = $Cell->{_Value};
}

sub numToColumnName {
    my $C = shift;
    my $colName = '';
    if ($C>25) {
        $colName = substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ', $C/26, 1);
        $C = $C % 26;
    }
    $colName .= substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ', $C, 1);
}

[download]

(Note: Untested!)

Of course, if you don't need to use a cell handler, you can just use the example in the first page of the documentation to access the data by sheet / row / column...

...roboticus

Replies are listed 'Best First'.
Re^2: Accessing values in a XSL file.
by Anonymous Monk on Oct 27, 2010 at 17:19 UTC
    The idea here is to read this xls file and get the values into a database, the columns will not necessarily be called C1 to C4, it will be called something else, that's why I asked about passing the column names to get is value. Any ideas?
[reply]
      I maintain Spreadsheet::ParseExcel.

      There aren't any column names in an Excel file so you can't use column names to access any data.

      --
      John.

[reply]
        I came across this example here, it should be what I am looking for.
        #!/usr/bin/perl -w

use strict;
use warnings;
use Spreadsheet::ParseExcel;

my $file = "test.xls";

use vars qw($user_name $user_number $identification);

my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($file)or die "
+Unable to open $file\n";

foreach my $page (@{$workbook->{Worksheet}}) {
print "Page $page\n";

    if ((defined $page->{MinCol}) && (defined $page->{MaxCol})) {
        foreach my $col ($page->{MinCol} .. $page->{MaxCol}) {
            if ($page->{Cells}[0][$col]->{Val} eq "User Name") {
                $user_name = $col;
                print "$user_name\n";
            }

            if ($page->{Cells}[0][$col]->{Val} eq "User Number") {
                $user_number = $col;
                print "$user_number\n";
            }

            if ($page->{Cells}[0][$col]->{Val} eq "Identification") {
                $identification = $col;
                print "$identification\n";
            }

        }
    }
    if ((defined $page->{MinRow}) && (defined $page->{MaxRow})) {
        foreach my $row ($page->{MinRow}+1 .. $page->{MaxRow}) {

            my $got_user_name = $page->{Cells}[$row][$user_name]->{Val
+};
            my $got_user_number = $page->{Cells}[$row][$user_number]->
+{Val};
            my $got_identification = $page->{Cells}[$row][$identificat
+ion]->{Val};

            print "\n This will be inserted into the database: $got_us
+er_name\n$got_user_number\n$got_identification\n";

        }
    }
}

exit;

        [download]

        Any ideas, but it looks good to me!
[reply]
[d/l]

In Section Seekers of Perl Wisdom