WPS JS生成透视表范例

//生成透视表
Sheets.Add(undefined, undefined, undefined, undefined);
ActiveSheet.Name="数据透视";
Sheets.Item("订单明细").Select();
var Report=ActiveSheet.Name;

Range("A1:BO"+a).Select();//变量a为数据源行数
Selection.Activate();
let pvt2cache=ActiveWorkbook.PivotCaches().Create(xlDatabase, "=订单明细!R1C1:R"+a+"C"+b);
Sheets.Item("数据透视").Select();
let pvt2=pvt2cache.CreatePivotTable("=数据透视!R3C1", "数据透视表1", undefined, xlPivotTableVersion15);
Range("A1").Activate();

 

/*透视表包含3个行字段,position的数字可排序*/

(obj=>{
obj.Orientation = xlRowField;
obj.Position = 1;
})(ActiveSheet.PivotTables("数据透视表1").PivotFields("店铺名称"));

 

(obj=>{
obj.Orientation = xlRowField;
obj.Position = 2;
})(ActiveSheet.PivotTables("数据透视表1").PivotFields("店铺编码"));

(obj=>{
obj.Orientation = xlRowField;
obj.Position = 3;
})(ActiveSheet.PivotTables("数据透视表1").PivotFields("店铺地址"));

ActiveSheet.PivotTables("数据透视表1").AddDataField(ActiveSheet.PivotTables("数据透视表1").PivotFields("对账金额"), undefined, undefined);
ActiveSheet.PivotTables("数据透视表1").AddDataField(ActiveSheet.PivotTables("数据透视表1").PivotFields("入账金额"), undefined, undefined);
ActiveSheet.PivotTables("数据透视表1").AddDataField(ActiveSheet.PivotTables("数据透视表1").PivotFields("平台服务费(含佣金和配送服务费)"), undefined, undefined);
ActiveSheet.PivotTables("数据透视表1").AddDataField(ActiveSheet.PivotTables("数据透视表1").PivotFields("商家应收款"), undefined, undefined);
ActiveSheet.Name="数据透视"