类: PivotTable

Spread.Pivot.PivotTable

Table of contents

构造方法

• constructor

属性

• options
• views

方法

• add
• addCalcField
• addCalcItem
• addConditionalRule
• autoFitColumn
• clearOverwriteList
• collapse
• connectSlicer
• dataPosition
• deserialize
• disconnectSlicer
• getAllSlicers
• getCalcFields
• getCalcItems
• getConditionalRules
• getField
• getFieldsByArea
• getItemsByField
• getNodeInfo
• getNodeValue
• getOverwriteList
• getPivotAreaRanges
• getPivotDetails
• getRange
• getSource
• getSourceFields
• getStyle
• getThemeName
• group
• isConnectedSlicer
• labelFilter
• layoutType
• name
• position
• refresh
• remove
• removeCalcField
• removeCalcItem
• removeConditionalRule
• resumeLayout
• serialize
• setNodeValue
• setStyle
• showDataAs
• showNoData
• sort
• subtotalPosition
• subtotalType
• suspendLayout
• theme
• ungroup
• updateCalcItem
• updateField
• updateFieldName
• updateSource
• valueFilter

构造方法

constructor

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

数据透视表

参数

属性名	类型	说明
name	string	数据透视表的名称
sheet?	Worksheet	透视表所在表单
row?	number	数据透视表的起始行
col?	number	数据透视表的起始列
layout?	PivotTableLayoutType	数据透视表布局类型
style?	PivotTableTheme	数据透视表主题样式
options?	IPivotTableOption	-
layoutModel?	any	-

属性

options

• options: IPivotTableOption

透视表选项

property [allowMultipleFiltersPerField] 指示是否在一个字段中使用多个过滤器

property [fillDownLabels] 指示是否显示重复标签项

property [insertBlankLineAfterEachItem] 指示是否在每个项目末尾插入空白行

property [grandTotalPosition] 指示是否在行、列或两者中显示总计

property [subtotalsPosition] 指示显示小计顶部或底部或不显示

property [displayFieldsInPageFilterArea] 指示页面区域中的字段显示先上后下或先下后上

property [reportFilterFieldsPerColumn] 指示每列的报表文件管理器字段数

property [bandRows] 表示是否显示带行

property [bandColumns] 表示是否显示带列

property [showRowHeader] 指示是否显示行标题样式

property [showColumnHeader] 指示是否显示列标题样式

property [showDrill] 指示是否显示展开/折叠按钮

property [showMissing] 指示missingCaption 选项是否生效

property [missingCaption] 指示当实际值为空时应显示什么值

property [rowLabelIndent] 表示每级标题的缩进

property [printDrill] 在数据透视表上显示时打印展开/折叠按钮

property [itemPrintTitles] 在每个数据透视表上重复行标签

property [fieldPrintTitles] 设置打印标题

property [showFilter] 表示是否显示过滤按钮

property [showToolTip] 指示是否显示工具提示

property [mergeItem] 指示是否将带有标签的单元格合并并居中

property [isShowErrorValue] 指示 errorValueInfo 选项是否生效

property [errorValueInfo] 指示当实际值错误时应显示什么值

property [rowHeaderCaption] 指示应在紧凑布局中显示什么值来替换行标签

property [colHeaderCaption] 指示应在紧凑布局中显示什么值来替换列标签

property [showHeaders] 表示显示字段标题

property [calcItemAggregation] 指示数据透视表总计是否包含 calcItem 的值

property [enableDataValueEditing] 指示是否允许编辑数据透视表数据区域的单元格值

---

views

• views: IPivotTableViewManager

数据透视表视图管理器

方法

add

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

description 向数据透视表添加字段

代码示例

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) //向透视表行区域中添加一个字段，字段名为displayName

参数

属性名	类型	说明
sourceName	string	字段的源名称
displayName	string	字段的显示名称
area	number	要添加到的区域
subtotal?	SubtotalType	-
index?	number	-

返回值

void

---

addCalcField

▸ addCalcField(fieldName, formula): void

description 添加计算字段，计算字段只能添加到透视表的值区

代码示例

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");

参数

属性名	类型	说明
fieldName	string	计算字段名
formula	string	计算公式

返回值

void

---

addCalcItem

▸ addCalcItem(sourceName, calcItemName, formula): void

description 添加calcItem字段

