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

hello i have problem in reading an excel file

i have tried too much but still not getting the solution

i want to open an excel file and read data from that but unfortunately it will shows me that no data found where i can see data in excel.

note that i want to open file with .xls extension and i have ms office 2013

when i try to open file in excel it shows me dialogue box where i have to click on yes then only it will display data. 

 #!/usr/bin/perl -w

    use strict;
    use Spreadsheet::ParseExcel;

    my $parser   = Spreadsheet::ParseExcel->new();
    my $workbook = $parser->parse('Book1.xls');

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

    for my $worksheet ( $workbook->worksheets() ) {

        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 ) {

                my $cell = $worksheet->get_cell( $row, $col );
                next unless $cell;

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

[download]

Replies are listed 'Best First'.
Re: excel file read
by marto (Cardinal) on Mar 24, 2015 at 09:49 UTC

    I run this code from the POD and get the expected output, given the Excel 97 - 2003 formatted file I used:

    Row, Col    = (0, 0)
Value       = 1
Unformatted = 1

Row, Col    = (0, 1)
Value       = a
Unformatted = a

Row, Col    = (1, 0)
Value       = 2
Unformatted = 2

Row, Col    = (1, 1)
Value       = b
Unformatted = b

Row, Col    = (2, 0)
Value       = 3
Unformatted = 3

Row, Col    = (2, 1)
Value       = c
Unformatted = c

Row, Col    = (3, 0)
Value       = 4
Unformatted = 4

Row, Col    = (3, 1)
Value       = d
Unformatted = d

Row, Col    = (4, 0)
Value       = 5
Unformatted = 5

Row, Col    = (4, 1)
Value       = e
Unformatted = e

    [download]

    Changing the format to Excel xlsx returns the same error you report, this suggests you are using an unsupported format (xlsx) with the file extension xls. Note the second line of the module description:

    "The module cannot read files in the Excel 2007 Open XML XLSX format. See the Spreadsheet::XLSX module instead."

[reply]
[d/l]
Re: excel file read
by Corion (Patriarch) on Mar 24, 2015 at 09:47 UTC

    What is the message that Excel shows you?

    My guess is that the file is corrupt. If it is corrupt, then your only approach is to open it in Excel and save it again so the file gets repaired.

    You may want to look at Win32::OLE to automate this if you have to deal with broken Excel files more often.

[reply]

      how can i perform save as operation using script. if i want to save my .xls file as .xlsx

[reply]
[reply]

        Websites lie. Period.

        I know a website that has a [Download as XLS] button that saves the data in a file called data_20150324.csv, which is actually HTML with a huge table. YMMV


        Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
Re: excel file read
by runrig (Abbot) on Mar 24, 2015 at 19:58 UTC
    Sometimes xlsx files are misnamed as xls files. You can tell by opening the file in a text editor. If the first two characters are 'PK' (meaning it's a PK zip file which is what xlsx files are), then it's an xlsx file. I use Spreadsheet::ParseExcel::Stream partly because it will correctly open misnamed xls(x) files. It still won't open if the file is really a csv or some other format.
[reply]
Re: excel file read
by Laurent_R (Canon) on Mar 24, 2015 at 18:59 UTC
    This has already been suggested to you by Corion, but since you don't seem to have noticed, try to open the file with a text editor (preferably a hex capable editor). If you see HTML or CSV content (or XML, JSON, etc.), then you know you simply have to use another parsing module. That's really the first step, because you need to know what type of file you're really working with. If it looks like binary content, then you need to further investigate.

    Je suis Charlie.
[reply]
Re: excel file read
by vasarabharat (Novice) on Mar 24, 2015 at 09:39 UTC

    it shows me error message like

    "No Excel data found in file."

[reply]

      No Excel data found in file

      The "no data found" error indicates some problem with the file format. I saw this error with pseudo-Excel files such as Html or CSV files that have an xls extension.

      All is well. I learn by answering your questions...
[reply]

        can u tell me how to handle with that

        i think i also have the same problem..

        coz when i try to save as the file type it by default is web page instance

        if it is not actual xls file then how can i read the data from it like excel?

        plz help me

[reply]
A reply falls below the community's threshold of quality. You may see it by logging in.

Back to Seekers of Perl Wisdom