In my excel worksheet I am using automation to insert formulas like this:
worksheet.Cells[X_index][current_row_index].Formula = "=AC17+ABS(100*$Y$10* COS(((G17+J17+M17+Q17-180)*PI/180)))";
worksheet.Cells[X_index][current_row_index].Formula = "=AC17+ABS(100*$Y$10* COS(Bogenmass(G17+J17+M17+Q17-180)))";
(The function Bogenmass() corresponds to Radians() - who t. f. thought of localizing excel function names? Seriously?)
The problem I have that for some reason the excel com object inserts a '@' character right in front of every reference to PI() or Bogenmass().
I would use "3.1415" instead of "PI()", but for some reason the excel com object keeps converting the decimal '.' separator into ',', although both my windows installation (english) and my localization setting (swiss german) use a decimal '.'!
The MS documentation doesn't help and I am out of ideas how to fix this. Does anyone have an idea what is causing the unwanted '@' insertion and how to prevent it? Alternately, does anyone know how to tell the excel formula interpreter to use the appropriate localization? I've read that the excel com object is always using the default localization (english), but apparently that isn't true!
1. As pointed out in the comments, I actually used "PI" in my formula rather than "PI()". The latter works just fine - no '@' gets inserted. Problem solved.
2. The suggested solution using the FormulaLocal property works just as well in C# as it does in VB. However, when you deliver such a program to a client, you shouldn't do that: it may not work if the client has a different language Excel installation than you do, or if he installs such a version in the future.
3. Something I've missed in my tests using alternately german and english formulas: the english version uses ',' to separate function arguments, whereas german uses ';' (only in Excel formulas). I didn't recognize this as part of the localization, and therefore haven't thought of replacing ';' with ',' when switching to english formulas...
Thanks again to all the contributors who pointed out these valuable bits of information!
What I have tried:
When setting a cell formula in C#, the excel COM formula setter converts:
- "PI()" to "@PI()"
- "Bogenmass()" or "BOGENMASS()" to "@Bogenmass()"
- "3.1415" to "3,1415"
None of these conversions produce a legitimate formula, and the last one even causes an excel exception at runtime!
using "PI()" in a formula gets interpreted as "@PI()" by the excel com object
using "Bogenmass() in a formula gets interpreted