代码示例

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]");

参数

属性名	类型	说明
sourceName	string	sourceField名称
calcItemName	string	sourceField的calcItem名称
formula	string	sourceField的calcItem公式

返回值

void

---

addConditionalRule

▸ addConditionalRule(pivotArea, conditionalRule): void

在数据透视表中添加规则

代码示例

//本示例使用了getRule方法
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);

参数

属性名	类型	说明
pivotArea	IPivotArea[]	数据透视表中的数据透视区域
conditionalRule	ConditionRuleBase	数据透视表中添加的规则

返回值

void

---

autoFitColumn

▸ autoFitColumn(): void

description 设置每个字段项的最小可视列宽度

代码示例

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

▸ clearOverwriteList(): void

clear overwrite info list.

返回值

void

---

collapse

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

description 获取或设置透视表字段的折叠信息

代码示例

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);

参数

属性名	类型	说明
fieldName	string	目标字段名称
item	string	折叠项名称
isCollapse?	boolean	-

返回值

boolean | void

是否折叠

---

connectSlicer

▸ connectSlicer(name): void

将切片器与PivotTable连接起来

参数

属性名	类型	说明
name	string	切片器名称

返回值

void

---

dataPosition

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

description 获取或设置值的位置

代码示例

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) //将值移动到行区域，索引为0
pivotTable.dataPosition();//{positionType:1,positionIndex:0}

参数

属性名	类型
positionType?	DataPosition
positionIndex?	number

返回值

void | IDataPosition

返回数据位置信息的透视表或void

---

deserialize

▸ deserialize(serializeInfo): void

description 将序列化透视表数据还原到现有透视表

代码示例

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);

参数

属性名	类型	说明
serializeInfo	ISerializeInfo	序列化的透视表数据

返回值

void

---

disconnectSlicer

▸ disconnectSlicer(name): void

与透视表断开切片器的连接

参数

属性名	类型
name	string

返回值

void

---

getAllSlicers

▸ getAllSlicers(): PivotTableItemSlicer[]

获取所有切片器与PivotTable的关联

返回值

PivotTableItemSlicer[]

切片器与透视表的关联

---

getCalcFields

▸ getCalcFields(): ICalcFieldInfo[]

description 获取所有计算字段的信息

代码示例

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();

返回值

ICalcFieldInfo[]

返回所有计算字段的信息

---

getCalcItems

▸ getCalcItems(sourceName?): ICalcItemInfo[]

description 获取calcItems信息

代码示例

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");

参数

属性名	类型	说明
sourceName?	string	sourceField名称

返回值

ICalcItemInfo[]

---

getConditionalRules

▸ getConditionalRules(pivotArea): ConditionRuleBase[]

获取使用数据透视区域的规则

代码示例

//本示例使用了getRule方法
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());

参数

属性名	类型	说明
pivotArea	IPivotArea	数据透视表中的数据透视区域

返回值

ConditionRuleBase[]

透视区域中的规则

---

getField

▸ getField(fieldName): IFieldInfo

description 按字段名从透视表获取字段信息

代码示例

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");

参数

属性名	类型	说明
fieldName	string	字段的名称

返回值

IFieldInfo

返回字段信息

---

getFieldsByArea

▸ getFieldsByArea(area?): IFieldInfo[]

description 从数据透视表或数据透视表区域获取所有字段信息

代码示例

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);

参数

属性名	类型
area?	PivotTableFieldType

返回值

IFieldInfo[]

返回数据透视表区域中的所有字段信息

---

getItemsByField

▸ getItemsByField(fieldName): any

description 按字段名从数据透视表中获取所有项

参数

属性名	类型	说明
fieldName	string	透视表字段的字段名

返回值

any

返回透视表字段的所有项

---

getNodeInfo

▸ getNodeInfo(row, col): IPivotNodeInfo

compose overwrite info from sheet row and column.

参数

属性名	类型	说明
row	number	sheet row of the cell.
col	number	sheet column of the cell.

返回值

IPivotNodeInfo

The node info want to get.

---

getNodeValue

▸ getNodeValue(nodeInfo): number

设定值 by node info.

参数

属性名	类型	说明
nodeInfo	IPivotNodeInfo	The node info want to get.

返回值

number

value of node info.

---

getOverwriteList

▸ getOverwriteList(): IPivotOverwriteNodeInfo[]

