示例说明

我们对电子表格报表进行回写时，经常会遇到如下需求。报表中不同单元格之间是有勾稽关系的，比如A2 = B3 + B4，或者A4 >= B4 + SUM(C2:C10)，在保存的时候需要做数据的平衡性校验。类似上述需求，皆可以参照如下方法进行设置。

参考示例：

设置方法

1、首先下载“工具宏”资源并导入到 Smartbi 中（下载地址），该资源定义了一些“宏代码库”模块，报表数据校验方法中需要引用它们。资源导入方法：定制管理 -> 系统运维 -> 导入资源。

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) {
    // debugger;
    var isSucceeded = true;
    var showMessage = "";
    var spreadsheetReport = this;
    if (writeBack) {
        spreadsheetReport = writeBack.spreadsheetReport;
    }

    // 规则1：E4 = E5 + E6
    var E4 = SpreadsheetClientUtils.getCellRealValue("E4", spreadsheetReport);
    var E5E6 = SpreadsheetClientUtils.getCellRealValue(["E5:E6"], spreadsheetReport);
    isSucceeded = (E4 == E5E6);
    showMessage = "规则1：E4 = E5 + E6，校验失败。\n" + [E4, " = ", E5E6].join("");
    SpreadsheetClientUtils.showVerifyDataResult(spreadsheetReport, showMessage, ["E4", "E5:E6"], isSucceeded);
    if (!isSucceeded) {
        return false;
    }

    // 规则2：E9 = E10 + E11 + E12
    var E9 = SpreadsheetClientUtils.getCellRealValue("E9", spreadsheetReport);
    var E10E11E12 = SpreadsheetClientUtils.getCellRealValue(["E10", "E11:E12"], spreadsheetReport);
    isSucceeded = (E9 == E10E11E12);
    showMessage = "规则2：E9 = E10 + E11 + E12，校验失败。\n" + [E9, " = ", E10E11E12].join("");
    SpreadsheetClientUtils.showVerifyDataResult(spreadsheetReport, showMessage, ["E9", "E10:E11", "E12"], isSucceeded, "green");
    if (!isSucceeded) {
        return false;
    }

    return isSucceeded;
};


关键代码解释

    // 添加"数据校验"按钮
    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);

 

    // 规则1：E4 = E5 + E6
    var E4 = SpreadsheetClientUtils.getCellRealValue("E4", spreadsheetReport);
    var E5E6 = SpreadsheetClientUtils.getCellRealValue(["E5:E6"], spreadsheetReport);
    isSucceeded = (E4 == E5E6);
    showMessage = "规则1：E4 = E5 + E6，校验失败。\n" + [E4, " = ", E5E6].join("");
    SpreadsheetClientUtils.showVerifyDataResult(spreadsheetReport, showMessage, ["E4", "E5:E6"], isSucceeded);
    if (!isSucceeded) {
        return false;
    }