【Handsontable】無償版で数式を実現してみる

はじめに

「Handsontable(ハンズオンテーブル)」は、WEBでExcelのようなスプレッドシートライクな入力を可能にしてくれるJavaScriptライブラリなんですが、Excelのように SUM 関数などの数式って出来ないんだっけ？

調べたらなんのことはない、数式プラグインがあり、バージョン 6.2.2 では 有償のPro版で使用可能となっていました。バージョン 7.0.0以降では有償版のみとなったので実質標準化されたわけです。ケチケチ根性だから有償機能を全然意識してなかった。
https://handsontable.com/docs/6.2.2/Formulas.html

ちなみに、HandsontableのフォークライブラリのJspreadsheet CE (無償版)では標準で数式に対応しています。
https://bossanova.uk/jspreadsheet/v4/examples/spreadsheet-formulas
とほほさんは Jspreadsheet推し

数式対応

Handsontableの数式対応を調べると、「RuleJS」という数式ライブラリを見つけました。

このライブラリは Handsontable 社が開発したもので、2018/9/20にアーカイブモードになっています。これはHandsontableの初期段階に実装されていたもののようです。

この「RuleJS」を組み込めば出来そうだなと思ったんですが、どうやればいいのか分かりません。RuleJS でひたすら検索すると、stackoverflow のある質問を見つけました。

その中に JavaScript コードスニペットである JSFiddle に Handsontable + RuleJS で組まれたものがありました。

後で調べ直したら、上記のJSFiddleは次のプログラムの再帰の場合の不具合を修正したもののようです。
https://github.com/handsontable/handsontable-ruleJS/blob/master/src/handsontable.formula.js

環境

HandsontableはMITライセンス版のバージョン 6.2.2を使用しています。

使い方

数式用にformulasオプションをtrueにセットします。
セルの内容の接頭辞に"="が付いていれば数式として認識されます。

formulas: true

使用できる数式は下記の通りです。

行追加、列追加、行削除、列削除に対応していますが、簡易なテストしかしていないので不具合はあるかも。
オートフィルの機能の部分を削除したので、オートフィルは非対応です。

デモ

右スクロールさせると末列に演算結果が表示されています。

See the Pen formula example in Handsontable-ruleJS by やじゅ (@yaju-the-encoder) on CodePen.

移行対応

JSFiddle内の Handsontable は2016年でバージョンは1.xのものと大変古いので、MIT版の 6.2.2 に変更を試みました。やはり、そう簡単に動きませんでしたね。
変更した箇所を列挙していきます。

リンク先

変更前

<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/jquery/jquery-1.10.2.js"></script>
<script src="http://handsontable.github.io/handsontable-ruleJS/lib/handsontable/handsontable.full.js"></script>
<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>

ruleJS.all.full.min.js のCDNが見当たらなかったため、暫定的に下記サイトを参考に rawgithub.com を使用しています。

変更後

<link href="https://cdn.jsdelivr.net/npm/handsontable@6.2.2/dist/handsontable.full.min.css" rel="stylesheet" media="screen">

<script src="https://rawgit.com/handsontable/ruleJS/master/dist/full/ruleJS.all.full.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/handsontable@6.2.2/dist/handsontable.full.min.js"></script>

afterCreateRowのauto

自動生成有無から、どこから追加されたかのソース元になりました。

変更前

