473,373 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,373 software developers and data experts.

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 42608
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,509 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

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

Similar topics

17
by: John Hunter | last post by:
I have a largish data set (1000 observations x 100 floating point variables), and some of the of the data are missing. I want to try a variety of clustering, neural network, etc, algorithms on the...
6
by: Hari Om | last post by:
Here are the details of my error log files: I execute the command and get following message at console: ---------------------------------------------------------------------- ../sqlldr...
6
by: RR | last post by:
Normally when I run a query, each record is displayed in a row, and the colums contain the field titles. Is there a way to switch this and have each record in a column, and the names of the...
3
by: | last post by:
I wrote a class in VB.NET to export the contents of a datagrid to Excel. It works perfectly on my machine, but it fails on my customers' PCs that have identical versions of Win XP (SP1) and Excel...
0
by: Robert Bravery | last post by:
HI All How can I get the data aschema from an excel worksheet. Keeping in mind that the actuall data might not be the first few rows, so actuall only start at row 60 or 100. I have set a name...
1
by: macupryk | last post by:
I am not sure how to get a column from a datatable, I cannot use foreach. DataColumn dataColumn = DataColumn TEXTDT.Columns.ColumnName.Trim(); if (rows < TEXTDT.Rows.Count) // this will...
3
by: marianowic | last post by:
Hello. I'm trying to get a real number of used (where is any data) rows in a Excel file in a C# code. I found some examples abount Excel.Range but it still isn't good. Does anyone have any ide...
1
by: Bonzs | last post by:
I have troule with this macro... geting the used rsnge... Public strName As String, ws As Worksheet Sub Test() Workbooks.Open Filename:= _ "C:\Documents and Settings\User\Desktop\IT...
2
by: jusking | last post by:
I have an excel(2002) worksheet with 5 columns and 800 rows with random cells values. I would like to count how many cells in the worksheet have values like A1 & A2. For example, if A1=3, A2=11,...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.