Share via

2.2.4.25 VisibleSheetRange

  • Article

The VisibleSheetRange complex type is used to specify the SpreadsheetDataXML range (section 2.2.4.24) to be returned in the response message.

The baseline range, in the context of this type and the operation that uses it, is the raw range initially found on the workbook before it is modified to only include visible worksheet cells.

If the ObjectName element is present and not empty, the following apply:

  • If the ExcelRange element is present and not empty:

    • The sheet (2) of the baseline range is determined in the following manner:

      • If a sheet (2) was specified by ExcelRange, that is the sheet (2) which will be used.

      • Otherwise, the protocol server will locate a published item by matching ObjectName to the name of the published item name.

        • If a match is found, the sheet (2) which contains the matching published item MUST be used.

        • Otherwise, the protocol server MUST return an ExcelServerMessage as specified in section 2.2.9.2. The id element of the ExcelServerMessage MUST be EcsInvalidNamedObject.

    • The protocol server will locate a published range which exactly matches ExcelRange on the baseline range's sheet (2).

      • If a match is found, the published range is the baseline range.

      • Otherwise, the protocol server MUST be a published item on the baseline range's sheet (2), which contains ExcelRange completely.

        • If a match was found, the published item is the baseline range.

        • Otherwise, the protocol server MUST return an ExcelServerMessage as specified in section 2.2.9.2. The id element of the ExcelServerMessage MUST be EcsInvalidNamedObject.

  • If ExcelRange is not present or is empty, the protocol server will locate ObjectName in all the published items that belong to the workbook by matching ObjectName to the published item's name, and the following apply.

    • If a match was found, the baseline range MUST be the published item's range.

    • Otherwise, the protocol server MUST return an ExcelServerMessage as specified in section 2.2.9.2. The id element of the ExcelServerMessage MUST be EcsInvalidNamedObject.

  • The protocol server MUST NOT use any range outside of the baseline range to retrieve data from.

If ObjectName is not present or is empty and the workbook contains published items, the following apply:

  • If ExcelRange is present and not empty, the following apply:

    • The sheet (2) of the baseline range MUST be determined in the following manner:

      • If a sheet (2) was specified by ExcelRange, that is the sheet (2) which MUST be used.

      • Otherwise, the sheet (2) which contains the first published item MUST be used.

    • The protocol server will locate a published range which exactly matches ExcelRange on the baseline range's sheet (2) in the following manner:

      • If a match is found, the published range is the baseline range.

      • Otherwise, the protocol server will locate a published item on the baseline range's sheet (2), which contains ExcelRange completely.

        • If a match was found, the published item is the baseline range.

        • Otherwise, the protocol server MUST return an ExcelServerMessage as specified in section 2.2.9.2. The id element of the ExcelServerMessage MUST be EcsInvalidNamedObject.

  • If ExcelRange is not present or is empty, the baseline range MUST be the range of the first published item.

  • The protocol server MUST NOT use any range outside of the baseline range to retrieve data from.

If ObjectName is not present or is empty and the workbook does not contain any published items:

  • If SheetName is not present or empty, the baseline range MUST be the default sheet. Otherwise, SheetName MUST be the sheet (2) of the baseline range.

  • If FirstRow is -1, the default row MUST be the first row (2) of the baseline range. Otherwise, FirstRow MUST be the first row (2) of the baseline range.

  • If FirstColumn is -1, the default column MUST be the first column (2) of the baseline range. Otherwise, FirstColumn MUST be the first column (2) of the baseline range.

  • First row (2) of the baseline range plus VisibleRowsRequested MUST be the last row (2) of the baseline range.

  • First column (2) of the baseline range plus VisibleColumnsRequested MUST be the last column (2) of the baseline range.

  • FirstColumn MUST be -1 if FirstRow is -1.

  • FirstRow MUST be -1 if FirstColumn is -1.

The product of VisibleRowsRequested and VisibleColumnsRequested MUST be less than or equal to 1000000.

