如何：设置范围的值

上次修改时间： 2010年5月1日

适用范围： SharePoint Server 2010

Excel Web Services 公开了四种向 Excel 工作簿中设置值的方法：SetCell、SetCellA1、SetRange 和 SetRangeA1。

备注

对工作簿进行更改（例如通过使用 Excel Web Services 向范围设置值）时，将只为该特定会话保留对工作簿的更改。这些更改不会保存或保持回到原始工作簿。当前工作簿会话结束时（例如调用 CloseWorkbook 方法或会话超时的时候），所做的更改将丢失。

如果要保存对工作簿做出的更改，可以使用 GetWorkbook 方法，然后使用目标文件存储区的 API 保存工作簿。有关详细信息，请参阅如何：获取整个工作簿或快照和How to: Save a Workbook。

使用 SetCell 和 SetCellA1 方法设置一个单元格中的值。如果尝试设置单元格范围中的值（例如通过传入"D3:G5"之类的范围引用或大于一个单元格的命名范围等），方法调用将失败。如果要设置单元格范围中的值，请改用 SetRange 和 SetRangeA1 方法。

后缀为 A1 的方法（SetCellA1 和 SetRangeA1）所使用的坐标系与无此后缀的方法（SetCell 和 SetRange）不同。如果要以 Excel 样式对单元格进行引用，如范围引用（例如 H8、A3:D5、Sheet2!A12:G18）或命名范围，则应使用后缀为 A1 的方法。利用这些方法，可以传入工作表的名称和范围。

如果要通过使用数字坐标系统来访问 Excel 范围，则应使用无 A1 后缀的方法。当代码在循环中遍历一组单元格时，或当动态计算范围坐标以作为算法的一部分时，使用范围坐标将更为方便。

单元格的行坐标和列坐标都以 0 开始，因此"0,0"将返回单元格 A1，如此例所示：

// Call the SetCell method to set a value, 8, into a cell.
// The cell is in the first row and first column; that is, cell A1.
xlservice.SetCell(sessionId, sheetName, 0, 0, 8);

' Call the SetCell method to set a value, 8, into a cell.
' The cell is in the first row and first column; that is, cell A1.
xlservice.SetCell(sessionId, sheetName, 0, 0, 8)

如果要从多个相邻的单元格获取值，则可能要考虑使用 SetRange 方法代替多次调用 SetCell 方法。这只会产生一次到服务器的往返，而不会产生多次往返。因此，在某些情况下使用 SetRange 方法代替 SetCell 方法可以显著提高性能。

使用 SetRange 和 SetRangeA1 方法向单元格范围设置值时，请使用对象数组（C# 中为 object[]，Visual Basic .NET 中为 Object ()）。这个对象数组实际上是一个交错数组；数组中的每项都是表示单元格的对象的另一个数组。有关交错数组的详细信息，请参阅交错数组（C# 编程指南）（该链接可能指向英文页面）(https://msdn.microsoft.com/zh-cn/library/2s05feca.aspx)（该链接可能指向英文页面）。

使用 SetCell 和 SetRange 方法设置值

采用数字范围坐标，使用 SetCell 方法设置打开的工作簿中单元格的值：

// Instantiate the Web service and make a status array object.
ExcelService xlservice = new ExcelService();
Status[] outStatus;
RangeCoordinates rangeCoordinates = new RangeCoordinates();
string sheetName = "Sheet2";

// Set the path to a workbook.
// The workbook must be in a trusted location.
string targetWorkbookPath = "http://myserver02/example/Shared%20Documents/Book1.xlsx";

// Set credentials for requests.
xlservice.Credentials = System.Net.CredentialCache.DefaultCredentials;

// Call the open workbook, and point to the trusted 
// location of the workbook to open.
string sessionId = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", out outStatus);

// Call the SetCell method to set the cell's value to 28.
// The cell is in the ninth row and second column, which is cell B9.
xlservice.SetCell(sessionId, sheetName, 8, 1, 28);

' Instantiate the Web service and make a status array object.
Dim xlservice As New ExcelService()
Dim outStatus() As Status
Dim rangeCoordinates As New RangeCoordinates()
Dim sheetName As String = "Sheet2"

' Set the path to a workbook.
' The workbook must be in a trusted location.
Dim targetWorkbookPath As String = "http://myserver02/example/Shared%20Documents/Book1.xlsx"

' Set credentials for requests.
xlservice.Credentials = System.Net.CredentialCache.DefaultCredentials

' Call the open workbook, and point to the trusted 
' location of the workbook to open.
Dim sessionId As String = xlservice.OpenWorkbook(targetWorkbookPath, "en-US", "en-US", outStatus)

' Call the SetCell method to set the cell's value to 28.
' The cell is in the ninth row and second column, which is cell B9.
xlservice.SetCell(sessionId, sheetName, 8, 1, 28)

采用数字范围坐标，使用 SetRange 方法设置打开的工作簿中范围的值：

// Instantiate the Web service and make a status array object.
ExcelService xlservice = new ExcelService();
Status[] outStatus;
RangeCoordinates rangeCoordinates = new RangeCoordinates();

