Getting count of number of rows and columns in excel worksheet

[deep022in]

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

$book=$excel->Workbooks->open("demo.csv");
while(my ($key,$value)=%$book)
{
print "$key:$value\n";
}
 

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

[robin]

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...

[deep022in]

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.

[deep022in]

===
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

$Book->Rows->{'Count'};
$Book->Cloumns->{'Count'};
 

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.

[thurban]

According to some code i've found this can be achieved with the following syntax:

# Find Last Column and Row
my $LastRow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};
 
my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
                  SearchDirection=>xlPrevious,
                  SearchOrder=>xlByColumns})->{Column};
 

The whole code can be found at http://www.ngbdigital.com/perl_ole_excel.html

[thurban]

Here's some more info for the Excel OLE stuff:

Worksheet Properties (Microsoft.Office.Tools.Excel )

[ulhasdeshmukh]

Why don't you try this 1:

 my $Tot_Rows= $Sheet->UsedRange->Rows->{'Count'};
 

[numberwhun]

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