Solved

C#.Net - Excel Last cell in row

Posted on 2011-03-16
9
2,053 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
 
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
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.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now