Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
582 views
in Technique[技术] by (71.8m points)

c# - Get Last non Empty Cell of Excel Column Programatically

I have excel sheets that always look like below

A |  B  |  C
  |     |    
  |     |  something here maybe 
1 |  x  |  k
2 |  y  |  l
3 |  z  |  n
  |  j  |  
  |  j  |  
   ...
  |     |  

I want to get the value of the last non empty cell in A column. A column always starts at A6 and goes for a while and after than it is empty but column B continues for a while. This has the effect that if I use the code below:

sh = app.Workbooks.get_Item("Workbook1.xlsx").Worksheets.get_Item("Sheet1");
fullRow = sh.Rows.Count;
lastRow = sh.Cells[fullRow, 1].End(Excel.XlDirection.xlUp).Row;

I get as last row the last row that has B non empty. Which might be even 50 rows below the place where A starts being null. Is there a way to do it with C# without iterating over all values?

EDIT

It turns out that Column A is based on column C so I should check for C. As it turns out C is yielding the same result with A maybe because of the function. Luckily there is a column D that does not have the same issue so if I use the code above changing 1 for 4 it works likes a charm.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

EDIT: Sorry, I initially misunderstood the question, thanks KekuSemau for clearing that up!

Try:

sh.Range["A1", sh.Cells[1,sh.Rows.Count]].End(Excel.XlDirection.xlDown).Value;

Also, if you know there will be no empty cells in the column you could use CountA:

var usedRows = xlApp.WorksheetFunction.CountA(xlWorkSheet.Columns[1]);

Source


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...