Hello all. I have recently been porting OLE based perl scripts to a Unix environment and have hit a snag when dealing with Spreadsheet::ParseExcel. The module returns a maximum row of 2954 when some of the spreadsheets I need to parse are well above 5,000 rows.

I'm hoping someone knows of a fix or work around. I have tried ignoring the MaxRow but the module will not retrieve values beyond that point. I've also snooped around the module its self and I am unable to find this value hard coded in anywhere.

I am open (and grateful) for any unix compatable work around, but preferably perl. I am not sure what is dictating the row number cap and was surprised to find no results on google of people with similiar problems.

Update, this is some of the parsing code

    for($r = $wks->{MinRow}; $wks->{MaxRow} && $r <= $wks->{MaxRow}; $
+r++) {
        if(cell($wks,$r,0) !~ /(group|category|project|client)/i)     
+ { next; }
        if(cell($wks,$r,0) =~ /group/i)    { $group=    cell($wks,$r,1
+); next; }
        if(cell($wks,$r,0) =~ /category/i) { $category= cell($wks,$r,1
+); next; }
        if(cell($wks,$r,0) =~ /project/i)  { $project= cell($wks,$r,1)
+; $client= "0"; }
        if(cell($wks,$r,0) =~ /client/i)   { $client=  cell($wks,$r,1)
+; }

[download]
# Utility function to safely look inside of a Cell
sub cell {
    my ($Wks, $r, $c)= @_;
    if (defined $Wks->{Cells}[$r][$c]) {
        my $val= $Wks->{Cells}[$r][$c]->Value;
        $val =~ s/ \(/\(/gi;
        $val =~ s/formerly/Frm\./gi;
        $val =~ s/\s\s/ /gi;
        return $val;
        }
    return "";
    }

[download]

Update 3 days later

One of the perlmonks was good enough to give me some code on his scratch pad that seemed to work, but now I have a WEIRD problem! Note the following code:

#!/usr/bin/perl -w 
use strict;

use Spreadsheet::ParseExcel;

my $parse_excel = Spreadsheet::ParseExcel->new();

    my $workbook1= $parse_excel->Parse('data/DASNYFootnotes.xls');
        print "Max row : ", 1 + ${$workbook1->{Worksheet}}[0]->{MaxRow
+}, "\n";

    my $workbook2= $parse_excel->Parse("data/dasnyFootnotes.xls");
        print "Maxrow b: ", 1 + ${$workbook2->{Worksheet}}[0]->{MaxRow
+}, "\n";

[download]

Gives the output

Max row : 3249
Maxrow b: 2955

and if I switch the order, maxrow b is still only 2955!!! I don't understand? They look identical

It was my own stupid fault! Some how two files had the same name with different case in my data folder! Sorry!

In reply to Spreadsheet::ParseExcel MaxRow is maxing out at 2954 by skelooth

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.