Several thread-related issues in Excel development

Using technologies such as VSTO or shared add-in to develop Excel Plug-ins is actually dealing with the APIs provided with Excel, most of which are COM components, meaning that you interact with COM through an Excel PIA. There are some problems that, if handled poorly, usually cause a COM exception that is difficult to debug at run time, resulting in the instability of the Excel plugin we developed.

Like the normal WinForm program, Excel is an STA (single thread Apartment) thread application, and the Excel plug-in is hosted in Excel, which means that the plug-in is also an STA thread application. Plug-in in the operation of Excel, if it is in the main thread of Excel, you can directly get the Excel object to operate, such as writing cell values, cell formatting, and so on. But usually, we will handle a series of complex data or logic in multithreaded or background work threads, after processing completes obtains the result, again like WinForm, returns to the UI thread, updates the interface information, for the Excel plug-in, is returns to the Excel main thread, And then update the interface. But Excel is also a different type of STA than the general WinForm, it is COM and Excel plug-ins are hosted on it, so there are some areas to be noted.

This article first describes what is the STA application and its working principle, and then introduces the General WinForm program Interface Refresh logic, and in this one of the very important one named SynchronizationContext object, Finally, how to get the Excel main thread in the Excel plug-in, and where you need to pay attention to it.

The most difficult part of Excel Plug-ins is the stability of their applications, and understanding the threads in Excel and their mechanisms can help improve the stability of the system.

1. STA (single Thread Apartment)

The threading model of a COM component is called the Apartment model, which is the execution context (Execution contexts) of a COM object when it is initialized, and either the STA (single thread Apartment) associated with the individual thread, or the MTA associated with multiple threads ( Multi Thread Apartment).

Typically, a COM object needs to be run to ensure that it is not being invoked by multiple threads at the same time in order to protect the data it maintains, and that calls to COM objects do not block the UI thread in addition to the runtime. Apartment is where COM objects live, and a Apartment can contain one or more threads. A call to a COM object can be accepted and processed by any of the threads in the COM's surviving apartment. If there is only one thread in a apartment, then the STA thread, or the MTA, is determined when the program initializes the COM component. A process can contain multiple STA, but only one MTA.

The STA model is a non-thread-safe model used by COM objects, which means that he cannot handle his own thread synchronization and typically uses this model in the UI component. Therefore, if other threads need to interact with the UI object, the message needs to be marshaled (Marshall) to the STA thread. In Windows Forms applications, this process is accomplished through window Message Queuing (pumping system). When the client thread starts in STA mode, a hidden window class is created for the STA, and all calls to the COM object are placed in the message queue of the hidden window.

If a COM object can handle its own synchronization logic, then it is the MTA model, and his multiple threads can interact with the object at the same time without the need for marshaling of message calls.

Which model the COM component uses when it is created can be specified in the ThreadingModel value of the registry key:

The COM component has four properties in the ThreadingModel property in the registry key:

Main thread. The COM object is created on the host program's main UI thread, and all calls must be marshaled to the main UI thread.

Apartment. Indicates that the COM object is capable of running on any but single-threaded model thread, and if the thread is created by an STA thread, the object runs on that STA thread, otherwise the object runs on the main STA thread, and if the main STA thread does not exist, the system automatically creates one.

Free. Indicates that the COM object is running on the MTA.

Both. Indicates that the COM object depends on the type of creation apartment on that model.