[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

C#.Net - Excel Last cell in row

Posted on 2011-03-16
9
Medium Priority
?
2,546 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

656 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