[]
        
(Showing Draft Content)

GC.Spread.Pivot.PivotTable-1

Class: PivotTable

Spread.Pivot.PivotTable

Table of contents

Constructors

Properties

Methods

Constructors

constructor

new PivotTable(name, sheet?, row?, col?, layout?, style?, options?, layoutModel?)

Represents a PivotTable.

Parameters

Name Type Description
name string Indicates the name of pivot table.
sheet? Worksheet Indicates the owner worksheet.
row? number Indicates the pivot table start row.
col? number Indicates the pivot table start column.
layout? PivotTableLayoutType Indicates the pivot table layout type.
style? PivotTableTheme Indicates the pivot table theme style.
options? IPivotTableOption -
layoutModel? any -

Properties

options

options: IPivotTableOption

Indicates the options of the PivotTable.

property [allowMultipleFiltersPerField] Indicates whether use multiple filter in one field.

property [fillDownLabels] Indicates show repeat label items or not.

property [insertBlankLineAfterEachItem] Indicates whether insert a blank row at end of each item.

property [grandTotalPosition] Indicates whether show grandtotal in row, column or both.

property [subtotalsPosition] Indicates show subtotal top or bottom or not show.

property [displayFieldsInPageFilterArea] Indicates the field display in page area show first over then down or first down then over.

property [reportFilterFieldsPerColumn] Indicates the number of report filer field per column.

property [bandRows] Indicates show band row or not.

property [bandColumns] Indicates show band column or not.

property [showRowHeader] Indicates show row header style or not.

property [showColumnHeader] Indicates show column header style or not.

property [showDrill] Indicates show expand/collapse button or not.

property [showMissing] Indicates whether the missingCaption option is effected.

property [missingCaption] Indicates what value should be shown when the actual value is empty

property [rowLabelIndent] Indicates the indent of the title of each level.

property [printDrill] Print expand/collapse buttons when displayed on PivotTable.

property [itemPrintTitles] Repeat row labels on each PivotTable.

property [fieldPrintTitles] Set Print titles.

property [showFilter] Indicates show filter button or not.

property [showToolTip] Indicates show tooltip or not.

property [mergeItem] Indicates whether merge and center the cells with labels.

property [isShowErrorValue] Indicates whether the errorValueInfo option is effected.

property [errorValueInfo] Indicates what value should be shown when the actual value is a error.

property [rowHeaderCaption] Indicates what value should be shown in compact layout to replace Row Label.

property [colHeaderCaption] Indicates what value should be shown in compact layout to replace Column Label.

property [showHeaders] Indicates show field headers.

property [calcItemAggregation] Indicates whether PivotTable total contains the value of calcItem.

property [enableDataValueEditing] Indicates whether allow edit cell values of data area of pivot table.


views

views: IPivotTableViewManager

Pivot table view manager for the pivot table.

Methods

add

add(sourceName, displayName, area, subtotal?, index?): void

description Add a field to pivot table.

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 subtotal = GC.Pivot.SubtotalType.count;
pivotTable.add("Buyer", "Buyer", 1, subtotal, 0) //add a field to pivot table, and field name is displayName, field in row area

Parameters

Name Type Description
sourceName string Indicates the source name of the field.
displayName string Indicates the display name of the field.
area number Indicates which area will be added to.
subtotal? SubtotalType -
index? number -

Returns

void


addCalcField

addCalcField(fieldName, formula): void

description Add a calculated field, the calculated field can only add into value area of pivot table.

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 pivotTable = sheet.pivotTables.add("myPivotTable", 'sourceData', 1, 1, layout, theme);
pivotTable.addCalcField("PercentOfEach", "=Amount/454");

Parameters

Name Type Description
fieldName string Indicates the calculated field name.
formula string Indicates the calculated formula.

Returns

void


addCalcItem

addCalcItem(sourceName, calcItemName, formula): void

description add a calcItem

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.addCalcItem("Buyer", "formula1", "=Buyer[Mom]+Buyer[Dad]");

