joshbtn / excelformulautilitiesjs Goto Github PK

=SUM(A:A) generates (A

I did try example code. excelFormulaUtilities.formula2Javascript( "" ); got from basic_example1/index.html. But it's didn't working for me. it's said "Uncaught TypeError: excelFormulaUtilities.formula2Javascript is not a function" What can i do?

Such as

Math.abs(n)
Math.acos(n)
Math.asin(n)
Math.atan(n)
Math.atan2(n,n2)
Math.ceil(n) 
Math.cos(n)
Math.exp(n)
Math.floor(n)
Math.log(n)
Math.max(n,n2)
Math.min(n,n2) 
Math.pow(n,n2) 
Math.random()
Math.round(n)
Math.sin(n) 
Math.sqrt(n)
Math.tan(n)

Hello,

i tried your class, but is it right, that it only calculates results. I can not see any line of Javacript Code. Could you perhaps help me?

Thanks,
Regards,
Olaf

Hi,

First: Thanks for a great tool 👍

Second: could you add a "feature" so that the semicolon acts the same as comma?
After copy/pasting formula from excel, I have to manually change all semicolons to commas for the beautify to work...

Hi, I have include dist folder in my project and tried to use the concatenated file. But it seems like core.js file is appended after ExcelFormulaUtils.js. Because of that when i include dist/excel-formula.js.
it gives error
Uncaught TypeError: Cannot read property 'formatStr' of undefined
(anonymous function) @ excel-formula.js:19
(anonymous function) @ excel-formula.js:1191

Seems like a build step issue.

I have an Excel function which reads =IF(Q11="","",Q11*S11) that I'm trying to convert to JavaScript in an Adobe Acrobat Form. In the Form, Q11 is labeled "QuantityRow1", S11 is labeled "AmountRow1" with the Function residing in "TotalRow1" I have the same function duplicated for 16 rows which then total up all the sub totals in a simple sum formula that Adobe accepts. I'm assuming that somehow I need to put those labels into the JavaScript. Is that correct, and if so, what is the correct syntax. TIA

A total brainsaver, this tool! Thanks.

I'd request the addition of a copy to clipboard button, for easy copying of the extended beautify output. Sometimes it's easier to print and scribble for working stuff out, and a copy button would facilitate quick transfer to a text editor.

Maybe I am just dumb (please, tell me :)), but when I enter

=CONCATENATE('A', 'B')

and convert it to JavaScript, it becomes

CONCATENATE(A,B)

This is not valid Javascript, how come?

Hitting some JavaScript error, no output is being generated for any formulas I type.

Error message below:

SCRIPT438: Object doesn't support property or method 'parseInt'
File: beautifier.min.js, Line: 1, Column: 1488

=MAX(A1,A2) is converted to Math.Max(A1,A2). Shouldn't it be Math.max(A1,A2)?

If I have any HTML tags in my formulas (between quotation marks, naturally), they're displayed as if they're part of the page source code. So, if I have a formula that says:

="<a href=#>test</a>

It will show up like:

test

Error as reported here, http://maran-emil.de/nodes/7-tools-for-better-excel-into-javascript-formula-conversion

=ROUND((P12/100+1)^(1/P13-1),6)

Expected: ROUND(Math.Pow(P12 / 100 + 1, 1 / P13 - 1)), 6)

Hello,
When I paste this formula:
=IF(EXACT(A1,VLOOKUP(A1,Sheet1!A:A,1,FALSE))=TRUE,VLOOKUP(A1,Sheet1!A:A,1,FALSE),"No exact match")

...into http://excelformulabeautifier.com/, the resulting "FALSE"s have no indentation. And the "= TRUE" seems weird.

What do you think?

Thanks,
Simon

trailing space after = in a formula causes a null exception

Thanks for the great tool this has been super helpful, it would be really nice to have a tool to reverse the operation and turn it back into a single formula

Many thanks

I am currently working on an Angular 2 app which uses npm as its dependency package manager. Seems like dist is there in npmignore, So not able to use the library directly on my web app without a build step.

Could you remove the forced line-break on http://excelformulabeautifier.com/ after ~140 chars?
I got some very nasty formulas and to me it seems a bit arbitrary to do additional line-breaks with no functional meaning.

Otherwise a very nice tool, helps a lot.
Thanks,
Hannes

Add un-beautify code option.

Sum(A1, B1, C1);

Should actually become

A1 + B1 + C1

Since Sum and Math.Sum are not built in to JavaScript

Hi,

I need to handle Roundup & down. Is there any written reference of the places I'd need to hit? Or, is there a current function/code checkin that I could base my changes on?

Thank you for this useful library!

Now that #13 is merged, could you publish this lib to npm?

excel doesn't require you to specify the else clause resulting in invalid javascript...

IF("foo" = "foo", "foo")

converts to

("foo"==="foo"?"foo")

If you turn on "Show Formulas" in Excel, you can see a bunch of formulas at once.

It would be great if you could then "beautify" or convert them to javascript as a batch.

For example, if you have the following 2 x 2 arrangement of formulas...

=SUM(D1,E1) =SUM(E1,F1)
=SUM(D2,E2) =SUM(E2,F2)

...doing a conversion to javascript should produce:

D1 + E1
E1 + F1
D2 + E2
E2 + F2

Hi,
when parsing a formula like A5 or =A1+B1 or anything that does not start with a function I get an error:

