Namespace EasyXLS.PivotTables

Class ExcelPivotTable

System.Object

EasyXLS.PivotTables.ExcelPivotTable

public class ExcelPivotTable
extends System.Object

This class stores pivot table information like name, location, data source, layout & format options, totals & filters, display, printing and data options.

Constructor Summary

Constructors

Constructor	Description
ExcelPivotTable()	Defines an instance of the pivot table.

Method Summary

Modifier and Type	Method	Description
void	addCalculatedField(System.String name, System.String formula)	Add a calculated field to pivot table.
void	addField(ExcelField xlsField)	Internal use only
void	addFieldToColumnLabels(System.String fieldName)	Adds the field to column labels area.
void	addFieldToReportFilter(System.String fieldName)	Adds the field to report filter area.
void	addFieldToRowLabels(System.String fieldName)	Adds the field to row labels area.
void	addFieldToValues(System.String fieldName, System.String customName, int subtotal)	Adds the field to values area.
bool	AllowMultipleFiltersPerField()	Returns true if all values, including those hidden by filtering, are included when the subtotals and the grand total are calculated, false otherwise.
ExcelPivotTable	Clone()	Creates and returns a copy of this object.
int	ColumnLabelsCount()	Returns the number of fields in the column labels area.
int	FieldCount()	Returns the number of fields in the pivot table.
System.String	getColumnHeaderTitle()	Returns the value to be displayed in column header in compact mode.
ExcelField	getColumnLabelsFieldAt(int index)	Returns the field from the specified index in the column labels area.
System.String	getDataHeaderTitle()	Returns the value to be displayed in value area header.
System.String	getEmptyCellValue()	Returns the text that is displayed in a cell instead of a blank cell.
System.String	getErrorValue()	Returns the text that is displayed in a cell instead of the error message.
ExcelField	getField(System.String name)	Returns the field having the specified name.
ExcelField	getFieldAt(int index)	Returns the field from the specified index.
System.String	getGrandTotalTitle()	Returns the value to be displayed for grand totals.
System.String	getLocation()	Returns the range where the pivot table is located.
System.String	getName()	Returns the pivot table name.
int	getPageWrap()	Returns the number of fields to display before taking up another column or row.
ExcelField	getReportFilterFieldAt(int index)	Returns the field from the specified index in the report filter area.
System.String	getRowHeaderTitle()	Returns the value to be displayed in row header in compact mode.
ExcelField	getRowLabelsFieldAt(int index)	Returns the field from the specified index in the row labels area.
int	getRowLabelsIndent()	Returns the indent of the rows in the row labels area when the pivot table report is in compact format.
int	getSortOrder()	Returns the sort order of the fields in pivot table.
System.String	getSourceRange()	Returns the data source of the pivot table.
System.String	getStyle()	Returns the style of the pivot table.
ExcelValueField	getValueField(System.String name)	Returns the field having the specified name in the values area.
ExcelValueField	getValueFieldAt(int index)	Returns the field from the specified index in the values area.
void	groupDateField(System.String fieldName, bool autoStartAt, System.DateTime startAt, bool autoEndAt, System.DateTime endAt, bool groupBySeconds, bool groupByMinutes, bool groupByHours, bool groupByDays, int numberOfDays, bool groupByMonths, bool groupByQuarters, bool groupByYears)	Group a pivot table field of date time format.
void	groupNumericField(System.String fieldName, bool autoStartAt, double startAt, bool autoEndAt, double endAt, double groupBy)	Group a pivot table field of numeric data type.
bool	IsAutofitColumnWidthsOnUpdate()	Returns true if the pivot table columns are adjusted automatically to fit the cell content, false if the current width is kept.
bool	IsClassicLayout()	Returns true if the classic layout is selected, false otherwise.
bool	IsEnabledShowDetails()	Returns true if the drilling down to detail data from the data source, and then displaying the data on a new worksheet is enabled, false otherwise.
bool	IsMergeAndCenterCellsWithLabels()	Returns true if the cells are merged for outer row and column items, false otherwise.
bool	IsPageOverThenDown()	Returns true if first are displayed the fields in the report filter area from left to right, false if first are displayed the fields in the report filter area from top to bottom
bool	IsPreserveCellFormattingOnUpdate()	Returns true if the layout and format is preserved each time an operation is performed on the pivot table, false otherwise.
bool	IsUseCustomListsWhenSorting()	Returns true if the custom lists are used when Excel sorts lists, false otherwise.
bool	PrintDrillButtons()	Returns true if the expand/collapse buttons are visible when the pivot table is printed, false otherwise.
bool	PrintTitles()	Returns true if the row and column field headers and column item labels are replicated on each printed page, false otherwise.
void	Refresh(ExcelDocument xls)	Refreshes the field names accordingly to the data on the source range.
void	removeFieldFromColumnLabels(System.String fieldName)	Removes the field from the column labels area.
void	removeFieldFromReportFilter(System.String fieldName)	Removes the field from the report filter area.
void	removeFieldFromRowLabels(System.String fieldName)	Removes the field from the row labels area.
void	removeValueFieldFromValues(System.String customFieldName)	Removes the field from the values area.
bool	RepeatRowLabels()	Returns true if the row labels are replicated on each printed page, false otherwise.
int	ReportFilterCount()	Returns the number of fields in the report filter area.
int	RowLabelsCount()	Returns the number of fields in the row labels area.
void	setAllowMultipleFiltersPerField(bool allow)	Sets if all values, including those hidden by filtering, are included when the subtotals and the grand total are calculated.
void	setAutofitColumnWidthsOnUpdate(bool autofit)	Sets if the pivot table columns are adjusted automatically to fit the cell content or the current width is kept.
void	setClassicLayout(bool isClassicLayout)	Sets if the classic layout is selected.
void	setColumnHeaderTitle(System.String text)	Sets the value to be displayed in column header in compact mode.
void	setCompactForm()	Sets the compact form for all the fields of the pivot table.
void	setDataHeaderTitle(System.String text)	Sets the value to be displayed in value area header.
void	setEmptyCellValue(bool showEmptyCell, System.String value)	Sets the text that is displayed in a cell instead of a blank cell.
void	setEnableShowDetails(bool enable)	Sets if the drilling down to detail data from the data source, and then displaying the data on a new worksheet is enabled.
void	setErrorValue(bool showError, System.String value)	Sets the text that is displayed in a cell instead of the error message.
void	setGrandTotalTitle(System.String text)	Sets the value to be displayed for grand totals.
void	setLocation(int firstRow, int firstColumn, int lastRow, int lastColumn)	Sets the location of the pivot table and must be a formula, that represents a range or a cell reference.
void	setLocation(System.String range)	Sets the location of the pivot table and must be a formula, that represents a range or a cell reference This method is overridden as setLocation_2 in COM+ version of EasyXLS.
void	setMergeAndCenterCellsWithLabels(bool mergeAndCenterCellsWithLabels)	Sets if the cells are merged for outer row and column items.
void	setName(System.String name)	Sets the pivot table name.
void	setOutlineForm()	Sets the outline form for all the fields of the pivot table.
void	setPageOverThenDown(bool isPageOverThenDown)	Sets if first are displayed the fields in the report filter area from left to right, or if first are displayed the fields in the report filter area from top to bottom
void	setPageWrap(int fields)	Sets the number of fields to display before taking up another column or row.
void	setPreserveCellFormattingOnUpdate(bool preserve)	Sets if the layout and format is preserved each time an operation is performed on the pivot table.
void	setPrintDrillButtons(bool printDrillButtons)	Sets if the expand/collapse buttons are visible when the pivot table is printed.
void	setPrintTitles(bool printTitles)	Sets if the row and column field headers and column item labels are replicated on each printed page.
void	setRepeatRowLabels(bool repeatRowLabels)	Sets if the row labels are replicated on each printed page.
void	setRowHeaderTitle(System.String text)	Sets the value to be displayed in row header in compact mode.
void	setRowLabelsIndent(int chars)	Sets the indent of the rows in the row labels area when the pivot table report is in compact format.
void	setShowColumnHeaders(bool show)	Sets if the column headers in the banding style are displayed.
void	setShowColumnStripes(bool show)	Sets if the column headers in the banding style are displayed.
void	setShowContextualTooltips(bool show)	Sets if the tooltips that show value, row, or column information for a field or data value are visible.
void	setShowDrillButtons(bool show)	Sets if the expand/collapse buttons are visible.
void	setShowGrandTotalsForColumns(bool show)	Sets if the pivot table contains grand totals for columns.
void	setShowGrandTotalsForRows(bool show)	Sets if the pivot table contains grand totals for rows.
void	setShowHeaders(bool showHeaders)	Sets if the captions at the top of the pivot table report and filter drop-down arrows on column and row labels are visible.
void	setShowRowHeaders(bool show)	Sets if the row headers in the banding style are displayed.
void	setShowRowStripes(bool show)	Sets if the row headers in the banding style are displayed.
void	setSortOrder(int sortOrder)	Sets the sort order of the fields in pivot table.
void	setSourceRange(System.String formulaRange, ExcelDocument xls)	Sets the data source for the pivot table.
void	setStyle(System.String style)	Sets the style of the pivot table.
void	setSubtotalFilteredPageItems(bool show)	Sets if the pivot table includes or excludes report-filtered items in subtotal.
void	setTabularForm()	Sets the outline form for all the fields of the pivot table.
void	setUseCustomListsWhenSorting(bool useCustomListsWhenSorting)	Sets if the custom lists are used when Excel sorts lists.
bool	ShowColumnHeaders()	Returns true if the column headers in the banding style are displayed, false otherwise.
bool	ShowColumnStripes()	Returns true if each column is alternated with a lighter and darker color, false otherwise.
bool	ShowContextualTooltips()	Returns true if the tooltips that show value, row, or column information for a field or data value are visible, false otherwise.
bool	ShowDrillButtons()	Returns true if the expand/collapse buttons are visible, false otherwise.
bool	ShowEmptyCells()	Returns true if a specific text is displayed in a cell instead of a blank cell, false otherwise.
bool	ShowError()	Returns true if a specific text is displayed in a cell instead of the error message, false otherwise.
bool	ShowGrandTotalsForColumns()	Returns true if the pivot table contains grand totals for columns, false otherwise.
bool	ShowGrandTotalsForRows()	Returns true if the pivot table contains grand totals for rows, false otherwise.
bool	ShowHeaders()	Returns true if the captions at the top of the pivot table report and filter drop-down arrows on column and row labels are visible, false otherwise.
bool	ShowRowHeaders()	Returns true if the row headers in the banding style are displayed, false otherwise.
bool	ShowRowStripes()	Returns true if each row is alternated with a lighter and darker color, false otherwise.
bool	ShowSubtotalFilteredPageItems()	Returns true if the pivot table includes or excludes report-filtered items in subtotals, false otherwise.
void	ungroupField(System.String fieldName)	Ungroup a pivot table field.
int	ValuesCount()	Returns the number of fields in the values area.

