SpreadJSV15.0新特新解密-透视表：支持切片器

收藏

本帖最后由 Winny 于 2022-1-21 10:42 编辑

表格产品中切片器是一个十分有用的功能，使数据更加容易筛选，从而做一些简单的数据可视化功能，切片器的使用场景一般有两种：
1. 将普通表格转化为超级表之后，使用切片器；
2. 在透视表中使用切片器。
SpreadJS在很早就支持了对于表格的切片器，而V15.0则支持了对透视表使用切片器，本章将详细介绍在SpreadJS中如何对透视表使用切片器。
SpreadJS中切片器分为TableSlicer和PivotTableSlicer，他们都由SlicerCollection管理，TableSlicer用来对表格做出响应，PivotTableSlicer则用来对透视表做出响应。在代码层面，他们的区分如下：

GC.Spread.Sheets.Slicers.SlicerType
enum SlicerType {
table = 0,
pivotTable = 1
}

复制代码

添加切片器的API：

///* function add(slicerName: string, targetName: string, itemName: string, style: GC.Spread.Sheets.Slicers.SlicerStyle, type?: GC.Spread.Sheets.Slicers.SlicerType): any
/**
* Adds a slicer to the sheet.
* @param {string} slicerName The name of the slicer.
* @param {string} targetName The name of the table or pivot table that relates to the slicer.
* @param {string} itemName The name of the table column or pivot table field that relates to the slicer.
* @param {GC.Spread.Sheets.Slicers.SlicerStyle} style The style of the slicer.
* @param {GC.Spread.Sheets.Slicers.SlicerType} [type] slicer type, table as dafault
* @return {any} The slicer that has been added to the sheet.
*/
function add (slicerName, targetName, itemName, style, type);

复制代码

透视表目前只支持在行、列字段上添加切片。
获取切片器的API：

/**
* Gets a slicer in the sheet by the name.
* @param {string} name The name of the slicer.
* @returns {GC.Spread.Sheets.Slicers.ISlicer} The slicer that has the indicated name.
*/
function get (name: string): GC.Spread.Sheets.Slicers.ISlicer

复制代码

删除切片器的API:

/**
* Removes a slicer from the sheet using the indicated slicer name.
* @param {string} name The name of the slicer.
*/
function remove (name: string): void

复制代码

接下来会通过代码演示如何创建一个透视表及与之关联的切片器，可以使用透视表的切片器完成对透视表的筛选，同时，借助透视表本身的筛选功能也会响应对应切片器。
Step1: 创建透视表；

