On Excel Development (IX.) common problems encountered in Excel development and solutions

Related articles:

On Excel Development (1) Overview of Excel Development

On Excel Development (II.) Excel menu system

On the development of Excel (III.) Excel object model

Excel development process sometimes encounter a variety of strange problems, listed below some of my own in the development of some of the more typical problems, and gave a solution, I hope to help.

A plugin cannot be debugged and the error causes a crash

On the development machine, there may be multiple versions available at times. NET runtime, sometimes it may also have multiple versions of Visual Studio, my development machine installed 3 versions of Visual Studio, after the latest Visual Studio 2013 came out, can't wait to move the project files to 2013, But after setting up the startup program, when debugging, the breakpoint can not hit at all, also debug not go in. Even using the attached to process method is not possible.

The problem is due to multiple installations. NET version of the machine, for some reason, Excel does not know which version of the framework is being loaded for COM add-in. Note: This is only for use. NET technology to develop a shared add-in plug-in, the problem does not exist because the version of the Assembly is indicated in VSTO in the installation deployment file. VSTO.

The workaround is to create a new configuration file named EXCEL.EXE.config, put it in the directory at the same level as the EXCEL.EXE, and configure the following to resolve the problem by creating a new Supprotedruntime node under the Startup node, specifying the version number of the runtime:

In the early days of the shared add-in plug-in development, when testing on a test machine, there were sometimes problems with the error of the code that caused Excel to crash directly. On the development machine, all we need to do is to set the Enable property of the Legacyunhandledexceptionpolicy under the runtime node to true in the configuration file so that Excel pops up a balloon when the problem occurs. Helps to find out why errors occur.

This is in fact one of the drawbacks of the vsto,shared add-in application, one of the VSTO Plug-ins is loaded into its own app domain, and problems do not affect other plug-ins.

Two several Excel method calls can cause an error

Because of the need to be compatible with different versions of Excel, the parameters of the API method that Excel bursts out will change as the version changes. Sometimes a direct call throws an exception when the version is different, and the COM-type exception is generally difficult to handle and recover. In some cases, by adopting a "late binding" approach, a reflection invocation method can solve the problem.

Take the conditional coloring feature for example. In the use of plug-ins to obtain and output data, usually need to be based on the conditions of the cell coloring, for example, the output stock of ups and downs, people's general habit will be marked red, the fall of the mark is green. In Excel, you can use the Formatcoondition object for a range cell condition, which has the following method of coloring the conditions for adding a price, as an example, the code follows.

Object missing = Type.Missing;

FormatCondition Conditionfall = (formatcondition) tmpRange.FormatConditions.Add (Xlformatconditiontype.xlcellvalue, Xlformatconditionoperator.xlless, "=0", missing);

ConditionFall.Font.ColorIndex = 10;//green

FormatCondition conditionrise = (formatcondition) tmpRange.FormatConditions.Add (Xlformatconditiontype.xlcellvalue, Xlformatconditionoperator.xlgreater, "=0", missing);

ConditionRise.Font.ColorIndex = 3;//green

This method has no problem with Excel07 and above, but throws an exception at 03. Because of a version of the API, the number of parameters is different. So using the method of reflection, the parameter can be passed in an array to solve the problem.

The following method encapsulates the Foormationcoondition Add method.

<summary>

Compatible with Excel 2003-excel2010 code condition coloring, if you use the Formatconditions.add method directly, you are not compatible

Details can be referred to: http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/8a91d154-f766-427a-963c-16dfa39e154a

Use methods such as: Temprange.addconditionvalue (xlformatconditionoperator.xlless, "= 0");

</summary>

Range area </param> for <param name= "R" > Pending Conditional coloring

<param name= "Conditionoperator" > Find color conditions, such as, greater than, less than </param>

<param name= "Formula" > Specific value </param>

<returns></returns>

public static FormatCondition Addconditionvalue (Range R, XlFormatConditionOperator conditionoperator, String Formula)

{

Return (FormatCondition) R.formatconditions.gettype (). InvokeMember ("Add", BindingFlags.InvokeMethod, NULL, (object) r.formatconditions, new object[] { Xlformatconditiontype.xlcellvalue, Conditionoperator, Formula});

}