c#中的多语言excel格式条件公式

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.

我正在尝试将一些格式(例如背景色)应用到excel范围的偶数行或奇数行。我在尝试使用格式条件。添加方法。

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:

问题是应用程序必须使用不同的excel语言(如西班牙语)。当我在Add方法中使用公式=MOD(ROW() 2)只对偶数行或奇数行应用格式时，它会抛出一个参数异常。西班牙语:

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.

系统。ArgumentException:该值不在预期区间之间。

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):

问题似乎是公式中的字符。如果我使用;这个异常消失了，但是excel没有转换公式，所以当我用西班牙语打开excel文件时，条件格式不起作用。我必须使用西班牙语的公式=RESIDUO(FILA();2)，但这在英语中行不通。如果a把这个公式放到某个randome cell里，它就会运行得很好(所以我认为这个公式没有错):

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.

“par”是区分偶数和奇数的变量。

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:

和使用类型。定义文化信息的InvokeMember方法:

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.

P。抱歉，我的英语说得不好，希望我解释得还不错。

1 个解决方案

#1

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:

基本的想法是使用一个单元格将公式放在英语中，然后从范围中获取翻译的公式。FormulaLocal属性:

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.

我希望这能对未来的人有所帮助。

#1