Handsontable RuleJS missing recursive resolution

2019-08-07 07:18发布

站内问答 / JavaScript
686 2 4

I use RuleJS plugin for Handsontable (see it on GitHub) which works great for basic formulas but seems to lack recursive resolution.

I've made a code snippet containing two detailed examples, please check it out : 

$(document).ready(function () {
  var container1 = $('#example1');
  var container2 = $('#example2');
  
  container1.handsontable({
    data: [[1, '=A2'], ['=B2', '=5 * 2']],
    colHeaders: true,
    rowHeaders: true,
    formulas: true,
    minSpareRows: 1
  });
  
  container2.handsontable({
    data: [[1, '=A2', 3], ['=C1 * B2', '=5 + 1', 3]], 
    colHeaders: true,
    rowHeaders: true,
    formulas: true,
    minSpareRows: 1
  });
});
<!DOCTYPE html>
<html>
<head>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/jquery/jquery-1.10.2.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.js"></script>
    <link rel="stylesheet" media="screen" href="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.css">
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/lodash/lodash.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/underscore.string/underscore.string.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/moment/moment.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/numeral/numeral.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/numericjs/numeric.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/js-md5/md5.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/jstat/jstat.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/lib/formulajs/formula.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/parser.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/RuleJS/js/ruleJS.js"></script>
    <script src="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.formula.js"></script>
    <link rel="stylesheet" media="screen" href="http://handsontable.github.io/handsontable-ruleJS/css/samples.css">

    <style type="text/css">
        body {background: white; margin: 20px;}
        h2 {margin: 20px 0;}
    </style>
</head>
<body>
  <h2>Bugs in handsontable-ruleJS</h2>

  <p>Both cases seem to come from the same problem, but they both worth seeing.</p>
  <p>Here B1 displays the value of B2 <b>before</b> its interpretation where it should display "<b>10</b>". Just like it misses some recursive processing. Focusing the cell will show its real value "<b>=A2</b>" which will next be interpreted correctly.</p>
  <div id="example1" class="handsontable"></div>

  <p>This one is interesting, because when the cell "<b>A2</b>" tries to calculate "<b>C1 * B2</b>" it does "<b>3 * =5 + 1"</b> instead of "<b>3 * 6</b>", which obviously fails.</p>
  <div id="example2" class="handsontable"></div>
  <p>The only way to correct it is to edit "<b>C1</b>" (even without changing its value).</p>
</body>
</html>

If you prefer JSFiddle, here you go.

Best regards.

Edit: You may not see the first bug when using the embed snippet and going to fullscreen because it seems to trigger a refresh of the table. Use the JSFiddle for better results.

Edit 2 (SOLVED): Ok I think I've patched it, you can find the result here. I'll post a complete answer when Stackoverflow allows me to do it. Any feedback is still welcome, I'm sure there is a better way to do it but at least it seems to work now.

2条回答
forever°为你锁心
2楼-- · 2019-08-07 07:27

The working code can be found here : http://jsfiddle.net/71o23gp0/8/.

The important part was to replace : 

var custom = {
      cellValue: instance.getDataAtCell
};

By 

var custom = {
        cellValue: function(row, col){
          var value = instance.getDataAtCell(row, col);
          if (value && value[0] === '=') {
              var formula = value.substr(1).toUpperCase();
              var cellId = instance.plugin.utils.translateCellCoords({
                  row: row,
                  col: col
              });
              var item = instance.plugin.matrix.getItem(cellId);

              if (!item) {
                  item = instance.plugin.matrix.addItem({
                      id: cellId,
                      formula: formula
                  });
              } else {
                  item = instance.plugin.matrix.updateItem({
                      id: cellId,
                      formula: formula
                  });
              }
              var formulaResult = instance.plugin.parse(formula, {
                  row: row,
                  col: col,
                  id: cellId
              });
              value = formulaResult.result || '#ERROR';
              formulasResults[cellId] = value;
              instance.plugin.matrix.updateItem(item, {
                  formula: formula,
                  value: formulaResult.result,
                  error: formulaResult.error
              });
          }
          return value;
      }
    };

Instead of simply returning the value of the cell, it checks if it's a formula and resolve it if so. Because this method is called by RuleJS when resolving a formula, this make the resolution recursive. The result is then cached for better performance.

There are other minor modifications in the source code, but I've added comments in the fiddle before and after every modification.

查看更多
0人赞 添加讨论(0) 举报
Emotional °昔
3楼-- · 2019-08-07 07:32

Following on from the answer https://stackoverflow.com/a/35528447/489865 in this question, I have patched a little further. The working code can be found at http://jsfiddle.net/ufL1vts5/.

The essential change is to store in formulasResults[] not just the "result" but rather the whole object returned by instance.plugin.parse(), so that cell highlighting for formulas is picked up, as per:

// parse formula
var newValue = instance.plugin.parse(formula, {row: row, col: col, id: cellId});
// cache result
formulasResults[cellId] = newValue;

My modifications include:

  1. Store whole instance.plugin.parse() object in formulasResults[], so that the formula/formula-error CSS style gets applied. [Note that this is not perfect: it picks up "calculated" formula style correctly, but sometimes what should be formula-error style only get formula style --- but sufficient for my needs.]

  2. Added formula & formula-error CSS styles (from supplied handsontable.formula.css) into JSFiddle example, so that cell highlighting can be seen.

  3. Added 2 extra examples: one from handsontable formula returning #NEED_UPDATE, to show that works too, and one from the example supplied with handsontable-ruleJS at https://github.com/handsontable/handsontable-ruleJS/blob/master/index.html.

  4. Removed the newly added beforeRender hook via instance.removeHook('beforeRender', beforeRender); in this.init = function ().

  5. Changed some code layout in handsontable.formula.js, so that it minimises the differences from that supplied with handsontable-ruleJS.

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)