前端数据魔法：解析数据透视功能实现

///1.通过编辑器UI界面设计空模板，创建对应的数据绑定关系以及透视表，图表

///2.更新数据源表，以及自动更新数据透视表和图表
let spread:GC.Spread.Sheets.Workbook = designer.getWorkbook() as GC.Spread.Sheets.Workbook
spread.suspendPaint();
//从后端获取最新的数据源
let data:any = await httpUtils.post("spread/getData");

//更新web端的原始记录表作为新数据源
spread.getSheet(0).setDataSource(new GC.Spread.Sheets.Bindings.CellBindingSource(data));
let pt=spread.getSheet(1).pivotTables.all()[0];

//更新数据透视表
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();

//根据数据透视结果后续做其他使用

@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;
}

public ArrayList<Record> getsourceData(int count) {
    ArrayList<Record> records=new ArrayList<Record>();
    String[] cars=new String[]{"奥迪","宝马","福特","比亚迪","特斯拉"};
    String[] sales=new String[]{"张三","李四","王五","杰克","姜宇"};
    Random r=new Random();
    for (int i = 0; i < count; i++) {
      records.add(new Record(new Date(), sales[r.nextInt(5)],cars[r.nextInt(5)],r.nextInt(100),r.nextFloat()*10000+5000));
    }
    return records;
}