网页中通过 Javascript 实现类似 Excel 的公式解析计算功能 1. 目标 2. 难点 3.垃圾CalcEval.js 4.正确姿势 5.代码说明

75 阅读 0 评论 50 点赞

我是靠谱客的博主温暖西装，最近开发中收集的这篇文章主要介绍网页中通过 Javascript 实现类似 Excel 的公式解析计算功能 1. 目标 2. 难点 3.垃圾CalcEval.js 4.正确姿势 5.代码说明，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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();
}
});
}
});