2

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")
Mottor
  • 1,938
  • 3
  • 12
  • 29
  • Just a guess, but I think you are modifying cell text instead of formulae. Try digging into formulas property of individual cells. – ttaaoossuuuu Jul 20 '16 at 14:38

1 Answers1

1

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.

Michael Zlatkovsky - Microsoft
  • 8,424
  • 1
  • 33
  • 46
  • For ranges, this is correct, but the API does *not* seem to be language-agnostic with respect to the `formula` property of defined names. While the `formulas` property of a range does give you the English version of the formula irrespective of the locale, the `formula` property of a defined name gives you the localized version. There seems to be no way of getting the English version for a defined name. I now use the `formulas` / `formulasLocal`properties of a range in a hidden worksheet for translation. That is a terrible solution, of course. I think this is a design flaw in the API. – JohnB Sep 11 '22 at 05:47
  • 1
    @JohnB, I have left the extensibility team several years ago. For the issue you describe, I would recommend filing a bug on https://github.com/OfficeDev/office-js/issues – Michael Zlatkovsky - Microsoft Sep 11 '22 at 17:17
  • Thank you, I've filed a bug report now. – JohnB Sep 13 '22 at 16:09