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

I have a perl script that parses an Excel file, do data and formating modifications and then saves the xls file using Spreadsheet::ParseExcel::SaveParser.

Everything works smooth until I use a xls file with more that 2 sheets.

Say I have a xls file input.xls with sheets named: sheet1,sheet2,sheet3,sheet4,sheet5. (size = 245k).
When I run this code:

------------------- Cut --------------------------
$oExcel = new Spreadsheet::ParseExcel;
$oBook = $oExcel->Parse('/tmp/input.xls');

for(my $iSheet=0; $iSheet < $oBook->{SheetCount};    $iSheet++) {
    $oWkS = $oBook->{Worksheet}[$iSheet];
    
    print "Sheet: " . $iSheet . "\n";

    print "Name: ".$oWkS->{Name}."\n";

    print "MaxCol: ".$oWkS->{MaxCol}."\n\n";
}

[download]
------------------ Cut --------------------------
I get the following output:
----------------------------
Sheet: 0
Name: sheet1
MaxCol: 24

Sheet: 1
Name: sheet2
MaxCol: 18

Sheet: 2
Name: sheet3
MaxCol:

Sheet: 3
Name: sheet4
MaxCol:

Sheet: 4
Name: sheet5
MaxCol:
-------------------------------------

From sheet3 everything starts to disapear, MaxCol is not set and there are no data being parsed.

I know Spreadsheet::ParseExcel can't handle too big files, but my file is 245k big. Am I doing something wrong?

Any help will be very welcome.

Replies are listed 'Best First'.
Re: Spreadsheet::ParseExcel Multiple Sheets behaving strangely.
by jmcnamara (Monsignor) on Jul 22, 2002 at 16:26 UTC

    I cannot say what is going wrong. But as a sanity check I created a 246k file with 17 worksheets and it was parsed correctly by your program.

    Perhaps, there is some entity on "sheet3" that is causing the problem. Try deleting "sheet3" and run the program again and/or try deleting the sheets preceding it.

    --
    John.

[reply]
      Thanks!

      This is what I think happened:

      The xls file contained losts of formulas and reverences between diffent sheets in the same file. Spreadsheet::ParseExcel only reads the value that was previously computed by excel and not the formula itself.

      When I used Spreadsheet::ParseExcel::Saveparser that interacts with Spreadsheet::WriteExcel it could not write the formulas to the xls file and I got a mixed up xls file with some of the other sheets data apearing on one sheet.

      I supose I must wait for Spreadsheet::ParseExcel to support formulas properly. I wanted to do this in Linux, but now I suppose I must use Win32::OLE.

      Thanks again.
[reply]

Back to Seekers of Perl Wisdom