Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Error getting number of rows in excel.

by monir (Acolyte)
on Oct 11, 2002 at 20:35 UTC ( #204655=perlquestion: print w/replies, xml ) Need Help??

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

Hi,

I am writing a perl script to fetch data from an Excel file.

I used Win:OLE interface. When I go to get the number of rows in the active sheet, I get a large number 55536 (max rows possible), instead of the actual total number of rows.

Here is the snippet I used:

$rowCount = $actvSheet->Rows->{'Count'};

Can anybody help?

Thanks,

-Monir

Replies are listed 'Best First'.
Re: Error getting number of rows in excel.
by cluka (Sexton) on Oct 11, 2002 at 22:10 UTC
    I'm a business analyst who does this kind of thing for a living... I pull reports from legacy systems and format them into Excel reports. I know, my life sucks.

    Fortunately for me, I discovered perl and now do all my data munging outside of Excel. However, I used to write VBA macros to do the job.

    If you want to find the last row in a range of data, the VBA construct is the following (assuming your block of data starts in column A):

    lastRow = activeSheeet.range("A65536").end(xlUp)

    This translates into perl as...

    $last_row = $active_sheet->Range('A65536')->End(-4121)

    (This assumes that the value of the 'xlUp' constant in VBA is constant across different copies and versions of Excel. To check, open the macro editor in Excel (F11), open the "Immediate" window under the View menu and type xlUp to obtain its value.

    Alternatively, you could figure out what I could not and determine how to bring those constants into perl. I've yet to figure out how to pull that off - I know they're in the OLE Excel object, I just don't know how to reference them.

    Anyone?

    Hope that helped.

    Cluka

      You can import the OLE constants using Win32::OLE::Const:
      use Win32::OLE::Const 'Microsoft Excel'; print xlUp; # gives -4162

      --
      John.

Re: Error getting number of rows in excel.
by jsprat (Curate) on Oct 11, 2002 at 21:03 UTC
    You want to use the Rows property of the UsedRange (a range object).

    Should look something like this:

    $rowCount = $actvSheet->UsedRange->Rows->{Count};

      Thanks, this is what I wanted, the number of actuallly used rows.
Re: Error getting number of rows in excel.
by Mr. Muskrat (Canon) on Oct 11, 2002 at 21:11 UTC
      Thanks.

      As a matter of fact, I even deal with this very question under the section Finding the Last Column and Row, but I'll reprint it here:
      my $LastRow = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row};

      C-.

      ---
      Flex the Geek

Re: Error getting number of rows in excel.
by VSarkiss (Monsignor) on Oct 11, 2002 at 21:06 UTC

    Well, you're asking for the number of rows in the sheet, and that's what it's giving you.

    Do you mean you want only rows with something particular in them? Then you either need to loop through the region looking for that something particular, or define a region that you're interested in and count the rows in that.

    In other words, all the work is in understanding the Excel object model and how the methods and properties work -- it has little to do with Perl or Win32::OLE. If you haven't already, go to http://www.roth.net and read up.

Re: Error getting number of rows in excel.
by Schuk (Pilgrim) on Oct 13, 2002 at 00:19 UTC
    Me is also working on Excel sheets at the moment.. But I am using Spreadsheet::ParseExcel (it was posted somewhere here)

    a simple "$oWkS->{MaxRow}" or "$oWkS->{MinRow}" does what you want

    I like the way I can handle the excel sheet because its quite easy to use and doesnt has any strange features I am not understanding anyway ;-) In fact I DONT want to know more about Excel stuff than rows and columns. Real Database for president!!

    Parsing 20mb big excel sheets is no fun at all :-(
Re: Error getting number of rows in excel.
by JaWi (Hermit) on Oct 11, 2002 at 20:45 UTC
    I've never used it before, but isn't it possible to retrieve a column with data as list? In that case you could simply do something like: $rowCount = scalar @data;. However, I've no experience with OLE programming with Perl, so I could be mumbling utter nonsense... :-)

    Hope it helps...

    -- JaWi

    "A chicken is an egg's way of producing more eggs."

      No, that won't work (without additional code, at least). OLE collections are strange beasties that don't map nicely into Perl data structures. I wish they would....

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://204655]
Approved by VSarkiss
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (5)
As of 2019-10-16 10:21 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?