Solved

C#.Net - Excel Last cell in row

Posted on 2011-03-16
9
2,087 Views
Last Modified: 2013-12-17
Hi ~

I'm fairly new to C# but in VBA, I was able to get the last active cell in a column by:
Range(Selection, Selection.End(xlDown)).Select

Open in new window


So, how can I do the same in C#?  I think I've started on the right foot, but not sure what to do next.
xlApp = new Excel.ApplicationClass();            
xlWorkBook = xlApp.Workbooks.Open(strPath + strFile, 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);

Open in new window


Thank you in advance to all the helpers.
0
Comment
Question by:gcastong
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 20

Expert Comment

by:Daniel Van Der Werken
ID: 35151735
Range range = xlWorkSheet.UsedRange;
0
 

Author Comment

by:gcastong
ID: 35153243
Thanks @Dan7el.  

I tried that with the following code to get the number of active rows and I get the wrong number.  For example, if there are 10 rows populated, the output shows around 130.  Is there another step that I'm missing?  Anyway, here's the code that I'm using:
Excel.Range xlTestRange;
xlTestRange = xlWorkSheet.UsedRange;

int NumberOfRows = xlTestRange.Rows.Count;

Open in new window

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35153263
Try:

int NumberOfRows = xlTestRange.Cells(1, 1).End(Excel.XlDirection.xlDown).Row;

Kevin
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 35

Expert Comment

by:Miguel Oz
ID: 35153680
If what you need is the current selection, then you need the xlApp.Selection object:
Excel.Range selection = xlApp.Selection as Excel.Range;
int NumberOfRows = selection.Rows.Count;

Check my vsto tutorial for more examples:
http://www.eggheadcafe.com/tutorials/aspnet/ff2d1d4b-aedf-4d14-9e60-39a86ccab5d6/using-vsto-addin-to-auto.aspx
0
 

Author Comment

by:gcastong
ID: 35156387
@zorvek:  I'm getting the following error:
'object' does not contain a definition for 'End' and no extension method 'End' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?
It seems that it's referring to the "Cells" method.

@mas_oz2003: I tried your suggestion but I'm only a getting a row count of 1, which I know is wrong.  This is what I have so far:
xlApp = new Excel.ApplicationClass();            
xlWorkBook = xlApp.Workbooks.Open(strResAnWBPath + strResAnWB, 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);

Excel.Range selection = xlApp.Selection as Excel.Range;
int NumberOfRows = selection.Rows.Count;

Open in new window


Let me know if I missed a step.
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 250 total points
ID: 35159095
It appears Cells is treated as an array versus a property. Try:

int NumberOfRows = xlTestRange.Cells[1, 1].End(Excel.XlDirection.xlDown).Row;

Kevin
0
 
LVL 35

Expert Comment

by:Miguel Oz
ID: 35161026
Ok, first of all, you a count =1 because you have not done any selection yet. Thus the default selection value in Excel is A1 (only one cell, thus one row)
If you read my article on the link:
http://www.eggheadcafe.com/tutorials/aspnet/ff2d1d4b-aedf-4d14-9e60-39a86ccab5d6/using-vsto-addin-to-auto.aspx 
You will find this code works OK when you have a defined selection.
Let us know how you are interfacing with Excel. Is it VSTO project, desktop project, COM shim, etc?
0
 

Author Comment

by:gcastong
ID: 35207605
@mas_oz2003:  It is a VSTO project.  I liked your suggestion, but I was getting really weird numbers when usinig it.  I later realized that the original excel file had cells that a background color in it.  Therefore, it would affect the value returned.  I only wanted to the cells that had numeric data in it, but the .UsedRange would be thrown off by the change in color.  Thanks for the suggestion though.

@zorvek:  I tried your code and it still didn't work.  I modified it a bit and it worked fine.  
The only thing is that I had set up a start range or cells for my code to work.  Here's what I've done:
Excel.Range xlStartRange = (Excel.Range)xlWorkSheet.Cells[1, 1]; // Xplicitlly state the first cell of data
int NumberOfRows = xlStartRange.get_End(Microsoft.Office.Interop.Excel.XlDirection.xlDown).Row;

Open in new window

0
 

Author Closing Comment

by:gcastong
ID: 35207628
The solution provided didn't show me exactly what needed to be done.  It was definitely a great starting point.  I found my solution through other resources.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question