6 ms·

Excel formulas in JavaScript

102 points by clxl 12y ago

ozh 12y ago
Well done, but I fail at seeing how that could be useful?
- clxl 12y ago
  Stringing this together with other libraries like http://github.com/warpech/jquery-handsontable http://github.com/warpech/jquery-handsontable and http://github.com/SheetJS/js-xlsx http://github.com/SheetJS/js-xlsx and http://github.com/SheetJS/js-xls http://github.com/SheetJS/js-xls could lead to a better web spreadsheet. EDIT: someone already thought about combining those libraries: http://SheetJS.github.io http://SheetJS.github.io
  - TTPrograms 12y ago
    I've done some work where I've implemented some stuff in code that was originally done via excel formulas (some traders do things that way). I wrote some python to do symbolic substitution on formulas operating over many columns of data to reduce functions to single line definitions over just the data input, making my job much easier. Any programming language implementing the excel formula functions would make it super easy to automate taking prototyped multi-column excel functions to code.
  - SunboX 12y ago
    There's a really nice XLSX reader and writer by Stephen Hardy, a Microsoft developer: https://github.com/stephen-hardy/xlsx.js/ https://github.com/stephen-hardy/xlsx.js/ blog post: http://blog.innovatejs.com/?p=24 http://blog.innovatejs.com/?p=24
    - clxl 12y ago
      I looked at that project but it uses a really strange non-free license that should be avoided. https://github.com/stephen-hardy/xlsx.js/issues/8 https://github.com/stephen-hardy/xlsx.js/issues/8
doppenhe 12y ago
very nice thanks
Robin_Message 12y ago
What licence is this under please? (MIT preferred!)
- nivla 12y ago
  It is included in the js file [1] and seems to be under MIT and Apache. [1] https://raw.githubusercontent.com/sutoiku/formula.js/master/lib/formula.js https://raw.githubusercontent.com/sutoiku/formula.js/master/...
- clxl 12y ago
  It's somewhat confusing. The structure is MIT licenses but the individual implementations have different licenses (for example, many functions were copied from OpenOffice (which is under Apache2 license)
robmcm 12y ago
Is there a demo?
- yaddayadda 12y ago
  Examples are available at: http://www.stoic.com/formula http://www.stoic.com/formula
maartenba 12y ago
Cool! Pretty similar to what www.phpexcel.net does for PHP.
nathell 12y ago
Any support for i18n? (For English-only speakers: Excel's translation to other languages goes as far as translating function names, thus rendering non-English Excel users' knowledge useless when confronted with an English Excel. E.g., IF(ROUND(A1)=1,B1,C1) in English Excel is JEŻELI(ZAOKR.DO.CAŁK(A1)=1,B1,C1) in Polish Excel -- yes, with all those funky non-ASCII letters.)
- dhekir 12y ago
  This also includes changing argument separators according to the locale. In France, for instance, where commas are used as decimal separators, function arguments must be separated by semicolons. Faithful i18n should take both into account.
  - ygra 12y ago
    Luckily this is a UI thing and the files themself normalize to English. So the project could still be useful for opening Excel files on the web (theoretically).
    - Pxtl 12y ago
      Oooh, so if I make a spanish excel file and open it in an english excel, the function names are all translated? That's actually pretty neat. Power of storing your equations in a binary format instead of plain text, I guess.
      - ygra 12y ago
        As far as I know it's still text, but the function names are stored in English and are only localised within the Excel UI. Fun fact: Some old versions of Excel even had a localised VBA ... using ZeichenF instead of String and other oddities made that a very weird experience.
- rjtavares 12y ago
  That's the reason I always use the English version of Office. But then it's so weird when I go help some colleague and they're using the local version, and I'm suddenly a beginner... A remainder of the old world in today's hyper-globalized world.
Aardwolf 12y ago
I've been trying to find which js file has the actual meat of the implementation, but didn't manage to find it. function.js seems the biggest candidate, but didn't contain that much. The rest are all minified other JS libraries or bootstrapping. Any hint on where the actual mapping of names to formulas, and implementation of the formulas, is? Thanks. As for the unimplemented.js formulas, for Gamma you could use the lanczos approximation, it's easy to convert the C code of it which you can find on the internet, to JS.
- frik 12y ago
  https://raw.githubusercontent.com/sutoiku/formula.js/master/lib/formula.js https://raw.githubusercontent.com/sutoiku/formula.js/master/...
- clxl 12y ago
  It primarily strings together other libraries. Most of the functions are provided by other libraries like moment.js and numeral.js