Methods inherited from class System.Object

equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Details

ExcelPivotTable

public ExcelPivotTable()

Defines an instance of the pivot table.

Method Details

setName

public void setName(System.String name)

Sets the pivot table name.

Parameters:

name - the pivot table name

See Also:

• getName()

getName

public System.String getName()

Returns the pivot table name.

Returns:

the pivot table name

See Also:

• setName(System.String)

setLocation

public void setLocation(int firstRow,
 int firstColumn,
 int lastRow,
 int lastColumn)

Sets the location of the pivot table and must be a formula, that represents a range or a cell reference.

Parameters:

firstRow - first row index of the pivot table location

firstColumn - first column index of the pivot table location

lastRow - last row index of the pivot table location

lastColumn - last column index of the pivot table location

See Also:

• setLocation(String)
• getLocation()

setLocation

public void setLocation(System.String range)

Sets the location of the pivot table and must be a formula, that represents a range or a cell reference
This method is overridden as setLocation_2 in COM+ version of EasyXLS.

Parameters:

range - the location of the pivot table

See Also:

• setLocation(int,int,int,int)
• getLocation()

getLocation

public System.String getLocation()

Returns the range where the pivot table is located.

Returns:

the range where the pivot table is located

See Also:

• setLocation(int,int,int,int)
• setLocation(String)

