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 ([MS-EXSPXML3] section 2.1.1) 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, and if the associated session is not an edit session, and if DisallowAutoNOV is false, 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.

        • 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.4.24. 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.

      • 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), which 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 will locate 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 published item's 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.

  • 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, and if DisallowAutoNOV is false:

  • 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) 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).

      • 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 in its entirety.

        • 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, or if DisallowAutoNOV is true:

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

  • First row (2) of baseline range+VisibleRowsRequested MUST be the last row (2) 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 1,000,000.

The first row (2) of the retrieved range MUST be one of 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 baseline range+VisibleFirstRowOffset, if VisibleFirstRowOffset is less than 0.

    • If baseline range+VisibleFirstRowOffset is less than 0, 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 VisibleRowsRequested visible rows (2) 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 (2) 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 number of visible rows (2) in the retrieved range MUST be equal to VisibleRowsRequested, unless there are not enough visible rows (2) in the baseline range to retrieve, in which case all the remaining visible rows (2) in the baseline range MUST be retrieved.

The first column of the retrieved range MUST be:

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

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

    • If baseline range+VisibleFirstColumnOffset is less than 0, 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 will be 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 0 and less than or equal to 16,383.

The number 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 encompassed 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 Workbook ([MS-EXSPXML3] section 2.1.1) will contain the CT_Images element ([MS-EXSPXML3] section 2.3.34) with a single CT_Image element ([MS-EXSPXML3] section 2.3.33). The type attribute for the CT_Image element ([MS-EXSPXML3] section 2.3.33) MUST be chart, as specified in [MS-EXSPXML3] section 2.3.33.

 <xs:complexType name="VisibleSheetRange" xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <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="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:element minOccurs="1" maxOccurs="1" name="DisallowAutoNOV" type="xs:boolean"/>
     <xs:element minOccurs="1" maxOccurs="1" name="FallBackOnInvalidSheet" type="xs:boolean"/>
     <xs:element minOccurs="1" maxOccurs="1" name="ReturnAllRelatedSlicers" type="xs:boolean"/>
     <xs:element minOccurs="1" maxOccurs="1" name="RenderingOptions" type="tns:GridRenderingOptions"/>
     <xs:element minOccurs="1" maxOccurs="1" name="BootstrapDataOnly" type="xs:boolean"/>
     <xs:element minOccurs="1" maxOccurs="1" name="TrimEmptyCells" type="xs:boolean"/>
   </xs:sequence>
 </xs: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.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, then 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 (2). MUST be greater than or equal to –1 and less than or equal to 1,048,575.

VisibleRowsRequested: The number of visible rows (2) 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. MUST be greater than or equal to –1 and less than or equal to 16,383.

VisibleColumnsRequested: The number 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 –1,048,575 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 –16,383 and less than or equal to 16,383.

CalculateVisibleOffsetFromOrigin: Specifies whether the protocol server will return information about the offsets for the first visible column and visible row (2), from the first column and row (2) of the baseline range. If true, then the returned Workbook ([MS-EXSPXML3] section 2.1.1) will contain the CurrentFirstRowVisibleOffset and CurrentFirstColumnVisibleOffset attributes in the CT_Table element ([MS-EXSPXML3] section 2.3.35).

NeedAllBorderValues: Specifies whether the protocol server will return information about all of the borders.  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, as specified in the ST_enmBorderPosition enumeration in [MS-EXSPXML3] section 2.4.28.

ChartsOnly: Specifies whether the protocol server will return 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.

DisallowAutoNOV: Specifies the behavior to use when determining the baseline range as described previously.

FallBackOnInvalidSheet: Specifies whether the protocol server retrieves the range from the default sheet (2) if the sheet (2) specified by SheetName does not exist.

ReturnAllRelatedSlicers: Specifies whether the protocol server returns all the slicers related to ObjectName.

If true, and ObjectName is present and not empty:

  • and the published item specified by ObjectName is a PivotTable, the protocol server MUST return all slicers whose data source is the same as the data source of that PivotTable.

  • and the published item specified by ObjectName is a table, the protocol server MUST return all slicers whose data source is a column in that table.

  • and the published item specified by ObjectName is a chart, the protocol server MUST return all slicers whose data source is the same as the data source of that chart.

In all other cases, the protocol server MUST return only the slicers whose range intersects with the baseline range.

RenderingOptions:  A GridRenderingOptions element as specified in section 2.2.5.19. Specifies options that the protocol servers MUST use when generating the returned Workbook ([MS-EXSPXML3] section 2.1.1) as follows:

  • If GridRenderingOptions.HideGridlines is specified, then the returned Workbook ([MS-EXSPXML3] section 2.1.1) MUST NOT contain the default gridlines for the range.

  • If GridRenderingOptions.HidRowAndColumnHeaders is specified, then the returned Workbook ([MS-EXSPXML3] section 2.1.1) MUST NOT contain row and column headers for the range.

BootstrapDataOnly: Specifies the behavior of the protocol server with respect to information about interactive report in the retrieved range as follows:

  • If the retrieved range intersects with an interactive report:

    • If BootstrapDataOnly, is true, then the returned Workbook ([MS-EXSPXML3] section 2.1.1) MUST NOT contain the InteractiveReport element of type CT_InteractiveReport ([MS-EXSPXML3] section 2.3.59).

    • If BootstrapDataOnly, is false, then the returned Workbook ([MS-EXSPXML3] section 2.1.1) MUST contain the InteractiveReport element of type CT_InteractiveReport ([MS-EXSPXML3] section 2.3.59).

  • Otherwise, if the retrieved range does not interact with an interactive report, BootstrapDataOnly MUST be ignored by the protocol server.

TrimEmptyCells: Specifies whether the protocol server will exclude information from the retrieved range about the following:

  • Any columns (2) between the last visible column (2) which contains a non-empty cell (not inclusive) and the last visible column (inclusive);

  • Any rows (2) between the last visible row (2) which contains a non-empty cell (not inclusive) and the last visible row (2) (inclusive);