Export (0) Print
Expand All

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)

[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.

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.

//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);
    }

Show:
© 2015 Microsoft