var afterCreateRow = function(row, amount, auto) {
    if (auto) {
        return;
    }

変更後

let afterRow = function(instance, row, amount, source, sign) {
    if (source === "auto") {
        return;
    }

getSelected()は2次元配列に変更

1次元配列から2次元配列に変更されました。

変更前

var selectedRow = instance.plugin.utils.isArray(instance.getSelected())
 ? instance.getSelected()[0] : undefined;

変更後

var selectedRow = instance.plugin.utils.isArray(instance.getSelected())
 ? instance.getSelected()[0][0] : undefined;

rendererの登録方法

registerCellType メソッドで登録する方法になりました。

変更前

Handsontable.cellTypes['formula'] = formulaCell;
Handsontable.TextCell.renderer = formulaRenderer;
Handsontable.NumericCell.renderer = formulaRenderer;

変更後

instance.setCellMeta(row, col, 'type', 'formula');

Handsontable.cellTypes.registerCellType('formula', formulaCell);

cellsイベント登録

cellsイベントを登録しないと反応しませんでした。

変更後

cells : function(row, col) {
    this.instance.plugin.custom.cellValue(row, col);
}

行削除と列削除の追加

行追加と列追加の機能しかなかったため、行削除と列削除の追加しました。
行削除と列削除は、行追加と列追加の機能内容はほぼそのままに正負の向きを変更するだけで対応できたため、共通関数化(afterRow、afterCol)しました。

変更後

let afterCreateRow = function(row, amount, auto) {
    return afterRow(this, row, amount, auto, 1)
};

let afterRemoveRow = function(row, amount, physicalrows, auto) {
    return afterRow(this, row, amount, auto, -1)
}

let afterCreateCol = function(col) {
    return afterCol(this, col, 1)
}

let afterRemoveCol = function(col) {
    return afterCol(this, col, -1)
}

instance.addHook('afterRemoveRow', afterRemoveRow);
instance.addHook('afterRemoveCol', afterRemoveCol);

instance.removeHook('afterRemoveRow', afterRemoveRow);
instance.removeHook('afterRemoveCol', afterRemoveCol);

その他

• 宣言 var から let に変更
• Handsontable.Dom を Handsontable.dom に変更
• beforeAutofillInsidePopulateの削除
  6.2.2ではまだ必要かも知れないですが、9.0.0から廃止となっているため削除しました。

ソースコード

<!DOCTYPE html>
<html lang="jp">
<body>
<head>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/handsontable/6.2.2/handsontable.full.css">
<style type="text/css">
</style>
</head>
<body onload="createGrid()">
    <div id="grid"></div>
</body>
<script src="https://rawgit.com/handsontable/ruleJS/master/dist/full/ruleJS.all.full.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/handsontable@6.2.2/dist/handsontable.full.min.js"></script>
<script>
//
// Here starts the new handsontable.formula.js
//
(function(Handsontable) {
    'use strict';

    let formulasResults = {};

    function HandsontableFormula() {

        let isFormula = function(value) {
            if (value) {
                if (value[0] === '=') {
                    return true;
                }
            }

            return false;
        };

        let beforeRender = function(isForced) {
            formulasResults = {};
            let data = this.getData();
            for (let i = 0; i < data.length; ++i) {
                for (let j = 0; j < data[i].length; ++j) {
                    let value = data[i][j];
                    if (value && value[0] === '=') {
                        let cellId = this.plugin.utils.translateCellCoords({
                            row: i,
                            col: j
                        });
                        this.plugin.matrix.removeItem(cellId);
                    }
                }
            }
        };

        let formulaRenderer = function(instance, TD, row, col, prop, value, cellProperties) {
            if (instance.formulasEnabled && isFormula(value)) {
                // translate coordinates into cellId
                let cellId = instance.plugin.utils.translateCellCoords({
                    row: row,
                    col: col
                }), prevFormula = null, formula = null, needUpdate = false, error, result;

                if (!cellId) {
                    return;
                }

                // get cell data
                let item = instance.plugin.matrix.getItem(cellId);

                if (item) {

                    needUpdate = !!item.needUpdate;

                    if (item.error) {
                        prevFormula = item.formula;
                        error = item.error;

                        if (needUpdate) {
                            error = null;
                        }
                    }
                }

                // check if typed formula or cell value should be recalculated
                if ((value && value[0] === '=') || needUpdate) {

                    if (formulasResults[cellId] === undefined) {
                        formula = value.substr(1).toUpperCase();

                        if (!error || formula !== prevFormula) {

                            let currentItem = item;

                            if (!currentItem) {

                                // define item to rulesJS matrix if not exists
                                item = {
                                    id: cellId,
                                    formula: formula
                                };

                                // add item to matrix
                                currentItem = instance.plugin.matrix.addItem(item);
                            }

                            // parse formula
                            let newValue = instance.plugin.parse(formula, {
                                row: row,
                                col: col,
                                id: cellId
                            });

                            // check if update needed
                            needUpdate = (newValue.error === '#NEED_UPDATE');

                            // update item value and error
                            instance.plugin.matrix.updateItem(currentItem, {
                                formula: formula,
                                value: newValue.result,
                                error: newValue.error,
                                needUpdate: needUpdate
                            });

                            error = newValue.error;
                            result = newValue.result;

                            // update cell value in hot
                            value = error || result;
                        }
                    }
                    else {
                        value = formulasResults[cellId];
                    }
                }

                if (error) {
                    // clear cell value
                    if (!value) {
                        // reset error
                        error = null;
                    } else {
                        // show error
                        value = error;
                    }
                }

                // change background color
                if (instance.plugin.utils.isSet(error)) {
                    Handsontable.dom.addClass(TD, 'formula-error');
                } else if (instance.plugin.utils.isSet(result)) {
                    Handsontable.dom.removeClass(TD, 'formula-error');
                    Handsontable.dom.addClass(TD, 'formula');
                }
            }

            // apply changes
            if (cellProperties.type === 'numeric') {
                numericCell.renderer.apply(this, [instance, TD, row, col, prop, value, cellProperties]);
            } else {
                textCell.renderer.apply(this, [instance, TD, row, col, prop, value, cellProperties]);
            }
        };

        let afterChange = function(changes, source) {
            let instance = this;

            if (!instance.formulasEnabled) {
                return;
            }

            if (source === 'edit' || source === 'undo' || source === 'autofill') {

                let rerender = false;

                changes.forEach(function(item) {

                    let row = item[0]
                      , col = item[1]
                      , prevValue = item[2]
                      , value = item[3];

                    let cellId = instance.plugin.utils.translateCellCoords({
                        row: row,
                        col: col
                    });

                    // if changed value, all references cells should be recalculated
                    if (value[0] !== '=' || prevValue !== value) {
                        instance.plugin.matrix.removeItem(cellId);

                        // get referenced cells
                        let deps = instance.plugin.matrix.getDependencies(cellId);

                        // update cells
                        deps.forEach(function(itemId) {
                            instance.plugin.matrix.updateItem(itemId, {
                                needUpdate: true
                            });
                        });

                        rerender = true;
                    }
                });

                if (rerender) {
                    instance.render();
                }
            }
        };

        let afterCreateRow = function(row, amount, auto) {
            return afterRow(this, row, amount, auto, 1)
        };

        let afterRemoveRow = function(row, amount, physicalrows, auto) {
            return afterRow(this, row, amount, auto, -1)
        }

        let afterCreateCol = function(col) {
            return afterCol(this, col, 1)
        }

        let afterRemoveCol = function(col) {
            return afterCol(this, col, -1)
        }

        let formulaCell = {
            renderer: formulaRenderer,
            editor: Handsontable.editors.TextEditor,
            dataType: 'formula'
        };

        let textCell = {
            renderer: Handsontable.renderers.TextRenderer,
            editor: Handsontable.editors.TextEditor
        };

        let numericCell = {
            renderer: Handsontable.renderers.NumericRenderer,
            editor: Handsontable.editors.NumericEditor
        };

        let afterRow = function(instance, row, amount, source, sign) {
            if (source === "auto") {
                return;
            }

            let selectedRow = instance.plugin.utils.isArray(
                              instance.getSelected()) ? instance.getSelected()[0][0] : undefined;

            if (instance.plugin.utils.isUndefined(selectedRow)) {
                return;
            }

            let direction = (selectedRow >= row) ? 'before' : 'after'
              , items = instance.plugin.matrix.getRefItemsToRow(row)
              , counter = amount * sign
              , changes = [];

            items.forEach(function(id) {
                let item = instance.plugin.matrix.getItem(id)
                  , formula = instance.plugin.utils.changeFormula(item.formula, counter, { row: row })
                  , newId = id;

                if (formula !== item.formula) {
                    // formula updated

                    // change row index and get new coordinates
                    if ((direction === 'before' && selectedRow <= item.row) ||
                        (direction === 'after' && selectedRow < item.row)) {
                        newId = instance.plugin.utils.changeRowIndex(id, counter);
                    }

                    let cellCoords = instance.plugin.utils.cellCoords(newId);

                    if (newId !== id) {
                        // remove current item from matrix
                        instance.plugin.matrix.removeItem(id);
                    }

                    // set updated formula in new cell
                    changes.push([cellCoords.row, cellCoords.col, '=' + formula]);
                }
            });

            if (items) {
                instance.plugin.matrix.removeItemsBelowRow(row);
            }

            if (changes) {
                instance.setDataAtCell(changes);
            }
        };

        let afterCol = function(instance, col, sign) {
            let selectedCol = instance.plugin.utils.isArray(
                              instance.getSelected()) ? instance.getSelected()[0][1] : undefined;

            if (instance.plugin.utils.isUndefined(selectedCol)) {
                return;
            }

            let direction = (selectedCol >= col) ? 'before' : 'after'
              , items = instance.plugin.matrix.getRefItemsToColumn(col)
              , counter = sign
              , changes = [];

            items.forEach(function(id) {
                let item = instance.plugin.matrix.getItem(id)
                  , formula = instance.plugin.utils.changeFormula(item.formula, counter, { col: col })
                  , newId = id;

                if (formula !== item.formula) {
                    // formula updated

                    // change col index and get new coordinates
                    if ((direction === 'before' && selectedCol <= item.col) ||
                        (direction === 'after' && selectedCol < item.col)) {
                        newId = instance.plugin.utils.changeColIndex(id, counter);
                    }

                    let cellCoords = instance.plugin.utils.cellCoords(newId);

                    if (newId !== id) {
                        // remove current item from matrix if id changed
                        instance.plugin.matrix.removeItem(id);
                    }

                    // set updated formula in new cell
                    changes.push([cellCoords.row, cellCoords.col, '=' + formula]);
                }
            });

            if (items) {
                instance.plugin.matrix.removeItemsBelowCol(col);
            }

            if (changes) {
                instance.setDataAtCell(changes);
            }
        };

        this.init = function() {
            let instance = this;
            instance.formulasEnabled = !!instance.getSettings().formulas;

            if (instance.formulasEnabled) {

                let custom = {
                    cellValue: function(row, col) {
                        let value = instance.getDataAtCell(row, col);
                        if (value && value[0] === '=') {
                            let formula = value.substr(1).toUpperCase();
                            let cellId = instance.plugin.utils.translateCellCoords({
                                row: row,
                                col: col
                            });
                            let 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
                                });
                            }
                            let 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
                            });
                            
                            instance.setCellMeta(row, col, 'type', 'formula');
                        }
                        return value;
                    }
                };

                instance.plugin = new ruleJS();
                instance.plugin.init();
                instance.plugin.custom = custom;

                Handsontable.cellTypes.registerCellType('formula', formulaCell);

                instance.addHook('beforeRender', beforeRender);
                instance.addHook('afterChange', afterChange);

                instance.addHook('afterCreateRow', afterCreateRow);
                instance.addHook('afterCreateCol', afterCreateCol);
                instance.addHook('afterRemoveRow', afterRemoveRow);
                instance.addHook('afterRemoveCol', afterRemoveCol);
            } else {
                instance.removeHook('afterChange', afterChange);

                instance.removeHook('afterCreateRow', afterCreateRow);
                instance.removeHook('afterCreateCol', afterCreateCol);
                instance.removeHook('afterRemoveRow', afterRemoveRow);
                instance.removeHook('afterRemoveCol', afterRemoveCol);
            }
        };
    }

    let htFormula = new HandsontableFormula();

    Handsontable.hooks.add('beforeInit', htFormula.init);
    Handsontable.hooks.add('afterUpdateSettings', function() {
        htFormula.init.call(this, 'afterUpdateSettings')
    });

}
)(Handsontable);