node_modules/excel-formula/src/ExcelFormulaUtilities.js:1117
                        switch (currentFunctionOnStack.name.toLowerCase()) {
                                                      ^

TypeError: Cannot read property 'name' of undefined

I am teaching Excel and was looking for easier display for complicated Excel Formulas!
This is amazing!! Well done & thank you!

=IF(A149="","",MAX(IF(Input!$E$11+BI!AB149>Input!$K$3,0,OFFSET(Proj!$M$1,BI!AB149*12,0))+IF(Input!$E$11+BI!AB149>Input!$J$74,0,Input!$I$74)+IF(AB149>Input!$K$76,0,ROUND((1-(1+Input!RT_INT)^-(Input!RT_term-AB149+1))/(1-(1+Input!RT_INT)^-Input!RT_term),4)*Input!SA_RT),ROUND(OFFSET(Proj!$CH$1,BI!AB149*12,0),-2)+IF(Input!$E$11+BI!AB149>Input!$J$74,0,Input!$I$74)+IF(AB149>Input!$K$76,0,ROUND((1-(1+Input!RT_INT)^-(Input!RT_term-AB149+1))/(1-(1+Input!RT_INT)^-Input!RT_term),4)*Input!SA_RT)))

It does't indent the -2 in ROUND properly unless it's a positive number.

Ranges don't convert to JavaScript, instead just copies the range over. For example SUM(A1:A3) => (A1:A3) instead of (A1 + A2 + A3).

I'd like to have a drop down to configure:

Indent with tabs or spaces?
Then: How many spaces?

Please!

Great tool!

Need to update tests to reflect tweak to formatFormulaHTML(formula). Functionality works but Tests are all failing.

http://excelformulabeautifier.com/ seems to be broken at the moment in Chrome.

Console output:

Refused to execute script from 'https://raw.github.com/joshatjben/excelFormulaUtilitiesJS/master/excelFormulaUtilities-0.9.41.min.js' because its MIME type ('text/plain') is not executable, and strict MIME type checking is enabled. excelformulabeautifier.com/:1
Uncaught TypeError: undefined is not a function 

There's a simple solution if you don't mind relying on a 3rd party, just remove the period from the URL, i.e.:
https://rawgithub.com/joshatjben/excelFormulaUtilitiesJS/master/excelFormulaUtilities-0.9.41.min.js

This is provided by http://rawgithub.com/

❤️

Would be nice if you could edit the formula while it is formatted

When the formula is extracted from Excel in Portuguese, the semicolon are converted to comma.

Test this: =SE(E7="";"";SE(E(E7=0;D7=0);1;(SE(E(D7<0;E7<0;D7>E7);ABS(E7/D7)(-1)+1;(SE(E(D7<0;E7<0;D7<E7);ABS(D7-E7)/(D7)(-1)+1;(SE(E(E7=0;D7<0);(D7/D7)+1;(SE(D7=0;(E7*1);SE(E(E7=0;D7>0);(D7/D7_-1);(SE(E(E7>0;D7<0);(D7/D7)+ABS(E7/D7)+1;(SE(E(E7<0;D7>0);(D7/D7)_(-1)+(E7/D7);E7/D7)))))))))))))))

Result:

=SE(
E7 = ""
),
{ ""
),
{ SE(
E(
E7 = 0
),............................................................

Try =IF(SUM( IF(FOO == BAR, 10, 0), 10 ) = 20 , "FOO", "BAR")

I'm trying to understand an expression with lots of brackets, so was hoping this tool would break the expression onto multiple lines with indentation to show the different levels of nesting.

Just a thought.

E.g. of a longish expression I'm currently looking at:

= (1+exp) * (B23+B25 - (11/24) * $I$2 + B5_(B28-(11/24) * $P$2-B31+(11/24)_$U$2))

First, thanks for making this very useful tool!

It could be even more useful if the beautified formula could be edited and then converted back to the one-line version. It looks like there just needs to be some white-space removal done, so hopefully this would be easy to implement.

Hi

I think it would be great, if one could also fold indented parts of formula. This would be especially useful for text formulas, that mostly receive text as first argument (which may be nested formula) and numbers as later arguments. Then user could just hide nested part and see all arguments at glance.

I am using excelFormulaUtilities.formula2JavaScript('=min(2,3)') which gives me Math.Min(2,3)
But there's no function by this name. It should give me Math.min and not Math.Min.
Same is for the MIN & ABS.

Great programs - thanks for sharing

only problem i have is when you have concurrenct commas.

eg with the code =IF(SUM( If(FOO = BAR, 10,,,,,,,,, 0), 10 ) = 20 , "FOO", "BAR") you will see that the commas are not tabbed across

Otherwise thanks for this.

Kind regards

Andrew

General Comments

It would be super-useful if the "beautified" output was of a form that could be cut-and-paste back into Excel.

• preserve the leading '='
• don't separate a function name from the following '('

This last implies a different indentation style of course!

"SUM((A1:A10>=5)_(A1:A10<=10)_A1:A10)" outputs

SUM ( A1:A10>= 5 )* ( A1:A10<= 10 )* A1:A10 )

There should be a space between all ) and *

Love the Beautify excel tool, really nice and simple, thanks for designing it!

Just thought it would be cool if instead of having the function fully laid out, you could expand different parts of it to reveal deeper layers of the formula, just not all at once. Like a reddit post when you click the [+] to expand.

Beautifier turns 'Data Sheet'!$D3 to DataSheet!$D3 breaking links to other sheets.

=C3 (Yearly Gross Income)
=C4 (Number of people in Houshold)
Answer is (B6) =MAX((C3-((11490+(C4-1)_4020)_1.5))*0.1/12,0)

formula2CSharp and formula2Javascript removes all spaces, even in strings.

Cute website!

You are not properly parsing range intersections:

=SUM((A1:C3 B2:D4))

The formula is evaluated as =SUM((B2:C3))

If you want to see the formula building blocks, consult MS-XLS Ptg* records. XLS files don't store the string version of the formulas, which is why the formula spec is laid out. Relevant code block from js-xls: https://github.com/SheetJS/js-xls/blob/master/bits/68_fparse.js#L114-L337