用C＃在Excel中找到最后一行 [英] Find the last used row in Excel with C#

问题描述

可能重复：

我试图在Excel工作表中找到最后使用的行。为此，我使用这个代码：

Im trying to find the last used row in an Excel worksheet. For doing this, I'm using this code:

int lastUsedRow = currentWS.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,Type.Missing).Row;

大部分时间都可以正常工作，但有时Excel会认为表格中的行数多于theres假设是。

Most of the time it works fine, but sometimes Excel thinks that there's more rows in the sheet than theres suppose to be.

Fx：如果我将sheet1中包含12行的数据复制到一个空的sheet2，然后通过右键单击并删除sheet2中的所有数据，然后按删除...，并将sheet5中包含5行的数据复制到sheet2，那么lastUsedRow将给出值为12的值，该值应为5。

Fx: If I copy data from sheet1, containing 12 rows, to an empty sheet2, then deleting all data in sheet2, by right clicking and press "Delete..." and copy data from sheet3, containing 5 rows, to sheet2, then lastUsedRow will give me the value of 12 which should have been 5.

上面的图像示例假设给我22的值作为行数，但是相反，我会得到634.注意右侧的滚动条。

The image example above is suppose to give my the value of 22 as row count, but instead i'll get 634. Notice the scroll bar to the right.

似乎Excel认为有些单元格填充，即使我刚刚删除所有单元格，在复制之前在表格中有更少行的新数据。

It seems like Excel thinks that some cells are filled even though I just deleted all cells, before copying new data with fewer rows into the sheet.

有没有办法调整大小表中的数据视图，以便我得到正确的数字的细胞或m aybe是找到最后一次使用行数的另一种方法？

Are there any way to "resize" the view of the data in the sheet, so that i'll get the right number of used cells or maybe another way to find the number of the last used row?

谢谢。

推荐答案

这是我使用的代码：

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;
    }
}

注意：您可能会试图将所有个人单元格值检查与 CountA 但在某些情况下会失败。例如，如果单元格包含公式 = IF（A1 = 55，Y，），则生成的空字符串将作为非空白单元格使用 CountA 。

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.

这篇关于用C＃在Excel中找到最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！