Method parse_function

FormulaDump/mini_formula_evaluator.cpp:181–262

}

QVariant mini_formula_evaluator_t::parse_function(const QString& base_sheet, const QString& func)
{
    static const QRegularExpression re(R"(^([A-Za-z_][A-Za-z0-9_]*)\((.*)\)$)");
    const auto m = re.match(func.trimmed());
    if (!m.hasMatch()) return {};

    const QString name = m.captured(1).toUpper();
    const QString args_str = m.captured(2).trimmed();
    const QStringList args = split_args_top_level(args_str);

    auto eval_arg_number = [&](const QString& a) -> double {
        const QVariant v = eval(base_sheet, a);
        return to_double_or_nan(v);
    };

    if (name == "SUM" || name == "AVERAGE" || name == "MIN" || name == "MAX") {
        double acc = 0.0;
        double mn = std::numeric_limits<double>::infinity();
        double mx = -std::numeric_limits<double>::infinity();
        int cnt = 0;

        auto consume_value = [&](double d) {
            if (std::isnan(d)) return;
            acc += d;
            mn = std::min(mn, d);
            mx = std::max(mx, d);
            ++cnt;
        };

        for (const QString& a0 : args) {
            const QString a = a0.trimmed();

            // range: A1:B3 or Sheet1!A1:B3
            static const QRegularExpression re_sheet(R"(^([^!]+)!(.+)$)");
            static const QRegularExpression re_range(R"(^(\$?[A-Za-z]+\$?\d+):(\$?[A-Za-z]+\$?\d+)$)");

            QString sheet = base_sheet;
            QString range = a;

            const auto ms = re_sheet.match(a);
            if (ms.hasMatch()) {
                sheet = ms.captured(1).trimmed();
                range = ms.captured(2).trimmed();
            }

            const auto mr = re_range.match(range);
            if (mr.hasMatch()) {
                int r1=0,c1=0,r2=0,c2=0;
                if (!try_parse_cell_ref(mr.captured(1), r1, c1)) continue;
                if (!try_parse_cell_ref(mr.captured(2), r2, c2)) continue;
                if (r1 > r2) std::swap(r1, r2);
                if (c1 > c2) std::swap(c1, c2);
                for (int r = r1; r <= r2; ++r) {
                    for (int c = c1; c <= c2; ++c) {
                        consume_value(to_double_or_nan(eval_cell(sheet, r, c)));
                    }
                }
                continue;