469,326 Members | 1,452 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Getting count of number of rows and columns in excel worksheet

guys,

I have written a perl script using Win32::OLe module.
I was able to read it completly.
but I have to specify the number of rows in excel sheet as command line option.
I want to know how to retrieve number of rows of the excel sheet and number of columns also.

i tried to retrieve the properties using the each methode.

see below
Expand|Select|Wrap|Line Numbers
  1. $book=$excel->Workbooks->open("demo.csv");
  2. while(my ($key,$value)=%$book)
  3. {
  4. print "$key:$value\n";
  5. }
  6.  
but it is giving some hash value and also thows exception error.

Can Somebody please help me out to resolve this issue.

It will be of great help!!!!!


With Regards,
deep022in
Sep 25 '06 #1
7 41659
robin
6
Hi deep022,

So you have a CSV file right?

How about simple line counting as you read it in?

For the columns you can count the commas in the first row...
Sep 26 '06 #2
Hi deep022,

So you have a CSV file right?

How about simple line counting as you read it in?

For the columns you can count the commas in the first row...
===
Thanks Robin for the help.

bu thte CSV file is not comma seperated.

i am reading it thorugh my script.
Do you haver any idea about how to retrieve the number of rows from the excel sheet.

I am stuck with that in my script.
Currently I have asked users to pass the number of rows through command propmpt.

but as it will not make the script userfriendly.

I need the synatax of how to retrieve the property value.
Sep 27 '06 #3
===
Thanks Robin for the help.

bu thte CSV file is not comma seperated.

i am reading it thorugh my script.
Do you haver any idea about how to retrieve the number of rows from the excel sheet.

I am stuck with that in my script.
Currently I have asked users to pass the number of rows through command propmpt.

but as it will not make the script userfriendly.

I need the synatax of how to retrieve the property value.
============

Guys i got the syntax for getting number of rows and columns in the excel sheet.
it is as folow
Expand|Select|Wrap|Line Numbers
  1. $Book->Rows->{'Count'};
  2. $Book->Cloumns->{'Count'};
  3.  
where $Book is a workbook object.

but it retruns total rows and columns in the excelsheet
i.e 65553 and 256

I want to only find used number of rows and columns.
there is a preoperty called UsedRange of Worksheet object.

i am trying to access it but it is returning currently the value in HASH.

Does any body has any idea about it.

Help will be appriciated.
Sep 28 '06 #4
According to some code i've found this can be achieved with the following syntax:

Expand|Select|Wrap|Line Numbers
  1. # Find Last Column and Row
  2. my $LastRow = $Sheet->UsedRange->Find({What=>"*",
  3.     SearchDirection=>xlPrevious,
  4.     SearchOrder=>xlByRows})->{Row};
  5.  
  6. my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
  7.                   SearchDirection=>xlPrevious,
  8.                   SearchOrder=>xlByColumns})->{Column};
  9.  
The whole code can be found at http://www.ngbdigital.com/perl_ole_excel.html
Sep 29 '06 #5
Here's some more info for the Excel OLE stuff:

Worksheet Properties (Microsoft.Office.Tools.Excel )
Sep 29 '06 #6
Why don't you try this 1:

Expand|Select|Wrap|Line Numbers
  1. my $Tot_Rows= $Sheet->UsedRange->Rows->{'Count'};
  2.  
Dec 17 '07 #7
numberwhun
3,503 Expert Mod 2GB
deep022in and ulhasdeshmukh,

I have edited both of your posts (2 for deep022in) and added the necessary code tags.

Please be sure and use them the next time you post code in the forums.

Regards,

Jeff
Dec 17 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

17 posts views Thread by John Hunter | last post: by
6 posts views Thread by RR | last post: by
reply views Thread by Robert Bravery | last post: by
3 posts views Thread by marianowic | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.