How to correctly install Handsontable with RuleJS

jon · February 25, 2016, 8:52am

Short

I am appallingly confused at what I need to do to install Handsontable with RuleJS (formula) support. I find the resources/links/documentation on the web of little help. I had made the post below on stackoverflow (http://stackoverflow.com/questions/35603349/how-to-correctly-install-handsontable-with-rulejs, but no response, so I have realised I need to join this forum. Please excuse me, but please help!

Long

I need to install Handsontable with RuleJS support (for formulas) locally (i.e. not just access its files from github), under Windows. I will later need to distribute with my application. I really do not want to have to install & configure bower, git etc.

Initially, starting from Handsontable · GitHub, I tried downloading the zip from GitHub - handsontable/handsontable: JavaScript Data Grid / Data Table with a Spreadsheet Look & Feel. Works with React, Angular, and Vue. Supported by the Handsontable team ⚡. Then I had to fetch various extra bits from elsewhere on github, attempting to make the demo at Plugin for handsontable using RuleJS library (formulas parser) work. This includes its stuff via:

<!-- handsontable v1 -->
<script data-jsfiddle="common" src="lib/handsontable/handsontable.full.js"></script>
<link data-jsfiddle="common" rel="stylesheet" media="screen" href="lib/handsontable/handsontable.full.css">

<!-- ruleJS -->
<link rel="stylesheet" media="screen" href="lib/handsontable/handsontable.formula.css">

<script data-jsfiddle="common" src="lib/RuleJS/lib/lodash/lodash.js"></script>
<script data-jsfiddle="common" src="lib/RuleJS/lib/underscore.string/underscore.string.js"></script>
<script data-jsfiddle="common" src="lib/RuleJS/lib/moment/moment.js"></script>
<script data-jsfiddle="common" src="lib/RuleJS/lib/numeral/numeral.js"></script>
<script data-jsfiddle="common" src="lib/RuleJS/lib/numericjs/numeric.js"></script>
<script data-jsfiddle="common" src="lib/RuleJS/lib/js-md5/md5.js"></script>
<script data-jsfiddle="common" src="lib/RuleJS/lib/jstat/jstat.js"></script>
<script data-jsfiddle="common" src="lib/RuleJS/lib/formulajs/formula.js"></script>

<script data-jsfiddle="common" src="lib/RuleJS/js/parser.js"></script>
<script data-jsfiddle="common" src="lib/RuleJS/js/ruleJS.js"></script>

<script data-jsfiddle="common" src="lib/handsontable/handsontable.formula.js"></script>

This is all messy, and (I think) I could not even find the numericjs/numeric.js in any download package.

I then noticed GitHub - handsontable/handsontable-ruleJS: Formula parser for Handsontable data grid using RuleJS library. This seemed much simpler: it has the handsontable stuff it wants in its own bower_components sub-directory, and its index.html example, with files included via:

<!-- handsontable --> 
<script data-jsfiddle="common" src="bower_components/handsontable/dist/handsontable.full.js"></script> 
<link data-jsfiddle="common" rel="stylesheet" media="screen" href="bower_components/handsontable/dist/handsontable.full.css"> 

<!-- ruleJS --> 
<script data-jsfiddle="common" src="bower_components/ruleJS/dist/full/ruleJS.all.full.js"></script> 
<script data-jsfiddle="common" src="src/handsontable.formula.js"></script> 
<link rel="stylesheet" media="screen" href="src/handsontable.formula.css">

Additionally, its README.md states Important: Few things have been modified in external libraries, so please use all library files only from this repo., which makes it sound like this is the safest way to go (and full, latest handsontable release might not even behave correctly).

However, I have just realised that handsontable-RuleJS has its included Handsontable stuff at version 0.14.1, while the latest release of that is 0.23, and there are functions I need from later versions.

So, is there a simple way to download what I need? Should I be using the simple handsontable-RuleJS link, with its included bower_components, or are they only there for the index.html demo, or overwrite them with newer versions, or what…? Or, do I really need to get bower & git, make them work right under Windows, and then magically everything will be sorted out in a way too difficult to just download directly?

aleksandra_budnik · February 25, 2016, 5:00pm

Hi @jon
RulejJS is like you saw currently updated to Handsontable 0.14.1 and your way of loading resources for now is the best way, which I prefer to use longer one (1 example).
We’re going to update formulas but there’s no estimated date when it will be.

jon · February 25, 2016, 5:34pm

@aleksandra_budnik
Thank you, aleksandr. I’m afraid I need to clarify what you are saying.

Just “Yes” or “No”: do you mean that in order to use RuleJS I should stick with Hot 0.14.1?

Then: assuming your answer is “Yes”, if I am to stick with 0.14.1 that is fine BUT I cannot then get one critical feature to work. This is why I became dissatisfied with 0.14 and tried to revert to 0.23.

There are several posts (e.g. http://stackoverflow.com/a/32117834/489865) showing me that in order to get the value of a cell (i.e. after it is calculated from formula) the code is like: hot.plugin.helper.cellValue ('C1'). Now the problem is that in 0.14.1 there is no hot.plugin in a Handsontable object; that was introduced in a later version (15??), and there are no other examples. I looked in the sources of 0.14.1 and I did come across cellValue function somewhere, so it is there, but I have no idea how to access/call it. So, at 0.14, how can I call cellValue()?

Many, many thanks in advance!

aleksandra_budnik · February 25, 2016, 10:02pm

You can actually load Handsontable 0.23.0 if you would like to use its newest features, like in this example http://jsfiddle.net/scxahqvr/. I got formulas working and in console printed a line to confirm me in that it’s working on 0.23.0

jon · February 25, 2016, 10:32pm

@alexsandr
I agree, from looking around, that I really need 0.23, as others have, and like you do in that example.

The problem is, you are accessing the files from http://handsontable.github.io/.... The file structure is similar to example #1 above. And, as I said, I have tried, but I cannot correctly get the files, in that structure, successfully locally to my disk. This was my original question. For example, if I recall correctly, numericjs/numeric.js, was nowhere among the various packages I could find to fetch. I guess I will have to look into this again. The question is: taking your jsfiddle example, are you able to get all the files required so that they are not accessed off the web but instead locally, and if so precisely how/what do you do to get them all, because I got stuck…?

aleksandra_budnik · February 25, 2016, 10:47pm

I’m not sure do I understand it correctly. You don’t want any of those resources to be downloaded externally so you can easily upload the into a one catalog if needed, like

<script src="YOUR-CATALOG/handsontable.full.js"></script>
<link type="text/css" rel="stylesheet" href="YOUR-CATALOG/handsontable.full.min.css">

<script src="YOUR-CATALOG/lodash.js"></script>
<script src="YOUR-CATALOG/underscore.string.js"></script>
<script src="YOUR-CATALOG/moment.js"></script>
<script src="YOUR-CATALOG/numeral.js"></script>
<script src="YOUR-CATALOG/numeric.js"></script>
<script src="YOUR-CATALOG/md5.js"></script>
<script src="YOUR-CATALOG/jstat.js"></script>
<script src="YOUR-CATALOG/formula.js"></script>
<script src="YOUR-CATALOG/parser.js"></script>
<script src="YOUR-CATALOG/ruleJS.js"></script>
<script src="YOUR-CATALOG/handsontable.formula.js"></script>

If you would have any issues related to catalog paths you can also copy and paste the code of every source and save in local files. I got them all working fine (maybe there’s something missing in by our side?) so if it’s not what you’re looking for just give me a hint.

jon · February 26, 2016, 10:17am

OK, we are back to I need to get my example #1 working, in some shape or form. Fair enough.

I do understand I therefore need to download all the files, into my area, and I will also be distributing that with my web application, so that it is all self-contained.

I was concentrating on getting the example I pasted, from http://handsontable.github.io/handsontable-ruleJS/, exactly as it was there. My problem was that I could not get all the files as per example #1. According to my notes:

Instructions: https://docs.handsontable.com/0.22.0/tutorial-quick-start.html, subsection “Alternative installation”.
Downloaded handsontable-master.zip from https://github.com/handsontable/handsontable/archive/master.zip
As per https://docs.handsontable.com/0.22.0/demo-formula-support.html, downloaded formula.js-master.zip from https://github.com/sutoiku/formula.js/archive/master.zip
Downloaded ruleJS-master.zip from https://libraries.io/github/handsontable/ruleJS

IIRC, even at this point I was finding I had no numericjs/numeric.js, one of the files required in example #1. You can also see I had picked up https://github.com/sutoiku/formula.js, which is quite outside anything handsontable. Also, I was fetching ruleJS directly from https://libraries.io/github/handsontable/ruleJS.

At that point I suddenly noticed https://github.com/handsontable/handsontable-ruleJS. This seemed to be better, as I was getting handsontable/**handsontable-ruleJS** rather than handsontable/**ruleJS**, and also it was inside https://github.com/handsontable instead of https://libraries.io/github. All this made me feel I would be getting a ruleJS properly tailored for handsontable.

Once I had downloaded that, I noticed it had an example in its index.html, as per example #2, which simply worked, using stuff only within itself (via bower_components), and I did not need anything else. So I switched to that only. Whereupon, as we have discovered, although the demo works, the included handsontable stuff is at version 0.14, but I need the 0.23 I had originally started with…

Now, assuming what you have pasted would work, that is a little simpler, and I will look into that.

My thought to proceed now is as follows:

Start from page https://github.com/handsontable/handsontable. Pick sub-packages from the links there.
Download the zip from https://github.com/handsontable/handsontable-ruleJS. This gives me a good handsontable-ruleJS. The only problem is that its included handsontable stuff in its bower_components directory is at too old version 0.14.
Download the zip from https://github.com/handsontable/handsontable. This gives me a good handsontable, at version 0.23.
Now, merge (or cross reference), so that handsontable-ruleJS/bower_components/handsontable picks up the handsontable stuff from the 0.23 handsontable instead.
Ignore the statement in handsontable-ruleJS/README.md which says “Important: Few things have been modified in external libraries, so please use all library files only from this repo.”, and accept/trust that the 0.23 handsontable stuff seems to work acceptably.
Instead of trying to find other versions of things like formula.js or ruleJS, accept what I have in handsontable-ruleJS/bower_components for these.
Then my only “clashes” are that handsontable-ruleJS/bower_components has moment, pikaday & zeroclipboard, which are also present in handsontable/dist. It may not matter which I use for these; I’m guessing they are independent of handsontable-ruleJS, so I might as well pick the (potentially newer) ones from handsontable.

Phew! This is a long post! You can see why I find downloading/installing RuleJS support so difficult (Handsontable without that would be easy)! Page https://github.com/handsontable has so many things to potentially download, with no explanation of what you actually need…

Would you be kind enough to read through, and confirm/comment that my proposed steps above are acceptable? Above all else, could you confirm that I only need to download two things, https://github.com/handsontable/handsontable-ruleJS & https://github.com/handsontable/handsontable, nothing else, that would really help? I would be so grateful!

aleksandra_budnik · March 2, 2016, 1:49pm

Hi @jon sorry for a delay. I found that all my notifications from friday were gone.
Are there any questions left that are still not solved? You were really deep in concluding this one.

jon · March 2, 2016, 2:24pm

Hi @aleksandra_budnik,

I thought you had disappeared! I just wanted your confirmation of my final paragraph in my last post above?

I have done more work, diffing what comes in https://github.com/handsontable/handsontable-ruleJS against standalone components. To summarise, for now at least, I am going with:

Get https://github.com/handsontable/handsontable-ruleJS. (I need that for files like handsontable.formula.js.)
Replace its bower_components/handsontable (version 0.14) with https://github.com/handsontable/handsontable (version 0.23). (At this point, I have a working handsontable-ruleJS but with up-to-date functions I need.)
I then wonder about replacing its bower_components/ruleJS (version 2.5.0) with https://github.com/handsontable/ruleJS (version 2.9.0)?
*For the time being I have not bothered, as the only difference I see is to do with moment code, which I am not looking at yet. (Nor am I yet bothering about the different versions of pikaday etc. they each supply.)

I will tell you that, from looking around the web at handsontable & ruleJS code people are using/questioning, everybody is fetching them a different way and have all sorts of different versions of the components running against each other…

aleksandra_budnik · March 2, 2016, 9:37pm

Hi @jon I’ll propose this issue to be discussed with developers this Friday.
I’m (or any team member) will be back with any updates as soon as possible.

jon · March 3, 2016, 8:28am

Thanks. I’m still just looking for confirmation that the only two things I need are https://github.com/handsontable/handsontable-ruleJS plus latest https://github.com/handsontable/handsontable ?

budnix · March 4, 2016, 9:06am

Hi @jon,

I think that you have only 2 options here.

Include ruleJS with Handsontable v0.14.1 (recommended by RuleJS)
Include ruleJS with the latest Handsontable and if everything would work as expected then use this configuration (experimental)

Problem is that Handsontable-RuleJS is no longer maintained. In Q4 we plan to rewrite formula support from scratch and support it with the newest version of Handsontable.

jon · March 8, 2016, 11:22am

@budnix
Thank you.

Since the opening sentence at https://docs.handsontable.com/0.23.0/tutorial-introduction.html states “Handsontable is a composite spreadsheet component for apps and websites”, I took it as such. I have never heard of a “spreadsheet” which does not do formulas (or do them correctly, and not as “experimental”). Unfortunately, I cannot wait till this year’s Q4 for it to work.

Every example I could find on the web which needs to look at/save the values in a handsontable (which I need) uses hot.plugin.helper.cellValue(). Since this was only introduced after Hot 0.14.1, I conclude that many others are forced to use a later “experimental”/“unsupported” combination, like me.

I am now using handsontable-ruleJS with handsontable 0.23. I believe the behaviour to be the same as it was when Hot was at 0.14.1, so that is good.

Not sure what you have in mind for “rewrite formula support from scratch”. In order to get any kind of reasonable formula recursive references working (the example supplied at https://github.com/handsontable/handsontable-ruleJS/blob/master/index.html is ridiculously simple/contrived), I have had to make modifications to handsontable.formula.js as per my post at http://stackoverflow.com/a/35865132/489865 plus working JSFiddle with code at http://jsfiddle.net/ufL1vts5/. You may, or may not, wish to glance at that.

As per http://stackoverflow.com/questions/31802875/delete-row-or-column-issue-formula-adjustment-not-done-on-these-operations, deleting column/row does not adjust formulas in the way that inserting column/row does. handsontable.formula.js needs an afterRemoveRow/Col() similar-ish to afterCreateRow/Col().

Although I realise this is not the fault/responsibility of handsontable-ruleJS, if you try to get any documentation/help on formulas, you are referred to http://handsontable.github.io/ruleJS/. That gives a list of all the formulas, but clicking on any of them redirects to http://stoic.com/pages/formula/function, which gives a 404. That leaves no documentation on the functions, nor do I see anything about formula syntax or cell references (A1, $A1, A$1, $A$1 ?). If you are going to support formulas, are you able to do anything about the documentation?

So there’s some food for thought!

aleksandra_budnik · March 29, 2016, 12:51pm

Hi @jon
There surely will be a docs improvement after formulas refactoring (more information, working demos and links).