Parameters

Name Type Description
sourceName string The name of sourceField name
calcItemName string The name of sourceField calcItem name
formula string The formula of sourceField calcItem formula

Returns

void


addConditionalRule

addConditionalRule(pivotArea, conditionalRule): void

Sets the rules using the pivot areas.

example

//This example uses the getRule method.
var pivotTable = activeSheet.pivotTables.all()[0];
var rule = new GC.Spread.Sheets.ConditionalFormatting.ScaleRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.threeScaleRule);
rule.midColor("#12ff34");
rule.midType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.midValue(50000);
rule.maxColor("#EE3344");
rule.maxType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.maxValue(400000);
rule.minColor("#AAff34");
rule.minType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.minValue(5000);
var AmericaPivotArea = {
       dataOnly: true
       references: [{
           fieldName: "Country",
           items: ["America"]
       }]
   }
var BritainPivotArea = {
       dataOnly: true
       references: [{
           fieldName: "Country",
           items: ["Britain"]
       }]
   }
pivotTable.addConditionalRule([AmericaPivotArea, BritainPivotArea], rule);

Parameters

Name Type Description
pivotArea IPivotArea[] The pivotArea in pivot table.
conditionalRule ConditionRuleBase The rules set to the pivot area.

Returns

void


autoFitColumn

autoFitColumn(): void

description Set the minimum visual column width for each Field item.

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 pivotTable = sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme);
pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount", "Sum of Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.autoFitColumn();

Returns

void


clearOverwriteList

clearOverwriteList(): void

clear overwrite info list.

Returns

void


collapse

collapse(fieldName, item, isCollapse?): boolean | void

description Get or set collapse info for a field of pivot table.

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);
pivotTable.add("Buyer", "Buyer", 1, subtotal, 0);
pivotTable.add("Type", "Type", 1, subtotal, 1);
var collapseValue = pivotTable.collapse("Buyer","Mom");
pivotTable.collapse("Buyer","Mom", !collapseValue);

Parameters

Name Type Description
fieldName string Indicates the target field name.
item string Indicates the collapse item name.
isCollapse? boolean -

Returns

boolean | void

whether is collapsed.


connectSlicer

connectSlicer(name): void

Connect slicer with the PivotTable

Parameters

Name Type Description
name string name of slicer

Returns

void


dataPosition

dataPosition(positionType?, positionIndex?): void | IDataPosition

description Get or set the values position.

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);
pivotTable.dataPosition(1,0) //move values to row area and index is 0
pivotTable.dataPosition();//{positionType:1,positionIndex:0}

Parameters

Name Type
positionType? DataPosition
positionIndex? number

Returns

void | IDataPosition

return the data position info of pivot table or void


deserialize

deserialize(serializeInfo): void

description restore serialized pivot table data to a existed pivot table

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 pivotTable = sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
var serialization = pivotTable.serialize();
pivotTable.remove('Type');
pivotTable.deserialize(serialization);

Parameters

Name Type Description
serializeInfo ISerializeInfo serialized pivot table data.

Returns

void


disconnectSlicer

disconnectSlicer(name): void

Disconnect slicer with PivotTable

Parameters

Name Type
name string

Returns

void


getAllSlicers

getAllSlicers(): PivotTableItemSlicer[]

Get All Slicers connect with the PivotTable

Returns

PivotTableItemSlicer[]

slicers connect with the PivotTable


getCalcFields

getCalcFields(): ICalcFieldInfo[]

description get all calculated fields's info.

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 pivotTable = sheet.pivotTables.add("pivotTable1", 'sourceData', 1, 1, layout, theme);
pivotTable.addCalcField("PercentOfEach", "=Amount/454");
pivotTable.getCalcFields();

Returns

ICalcFieldInfo[]

return all calculated fields's info.


getCalcItems

getCalcItems(sourceName?): ICalcItemInfo[]