khc 12y ago
This reminds me of my college days. At Statistical Computing Facility at UC Berkeley, a grad student and I (I just finished 1st year at the time) built a web-based spreadsheet for tracking students' homework and exam scores. We called it StatGrades. One feature that it had was that you could write javascript for each column for calculation/validation. I looked at the excel help docs and implemented most of the relevant/useful functions, so people could do something like: AVERAGE(homework1, homework2) * 0.2 + AVERAGE(exam1, exam2) * 0.8 for the final grade. We used rhino for javascript and the service was built on jsp/servlet. It also had access controls so different readers/GSIs had access to different students and columns, and students could see their own grades. Everyone hated Blackboard and loved what we built, but unfortunately they couldn't find anyone to maintain it after I graduated (I was the only person working on it shortly after I joined). The service was shutdown a couple years after I left.
brudgers 12y ago
I honestly admire the thought and effort that went into it. I fear that JavaScript's use of floating point as its single numeric type is likely to create surprising results for the unwary. I have come to the suspicion that the longevity of Excel's dominance is in part due to a sophisticated system for inferring numeric types.
- clxl 12y ago
  Excel only supports numbers that fit in an IEEE754 double, just like JavaScript. There is some fancy logic to format numbers, however (including some mess for date codes). http://oss.sheetjs.com/ssf/ http://oss.sheetjs.com/ssf/ is a pretty decent implementation in JavaScript.
  - brudgers 12y ago
    All versions of Excel have native support for integers. http://msdn.microsoft.com/en-us/library/office/bb687869%28v=office.15%29.aspx http://msdn.microsoft.com/en-us/library/office/bb687869%28v=...
    - clxl 12y ago
      Excel supports 32 bit integers, which fit comfortably in IEEE754 doubles (there are 64 bit integers which cannot be exactly represented)
- dragonwriter 12y ago
  > I have come to the suspicion that the longevity of Excel's dominance is in part due to a sophisticated system for inferring numeric types. Huh? Doesn't Excel just treat everything computationally as doubles and sometimes display them as integers? As far as "sophisticated systems" for dealing with numeric types go, that's fairly weak sauce (and quite easily doable in JavaScript, which already does the first part).
- DanBC 12y ago
  Excel is dominant because it runs on Windows and is supplied with MS Office. Many people would be fine to use other spreadsheet software but why bother going through the bother of researching and downloading and learning that software when your company probably has Excel licences and you've got some entry level training that used Excel? That dominance has other effects. There is a huge amoun of support (training courses; books; Usenet groups and forums; Magazine articles; other people in the office; etc) for Excel that don't translate immediately to Excel. There are very many third party tools to help you do stuff in Excel if you really need it to. And Excel is probably some people's first introduction to programming. (Which is perhaps a bit scary.) I speak as someone who loves gnumeric and LibreOffice btw.
dhotson 12y ago
Very cool! Slightly related... I reckon a command line / ncurses version of Excel would be really useful. Am I crazy? e.g. something like http://upload.wikimedia.org/wikipedia/en/e/ec/Lotus-123-3.0-dos.png http://upload.wikimedia.org/wikipedia/en/e/ec/Lotus-123-3.0-... Spreadsheets are a pretty useful and powerful tool when it comes to doing ad-hoc data analysis, but it's kind of annoying to get data in and out of them from command line tools.
- clxl 12y ago
  I'm actually working on this, although for various reasons I had to reimplement the formula parser (corner cases are a pain). Screenshot (in progress): http://i.imgur.com/snyBhYH.png http://i.imgur.com/snyBhYH.png
- Someone 12y ago
  Just download VisiCalc: http://www.bricklin.com/history/vcexecutable.htm http://www.bricklin.com/history/vcexecutable.htm I have no idea how easy it is to pipe input into and out of it, but it certainly want designed for it.
- DanBC 12y ago
  Fat-finger errors are already terrifying in spreadsheets. How would you help prevent them in command line tools?
  - dhotson 12y ago
    Isn't that true for a lot of tools? Including programming languages?
- Raphael 12y ago
  R?
- dmd 12y ago
  GNU Oleo has a curses mode.
  - 12y ago
chiph 12y ago
They even included IRR, which is pretty impressive.
- personZ 12y ago
  IRR is one of those things that is remarkably simple to calculate and grok, but is made difficult in the number of horrendous instructions across the web, many of them simply wrong. Until I corrected it the NPV entry on Wikipedia (NPV being a dependency of IRR) sat with incorrect input / result data. It all makes me think that some in the financial industry think this is some sort of trade secret.
- deleted 12y ago
  [deleted]
