表格绑定结合数据透视表

const loadBindData = async(spreadHost:HTMLElement) => {
    let spread = GC.Spread.Sheets.findControl(spreadHost);
    let formData = new FormData();
    formData.append("path", "template.ssjson");
    let template:any = await httpUtils.post("spread/loadTemplate", formData);
    
    spread.fromJSON(template);
}

const customeConfig = (designer: GcDesigner.Spread.Sheets.Designer.Designer) => {
    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",]
                        }
                    ]
                }
            }
        ]
    }
    // spreadjs 绑定数据
    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 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 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();

        }
    }

    // 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";
}