description get calcItems information

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.addCalcItem("Buyer", "formula1", "=Buyer[Mom]+Buyer[Dad]");
pivotTable.getCalcItems("Buyer");

Parameters

Name Type Description
sourceName? string The name of sourceField name

Returns

ICalcItemInfo[]


getConditionalRules

getConditionalRules(pivotArea): ConditionRuleBase[]

Gets the rules using the pivot area.

example

//This example uses the getRule method.
var pivotTable = activeSheet.pivotTables.all()[0];
var rule = new GC.Spread.Sheets.ConditionalFormatting.ScaleRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.threeScaleRule);
rule.midColor("#12ff34");
rule.midType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.midValue(50000);
rule.maxColor("#EE3344");
rule.maxType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.maxValue(400000);
rule.minColor("#AAff34");
rule.minType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.minValue(5000);
var AmericaPivotArea = {
       dataOnly: true
       references: [{
           fieldName: "Country",
           items: ["America"]
       }]
   }
var BritainPivotArea = {
       dataOnly: true
       references: [{
           fieldName: "Country",
           items: ["Britain"]
       }]
   }
pivotTable.addConditionalRule([AmericaPivotArea, BritainPivotArea], rule);
var ruleTest = pivotTable.getConditionalRules(BritainPivotArea);
alert(ruleTest[0].midValue());

Parameters

Name Type Description
pivotArea IPivotArea The pivotArea in pivot table.

Returns

ConditionRuleBase[]

The rules from the pivot area.


getField

getField(fieldName): IFieldInfo

description Get field information from pivot table by field name

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);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.getField("Type");

Parameters

Name Type Description
fieldName string the field's name

Returns

IFieldInfo

return a field information


getFieldsByArea

getFieldsByArea(area?): IFieldInfo[]

description Get all field information from pivot table or one of pivot table area

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);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.getFieldsByArea(GC.Spread.PivotTableFieldType.columnField);

Parameters

Name Type
area? PivotTableFieldType

Returns

IFieldInfo[]

return all field information in the pivot table area


getItemsByField

getItemsByField(fieldName): any

description get all items from pivot table by field name

Parameters

Name Type Description
fieldName string the field name of pivot table field

Returns

any

return all items of pivot table field


getNodeInfo

getNodeInfo(row, col): IPivotNodeInfo

compose overwrite info from sheet row and column.

Parameters

Name Type Description
row number sheet row of the cell.
col number sheet column of the cell.

Returns

IPivotNodeInfo

The node info want to get.


getNodeValue

getNodeValue(nodeInfo): number

get value by node info.

Parameters

Name Type Description
nodeInfo IPivotNodeInfo The node info want to get.

Returns

number

value of node info.


getOverwriteList

getOverwriteList(): IPivotOverwriteNodeInfo[]

get all overwrite info of pivot cache.

Returns

IPivotOverwriteNodeInfo[]

overwrite info list.


getPivotAreaRanges

getPivotAreaRanges(pivotArea): Range[]

description get the sheet ranges that corresponding to the specific pivotArea.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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("pivotTable1", 'sourceData', 1, 1, layout, theme, options);
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount", "Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
var pivotArea = {
    dataOnly: false,
    references: [
        {
            fieldName: "Buyer",
            items: ["Mom", "Dad"]
        }
    ]
};
let ranges = pivotTable.getPivotAreaRanges(pivotArea);

Parameters

Name Type Description
pivotArea IPivotArea the specific pivotArea

Returns

Range[]

ranges the sheet ranges that corresponding to the specific pivotArea.


getPivotDetails

getPivotDetails(pivotItemInfo): void | any[][]

description get PivotTable Details

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.getPivotDetails([{fieldName:"Buyer", fieldItem:"Kelly"}]);

Parameters

Name Type Description
pivotItemInfo IPivotItemInfo[] The pivot details information list

Returns

void | any[][]


getRange

getRange(): IPivotTableRange

