Excel互操作条件格式与公式 [英] Excel interop conditional formatting with formula

问题描述

我有两种情况，我正在使用两台PC进行开发，一台使用Visual Studio 2010，Office 2010，PIA 2010，另一台使用Visual Studio 2010，Office 2007，PIA 2007. 相同的应用程序具有不同的行为，第一个可以正常工作，第二个不能正常工作.

I have two scenario, I'm developing with two pc, one with Visual Studio 2010, Office 2010, PIA 2010 and another PC with Visual Studio 2010, Office 2007, PIA 2007. The same application have different behaviour, the first one works fine, the second one doesn't work.

我需要在电子表格中设置条件格式，但是在Office 2007中它不起作用，因为Excel中的公式是作为字符串而不是公式复制的. 对于两个办公版本，我的代码如下:

I need to set a conditional formatting in a spreadsheet but with office 2007 it doesn't work because the formula in Excel is copy as string and not as formula. My code is the following for both office version:

Excel.Range cellToSet = worksheet.get_Range(startCell, endCell);
Excel.FormatConditions fcs = cellToSet.FormatConditions;
Excel.FormatCondition fc = (Excel.FormatCondition)fcs.Add(Excel.XlFormatConditionType.xlExpression, Type.Missing, "H4>=M4");
string str = fc.Formula1;
Excel.Interior interior = fc.Interior;
interior.Color = ColorTranslator.ToOle(backgroundColor);

在添加调用之后，变量str用于检查公式内容，str返回以下内容:"= \" H4> = M4 \" ，并且工作表无法正常工作. 在条件条件格式/经理规则/显示格式规则菜单下，打开excel文件，我看到: 公式:="H4> = M4" 代替 公式:= H4> = M4 (不包含字符)

The variable str is used to check the formula content after the Add calling, str returns the following: "=\"H4>=M4\"" and the worksheet doesn't work properly. Opening the excel file, under the menu Conditional Formatting / Manager Rules / Show Formatting rules I see: Formula: ="H4>=M4" instead of Formula: =H4>=M4 (without the characters ")

在装有office 2010的PC中，str变量返回"= H4> = M4" (不带字符)"，并且生成的文件可以正常工作.

In the PC with office 2010 the str variable returns "=H4>=M4" (without the characters ") and the generated file works fine.

为什么在Office 2007中此代码不起作用?

Why in office 2007 this code doesn't work?

推荐答案

尝试使用此

Try to used absolute Cell references as described in this article. So instead of:

 "H4>=M4" try "$H$4>=$M$4$".

这篇关于Excel互操作条件格式与公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！