如何在 VSTO/C# 中使用行号和列号获取 Excel 范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2333202/
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
How do I get an Excel range using row and column numbers in VSTO / C#?
提问by Dan Crowther
I think the question sums it up. Given two integers for row and column or four integers for row and column for the two corners of a range, how do I get a range object for that range.
我认为这个问题总结了它。给定一个范围的两个角的行和列的两个整数或行和列的四个整数，我如何获得该范围的范围对象。
回答by burnside
Where the range is multiple cells:
其中范围是多个单元格：
Excel.Worksheet sheet = workbook.ActiveSheet;
Excel.Range rng = (Excel.Range) sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[3,3]);
Where range is one cell:
其中范围是一个单元格：
Excel.Worksheet sheet = workbook.ActiveSheet;
Excel.Range rng = (Excel.Range) sheet.Cells[1, 1];
回答by Askjerry
I found a good short method that seems to work well...
我找到了一个很好的简短方法，似乎效果很好......
Dim x, y As Integer
x = 3: y = 5
ActiveSheet.Cells(y, x).Select
ActiveCell.Value = "Tada"
In this example we are selecting 3 columns over and 5 rows down, then putting "Tada" in the cell.
在这个例子中，我们选择了 3 列和 5 行，然后将“Tada”放入单元格中。
回答by flaming
you can retrieve value like this
你可以像这样检索值
string str = (string)(range.Cells[row, col] as Excel.Range).Value2 ;
select entire used range
选择整个使用范围
Excel.Range range = xlWorkSheet.UsedRange;
source :
来源 ：
http://csharp.net-informations.com/excel/csharp-read-excel.htm
http://csharp.net-informations.com/excel/csharp-read-excel.htm
flaming
炽盛
回答by tic
The given answer will throw an error if used in Microsoft Excel 14.0 Object Library. Object does not contain a definition for get_range. Instead use
如果在 Microsoft Excel 14.0 对象库中使用，给出的答案将引发错误。对象不包含 get_range 的定义。而是使用
int countRows = xlWorkSheetData.UsedRange.Rows.Count;
int countColumns = xlWorkSheetData.UsedRange.Columns.Count;
object[,] data = xlWorkSheetData.Range[xlWorkSheetData.Cells[1, 1], xlWorkSheetData.Cells[countRows, countColumns]].Cells.Value2;
回答by Asrhael
UsedRange work fine with "virgins" cells, but if your cells are filled in the past, then UsedRange will deliver to you the old value.
UsedRange 可以很好地处理“原始”单元格，但如果您的单元格在过去已被填充，则 UsedRange 将为您提供旧值。
For example:
例如：
"Think in a Excel sheet that have cells A1to A5filled with text". In this scenario, UsedRangemust be implemented as:
“在 Excel 工作表中思考，其中单元格A1到A5 中填充了文本”。在这种情况下，UsedRange必须实现为：
Long SheetRows;
SheetRows = ActiveSheet.UsedRange.Rows.Count;
A watch to SheetRows variable must display a value of 5 after the execution of this couple of lines.
在执行这几行之后，对 SheetRows 变量的监视必须显示值 5。
Q1: But, what happen if the value of A5 is deleted?
A1: The value of SheetRows would be 5
Q2: Why this?
A2: Because MSDN define UsedRange property as:
Gets a Microsoft.Office.Interop.Excel.Range object that represents allthe cells that have contained a value at any time.
Q1：但是，如果删除 A5 的值会怎样？
A1：SheetRows 的值为 5
Q2：为什么会这样？
A2：因为 MSDN 将 UsedRange 属性定义为：
获取一个 Microsoft.Office.Interop.Excel.Range 对象，该对象表示在任何时候都包含一个值的所有单元格。
So, the question is: Exist some/any workaround for this behavior?
所以，问题是：是否存在针对此行为的一些/任何解决方法？
I think in 2 alternatives:
我认为有两种选择：
- Avoid deleting the content of the cell, preferring deletion of the whole row (right click in the row number, then "delete row".
- Use CurrentRegioninstead of UsedRange property as follow:
- 避免删除单元格的内容，更喜欢删除整行（右键单击行号，然后“删除行”。
- 使用CurrentRegion而不是 UsedRange 属性，如下所示：
Long SheetRows;
SheetRows = ActiveSheet.Range("A1").CurrentRegion.Rows.Count;
回答by FaceTowel
If you want like Cells(Rows.Count, 1).End(xlUp).Row, you can do it.
如果你想要喜欢Cells(Rows.Count, 1).End(xlUp).Row，你可以做到。
just use the following code:
只需使用以下代码：
using Excel = Microsoft.Office.Interop.Excel;
string xlBk = @"D:\Test.xlsx";
Excel.Application xlApp;
Excel.Workbook xlWb;
Excel.Worksheet xlWs;
Excel.Range rng;
int iLast;
xlApp = new Excel.Application();
xlWb = xlApp.Workbooks.Open(xlBk, 0, true, 5, "", "", true,
Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWs = (Excel.Worksheet)xlWb.Worksheets.get_Item(1);
iLast = xlWs.Rows.Count;
rng = (Excel.Range)xlWs.Cells[iLast, 1];
iLast = rng.get_End(Excel.XlDirection.xlUp).Row;
回答by A Burns
Facing the same problem I found the quickest solution was to actually scan the rows of the cells I wished to sort, determine the last row with a non-blank element and then select and sort on that grouping.
面对同样的问题，我发现最快的解决方案是实际扫描我希望排序的单元格的行，用非空白元素确定最后一行，然后对该分组进行选择和排序。
Dim lastrow As Integer
lastrow = 0
For r = 3 To 120
If Cells(r, 2) = "" Then
Dim rng As Range
Set rng = Range(Cells(3, 2), Cells(r - 1, 2 + 6))
rng.Select
rng.Sort Key1:=Range("h3"), order1:=xlDescending, Header:=xlGuess, DataOption1:=xlSortNormal
r = 205
End If
Next r
回答by user1141820
If you are getting an error stating that "Object does not contain a definition for get_range."
如果您收到一条错误消息，指出“对象不包含 get_range 的定义”。
Try following.
尝试跟随。
Excel.Worksheet sheet = workbook.ActiveSheet;
Excel.Range rng = (Excel.Range) sheet.Range[sheet.Cells[1, 1], sheet.Cells[3,3]].Cells;
回答by Ilie
Try this, works!
试试这个，有效！
Excel.Worksheet sheet = xlWorkSheet;
Excel.Series series1 = seriesCollection.NewSeries();
Excel.Range rng = (Excel.Range)xlWorkSheet.Range[xlWorkSheet.Cells[3, 13], xlWorkSheet.Cells[pp, 13]].Cells;
series1.Values = rng;