var spread = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var sheet = spread.getActiveSheet();
var dataSource = [
[ "name", "product", "date", "amount", "price", "sales" ],
[ "chris", "desk", new Date("2020-10-08T16:00:00.000Z"), 5, 199, 995 ],
[ "radow", "pen", new Date("2020-09-15T16:00:00.000Z"), 2, 5, 10 ],
[ "peyton", "pencil", new Date("2021-06-22T16:00:00.000Z"), 6, 1.5, 9 ],
[ "johnson", "chair", new Date("2021-07-19T16:00:00.000Z"), 7, 68, 476 ],
[ "vic", "notebook", new Date("2021-01-13T16:00:00.000Z"), 7, 3.2, 22.4 ],
[ "lan", "desk", new Date("2021-03-12T16:00:00.000Z"), 9, 199, 1791 ],
[ "chris", "pen", new Date("2021-03-06T16:00:00.000Z"), 4, 5, 20 ],
[ "chris", "pencil", new Date("2020-09-02T16:00:00.000Z"), 10, 1.5, 15 ],
[ "radow", "chair", new Date("2020-08-09T16:00:00.000Z"), 3, 68, 204 ],
[ "peyton", "notebook", new Date("2021-02-08T16:00:00.000Z"), 9, 3.2, 28.8 ],
[ "johnson", "desk", new Date("2021-07-03T16:00:00.000Z"), 7, 199, 1393],
[ "vic", "pen", new Date("2021-06-27T16:00:00.000Z"), 8, 5, 40],
[ "lan", "pencil", new Date("2020-10-10T16:00:00.000Z"), 2, 1.5, 3],
[ "chris", "chair", new Date("2021-03-04T16:00:00.000Z"), 2, 68, 136],
[ "chris", "notebook", new Date("2021-02-21T16:00:00.000Z"), 11, 3.2, 35.2],
[ "radow", "desk", new Date("2021-06-03T16:00:00.000Z"), 6, 199, 1194]
];
var sourceSheet = spread.sheets[0];
sourceSheet.setArray(0, 0, dataSource);
sourceSheet.tables.add("table1", 0, 0, 17, 6);
spread.sheets[0].name("sourceSheet");
var pivotSheet = new GC.Spread.Sheets.Worksheet('pivotTable1');
pivotSheet.setRowCount(2000);
pivotSheet.setColumnCount(30);
spread.addSheet(1, pivotSheet);
var pt = pivotSheet.pivotTables.add("pivotTable1", "table1", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.compact, GC.Spread.Pivot.PivotTableThemes.medium1, null);
pt.suspendLayout();
pt.add("name", "name", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.add("product", "product", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.group({
originFieldName: 'date',
dateGroups: [
{
by: GC.Pivot.DateGroupType.quarters
}
]
});
pt.add("date", "date", GC.Spread.Pivot.PivotTableFieldType.columnField);
pt.add("sales", "sales", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
pt.resumeLayout();
pt.autoFitColumn();
spread.setActiveSheet("pivotTable1");

复制代码

Step2: 对透视表设置筛选；

/ set filter info in "name" field.
var nameFilter = {
condition: undefined,
textItem: {
isAll: false,
list: [
"johnson",
"radow",
"vic"
]
}
}
pt.labelFilter("name", nameFilter);
// set filter info in "date" field.
var dateFilter = {
condition: undefined,
textItem: {
isAll: false,
list: [
"<8/10/2020",
"Qtr1",
"Qtr2",
">7/20/2021"
]
}
}
pt.labelFilter("date", dateFilter);

复制代码

Step3：插入透视表切片器；

// insert slicer which connect to pivot table "name" field.
var slicer_name = sheet.slicers.add("name", pt.name(), "name", GC.Spread.Sheets.Slicers.SlicerStyles.other2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable)
slicer_name.startRow(1)
slicer_name.startColumn(6)
slicer_name.startRowOffset(0)
slicer_name.startColumnOffset(0)
// insert slicer which connect to pivot table "date" field.
var slicer_date = sheet.slicers.add("date", pt.name(), "date", GC.Spread.Sheets.Slicers.SlicerStyles.other2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable)
slicer_date.startRow(1)
slicer_date.startColumn(8)
slicer_date.startRowOffset(0)
slicer_date.startColumnOffset(0)

复制代码

Step4：通过切片器设置筛选项；

var nameFilter2 = {
condition: undefined,
textItem: {
isAll: false,
list: [
"chris"
]
}
}
pt.labelFilter("name", nameFilter2);

复制代码

至此，通过代码即可完成透视表切片器的创建及使用。你可以点击切片器右上方的"×"按钮来清除筛选条件，需要注意的使，条件格式不会随着切片器的清除而取消。
点击切片器右上方的"M"按钮可以实现切片器根据多个数据项进行筛选，在代码层面可以通过改变筛选模式来达到多条件筛选的目的，具体代码如下：

/**
* Gets or sets the multiSelect of the slicer.
* @param {boolean} [value] The multiSelect of the slicer.
* @returns {boolean} If no value is set, returns the multiSelect of the slicer
*/
multiSelect (value?: boolean)

复制代码

切片器与透视表关联：在透视表切片器中，可以设置切片器和数据透视表之间的关联；
连接关系由双方控制，切片器或数据透视表都可以连接或断开连接；
如果连接关系断开，切片器上的筛选行为不会对透视表产生影响，同理，透视表上的筛选行为也不会对切片器产生影响。
透视表切片器（GC.Spread.Pivot.PivotTableItemSlicer）上的主要API如下所示：1. 建立透视表与切片器之间的连接；

/**
* Connect pivot table with the slicer
* @param {string} ptName name of pivot table
*/
connectPivotTable (ptName: string): void

复制代码

2. 断开透视表与切片器之间的连接；

/**
* Disconnect pivot table with the slicer
* @param {string} ptName name of pivot table
*/
disconnectPivotTable (ptName: string): void

复制代码

3. 检查切片器与透视表之间是否有连接；

/**
* Check whether pivot table connect with the slicer
* @param {string} ptName name of pivot table
*/
isConnectedPivotTable (ptName: string): boolean

复制代码

4. 获取当前切片器连接的数据透视表集合；

/**
* get all connected PivotTables
* @returns {GC.Spread.Pivot.PivotTable[]} PivotTables connected with the slicer.
*/
getAllConnectedPivotTables (): PivotTable[]

复制代码

5. 获取所有透视表，无论是都连接。

/**
* get all PivotTables whether connected or not.
* @returns {GC.Spread.Pivot.PivotTable[]} PivotTables whose source is same as the slicer.
*/
getAllPivotTables (): PivotTable[]

复制代码

对于透视表（GC.Spread.Pivot.PivotTable），核心API如下：
1. 获取当前透视表所有建立连接的切片器；

/**
* Get All Slicers connect with the PivotTable
* @returns {GC.Spread.Pivot.PivotTableItemSlicer[]} slicers connect with the PivotTable
*/
getAllSlicers (): GC.Spread.Pivot.PivotTableItemSlicer[]

复制代码

2. 建立与切片器之间的连接；

/**
* Connect slicer with the PivotTable
* @param {string} name name of slicer
*/
connectSlicer (name: string): void

复制代码

3. 断开透视表与切片器之间的连接；

/**
* Disconnect slicer with PivotTable
* @param {string} ptName name of slicer
*/
disconnectSlicer (name: string): void

复制代码

4. 获取当前透视表是否有已经建立连接的切片器；

/**
* Whether the slicer is connected with the PivotTable
* @param {string} name name of slicer
* @returns {boolean} Whether the slicer is connected with the PivotTable
*/
isConnectedSlicer (name: string): boolean

复制代码

完整的测试代码如下：

var datas = [
["1", "NewYork", "1968/6/8", "80", "180"],
["4", "NewYork", "1972/7/3", "72", "168"],
["4", "NewYork", "1964/3/2", "71", "179"],
["5", "Washington", "1972/8/8","80", "171"],
["6", "Washington", "1986/2/2", "89", "161"],
["7", "Washington", "2012/2/15", "71", "240"]];
var table = activeSheet.tables.addFromDataSource("table1", 2, 2, datas);
dataColumns = ["Name", "City", "Birthday", "Weight", "Height"];
table.setColumnName(0, dataColumns[0]);
table.setColumnName(1, dataColumns[1]);
table.setColumnName(2, dataColumns[2]);
table.setColumnName(3, dataColumns[3]);
table.setColumnName(4, dataColumns[4]);
var pt = activeSheet.pivotTables.add("pivotTable1", "table1", 10, 2);
pt.add("Name", "Name", GC.Spread.Pivot.PivotTableFieldType.rowField);
pt.add("City", "City", GC.Spread.Pivot.PivotTableFieldType.columnField);
pt.add("Height", "Height", GC.Spread.Pivot.PivotTableFieldType.valueField);
// add two slicers
var slicer_name = activeSheet.slicers.add("slicer1", pt.name(), "Name", GC.Spread.Sheets.Slicers.SlicerStyles.dark1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
var slicer_name2 = activeSheet.slicers.add("slicer2", pt.name(), "Name", GC.Spread.Sheets.Slicers.SlicerStyles.other2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
slicer_name.startRow(2);
slicer_name2.startRow(2);
slicer_name.startColumn(7);
slicer_name2.startColumn(11);
// let slicer_name2 disconnect with PivotTable.
slicer_name2.disconnectPivotTable(pt.name());
slicer_name2.captionName("slicer_disconnect");
// set filter in PivotTable
var filter = {
textItem: {
isAll: false,
list: ["4", "6", "7"]
}
}
pt.labelFilter("Name", filter)

复制代码

		自动登录	找回密码
密码			立即注册

SpreadJSV15.0新特新解密-透视表：支持切片器

0 个回复