description Get the range of PivotTable, consist of page & content. They are readonly, change the range will not take any effect.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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 pivotTable = sheet.pivotTables.add("pivotTable1", 'sourceData', 1, 1, layout, theme);
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.filterField);
pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount", "Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
var ranges = pivotTable.getRange();
console.log(ranges.page, ranges.content);

Returns

IPivotTableRange

return current pivot table range.


getSource

getSource(): string

description get PivotTable data refer

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.getSource();

Returns

string


getSourceFields

getSourceFields(): ISourceFieldInfo[]

description get PivotTable source field information

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.addCalcField("calcField", "=Amount*2");
pivotTable.getSourceFields();

Returns

ISourceFieldInfo[]


getStyle

getStyle(pivotArea): Style

description Get style by the specific pivotArea.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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("pivotTable1", 'sourceData', 1, 1, layout, theme, options);
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount", "Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
var pivotArea = {
    dataOnly: false,
    references: [
        {
            fieldName: "Buyer",
            items: ["Mom", "Dad"]
        }
    ]
};
var style = new GC.Spread.Sheets.Style();
redBack.backColor = '#ff0000';
pivotTable.setStyle(pivotArea, style);
pivotTable.getStyle(pivotArea);

Parameters

Name Type Description
pivotArea IPivotArea the specific pivotArea

Returns

Style

GC.Spread.Sheets.Style


group

group(groupInfo): void

description Group the items of field

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],
                  ["19-Jan","David","Books",120],
                  ["20-Jan","Dad","Food",160],
                  ["21-Jan","David","Sports",15],
                  ["21-Jan","Kelly","Books",125]];
sourceSheet.setArray(0, 0, sourceData );
sourceSheet.tables.add('sourceData', 0, 0, 8, 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 groupInfo = {
    originFieldName: "Buyer",
    textGroup: {
        fieldName: "FamilyMembers",
        groupItems: {
             "parent": ["Mom", "Dad"],
             "children": ["David", "Kelly"]
        }
    }
};
pivotTable.group(groupInfo);
pivotTable.add("FamilyMembers", "FamilyMembers", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Amount", "Sum of Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
sheet.resumePaint();

Parameters

Name Type Description
groupInfo IDateGroupsInfo | INumberGroupInfo | ITextGroupInfo Indicates the grouped info.

Returns

void


isConnectedSlicer

isConnectedSlicer(name): boolean

Whether the slicer is connected with the PivotTable

Parameters

Name Type Description
name string name of slicer

Returns

boolean

Whether the slicer is connected with the PivotTable


labelFilter

labelFilter(fieldName, filterInfo?): void | IPivotConditionFilterInfo | IPivotTextFilterInfo

Get or set label filter info for a field.

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);
pivotTable.suspendLayout();
pivotTable.options.showRowHeader = true;
pivotTable.options.showColumnHeader = true;
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.rowField);
var condition = { conType: GC.Pivot.PivotConditionType.caption, operator: GC.Pivot.PivotCaptionFilterOperator.contains, val: ["Mom"] };
var filterInfo = { condition };
pivotTable.labelFilter("Buyer", filterInfo);
pivotTable.add("Amount", "Sum of Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.resumeLayout();

Parameters

Name Type Description
fieldName string Indicates the target field name of pivot table.
filterInfo? null | IPivotConditionFilterInfo | IPivotTextFilterInfo -

Returns

void | IPivotConditionFilterInfo | IPivotTextFilterInfo

return pivot table labelFilter information


layoutType

layoutType(type?): PivotTableLayoutType

description Get or set the layoutType of pivot table

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 pivotTableLayoutType = GC.Spread.Pivot.PivotTableLayoutType.compact;
pivotTable.layoutType(pivotTableLayoutType);
pivotTable.layoutType();//GC.Spread.Pivot.PivotTableLayoutType.compact

Parameters

Name Type
type? PivotTableLayoutType

Returns

PivotTableLayoutType

If no parameters are passed in, get the current layout type.


name

name(name?): string | void

description Get or set pivot table name.

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);
pivotTable.name("pivotTable_2")
console.log(pivotTable.name()); //pivotTable_2

Parameters

Name Type Description
name? string Indicates the pivot table name.

Returns

string | void


position

position(row?, col?, sheetName?): void | IPivotTablePosition

description Get or set pivot table start position, the position of pivot table will auto change when there are enough cells to put the pivot table

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"),{sheetCount:3});
var sourceSheet = spread.getSheet(0);
var sheet = spread.getSheet(1);
var toSheet = spread.getSheet(2);
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);
pivotTable.position(10,10,toSheet.name());
pivotTable.position(); //{row:10,col:10, sheetName: "Sheet3"}

Parameters

Name Type
row? number
col? number
sheetName? string

Returns

void | IPivotTablePosition


refresh

refresh(): void

description Refresh fields Layout, re calc all field data in sheet.

Returns

void


remove

remove(fieldName): void

description Delete a field by name.

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);
pivotTable.add("Buyer", "Buyer", 1, GC.Pivot.SubtotalType.count, 0);
pivotTable.remove("Buyer");

Parameters

Name Type Description
fieldName string Indicates the fieldName which will be removed.

Returns

void


removeCalcField

removeCalcField(fieldName): void

description remove a calculated field

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 pivotTable = sheet.pivotTables.add("pivotTable1", 'sourceData', 1, 1, layout, theme);
pivotTable.addCalcField("Amount", "PercentOfEach", "=Amount/454");
var calcFieldsInfo = pivotTable.getCalcFields();
pivotTable.removeCalcField(calcFieldsInfo[i].fieldName);

Parameters

Name Type Description
fieldName string Indicates the calculated field name.

Returns

void


removeCalcItem

removeCalcItem(sourceName, calcItemName): void

description remove a calcItem

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.addCalcItem("Buyer", "formula1", "=Buyer[Mom]+Buyer[Dad]");
pivotTable.removeCalcItem("Buyer", "formula1");

Parameters

Name Type Description
sourceName string The name of sourceField name
calcItemName string The name of sourceField calcItem name

Returns

void


removeConditionalRule

removeConditionalRule(conditionalRule): void

Remove the rule of the pivot table.

example

//This example uses the getRule method.
var pivotTable = activeSheet.pivotTables.all()[0];
var rule = new GC.Spread.Sheets.ConditionalFormatting.ScaleRule();
rule.ruleType(GC.Spread.Sheets.ConditionalFormatting.RuleType.threeScaleRule);
rule.midColor("#12ff34");
rule.midType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.midValue(50000);
rule.maxColor("#EE3344");
rule.maxType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.maxValue(400000);
rule.minColor("#AAff34");
rule.minType(GC.Spread.Sheets.ConditionalFormatting.ScaleValueType.number);
rule.minValue(5000);
var AmericaPivotArea = {
       dataOnly: true
       references: [{
           fieldName: "Country",
           items: ["America"]
       }]
   }
var BritainPivotArea = {
       dataOnly: true
       references: [{
           fieldName: "Country",
           items: ["Britain"]
       }]
   }
pivotTable.addConditionalRule([AmericaPivotArea, BritainPivotArea], rule);
pivotTable.removeConditionalRule(rule);

Parameters

Name Type Description
conditionalRule ConditionRuleBase The rules set to the pivot table.

Returns

void


resumeLayout

resumeLayout(): void

description Stop hold off update field, end of suspendLayout effect, it must be in pairs with suspendLayout.

Returns

void


serialize

serialize(): ISerializeInfo

description get serialized pivot table data

Returns

ISerializeInfo

serialized pivot table data


setNodeValue

setNodeValue(nodeInfo, value?): void

Set overwrite value to pivot cache.

Parameters

Name Type Description
nodeInfo IPivotNodeInfo The node info to be set.
value? number -

Returns

