页面历史

function main(spreadsheetReport) {
    // 执行sql 参考：https://wiki.smartbi.com.cn/pages/viewpage.action?smt_poid=43&pageId=119277253
    // 参数变量需要用 \\ 转义
    var sql = "select count(Freight) from orders where ShipRegion = \\'paramValue1\\' and ShipProvince = \\'paramValue2\\' ";
    // 维护参数在sql中的映射关系
    var param = [
        {name:"地区",sqlName: "ShipRegion", paramInSql: "paramValue1"},
        {name:"省份",sqlName: "ShipProvince", paramInSql: "paramValue2"},
    ]
    for(var i = 0; i< param.length;i++){
        // 获取参数
        var value = spreadsheetReport.getParameterValue(param[i].name);
        logger.debug("参数" + param[i].name + "的值为:" + value)
        // 参数判空，防止拼接空字符串
        if(value == '') {
            sql = sql.replace(param[i].sqlName, "\\'1\\'")
            sql = sql.replace("\\'" + param[i].paramInSql + "\\'", "1")
            continue;
        }
        sql = sql.replace(param[i].paramInSql, value)
    }
    // 执行获取总行结果
    var res = getSQLResult(sql);
    // 单元格赋值
    setCellValueByValue(spreadsheetReport,res)

}

function getSQLResult(executeSQL) {
    logger.info("执行sql---->" + executeSQL)
    var ret = spreadsheetReport.remoteInvoke("DataSourceService", "executeNoCacheable", "['DS.northwind_lck','"+ executeSQL +"']");
    var rets = eval('(' + ret + ')');
    logger.info("查询数据行数=" + rets.result.data.length);
    logger.info("sql获得的sql结果为=" + rets.result.data[0][0].displayValue);
    // 返回前两个值
    return rets.result.data[0][0].displayValue;
}

function setCellValueByValue(spreadsheetReport,value) {
    spreadsheetReport.cacheable = false;
    //获取电子表格工作簿
    var workbook = spreadsheetReport.workbook; 
    // 获得sheet1
    var worksheet = workbook.worksheets.get(0);
    // 获取单元格对象
    var cells = worksheet.cells;
    //表格最大范围 行：maxDisplayRange.rowCount 列：maxDisplayRange.colCount
    var maxDisplayRange = cells.maxDisplayRange;
    //找到对应的单元格赋值，行列
    var cell = cells.get(maxDisplayRange.rowCount - 1, 3);
    cell.value = value
}