ExcelService.GetRange Method

Gets calculated values from a range in the open workbook, using numeric range coordinates.

Namespace:  Microsoft.Office.Excel.Server.WebServices
Assembly:  Microsoft.Office.Excel.Server.WebServices (in Microsoft.Office.Excel.Server.WebServices.dll)

Syntax

'Declaration
<WebMethodAttribute> _
Public Function GetRange ( _
    sessionId As String, _
    sheetName As String, _
    rangeCoordinates As RangeCoordinates, _
    formatted As Boolean, _
    <OutAttribute> ByRef status As Status() _
) As Object()
'Usage
Dim instance As ExcelService
Dim sessionId As String
Dim sheetName As String
Dim rangeCoordinates As RangeCoordinates
Dim formatted As Boolean
Dim status As Status()
Dim returnValue As Object()

returnValue = instance.GetRange(sessionId, _
    sheetName, rangeCoordinates, formatted, _
    status)

[WebMethodAttribute]
public Object[] GetRange(
    string sessionId,
    string sheetName,
    RangeCoordinates rangeCoordinates,
    bool formatted,
    out Status[] status
)

Parameters

• sessionId
  Type: System.String

  The Excel Web Services session ID.

• sheetName
  Type: System.String

  The name of the sheet that you want to reference. Sheet name length is limited to 31 characters.

• rangeCoordinates
  Type: Microsoft.Office.Excel.Server.WebServices.RangeCoordinates

  The four integer coordinates used to select a contiguous range.

• formatted
  Type: System.Boolean

  true returns formatted strings.

  false returns raw values.

• status
  Type: []

  Alert information.

Return Value

Type: []
An array of values from a range of cells, using range coordinates to select the range.

Remarks

The returned array has the exact dimensionality of the requested range. Null objects represent empty cells.

Formatted values respect the cell's format or the default format, but are independent of any layout or grid modifications. Specifically, there is no concept of cell width for a value returned through the API, which means:

• No number signs (####) are returned if the cell width is too small to display the formatted value.

• In general format, no "precision displayed" rounding is done.

• No fill-in is performed, even if a fill-in character is defined in the cell format.

Excel Web Services returns a jagged array for this. It is an array of type "object". Each entry represents a row, and is defined as an array of type "object"; each entry is mapped to one cell value.

A null object represents an empty cell. The array is jagged technically, but should be rectangular in dimensionality, meaning that all row arrays should have the same number of entries.

If the formatted parameter is set to true, this method will return the Empty string for empty cells.

If the formatted parameter is set to false, this method will return Null for empty cells.

Examples

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

//Using workbookPath this way will allow 
//you to call the workbook remotely.
string targetWorkbookPath = "http://myserver02/example/Shared%20Documents/Book1.xlsx";

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

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

        //Prepare object to define range coordinates, 
        //and GetRange
  rangeCoordinates.Column = 2;
  rangeCoordinates.Row = 3;
        rangeCoordinates.Height = 12;
        rangeCoordinates.Width = 13;

        object[] rangeResult1 = xlservice.GetRange(sessionId, sheetName, rangeCoordinates, false, out outStatus);
        Console.WriteLine("Total Rows in Range: " + rangeResult1.Length);

        //Close workbook. This also closes session.
        xlservice.CloseWorkbook(sessionId);
    }
    catch (SoapException e)
    {
        Console.WriteLine("Exception Message: {0}", e.Message);
    }

See Also

Reference

ExcelService Class

ExcelService Members

Microsoft.Office.Excel.Server.WebServices Namespace