上次修改时间： 2010年3月24日
适用范围： SharePoint Server 2010
本示例说明如何通过使用范围坐标、命名范围、行和列来指定范围地址。它还说明如何指定工作表名称以及工作表名称与范围地址之间的关系。
范围坐标是用来选择连续范围的四个整数坐标。借助于范围坐标，您可以通过将直接整数索引用作"A1"表达式的替代物来指定 Excel 范围。您可以指定的坐标是首行、左列、高度和宽度。当代码在循环中遍历一组单元格时，或当动态计算范围坐标以作为算法的一部分时，使用范围坐标将更为方便。
范围规范必需包含工作表名称：Excel Web Services 不能识别"当前表"。以下是指定工作表名称的几种方法：
作为范围地址的一部分（例如，"Sheet3!B12:D18"），在这种情况下，工作表名称参数可以为空：
object[] rangeResult1 = xlservice.GetRangeA1(sessionId, String.Empty, "Sheet2!A12:G18", true, out outStatus);Dim rangeResult1() As Object = xlservice.GetRangeA1(sessionId, String.Empty, "Sheet2!A12:G18", True, outStatus)在单独的工作表名称参数中，在这种情况下，范围地址参数不必包含工作表名称：
xlservice.SetCell(sessionId, "Sheet3", 0, 11, 1000);xlservice.SetCell(sessionId, "Sheet3", 0, 11, 1000)同时在工作表名称和范围地址中，在这种情况下，工作表的名称必须与以下内容匹配：
object[] rangeResult = xlservice.GetCellA1(sessionId, "Sheet3", "Sheet3!G18", true, out outStatus);Dim rangeResult() As Object = xlservice.GetCellA1(sessionId, "Sheet3", "Sheet3!G18", True, outStatus)
唯一不需要工作表名称的情况是命名范围，因为某些命名范围具有工作簿作用域。例如，您可以参考命名范围，而不指定工作表名称参数：
xlServices.SetCellA1(sessionId, String.Empty, "MyNamedRange", 8);
xlServices.SetCellA1(sessionId, String.Empty, "MyNamedRange", 8)
如果您指定工作表名称，您引用的范围必须存在于您指定的工作表上。如果您指定不存在的工作表，调用将失败，并且您将获得一个简单对象访问协议 (SOAP) 异常，它指示工作表不存在。
示例
备注
假定您已经创建一个 SharePoint 文档库并使其成为受信任的位置。有关详细信息，请参阅如何：信任一个位置和How to: Trust Workbook Locations Using Script。
using System;
using System.Text;
using System.Web.Services.Protocols;
using ExcelWebService.myserver02;
namespace ExcelWebService
{
/// <summary>
/// Summary description for Class1.
/// </summary>
class MyExcelWebService
{
[STAThread]
static void Main(string[] args)
{
// Instantiate the Web service
// and range coordinate array object.
ExcelService xlservice = new ExcelService();
Status[] outStatus;
RangeCoordinates rangeCoordinates = new RangeCoordinates();
string sheetName = "MySheet1";
// TODO: Change the path to the workbook
// to point to a workbook you have access to.
// The workbook must be in a trusted location.
// Using the workbook path this way will allow
// you to call the workbook remotely.
string targetWorkbookPath =
"http://myserver02/example/Shared%20Documents/MyWorkbook1.xlsx";
// Set Credentials for requests
xlservice.Credentials =
System.Net.CredentialCache.DefaultCredentials;
try
{
// Call the open workbook, and point to
// the workbook to open.
string sessionId =
xlservice.OpenWorkbook(targetWorkbookPath,
String.Empty, String.Empty, out outStatus);
// Prepare object to define range coordinates
// and call the GetRange method.
// startCol, startRow, startHeight, and startWidth
// get their values from user input.
rangeCoordinates.Column = (int)startCol.Value;
rangeCoordinates.Row = (int)startRow.Value;
rangeCoordinates.Height = (int)startHeight.Value;
rangeCoordinates.Width = (int)startWidth.Value;
object[] rangeResult1 = xlservice.GetRange(sessionId,
sheetName, rangeCoordinates, false, out outStatus);
Console.WriteLine("Total rows in range: " +
rangeResult1.Length);
Console.WriteLine("Sum in last column is: " +
((object[])rangeResult1[18])[11]);
// Call the SetCell method, which invokes
// the Calculate method.
// Set first row in last column cell to 1000.
xlservice.SetCell(sessionId, sheetName, 0, 11, 1000);
// Call the GetRange method again to see if
// the Sum total in the last column changed.
object[] rangeResult2 = xlservice.GetRange(sessionId,
sheetName, rangeCoordinates, false, out outStatus);
Console.WriteLine("Sum in the last column after SetCell
is: " + ((object[])rangeResult2[18])[11]);
// Close workbook. This also closes the session.
xlservice.CloseWorkbook(sessionId);
}
catch (SoapException e)
{
Console.WriteLine("Exception Message: {0}", e.Message);
}
catch (Exception e)
{
Console.WriteLine("Exception Message: {0}", e.Message);
}
Console.ReadLine();
}
}
}
Imports System
Imports System.Text
Imports System.Web.Services.Protocols
Imports ExcelWebService.myserver02
Namespace ExcelWebService
''' <summary>
''' Summary description for Class1.
''' </summary>
Friend Class MyExcelWebService
<STAThread> _
Shared Sub Main(ByVal args() As String)
' Instantiate the Web service
' and range coordinate array object.
Dim xlservice As New ExcelService()
Dim outStatus() As Status
Dim rangeCoordinates As New RangeCoordinates()
Dim sheetName As String = "MySheet1"
' TODO: Change the path to the workbook
' to point to a workbook you have access to.
' The workbook must be in a trusted location.
' Using the workbook path this way will allow
' you to call the workbook remotely.
Dim targetWorkbookPath As String = "http://myserver02/example/Shared%20Documents/MyWorkbook1.xlsx"
' Set Credentials for requests
xlservice.Credentials = System.Net.CredentialCache.DefaultCredentials
Try
' Call the open workbook, and point to
' the workbook to open.
Dim sessionId As String = xlservice.OpenWorkbook(targetWorkbookPath, String.Empty, String.Empty, outStatus)
' Prepare object to define range coordinates
' and call the GetRange method.
' startCol, startRow, startHeight, and startWidth
' get their values from user input.
rangeCoordinates.Column = CInt(Fix(startCol.Value))
rangeCoordinates.Row = CInt(Fix(startRow.Value))
rangeCoordinates.Height = CInt(Fix(startHeight.Value))
rangeCoordinates.Width = CInt(Fix(startWidth.Value))
Dim rangeResult1() As Object = xlservice.GetRange(sessionId, sheetName, rangeCoordinates, False, outStatus)
Console.WriteLine("Total rows in range: " & rangeResult1.Length)
Console.WriteLine("Sum in last column is: " & (CType(rangeResult1(18), Object()))(11))
' Call the SetCell method, which invokes
' the Calculate method.
' Set first row in last column cell to 1000.
xlservice.SetCell(sessionId, sheetName, 0, 11, 1000)
' Call the GetRange method again to see if
' the Sum total in the last column changed.
Dim rangeResult2() As Object = xlservice.GetRange(sessionId, sheetName, rangeCoordinates, False, outStatus)
Console.WriteLine("Sum in the last column after SetCell is: " & (CType(rangeResult2(18), Object()))(11))
' Close workbook. This also closes the session.
xlservice.CloseWorkbook(sessionId)
Catch e As SoapException
Console.WriteLine("Exception Message: {0}", e.Message)
Catch e As Exception
Console.WriteLine("Exception Message: {0}", e.Message)
End Try
Console.ReadLine()
End Sub
End Class
End Namespace
强大的编程功能
请确保您将 Web 引用添加到您对其具有访问权限的 Excel Web Services 网站。请更改以下内容：
更改 using ExcelWebService.myserver02; 语句以指向您要引用的 Web 服务网站。
更改 string targetWorkbookPath = "http://myserver02/example/Shared%20Documents/Book1.xlsx"; 以指向您具有访问权限的工作簿。工作簿必须处于受信任的位置。
请参阅
任务
演练：使用 Excel Web Services 开发自定义应用程序