【SpreadJS v16.1 新特性介绍】透视表增强：小计位置 / 排序 / GetPivotData 函数开关

收藏

本帖最后由 AlexZ 于 2023-6-15 14:23 编辑

单字段小计位置 / Subtotal Position
新版本中，可以对单字段的小计位置进行设置
pivottable-subtotal-option-for-single-fields-gif-sjs-v16-1-0-release-blog.gif

pivottable-subtotal-option-for-single-fields-gif-sjs-v16-1-0-release-blog.gif

///* function subtotalPosition(fieldName: string, position: GC.Spread.Pivot.SubtotalsPosition): GC.Spread.Pivot.SubtotalsPosition
/**
* @description set or get field show subtotal position information.
* @param {string} fieldName Indicates the field name.
* @param {GC.Spread.Pivot.SubtotalsPosition} position The indicates set whether subtotal position, only top and bottom is supported.
* @returns {GC.Spread.Pivot.SubtotalsPosition}
* @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("Date", GC.Spread.Pivot.SubtotalsPosition.top);
*/
subtotalPosition (fieldName: string, position?: SubtotalsPosition): SubtotalsPosition {

复制代码

支持对值进行排序 / Sort Value
新版本的数据透视表，支持对数据进行排序，方式与 Excel 保持兼容，如下图所示：

API

/**
* @description get or set sort for a field of pivot table.
* @param {string} fieldName Indicates the target field name.
* @param {GC.Pivot.IPivotViewSortInfo} [sortInfo] Indicates the sort info.
* @returns {GC.Pivot.IPivotViewSortInfo | void}
**/
sort (fieldName: string, sortInfo?: GC.Pivot.IPivotViewSortInfo): GC.Pivot.IPivotViewSortInfo | void

复制代码

示例代码