Getting the last row of a given column in Excel with a formula or with VBA is quite a trivial task. It is explained here quite well 🙂
However, when we come to C#, the only “trick” that is provided by the Microsoft.Office.Interop.Excel is getting the last row in general:
|
1 2 3 4 5 6 |
static int LastRowTotal(Excel.Worksheet wks) { Excel.Range lastCell = wks.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); return lastCell.Row; } |
The question what happens, if one needs the last cell in a given Excel column, which is of course not the last cell out of all columns. Then simple reverse loop, starting from the last cell in the worksheet until the first cell with value would be of use. Something like this:
|
1 2 3 4 5 6 7 8 9 |
static int LastRowPerColumn(int column, Excel.Worksheet wks) { int lastRow = LastRowTotal(wks); while (((wks.Cells[lastRow, column]).Text == "") && (lastRow != 1)) { lastRow--; } return lastRow; } |
Both functions, LastRowPerColumn and LastRowTotal work rather well together, providing the last row per worksheet:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
namespace ExcelTest { using System; using Excel = Microsoft.Office.Interop.Excel; public class Startup { const string filePath = @"C:\Users\gropc\Desktop\Sample.xlsx"; static void Main() { Excel.Application excel = new Excel.Application { Visible = true, EnableAnimations = false }; Excel.Workbook wkb = Open(excel, filePath); foreach (Excel.Worksheet wks in wkb.Worksheets) { int lastRowA = LastRowPerColumn(1, wks); int lastRowB = LastRowPerColumn(2, wks); int lastRowC = LastRowPerColumn(3, wks); Console.WriteLine($"{lastRowA} - {lastRowB} - {lastRowC}"); } wkb.Close(true); excel.Quit(); } static int LastRowPerColumn(int column, Excel.Worksheet wks) { int lastRow = LastRowTotal(wks); while (((wks.Cells[lastRow, column]).Text == "") && (lastRow != 1)) { lastRow--; } return lastRow; } static int LastRowTotal(Excel.Worksheet wks) { Excel.Range lastCell = wks.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); return lastCell.Row; } static Excel.Workbook Open(Excel.Application excelInstance, string fileName, bool readOnly = false, bool editable = true, bool updateLinks = true) { return excelInstance.Workbooks.Open(fileName, updateLinks, readOnly); } } } |
The whole code is in GitHub.com here. Enjoy it!