2.2.4.44 VisibleSheetRange

  • Article

Namespace: http://schemas.microsoft.com/office/Excel/Server/WebServices/ExcelServerInternalService/

The VisibleSheetRange complex type is used to specify the Workbook, as specified in [MS-EXSPXML] section 2.1.1, to be returned in the response message.

 <xs:complexType name="VisibleSheetRange">
   <xs:sequence>
     <xs:element minOccurs="0" maxOccurs="1" name="SheetName" type="xs:string"/>
     <xs:element minOccurs="0" maxOccurs="1" name="ObjectName" type="xs:string"/>
     <xs:element minOccurs="0" maxOccurs="1" name="ExcelRange" type="xs:string"/>
     <xs:element minOccurs="1" maxOccurs="1" name="FirstRow" type="xs:int"/>
     <xs:element minOccurs="1" maxOccurs="1" name="VisibleRowsRequested" type="xs:int"/>
     <xs:element minOccurs="1" maxOccurs="1" name="FirstColumn" type="xs:int"/>
     <xs:element minOccurs="1" maxOccurs="1" name="VisibleColumnsRequested" type="xs:int"/>
     <xs:element minOccurs="1" maxOccurs="1" name="VisibleFirstRowOffset" type="xs:int"/>
     <xs:element minOccurs="1" maxOccurs="1" name="VisibleFirstColumnOffset" type="xs:int"/>
     <xs:element minOccurs="1" maxOccurs="1" name="NoAutoAdjustToPublishedUsedRange" type="xs:boolean"/>
     <xs:element minOccurs="1" maxOccurs="1" name="CalculateVisibleOffsetFromOrigin" type="xs:boolean"/>
     <xs:element minOccurs="1" maxOccurs="1" name="NeedAllBorderValues" type="xs:boolean"/>
     <xs:element minOccurs="1" maxOccurs="1" name="ChartsOnly" type="xs:boolean"/>
   </xs:sequence>
 </xs:complexType>

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, and if the associated session is not an edit session, 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) that is used.

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

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

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

    • The protocol server locates a published range that exactly matches ExcelRange on the sheet of the baseline range.

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

      • Otherwise, the protocol server MUST locate a published item on the baseline range's sheet (2) that exactly matches ExcelRange.

        • 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.4.24. The Id element of the ExcelServerMessage MUST be "EcsInvalidNamedObject".

  • If ExcelRange is not present or is empty, the protocol server locates ObjectName in all the published items that belong to the workbook by matching ObjectName to the name of the published item.

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

    • Otherwise, the protocol server MUST return an ExcelServerMessage, as specified in the section 2.2.4.24. 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, and if the associated session is not an edit session:

  • If ExcelRange is present and not empty:

    • 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) that contains the first published item MUST be used.

    • The protocol server locates a published range that 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 locates a published item on the baseline range's sheet (2) that exactly matches ExcelRange.

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

        • Otherwise, the protocol server MUST return an ExcelServerMessage, as specified in the section 2.2.4.24. 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, or if the associated session is an edit session:

  • 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 of the baseline range. Otherwise, FirstRow MUST be the first row of the baseline range.

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

  • First row of baseline range+VisibleRowsRequested MUST be the last row of the baseline range.

  • First column of baseline range+VisibleColumnsRequested MUST be the last column 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 of the retrieved range MUST be one of the following:

  • The first visible row at or after the first row of baseline range+VisibleFirstRowOffset, if VisibleFirstRowOffset is greater than or equal to zero.

  • The first visible row at or before the first row of baseline range+VisibleFirstRowOffset, if VisibleFirstRowOffset is less than zero.

    • If baseline range+VisibleFirstRowOffset is less than zero, the protocol server MUST return an ExcelServerMessage, as specified in section 2.2.4.24. The Id element of the ExcelServerMessage MUST be "SpecifiedRangeNotFound".

  • If there are less than VisibleRowsRequested visible rows between that first visible row and the last visible row of the baseline range, the first row is the first visible row such that there are VisibleRowsRequested rows between it and the last visible row of baseline range.

  • Greater than or equal to zero, and less than or equal to 1048575.

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

The first column of the retrieved range MUST be:

  • The first visible column at or after the first column of baseline range+VisibleFirstColumnOffset, if VisibleFirstColumnOffset is greater than or equal to zero.

  • The first visible column at or before the first column of baseline range+VisibleFirstColumnOffset, if VisibleFirstColumnOffset is less than zero.

    • If baseline range+VisibleFirstColumnOffset is less than zero, the protocol server MUST return an ExcelServerMessage, as specified in the section 2.2.4.24. The Id element of the ExcelServerMessage MUST be "SpecifiedRangeNotFound".

  • If there are less than VisibleColumnsRequested visible columns between that first visible column and the last visible column of the baseline range, the first column is the first visible column such that there are VisibleColumnsRequested columns between it and the last visible column of baseline range.

  • Greater than or equal to zero 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 covered by a published chart, and either ObjectName is present and not empty, or ObjectName is not present or is empty, and the workbook contains published items, the returned Workbook ([MS-EXSPXML] section 2.1.1) contains the CT_Images element ( [MS-EXSPXML] section 2.2.34) with a single CT_Image element ( [MS-EXSPXML] section 2.2.33). The type attribute for the CT_Image element ( [MS-EXSPXML] section 2.2.33) MUST be chart, as specified in [MS-EXSPXML] section 2.2.33.

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, the protocol server MUST return an ExcelServerMessage, as specified in section 2.2.4.24. The Id element of the ExcelServerMessage MUST be "EcsInvalidNamedObject".

ExcelRange: A RangeReference to the range to retrieve. MUST follow the RangeReference specification, with the following exceptions: The protocol server MUST support the R1C1 notation. If the value is longer than 287 characters, the protocol server MUST return an ExcelServerMessage, as specified in section 2.2.4.24, with the value of the Id element of the ExcelServerMessage set to "EcsInvalidNamedObject".

FirstRow: The zero-based index of the first row. 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 zero and less than or equal to 1,048,575.

FirstColumn: The zero-based index of the first column. 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 zero and less than or equal to 16,383.

VisibleFirstRowOffset: The visible row 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 offset from FirstColumn of the range to retrieve. MUST be greater than or equal to -16383 and less than or equal to 16,383.

NoAutoAdjustToPublishedUsedRange: MUST be ignored by the protocol server.

CalculateVisibleOffsetFromOrigin: Specifies whether the protocol server returns information about the offsets for the first visible column and visible row, from the first column and row of the baseline range. If "true", the returned Workbook ([MS-EXSPXML] section 2.1.1) contains the CurrentFirstRowVisibleOffset and CurrentFirstColumnVisibleOffset attributes in the CT_Table element ([MS-EXSPXML] section 2.2.35).

NeedAllBorderValues: Specifies whether the protocol server returns information about all of the borders of a cell. MUST be present. If "true", the protocol server returns information about all borders. If "false", the protocol server returns information about the bottom and right borders of a cell, as specified in the ST_enmBorderPosition enumeration in [MS-EXSPXML] section 2.3.20.

ChartsOnly: Specifies whether the protocol server returns information about only charts. MUST be present. If "true", the protocol server returns only information pertaining to charts. If "false", the protocol server returns information pertaining to charts and worksheet cells.