@ added to formula in html-to-xlsx recipe

[jcschmidig]

I use the formula

=LOOKUP(2,1/(INDIRECT(CONCATENATE("'",INDIRECT(CONCATENATE("Z",ROW(),"S1"),FALSE),"'!D:D"))<>""),INDIRECT(CONCATENATE("'",INDIRECT(CONCATENATE("Z",ROW(),"S1"),FALSE),"'!D:D")))

to get some data from another location.
After the conversion by html-to-xlsx recipe it looks like (I use a german version of excel)

=VERWEIS(2;1/(INDIREKT(VERKETTEN("'";INDIREKT(VERKETTEN("Z";ZEILE();"S1");FALSCH);"'!D:D"))<>"");INDIREKT(VERKETTEN("'";@INDIREKT(VERKETTEN("Z";ZEILE();"S1");FALSCH);"'!D:D")))

As you can see, a @ has been added to the last INDIREKT. This prevents the formula from being evaluated.
What is the problem? How can I prevent it?

Thanks for listening!

[bjrmatos]

hi! generally, i don't know what can be causing that extra @ character in the formula, there is a lot of logic around formulas so this needs to be debugged, can you share a playground demo replicating the issue?

[jcschmidig]

Thanks for your reply!
Here's the playground for it: https://playground.jsreport.net/w/anon/YEABx0bl

[bjrmatos]

i have checked the template but we are not doing nothing on our side to change your formula, the output for me looks like this:

=LOOKUP(2,1/(INDIRECT(CONCATENATE("'",INDIRECT(CONCATENATE("Z",ROW(),"S1"),FALSE),"'!D:D"))<>""),INDIRECT(CONCATENATE("'",INDIRECT(CONCATENATE("Z",ROW(),"S1"),FALSE),"'!D:D")))

and in MS excel (which it displays according to my local, in Spanish)

=BUSCAR(2;1/(INDIRECTO(CONCATENAR("'";INDIRECTO(CONCATENAR("Z";FILA();"S1");FALSO);"'!D:D"))<>"");INDIRECTO(CONCATENAR("'";INDIRECTO(CONCATENAR("Z";FILA();"S1");FALSO);"'!D:D")))

i don't see the @ being added

however what i've noticed is that internally the formulas are being html escaped, this is likely caused by your custom formula formula helper, i recommend you to change the formula helper call to use triple {{{, update your calls to {{{formula ....}}} maybe this fix the way your MS Excel parse the formula

[jcschmidig]

Thanks for looking!
I updated the playground to not use the {{formula ...}} helper and the result is the same.
Could it be a 'german' thing?

[bjrmatos]

maybe, but it would be weird that MS Excel changes something just because the locale, can you email me your generated xlsx file? (bjrmatos@gmail.com) i just want to double-check if what is stored internally for your output matches the same for what i see in the output of playground.

[jcschmidig]

I just sent you the file. Thx.

[bjrmatos]

i am checking the xlsx you sent me, but even after changing my language to German i still can not see the @ character.

any other step you do that reveals such character?

[jcschmidig]

It's all in the playground ?! I can't imagine what the difference comes from.
What does the error message (at the exclamation triangle) say?

[bjrmatos]

What does the error message (at the exclamation triangle) say?

i think that is translated to "Error: The value is not available"

[jcschmidig]

The strange thing is that the formula as shown is correct, but there must be some thing not shown here.
Anyway let us close it.I'll find another way to achieve my goal.
Thanks a lot for your help!