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

Hi I am working to write a script that reads an excel and copies to hash variable but i am getting some errors while using it..Please guide me

#!usr/bin/perl;
use Spreadsheet::ExcelHashTable;
my $excel_table  = Spreadsheet::ExcelHashTable->new("Ping.xls");
$excel_table->parse_table("Report", "TestCase ") ;
my $excel_hash = $excel_table->get_table("TestCase");
#print "$execl_hash\n";


$excel_table->set_write_xls("Employee_new.xls");
        $excel_table->write_excel( "sheet1", "Employee" );

[download]

Here is the error i am getting

    ERROR: No Cell found with <A > value in <Ping.xls : Report, Make S
+ure it is Valid Excel Table

Died at /usr/local/share/perl/5.12.4/Spreadsheet/ExcelHashTable.pm lin
+e 51.

[download]

Replies are listed 'Best First'.
Re: Unable to use Spreadsheet::ExcelHashtable module
by keszler (Priest) on Oct 24, 2011 at 10:35 UTC

    Your example code and error message don't seem to match. If parse_table was given "TestCase " the error should have said ERROR: No Cell found with <TestCase >....

    Regardless, parse_table calls the internal function _search_var, passing your search term. Neither routine modifies the search term in any way. It then checks every cell for a match like this:

    if ( defined $cell ) {
  my $val =  $cell->value;
  if ($val =~ /\s+$/ ) { $val =~ s/\s+//g; }
  if ( $val eq "$var") { return "$row $col"; }
}

    [download]
    Note that if the cell has trailing white space then all white space is stripped from $val before comparing with your search term in $var. It can never find "TestCase ".
[reply]
[d/l]
[select]

      Thank u very much, it worked for me, I want to retreive values from $excel_hash

      for (keys %{$excel_hash}) { print "$_\n"; }

      Above is printing the values but i am unable to interprete it properly..please explain me..

[reply]
[d/l]

        %{$excel_hash}: treat the variable $excel_hash as a hash. perlreftut perlref perldsc
        (keys %{$excel_hash}): return a list of keys for the given hash. keys
        for (keys %{$excel_hash}) {: loop over the hash keys, setting $_ (by default because no variable was given to for) to each key in turn. For Loops
        for (keys %{$excel_hash}) { print "$_\n"; }: print each hash key (note: not the value, just the key).
        for (keys %{$excel_hash}) { print "key:$_ = $excel_hash->{$_}\n"; }: print keys and values.

        For a complete view of the structure of $excel_hash, try:

        use Data::Dumper;
print Dumper($excel_hash);

        [download]

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom