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

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

1. GC.Spread.Sheets.Slicers.SlicerType
   
2. enum SlicerType {
   
3.     table = 0,
   
4.     pivotTable = 1
   
5. }

复制代码

添加切片器的API：

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

复制代码

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

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

复制代码

删除切片器的API:

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

复制代码

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

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

复制代码

Step2: 对透视表设置筛选；

1. / set filter info in "name" field.
   
2. var nameFilter = {
   
3.     condition: undefined,
   
4.     textItem: {
   
5.         isAll: false,
   
6.         list: [
   
7.             "johnson",
   
8.             "radow",
   
9.             "vic"
   
10.         ]
    
11.     }
    
12. }
    
13. pt.labelFilter("name", nameFilter);
    
14. 
    
15. // set filter info in "date" field.
    
16. var dateFilter = {
    
17.     condition: undefined,
    
18.     textItem: {
    
19.         isAll: false,
    
20.         list: [
    
21.             "<8/10/2020",
    
22.             "Qtr1",
    
23.             "Qtr2",
    
24.             ">7/20/2021"
    
25.         ]
    
26.     }
    
27. }
    
28. pt.labelFilter("date", dateFilter);

复制代码

Step3：插入透视表切片器；

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

复制代码

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

1. var nameFilter2 = {
   
2.     condition: undefined,
   
3.     textItem: {
   
4.     isAll: false,
   
5.         list: [
   
6.             "chris"
   
7.         ]
   
8.     }
   
9. }
   
10. pt.labelFilter("name", nameFilter2);

复制代码

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

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

复制代码

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

1. /**
   
2. * Connect pivot table with the slicer
   
3. * @param {string} ptName name of pivot table
   
4. */
   
5. connectPivotTable (ptName: string): void

复制代码

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

1. /**
   
2. * Disconnect pivot table with the slicer
   
3. * @param {string} ptName name of pivot table
   
4. */
   
5. disconnectPivotTable (ptName: string): void

复制代码

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

1. /**
   
2. * Check whether pivot table connect with the slicer
   
3. * @param {string} ptName name of pivot table
   
4. */
   
5. isConnectedPivotTable (ptName: string): boolean

复制代码

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

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

复制代码

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

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

复制代码

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

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

复制代码

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

1. /**
   
2. * Connect slicer with the PivotTable
   
3. * @param {string} name name of slicer
   
4. */
   
5. connectSlicer (name: string): void

复制代码

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

1. /**
   
2. * Disconnect slicer with PivotTable
   
3. * @param {string} ptName name of slicer
   
4. */
   
5. disconnectSlicer (name: string): void

复制代码

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

1. /**
   
2. * Whether the slicer is connected with the PivotTable
   
3. * @param {string} name name of slicer
   
4. * @returns {boolean} Whether the slicer is connected with the PivotTable
   
5. */
   
6. isConnectedSlicer (name: string): boolean

复制代码

完整的测试代码如下：

1. var datas = [
   
2.     ["1", "NewYork", "1968/6/8", "80", "180"],
   
3.     ["4", "NewYork", "1972/7/3", "72", "168"],
   
4.     ["4", "NewYork", "1964/3/2", "71", "179"],
   
5.     ["5", "Washington", "1972/8/8","80", "171"],
   
6.     ["6", "Washington", "1986/2/2", "89", "161"],
   
7.     ["7", "Washington", "2012/2/15", "71", "240"]];
   
8. var table = activeSheet.tables.addFromDataSource("table1", 2, 2, datas);
   
9. dataColumns = ["Name", "City", "Birthday", "Weight", "Height"];
   
10. table.setColumnName(0, dataColumns[0]);
    
11. table.setColumnName(1, dataColumns[1]);
    
12. table.setColumnName(2, dataColumns[2]);
    
13. table.setColumnName(3, dataColumns[3]);
    
14. table.setColumnName(4, dataColumns[4]);
    
15. var pt = activeSheet.pivotTables.add("pivotTable1", "table1", 10, 2);
    
16. pt.add("Name", "Name", GC.Spread.Pivot.PivotTableFieldType.rowField);
    
17. pt.add("City", "City", GC.Spread.Pivot.PivotTableFieldType.columnField);
    
18. pt.add("Height", "Height", GC.Spread.Pivot.PivotTableFieldType.valueField);
    
19. 
    
20. // add two slicers
    
21. var slicer_name = activeSheet.slicers.add("slicer1", pt.name(), "Name", GC.Spread.Sheets.Slicers.SlicerStyles.dark1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
    
22. var slicer_name2 = activeSheet.slicers.add("slicer2", pt.name(), "Name", GC.Spread.Sheets.Slicers.SlicerStyles.other2(), GC.Spread.Sheets.Slicers.SlicerType.pivotTable);
    
23. slicer_name.startRow(2);
    
24. slicer_name2.startRow(2);
    
25. slicer_name.startColumn(7);
    
26. slicer_name2.startColumn(11);
    
27. 
    
28. // let slicer_name2 disconnect with PivotTable.
    
29. slicer_name2.disconnectPivotTable(pt.name());
    
30. slicer_name2.captionName("slicer_disconnect");
    
31. 
    
32. // set filter in PivotTable
    
33. var filter = {
    
34.     textItem: {
    
35.         isAll: false,
    
36.         list: ["4", "6", "7"]
    
37.     }
    
38. }
    
39. pt.labelFilter("Name", filter)

复制代码