How do I determine the number of rows in a spreadsheet?

[Jerry Shaver]

Simple question:

I import a spreadsheet and I need to read the data into a program to act on it. How do I know the total number of rows, or how do I know when to stop reading rows before I cause a range exception?

I''m using the sheet.Range[i,j].text approach to reading through the cells in the worksheet.

Thanks in advance for your good help!

[Administrator]

Hi Jerry, Here is the code to find the end row of the rows in the used range.

int row=sheet.UsedRange.Row

Here is the code to find the end column of the columns in the used range.

int col=sheet.UsedRange.Column

Please take a look at the attached sample and let me know if you have any queries. Sample:Readingspreadsheet.zip BestRegards, Bharath

[Jerry Shaver]

Bharath, Thanks for the sample, it explains everything: for(int row=sheet.UsedRange.Row;row<=sheet.UsedRange.End.Row;row++) { for(int col=sheet.UsedRange.Column;col<=sheet.UsedRange.End.Column;col++) { sheet1.Range[row,col].Text=sheet.Range[row,col].Text; } } >Hi Jerry, > >Here is the code to find the end row of the rows in the used range. >

>int row=sheet.UsedRange.Row
>

> >Here is the code to find the end column of the columns in the used range. >

>int col=sheet.UsedRange.Column
>

> >Please take a look at the attached sample and let me know if you have any queries. > >Sample:Readingspreadsheet.zip > >BestRegards, > >Bharath

[Aymiee Lee]

I have noticed that on several occasions:

Dim rowend = sheet.UsedRange.End.Row

sheet.UsedRange.End.Row does not return the correct end row. I have an example of a sheet that has 124 as the end row. But it is being reported as 132 as the end row. See attached. Can you tell why this is happening?

>Hi Jerry,
>
>Here is the code to find the end row of the rows in the used range.
>

>int row=sheet.UsedRange.Row
>

>
>Here is the code to find the end column of the columns in the used range.
>

>int col=sheet.UsedRange.Column
>

>
>Please take a look at the attached sample and let me know if you have any queries.
>
>Sample:Readingspreadsheet.zip
>
>BestRegards,
>
>Bharath

[Yavanaarasi G]

Hi Mia,

We regret for the delayed response.

1.sheet.UsedRange.End.Row does not return the correct end row.

UsedRange property is used to get the range of used cells on a worksheet. so it determines the actual used range.

2.I have an example of a sheet that has 124 as the end row. But it is being reported as 132 as the end row. Can you tell why this is happening?

Normally,sheet.UsedRange is used to get the used range of the work sheet. If you want to get the total length of the sheet, you should use IWorksheet.Rows.Length to get the total length.



//To get the total length
int length=sheet.Rows.Length.ToString();



Please try the above code and if still the issue exists, could you please send your file in which the issue occurs so that we could sort out the cause of the issue with that file and provide you a solution?

Regards,
Yavana.G