baconner 12y ago
This plus something like crossfilter and you'll really be able to build.some exciting in browser data exploration apps. Cool stuff.

102 points by clxl 12y ago

ozh 12y ago

Well done, but I fail at seeing how that could be useful?

clxl 12y ago
Stringing this together with other libraries like http://github.com/warpech/jquery-handsontable http://github.com/warpech/jquery-handsontable and http://github.com/SheetJS/js-xlsx http://github.com/SheetJS/js-xlsx and http://github.com/SheetJS/js-xls http://github.com/SheetJS/js-xls could lead to a better web spreadsheet. EDIT: someone already thought about combining those libraries: http://SheetJS.github.io http://SheetJS.github.io
- TTPrograms 12y ago
  I've done some work where I've implemented some stuff in code that was originally done via excel formulas (some traders do things that way). I wrote some python to do symbolic substitution on formulas operating over many columns of data to reduce functions to single line definitions over just the data input, making my job much easier. Any programming language implementing the excel formula functions would make it super easy to automate taking prototyped multi-column excel functions to code.
- SunboX 12y ago
  There's a really nice XLSX reader and writer by Stephen Hardy, a Microsoft developer: https://github.com/stephen-hardy/xlsx.js/ https://github.com/stephen-hardy/xlsx.js/ blog post: http://blog.innovatejs.com/?p=24 http://blog.innovatejs.com/?p=24
  - clxl 12y ago
    I looked at that project but it uses a really strange non-free license that should be avoided. https://github.com/stephen-hardy/xlsx.js/issues/8 https://github.com/stephen-hardy/xlsx.js/issues/8

doppenhe 12y ago

very nice thanks

Robin_Message 12y ago

What licence is this under please? (MIT preferred!)

nivla 12y ago
It is included in the js file [1] and seems to be under MIT and Apache. [1] https://raw.githubusercontent.com/sutoiku/formula.js/master/lib/formula.js https://raw.githubusercontent.com/sutoiku/formula.js/master/...
clxl 12y ago
It's somewhat confusing. The structure is MIT licenses but the individual implementations have different licenses (for example, many functions were copied from OpenOffice (which is under Apache2 license)

robmcm 12y ago

Is there a demo?

yaddayadda 12y ago
Examples are available at: http://www.stoic.com/formula http://www.stoic.com/formula

maartenba 12y ago

Cool! Pretty similar to what www.phpexcel.net does for PHP.

nathell 12y ago

Any support for i18n? (For English-only speakers: Excel's translation to other languages goes as far as translating function names, thus rendering non-English Excel users' knowledge useless when confronted with an English Excel. E.g., IF(ROUND(A1)=1,B1,C1) in English Excel is JEŻELI(ZAOKR.DO.CAŁK(A1)=1,B1,C1) in Polish Excel -- yes, with all those funky non-ASCII letters.)

dhekir 12y ago
This also includes changing argument separators according to the locale. In France, for instance, where commas are used as decimal separators, function arguments must be separated by semicolons. Faithful i18n should take both into account.
- ygra 12y ago
  Luckily this is a UI thing and the files themself normalize to English. So the project could still be useful for opening Excel files on the web (theoretically).
  - Pxtl 12y ago
    Oooh, so if I make a spanish excel file and open it in an english excel, the function names are all translated? That's actually pretty neat. Power of storing your equations in a binary format instead of plain text, I guess.
    - ygra 12y ago
      As far as I know it's still text, but the function names are stored in English and are only localised within the Excel UI. Fun fact: Some old versions of Excel even had a localised VBA ... using ZeichenF instead of String and other oddities made that a very weird experience.
rjtavares 12y ago
That's the reason I always use the English version of Office. But then it's so weird when I go help some colleague and they're using the local version, and I'm suddenly a beginner... A remainder of the old world in today's hyper-globalized world.

Aardwolf 12y ago

I've been trying to find which js file has the actual meat of the implementation, but didn't manage to find it. function.js seems the biggest candidate, but didn't contain that much. The rest are all minified other JS libraries or bootstrapping. Any hint on where the actual mapping of names to formulas, and implementation of the formulas, is? Thanks. As for the unimplemented.js formulas, for Gamma you could use the lanczos approximation, it's easy to convert the C code of it which you can find on the internet, to JS.

frik 12y ago
https://raw.githubusercontent.com/sutoiku/formula.js/master/lib/formula.js https://raw.githubusercontent.com/sutoiku/formula.js/master/...
clxl 12y ago
It primarily strings together other libraries. Most of the functions are provided by other libraries like moment.js and numeral.js

khc 12y ago

