a.alandkar has asked for the wisdom of the Perl Monks concerning the following question:

Hello, I am trying to access xls file but its giving me an error. The code is as below. 

#!/usr/bin/perl –w

    use strict;
 #   use Spreadsheet::ParseExcel;
    use Spreadsheet::XLSX;
    
    my $FileName = "C:/Users/Ambar.Alandkar/Desktop/Ambar/Perl-Test/Pe
+rl-Test.xls";
    my $parser   = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->parse($FileName);

    die $parser->error(), ".\n" if ( !defined $workbook );

    # Following block is used to Iterate through all worksheets
    # in the workbook and print the worksheet content 

    for my $worksheet ( $workbook->worksheet(0) ) 
    {
            print("Test");
        # Find out the worksheet ranges
        my ( $row_min, $row_max ) = $worksheet->row_range();
        my ( $col_min, $col_max ) = $worksheet->col_range();

        for my $row ( $row_min .. $row_max ) 
        {
            for my $col ( $col_min .. $col_max ) 
            {

                # Return the cell object at $row and $col
                my $cell = $worksheet->get_cell( $row, $col );
                next unless $cell;

                print "Row, Col    = ($row, $col)\n";
                print "Value       = ", $cell->value(),       "\n";

            }
        }
    }

[download]

Please help

Replies are listed 'Best First'.
Re: No Excel data found in file
by Athanasius (Archbishop) on Aug 22, 2014 at 09:53 UTC

    Hello a.alandkar,

    First-off, in the code shown the line use Spreadsheet::ParseExcel; has been commented out; but it is still referenced in the line my $parser = Spreadsheet::ParseExcel->new();. So I uncommented that use statement and commented out the following one instead.

    Since I have Excel 2007, my first attempt to write a test spreadsheet resulted in a .xlsx file, and even with the correct value for $FileName the script terminated in the error message: No Excel data found in file. Adding a couple of print statements, I was able to confirm that that error message is generated by the line die $parser->error(), ".\n" if ( !defined $workbook );. So I strongly suspect that your problem results from the failure of the line my $workbook = $parser->parse($FileName); (i.e., that $workbook is being assigned undef). Most likely your Excel file is invalid in some way. Use the error method to get information on the specific cause of the failure (see error of Spreadsheet::ParseExcel).

    One further note: the loop beginning for my $worksheet ( $workbook->worksheet(0) ) can only ever have one iteration, since the worksheet method returns a single worksheet. I think you meant to write: for my $worksheet ( $workbook->worksheets() ) which will iterate through all the worksheets in the Excel file. Apart from this issue, your code is correctly reading my test .xls file, as expected.

    Hope that helps,

    Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]

      I have tried uncommenting the use Spreadsheet::ParseExcel; and running the code but now its giving me an error saying: Can't call method "worksheet" on an undefined value

[reply]

        a.alandkar:

        That means that the parse function failed. The problem might be that the .xls file isn't actually an Excel spreadsheet. I'd run the file command on the spreadsheet to verify it. Excel is (in my opinion) too lenient of file extensions--it will parse an HTML or CSV file with no problem even if it's got an .xls extension.

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

[reply]
[d/l]
[select]
Re: No Excel data found in file
by aitap (Curate) on Aug 22, 2014 at 10:01 UTC

    You forgot to specify the error message you got.

    It probably says: Can't locate object method "new" via package "Spreadsheet::ParseExcel" (perhaps you forgot to load "Spreadsheet::ParseExcel"?). Consulting perldiag (or feeding this message to splain, or adding use diagnostics;) yields:

    (F) You called a method correctly, and it correctly indicated a package functioning as a class, but that package doesn't define that particular method, nor does any of its base classes. See perlobj.
    You commented out the code which loads Spreadsheet::ParseExcel, but you continue to use this module. You need to consult the documentation of Spreadsheet::XLSX and change your program accordingly.

[reply]
[d/l]
Re: No Excel data found in file
by Tux (Canon) on Aug 22, 2014 at 10:50 UTC
[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom