[]
• new PivotTable(name
, sheet?
, row?
, col?
, layout?
, style?
, options?
, layoutModel?
)
Represents a PivotTable.
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? |
string | PivotTableTheme |
Indicates the pivot table theme style or style name. |
options? |
IPivotTableOption |
- |
layoutModel? |
any |
- |
• 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: IPivotTableViewManager
Pivot table view manager for the pivot table.
▸ 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
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 |
- |
void
▸ 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");
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the calculated field name. |
formula |
string |
Indicates the calculated formula. |
void
▸ 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]");
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 |
void
▸ 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);
Name | Type | Description |
---|---|---|
pivotArea |
IPivotArea [] |
The pivotArea in pivot table. |
conditionalRule |
ConditionRuleBase |
The rules set to the pivot area. |
void
▸ 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();
void
▸ clearOverwriteList(): void
clear overwrite info list.
void
▸ 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);
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the target field name. |
item |
string |
Indicates the collapse item name. |
isCollapse? |
boolean |
- |
boolean
| void
whether is collapsed.
▸ connectSlicer(name
): void
Connect slicer with the PivotTable
Name | Type | Description |
---|---|---|
name |
string |
name of slicer |
void
▸ 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}
Name | Type |
---|---|
positionType? |
DataPosition |
positionIndex? |
number |
void
| IDataPosition
return the data position info of pivot table or void
▸ 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);
Name | Type | Description |
---|---|---|
serializeInfo |
ISerializeInfo |
serialized pivot table data. |
void
▸ disconnectSlicer(name
): void
Disconnect slicer with PivotTable
Name | Type |
---|---|
name |
string |
void
▸ getAllSlicers(): PivotTableItemSlicer
[]
Get All Slicers connect with the PivotTable
slicers connect with the PivotTable
▸ 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();
return all calculated fields's info.
▸ 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");
Name | Type | Description |
---|---|---|
sourceName? |
string |
The name of sourceField name |
▸ 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());
Name | Type | Description |
---|---|---|
pivotArea |
IPivotArea |
The pivotArea in pivot table. |
The rules from the pivot area.
▸ 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");
Name | Type | Description |
---|---|---|
fieldName |
string |
the field's name |
return a field information
▸ 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);
Name | Type |
---|---|
area? |
PivotTableFieldType |
return all field information in the pivot table area
▸ getItemsByField(fieldName
): any
description
get all items from pivot table by field name
Name | Type | Description |
---|---|---|
fieldName |
string |
the field name of pivot table field |
any
return all items of pivot table field
▸ getNodeInfo(row
, col
): IPivotNodeInfo
compose overwrite info from sheet row and column.
Name | Type | Description |
---|---|---|
row |
number |
sheet row of the cell. |
col |
number |
sheet column of the cell. |
The node info want to get.
▸ getNodeValue(nodeInfo
): number
get value by node info.
Name | Type | Description |
---|---|---|
nodeInfo |
IPivotNodeInfo |
The node info want to get. |
number
value of node info.
▸ getOverwriteList(): IPivotOverwriteNodeInfo
[]
get all overwrite info of pivot cache.
overwrite info list.
▸ 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);
Name | Type | Description |
---|---|---|
pivotArea |
IPivotArea |
the specific pivotArea |
Range
[]
ranges the sheet ranges that corresponding to the specific pivotArea.
▸ 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"}]);
Name | Type | Description |
---|---|---|
pivotItemInfo |
IPivotItemInfo [] |
The pivot details information list |
void
| any
[][]
▸ 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);
return current pivot table range.
▸ 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();
string
▸ 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();
▸ 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);
Name | Type | Description |
---|---|---|
pivotArea |
IPivotArea |
the specific pivotArea |
GC.Spread.Sheets.Style
▸ getThemeName(): undefined
| string
Gets or sets a style name for the pivot table.
undefined
| string
returns the pivot table style name.
▸ 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();
Name | Type | Description |
---|---|---|
groupInfo |
IDateGroupsInfo | INumberGroupInfo | ITextGroupInfo |
Indicates the grouped info. |
void
▸ isConnectedSlicer(name
): boolean
Whether the slicer is connected with the PivotTable
Name | Type | Description |
---|---|---|
name |
string |
name of slicer |
boolean
Whether the slicer is connected with the PivotTable
▸ 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();
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the target field name of pivot table. |
filterInfo? |
null | IPivotConditionFilterInfo | IPivotTextFilterInfo |
- |
void
| IPivotConditionFilterInfo
| IPivotTextFilterInfo
return pivot table labelFilter information
▸ 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
Name | Type |
---|---|
type? |
PivotTableLayoutType |
If no parameters are passed in, get the current layout type.
▸ 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
Name | Type | Description |
---|---|---|
name? |
string |
Indicates the pivot table name. |
string
| void
▸ 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"}
Name | Type |
---|---|
row? |
number |
col? |
number |
sheetName? |
string |
void
| IPivotTablePosition
▸ refresh(): void
description
Refresh fields Layout, re calc all field data in sheet.
void
▸ 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");
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the fieldName which will be removed. |
void
▸ 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);
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the calculated field name. |
void
▸ 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");
Name | Type | Description |
---|---|---|
sourceName |
string |
The name of sourceField name |
calcItemName |
string |
The name of sourceField calcItem name |
void
▸ 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);
Name | Type | Description |
---|---|---|
conditionalRule |
ConditionRuleBase |
The rules set to the pivot table. |
void
▸ resumeLayout(): void
description
Stop hold off update field, end of suspendLayout effect, it must be in pairs with suspendLayout.
void
▸ serialize(): ISerializeInfo
description
get serialized pivot table data
serialized pivot table data
▸ setNodeValue(nodeInfo
, value?
): void
Set overwrite value to pivot cache.
Name | Type | Description |
---|---|---|
nodeInfo |
IPivotNodeInfo |
The node info to be set. |
value? |
number |
- |
void
▸ 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);
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. |
void
▸ showDataAs(fieldName
, showDataAsInfo?
): void
| IPivotShowDataAsInfo
description
get or set a value field's showDataAs info.
Name | Type | Description |
---|---|---|
fieldName |
string |
the value field name the show value as will apply. |
showDataAsInfo? |
IPivotShowDataAsInfo |
- |
void
| IPivotShowDataAsInfo
▸ 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);
Name | Type | Description |
---|---|---|
cacheFieldName |
string |
- |
isShow |
boolean |
The flag indicates whether items without data need to be displayed |
boolean
▸ 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);
}
});
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the target field name. |
sortInfo |
IPivotViewSortInfo |
Indicates the sort info. |
void
| IPivotViewSortInfo
▸ 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);
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the field name. |
position |
SubtotalsPosition |
The indicates set whether subtotal position, only top and bottom is supported. |
void
| SubtotalsPosition
▸ 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"
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the target field name of pivot table. |
type? |
SubtotalType |
Indicates the subtotal type to set. |
void
| SubtotalType
▸ suspendLayout(): void
description
Stop update field util resumeFieldsLayout, it must be used in pairs with resumeFieldsLayout.
void
▸ 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");
Name | Type |
---|---|
theme? |
string | PivotTableTheme |
void
| PivotTableTheme
If no parameters are passed in, get the current theme
▸ 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");
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the ungroup field name. |
void
▸ 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);
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 |
void
▸ 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
Name | Type | Description |
---|---|---|
name |
string |
Indicates the field name. |
area |
PivotTableFieldType |
Indicates which area the field to be put. |
index? |
number |
- |
void
▸ 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")
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. |
void
▸ 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();
void
▸ 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();
Name | Type | Description |
---|---|---|
fieldName |
string |
Indicates the target field name of pivot table. |
filterInfo? |
null | IPivotConditionFilterInfo |
- |
void
| IPivotConditionFilterInfo
return the pivot table value information.