Solved

C#.Net - Excel Last cell in row

Posted on 2011-03-16
9
2,305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
LVL 36

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 36

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

691 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