What I do:
var myTable = new Office.TableData();
.....
var y = [];
y.push(...);
.....
y.push('=SUM(Test[@[364]:[0]])/365');
y.push(...);
.....
myTable.rows.push(y);
and later
binding.addRowsAsync(myTable.rows, function (addResult){....});
Now everything is OK, if the user uses English in Excel. But if he uses a German, there comes "#NAME?", because in German SUM have to be SUMME. How to write a formula from Javascript Api, that is independent from the user language?
Or may be the question is: Can I write (in a cell) a formula in English in an Excel with German as standard language?
Edit:
I will mark the Michael Zlatkovsky's answer, because it is working. But I am not using it. The problem is that I am getting a lot of "The argument is invalid or missing or has an incorrect format."
I have asked the same question in the Office Dev Center forum and the suggestion there was to use Context.displayLanguage or Context.contentLanguage to find the language and use different formulas. But this cannot be universal.
What I am using now is to write the formulas in some sheet and some cells (hidden) and then to use FORMULATEXT in other cell to get the correct formula. For example if in cell P2 is this formula:
=COUNTIF(Test[@[364]:[0]];"<=0")
and in P3:
=FORMULATEXT(P2)
you get after the excel sheet is loaded, the formula in the right language and I am using it direct in the table. And despite the errors in the formula it is translated. Here in German:
=ZĂ„HLENWENN(Test[@[364]:[0]];"<=0")
The Office.js Excel APIs (like VBA before them) are meant to be language/locale agnostic. That is, when you set a formula to range.formulas
, it's designed to only interpret ENGLISH input (which is why we also have range.formulasLocal
, corresponding to locale-specific versions of the formulas). So the behavior your describe, of setting SUM
and expecting the German Excel to interpret it as SUMME
is absolutely what it should be.
A couple questions to help us investigate this:
1) Instead of setting the formula via a table value, could you try setting range.formulas
?
2) If that works, what about setting range.values = yourFormula
? I assume that this should work just like #1, but just wanted to be sure.