function createGrid() {
    let hot = Handsontable(grid, {
        data: [[ 1,  2, 3,  4,  5, 6, 7,  8,  9,  10,'SUM(A1:D1, H1)',   '=SUM(A1:D1, H1)'],
               [-1,-10, 2,  4,100, 1,50, 20,200,-100,'MAX(A2:J2)',       '=MAX(A2:J2)'],
               [-1,-40,-53, 1, 10,30,10,301, -1, -20,'MIN(A3:J3)',       '=MIN(A3:J3)'],
               [20, 50,100,20,  1, 5,15, 25, 45,  23,'AVERAGE(A4:J4)',   '=AVERAGE(A4:J4)'],
               [ 0, 10,  1,10,  2,10, 3, 10,  4,  10,"SUMIF(A5:J5,'>5')","=SUMIF(A5:J5,'>5')"]],
        colHeaders: ['A','B','C','D','E','F','G','H','I','J','fn(x)','result'],
        rowHeaders: true,
        formulas: true,
        contextMenu: true,
        cells : function(row, col) {
            this.instance.plugin.custom.cellValue(row, col);
        }
    });
}
</script>
</html>

最後に

記事を書いている最中に、行削除と列削除の機能が抜けていることが分かり、このままだと記事を公開できないじゃんと思って、先ずは行追加を参考に行削除を見よう見真似で作ってみると、行追加の 1 が記載されている箇所を -1 に書き換えれば上手く動いてくれたんですよね、列削除も同様にしました。ただ削除の場合、複数範囲に対応する必要がありましたが、これも -1 を amount * -1 にするだけで対応できたんで良かったです。

これで数式が表示できるようになりました。本家は hyperformula という本格的な数式ライブラリになっているので、自分が数式対応したくらいでは大した影響はないでしょう。

今の書き方もプラグインのようなんですが、Handsontable では、Custom Plugin の作り方があるようなので、その作法に書き換えようと思っています。あと「RuleJS」のCDNが検索しても見当たらなかったので、他の数式ライブラリに乗り換えた方が使ってもらえるかなと思っています。

他の数式ライブラリ