Programmatically getting the last filled excel row using C#

Question

I am trying to get the last row of an excel sheet programatically using the Microsoft.interop.Excel Library and C#. I want to do that, because I am charged with looping through all the records of an excel spreadsheet and performing some kind of operation on them. Specifically, I need the actual number of the last row, as I will throw this number into a function. Anybody have any idea how to do that?

Answer 1

Couple ways,

using Excel = Microsoft.Office.Interop.Excel;  Excel.ApplicationClass excel = new Excel.ApplicationClass(); Excel.Application app = excel.Application; Excel.Range all = app.get_Range("A1:H10", Type.Missing); 

OR

Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range range = sheet.get_Range("A1", last);  int lastUsedRow = last.Row; int lastUsedColumn = last.Column; 

Answer 2

This is a common issue in Excel.

Here is some C# code:

// Find the last real row nInLastRow = oSheet.Cells.Find("*",System.Reflection.Missing.Value,  System.Reflection.Missing.Value, System.Reflection.Missing.Value,    Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;  // Find the last real column nInLastCol = oSheet.Cells.Find("*", System.Reflection.Missing.Value,     System.Reflection.Missing.Value,System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByColumns,Excel.XlSearchDirection.xlPrevious,    false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Column; 

found here

or using SpecialCells

Excel.Range last = sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing); Excel.Range range = sheet.get_Range("A1", last); 

[EDIT] Similar threads:

• VB.NET - Reading ENTIRE content of an excel file
• How to get the range of occupied cells in excel sheet