void


setStyle

setStyle(pivotArea, style): void

description Set or remove style to the specific pivotArea.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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("pivotTable1", 'sourceData', 1, 1, layout, theme, options);
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Date", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount", "Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
var pivotArea = {
    dataOnly: false,
    references: [
        {
            fieldName: "Buyer",
            items: ["Mom", "Dad"]
        }
    ]
};
var style = new GC.Spread.Sheets.Style();
redBack.backColor = '#ff0000';
pivotTable.setStyle(pivotArea, style);

Parameters

Name Type Description
pivotArea IPivotArea the specific pivotArea
style Style the style set to the specific pivotArea, null or undefined to remove style of the specific pivotArea.

Returns

void


showDataAs

showDataAs(fieldName, showDataAsInfo?): void | IPivotShowDataAsInfo

description get or set a value field's showDataAs info.

Parameters

Name Type Description
fieldName string the value field name the show value as will apply.
showDataAsInfo? IPivotShowDataAsInfo -

Returns

void | IPivotShowDataAsInfo


showNoData

showNoData(cacheFieldName, isShow): boolean

description set or get field 'show items with no data' information

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.add("Date","Date",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.showNoData("Buyer", true);

Parameters

Name Type Description
cacheFieldName string -
isShow boolean The flag indicates whether items without data need to be displayed

Returns

boolean


sort

sort(fieldName, sortInfo): void | IPivotViewSortInfo

description get or set sort for a field of pivot table.

example

var spread = new GC.Spread.Sheets.workbook(document.getElementById("ss"));
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]];
var layout = GC.Spread.Pivot.PivotTableLayoutType.compact;
var theme = GC.Spread.Pivot.PivotTableThemes.medium2;
var options = {showRowHeader: true, showColumnHeader: true};
var pivotTable = spread.getSheet(0).pivotTables.add("pivotTable_1",sourceData,1,1,layout,theme,option);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Type", { sortType: GC.Pivot.SortType.asc, sortValueFieldName: "Sum of Amount"});
pivotTable.sort("Buyer", { sortType: GC.Pivot.SortType.asc });
pivotTable.sort("Buyer", { customSortCallback: function(fieldItemNameArray) {
    return fieldItemNameArray.sort((a, b) => a.length - b.length);
    }
});

Parameters

Name Type Description
fieldName string Indicates the target field name.
sortInfo IPivotViewSortInfo Indicates the sort info.

Returns

void | IPivotViewSortInfo


subtotalPosition

subtotalPosition(fieldName, position): void | SubtotalsPosition

description set or get field show subtotal position information.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.add("Date","Date",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.subtotalPosition("Buyer", GC.Spread.Pivot.SubtotalsPosition.top);

Parameters

Name Type Description
fieldName string Indicates the field name.
position SubtotalsPosition The indicates set whether subtotal position, only top and bottom is supported.

Returns

void | SubtotalsPosition


subtotalType

subtotalType(fieldName, type?): void | SubtotalType

Get or set SubtotalType for a field.

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);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
var subtotalType = GC.Pivot.SubtotalType.average;
pivotTable.subtotalType("Buyer", subtotalType) //set a subtotalType for a Field of name is "fieldName"

Parameters

Name Type Description
fieldName string Indicates the target field name of pivot table.
type? SubtotalType Indicates the subtotal type to set.

Returns

void | SubtotalType


suspendLayout

suspendLayout(): void

description Stop update field util resumeFieldsLayout, it must be used in pairs with resumeFieldsLayout.

Returns

void


theme

theme(theme?): void | PivotTableTheme

Get or set pivot table theme

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);
pivotTable.theme("light3");

Parameters

Name Type
theme? string | PivotTableTheme

Returns

void | PivotTableTheme

If no parameters are passed in, get the current theme


ungroup

ungroup(fieldName): void