get all overwrite info of pivot cache.

返回值

IPivotOverwriteNodeInfo[]

overwrite info list.

---

getPivotAreaRanges

▸ getPivotAreaRanges(pivotArea): Range[]

description 获取指定数据透视区域对应的表单区域

代码示例

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);

参数

属性名	类型	说明
pivotArea	IPivotArea	指定透视表区域

返回值

Range[]

指定数据透视区域相对应的表单区域

---

getPivotDetails

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

description 获取数据透视表详细信息

代码示例

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"}]);

参数

属性名	类型	说明
pivotItemInfo	IPivotItemInfo[]	透视详细信息列表

返回值

void | any[][]

---

getRange

▸ getRange(): IPivotTableRange

description 获取数据透视表的范围，包括页面和内容它们是只读的，更改范围不会产生任何效果

代码示例

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);

返回值

IPivotTableRange

返回当前透视表范围

---

getSource

▸ getSource(): string

description 获取数据透视表数据引用

代码示例

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

▸ getSourceFields(): ISourceFieldInfo[]

description 获取数据透视表源字段信息

代码示例

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();

返回值

ISourceFieldInfo[]

---

getStyle

▸ getStyle(pivotArea): Style

description 按特定的数据透视区域获取样式

代码示例

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);

参数

属性名	类型	说明
pivotArea	IPivotArea	指定透视表区域

返回值

Style

GC.Spread.Sheets.Style

---

getThemeName

▸ getThemeName(): undefined | string

获取或设置数据透视表的样式名称。

返回值

undefined | string

返回数据透视表样式名称。

---

group

▸ group(groupInfo): void

description 对字段项进行分组

代码示例

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();

参数

属性名	类型	说明
groupInfo	IDateGroupsInfo | INumberGroupInfo | ITextGroupInfo	分组信息

返回值

void

---

isConnectedSlicer

▸ isConnectedSlicer(name): boolean

切片器是否与透视表关联

参数

属性名	类型	说明
name	string	切片器名称

返回值

boolean

切片器是否与透视表关联

---

labelFilter

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

获取或设置字段的标签筛选信息

代码示例

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();

参数

属性名	类型	说明
fieldName	string	数据透视表的目标字段名称
filterInfo?	null | IPivotConditionFilterInfo | IPivotTextFilterInfo	-

返回值

void | IPivotConditionFilterInfo | IPivotTextFilterInfo

返回数据透视表标签筛选信息

---

layoutType

▸ layoutType(type?): PivotTableLayoutType

description 获取或设置数据透视表的布局类型

代码示例

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

参数

属性名	类型
type?	PivotTableLayoutType

返回值

PivotTableLayoutType

如果没有传入参数，则获取当前布局类型

---

name

▸ name(name?): string | void

description 获取或设置数据透视表名称

代码示例

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?	string	数据透视表名称

返回值

string | void

---

position

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

description 获取或设置透视表的起始位置，当有足够的单元格放置透视表时，透视表的位置将自动更改

代码示例

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"}

参数

属性名	类型
row?	number
col?	number
sheetName?	string

返回值

void | IPivotTablePosition

---

refresh

▸ refresh(): void

description 刷新字段布局，重新计算表中的所有字段数据

返回值

void

---

remove

▸ remove(fieldName): void

description 按名称删除字段

代码示例

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");

参数

属性名	类型	说明
fieldName	string	将被删除的字段名

返回值

void

---

removeCalcField

▸ removeCalcField(fieldName): void

description 移除计算字段

代码示例

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);

参数

属性名	类型	说明
fieldName	string	计算字段名

返回值

void

---

removeCalcItem

▸ removeCalcItem(sourceName, calcItemName): void

description 移除calcItem

代码示例

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");

参数

属性名	类型	说明
sourceName	string	sourceField名称
calcItemName	string	sourceField的calcItem名称

返回值

void

---

removeConditionalRule

▸ removeConditionalRule(conditionalRule): void

删除透视表的格式

代码示例

//本示例使用了getRule方法
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);

参数

属性名	类型	说明
conditionalRule	ConditionRuleBase	透视表设置的条件规则

返回值

void

---

resumeLayout

▸ resumeLayout(): void

description 停止更新字段, 直至 suspendLayout 结束, 并且必须与 suspendLayout 成对使用

