Get Started

API Reference

Class

PivotTableBuilder

Edit on GitHub
Namespace OfficeIMO.Excel.Fluent
Assembly OfficeIMO.Excel
Source OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:48
Modifiers sealed

Fluent builder for creating pivot tables from an A1 source range.

Inheritance

  • Object
  • PivotTableBuilder

Usage

This type appears in these public API surfaces even when no hand-authored example is attached directly to the page.

Returned or exposed by

Methods

public ExcelSheet At(String destinationCell, String name = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:325
Returns: ExcelSheet

Creates the pivot table at the destination cell and returns the source sheet.

Parameters

destinationCell System.String requiredposition: 0
name System.String = null optionalposition: 1
public PivotTableBuilder Average(String fieldName, String displayName = null, String numberFormat = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:79
Returns: PivotTableBuilder

Adds an Average data field.

Parameters

fieldName System.String requiredposition: 0
displayName System.String = null optionalposition: 1
numberFormat System.String = null optionalposition: 2
public PivotTableBuilder CalculatedField(String name, String formula, String caption = null, Nullable<UInt32> numberFormatId = null, String numberFormat = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:289
Returns: PivotTableBuilder

Adds a formula-backed calculated pivot field.

Parameters

name System.String requiredposition: 0
formula System.String requiredposition: 1
caption System.String = null optionalposition: 2
numberFormatId System.Nullable{System.UInt32} = null optionalposition: 3
numberFormat System.String = null optionalposition: 4
public PivotTableBuilder Captions(String rowHeader = null, String columnHeader = null, String grandTotal = null, String missing = null, String error = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:167
Returns: PivotTableBuilder

Sets pivot table captions.

Parameters

rowHeader System.String = null optionalposition: 0
columnHeader System.String = null optionalposition: 1
grandTotal System.String = null optionalposition: 2
missing System.String = null optionalposition: 3
error System.String = null optionalposition: 4
public PivotTableBuilder Columns(params String[] fieldNames) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:54
Returns: PivotTableBuilder

Adds one or more column fields by source header name.

Parameters

fieldNames System.String[] requiredposition: 0
public PivotTableBuilder Count(String fieldName, String displayName = null, String numberFormat = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:74
Returns: PivotTableBuilder

Adds a Count data field.

Parameters

fieldName System.String requiredposition: 0
displayName System.String = null optionalposition: 1
numberFormat System.String = null optionalposition: 2
public PivotTableBuilder DateGroup(String fieldName, GroupByValues groupBy, Nullable<DateTime> startDate = null, Nullable<DateTime> endDate = null, Nullable<Double> interval = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:307
Returns: PivotTableBuilder

Adds date grouping metadata for a pivot field.

Parameters

fieldName System.String requiredposition: 0
groupBy DocumentFormat.OpenXml.Spreadsheet.GroupByValues requiredposition: 1
startDate System.Nullable{System.DateTime} = null optionalposition: 2
endDate System.Nullable{System.DateTime} = null optionalposition: 3
interval System.Nullable{System.Double} = null optionalposition: 4
public PivotTableBuilder DateHierarchy(String fieldName, params GroupByValues[] levels) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:313
Returns: PivotTableBuilder

Adds generated date hierarchy fields for a pivot field, such as years, quarters, and months.

Parameters

fieldName System.String requiredposition: 0
levels DocumentFormat.OpenXml.Spreadsheet.GroupByValues[] requiredposition: 1
public PivotTableBuilder Display(Nullable<Boolean> dataOnRows = null, Nullable<Boolean> showHeaders = null, Nullable<Boolean> showEmptyRows = null, Nullable<Boolean> showEmptyColumns = null, Nullable<Boolean> showDrill = null, Nullable<Boolean> showDataDropDown = null, Nullable<Boolean> showDropZones = null, Nullable<Boolean> showDataTips = null, Nullable<Boolean> showMemberPropertyTips = null, Nullable<Boolean> fieldListSortAscending = null, Nullable<Boolean> customListSort = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:146
Returns: PivotTableBuilder

Controls common pivot display flags.

Parameters

dataOnRows System.Nullable{System.Boolean} = null optionalposition: 0
showHeaders System.Nullable{System.Boolean} = null optionalposition: 1
showEmptyRows System.Nullable{System.Boolean} = null optionalposition: 2
showEmptyColumns System.Nullable{System.Boolean} = null optionalposition: 3
showDrill System.Nullable{System.Boolean} = null optionalposition: 4
showDataDropDown System.Nullable{System.Boolean} = null optionalposition: 5
showDropZones System.Nullable{System.Boolean} = null optionalposition: 6
showDataTips System.Nullable{System.Boolean} = null optionalposition: 7
showMemberPropertyTips System.Nullable{System.Boolean} = null optionalposition: 8
fieldListSortAscending System.Nullable{System.Boolean} = null optionalposition: 9
customListSort System.Nullable{System.Boolean} = null optionalposition: 10
public PivotTableBuilder FieldBreaks(String fieldName, Nullable<Boolean> insertBlankRow = null, Nullable<Boolean> insertPageBreak = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:240
Returns: PivotTableBuilder

Controls blank-row and page-break insertion after pivot field items.

Parameters

fieldName System.String requiredposition: 0
insertBlankRow System.Nullable{System.Boolean} = null optionalposition: 1
insertPageBreak System.Nullable{System.Boolean} = null optionalposition: 2
public PivotTableBuilder FieldDisplay(String fieldName, Nullable<Boolean> showAll = null, Nullable<Boolean> showDropDowns = null, Nullable<Boolean> multipleItemSelectionAllowed = null, Nullable<Boolean> includeNewItemsInFilter = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:250
Returns: PivotTableBuilder

Controls common pivot field display flags.

Parameters

fieldName System.String requiredposition: 0
showAll System.Nullable{System.Boolean} = null optionalposition: 1
showDropDowns System.Nullable{System.Boolean} = null optionalposition: 2
multipleItemSelectionAllowed System.Nullable{System.Boolean} = null optionalposition: 3
includeNewItemsInFilter System.Nullable{System.Boolean} = null optionalposition: 4
public PivotTableBuilder FieldLayout(String fieldName, Nullable<Boolean> compact = null, Nullable<Boolean> outline = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:235
Returns: PivotTableBuilder

Controls compact and outline layout flags for a pivot field.

Parameters

fieldName System.String requiredposition: 0
compact System.Nullable{System.Boolean} = null optionalposition: 1
outline System.Nullable{System.Boolean} = null optionalposition: 2
public PivotTableBuilder FieldNumberFormat(String fieldName, String numberFormat) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:260
Returns: PivotTableBuilder

Sets a number format code for a pivot field.

Parameters

fieldName System.String requiredposition: 0
numberFormat System.String requiredposition: 1
public PivotTableBuilder FieldNumberFormatId(String fieldName, UInt32 numberFormatId) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:266
Returns: PivotTableBuilder

Sets a built-in or custom number format id for a pivot field.

Parameters

fieldName System.String requiredposition: 0
numberFormatId System.UInt32 requiredposition: 1
public PivotTableBuilder FieldOptions(params ExcelPivotFieldOptions[] options) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:177
Returns: PivotTableBuilder

Adds field-level display, sort, format, and item-filter options.

Parameters

options OfficeIMO.Excel.ExcelPivotFieldOptions[] requiredposition: 0
public PivotTableBuilder Filter(params ExcelPivotFilter[] filters) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:277
Returns: PivotTableBuilder

Adds label or value filters to the pivot table.

Parameters

filters OfficeIMO.Excel.ExcelPivotFilter[] requiredposition: 0
public PivotTableBuilder Filters(params String[] fieldNames) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:60
Returns: PivotTableBuilder

Adds one or more page/filter fields by source header name.

Parameters

fieldNames System.String[] requiredposition: 0
public PivotTableBuilder GrandTotals(Boolean rows = true, Boolean columns = true) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:128
Returns: PivotTableBuilder

Controls row and column grand totals.

Parameters

rows System.Boolean = true optionalposition: 0
columns System.Boolean = true optionalposition: 1
public PivotTableBuilder Group(params ExcelPivotGrouping[] groupings) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:295
Returns: PivotTableBuilder

Adds date or numeric grouping metadata for pivot fields.

Parameters

groupings OfficeIMO.Excel.ExcelPivotGrouping[] requiredposition: 0
public PivotTableBuilder HideItems(String fieldName, params String[] items) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:189
Returns: PivotTableBuilder

Hides specific source items for a pivot field.

Parameters

fieldName System.String requiredposition: 0
items System.String[] requiredposition: 1
public PivotTableBuilder Layout(ExcelPivotLayout layout) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:122
Returns: PivotTableBuilder

Sets the pivot table layout.

Parameters

layout OfficeIMO.Excel.ExcelPivotLayout requiredposition: 0
public PivotTableBuilder Max(String fieldName, String displayName = null, String numberFormat = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:89
Returns: PivotTableBuilder

Adds a Max data field.

Parameters

fieldName System.String requiredposition: 0
displayName System.String = null optionalposition: 1
numberFormat System.String = null optionalposition: 2
public PivotTableBuilder Min(String fieldName, String displayName = null, String numberFormat = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:84
Returns: PivotTableBuilder

Adds a Min data field.

Parameters

fieldName System.String requiredposition: 0
displayName System.String = null optionalposition: 1
numberFormat System.String = null optionalposition: 2
public PivotTableBuilder NumberGroup(String fieldName, Double interval, Nullable<Double> startNumber = null, Nullable<Double> endNumber = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:319
Returns: PivotTableBuilder

Adds numeric range grouping metadata for a pivot field.

Parameters

fieldName System.String requiredposition: 0
interval System.Double requiredposition: 1
startNumber System.Nullable{System.Double} = null optionalposition: 2
endNumber System.Nullable{System.Double} = null optionalposition: 3
public PivotTableBuilder Pages(params String[] fieldNames) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:65
Returns: PivotTableBuilder

Adds one or more page/filter fields by source header name.

Parameters

fieldNames System.String[] requiredposition: 0
public PivotTableBuilder PercentOfTotal(String fieldName, String displayName = null, String numberFormat = "0.0%") #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:109
Returns: PivotTableBuilder

Adds a Sum data field shown as a percentage of the pivot grand total.

Parameters

fieldName System.String requiredposition: 0
displayName System.String = null optionalposition: 1
numberFormat System.String = "0.0%" optionalposition: 2
public PivotTableBuilder Rows(params String[] fieldNames) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:48
Returns: PivotTableBuilder

Adds one or more row fields by source header name.

Parameters

fieldNames System.String[] requiredposition: 0
public PivotTableBuilder SelectPageItem(String fieldName, String item) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:209
Returns: PivotTableBuilder

Selects an item for a page/filter field and adds the field to the page area if needed.

Parameters

fieldName System.String requiredposition: 0
item System.String requiredposition: 1
public PivotTableBuilder ShowOnlyItems(String fieldName, params String[] items) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:199
Returns: PivotTableBuilder

Shows only specific source items for a pivot field.

Parameters

fieldName System.String requiredposition: 0
items System.String[] requiredposition: 1
public PivotTableBuilder SortField(String fieldName, FieldSortValues sortType) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:220
Returns: PivotTableBuilder

Sets the sort mode for a pivot field.

Parameters

fieldName System.String requiredposition: 0
sortType DocumentFormat.OpenXml.Spreadsheet.FieldSortValues requiredposition: 1
public PivotTableBuilder Style(String pivotStyleName) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:114
Returns: PivotTableBuilder

Sets the pivot table style name, for example PivotStyleMedium9.

Parameters

pivotStyleName System.String requiredposition: 0
public PivotTableBuilder SubtotalCaption(String fieldName, String caption) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:271
Returns: PivotTableBuilder

Sets a custom subtotal caption for a pivot field.

Parameters

fieldName System.String requiredposition: 0
caption System.String requiredposition: 1
public PivotTableBuilder Subtotals(String fieldName, Boolean enabled = true) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:225
Returns: PivotTableBuilder

Controls whether the pivot field uses its default subtotal.

Parameters

fieldName System.String requiredposition: 0
enabled System.Boolean = true optionalposition: 1
public PivotTableBuilder SubtotalsAtTop(String fieldName, Boolean enabled = true) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:230
Returns: PivotTableBuilder

Controls whether subtotals are shown at the top for a pivot field.

Parameters

fieldName System.String requiredposition: 0
enabled System.Boolean = true optionalposition: 1
public PivotTableBuilder Sum(String fieldName, String displayName = null, String numberFormat = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:69
Returns: PivotTableBuilder

Adds a Sum data field.

Parameters

fieldName System.String requiredposition: 0
displayName System.String = null optionalposition: 1
numberFormat System.String = null optionalposition: 2
public PivotTableBuilder Value(String fieldName, DataConsolidateFunctionValues function, String displayName = null, Nullable<UInt32> numberFormatId = null, String numberFormat = null, Nullable<ShowDataAsValues> showDataAs = null, Nullable<Int32> baseField = null, Nullable<UInt32> baseItem = null) #
OfficeIMO.Excel/Fluent/PivotTableBuilder.cs:102
Returns: PivotTableBuilder

Adds a data field using a specific aggregation function.

Parameters

fieldName System.String requiredposition: 0
function DocumentFormat.OpenXml.Spreadsheet.DataConsolidateFunctionValues requiredposition: 1
displayName System.String = null optionalposition: 2
numberFormatId System.Nullable{System.UInt32} = null optionalposition: 3
numberFormat System.String = null optionalposition: 4
showDataAs System.Nullable{DocumentFormat.OpenXml.Spreadsheet.ShowDataAsValues} = null optionalposition: 5
baseField System.Nullable{System.Int32} = null optionalposition: 6
baseItem System.Nullable{System.UInt32} = null optionalposition: 7