使用 C# 在 Excel 中查找最后使用的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12276745/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find the last used row in Excel with C#
提问by Harving
Possible Duplicate:
How to get the range of occupied cells in excel sheet
可能的重复：
如何获取 Excel 工作表中被占用单元格的范围
Im trying to find the last used row in an Excel worksheet. For doing this, I'm using this code:
我试图在 Excel 工作表中找到最后使用的行。为此，我使用以下代码：
int lastUsedRow = currentWS.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell,Type.Missing).Row;
Most of the time it works fine, but sometimes Excel thinks that there's more rows in the sheet than theres suppose to be.
大多数情况下它工作正常，但有时 Excel 认为工作表中的行数比预期的多。
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.
Fx：如果我将包含 12 行的工作表 1 中的数据复制到空白工作表 2，然后通过右键单击并按“删除...”删除工作表 2 中的所有数据，然后将包含 5 行的工作表 3 中的数据复制到工作表 2，然后lastUsedRow 会给我 12 的值，它应该是 5。
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.
上面的图像示例假设我的行数为 22，但我会得到 634。注意右侧的滚动条。
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.
在将行数较少的新数据复制到工作表中之前，Excel 似乎认为即使我刚刚删除了所有单元格，也填充了某些单元格。
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?
有什么方法可以“调整”工作表中数据的视图大小，以便我获得正确数量的已使用单元格或另一种方法来查找最后使用的行数？
Thanks.
谢谢。
采纳答案by CtrlDot
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 CountAbut 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.
注意：您可能想用 替换所有单个单元格值检查，CountA但在某些情况下会失败。例如，如果单元格包含公式=IF(A1=55,"Y","")，则生成的空字符串将计为使用CountA.
回答by lorenz albert
Edit: New Solution
编辑：新解决方案
Since Joe provided the correct code to get the last used row.
由于 Joe 提供了正确的代码来获取最后使用的行。
Worksheet.UsedRange.Row + Worksheet.UsedRange.Rows.Count - 1
And using the following command to clear content and formattings
并使用以下命令清除内容和格式
Selection.Delete
Selection.ClearFormats
This should work ;)
这应该有效;)
回答by Joe
To get the last used row in a worksheet, you can use:
要获取工作表中最后使用的行，您可以使用：
Worksheet.UsedRange.Row + Worksheet.UsedRange.Rows.Count - 1
Note that:
注意：
UsedRange does not necessarily start on the first row - the first row might be empty.
UsedRange includes cells that contain formatting even if their content is empty. I suspect this is why you're seeing values bigger than you expect. You need to delete formatting as well as data from the empty cells.
UsedRange 不一定从第一行开始 - 第一行可能是空的。
UsedRange 包括包含格式的单元格，即使它们的内容为空。我怀疑这就是为什么您看到的值比您预期的要大的原因。您需要从空单元格中删除格式和数据。
I'm not sure how to do the deletion of formatting on cells programmatically
我不确定如何以编程方式删除单元格上的格式
You can use Range.ClearFormatsto clear formatting, or Range.Clearto clear everything. In general, if you're not sure how to do something programatically in Excel, try recording a macro, doing it manually, then inspecting the generated macro.
您可以使用Range.ClearFormats清除格式，或Range.Clear清除所有内容。通常，如果您不确定如何在 Excel 中以编程方式执行某些操作，请尝试录制宏，手动执行，然后检查生成的宏。