...
private void Form1_Load(object sender, EventArgs e)
{
...
...
//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;
...
...
}
private void SetRangeButton_Click(object sender, EventArgs e)
{
object[] values = new object[rangeCoordinates.Height];
string[] fieldValues =    
    SetRangeTextBox.Text.Split((",").ToCharArray());

if (fieldValues.Length != rangeCoordinates.Height * 
rangeCoordinate.Width)
    {
        throw new Exception("The number of inputs (" + 
            fieldValues.Length + ") does not match" +
            " the product of Height (" +             rangeCoordinates.Height + ") and Width (" + 
            rangeCoordinates.Width + ")");
    }

for (int i = 0; i < rangeCoordinates.Height; i++)
    {
        object[] currentRow = 
            new object[rangeCoordinates.Width];
        for (int j = 0; j < rangeCoordinates.Width; j++)
        {
            currentRow[j] = fieldValues[i *             rangeCoordinates.Width + j];
        }
        values[i] = currentRow;
    }

SetStatusText("Waiting for SetRange...");
outStatus = xlservice.SetRange(
    sessionID, SheetNameTextBox.Text, 
    rangeCoordinates, values);
}
catch (SoapException exc)
{
StopTimer("SetRange");
GenerateErrorMessage("SetRange", exc);
}
catch (Exception exc)
{
StopTimer("SetRange");
GenerateToolErrorMessage("While calling SetRange", exc);
}
}

' Instantiate the Web service and make a status array object.
Private xlservice As New ExcelService()
Private outStatus() As Status
Private rangeCoordinates As New RangeCoordinates()

...
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)
...
...
'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))
...
...
End Sub
Private Sub SetRangeButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim values(rangeCoordinates.Height - 1) As Object
Dim fieldValues() As String = SetRangeTextBox.Text.Split((",").ToCharArray())

If fieldValues.Length <> rangeCoordinates.Height * rangeCoordinate.Width Then
        Throw New Exception("The number of inputs (" & fieldValues.Length & ") does not match" & " the product of Height (" & rangeCoordinates.Height & ") and Width (" & rangeCoordinates.Width & ")")
End If

For i As Integer = 0 To rangeCoordinates.Height - 1
        Dim currentRow(rangeCoordinates.Width - 1) As Object
        For j As Integer = 0 To rangeCoordinates.Width - 1
            currentRow(j) = fieldValues(i * rangeCoordinates.Width + j)
        Next j
        values(i) = currentRow
Next i
Try
SetStatusText("Waiting for SetRange...")
outStatus = xlservice.SetRange(sessionID, SheetNameTextBox.Text, rangeCoordinates, values)
Catch exc As SoapException
StopTimer("SetRange")
GenerateErrorMessage("SetRange", exc)
Catch exc As Exception
StopTimer("SetRange")
GenerateToolErrorMessage("While calling SetRange", exc)
End Try
End Sub

使用 SetCellA1 和 SetRangeA1 方法设置值

采用 Excel"A1"范围规范，使用 SetCellA1 方法设置打开的工作簿中单元格的值：

// Instantiate the Web service and make a status array object.
ExcelService xlservice = new ExcelService();
Status[] outStatus;

xlservice.SetCellA1(sessionId, String.Empty, "InterestRateParam", 8);

' Instantiate the Web service and make a status array object.
Dim xlservice As New ExcelService()
Dim outStatus() As Status

xlservice.SetCellA1(sessionId, String.Empty, "InterestRateParam", 8)

采用 Excel"A1"范围规范，使用 SetRangeA1 方法获取打开的工作簿中范围的值：

// Instantiate the Web service and make a status array object.
ExcelService xlservice = new ExcelService();
Status[] outStatus;
...

void SetRangeA1Button_ServerClick(object sender, EventArgs e)
{
    int height, width;
    CalculateHeightAndWidth(RangeNameTextBox5.Value.Trim(), 
        out height, out width);

    object[] values = new object[height];
    string[] fieldValues = 
        RangeValuesTextBox1.Value.Split((",").ToCharArray());
    if (fieldValues.Length != height * width)
    {
        throw new Exception("The number of inputs (" + 
            fieldValues.Length + ") does not match" +
            " the product of Height (" + height + ") and 
            Width (" + width + ")");
    }

    for (int i = 0; i < height; i++)
    {
        object[] currentRow = new object[width];
        for (int j = 0; j < width; j++)
        {
            currentRow[j] = fieldValues[i * width + j];
        }
        values[i] = currentRow;
    }
    try
    {
        xlservice.SetRangeA1(SessionIDTextBox.Value, 
            SheetNameTextBox1.Value,RangeNameTextBox5.Value,
            values, out outStatus);
    }
    catch (SoapException exc)
    {
        ExceptionTextBox1.Value = exc.Message;
    }

}

' Instantiate the Web service and make a status array object.
Private xlservice As New ExcelService()
Private outStatus() As Status
...

Private Sub SetRangeA1Button_ServerClick(ByVal sender As Object, ByVal e As EventArgs)
    Dim height, width As Integer
    CalculateHeightAndWidth(RangeNameTextBox5.Value.Trim(), height, width)

    Dim values(height - 1) As Object
    Dim fieldValues() As String = RangeValuesTextBox1.Value.Split((",").ToCharArray())
    If fieldValues.Length <> height * width Then
        Throw New Exception("The number of inputs (" & fieldValues.Length & ") does not match" & " the product of Height (" & height & ") and Width (" & width & ")")
    End If

    For i As Integer = 0 To height - 1
        Dim currentRow(width - 1) As Object
        For j As Integer = 0 To width - 1
            currentRow(j) = fieldValues(i * width + j)
        Next j
        values(i) = currentRow
    Next i
    Try
        xlservice.SetRangeA1(SessionIDTextBox.Value, SheetNameTextBox1.Value,RangeNameTextBox5.Value, values, outStatus)
    Catch exc As SoapException
        ExceptionTextBox1.Value = exc.Message
    End Try

End Sub

请参阅

任务

如何：指定范围地址和工作表名称

如何：获取范围中的值

演练：使用 Excel Web Services 开发自定义应用程序

概念

访问 SOAP API

Last updated on 2015-06-15

通过

如何：设置范围的值

使用 SetCell 和 SetRange 方法设置值

使用 SetCellA1 和 SetRangeA1 方法设置值

请参阅

任务

概念

其他资源