Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Programmatically getting the last filled excel row using C#

Tags:

c#

excel

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?

like image 679
SoftwareSavant Avatar asked Oct 06 '11 13:10

SoftwareSavant


People also ask

How do I find the last row that contains data in Excel?

Locate the last cell that contains data or formatting on a worksheet. To locate the last cell that contains data or formatting, click anywhere in the worksheet, and then press CTRL+END.

How do you jump to the last row of data?

Ctrl-Down -- The shortcut moves the cursor to the last row with data before the first blank row that is encountered; this may be the last row in the table ideally, but only if there are not any blank rows in the table.

How do you get to the last row of a worksheet row 1048576 By pressing this shortcut?

Select this entire row by pressing the shortcut key Shift Space. Step 2: Now hold the Keys Shift & Ctrl > Press down Arrow; it will take you till the end of the last row.


2 Answers

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; 
like image 88
Priyank Avatar answered Oct 23 '22 09:10

Priyank


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
like image 24
JMax Avatar answered Oct 23 '22 09:10

JMax