与后端数据透视表结合

let bindingDataExecute = {
    iconClass:"data-binding",
    text: '绑定数据',
    commandName:"bindingData",
    execute:async() => {
        let spread:GC.Spread.Sheets.Workbook = designer.getWorkbook() as GC.Spread.Sheets.Workbook
        spread.suspendPaint();
        let data:any = await httpUtils.post("spread/getData");
        console.log(data);
        spread.getSheet(0).setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(data));
        let pt=spread.getSheet(1).pivotTables.all()[0];
        pt.options.grandTotalPosition=GC.Spread.Pivot.GrandTotalPosition.none;
        pt.updateSource();
        var range=pt.getRange().content;
        console.log(range);
        spread.getSheet(1).charts.all()[0].dataRange(GC.Spread.Sheets.CalcEngine.rangeToFormula(range));
        spread.setActiveSheet("Sheet2");
        spread.resumePaint();

    }
}

//刷新数据透视表
let generateChartExecute = {
    iconClass: "data-binding",
    text: '后端生成透视表图表',
    commandName: "generateChart",
    execute: async () => {
        let formData = new FormData();
        formData.append("path", "template.ssjson");
        let template: any = await httpUtils.post("spread/generateChart", formData);

        let spread: GC.Spread.Sheets.Workbook = designer.getWorkbook() as GC.Spread.Sheets.Workbook
        let sheet: GC.Spread.Sheets.Worksheet = spread.getSheet(1);
        sheet.pivotTables.all()[0].updateSource();

    }
}

//刷新数据透视表
let refreshPivotExecute = {
    iconClass:"data-binding",
    text: '刷新透视表',
    commandName:"refreshPivot",
    execute:async() => {
        let spread:GC.Spread.Sheets.Workbook = designer.getWorkbook() as GC.Spread.Sheets.Workbook
        let sheet:GC.Spread.Sheets.Worksheet = spread.getSheet(1);
        sheet.pivotTables.all()[0].updateSource();

    }
}

let config = JSON.parse(JSON.stringify(GcDesigner.Spread.Sheets.Designer.DefaultConfig as any))
console.log(config)
// 新增Tab
let customerRibbon = {
    id: "operate",
    text: "自定义操作",
    buttonGroups: [
        {
            label: "数据管理",
            commandGroup: {
                children: [
                    {
                        direction: "vertical",
                        commands: ["bindingData", "refreshPivot", "generateChart"]
                    }
                ]
            }
        }, {
            label: "文件管理",
            commandGroup: {
                children: [
                    {
                        direction: "vertical",
                        commands: ["loadFile", "exportTemplate",]
                    }
                ]
            }
        }
    ]
}

// push新的按钮
config.ribbon.push(customerRibbon)
// push新增按钮点击是对应命令
config.commandMap = {
    bindingData: bindingDataExecute,
    refreshPivot: refreshPivotExecute,
    generateChart: generateChartExecute
}
// 重新设置config
designer.setConfig(config)

@CrossOrigin
@RequestMapping(value = "/generateChart", method = RequestMethod.POST)
public String generateChart(@RequestParam(value = "path", required = true) String filename) throws FileNotFoundException {
    String filePath ="D:\\Product\\Spreadjs\\Demo\\\u65B0\u5EFA\u6587\u4EF6\u5939\\GCExcelAndSpreadJS_Service\\template\\"+filename;
    try {
        String workbookJSON;
        Workbook workbook = new Workbook();
        workbook.open(filePath);
        IWorksheet sheet= workbook.getWorksheets().get(0);

        ArrayList<Record> records=getsourceData(100);
        SalesData data=new SalesData();
        data.records=records;
        sheet.setDataSource(data);
        IRange range= sheet.getTables().get(0).getRange();
        IPivotTable pivot= workbook.getWorksheets().get(1).getPivotTables().get(0);
        pivot.setRowGrand(false);
        pivot.setColumnGrand(false);
        pivot.getPivotCache().setSourceData(range);
        IRange range1= pivot.getTableRange2();
        range1=workbook.getWorksheets().get(1).getRange(range1.getRow()+1, range1.getColumn(), range1.getRowCount()-1, range1.getColumnCount());
        workbook.getWorksheets().get(1).getShapes().get(0).getChart().setSourceData(range1);
        pivot.refresh();
        workbook.save("123.xlsx");
        //这里可以获取到透视表的数据，来传给前端
        pivot.getTableRange2().getValue();

        return "后端生成成功";
    } catch (Exception e) {
        e.printStackTrace();
    }
    return "失败";
}

@CrossOrigin
@RequestMapping(value = "/loadTemplate", method = RequestMethod.POST)
public String loadTemplate(@RequestParam(value = "path", required = true) String filename) {
    String filePath ="D:\\Product\\Spreadjs\\Demo\\\u65B0\u5EFA\u6587\u4EF6\u5939\\GCExcelAndSpreadJS_Service\\template\\"+filename;
    try {
        String workbookJSON;
        Workbook workbook = new Workbook();
        workbook.open(filePath);
        workbookJSON = workbook.toJson();
        return workbookJSON;
    } catch (Exception e) {
        e.printStackTrace();
    }
    return "0";
}

@CrossOrigin
@RequestMapping(value = "/getData", method = RequestMethod.POST)
public String getData() throws FileNotFoundException {
    ArrayList<Record> records=getsourceData(100);
    SalesData data=new SalesData();
    data.records=records;
    Gson gson = new Gson();
    String jsonstr = gson.toJson(data); 
    return jsonstr;
}