Class: PivotTableManager

Spread.Sheets.PivotTableManager

Table of contents

Constructors

• constructor

Methods

• add
• all
• customList
• findPivotTable
• get
• getRangePivotAreas
• remove

Constructors

constructor

• new PivotTableManager(sheet)

Represents a pivot table manager which can manage all pivot tables in a sheet.

Parameters

Name	Type	Description
sheet	Worksheet	The worksheet.

Methods

add

▸ add(name, sourceData, row, col, layout?, theme?, options?): PivotTable

description Add a pivot table to current worksheet.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
var sourceSheet = spread.getSheet(0);
var sheet = spread.getSheet(1);
var sourceData = [["Date","Buyer","Type","Amount"],
                  ["01-Jan","Mom","Fuel",74],
                  ["15-Jan","Mom","Food",235],
                  ["17-Jan","Dad","Sports",20],
                  ["21-Jan","Kelly","Books",125]];
sourceSheet.setArray(0, 0, sourceData );
sourceSheet.tables.add('sourceData', 0, 0, 5, 4);
var layout = GC.Spread.Pivot.PivotTableLayoutType.compact;
var theme = GC.Spread.Pivot.PivotTableThemes.medium2;
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme);

Parameters

Name	Type	Description
name	string	Indicates the pivot table name, it should be unique in the whole workbook.
sourceData	string	Indicates the sourceData is using for pivot table. It supports three types: a table name or a table sheet name or the formula which references a range absolutely.
row	number	Indicates the pivot table start row position.
col	number	Indicates the pivot table start col position.
layout?	PivotTableLayoutType	Indicates the pivot table layout.
theme?	string | PivotTableTheme	Indicates the pivot table theme style name.
options?	IPivotTableOption	Indicates the options of pivot table.

Returns

PivotTable

The new pivot table instance.

---

all

▸ all(): PivotTable[]

description Get all pivot table in current worksheet.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
var sourceSheet = spread.getSheet(0);
var sheet = spread.getSheet(1);
var sourceData = [["Date","Buyer","Type","Amount"],
                  ["01-Jan","Mom","Fuel",74],
                  ["15-Jan","Mom","Food",235],
                  ["17-Jan","Dad","Sports",20],
                  ["21-Jan","Kelly","Books",125]];
sourceSheet.setArray(0, 0, sourceData );
sourceSheet.tables.add('sourceData', 0, 0, 5, 4);
var layout = GC.Spread.Pivot.PivotTableLayoutType.compact;
var theme = GC.Spread.Pivot.PivotTableThemes.medium2;
var options = {showRowHeader: true, showColumnHeader: true};
var pivotTable = sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTables = pivotTableManager.all();
console.log(pivotTables);

Returns

PivotTable[]

return all pivot table in current worksheet.

---

customList

▸ customList(customList?): void | string[]

description Get or Set a custom sort list.

Parameters

Name	Type	Description
customList?	string[]	Set a custom sort list.

Returns

void | string[]

return the custom sort list.

---

findPivotTable

▸ findPivotTable(r, c): PivotTable

description Get pivot table by cell position.

Parameters

Name	Type	Description
r	number	Indicates cell row index.
c	number	Indicates cell column index.

Returns

PivotTable

return the pivot table instance.

---

get

▸ get(name): PivotTable

description Get pivot table by name.

Parameters

Name	Type	Description
name	string	Indicates pivot table name.

Returns

PivotTable

return the pivot table instance.

---

getRangePivotAreas

▸ getRangePivotAreas(range): IPivotAreasCollection

description get pivot areas by specified sheet range.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
var sourceSheet = spread.getSheet(0);
var sheet = spread.getSheet(1);
var sourceData = [["Date","Buyer","Type","Amount"],
                  ["01-Jan","Mom","Fuel",74],
                  ["15-Jan","Mom","Food",235],
                  ["17-Jan","Dad","Sports",20],
                  ["21-Jan","Kelly","Books",125]];
sourceSheet.setArray(0, 0, sourceData );
sourceSheet.tables.add('sourceData', 0, 0, 5, 4);
var layout = GC.Spread.Pivot.PivotTableLayoutType.compact;
var theme = GC.Spread.Pivot.PivotTableThemes.medium2;
var myPivotTable = sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme);
myPivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.rowField);
myPivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.columnField)
myPivotTable.add("Amount", "Sum of Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
var pivotAreas = sheet.pivotTables.getRangePivotAreas(new GC.Spread.Sheets.Range(3, 2, 2, 2))[myPivotTable.name()];
var style = new GC.Spread.Sheets.Style();
style.backColor = 'red';
myPivotTable.setStyle(pivotAreas[0], style);

Parameters

Name	Type	Description
range	Range	Indicates the sheet range.

Returns

IPivotAreasCollection

all pivot areas contains in range.

---

remove

▸ remove(name): any

description Remove a pivot table from worksheet.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
var sourceSheet = spread.getSheet(0);
var sheet = spread.getSheet(1);
var sourceData = [["Date","Buyer","Type","Amount"],
                  ["01-Jan","Mom","Fuel",74],
                  ["15-Jan","Mom","Food",235],
                  ["17-Jan","Dad","Sports",20],
                  ["21-Jan","Kelly","Books",125]];
sourceSheet.setArray(0, 0, sourceData );
sourceSheet.tables.add('sourceData', 0, 0, 5, 4);
var layout = GC.Spread.Pivot.PivotTableLayoutType.compact;
var theme = GC.Spread.Pivot.PivotTableThemes.medium2;
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme);
sheet.pivotTables.remove("pivotTable_1");

Parameters

Name	Type	Description
name	string	Indicates the pivot table name.

Returns

any