This reminds me of my college days. At Statistical Computing Facility at UC Berkeley, a grad student and I (I just finished 1st year at the time) built a web-based spreadsheet for tracking students' homework and exam scores. We called it StatGrades. One feature that it had was that you could write javascript for each column for calculation/validation. I looked at the excel help docs and implemented most of the relevant/useful functions, so people could do something like: AVERAGE(homework1, homework2) * 0.2 + AVERAGE(exam1, exam2) * 0.8 for the final grade. We used rhino for javascript and the service was built on jsp/servlet. It also had access controls so different readers/GSIs had access to different students and columns, and students could see their own grades. Everyone hated Blackboard and loved what we built, but unfortunately they couldn't find anyone to maintain it after I graduated (I was the only person working on it shortly after I joined). The service was shutdown a couple years after I left.

brudgers 12y ago

I honestly admire the thought and effort that went into it. I fear that JavaScript's use of floating point as its single numeric type is likely to create surprising results for the unwary. I have come to the suspicion that the longevity of Excel's dominance is in part due to a sophisticated system for inferring numeric types.

clxl 12y ago
Excel only supports numbers that fit in an IEEE754 double, just like JavaScript. There is some fancy logic to format numbers, however (including some mess for date codes). http://oss.sheetjs.com/ssf/ http://oss.sheetjs.com/ssf/ is a pretty decent implementation in JavaScript.
- brudgers 12y ago
  All versions of Excel have native support for integers. http://msdn.microsoft.com/en-us/library/office/bb687869%28v=office.15%29.aspx http://msdn.microsoft.com/en-us/library/office/bb687869%28v=...
  - clxl 12y ago
    Excel supports 32 bit integers, which fit comfortably in IEEE754 doubles (there are 64 bit integers which cannot be exactly represented)
dragonwriter 12y ago
> I have come to the suspicion that the longevity of Excel's dominance is in part due to a sophisticated system for inferring numeric types. Huh? Doesn't Excel just treat everything computationally as doubles and sometimes display them as integers? As far as "sophisticated systems" for dealing with numeric types go, that's fairly weak sauce (and quite easily doable in JavaScript, which already does the first part).
DanBC 12y ago
Excel is dominant because it runs on Windows and is supplied with MS Office. Many people would be fine to use other spreadsheet software but why bother going through the bother of researching and downloading and learning that software when your company probably has Excel licences and you've got some entry level training that used Excel? That dominance has other effects. There is a huge amoun of support (training courses; books; Usenet groups and forums; Magazine articles; other people in the office; etc) for Excel that don't translate immediately to Excel. There are very many third party tools to help you do stuff in Excel if you really need it to. And Excel is probably some people's first introduction to programming. (Which is perhaps a bit scary.) I speak as someone who loves gnumeric and LibreOffice btw.

dhotson 12y ago

Very cool! Slightly related... I reckon a command line / ncurses version of Excel would be really useful. Am I crazy? e.g. something like http://upload.wikimedia.org/wikipedia/en/e/ec/Lotus-123-3.0-dos.png http://upload.wikimedia.org/wikipedia/en/e/ec/Lotus-123-3.0-... Spreadsheets are a pretty useful and powerful tool when it comes to doing ad-hoc data analysis, but it's kind of annoying to get data in and out of them from command line tools.

clxl 12y ago
I'm actually working on this, although for various reasons I had to reimplement the formula parser (corner cases are a pain). Screenshot (in progress): http://i.imgur.com/snyBhYH.png http://i.imgur.com/snyBhYH.png
Someone 12y ago
Just download VisiCalc: http://www.bricklin.com/history/vcexecutable.htm http://www.bricklin.com/history/vcexecutable.htm I have no idea how easy it is to pipe input into and out of it, but it certainly want designed for it.
DanBC 12y ago
Fat-finger errors are already terrifying in spreadsheets. How would you help prevent them in command line tools?
- dhotson 12y ago
  Isn't that true for a lot of tools? Including programming languages?
Raphael 12y ago
R?
dmd 12y ago
GNU Oleo has a curses mode.
- 12y ago

chiph 12y ago

They even included IRR, which is pretty impressive.

personZ 12y ago
IRR is one of those things that is remarkably simple to calculate and grok, but is made difficult in the number of horrendous instructions across the web, many of them simply wrong. Until I corrected it the NPV entry on Wikipedia (NPV being a dependency of IRR) sat with incorrect input / result data. It all makes me think that some in the financial industry think this is some sort of trade secret.
deleted 12y ago
[deleted]

baconner 12y ago

This plus something like crossfilter and you'll really be able to build.some exciting in browser data exploration apps. Cool stuff.