如何在Excel VSTO自定义Ribbon中强制UI线程执行写入逻辑？

问题原因 #

Excel的COM对象（包括Application.ScreenUpdating）必须在UI线程中访问。你的代码中，尽管调用异步API时使用了ConfigureAwait(true)，但可能因API异步方法的上下文捕获问题，导致后续执行写入逻辑时处于非UI线程，最终在finally块设置ScreenUpdating = true时触发0x800AC472的COM异常。

解决方案 #

通过Excel应用的Dispatcher强制将所有操作Excel COM对象的逻辑调度到UI线程执行，具体修改如下：

1. 修改RefreshDataAsync方法，确保写入逻辑在UI线程执行

// in CustomRibbon.cs
private async Task RefreshDataAsync(Formula Formula, Excel.Range formulaCell)
{
    string getDataFromListOfSymbols = await SomeApi.GetForwardChartingCurveAsync(
        formula.MaturityDateStart.Value, 
        formula.MaturityDateEnd.Value, 
        formula.Symbols.Keys).ConfigureAwait(true);

    // 使用Dispatcher将写入逻辑切换到UI线程执行
    Globals.ThisAddIn.Application.Dispatcher.Invoke(() => 
    {
        ExcelWriteScheme.WriteForwardCurveDataToActivatedCell(formula, getDataFromListOfSymbols, formulaCell);
    });
}

2. 优化WriteForwardCurveDataToActivatedCell方法（可选）

为了更严谨，可以在方法内部添加线程检查，确保所有Excel操作都在UI线程：

public static void WriteForwardCurveDataToActivatedCell(Formula formula, string forwardCurveResponses, Excel.Range activeCellUponFormOpen)
{
    // 检查当前是否在UI线程，不在则切换
    if (!Globals.ThisAddIn.Application.Dispatcher.CheckAccess())
    {
        Globals.ThisAddIn.Application.Dispatcher.Invoke(() => 
            WriteForwardCurveDataToActivatedCell(formula, forwardCurveResponses, activeCellUponFormOpen));
        return;
    }

    Globals.ThisAddIn.Application.ScreenUpdating = false;

    try
    {
        Excel.Worksheet currentSheet = activeCellUponFormOpen.Worksheet;
        var responses = JsonConvert.DeserializeObject<IEnumerable<ForwardCurveResponse>>(forwardCurveResponses);

        // start index at 2 as first column is formula / trade date and second column is maturity date
        var distinctSymbolHeadersWithColumnIndices = responses
            .Select(x => x.Symbol)
            .Distinct()
            .Select((header, index) => new { Header = header, Index = index + 2 + activeCellUponFormOpen.Column })
            .ToDictionary(item => item.Header, item => item.Index);

        currentSheet.Cells[activeCellUponFormOpen.Row, activeCellUponFormOpen.Column + 1].Value = "Maturity Date";
        currentSheet.Cells[activeCellUponFormOpen.Row, activeCellUponFormOpen.Column + 1].Font.Bold = true;
        currentSheet.Cells[activeCellUponFormOpen.Row, activeCellUponFormOpen.Column + 1].wrapText = true;

        foreach (var kvp in distinctSymbolHeadersWithColumnIndices)
        {
            currentSheet.Cells[activeCellUponFormOpen.Row, kvp.Value].Value = kvp.Key;
            currentSheet.Cells[activeCellUponFormOpen.Row, kvp.Value].Font.Bold = true;
        }

        int row = activeCellUponFormOpen.Row + 1;
        bool firstPass = true;
        foreach (var response in responses.OrderByDescending(x => x.SymbolGroup.Count))
        {
            foreach (var group in response.SymbolGroup)
            {
                if (firstPass)
                {
                    currentSheet.Cells[row, activeCellUponFormOpen.Column].Value = group.TradeDate;
                    currentSheet.Cells[row, activeCellUponFormOpen.Column + 1].Value = group.MaturityDate;
                }

                if (group.Value.HasValue)
                {
                    currentSheet.Cells[row, distinctSymbolHeadersWithColumnIndices[response.Symbol]].Value = group.Value;
                }
                else
                {
                    currentSheet.Cells[row, distinctSymbolHeadersWithColumnIndices[response.Symbol]].Value = string.Empty;
                }

                row++;
            }

            firstPass = false;
            row = activeCellUponFormOpen.Row + 1;
        }

        activeCellUponFormOpen.Formula = FormatStringForFormulaFunction(formula.FormatInstanceToExcelString());
    }
    catch (Exception e)
    {
        _logger.Error("写入Excel数据失败", e);
    }
    finally
    {
        Globals.ThisAddIn.Application.ScreenUpdating = true;
    }
}

关键说明 #

• Dispatcher.Invoke会阻塞当前线程，直到UI线程完成指定操作，确保Excel COM对象的访问安全。
• 所有直接操作Excel对象（如Worksheet、Range、Application属性）的代码都必须在UI线程执行，否则都会触发类似的COM异常。

内容的提问来源于stack exchange，提问作者user9037543