The first row (2) of the retrieved range MUST be the following:

  • The first visible row (2) at or after first row (2) of baseline range+VisibleFirstRowOffset, if VisibleFirstRowOffset is greater than or equal to 0.The first visible row (2) at or before first row (2) of the baseline range plus VisibleFirstRowOffset, if VisibleFirstRowOffset is less than 0.If there are less than VisibleRowsRequested visible rows between that first visible row (2) and the last visible row (2) of the baseline range, the first row (2) will be the first visible row (2) such that there are VisibleRowsRequested rows between it and the last visible row (2) of baseline range.

  • Greater than or equal to 0, and less than or equal to 1,048,575.

The amount of visible rows in the retrieved range MUST be equal to VisibleRowsRequested, unless there are not enough visible rows in the baseline range to retrieve, in which case all the remaining visible rows in the baseline range MUST be retrieved.

The first column (2) of the retrieved range MUST be:

  • The first visible column (2) at or after first column (2) of the baseline range plus VisibleFirstColumnOffset, if VisibleFirstColumnOffset is greater than or equal to 0.

  • The first visible column (2) at or before first column (2) of the baseline range plus VisibleFirstColumnOffset, if VisibleFirstColumnOffset is less than 0.

  • If there are less than VisibleColumnsRequested visible columns between that first visible column (2) and the last visible column (2) of the baseline range, the first column (2) will be the first visible column (2) such that there are VisibleColumnsRequested columns between it and the last visible column (2) of baseline range.

  • Greater than or equal to 0 and less than or equal to 16,383.

The amount of visible columns in the retrieved range MUST be equal to VisibleColumnsRequested unless there are not enough visible columns in the baseline range to retrieve, in which case all the remaining visible columns in the baseline range MUST be retrieved.

If the retrieved range is completely covered by a published chart, and either a) ObjectName is present and not empty, or b) ObjectName is not present or is empty, and the workbook contains published items, then the returned SpreadsheetDataXML will contain the Images element (section 2.2.4.24.33) with a single Image element (section 2.2.4.24.34). The type attribute of the Image element (section 2.2.4.24.34) MUST be "chart", as specified section 2.2.4.24.57.

 <s:complexType name="VisibleSheetRange">
   <s:sequence>
     <s:element name="SheetName" type="s:string" minOccurs="0"/>
     <s:element name="ObjectName" type="s:string" minOccurs="0"/>
     <s:element name="ExcelRange" type="s:string" minOccurs="0"/>
     <s:element name="FirstRow" type="s:int"/>
     <s:element name="VisibleRowsRequested" type="s:int"/>
     <s:element name="FirstColumn" type="s:int"/>
     <s:element name="VisibleColumnsRequested" type="s:int"/>
     <s:element name="VisibleFirstRowOffset" type="s:int"/>
     <s:element name="VisibleFirstColumnOffset" type="s:int"/>
   </s:sequence>
 </s:complexType>

SheetName: The name of the sheet (2) to retrieve the range from. MUST NOT be longer than 31 characters.

ObjectName: The name of the published item to retrieve the range from. If the value is longer than 287 characters, then the protocol server MUST return an ExcelServerMessage as specified in section 2.2.9.2. The id element of the ExcelServerMessage MUST be EcsInvalidNamedObject.

ExcelRange: A RangeReference to the range to retrieve. MUST follow the RangeReference specification in section 2.2.5.10, with the following exceptions:

  • The protocol server MUST support the R1C1 notation.

  • If the value is longer than 287 characters, then the protocol server MUST return an ExcelServerMessage as specified in section 2.2.9.2, with the value of the id element of the ExcelServerMessage set to EcsInvalidNamedObject.

FirstRow: The zero-based index of the first row (2). MUST be greater than or equal to -1 and less than or equal to 1,048,575.

VisibleRowsRequested: The amount of visible rows to retrieve in the range. MUST be greater than 0 and less than or equal to 1,048,575.

FirstColumn: The zero-based index of the first column (2). MUST be greater than or equal to -1 and less than or equal to 16,383.

VisibleColumnsRequested: The amount of visible columns to retrieve in the range. MUST be greater than 0 and less than or equal to 16,383.

VisibleFirstRowOffset: The visible row (2) offset from FirstRow of the range to retrieve. MUST be greater than or equal to -1048575 and less than or equal to 1,048,575.

VisibleFirstColumnOffset: The visible column (2) offset from FirstColumn of the range to retrieve. MUST be greater than or equal to -16383 and less than or equal to 16,383.