|
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
| [reply]
[d/l]
[select] |
|
|
use Win32::OLE::Const 'Microsoft Excel';
print xlUp; # gives -4162
--
John.
| [reply]
[d/l] |
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}; | [reply]
[d/l] |
|
|
Thanks, this is what I wanted, the number of actuallly used rows.
| [reply] |
Re: Error getting number of rows in excel.
by Mr. Muskrat (Canon) on Oct 11, 2002 at 21:11 UTC
|
| [reply] |
|
|
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 | [reply]
[d/l] |
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.
| [reply] |
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 :-( | [reply] |
Re: Error getting number of rows in excel.
by JaWi (Hermit) on Oct 11, 2002 at 20:45 UTC
|
| [reply]
[d/l] |
|
|
| [reply] |