setSourceRange

public void setSourceRange(System.String formulaRange,
 ExcelDocument xls)

Sets the data source for the pivot table. It must be a range including the sheet name (like Sheet1!A1:C10) or a named range.

Parameters:

formulaRange - the data source for the pivot table

xls - the instance of the document

See Also:

• getSourceRange()

getSourceRange

public System.String getSourceRange()

Returns the data source of the pivot table. It is a range including the sheet name (like Sheet1!A1:C10) or a named range.

Returns:

the data source of the pivot table

See Also:

• setSourceRange(System.String, EasyXLS.ExcelDocument)

addField

public void addField(ExcelField xlsField)

Internal use only

Parameters:

xlsField - pivot table field

addCalculatedField

public void addCalculatedField(System.String name,
 System.String formula)

Add a calculated field to pivot table. The formula must start with "=" symbol.

Parameters:

name - the name of the field

formula - the formula that defines the field.

See Also:

• getFieldAt(int)
• getField(System.String)
• FieldCount()

getFieldAt

public ExcelField getFieldAt(int index)

Returns the field from the specified index.

Parameters:

index - the field index

Returns:

the field from the specified index

See Also:

• setSourceRange(System.String, EasyXLS.ExcelDocument)
• getField(System.String)
• FieldCount()

getField

public ExcelField getField(System.String name)

Returns the field having the specified name.

Parameters:

name - the field name

Returns:

the field having the specified name

See Also:

• setSourceRange(System.String, EasyXLS.ExcelDocument)
• getFieldAt(int)
• FieldCount()

FieldCount

public int FieldCount()

Returns the number of fields in the pivot table.

Returns:

the number of fields in the pivot table

See Also:

• setSourceRange(System.String, EasyXLS.ExcelDocument)
• getFieldAt(int)
• getField(System.String)

addFieldToRowLabels

public void addFieldToRowLabels(System.String fieldName)

Adds the field to row labels area.

Parameters:

fieldName - the name of the field that will be added to row labels area. The summarization special field is PivotTable.SUM_VALUES_FIELD.

See Also:

• RowLabelsCount()
• getRowLabelsFieldAt(int)
• removeFieldFromRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

RowLabelsCount

public int RowLabelsCount()

Returns the number of fields in the row labels area.

Returns:

the number of fields in the row labels area

See Also:

• addFieldToRowLabels(System.String)
• getRowLabelsFieldAt(int)
• removeFieldFromRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

removeFieldFromRowLabels

public void removeFieldFromRowLabels(System.String fieldName)

Removes the field from the row labels area.

Parameters:

fieldName - the name of the field that will be removed

See Also:

• addFieldToRowLabels(System.String)
• getRowLabelsFieldAt(int)
• RowLabelsCount()
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

getRowLabelsFieldAt

public ExcelField getRowLabelsFieldAt(int index)

Returns the field from the specified index in the row labels area.

Parameters:

index - the index of the field in the row labels area

Returns:

the field at the specified index in the row labels area

See Also:

• addFieldToRowLabels(System.String)
• removeFieldFromRowLabels(System.String)
• RowLabelsCount()
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

addFieldToColumnLabels

public void addFieldToColumnLabels(System.String fieldName)

Adds the field to column labels area.

Parameters:

fieldName - the name of the field that will be added to column labels area. The summarization special field is PivotTable.SUM_VALUES_FIELD.

See Also:

