Range 类的使用非常灵活。有时 Range 对象是单个对象,有时它表示一个对象集合。尽管 Range 对象常常指代单个对象,它却拥有 Item 和 Count 成员,因而有时很难确切把握 Range 对象的用法。
注意 下面有几个实例检索一个范围的 Address 属性。此属性返回一个字符串,该字符串包含范围坐标的表示形式,其格式可以是以下几种格式中的一种:“$A$1”(位置 A1 的单元格)、“$1”(工作表的第一行)和“$A$1:$C$5”(由 A1 和 C5 限定的矩形内的所有单元格组成的范围)。“$”指示绝对坐标,而不是相对坐标。使用 Address 属性是确定检索到的范围的精确位置的最简单方法。
以下过程使 Range 对象引用单个单元格或一组单元格。每个实例都采用了以下设置代码:
' Visual Basic Dim ws As Excel.Worksheet = _ DirectCast(ThisWorkbook.Worksheets(1), Excel.Worksheet) Dim rng, rng1, rng2 As Excel.Range // C# Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.Worksheets[1]; Excel.Range rng,rng1,rng2;
可以使用以下任一方法引用特定的范围。
引用工作表中的特定范围
- 引用 Application 对象的 ActiveCell 属性:
' Visual Basic rng = ThisApplication.ActiveCell // C# rng = ThisApplication.ActiveCell;
- 使用对象的 Range 属性可指定一个范围:
' Visual Basic rng = ws.Range("A1") rng = ws.Range("A1:B12") // C# rng = ws.get_Range("A1",Type.Missing); rng = ws.get_Range("A1:B12",Type.Missing);
- 使用工作表的 Cells 属性可指定单个行和列的值:
' Visual Basic ' The Cells collection returns an Object. ' Convert it to a Range object explicitly. rng = DirectCast(ws.Cells(1, 1), Excel.Range) // C# // The Cells collection returns an Object. // Convert it to a Range object explicitly. rng = (Excel.Range)ws.Cells[1,1];
- 指定一个范围的“角”;也可直接引用范围的 Cells、Rows 或 Columns 属性;这几种情况下该属性都返回一个范围:
' Visual Basic rng = ws.Range("A1", "C5") rng = ws.Range("A1", "C5").Cells rng = ws.Range("A1", "C5").Rows rng = ws.Range("A1", "C5").Columns // C# rng = ws.get_Range("A1", "C5"); rng = ws.get_Range("A1", "C5").Cells; rng = ws.get_Range("A1", "C5").Rows; rng = ws.get_Range("A1", "C5").Columns;
- 引用命名范围:
' Visual Basic rng = ThisApplication.Range("SomeRangeName") // C# rng = ThisApplication.get_Range("SomeRangeName", Type.Missing);
- 引用特定的行或列或者由行和列构成的范围;请注意,这些 Rows 和 Columns 属性分别返回一个 Object,如果将 Option Strict 设置为 On,则需要进行转换:
' Visual Basic rng = DirectCast(ws.Rows(1), Excel.Range) rng = DirectCast(ws.Rows("1:3"), Excel.Range) rng = DirectCast(ws.Columns("B:E"), Excel.Range) // C# rng = (Excel.Range)ws.Rows[1,Type.Missing]; rng = (Excel.Range)ws.Rows["1:3",Type.Missing]; rng = (Excel.Range)ws.Columns["B:E",Type.Missing];
- 使用 Application 对象的 Selection 属性返回与选定的单元格相对应的范围:
' Visual Basic System.Diagnostics.Debug.WriteLine(ThisApplication.Selection.Address) // C# System.Diagnostics.Debug.WriteLine(((Excel.Range) ThisApplication.Selection).get_Address( Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
- 创建一个包含两个范围的并集的范围(在引号中指定这两个范围,之间以逗号分隔):
' Visual Basic rng = ThisApplication.Range("A1:D4, F2:G5") ' You can also use the Application object's Union ' method to retrieve the intersection of two ranges: rng1 = ThisApplication.Range("A1:D4") rng2 = ThisApplication.Range("F2:G5") rng = ThisApplication.Union(rng1, rng2) // C# rng = ThisApplication.get_Range("A1:D4","F2:G5"); // You can also use the Application object's Union // method to retreive the intersection of two ranges: rng1 = ThisApplication.get_Range("A1","D4"); rng2 = ThisApplication.get_Range("F2","G5"); rng = ThisApplication.Union(rng1, rng2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
- 创建一个表示两个范围的交集的范围(在引号中指定这两个范围,之间以逗号分隔):
' Visual Basic rng = ThisApplication.Range("A1:D16 B2:F14") ' You can also use the Application object's Intersect ' method to retrieve the intersection of two ranges: rng1 = ThisApplication.Range("A1:D16") rng2 = ThisApplication.Range("B2:F14") rng = ThisApplication.Intersect(rng1, rng2) // C# rng = ThisApplication.get_Range("A1:D16","B2:F14"); // You can also use the Application object's Intersect // method to retrieve the intersection of two ranges: rng1 = ThisApplication.get_Range("A1","D16"); rng2 = ThisApplication.get_Range("B2","F14"); rng = ThisApplication.Intersect(rng1, rng2, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
- 使用范围的 Offset 属性可检索相对于原始范围的范围;以下实例将内容添加到位于第 1 行第 1 列的单元格下方的范围:
' Visual Basic rng = DirectCast(ws.Cells(1, 1), Excel.Range) Dim i As Integer For i = 1 To 5 rng.Offset(i, 0).Value = i.ToString Next // C# rng = (Excel.Range)ws.Cells[1,1]; for(int i = 1; i <= 5; i++) rng.get_Offset(i,0).Value2 = i.ToString();
- 使用范围的 CurrentRegion 属性可检索代表当前区域的范围,当前区域指的是由最近的空行和空列所界定的区域:
' Visual Basic System.Diagnostics.Debug.WriteLine( _ ThisApplication.Range("C3").CurrentRegion.Address()) // C# System.Diagnostics.Debug.WriteLine(ThisApplication.get_Range( "C3", Type.Missing).CurrentRegion.get_Address( Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
- 使用范围的 Areas 属性可检索一组范围,每个范围对应于该范围内容中的一个区域:以下代码要求工作表中有一个名为“rangeTest”的范围:
' Visual Basic rng = ThisApplication.Range("rangeTest") Dim i As Integer For i = 1 To rng.Areas.Count System.Diagnostics.Debug.WriteLine(rng.Areas(i).Address) Next // C# rng = ThisApplication.get_Range("rangeTest",Type.Missing); for(int i = 1; i <= rng.Areas.Count; i++) System.Diagnostics.Debug.WriteLine( rng.Areas[i].get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
- 使用 End 属性以及 XlDirection 枚举中的值 (xlUp、xlToRight、xlToLeft、xlDown),可检索代表该区域末尾处的单元格的范围(如同按下了枚举值所描述的键一样):
' Visual Studio With ThisApplication.Selection System.Diagnostics.Debug.WriteLine(.End( Excel.XlDirection.xlToRight).Address) System.Diagnostics.Debug.WriteLine(.End( Excel.XlDirection.xlToLeft).Address) System.Diagnostics.Debug.WriteLine(.End( Excel.XlDirection.xlUp).Address) System.Diagnostics.Debug.WriteLine(.End( Excel.XlDirection.xlDown).Address) End With // C# rng = (Excel.Range)ThisApplication.Selection; System.Diagnostics.Debug.WriteLine(rng.get_End( Excel.XlDirection.xlToRight).get_Address( Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)); System.Diagnostics.Debug.WriteLine(rng.get_End( Excel.XlDirection.xlToLeft).get_Address( Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)); System.Diagnostics.Debug.WriteLine(rng.get_End( Excel.XlDirection.xlUp).get_Address( Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing)); System.Diagnostics.Debug.WriteLine(rng.get_End( Excel.XlDirection.xlDown).get_Address( Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
- 使用 EntireRow 或 EntireColumn 属性引用包含指定范围的行或列。以下代码要求工作表中有一个名为“rangeTest”的范围:
' Visual Basic rng = ThisApplication.Range("rangeTest") System.Diagnostics.Debug.WriteLine(rng.Areas(2).EntireRow.Address) // C# rng = ThisApplication.get_Range("rangeTest",Type.Missing); System.Diagnostics.Debug.WriteLine(rng.Areas[2].EntireRow.get_Address( Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));