[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

GC.Spread.Sheets.PivotTableManager

Class: PivotTableManager

Spread.Sheets.PivotTableManager

Table of contents

Constructors

Methods

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 | any[][] 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? PivotTableTheme Indicates the pivot table theme style.
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.

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