示例说明
我们对电子表格报表进行回写时，经常会遇到如下需求。报表中不同单元格之间是有勾稽关系的，比如A2 = B3 + B4，或者A4 >= B4 + SUM(C2:C10)，在保存的时候需要做数据的平衡性校验。类似上述需求，皆可以参照如下方法进行设置。
若是根据含计算公式的列进行校验，将列隐藏之后，校验不生效！
参考示例：
设置方法
1、首先下载“宏代码库”资源并导入到 Smartbi 中（migrate-工具宏.xml），该资源定义了一些“宏代码库”模块，报表数据校验方法中需要引用它们。资源导入方法：系统运维 > 导入资源。
2、接着在电子表格设计器（Microsoft Office Excel）中，创建电子表格报表。
3、在浏览器的“分析展现”节点下，选中电子表格，右键选择 编辑宏 进入报表宏界面。
4、在报表宏界面新建客户端模块。在弹出的新建模块对话框中，选择对象为spreadSheetReport、事件为onRenderReport、并把下面宏代码复制到代码编辑区域。
宏类型
类型 | 对象 | 事件 |
|---|---|---|
ClientSide | spreadsheetReport | onRenderReport |
宏代码
//引入资源包
var JMClientUtils = use("system.utils.JMClientUtils");
var SpreadsheetClientUtils = use("exts.utils.SpreadsheetClientUtils");
var SpreadsheetVerifyData = use("exts.utils.SpreadsheetVerifyData");
function main(spreadsheetReport) {
//debugger;
// 添加"数据校验"按钮
SpreadsheetVerifyData.addVerifyReportDataButton(spreadsheetReport, doVerifyReportDataBalanceCallback);
// 对"保存"方法重载，只有校验通过后才可以保存
var writeBack = spreadsheetReport.spreadsheetReportWriteBack;
writeBack.removeListener(writeBack.elem_btnSave, "click", writeBack.doSaveClick, writeBack);
writeBack.addListener(writeBack.elem_btnSave, "click",
function(e) {
//debugger;
if (doVerifyReportDataBalanceCallback(e, this)) {
this.doSaveClick(e);
}
},
writeBack);
}
// 执行数据校验工作，校验通过返回true，否则返回false
function doVerifyReportDataBalanceCallback(e, writeBack) {
var isSucceeded = true;
var showMessage = "";
var spreadsheetReport = this;
if (writeBack) {
spreadsheetReport = writeBack.spreadsheetReport;
}
// 规则1：D8 = D6 + D7
var D8 = SpreadsheetClientUtils.getCellRealValue("D8", spreadsheetReport);
var D6D7 = SpreadsheetClientUtils.getCellRealValue(["D6:D7"], spreadsheetReport);
isSucceeded = (D8 - D6D7) < 0.001;
showMessage = "规则1：D8 = D6 + D7，校验失败。\n" + [D8, " = ", D6D7].join("");
SpreadsheetClientUtils.showVerifyDataResult(spreadsheetReport, showMessage, ["D8", "D6:D7"], isSucceeded, "#ff5478");
if (!isSucceeded) {
return false;
}
// 规则2：D12 = D9 + D10 + D11
var D12 = SpreadsheetClientUtils.getCellRealValue("D12", spreadsheetReport);
var D9D10D11 = SpreadsheetClientUtils.getCellRealValue(["D9", "D10:D11"], spreadsheetReport);
isSucceeded = (D12 - D9D10D11) < 0.001;
showMessage = "规则2：D12 = D9 + D10 + D11，校验失败。\n" + [D12, " = ", D9D10D11].join("");
SpreadsheetClientUtils.showVerifyDataResult(spreadsheetReport, showMessage, ["D12", "D9:D10", "D11"], isSucceeded, "#ff5478");
if (!isSucceeded) {
return false;
}
return isSucceeded;
}
关键代码解释
- 通过SpreadsheetVerifyData.addVerifyReportDataButton方法，向工具栏上添加一“数据校验”按钮。
// 添加"数据校验"按钮
SpreadsheetVerifyData.addVerifyReportDataButton(spreadsheetReport, doVerifyReportDataBalanceCallback);
- 对“保存”方法重载，只有校验通过后才可以保存。首先取消系统默认的事件处理函数，再绑定自定义的函数。
// 对"保存"方法重载，只有校验通过后才可以保存
var writeBack = spreadsheetReport.spreadsheetReportWriteBack;
writeBack.removeListener(writeBack.elem_btnSave, "click", writeBack.doSaveClick, writeBack);
writeBack.addListener(writeBack.elem_btnSave, "click",
function(e) {
//debugger;
if (doVerifyReportDataBalanceCallback(e, this)) {
this.doSaveClick(e);
}
},
writeBack);
- 如下一段代码定义一条校验规则。首先利用 SpreadsheetClientUtils.getCellRealValue方法（该方法只支持获取数值类型）获取指定单元格或区域的数据，做平衡性判断，然后再调用 SpreadsheetClientUtils.showVerifyDataResult方法显示校验结果：失败了弹出提示对话框，并将所有单元格背景色高亮。
// 规则1：D8 = D6 + D7
var D8 = SpreadsheetClientUtils.getCellRealValue("D8", spreadsheetReport);
var D6D7 = SpreadsheetClientUtils.getCellRealValue(["D6:D7"], spreadsheetReport);
isSucceeded = (D8 == D6D7);
showMessage = "规则1：D8 = D6 + D7，校验失败。\n" + [D8, " = ", D6D7].join("");
SpreadsheetClientUtils.showVerifyDataResult(spreadsheetReport, showMessage, ["D8", "D6:D7"], isSucceeded, "#ff5478");
if (!isSucceeded) {
return false;
}
示例资源：保存时进行数据校验.xml
相关SQL：writeback.sql
