【SpreadJS V15.1 新特性介绍】 数据透视表增加日期切片器支持
V15.1中SpreadJS数据透视表新增了日期切片器，通过方便的图形界面，可轻松筛选出数据透视表数据。日期切片器界面介绍：
选择日期区间标签：通过文本描述当前日期的筛选条件
时间级别：单击显示时间级别下拉菜单，并可选择时间线的级别。
筛选按钮：点击即清空筛选条件，默认显示所有期间
已选中日期区域：仅支持一个连续的日期范围
可参看文末动图，了解数据透视表日期切片器的筛选能力。
使用日期切片器
1、创建一个名为“pt”的数据透视表（initPivotTable方法完整代码见文末）
var spread = GC.Spread.Sheets.findControl(document.getElementById('ss'));
var sheet = spread.getActiveSheet();
initPivotTable();
2、将日期切片器添加到“name”字段
var timeline_date = sheet.slicers.add("timeline_date", pt.name(), "date", GC.Spread.Sheets.Slicers.TimelineStyles.light1(), GC.Spread.Sheets.Slicers.SlicerType.pivotTimeline);
3、控制日期切片器的属性显示
如：不显示水平滚动条
timeline_date.showHorizontalScrollbar(false);让时间线滚动至某一天
timeline_date.scrollPosition(new Date('2021-6-23'));设置时间级别为年
timeline_date.level(GC.Spread.Sheets.Slicers.TimelineLevel.years);
至此即可创建一个日期切片器：
注：initPivotTable方法完整代码如下：
function initPivotTable () {
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;
sourceSheet.setArray(0, 0, dataSource);
sourceSheet.tables.add("table1", 0, 0, 17, 6);
spread.sheets.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.years
},
{
by: GC.Pivot.DateGroupType.quarters
},
{
by: GC.Pivot.DateGroupType.months
},
]
});
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");
}
页:
[1]