Programmatically Getting the Last Filled Excel Row Using C#

Programmatically getting the last filled excel row using C#

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;

Get the last cell (column, row) of a Excel range object

This should get the first first and last cell of the range:

1. Initiate Excel.

2. Open workbook.

3. Select your range, in this case I got the used range of the active sheet.

4. Call the get_address method of the range.

5. Split the result of get_address on the colon.

6. The first value of the array resulting from the split will have the beginning cell.

7. The second value of the array resulting from the split will have the ending cell.

8. Replace the dollar signs with nothing and you will have the beginning and ending cells for the range.

   Microsoft.Office.Interop.Excel.Application excel = new Application();
   Microsoft.Office.Interop.Excel.Workbook workBook =
       excel.Workbooks.Open(fileLocation);
   Microsoft.Office.Interop.Excel.Worksheet sheet = workBook.ActiveSheet;
   Microsoft.Office.Interop.Excel.Range range = sheet.UsedRange;
   string address = range.get_Address();
   string[] cells = address.Split(new char[] {':'});
   string beginCell = cells[0].Replace("$", "");
   string endCell = cells[1].Replace("$", "");
   workBook.Close(true);
   excel.Quit();
   

If you want the last column and last row relative to the beginning of the range you can do the following:

    int lastColumn = range.Columns.Count;
    int lastRow = range.Rows.Count;

Find the last used row in Excel with C#

Here is the code I use:

public static string GetMinimalUsedRangeAddress(Excel.Worksheet sheet)
{
    string address = String.Empty;
    try
    {
        int rowMax = 0;
        int colMax = 0;

        Excel.Range usedRange = sheet.UsedRange;
        Excel.Range lastCell = usedRange.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
        int lastRow = lastCell.Row;
        int lastCol = lastCell.Column;
        int rowMin = lastRow + 1;
        int colMin = lastCol + 1;

        int rr = usedRange.Rows.Count;
        int cc = usedRange.Columns.Count;
        for (int r = 1; r <= rr; r++)
        {
            for (int c = 1; c <= cc; c++)
            {
                Excel.Range cell = usedRange.Cells[r, c] as Excel.Range;
                if (cell != null && cell.Value != null && !String.IsNullOrEmpty(cell.Value.ToString()))
                {
                    if (cell.Row > rowMax)
                        rowMax = cell.Row;
                    if (cell.Column > colMax)
                        colMax = cell.Column;
                    if (cell.Row < rowMin)
                        rowMin = cell.Row;
                    if (cell.Column < colMin)
                        colMin = cell.Column;
                }
                MRCO(cell);
            }
        }

        if (!(rowMax == 0 || colMax == 0 || rowMin == lastRow + 1 || colMin == lastCol + 1))
            address = Cells2Address(rowMin, colMin, rowMax, colMax);

        MRCO(lastCell);
        MRCO(usedRange);
    }
    catch (Exception ex)
    {
        // log as needed
    }
    return address; // caller should test return for String.Empty
}

public static string Cells2Address(int row1, int col1, int row2, int col2)
{
    return ColNum2Letter(col1) + row1.ToString() + ":" + ColNum2Letter(col2) + row2.ToString();
}

public static string ColNum2Letter(int colNum)
{
    if (colNum <= 26) 
        return ((char)(colNum + 64)).ToString();

    colNum--; //decrement to put value on zero based index
    return ColNum2Letter(colNum / 26) + ColNum2Letter((colNum % 26) + 1);
}

public static void MRCO(object obj)
{
    if (obj == null) { return; }
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
    }
    catch
    {
        // ignore, cf: http://support.microsoft.com/default.aspx/kb/317109
    }
    finally
    {
        obj = null;
    }
}

Note: you might be tempted to replace all the individual cell value checks with CountA but that will fail in certain cases. For example, if a cell contains the formula =IF(A1=55,"Y",""), a resulting empty string will count as a non-blank cell using CountA.

Last Row in a column in excel file using C# (cells does not have get_end method)

If you assign the return from MySheet.Cells[1, 1] in to a Range object or call the function End, you should find that it works as expected.
Also, note the case of the function, it is meant to be: get_End.

        // Working
        Excel.Range firstCell1 = MySheet.Cells[1, 1];
        int LastRow1 = firstCell1.get_End(Excel.XlDirection.xlUp).Row;

        // Working
        Excel.Range firstCell2 = MySheet.Cells[fullRow, 1];
        int LastRow2 = firstCell2.get_End(Excel.XlDirection.xlUp).Row;

        // Working
        int LastRow3 = MySheet.Cells[fullRow, 1].End(Excel.XlDirection.xlUp).Row;

        // Does not work
        int LastRow4 = MySheet.Cells[1, 1].get_End(Excel.XlDirection.xlUp).Row;

I believe the problem is down to the Dynamic Binding done at runtime for COM Interop, but, I am not 100% certain of the reason.

GemBox.Spreadsheet last used row

Hi you can just use ExcelFile.Rows.Count property.

Gets the number of currently allocated elements (dynamically changes when worksheet is modified)

Try the following:

int lastUsedRow = worksheet.Rows.Count - 1;

Also regarding the shahkalpesh suggestion, yes you can also achieve your task with that approach as well, here is how:

var usedRange = worksheet.GetUsedCellRange(true);
int lastUsedRow = usedRange.LastRowIndex;

---

Related Topics