Class PivotTable

Namespace

SheetStream

Assembly

SheetStream.dll

A pivot table summarising a range (or an Excel table) written earlier in the same workbook. Create it with AddPivotTable(string, string, string, int, int, IReadOnlyList<string>) or AddPivotTable(string, ExcelTable, int, int), then place source columns on the row, column, filter and data areas by their header text.

public sealed class PivotTable

Inheritance

object

PivotTable

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Examples

var pivot = summary.AddPivotTable("ByCustomer", invoicesTable, row: 3, column: 1);
pivot.AddRowField("Customer");
pivot.AddColumnField("Status");
pivot.AddDataField("Amount", PivotFunction.Sum, numberFormat: "$#,##0.00");
pivot.AddDataField("Invoice #", PivotFunction.Count, "Invoices");

Remarks

SheetStream streams every sheet forward-only, so by the time the pivot is written the source data has already left memory. The file therefore carries the pivot's layout and an empty cache flagged refreshOnLoad: Excel reads the source cells and computes the totals the moment the workbook opens, exactly as if the user had pressed Refresh. Viewers that do not run the pivot engine (some web previews, LibreOffice in certain versions) show an empty pivot until the file has been opened and saved in Excel.

Not supported: calculated fields, grouping, slicers, custom item sorting, reading or editing pivots of an existing workbook, and pivots on template or edited workbooks.

Properties

Column

1-based column of the pivot's top-left cell.

public int Column { get; }

Property Value

int

Compact

Compact form (default): all row fields share one indented column, as Excel creates pivots. Set to false for outline form, where each row field gets its own column.

public bool Compact { get; set; }

Property Value

bool

Headers

The source header texts, in column order; these are the names Add*Field accepts.

public IReadOnlyList<string> Headers { get; }

Property Value

IReadOnlyList<string>

Name

Pivot table name shown in Excel's PivotTable Analyze ribbon. Unique within the workbook.

public string Name { get; }

Property Value

string

Row

1-based row of the pivot's top-left cell (the first filter cell when there are filter fields).

public int Row { get; }

Property Value

int

ShowColumnGrandTotals

Show the Grand Total row at the bottom (the total of each column). Default true.

public bool ShowColumnGrandTotals { get; set; }

Property Value

bool

ShowColumnStripes

Shade alternate columns with the style's banding.

public bool ShowColumnStripes { get; set; }

Property Value

bool

ShowRowGrandTotals

Show the Grand Total column at the right (the total of each row). Default true.

public bool ShowRowGrandTotals { get; set; }

Property Value

bool

ShowRowStripes

Shade alternate rows with the style's banding.

public bool ShowRowStripes { get; set; }

Property Value

bool

SourceSheetName

Name of the sheet holding the source data.

public string SourceSheetName { get; }

Property Value

string

StyleName

Built-in pivot style, e.g. "PivotStyleMedium9" (default), "PivotStyleLight16", "PivotStyleDark2".

public string StyleName { get; set; }

Property Value

string

Methods

AddColumnField(string)

Puts a source column on the column axis.

public PivotTable AddColumnField(string sourceColumnHeader)

Parameters

sourceColumnHeader string

Returns

PivotTable

AddDataField(string, PivotFunction, string?, string?)

Adds a value to the data area. With more than one data field Excel shows them side by side under a "Values" column header.

public PivotTable AddDataField(string sourceColumnHeader, PivotFunction function = PivotFunction.Sum, string? displayName = null, string? numberFormat = null)

Parameters

sourceColumnHeader string

Header text of the source column to aggregate.

function PivotFunction

Aggregation. Default sum.

displayName string

Caption shown in the pivot, default "Sum of Amount" style. Must differ from every source header: Excel reports "PivotTable field name already exists" otherwise, so that is rejected here.

numberFormat string

Excel number format for the values, e.g. "#,##0.00", or null for General.

Returns

PivotTable

AddFilterField(string)

Adds a report filter (page field) above the pivot. Each filter takes one row; the pivot body starts below them with one blank row in between, as Excel lays it out.

public PivotTable AddFilterField(string sourceColumnHeader)

Parameters

sourceColumnHeader string

Returns

PivotTable

AddRowField(string)

Puts a source column on the row axis. Fields nest in the order they are added.

public PivotTable AddRowField(string sourceColumnHeader)

Parameters

sourceColumnHeader string

Header text of the source column, as passed in headers or captured from the table.

Returns

PivotTable