Multilanguage excel format conditions formula in c#

Question

It's my first question here so I hope I'm doing everything ok.

I'm trying to apply some format (background color, for example) to the even or odd rows of an excel range. I'm trying to use the FormatCondition.Add method.

The problem is that the application must work in diferent excel languages (like spanish). And when I use the formula =MOD(ROW(),2) in the Add method to apply the format only to even or odd rows it throws me an argument exception. Here in spanish:

System.ArgumentException: El valor no está dentro del intervalo esperado.

And here my best translation of the exception:

System.ArgumentException: The value isn't between the expected interval.

The problem seems to be the , character in the formula. If I use ; the exception disappears, but then excel doesn't translate the formula so when I open the excel file in spanish the conditional format doesn't work. I must use the spanish version of the formula =RESIDUO(FILA();2) but that won't work in english. And if a put the formula in some randome cell it works well (so I suppose the formula isn't wrong):

excelSheet.Cells[1, 1] = "=MOD(ROW(),2)";

My question is: How can I use multilanguage formula in FormatConditions.Add method?

My code looks like this:

excelRange = excelRange.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression,
                                             Formula1: "=MOD(ROW(),2)=" + par);

"par" is a variable to distinguish between even or odd.

I've tried changing the thread culture:

System.Globalization.CultureInfo before = System.Threading.Thread.CurrentThread.CurrentCulture;
            try
            {
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                excelRange = excelRange.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Formula1: "=MOD(ROW(),2)=" + par);
            }
            finally
            {
                System.Threading.Thread.CurrentThread.CurrentUICulture = before;
            }

and using the Type.InvokeMember method to define the culture info:

Excel.FormatConditions aux = excelRange.FormatConditions;
Object[] args = { Excel.XlFormatConditionType.xlExpression, Type.Missing, "=MOD(ROW(),2)=" + par };
excelRange = aux.GetType().InvokeMember("Add",
                                        System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public,
                                         null,
                                         aux,
                                         args,
                                         culture: new System.Globalization.CultureInfo("en-US"));

But the result is the same always.

If you have some clue to help me it'll be very appreciated. Thanks to all.

P.S: Sorry for my english, I hope I've explained it not too bad.

score 2 · Accepted Answer · edited May 23 '17 at 12:16

2

I've found the solution (not the best solution I think, but anyway...).I took the idea from here

The basic idea is to use a single cell to put the formula in English and then get the translated formula from the Range.FormulaLocal property:

Excel.Range temp = excelSheet.get_Range("a1");
dynamic tempValue = temp.Value2;
temp.Formula = "=MOD(ROW(),2)=" + par;
String formula = temp.FormulaLocal;
temp.Formula = "";
temp.Value2 = tempValue;
excelRange = excelRange.FormatConditions.Add(Excel.XlFormatConditionType.xlExpression, Formula1: formula);

I hope this can help someone in the future.

edited May 23 '17 at 12:16

Community

1
1

answered Feb 12 '16 at 13:36

Ion Gorostizu

116
7

Thank you for this! I had the same problem, it is really inconsistent and stupid that for conditional formulas and data validation, the local format must be used. – explorerDude Nov 08 '22 at 20:05

Multilanguage excel format conditions formula in c#

1 Answers1