RangeSet Class

Range Set.When the object is serialized out as xml, its qualified name is x:rangeSet.

Inheritance Hierarchy

System.Object
  DocumentFormat.OpenXml.OpenXmlElement
    DocumentFormat.OpenXml.OpenXmlLeafElement
      DocumentFormat.OpenXml.Spreadsheet.RangeSet

Namespace:  DocumentFormat.OpenXml.Spreadsheet
Assembly:  DocumentFormat.OpenXml (in DocumentFormat.OpenXml.dll)

Syntax

'Declaration
Public Class RangeSet _
    Inherits OpenXmlLeafElement
'Usage
Dim instance As RangeSet

public class RangeSet : OpenXmlLeafElement

Remarks

[ISO/IEC 29500-1 1st Edition]

18.10.1.79 rangeSet (Range Set)

Represents a single range in the rangeSets collection. element is intended to facilitate creating a PivotTable report by consolidating SpreadsheetML ranges that have similar categories of data to be summarized. The simplest layout for the data source is for each rangeSets of data to be in list-like format, with column labels in the first row, row labels in the first column, the rest of the rows having similar items in the same row and column, and no blank rows or columns within the range. A particular rangeSet can consist of a built-in named range that is provided by the application, a user defined named range, a range reference, or a reference to an external workbook.

When multiple ranges are consolidated using this functionality, up to 4 custom report filters (also known as page fields) can be created to help filter the PivotTable report, by specifically enabling one or more of the individual ranges to be selected in the report filter. For each custom page field created, a custom label can be specified and assigned to each range participating in the consolidation range, so that the PivotTable can be filtered by one or more of the ranges being summarized.

[Example:Consider a workbook with 6 worksheets. On Sheet1 we have:

On Sheet2 we have:

… and so on up through Sheet5.

On Sheet6, we have the consolidated ranges being summarized by a PivotTable, and two page filters exist for the PivotTable.

Notice that for the second page filter, the items have been assigned a custom label, "one", "two", …, "five", for each of Sheet1, Sheet2, …, Sheet5 data sources, respectively. Similarly, the items have been assigned a custom label, "1", "2", …, "5" for each of Sheet1, Sheet2, …, Shet5 data sources, respectively.

The XML representing these custom page filters must be like the following:

<cacheSource type="consolidation">
<consolidation autoPage="0">
<pages count="2">
<page count="5">
<pageItem name="1"/>
<pageItem name="2"/>
<pageItem name="3"/>
<pageItem name="4"/>
<pageItem name="5"/>
</page>
      <page count="5">
<pageItem name="one"/>
<pageItem name="two"/>
<pageItem name="three"/>
<pageItem name="four"/>
<pageItem name="five"/>
</page>
</pages>
    <rangeSets count="5">
<rangeSet i1="0" i2="0" ref="A1:B3" sheet="Sheet1"/>
<rangeSet i1="1" i2="1" ref="A1:B3" sheet="Sheet2"/>
<rangeSet i1="2" i2="2" ref="A1:B3" sheet="Sheet3"/>
<rangeSet i1="3" i2="3" ref="A1:B3" sheet="Sheet4"/>
<rangeSet i1="4" i2="4" ref="A1:B3" sheet="Sheet5"/>
</rangeSets>
</consolidation>
</cacheSource>

end example]

[Note: Attributes i1, i2, i3, and i4 correspond to custom page fields created in the user interface. Spreadsheet ML only supports 4 custom page fields. end note]

[Note: The W3C XML Schema definition of this element’s content model (CT_RangeSet) is located in §A.2. end note]

© ISO/IEC29500: 2008.

Thread Safety

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

See Also

Reference

RangeSet Members

DocumentFormat.OpenXml.Spreadsheet Namespace