C# 获取 Excel 范围对象的最后一个单元格（列、行）

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

提问by M. X

I have a Microsoft.Office.Interop.Excel.Rangeobject and want to estimate the end-coordinates of that range ie (last column, last row).

我有一个Microsoft.Office.Interop.Excel.Range对象，想估计该范围的结束坐标，即（最后一列，最后一行）。

There are no properties like LastCol, LastRow. The only properties are Columnand Row, which specify the firstcell. But how can I get the last cell of the range?

没有像LastCol, 之类的属性LastRow。唯一的属性是Columnand Row，它们指定第一个单元格。但是我怎样才能得到范围的最后一个单元格？

采纳答案by jiverson

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();
   

1. 启动 Excel。

2. 打开工作簿。

3. 选择您的范围，在这种情况下，我得到了活动工作表的使用范围。

4. 调用范围的 get_address 方法。

5. 在冒号上拆分 get_address 的结果。

6. 拆分产生的数组的第一个值将具有起始单元格。

7. 拆分产生的数组的第二个值将具有结束单元格。

8. 用空替换美元符号，您将拥有该范围的开始和结束单元格。

   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;

回答by Xiaoy312

there is two way to do this :

有两种方法可以做到这一点：

1. Using Worksheet.UsedRangeto determine the range. This will give you a range like A1:F10, or use Worksheet.UsedRange.SpecialCells(IExcel.XlCellType.xlCellTypeLastCell)to obtain F10.

2. With Range.End[IExcel.XlDirection]property, it returns the last continuous no-empty cell in the specified direction. Note that if the Range is empty, it will return the first no-empty cell or last cell(when excel reach its boundaries) in the given direction. Ex: the whole column A is empty, Range["A1"].End[IExcel.XlDirection.xlDown]will be A65535(last row in excel 97-2003, A1048576for excel 2007)

1. 使用Worksheet.UsedRange确定的范围内。这将为您提供A1:F10 之类的范围，或用于Worksheet.UsedRange.SpecialCells(IExcel.XlCellType.xlCellTypeLastCell)获取F10。

2. 带Range.End[IExcel.XlDirection]属性，返回指定方向的最后一个连续的非空单元格。请注意，如果 Range 为空，它将返回给定方向的第一个非空单元格或最后一个单元格（当 excel 到达其边界时）。例如：整个 A列为空，Range["A1"].End[IExcel.XlDirection.xlDown]将为A65535（excel 97-2003 中的最后一行，excel 2007 中为A1048576）

//Just In case if you are wondering what IExcel is
using IExcel = Microsoft.Office.Interop.Excel;

//Just In case if you are wondering what IExcel is
using IExcel = Microsoft.Office.Interop.Excel;

回答by Mike Gledhill

Using Excel 2013, we've had lots of cases where UsedRangeand xlCellTypeLastCellsimply gave horribly wrong values.

使用 Excel 2013，我们遇到过很多情况，UsedRange并且xlCellTypeLastCell只是给出了非常错误的值。

For example, we'd have a worksheet containing just 7 columns, and sometimes these two functions would tell our C# code that there were 16,000+ columns of data.

例如，我们有一个仅包含 7 列的工作表，有时这两个函数会告诉我们的 C# 代码有 16,000 多列数据。

The only method I found which truly worked out the bottom-right cell containing data was to use the tips in this suggestion:

我发现真正计算出包含数据的右下角单元格的唯一方法是使用此建议中的提示：

sheet.Cells.Find()

sheet.Cells.Find()