CODEWITHSUNDEEP
c#
Murthy
Murthy

Reputation: 1552

Get specified row count in excel using c#

I have a excel template where I have 1-100 row filled just with ID, my other column name and email is empty. 

ID   Name   Email
1
2
3
.
.
100

here I have no data in the excel when I try to get row count I get 100, but I want to get the row count of either name or email which is filled with data, How can I do that.

        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range range;         
        xlApp = new Excel.ApplicationClass();
        xlWorkBook = xlApp.Workbooks.Open(fileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
        range = xlWorkSheet.UsedRange;
        int iRowCount = xlWorkSheet.UsedRange.Rows.Count;

Upvotes: 1

Views: 7558

Answers (3)

Aineislis Cole
Aineislis Cole

Reputation: 189

One easy way is to check which rows contain some value for aither Name or Email. Use

System.Array values = (System.Array)range.Cells.Value;

after which loop through the array and check that either Name or Email are different than string.IsNullOrWhiteSpace.

Upvotes: 0

Vladimir Perevalov
Vladimir Perevalov

Reputation: 4157

You'll have to iterate over rows, because Excel will always show you the maximum used row and column number for worksheet.

Also, I would suggest you to not use Excel to read the data. And instead use some library, that can read files directly (and not depend on Excel installed). I used http://exceldatareader.codeplex.com/ quite succefully (at least for xlsx files). You may also want to download latest sources and build them your-self, because release is not very new, and there were a lot of fixes.

PS By not using Excel you will also solve the problem of performance, because using Excel as you showed in your code is very slow, and this will really matter when you start iterating over rows.

ExcelDataReader, on the other hand, will give a DataTable of data from excel worksheet, and will be able to parse it as you want in memory, which should be 100-1000 times quicker then working with excel.

Upvotes: 2

CloudyMarble
CloudyMarble

Reputation: 37576

You will have to access each Cell in your Column and check if it contains data using:

xlWorkSheet.Cells(x, y).Value

Upvotes: 0

Related Questions