Tags: columns, completly, excel, guys, module, number, perl, programming, rows, script, sheet, specify, win32ole, worksheet, written

Getting count of number of rows and columns in excel worksheet

On Programmer » Perl

8,881 words with 7 Comments; publish: Thu, 03 Jan 2008 13:09:00 GMT; (20093.75, « »)

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

Code: ( perl )
  1. $book=$excel->Workbooks->open("demo.csv");
  2. while(my ($key,$value)=%$book)
  3. {
  4. print "$key:$value\n";
  5. }

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

All Comments

Leave a comment...

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

      #1; Thu, 03 Jan 2008 13:10:00 GMT
    • Quote:
      === Original Words ===

      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.

      #2; Thu, 03 Jan 2008 13:11:00 GMT
    • Quote:
      === Original Words ===

      ===

      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

      Code: ( perl )
      1. $Book->Rows->{'Count'};
      2. $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.

      #3; Thu, 03 Jan 2008 13:12:00 GMT
    • According to some code i've found this can be achieved with the following syntax:

      Code: ( text )
      1. # Find Last Column and Row
      2. my $LastRow = $Sheet->UsedRange->Find({What=>"*",
      3. SearchDirection=>xlPrevious,
      4. SearchOrder=>xlByRows})->{Row};
      5. my $LastCol = $Sheet->UsedRange->Find({What=>"*",
      6. SearchDirection=>xlPrevious,
      7. SearchOrder=>xlByColumns})->{Column};
      The whole code can be found at http://www.ngbdigital.com/perl_ole_excel.html
      #4; Thu, 03 Jan 2008 13:13:00 GMT
    • Why don't you try this 1:

      Code: ( perl )
      1. [B]my $Tot_Rows= $Sheet->UsedRange->Rows->{'Count'};[/B]
      #6; Thu, 03 Jan 2008 13:15:00 GMT
    • 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

      #7; Thu, 03 Jan 2008 13:16:00 GMT