?
Solved

C#.Net - Excel Last cell in row

Posted on 2011-03-16
9
Medium Priority
?
2,411 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 750 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

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

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

Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

765 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