网页中通过 Javascript 实现类似 Excel 的公式解析计算功能

   1. 目标

     Excel中，公式形如"A1+B1-C1*D1"，在网页中将表格单元格也按照Excel方式定位(字母A~Z代表列，数字1-n代表行，字母+数字定位单元格)，然后在单元格中保存计算公式，当输入单元格数值变化时触发公式计算。

   2. 难点

    1). 获取公式中的变量；2).计算公式；3).javascript浮点数计算的精度问题（1.4->1.4000000000000001)。

   3.垃圾CalcEval.js

    这个库自己做的公式解析（没有用语法树），存在不支持减号-，不能处理1e-7等等bug。

   4.正确姿势

    1).用正则表达式解析变量: var variables = formula.match(/[A-Za-z]+[1-9]+/g) //返回字符串数组.

    2).替换formula中变量名为数值后，eval(formula).toPrecision(12); 获得计算结果，toPrecision(12)用来去掉1.4后秒一大堆0和最后的1。

   5.代码说明

     代码是extjs6.2写的，公式计算重点在 cascadeCalc 函数中，其中级联计算的实现可供大家参考。

    

Ext.define('Arim.view.report.free.Grid', {
    extend: 'Ext.grid.Panel',
    alias: 'widget.freegrid',

    requires: [
        'Arim.model.report.free.Cell',
        'Arim.model.report.free.Column',
        'Arim.model.report.free.Table',
        'Arim.view.report.free.Cell'
    ],

    viewConfig: {
        stripeRows: true
    },
    cls: 'custom-grid',
    selType: 'cellmodel',
    columnLines: false,
    rowLines: false,
    autoRender: true,
    autoScroll: true,

    configMode: true,
    table: null,

    initComponent: function () {
        var me = this;
        me.callParent(arguments);

        me.openTable(me.table);
    },

    //@public 打开模板.
    openTable:function(table){
        var me = this;
        if (!table) {
            table = Ext.create('Arim.model.report.free.Table');
        }
        me.table = table;
        var defaultColumnWidth = table.DefaultColumnWidth;
        var gridwidth = defaultColumnWidth * table.ColumnCount + 50;
        var columns = table.Columns;
        var gridColumns = [];
        var fields = [];
        gridColumns.push({ xtype: 'rownumberer', width: 30 });
        for (var i = 0; i < table.ColumnCount; i++) {
            var columnname = me.getColumnName(i);
            var colwidth = defaultColumnWidth;
            if (columns) {
                Ext.Array.each(columns, function (column) {
                    if (column.Header == columnname) {
                        colwidth = column.Width;
                        gridwidth += colwidth - defaultColumnWidth;
                        return false;
                    }
                });
            }
            fields.push(columnname);
            gridColumns.push(me.getNewColumn(columnname, colwidth));
        }
        var data = [];
        for (var j = 0; j < table.RowCount; j++) {
            var rowdata = {};
            Ext.Array.each(fields, function (field) {
                rowdata[field] = "";
            });
            data.push(rowdata);
        }
        var cells = table.Cells;
        if (cells) {
            Ext.Array.each(cells, function (cell) {
                var column = "", row = "";
                for (var i=0;i<cell.Key.length;i++) {
                    var c = cell.Key.charAt(i);
                    if (c >= 'A' && c <= 'Z') {
                        column += c;
                    }
                    else {
                        row += c;
                    }
                }//从Key(A1)解析列A和行1
                var rowdata = data[Number(row) - 1];
                rowdata[column] = cell;
            });
        }
        var store = Ext.create('Ext.data.Store', {
            fields: fields,
            data: data
        });
        me.width = gridwidth;
        me.reconfigure(store, gridColumns);
    },

    listeners: {
        cellclick: function (view, td, cellIndex, record, tr, rowIndex, e, eOpts) {
            var me = view.ownerCt;
            if (me.configMode) {
                var field = me.getColumns()[cellIndex].dataIndex;
                var celldata = record.data[field];
                var cellmodel = Ext.create('Arim.model.report.free.Cell', celldata);
                cellmodel.set('Key', field + (rowIndex + 1));
                if (!me.cellwindow) {
                    var win = Ext.create('Ext.window.Window', {
                        height: 600,
                        width: 800,
                        title: '单元格设置',
                        closable: true,
                        closeAction: 'hide',
                        plain: true,
                        maximizable: true,
                        autoScroll: true,
                        modal: true,
                        layout: 'fit',
                        items: [{
                            xtype: 'freecell',
                            itemId: 'cellconfig'
                        }]
                    });
                    var config = win.down('#cellconfig');
                    var okBtn = config.down('#okbtn');
                    okBtn.on('click', function () {
                        record = config.getRecord();
                        config.updateRecord(record);
                        me.updateGridCell(record, config.cellcolumn, config.cellrow);
                    }, me);
                    me.cellwindow = win;
                }
                var form = me.cellwindow.down('#cellconfig');
                form.cellcolumn = field;//传递单元格的列
                form.cellrow = rowIndex;//传递单元格的行
                form.getForm().loadRecord(cellmodel);
                var router = form.down("#router");
                router.setValue(JSON.stringify(cellmodel.data.Router));
                me.cellwindow.show();
            }
        }//点击单元格时，根据configMode弹出配置框或绘制曲线.
    },

    //@public 设置表格的列数和行数，复制原表格中交集部分，并根据需要扩展列、行.
    setColRow: function (colcount, rowcount, defaultColumnWidth) {
        var me = this;
        var oldcolumns = me.getColumns();
        var oldcolcount = oldcolumns.length - 1;//-1序号列
        var mincolcount = oldcolcount;
        if (colcount < oldcolcount && colcount > 0) {
            mincolcount = colcount;
        }
        var gridwidth = 0;
        var gridColumns = [{ xtype: 'rownumberer', width: 30 }];
        var fields = [];
        for (var i = 1; i <= mincolcount; i++) {
            var column = oldcolumns[i];
            gridwidth += column.width;
            gridColumns.push(me.getNewColumn(column.dataIndex, column.width));
            fields.push(column.dataIndex);
        }
        for (var j = mincolcount; j < colcount; j++) {
            gridwidth += defaultColumnWidth;
            var colname = me.getColumnName(j);
            gridColumns.push(me.getNewColumn(colname, defaultColumnWidth));
            fields.push(colname);
        }//新增的列
        gridwidth = gridwidth + 50;

        var store = me.store;
        var minrowcount = store.count();
        if (rowcount < minrowcount && rowcount > 0) {
            minrowcount = rowcount;
        }
        //复制原有数据
        var data = [];
        for (var i = 0; i < minrowcount; i++) {
            var rowdata = {};
            var record = store.getAt(i);
            Ext.Array.each(fields, function (field) {
                var oldvalue = record.get(field);
                rowdata[field] = oldvalue ? oldvalue : "";
            });
            data.push(rowdata);
        }
        //新增的行.
        for (var j = minrowcount; j < rowcount; j++) {
            var rowdata = {};
            Ext.Array.each(fields, function (field) {
                rowdata[field] = "";
            });
            data.push(rowdata);
        }
        var newstore = Ext.create('Ext.data.Store', {
            fields: fields,
            data: data
        });
        me.setWidth(gridwidth);
        me.reconfigure(newstore, gridColumns);
    },

    //@public 将表格的变更保存到@table参数.
    acceptChanges: function () {
        var me = this;
        var table = me.table;
        var tocolumns = [];
        //记录列
        var columns = me.getColumns();
        for (var i = 1; i < columns.length; i++) {
            var column = columns[i];
            tocolumns.push({Header: column.dataIndex, Width: column.width });
        }
        table.Columns = tocolumns;
        //记录单元格
        var tocells = [];
        var store = me.store;
        store.each(function (record) {
            var row = record.data;
            for (var p in row) {
                var celldata = row[p];
                if (celldata.Value || celldata.Formula || celldata.Router || celldata.TagId) {
                    tocells.push(celldata);
                }
            }
        });
        table.Cells = tocells;
    },

    //@pulbic 显示请求到的数据，cell形如{Id:,Flag:0,Value:{Key:A1,Value:5}}.
    showData: function (rows) {
        var me = this;
        if(rows && rows.length > 0){
            var row = rows[0];
            var store = me.store;
            var fields = store.config.fields;
            var i = 1;
            store.each(function (record) {
                Ext.Array.each(fields, function (field) {
                    var eachcell = record.data[field];
                    var key = field + i;
                    if (row[key]) {
                        var cell = row[key];
                        if (!eachcell.Value) {
                            record.data[field] = { Key: key, Flag: cell.Flag, Id: cell.Id, Value: cell.Value };
                        } else {
                            eachcell.Flag = cell.Flag;
                            eachcell.Id = cell.Id;
                            eachcell.Value = cell.Value;
                        }
                    }//用返回的数据更新表格数据.
                });
                i++;
            });
            me.cascadeCalc();//重新计算公式值.
            me.getView().refresh();
        }
    },

    //@private 根据顺序返回A~Z AA~AZ BA~BZ...列明.
    getColumnName: function (i) {
        if (i < 26) {
            return String.fromCharCode(65 + i);
        } else {
            var j = i / 26, k = i - j * 26;
            return String.fromCharCode(65 + j - 1) + String.fromCharCode(65 + k);
        }
    },

    //@private 创建新列.
    getNewColumn: function (colname, colwidth) {
        return {
            dataIndex: colname,
            text: colname,
            width: colwidth,
            editor: 'textfield',
            renderer: function (value) {
                if (value.NumberFloat && Ext.isNumeric(value.Value)) {                          
                    return Ext.Number.from(value.Value).toFixed(value.NumberFloat);
                }
                else if(value.Value){
                    return value.Value;
                }
                else {
                    return '<span style="text-align: center">' + '/' + '</span>';
                }
            }
        }
    },

    //@private 将数据更新到单元格上，并计算受影响的单元格.
    updateGridCell: function (record, column, row) {
        var me = this;
        if ('data' in record) {
            record = record.data;
        }
        me.cascadeCalc(record);
        var store = me.store;
        var row = store.getAt(row);
        row.data[column] = record;
        me.getView().refresh();
    },

    //@private 级联计算带公式的单元格,@cell为null时表示全部重算.
    cascadeCalc: function (cell) {
        var me = this;
        var crvalue = {};
        var store = me.store;
        var fields = store.config.fields;
        var row = 1;
        var triggers = {};//输入单元格-[公式单元格]
        var formulas = {};//公式单元格-[输入单元格]
        store.each(function (record) {
            Ext.Array.each(fields, function (field) {
                var eachcell = record.data[field];
                var key = field + row;
                if (eachcell.Value) {
                    crvalue[key] = Ext.Number.from(eachcell.Value);
                }
                else {
                    crvalue[key] = 0;
                }
                if (eachcell.Formula) {
                    var inputs = [];//输入变量
                    me.getVars(eachcell.Formula, inputs);
                    Ext.Array.each(inputs, function (input) {
                        if (!triggers[input]) {
                            triggers[input] = [eachcell];
                        } else {
                            triggers[input].push(eachcell);
                        }
                    });//记录触发关系  
                    formulas[key] = { cell: eachcell, inputs: inputs };
                }
            });
            row++;
        });//搜集单元格的值作为公式输入.  
        if (cell) {
            var key = cell.Key;
            var math = cell.Formula;
            var value = cell.Value;
            if (cell.Formula) {
                value = me.doCalc(crvalue, math);
                crvalue[key] = Ext.Number.from(value);
                cell.Value = value;
            }
            if (triggers[key]) {
                var tocalc = triggers[key];
                for (var times = 0; times < 10; times++) {
                    if (tocalc.length > 0) {
                        var tocalc2 = [];
                        Ext.Array.each(tocalc, function (tocalccell) {
                            var key2 = tocalccell.Key;
                            value = me.doCalc(crvalue, tocalccell.Formula);
                            crvalue[key2] = Ext.Number.from(value);
                            tocalccell.Value = value;
                            //去重合并
                            if (triggers[key2]) {
                                Ext.Array.each(triggers[key2], function (item) {
                                    if (!Ext.Array.contains(tocalc2, item)) {
                                        tocalc2.push(item);
                                    }
                                })
                            }
                        });
                        tocalc = tocalc2;
                    }
                    else {
                        break;
                    }
                }//最多迭代10次，防止死循环.
            }//级联计算受影响的公式
        }//计算cell公式的值
        else {
            var calculated = {};
            for (var times = 0; times < 10; times++) {
                var loopagain = false;
                for (var key in formulas) {
                    if (!calculated[key]) {
                        loopagain = true;
                        var inputs = formulas[key].inputs;
                        var hasunknown = false;
                        Ext.Array.each(inputs, function (input) {
                            if (formulas[input] && !calculated[input]) {
                                hasunknown = true;
                                return false;//break;
                            }
                        })
                        if (!hasunknown) {
                            var cell = formulas[key].cell;
                            var value = me.doCalc(crvalue, cell.Formula);
                            crvalue[key] = value;
                            calculated[key] = 1;
                            cell.Value = value;
                        }
                    }
                }
                if (!loopagain) {
                    break;
                }
            }
        }//计算所有公式
    },

    //@private 调用eval执行公式计算.
    doCalc: function (crvalue, math) {
        var arr = [];
        this.getVars(math, arr);
        for (var i = 0; i < arr.length; i++) {
            if (arr[i] in crvalue) {
                if (Ext.isNumeric(crvalue[arr[i]]))
                    math = math.replace(arr[i], crvalue[arr[i]]);
                else
                    math = math.replace(arr[i], 0);
            }
        }
        return eval(math).toPrecision(12);//toPrecision(12)防止浮点数漂移1.4000000000000001.
    },

    getVars:function(formula, vars){
        var arr = formula.match(/[A-Za-z]+[1-9]+/g);    
        Ext.Array.each(arr, function(item){
            if(!Ext.Array.contains(vars, item)){
                vars.push(item);
            }
        })
    },

    //@public 导出到Excel文件.	
    export: function (templateId, contentId) {
        var me = this;
        if (!me.table) {
            Ext.MessageBox.alert('出错', '报表还未打开，请先打开报表');
            return false;
        }
        Ext.Msg.wait("正在导出数据,请稍后......");
        var store = me.store;
        var data = [];
        store.each(function (record) {
            var row = record.data;
            var obj = {};
            for (var p in row) {
                var cell = row[p];
                obj[p] = cell.Value ? cell.Value : null;
            }
            data.push(obj);
        });
        var params = { templateId: templateId, contentId: contentId, title: me.table.Name, data: data, IsTree: false };
        Ext.Ajax.request({
            url: '/api/reportexport',
            jsonData: Ext.encode(params),
            headers: { 'Content-Type': 'application/json', Authorization: localStorage.getItem('auth') },
            method: 'POST',
            timeout: 10000,
            success: function (response) {
                var url = response.responseText;
                window.open(url);
                Ext.Msg.hide();
            },
            failure: function () {
                Ext.Msg.show({
                    title: '报表导出',
                    msg: '导出失败',
                    icon: Ext.Msg.ERROR,
                    buttons: Ext.Msg.OK
                });
                Ext.Msg.hide();
            }
        });

    }
});