[]
        
(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? 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.


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