Class PivotTable

Represents a PivotTable that summarizes and analyzes data from a worksheet range.

Inheritance

object

PivotTable

Inherited Members

object.ToString()

object.Equals(object)

object.Equals(object, object)

object.ReferenceEquals(object, object)

object.GetHashCode()

object.GetType()

object.MemberwiseClone()

Namespace: DRIT.Spreadsheet.Pivot

Assembly: DRIT.Spreadsheet.dll

Syntax

public class PivotTable

Properties

AltTextDescription

Gets or sets alternative, text based representation of the description.

Declaration

public string AltTextDescription { get; set; }

Property Value

Type	Description
string

AltTextTitle

Gets or sets alternative, text based representation of the title.

Declaration

public string AltTextTitle { get; set; }

Property Value

Type	Description
string

BuiltInStyle

Specifies the built-in pivot table style applied to this pivot table.

Declaration

public PivotStyleType? BuiltInStyle { get; set; }

Property Value

Type	Description
PivotStyleType?

Remarks

Excel UI: "PivotTable Design > PivotTable Styles" gallery.

Setting this property records the built-in style name so it is written to the OOXML pivotTableStyleInfo element on save. Set to None to remove the built-in style.

ColumnFields

Gets the collection of fields in the column area of the pivot table.

Declaration

public PivotFieldCollection ColumnFields { get; }

Property Value

Type	Description
PivotFieldCollection

ColumnHeaderCaption

Specifies the string to be displayed in column header in compact mode.

Declaration

public string ColumnHeaderCaption { get; set; }

Property Value

Type	Description
string

CustomListSort

Gets whether the "custom lists" option is offered when sorting this PivotTable.

Declaration

public bool CustomListSort { get; }

Property Value

Type	Description
bool

DataFields

Gets the collection of fields in the data area of the pivot table.

Declaration

public PivotFieldCollection DataFields { get; }

Property Value

Type	Description
PivotFieldCollection

DataSource

Gets the datasource range label of the pivot table.

Declaration

public string DataSource { get; }

Property Value

Type	Description
string

EditData

Gets whether the user is allowed to edit the cells in the data area of the PivotTable.

Declaration

public bool EditData { get; }

Property Value

Type	Description
bool

EnableDrill

Gets whether whether the user is prevented from drilling down on a PivotItem or aggregate value..

Declaration

public bool EnableDrill { get; }

Property Value

Type	Description
bool

EnableFieldProperties

Gets whether the user is prevented from displaying PivotField properties.

Declaration

public bool EnableFieldProperties { get; }

Property Value

Type	Description
bool

EnableGridDropZones

Gets whether the in-grid drop zones should be enabled.

Declaration

public bool EnableGridDropZones { get; }

Property Value

Type	Description
bool

EnableWizard

Gets whether the user is prevented from displaying the PivotTable wizard.

Declaration

public bool EnableWizard { get; }

Property Value

Type	Description
bool

ErrorCaption

Gets the string to be displayed in cells that contain errors.

Declaration

public string ErrorCaption { get; }

Property Value

Type	Description
string

FieldListSortAscending

Gets whether fields in the PivotTable are sorted in non-default order in the field list.

Declaration

public bool FieldListSortAscending { get; }

Property Value

Type	Description
bool

FieldPrintTitles

Gets whether the row and column titles from the PivotTable should be printed.

Declaration

public bool FieldPrintTitles { get; }

Property Value

Type	Description
bool

GrandTotalCaption

Gets the string to be displayed for grand totals.

Declaration

public string GrandTotalCaption { get; }

Property Value

Type	Description
string

Indent

Gets the indentation increment for compact axis and can be used to set the Report Layout to Compact Form.

Declaration

public int Indent { get; }

Property Value

Type	Description
int

ItemPrintTitles

Gets whether PivotItem names should be repeated at the top of each printed page.

Declaration

public bool ItemPrintTitles { get; }

Property Value

Type	Description
bool

MergeTitles

Gets whether row or column titles that span multiple cells should be merged into a single cell.

Declaration

public bool MergeTitles { get; }

Property Value

Type	Description
bool

MissingValueString

Gets the string to be displayed in cells with no value

Declaration

public string MissingValueString { get; }

Property Value

Type	Description
string

MultipleFieldFilters

Gets whether the fields of a PivotTable can have multiple filters set on them.

Declaration

public bool MultipleFieldFilters { get; }

Property Value

Type	Description
bool

Name

Specifies the PivotTable name.

Declaration

public string Name { get; set; }

Property Value

Type	Description
string

PageFieldWrapCount

Gets the number of page fields to display before starting another row or column.

Declaration

public int PageFieldWrapCount { get; }

Property Value

Type	Description
int

PageFields