description Ungroup the field by field name.

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 3 });
var sheet = spread.getActiveSheet();
sheet.suspendPaint();
var sourceDataArray = [["Date", "Buyer", "Type", "Amount"],
["01-Jan", "Mom", "Fuel", 74],
["15-Jan", "Mom", "Food", 235],
["17-Jan", "Dad", "Sports", 20],
["19-Jan", "David", "Books", 120],
["20-Jan", "Dad", "Food", 160],
["21-Jan", "David", "Sports", 15],
["21-Jan", "Kelly", "Books", 125]];
sheet.setArray(3, 0, sourceDataArray);
sheet.tables.add('Table1', 3, 0, 8, 4);
sheet.setColumnWidth(6, 130);
sheet.setColumnWidth(8, 100);
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("PivotTable1", 'Table1', 3, 6, layout, theme, option);
var groupInfo = {
    originFieldName: "Buyer",
    textGroup: {
        "parent": ["Mom", "Dad"],
        "children": ["David", "Kelly"]
    }
};
pivotTable.group("FamilyMembers", groupInfo);
pivotTable.add("FamilyMembers", "FamilyMembers", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Amount", "Sum of Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
sheet.resumePaint();

pivotTable.ungroup("FamilyMembers");

Parameters

Name Type Description
fieldName string Indicates the ungroup field name.

Returns

void


updateCalcItem

updateCalcItem(sourceName, calcItemName, formula, priority): void

description update calcItem Information

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.addCalcItem("Buyer", "formula1", "=Buyer[Mom]+Buyer[Dad]");
pivotTable.updateCalcItem("Buyer", "formula1", "=Buyer[Mom]+Buyer[Kelly]", 1);

Parameters

Name Type Description
sourceName string The name of sourceField name
calcItemName string The name of sourceField calcItem name
formula string The new formula for this calcItem
priority number The new priority for this calcItem

Returns

void


updateField

updateField(name, area, index?): void

description Update the field area and index

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);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pivotTable.updateField("Buyer", GC.Spread.Pivot.PivotTableFieldType.columnField, 0) //The Field of name is "Buyer" move to column area and Field index is 2

Parameters

Name Type Description
name string Indicates the field name.
area PivotTableFieldType Indicates which area the field to be put.
index? number -

Returns

void


updateFieldName

updateFieldName(oldName, newName): void

description Update the exist field Name.

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);
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.updateFieldName("Buyer", "newBuyer")

Parameters

Name Type Description
oldName string Indicates the old display name of field in pivot table.
newName string Indicates the new display name of field in pivot table.

Returns

void


updateSource

updateSource(): void

description refresh pivotTable data source

example

var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
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};
sheet.pivotTables.add("pivotTable_1", 'sourceData', 1, 1, layout, theme, options);
var pivotTable = sheet.pivotTables.get("pivotTable_1");
pivotTable.add("Buyer","Buyer",GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Type","Type",GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Amount","Sum of Amount",GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
sourceSheet.setValue(1,3,1000);
pivotTable.updateSource();

Returns

void


valueFilter

valueFilter(fieldName, filterInfo?): void | IPivotConditionFilterInfo

Get or set value filter info for a field.

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);
pivotTable.suspendLayout();
pivotTable.options.showRowHeader = true;
pivotTable.options.showColumnHeader = true;
pivotTable.add("Buyer", "Buyer", GC.Spread.Pivot.PivotTableFieldType.columnField);
pivotTable.add("Type", "Type", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("Amount", "Sum of Amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
let condition = { conType: GC.Pivot.PivotConditionType.value, operator: GC.Pivot.PivotValueFilterOperator.between, val: [0, 100] };
let filterInfo = { condition: condition, conditionByName: "Sum of Amount" };
pivotTable.valueFilter("Buyer", filterInfo);
pivotTable.resumeLayout();

Parameters

Name Type Description
fieldName string Indicates the target field name of pivot table.
filterInfo? null | IPivotConditionFilterInfo -

Returns

void | IPivotConditionFilterInfo

return the pivot table value information.