DeepSeek + 在线Excel ， 打造智能表格新纪元

// 自定义DeepSeek提问函数
var DeepSeek_Query = function () { };
DeepSeek_Query.prototype = new GC.Spread.CalcEngine.Functions.AsyncFunction('DeepSeek.QUERY', 1, 1, {
    description: "向GPT提问，直接返回结果",
    parameters: [
        {
            name: "问题"
        }]
});
DeepSeek_Query.prototype.defaultValue = function () { return 'Loading...'; };
DeepSeek_Query.prototype.evaluateAsync = function (context, arg) {
    if (!arg) {
        return GC.Spread.CalcEngine.Errors.NotAvailable;
    }

    const response = openai.chat.completions.create({
        model: modelInfo.model,
        messages: [
            { role: "system", content: "You are a helpful excel assistant. " },
            { role: "user", content: arg + "，？只返回结果。" }
        ],
    });
    response.then(function (completion) {
        let desc = completion.choices[0].message.content;
        context.setAsyncResult(desc);
    });
};
GC.Spread.CalcEngine.Functions.defineGlobalCustomFunction("DeepSeek.QUERY", new DeepSeek_Query());

let formulaAnalyze = {
    "title":"智能公式分析",
    "text":"公式分析",
    "iconClass":"ribbon-button-formulaAnalyze",
    "bigButton":"=ribbonHeight>toolbarHeight",
    "commandName":"formulaAnalyze",
    execute: function(designer){
        let spread = designer.getWorkbook(),sheet = spread.getActiveSheet();
        let formula = sheet.getFormula(sheet.getActiveRowIndex(), sheet.getActiveColumnIndex());
        if(formula){           
            let loading = ElLoading.service({ lock: true, text: "Loading", background: "rgba(0, 0, 0, 0.7)"});
            const response = openai.chat.completions.create({
                model: modelInfo.model,
                messages: [
                    { role: "system", content: "You are a helpful assistant. 直接告诉我公式的意义，不用计算结果，答复里不能重复问题。" },
                    { role: "user", content: formula + "，这个公式有什么意义？" }
                ],
            });
            response.then(function(completion){
                loading.close();
                let desc = completion.choices[0].message.content;
                GC.Spread.Sheets.Designer.showMessageBox(desc, "", GC.Spread.Sheets.Designer.MessageBoxIcon.info)
            }).catch(function(){loading.close()});
        }
        else{
            GC.Spread.Sheets.Designer.showMessageBox("单元格没有公式", "提醒", GC.Spread.Sheets.Designer.MessageBoxIcon.warning)
        }
    }
}

        let messages = [{"role": "system","content": "你是一个数据透视表分析助手。"},
            {
                "role": "user", 
                "content": 
`根据表格标题内容和需求描述推荐创建数据透视表需要的行、列和值字段。
表格标题为：
---
${headerList}
---
需求描述：
---
${bindingData.description}
---`
            }];
        let functions = [{"type": "function", 
            "function":{
            "name": "pivot_talbe_analyze",
            "description": "对数据创建数据透视表，返回数据透视表结果",
            "parameters": {
                "type": "object",
                "properties": {
                    "rowFieldName": {
                        "type": "string",
                        "description": "行字段名称"
                    },
                    "columnFieldName": {
                        "type": "string",
                        "description": "列段名称"
                    },
                    "dataFieldName": {
                        "type": "string",
                        "description": "值字段名称"
                    },
                },
                "required": ["rowFieldName", "dataFieldName"]
            },
            "strict": true
        }}]
        try {
            var completion = await openai.chat.completions.create({
                "model": "qwen-plus",
                "messages": messages,
                "tools": functions,
                "function_call": {"name": "pivot_talbe_analyze"}
            });
            if(completion.choices[0].message.tool_calls){
                let args = JSON.parse(completion.choices[0].message.tool_calls[0].function.arguments);
                spread.suspendPaint();
                let activeSheetIndex = spread.getActiveSheetIndex();
                spread.addSheet(activeSheetIndex);
                spread.setActiveSheetIndex(activeSheetIndex);
                let newSheet = spread.getSheet(activeSheetIndex);
                let pivotTable = newSheet.pivotTables.add(getUniquePivotName(newSheet), pivotRange, 2, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2);
                pivotTable.add(args.rowFieldName, args.rowFieldName, GC.Spread.Pivot.PivotTableFieldType.rowField);
                if(args.columnFieldName){
                    pivotTable.add(args.columnFieldName, args.columnFieldName, GC.Spread.Pivot.PivotTableFieldType.columnField);
                }
                pivotTable.add(args.dataFieldName, "求和项：" + args.dataFieldName, GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);

                spread.resumePaint();
            }
        }
        catch(err){
            console.log(err)
        }
        finally{
        }