Gets the collection of fields in the page area of the pivot table.

Declaration

public PivotFieldCollection PageFields { get; }

Property Value

Type	Description
PivotFieldCollection

PageOverThenDown

Gets how the page fields are laid out when there are multiple PivotFields in the page area.

Declaration

public bool PageOverThenDown { get; }

Property Value

Type	Description
bool	True indicates the fields will display "Over, then down". False indicates the fields will display "down, then Over"

PivotFilters

Gets the collection of advanced filters applied to this pivot table.

Declaration

public PivotFilterCollection PivotFilters { get; }

Property Value

Type	Description
PivotFilterCollection

PrintExpandCollapseButtons

Gets whether drill indicators should be printed.

Declaration

public bool PrintExpandCollapseButtons { get; }

Property Value

Type	Description
bool

RefreshDate

Gets the date when the PivotTable cache was last refreshed.

Declaration

public DateTime RefreshDate { get; }

Property Value

Type	Description
DateTime

RefreshOnLoad

Gets or sets whether the application will refresh the cache when the workbook has been opened.

Declaration

public bool RefreshOnLoad { get; set; }

Property Value

Type	Description
bool

RefreshedByName

Gets the name of the user who last refreshed the PivotTable cache.

Declaration

public string RefreshedByName { get; }

Property Value

Type	Description
string

RowFields

Gets the collection of fields in the row area of the pivot table.

Declaration

public PivotFieldCollection RowFields { get; }

Property Value

Type	Description
PivotFieldCollection

RowHeaderCaption

Specifies the string to be displayed in row header in compact mode.

Declaration

public string RowHeaderCaption { get; set; }

Property Value

Type	Description
string

SaveData

Gets whether the pivot records are saved with the cache.

Declaration

public bool SaveData { get; }

Property Value

Type	Description
bool

ShowContextualToolTips

Gets whether to show contextual tool tips.

Declaration

public bool ShowContextualToolTips { get; }

Property Value

Type	Description
bool

ShowEmptyColumn

Gets whether to include empty columns in the table.

Declaration

public bool ShowEmptyColumn { get; }

Property Value

Type	Description
bool

ShowEmptyRow

Gets whether to include empty rows in the table..

Declaration

public bool ShowEmptyRow { get; }

Property Value

Type	Description
bool

ShowError

Gets whether to show error messages in cells.

Declaration

public bool ShowError { get; }

Property Value

Type	Description
bool

ShowExpandCollapseButtons

Gets whether to show expand/collapse buttons.

Declaration

public bool ShowExpandCollapseButtons { get; }

Property Value

Type	Description
bool

ShowGrandTotalsForColumns

Exposes the show grand totals for columns associated with PivotTable.

Declaration

public bool ShowGrandTotalsForColumns { get; set; }

Property Value

Type	Description
bool	The show grand totals for columns returned by the operation.

ShowGrandTotalsForRows

Gets whether grand totals should be displayed for the PivotTable rows. The default value for this attribute is true.

Declaration

public bool ShowGrandTotalsForRows { get; set; }

Property Value

Type	Description
bool

ShowItemNames

Gets whether to display item names when adding a field onto a PivotTable that has no data fields.

Declaration

public bool ShowItemNames { get; }

Property Value

Type	Description
bool

ShowMissing

Gets whether to show a message in cells with no value.

Declaration

public bool ShowMissing { get; }

Property Value

Type	Description
bool

ShowPropertiesInTooltips

Gets or sets whether member property information is displayed in PivotTable tooltips.

Declaration

public bool ShowPropertiesInTooltips { get; }

Property Value

Type	Description
bool

SourceFields

Gets the collection of all fields available from the data source.

Declaration

public PivotFieldCollection SourceFields { get; }

Property Value

Type	Description
PivotFieldCollection

SubtotalHiddenItems

Gets whether data for hidden pivotItems for PivotFields in the data area should be included in subtotals.

Declaration

public bool SubtotalHiddenItems { get; }

Property Value

Type	Description
bool

TableStyle

Specifies the pivot table style to apply to the main table area of the PivotTable.

Declaration

public PivotTableStyle TableStyle { get; set; }

Property Value

Type	Description
PivotTableStyle

Worksheet

Gets the worksheet that contains this pivot table.

Declaration

public Worksheet Worksheet { get; }

Property Value

Type	Description
Worksheet

Methods

Calculate()

Computes aggregated values from the cache records and writes them into the worksheet cells, then updates Location.Ref, RowItems, ColItems and PivotField.Items in the definition.

Declaration

public void Calculate()

RefreshData()

Re-reads the data source and rebuilds the pivot cache records. Call this after changing source cell values so that a subsequent Calculate picks up the updated data.

Declaration

public void RefreshData()