概述
本 Demo 展示了 AI 辅助公式编辑器面板的完整实现,包括公式生成和公式解释两个核心功能。Demo 中预先设置了包含销售数据的表格和三个示例公式(成绩等级判断、众数查找、GUID 生成),用户可以选中这些公式单元格后通过 AI 助手生成新的公式或查看公式解释。
实现思路
创建 SpreadJS 工作簿实例,根据运行环境选择是否加载设计器模式
通过 injectAI() 方法注入 AI 服务回调函数,配置服务器端点
初始化演示数据,创建包含销售数据的表格和示例公式
创建公式编辑器面板实例,通过 attach() 方法绑定到工作簿
监听单元格切换事件,实时更新当前编辑位置的显示
代码解析
注入 AI 服务配置
这段代码通过 injectAI() 方法将 AI 服务注入到工作簿中。serverCallback 函数负责将请求发送到后端服务器,后端服务器再与 AI 服务通信。这种方式可以避免在前端暴露 API 密钥,提高安全性。当服务器繁忙时(状态码 429),会提示用户稍后重试。
创建公式编辑器面板
创建公式编辑器实例时需要传入 DOM 宿主元素,然后通过 attach() 方法将其绑定到工作簿。绑定后,编辑器会自动同步当前选中单元格的公式内容。
保存公式到单元格
用户在公式编辑器中修改公式后,点击保存按钮时执行 commitContentToActiveCell 命令,将编辑器中的内容提交到当前活动单元格。
监听单元格切换
通过绑定 ActiveCellChanged 事件,当用户切换单元格时更新位置显示(如 "Sheet1>B1"),方便用户了解当前正在编辑哪个单元格的公式。
初始化演示数据
Demo 中预设了三个示例公式单元格,用于展示 AI 的公式解释功能。第一个示例使用 LET 函数和嵌套 IF 实现成绩等级判断,展示了复杂公式的解释效果。
运行效果
运行后在工作表中看到预设的销售数据表格和三个示例公式单元格
点击任意包含公式的单元格,公式编辑器面板会自动显示该公式
在公式编辑器中输入自然语言描述(如"计算销售额总和"),AI 会生成对应的公式建议
选中公式后点击"解释公式"按钮,AI 会逐步解释公式的逻辑和各部分的作用
所有 AI 功能都通过后端服务器代理,确保 API 密钥安全
API 参考
injectAI 方法
callbackOrEnv:AI 服务配置对象或回调函数
回调函数签名:async (requestBody) => Response
接收 SpreadJS 构建的请求体,返回 fetch Response 对象
FormulaEditor 类
host:DOM 元素或元素 ID,作为编辑器的容器
options:可选配置项
formatWidthLimit:公式宽度限制(-1 表示自动换行)
tabSize:Tab 键缩进空格数
attach 方法
workbook:要绑定的 Workbook 实例
execute 方法
cmd:命令名称(必需)
其他参数根据具体命令而定
let designer, spread;
window.onload = function () {
const spread = createSpreadAndDesigner();
injectAI(spread);
initData(spread);
};
function initData(spread) {
spread.suspendPaint();
spread.options.allowDynamicArray = true;
var sheet = spread.sheets[0];
sheet.setValue(0, 0, "Grade");
sheet.setValue(0, 6, 72);
sheet.setFormula(
0,
1,
'=LET(score, G1, IF(score >= 90, "A", IF(score >= 80, "B", IF(score >= 70, "C", IF(score >= 60, "D", "F")))))'
);
sheet.setValue(1, 0, "Most Frequent");
sheet.setArray(1, 6, [[1, 2, 6, 6, 6, 5]]);
sheet.setFormula(
1,
1,
'=LET(data, G2:L2, unique_data, UNIQUE(data), count_data, COUNTIF(data, unique_data), max_count, MAX(count_data), most_frequent, INDEX(unique_data, MATCH(max_count, count_data, 0)), IF(max_count > 1, most_frequent, ""))'
);
sheet.setValue(2, 0, "GUID");
sheet.setFormula(
2,
1,
'=CONCATENATE(DEC2HEX(RANDBETWEEN(0, 4294967295), 8), "-", DEC2HEX(RANDBETWEEN(0, 65535), 4), "-", DEC2HEX(RANDBETWEEN(16384, 20479), 4), "-", DEC2HEX(RANDBETWEEN(32768, 49151), 4), "-", DEC2HEX(RANDBETWEEN(0, 65535), 4), DEC2HEX(RANDBETWEEN(0, 4294967295), 8))'
);
sheet.setActiveCell(0, 1);
const showDesigner = top === window;
if (!showDesigner) {
document.getElementById("location").innerText = "Sheet1>B1";
}
sheet.setColumnWidth(0, 100);
const dataSource = [
["name", "product", "date", "amount", "price", "sales"],
["chris", "desk", new Date("2020-10-08T16:00:00.000Z"), 5, 199, 995],
["radow", "pen", new Date("2020-09-15T16:00:00.000Z"), 2, 5, 10],
["peyton", "pencil", new Date("2021-06-22T16:00:00.000Z"), 6, 1.5, 9],
["johnson", "chair", new Date("2021-07-19T16:00:00.000Z"), 7, 68, 476],
["vic", "notebook", new Date("2021-01-13T16:00:00.000Z"), 7, 3.2, 22.4],
["lan", "desk", new Date("2021-03-12T16:00:00.000Z"), 9, 199, 1791],
["chris", "pen", new Date("2021-03-06T16:00:00.000Z"), 4, 5, 20],
["chris", "pencil", new Date("2020-09-02T16:00:00.000Z"), 10, 1.5, 15],
["radow", "chair", new Date("2020-08-09T16:00:00.000Z"), 3, 68, 204],
["peyton", "notebook", new Date("2021-02-08T16:00:00.000Z"), 9, 3.2, 28.8],
["johnson", "desk", new Date("2021-07-03T16:00:00.000Z"), 7, 199, 1393],
["vic", "pen", new Date("2021-06-27T16:00:00.000Z"), 8, 5, 40],
["lan", "pencil", new Date("2020-10-10T16:00:00.000Z"), 2, 1.5, 3],
["chris", "chair", new Date("2021-03-04T16:00:00.000Z"), 2, 68, 136],
["chris", "notebook", new Date("2021-02-21T16:00:00.000Z"), 11, 3.2, 35.2],
["radow", "desk", new Date("2021-06-03T16:00:00.000Z"), 6, 199, 1194],
];
sheet.setArray(5, 0, dataSource);
sheet.tables.add("table1", 5, 0, 17, 6);
spread.resumePaint();
}
function createSpreadAndDesigner() {
const demoHost = document.getElementById("demo-host");
if (window !== top) {
const spread = new GC.Spread.Sheets.Workbook(demoHost, { sheetCount: 1 });
const formulaEditor = new GC.Spread.Sheets.FormulaPanel.FormulaEditor(
document.getElementById("editor")
);
formulaEditor.attach(spread);
const save_btn = document.getElementById("save");
save_btn.addEventListener("click", () => {
formulaEditor
.commandManager()
.execute({ cmd: "commitContentToActiveCell" });
});
const location = document.getElementById("location");
spread.bind("ActiveCellChanged", (eventType, args) => {
location.innerText = `${spread.getActiveSheet().name()}>${toColumnIndex(
args.col
)}${args.row + 1}`;
});
return spread;
} else {
const designer = new GC.Spread.Sheets.Designer.Designer(
demoHost,
undefined,
undefined,
{ sheetCount: 1 }
);
const panel = document.getElementById("panel");
panel.style.display = "none";
designer.setData("formulaEditorPanel_Visible", true);
return designer.getWorkbook();
}
}
function toColumnIndex(index) {
let column = "";
while (index >= 0) {
column = String.fromCharCode((index % 26) + 65) + column;
index = Math.floor(index / 26) - 1;
}
return column;
}
function injectAI(spread) {
const serverCallback = async (requestBody) => {
const response = await fetch(getAIApiUrl(), {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify(requestBody)
});
if (response.status === 429) {
alert('The server is busy, please try again later.');
return;
}
if (!response.ok) {
throw new Error(`HTTP error! status: ${response.status}`);
}
return response;
};
spread.injectAI(serverCallback);
}
function getAIApiUrl() {
return (
window.location.href.match(/http.+spreadjs\/SpreadJSTutorial\//)[0] + 'server/api/queryAI'
);
}
<!DOCTYPE html>
<html style="height: 100%; font-size: 14px">
<head>
<meta charset="utf-8" />
<meta name="spreadjs culture" content="zh-cn" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link
rel="stylesheet"
type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css"
/>
<link
rel="stylesheet"
type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer/styles/gc.spread.sheets.designer.light.min.css"
/>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"
></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-formula-panel/dist/gc.spread.sheets.formulapanel.min.js"
type="text/javascript"
></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-ai-addon/dist/gc.spread.sheets.ai.min.js"
type="text/javascript"
></script>
<script
src="$DEMOROOT$/spread/source/js/license.js"
type="text/javascript"
></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js" type="text/javascript"></script>
<script>
const designerDependencyScripts = [
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-barcode/dist/gc.spread.sheets.barcode.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-print/dist/gc.spread.sheets.print.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pdf/dist/gc.spread.sheets.pdf.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-ganttsheet/dist/gc.spread.sheets.ganttsheet.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-reportsheet-addon/dist/gc.spread.report.reportsheet.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-io/dist/gc.spread.sheets.io.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer-resources-cn/dist/gc.spread.sheets.designer.resource.cn.min.js",
"$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-designer/dist/gc.spread.sheets.designer.all.min.js",
"$DEMOROOT$/spread/source/js/designer/license.js",
];
function appendScriptNode(src) {
const script = document.createElement("script");
script.src = src;
script.async = false;
script.type = "text/javascript";
document.head.appendChild(script);
}
if (top === window) {
// not in iframe
designerDependencyScripts.forEach(appendScriptNode);
}
</script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<div class="sample-tutorial">
<div id="demo-host"></div>
<div id="panel">
<div id="header">
<div id="save"></div>
<div id="location"></div>
</div>
<div id="editor"></div>
</div>
</div>
</body>
</html>
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
display: flex;
flex-direction: row;
}
#demo-host {
width: 69vw;
height: 100%;
flex-grow: 1;
}
#panel {
margin-left: 10px;
width: 30vw;
display: flex;
flex-direction: column;
}
#header {
display: flex;
flex-direction: row;
justify-content: flex-start;
align-items: center;
}
#save {
margin-left: 7px;
height: 30px;
width: 30px;
cursor: pointer;
background-position: center;
background-repeat: no-repeat;
background-image: url(data:image/svg+xml;base64,PHN2ZyB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciIHdpZHRoPSIyNiIgaGVpZ2h0PSIyNiI+PGcgZmlsbD0iIzY5Njk2OSIgZmlsbC1ydWxlPSJub256ZXJvIj48cGF0aCBzdHJva2U9IiM2NjYiIHN0cm9rZS13aWR0aD0iLjciIGQ9Ik0xMC44NzIgMnEuNTgyIDAgLjkzNS4yOS4zNTUuMjg4LjM1NC42MjQtLjAwMS4yNy0uMTc2LjQ0N2EuNjIuNjIgMCAwIDEtLjQ1NC4xNzcuNTcuNTcgMCAwIDEtLjM5Mi0uMTM0LjQyLjQyIDAgMCAxLS4xNTMtLjMyM3EwLS4xMi4xMDEtLjI5OS4xLS4xNzUuMS0uMjMzYS4yLjIgMCAwIDAtLjA2Ni0uMTU4cS0uMDk2LS4wNzUtLjI4Ny0uMDc1LS40ODggMC0uODcuMjk5LS41MTUuNDAxLS45MjYgMS4yNS0uMjEuNDM4LS43NjQgMi4yMDFoMS4xMzdsLS4xNDQuNDk2SDguMTVMNy4zIDkuODQ0cS0uNTYzIDIuMTY0LTEuMTI3IDMuMzU4LS44MSAxLjY3LTEuNzM4IDIuMzAzUTMuNzMgMTYgMy4wMjIgMTZxLS40NjggMC0uNzgzLS4yNzFBLjU5LjU5IDAgMCAxIDIgMTUuMjQ1cTAtLjI0MS4xOTYtLjQyMWEuNjguNjggMCAwIDEgLjQ3My0uMTc2cS4yMSAwIC4zNTMuMTI2YS4zOC4zOCAwIDAgMSAuMTQzLjI5NHEwIC4xNjYtLjE2Mi4zMDgtLjEyNC4xMDEtLjEyNC4xNTggMCAuMDY1LjA1Ny4xMDIuMDY4LjA1Ny4yMS4wNTcuMzM0IDAgLjY5Ny0uMjA2LjM2My0uMjA1LjY1LS42MDYuMjg1LS40MDIuNTQ0LTEuMTU2LjEwNS0uMzE4LjU3My0yLjA4MWwxLjMzNy01LjA4Mkg1LjYxbC4xMDUtLjQ5NXEuNjQgMCAuODkzLS4wODkuMjUzLS4wODguNDYzLS4zMzIuMjEtLjI0MS41NTQtLjkxNC40NDktLjg5NC44NjktMS4zODkuNTYzLS42NzEgMS4xOTQtMS4wMDguNjMtLjMzNiAxLjE4NC0uMzM1Wm00LjQxMSAzLjg4MXEuMzI2IDAgLjUyMS4xNzYuMTk2LjE3Ny4xOTYuNDE5IDAgLjI5LS4xMTQuMzkzYS43MS43MSAwIDAgMS0uNDg3LjE4NnEtLjE2NC4wMDItLjM1NC0uMDY2LS4zNTMtLjEyLS40NzgtLjEyLS4xOSAwLS40MzkuMjA0LS40NzguMzk0LTEuMTM3IDEuNTFsLjYzMiAyLjU4NnEuMTQzLjU5Ny4yNDMuNzEyLjEuMTE5LjE5NS4xMTguMTYyIDAgLjM4My0uMTc2YTMuMSAzLjEgMCAwIDAgLjcyNi0uODk2bC4yNjcuMTI5YTQuOCA0LjggMCAwIDEtMS4yMzMgMS40NzRxLS40Mi4zMjYtLjcxNS4zMjYtLjQzLjAwMS0uNjg4LS40NjYtLjE2Mi0uMjk3LS42Ny0yLjUyOC0xLjE5MyAyLjAzNS0xLjkxOCAyLjYyLS40NjguMzc0LS45MDcuMzc0YS44Ni44NiAwIDAgMS0uNTY0LS4yMTUuNTguNTggMCAwIDEtLjE4Mi0uNDQ4LjU2OC41NjggMCAwIDEgLjU4My0uNTc3cS4yNDggMCAuNTI2LjI0My4yLjE3Ni4zMDUuMTc2LjA5NSAwIC4yMzktLjEyLjM2My0uMjkuOTgzLTEuMjM3LjYyLS45NDYuODEzLTEuMzY2YTEwMCAxMDAgMCAwIDAtLjUwNy0xLjkzMmMtLjEyMi0uMzItLjI3Ny0uNTUyLS40NjgtLjY4NXEtLjI4Ny0uMi0uODMxLS4xOTktLjE4MSAwLS40MS4wMDl2LS4yNzFsMi4wNTQtLjM1M3EuMzcuMzkxLjU2My43ODMuMTQyLjI3LjQ1OCAxLjQxOGwuNjc3LS45ODlxLjI2OS0uMzY0LjY1LS42OS4zODItLjMyOC42NzktLjQzOGEuOTYuOTYgMCAwIDEgLjQxLS4wODRaIi8+PHBhdGggZD0ibTExLjcxMiAyNS4yNi0uNjE3LS42MDdMNCAxOC43MjZsMS40NDItMS40ODYgNi4yNyA1LjE1NUwyMy42NiAxMCAyNSAxMS4yMzYgMTIuMzAxIDI0LjY1MyIvPjwvZz48L3N2Zz4=);
}
#save:hover {
transform: scale(1.1);
}
#location {
margin-left: 12px;
line-height: 30px;
font-size: 18px;
}
#editor {
height: calc(100% - 30px);
}