• ColumnLabelsCount()
• getColumnLabelsFieldAt(int)
• removeFieldFromColumnLabels(System.String)
• addFieldToRowLabels(System.String)
• addFieldToReportFilter(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

ColumnLabelsCount

public int ColumnLabelsCount()

Returns the number of fields in the column labels area.

Returns:

the number of fields in the column labels area

See Also:

• addFieldToColumnLabels(System.String)
• getColumnLabelsFieldAt(int)
• removeFieldFromColumnLabels(System.String)
• addFieldToRowLabels(System.String)
• addFieldToReportFilter(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

removeFieldFromColumnLabels

public void removeFieldFromColumnLabels(System.String fieldName)

Removes the field from the column labels area.

Parameters:

fieldName - the name of the field that will be removed

See Also:

• addFieldToColumnLabels(System.String)
• getColumnLabelsFieldAt(int)
• ColumnLabelsCount()
• addFieldToRowLabels(System.String)
• addFieldToReportFilter(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

getColumnLabelsFieldAt

public ExcelField getColumnLabelsFieldAt(int index)

Returns the field from the specified index in the column labels area.

Parameters:

index - the index of the field in the column labels area

Returns:

the field at the specified index in the column labels area

See Also:

• addFieldToColumnLabels(System.String)
• removeFieldFromColumnLabels(System.String)
• ColumnLabelsCount()
• addFieldToRowLabels(System.String)
• addFieldToReportFilter(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

addFieldToReportFilter

public void addFieldToReportFilter(System.String fieldName)

Adds the field to report filter area.

Parameters:

fieldName - the name of the field that will be added to report filter area.

See Also:

• ReportFilterCount()
• getReportFilterFieldAt(int)
• removeFieldFromReportFilter(System.String)
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

ReportFilterCount

public int ReportFilterCount()

Returns the number of fields in the report filter area.

Returns:

the number of fields in the report filter area

See Also:

• addFieldToReportFilter(System.String)
• getReportFilterFieldAt(int)
• removeFieldFromReportFilter(System.String)
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

removeFieldFromReportFilter

public void removeFieldFromReportFilter(System.String fieldName)

Removes the field from the report filter area.

Parameters:

fieldName - the name of the field that will be removed

See Also:

• addFieldToReportFilter(System.String)
• getReportFilterFieldAt(int)
• ReportFilterCount()
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

getReportFilterFieldAt

public ExcelField getReportFilterFieldAt(int index)

Returns the field from the specified index in the report filter area.

Parameters:

index - the index of the field in the report filter area

Returns:

the field at the specified index in the report filter area

See Also:

• addFieldToReportFilter(System.String)
• removeFieldFromReportFilter(System.String)
• ReportFilterCount()
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToValues(System.String, System.String, int)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

addFieldToValues

public void addFieldToValues(System.String fieldName,
 System.String customName,
 int subtotal)

Adds the field to values area.

Parameters:

fieldName - the name of the field that will be added to values area.

customName - the name of the pivot table value field

subtotal - the subtotal applied on the value field. Possible values are available in PivotTable class.

See Also:

• ValuesCount()
• getValueFieldAt(int)
• removeValueFieldFromValues(System.String)
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

ValuesCount

public int ValuesCount()

Returns the number of fields in the values area.

Returns:

the number of fields in the values area

See Also:

• addFieldToValues(System.String, System.String, int)
• getValueFieldAt(int)
• removeValueFieldFromValues(System.String)
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

removeValueFieldFromValues

public void removeValueFieldFromValues(System.String customFieldName)

Removes the field from the values area.

Parameters:

customFieldName - the name of the field that will be removed

See Also:

• addFieldToValues(System.String, System.String, int)
• getValueFieldAt(int)
• ValuesCount()
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

getValueField

public ExcelValueField getValueField(System.String name)

Returns the field having the specified name in the values area.

Parameters:

name - the name of the field

Returns:

the field having the specified name in the values area

See Also:

• addFieldToValues(System.String, System.String, int)
• removeValueFieldFromValues(System.String)
• ValuesCount()
• getValueFieldAt(int)
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

getValueFieldAt

public ExcelValueField getValueFieldAt(int index)

Returns the field from the specified index in the values area.

Parameters:

index - the index of the field in the values area

Returns:

the field at the specified index in the values area

See Also:

• addFieldToValues(System.String, System.String, int)
• removeValueFieldFromValues(System.String)
• ValuesCount()
• getValueField(String)
• addFieldToRowLabels(System.String)
• addFieldToColumnLabels(System.String)
• addFieldToReportFilter(System.String)
• getFieldAt(int)
• getField(System.String)
• setSourceRange(System.String, EasyXLS.ExcelDocument)

Refresh

public void Refresh(ExcelDocument xls)

Refreshes the field names accordingly to the data on the source range.

Parameters:

xls - the instance of the document

groupDateField

public void groupDateField(System.String fieldName,
 bool autoStartAt,
 System.DateTime startAt,
 bool autoEndAt,
 System.DateTime endAt,
 bool groupBySeconds,
 bool groupByMinutes,
 bool groupByHours,
 bool groupByDays,
 int numberOfDays,
 bool groupByMonths,
 bool groupByQuarters,
 bool groupByYears)

Group a pivot table field of date time format.

Parameters:

fieldName - the pivot table field where the group is applied

autoStartAt - true if the group starting at automatic date, false otherwise

startAt - the date of start. It is applied only if autoStartAt is false

autoEndAt - true if the group ending at automatic date, false otherwise

endAt - the date of end. It is applied only if autoEndAt is false

groupBySeconds - true if the field is group by seconds, false otherwise

groupByMinutes - true if the field is group by minutes, false otherwise

groupByHours - true if the field is group by hours, false otherwise

groupByDays - true if the field is group by days, false otherwise

numberOfDays - the number of days for intervals. It is valid only if groupByDays is true

groupByMonths - true if the field is group by months, false otherwise

groupByQuarters - true if the field is group by quarters, false otherwise

groupByYears - true if the field is group by years, false otherwise

See Also:

• groupNumericField(System.String, bool, double, bool, double, double)
• ungroupField(System.String)

groupNumericField

public void groupNumericField(System.String fieldName,
 bool autoStartAt,
 double startAt,
 bool autoEndAt,
 double endAt,
 double groupBy)

Group a pivot table field of numeric data type.

Parameters:

fieldName - the pivot table field where the group is applied

autoStartAt - true if the group starting at automatic date, false otherwise

startAt - the start value for the group. It is applied only if autoStartAt is false

autoEndAt - true if the group ending at automatic date, false otherwise

endAt - the end value for the group. It is applied only if autoEndAt is false

groupBy - the group interval

See Also:

• groupDateField(System.String, bool, System.DateTime, bool, System.DateTime, bool, bool, bool, bool, int, bool, bool, bool)
• ungroupField(System.String)

ungroupField

public void ungroupField(System.String fieldName)

Ungroup a pivot table field.

Parameters:

fieldName - the pivot table field where the group is removed

See Also:

• groupDateField(System.String, bool, System.DateTime, bool, System.DateTime, bool, bool, bool, bool, int, bool, bool, bool)
• groupNumericField(System.String, bool, double, bool, double, double)

getRowHeaderTitle

public System.String getRowHeaderTitle()

Returns the value to be displayed in row header in compact mode.

Returns:

the value to be displayed in row header in compact mode

See Also:

• setRowHeaderTitle(System.String)
• getColumnHeaderTitle()
• getDataHeaderTitle()
• getGrandTotalTitle()

setRowHeaderTitle

public void setRowHeaderTitle(System.String text)

Sets the value to be displayed in row header in compact mode.

Parameters:

text - the value to be displayed in row header in compact mode

See Also:

• getRowHeaderTitle()
• setColumnHeaderTitle(System.String)
• setDataHeaderTitle(System.String)
• setGrandTotalTitle(System.String)

getColumnHeaderTitle

public System.String getColumnHeaderTitle()

Returns the value to be displayed in column header in compact mode.

Returns:

the value to be displayed in column header in compact mode

See Also:

• setColumnHeaderTitle(System.String)
• getRowHeaderTitle()
• getDataHeaderTitle()
• getGrandTotalTitle()

setColumnHeaderTitle

public void setColumnHeaderTitle(System.String text)

Sets the value to be displayed in column header in compact mode.

Parameters:

text - the value to be displayed in column header in compact mode

See Also:

• getColumnHeaderTitle()
• setRowHeaderTitle(System.String)
• setDataHeaderTitle(System.String)
• setGrandTotalTitle(System.String)

getDataHeaderTitle

public System.String getDataHeaderTitle()

Returns the value to be displayed in value area header. This value is shown when the pivot table has more than two fields in the values area.

Returns:

the value to be displayed in value area header

See Also:

• setDataHeaderTitle(System.String)
• getRowHeaderTitle()
• getColumnHeaderTitle()
• getGrandTotalTitle()

setDataHeaderTitle

public void setDataHeaderTitle(System.String text)

Sets the value to be displayed in value area header. This value is shown when the pivot table has more than two fields are in the values area.

Parameters:

text - the value to be displayed in value area header

See Also:

• getDataHeaderTitle()
• setRowHeaderTitle(System.String)
• setColumnHeaderTitle(System.String)
• setGrandTotalTitle(System.String)

getGrandTotalTitle

public System.String getGrandTotalTitle()

Returns the value to be displayed for grand totals.

Returns:

the value to be displayed for grand totals

See Also:

• setGrandTotalTitle(System.String)
• getRowHeaderTitle()
• getColumnHeaderTitle()
• getDataHeaderTitle()

setGrandTotalTitle

public void setGrandTotalTitle(System.String text)

Sets the value to be displayed for grand totals.

Parameters:

text - the value to be displayed for grand totals

See Also:

• getGrandTotalTitle()
• setRowHeaderTitle(System.String)
• setColumnHeaderTitle(System.String)
• setDataHeaderTitle(System.String)

IsMergeAndCenterCellsWithLabels

public bool IsMergeAndCenterCellsWithLabels()

Returns true if the cells are merged for outer row and column items, false otherwise.

Returns:

true if the cells are merged for outer row and column items, false otherwise

See Also:

• setMergeAndCenterCellsWithLabels(bool)

setMergeAndCenterCellsWithLabels

public void setMergeAndCenterCellsWithLabels(bool mergeAndCenterCellsWithLabels)

Sets if the cells are merged for outer row and column items.

Parameters:

mergeAndCenterCellsWithLabels - true if the cells are merged for outer row and column items, false otherwise

See Also:

• IsMergeAndCenterCellsWithLabels()

getRowLabelsIndent

public int getRowLabelsIndent()

Returns the indent of the rows in the row labels area when the pivot table report is in compact format.

Returns:

the indent of the rows in the row labels area

See Also:

• setRowLabelsIndent(int)

setRowLabelsIndent

public void setRowLabelsIndent(int chars)

Sets the indent of the rows in the row labels area when the pivot table report is in compact format.

Parameters:

chars - the indent of the rows in the row labels area. It can be a value between 0 and 127.

See Also:

• getRowLabelsIndent()

IsPageOverThenDown

public bool IsPageOverThenDown()

Returns true if first are displayed the fields in the report filter area from left to right, false if first are displayed the fields in the report filter area from top to bottom

Returns:

true if first are displayed the fields in the report filter area from left to right, false if first are displayed the fields in the report filter area from top to bottom

See Also:

• setPageOverThenDown(bool)

setPageOverThenDown

public void setPageOverThenDown(bool isPageOverThenDown)

Sets if first are displayed the fields in the report filter area from left to right, or if first are displayed the fields in the report filter area from top to bottom

Parameters:

isPageOverThenDown - true if first are displayed the fields in the report filter area from left to right, false otherwise

See Also:

• IsPageOverThenDown()

getPageWrap

public int getPageWrap()

Returns the number of fields to display before taking up another column or row.

Returns:

the number of fields to display before taking up another column or row

See Also:

• setPageWrap(int)

setPageWrap

public void setPageWrap(int fields)

Sets the number of fields to display before taking up another column or row.

Parameters:

fields - the number of fields to display before taking up another column or row. It can be a value between 0 and 255.

See Also:

• getPageWrap()

ShowError

public bool ShowError()

Returns true if a specific text is displayed in a cell instead of the error message, false otherwise.

Returns:

true if a specific text is displayed in a cell instead of the error message, false otherwise

See Also:

• setErrorValue(bool, System.String)
• getErrorValue()

getErrorValue

public System.String getErrorValue()

Returns the text that is displayed in a cell instead of the error message.

Returns:

the text that is displayed in a cell instead of the error message

See Also:

• setErrorValue(bool, System.String)
• ShowError()

setErrorValue

public void setErrorValue(bool showError,
 System.String value)

Sets the text that is displayed in a cell instead of the error message.

Parameters:

showError - true if the custom text is applied, false otherwise

value - the text that is displayed in a cell instead of the error message

See Also:

• ShowError()
• getErrorValue()

ShowEmptyCells

public bool ShowEmptyCells()

Returns true if a specific text is displayed in a cell instead of a blank cell, false otherwise.

Returns:

true if a specific text is displayed in a cell instead of a blank cell, false otherwise

See Also:

• setEmptyCellValue(bool, System.String)
• getEmptyCellValue()

getEmptyCellValue

public System.String getEmptyCellValue()

Returns the text that is displayed in a cell instead of a blank cell.

Returns:

the text that is displayed in a cell instead of a blank cell

See Also:

• setEmptyCellValue(bool, System.String)
• ShowEmptyCells()

setEmptyCellValue

public void setEmptyCellValue(bool showEmptyCell,
 System.String value)

Sets the text that is displayed in a cell instead of a blank cell.

Parameters:

showEmptyCell - true if the custom text is applied, false otherwise

value - the text that is displayed in a cell instead of a blank cell

See Also:

• ShowEmptyCells()
• getEmptyCellValue()

IsPreserveCellFormattingOnUpdate

public bool IsPreserveCellFormattingOnUpdate()

Returns true if the layout and format is preserved each time an operation is performed on the pivot table, false otherwise.

Returns:

true if the layout and format is preserved each time an operation is performed on the pivot table, false otherwise

See Also:

• setPreserveCellFormattingOnUpdate(bool)

setPreserveCellFormattingOnUpdate

public void setPreserveCellFormattingOnUpdate(bool preserve)

Sets if the layout and format is preserved each time an operation is performed on the pivot table.

Parameters:

preserve - true if the layout and format is preserved each time an operation is performed on the pivot table, false otherwise

See Also:

• IsPreserveCellFormattingOnUpdate()

IsAutofitColumnWidthsOnUpdate

public bool IsAutofitColumnWidthsOnUpdate()

Returns true if the pivot table columns are adjusted automatically to fit the cell content, false if the current width is kept.

Returns:

true if the pivot table columns are adjusted automatically to fit the cell content, false if the current width is kept

See Also:

• setAutofitColumnWidthsOnUpdate(bool)

setAutofitColumnWidthsOnUpdate

public void setAutofitColumnWidthsOnUpdate(bool autofit)

Sets if the pivot table columns are adjusted automatically to fit the cell content or the current width is kept.

Parameters:

autofit - true if the pivot table columns are adjusted automatically to fit the cell content, false if the current width is kept

See Also:

• IsAutofitColumnWidthsOnUpdate()

ShowGrandTotalsForRows

public bool ShowGrandTotalsForRows()

Returns true if the pivot table contains grand totals for rows, false otherwise.

Returns:

true if the pivot table contains grand totals for rows, false otherwise

See Also:

• setShowGrandTotalsForRows(bool)
• setShowGrandTotalsForColumns(bool)

setShowGrandTotalsForRows

public void setShowGrandTotalsForRows(bool show)

Sets if the pivot table contains grand totals for rows.

Parameters:

show - the grand totals for rows for the pivot table

See Also:

• ShowGrandTotalsForRows()
• ShowGrandTotalsForColumns()

ShowGrandTotalsForColumns

public bool ShowGrandTotalsForColumns()

Returns true if the pivot table contains grand totals for columns, false otherwise.

Returns:

true if the pivot table contains grand totals for columns, false otherwise

See Also:

• setShowGrandTotalsForColumns(bool)
• setShowGrandTotalsForRows(bool)

setShowGrandTotalsForColumns

public void setShowGrandTotalsForColumns(bool show)

Sets if the pivot table contains grand totals for columns.

Parameters:

show - the grand totals for columns for the pivot table

See Also:

• ShowGrandTotalsForColumns()
• ShowGrandTotalsForRows()

ShowSubtotalFilteredPageItems

public bool ShowSubtotalFilteredPageItems()

Returns true if the pivot table includes or excludes report-filtered items in subtotals, false otherwise. Available only for Office 97-2003 file format.

Returns:

true if the pivot table includes or excludes report-filtered items in subtotals, false otherwise

See Also:

• setSubtotalFilteredPageItems(bool)

setSubtotalFilteredPageItems

public void setSubtotalFilteredPageItems(bool show)

Sets if the pivot table includes or excludes report-filtered items in subtotal. Available only for Office 97-2003 file format.

Parameters:

show - true if the pivot table includes or excludes report-filtered items in subtotals, false otherwise

See Also:

• ShowSubtotalFilteredPageItems()

AllowMultipleFiltersPerField

public bool AllowMultipleFiltersPerField()

Returns true if all values, including those hidden by filtering, are included when the subtotals and the grand total are calculated, false otherwise.

Returns:

true if all values, including those hidden by filtering, are included when the subtotals and the grand total are calculated, false otherwise

See Also:

• setAllowMultipleFiltersPerField(bool)

setAllowMultipleFiltersPerField

public void setAllowMultipleFiltersPerField(bool allow)

Sets if all values, including those hidden by filtering, are included when the subtotals and the grand total are calculated.

Parameters:

allow - true if all values, including those hidden by filtering, are included when the subtotals and the grand total are calculated, false otherwise

See Also:

• AllowMultipleFiltersPerField()

IsUseCustomListsWhenSorting

public bool IsUseCustomListsWhenSorting()

Returns true if the custom lists are used when Excel sorts lists, false otherwise.

Returns:

true if the custom lists are used when Excel sorts lists, false otherwise

See Also:

• setUseCustomListsWhenSorting(bool)

setUseCustomListsWhenSorting

public void setUseCustomListsWhenSorting(bool useCustomListsWhenSorting)

Sets if the custom lists are used when Excel sorts lists. The performance can be improved when sorting big volume of data by disabling this option.

Parameters:

useCustomListsWhenSorting - true if the custom lists are used when Excel sorts lists, false otherwise

See Also:

• IsUseCustomListsWhenSorting()

ShowDrillButtons

public bool ShowDrillButtons()

Returns true if the expand/collapse buttons are visible, false otherwise.

Returns:

true if the expand/collapse buttons are visible, false otherwise

See Also:

• setShowDrillButtons(bool)

setShowDrillButtons

public void setShowDrillButtons(bool show)

Sets if the expand/collapse buttons are visible.

Parameters:

show - true if the expand/collapse buttons are visible, false otherwise

See Also:

• ShowDrillButtons()

ShowContextualTooltips

public bool ShowContextualTooltips()

Returns true if the tooltips that show value, row, or column information for a field or data value are visible, false otherwise.

Returns:

true if the tooltips that show value, row, or column information for a field or data value are visible, false otherwise

See Also:

• setShowContextualTooltips(bool)

setShowContextualTooltips

public void setShowContextualTooltips(bool show)

Sets if the tooltips that show value, row, or column information for a field or data value are visible.

Parameters:

show - true if the tooltips that show value, row, or column information for a field or data value are visible, false otherwise

See Also:

• ShowContextualTooltips()

ShowHeaders

public bool ShowHeaders()

Returns true if the captions at the top of the pivot table report and filter drop-down arrows on column and row labels are visible, false otherwise.

Returns:

true if the captions at the top of the pivot table report and filter drop-down arrows on column and row labels are visible, false otherwise

See Also:

• setShowHeaders(bool)

setShowHeaders

public void setShowHeaders(bool showHeaders)

Sets if the captions at the top of the pivot table report and filter drop-down arrows on column and row labels are visible.

Parameters:

showHeaders - true if the captions at the top of the pivot table report and filter drop-down arrows on column and row labels are visible, false otherwise

See Also:

• ShowHeaders()

IsClassicLayout

public bool IsClassicLayout()

Returns true if the classic layout is selected, false otherwise.

Returns:

true if the classic layout is selected, false otherwise

See Also:

• setClassicLayout(bool)

setClassicLayout

public void setClassicLayout(bool isClassicLayout)

Sets if the classic layout is selected.

Parameters:

isClassicLayout - true if the classic layout is selected, false otherwise

See Also:

• IsClassicLayout()

getSortOrder

public int getSortOrder()

Returns the sort order of the fields in pivot table. Possible values are available in PivotTable class.

Returns:

the sort order of the fields in pivot table

See Also:

• setSortOrder(int)

setSortOrder

public void setSortOrder(int sortOrder)

Sets the sort order of the fields in pivot table.

Parameters:

sortOrder - the sort order. Possible values are available in PivotTable class.

See Also:

• getSortOrder()

setCompactForm

public void setCompactForm()

Sets the compact form for all the fields of the pivot table.

setOutlineForm

public void setOutlineForm()

Sets the outline form for all the fields of the pivot table.

setTabularForm

public void setTabularForm()

Sets the outline form for all the fields of the pivot table.

PrintDrillButtons

public bool PrintDrillButtons()

Returns true if the expand/collapse buttons are visible when the pivot table is printed, false otherwise.

Returns:

true if the expand/collapse buttons are visible when the pivot table is printed, false otherwise

See Also:

• setPrintDrillButtons(bool)

setPrintDrillButtons

public void setPrintDrillButtons(bool printDrillButtons)

Sets if the expand/collapse buttons are visible when the pivot table is printed.

Parameters:

printDrillButtons - true if the expand/collapse buttons are visible when the pivot table is printed, false otherwise

See Also:

• PrintDrillButtons()

RepeatRowLabels

public bool RepeatRowLabels()

Returns true if the row labels are replicated on each printed page, false otherwise.

Returns:

true if the row labels are replicated on each printed page, false otherwise

See Also:

• setRepeatRowLabels(bool)

setRepeatRowLabels

public void setRepeatRowLabels(bool repeatRowLabels)

Sets if the row labels are replicated on each printed page.

Parameters:

repeatRowLabels - true if the row labels are replicated on each printed page, false otherwise

See Also:

• RepeatRowLabels()

PrintTitles

public bool PrintTitles()

Returns true if the row and column field headers and column item labels are replicated on each printed page, false otherwise.

Returns:

true if the row and column field headers and column item labels are replicated on each printed page, false otherwise

See Also:

• setPrintTitles(bool)

setPrintTitles

public void setPrintTitles(bool printTitles)

Sets if the row and column field headers and column item labels are replicated on each printed page.

Parameters:

printTitles - true if the row and column field headers and column item labels are replicated on each printed page, false otherwise

See Also:

• PrintTitles()

IsEnabledShowDetails

public bool IsEnabledShowDetails()

Returns true if the drilling down to detail data from the data source, and then displaying the data on a new worksheet is enabled, false otherwise.

Returns:

true if the drilling down to detail data from the data source, and then displaying the data on a new worksheet is enabled, false otherwise

See Also:

• setEnableShowDetails(bool)

setEnableShowDetails

public void setEnableShowDetails(bool enable)

Sets if the drilling down to detail data from the data source, and then displaying the data on a new worksheet is enabled.

Parameters:

enable - true if the drilling down to detail data from the data source, and then displaying the data on a new worksheet is enabled, false otherwise

See Also:

• IsEnabledShowDetails()

getStyle

public System.String getStyle()

Returns the style of the pivot table. The look and feel is valid only for Office 2007 or later files.

Returns:

the style of the pivot table. Possible values are available in PivotTable class.

See Also:

• setStyle(System.String)

setStyle

public void setStyle(System.String style)

Sets the style of the pivot table. The look and feel is valid only for Office 2007 or later files.

Parameters:

style - the style of the pivot table. Possible values are available in PivotTable class.

See Also:

• getStyle()

ShowRowHeaders

public bool ShowRowHeaders()

Returns true if the row headers in the banding style are displayed, false otherwise.

Returns:

true if the row headers in the banding style are displayed, false otherwise

See Also:

• setShowRowHeaders(bool)
• setStyle(System.String)
• getStyle()

setShowRowHeaders

public void setShowRowHeaders(bool show)

Sets if the row headers in the banding style are displayed.

Parameters:

show - true if the row headers in the banding style are displayed, false otherwise

See Also:

• ShowRowHeaders()
• setStyle(System.String)
• getStyle()

ShowColumnHeaders

public bool ShowColumnHeaders()

Returns true if the column headers in the banding style are displayed, false otherwise.

Returns:

true if the column headers in the banding style are displayed, false otherwise

See Also:

• setShowColumnHeaders(bool)
• setStyle(System.String)
• getStyle()

setShowColumnHeaders

public void setShowColumnHeaders(bool show)

Sets if the column headers in the banding style are displayed.

Parameters:

show - true if the column headers in the banding style are displayed, false otherwise

See Also:

• ShowColumnHeaders()
• setStyle(System.String)
• getStyle()

ShowRowStripes

public bool ShowRowStripes()

Returns true if each row is alternated with a lighter and darker color, false otherwise.

Returns:

true if each row is alternated with a lighter and darker color, false otherwise

See Also:

• setShowRowStripes(bool)
• setStyle(System.String)
• getStyle()

setShowRowStripes

public void setShowRowStripes(bool show)

Sets if the row headers in the banding style are displayed.

Parameters:

show - true if the row headers in the banding style are displayed, false otherwise

See Also:

• ShowRowStripes()
• setStyle(System.String)
• getStyle()

ShowColumnStripes

public bool ShowColumnStripes()

Returns true if each column is alternated with a lighter and darker color, false otherwise.

Returns:

true if each column is alternated with a lighter and darker color, false otherwise

See Also:

• setShowColumnStripes(bool)
• setStyle(System.String)
• getStyle()

setShowColumnStripes

public void setShowColumnStripes(bool show)

Sets if the column headers in the banding style are displayed.

Parameters:

show - true if the column headers in the banding style are displayed, false otherwise

See Also:

• ShowColumnStripes()
• setStyle(System.String)
• getStyle()

Clone

public ExcelPivotTable Clone()

Creates and returns a copy of this object.

Returns:

a clone of this instance.