服务端宏：电子表格清单报表使用sum函数计算超1w行数据

var Pattern = Packages.java.util.regex.Pattern;
var Matcher = Packages.java.util.regex.Matcher;
var StringBuffer = Packages.java.lang.StringBuffer;
var StringBuilder = Packages.java.lang.StringBuilder;
var Integer = Packages.java.lang.Integer;
var JSONObject = Packages.smartbi.net.sf.json.JSONObject;

/**
 * 只支持单个sum公式，同时sum公式的内容为单元格位置，不能存在其它运算
 * eg:=SUM(A11) or SUM($A11)
 */
function main(spreadsheetReport) {
    spreadsheetReport.cacheable = false;
    var type = spreadsheetReport.outputType;
    // only the first executeReport need change the formula
    var operation = spreadsheetReport.operation;
    var sheetListPages = JSONObject.fromString(operation);
    var listPage = sheetListPages.optJSONArray("sheetListPages");
    if (type == "LIST_EXCEL" && listPage == null) {
        var workbook = spreadsheetReport.workbook;
        var worksheet = workbook.worksheets.get(0);
        var cells = worksheet.cells;
        var maxDisplayRange = cells.maxDisplayRange;
        for (var i = 0; i < maxDisplayRange.rowCount; i++) {
            for (var j = 0; j < maxDisplayRange.columnCount; j++) {
                var cell = cells.get(i, j);
                if (!cell.isFormula()) {
                    continue;
                }
                var formula = cell.getFormula();
                var index = formula.indexOf("SUM");
                if (index < 0) {
                    continue;
                }
                var replaceStrList = getReplaceStr(formula, index);
                var sb = new StringBuilder(formula);
                while (replaceStrList.length != 0) {
                    var str = replaceStrList.pop();
                    sb.replace(str[0], str[1], str[2]);
                }
                var newFormula = sb.toString();
                // logger.info("newFormula " + newFormula);
                cell.setFormula(newFormula);
            }
        }
    }
}

function getSumEndPosition(formula, start) {
    var i = start;
    var stack = ["("];
    while (i < formula.length() && stack.length != 0) {
        if (formula.charAt(i) == 40) {
            stack.push(formula.charAt(i));
        } else if (formula.charAt(i) == 41) {
            stack.pop();
        }
        i++;
    }
    return i;
}

function getReplaceStr(formula, start) {
    var position = [];
    while (start >= 0) {
        var end = getSumEndPosition(formula, start + 4); // 获取到sum公式对应内容所在的位置
        var sumFormula = formula.substring(start, end);
        var pattern = Pattern.compile("(\\$*[A-Za-z]+)([0-9]+)"); // 匹配$A11和A11两种形式
        var matcher = pattern.matcher(sumFormula);
        var sb = new StringBuffer();
        while (matcher.find()) {
            var letter = matcher.group(1);
            var number = Integer.valueOf(matcher.group(2)).intValue();
            var str = "OFFSET(" + "\\" + letter + (number + 1) + ", -1, 0)";
            matcher.appendReplacement(sb, str);
        }
        matcher.appendTail(sb);
        position.push([start, end, sb.toString()]);
        start = formula.indexOf("SUM", end);
    }
    return position;
}