返回值

void

---

serialize

▸ serialize(): ISerializeInfo

description 获取序列化的透视表数据

返回值

ISerializeInfo

序列化数据透视表数据

---

setNodeValue

▸ setNodeValue(nodeInfo, value?): void

将覆盖值设置为枢轴缓存。

参数

属性名	类型	说明
nodeInfo	IPivotNodeInfo	要设置的节点信息
value?	number	-

返回值

void

---

setStyle

▸ setStyle(pivotArea, style): void

description 设置或删除特定透视表区域的样式

代码示例

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);

参数

属性名	类型	说明
pivotArea	IPivotArea	指定透视表区域
style	Style	设置指定透视区域的样式,null或未定义可删除指定透视区域的样式

返回值

void

---

showDataAs

▸ showDataAs(fieldName, showDataAsInfo?): void | IPivotShowDataAsInfo

description 获取或设置一个值字段的showDataAs信息

参数

属性名	类型	说明
fieldName	string	值字段的名称将应用显示值
showDataAsInfo?	IPivotShowDataAsInfo	-

返回值

void | IPivotShowDataAsInfo

---

showNoData

▸ showNoData(cacheFieldName, isShow): boolean

description 设置或获取 "显示无数据的项 "信息字段

代码示例

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);

参数

属性名	类型	说明
cacheFieldName	string	-
isShow	boolean	该标志表示是否需要显示没有数据的项

返回值

boolean

---

sort

▸ sort(fieldName, sortInfo): void | IPivotViewSortInfo

description 获取或设置透视表的字段排序

代码示例

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);
    }
});

参数

属性名	类型	说明
fieldName	string	目标字段名称
sortInfo	IPivotViewSortInfo	排序信息

返回值

void | IPivotViewSortInfo

---

subtotalPosition

▸ subtotalPosition(fieldName, position): void | SubtotalsPosition

description 获取或设置subtotal的位置信息

代码示例

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);

参数

属性名	类型	说明
fieldName	string	字段名称
position	SubtotalsPosition	指示设置是否小计位置，仅支持顶部和底部

返回值

void | SubtotalsPosition

---

subtotalType

▸ subtotalType(fieldName, type?): void

获取或设置字段的SubTotalType

代码示例

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) //将名称字段的小计类型设置为"fieldName"

参数

属性名	类型	说明
fieldName	string	数据透视表的目标字段名称
type?	SubtotalType	要设置的小计类型

返回值

void

---

suspendLayout

▸ suspendLayout(): void

description 停止更新字段直至 resumeFieldsLayout, 必须与 resumeFieldsLayout 成对使用

返回值

void

---

theme

▸ theme(theme?): void | PivotTableTheme

获取或设置数据透视表主题

代码示例

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");

参数

属性名	类型
theme?	string | PivotTableTheme

返回值

void | PivotTableTheme

如果没有传入参数，则获取当前主题

---

ungroup

▸ ungroup(fieldName): void

description 按字段名称取消对字段的分组

代码示例

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");

参数

属性名	类型	说明
fieldName	string	取消分组的字段名称

返回值

void

---

updateCalcItem

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

description 更新calcItem

代码示例

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);

参数

属性名	类型	说明
sourceName	string	sourceField名称
calcItemName	string	sourceField的calcItem名称
formula	string	此 calcItem 的新公式
priority	number	此 calcItem 的新优先级

返回值

void

---

updateField

▸ updateField(name, area, index?): void

description 更新字段区域和索引

代码示例

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	string	字段名称
area	PivotTableFieldType	字段要放置的区域
index?	number	-

返回值

void

---

updateFieldName

▸ updateFieldName(oldName, newName): void

description 更新现有字段名

代码示例

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")

参数

属性名	类型	说明
oldName	string	数据透视表中字段的旧显示名称
newName	string	数据透视表中字段的新显示名称

返回值

void

---

updateSource

▸ updateSource(): void

description 刷新数据透视表数据源

代码示例

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

▸ valueFilter(fieldName, filterInfo?): void | IPivotConditionFilterInfo

获取或设置字段的值筛选信息

代码示例

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();

参数

属性名	类型	说明
fieldName	string	数据透视表的目标字段名称
filterInfo?	null | IPivotConditionFilterInfo	-

返回值

void | IPivotConditionFilterInfo

返回透视表值信息