Excel formulas in JavaScript • Hacker News Board

Re: Excel formulas in JavaScript

#11

Post by ygra » Thu, Apr 24, 2014, 9:21 AM UTC

dhekir wrote: post #10

nathell wrote: post #9

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.)

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.

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).

Re: Excel formulas in JavaScript

#12

Post by rjtavares » Thu, Apr 24, 2014, 9:36 AM UTC

nathell wrote: post #9

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.)

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.

Re: Excel formulas in JavaScript

#13

Post by TTPrograms » Thu, Apr 24, 2014, 9:38 AM UTC

clxl wrote: post #3

ozh wrote: post #2

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

Stringing this together with other libraries like http://github.com/warpech/jquery-handsontable and http://github.com/SheetJS/js-xlsx and 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

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.

Re: Excel formulas in JavaScript

#14

Post by SunboX » Thu, Apr 24, 2014, 9:41 AM UTC

clxl wrote: post #3

ozh wrote: post #2

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

Stringing this together with other libraries like http://github.com/warpech/jquery-handsontable and http://github.com/SheetJS/js-xlsx and 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

There's a really nice XLSX reader and writer by Stephen Hardy, a Microsoft developer: https://github.com/stephen-hardy/xlsx.js/ blog post: http://blog.innovatejs.com/?p=24

Re: Excel formulas in JavaScript

#15

Post by Aardwolf » Thu, Apr 24, 2014, 10:09 AM UTC

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.

Re: Excel formulas in JavaScript

#16

Post by frik » Thu, Apr 24, 2014, 10:16 AM UTC

Aardwolf wrote: post #15

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 l…

https://raw.githubusercontent.com/sutoiku/formula.js/master/...

Re: Excel formulas in JavaScript

#17

Post by swah » Thu, Apr 24, 2014, 10:53 AM UTC

rjtavares wrote: post #12

nathell wrote: post #9

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.)

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.

And it makes auto completion useless. You type 'SU' and stare the screen "WTF?". Oh, this Excel is in PT-BR! 'SOMA'

Re: Excel formulas in JavaScript

#18

Post by rjtavares » Thu, Apr 24, 2014, 11:03 AM UTC

swah wrote: post #17

rjtavares wrote: post #12

Earlier quoted context omitted.

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.

And it makes auto completion useless. You type 'SU' and stare the screen "WTF?". Oh, this Excel is in PT-BR! 'SOMA'

And they even change conventions. "SUMIF" must be "SOMASE", right? Wrong, in PT there's a "." between words, so it's "SOMA.SE"...

Re: Excel formulas in JavaScript

#19

Post by dhekir » Thu, Apr 24, 2014, 11:18 AM UTC

rjtavares wrote: post #18

swah wrote: post #17

Earlier quoted context omitted.

And it makes auto completion useless. You type 'SU' and stare the screen "WTF?". Oh, this Excel is in PT-BR! 'SOMA'

And they even change conventions. "SUMIF" must be "SOMASE", right? Wrong, in PT there's a "." between words, so it's "SOMA.SE"...

Not only PT, that happens in most languages where the dot is not the decimal separator. It might have been the case in English as well if they used commas.

Re: Excel formulas in JavaScript

#20

Post by khc » Thu, Apr 24, 2014, 12:07 PM UTC

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.