Excel-DNA UDF循环计算问题：如何设置仅计算特定工作表？

使用Excel-DNA（版本1.7.0-rc9）用C#编写UDF xlMyGet，功能是从服务API获取数据并写入指定工作表。为实现数据实时更新且易触发查询，将UDF标记为IsVolatile=true，但在未知场景下会出现循环计算，写入目标工作表时导致Excel进程无响应。

将Application.Calculation设为XLCalculation.xlCalculationManual可缓解，但会导致其他工作表的volatile UDF无法在单元格变更后自动重算。需求是让Excel应用保持xlCalculationAutomatic模式，仅跳过包含xlMyGet的工作表的自动计算。

UDF调用位置为[Workbook1]Sheet1!$A$1，数据写入位置为[Workbook1]Sheet2!$A$5，返回数据格式为object[,] data = new object[2,4]。

UDF代码 #

// UDF Summary
[ExcelFunction(IsVolatile=true)]
public static object xlMyGet(string url,object[] argumentKeys,object[] argumentValues,string cellAddress)
{
    if(ExcelDnaUtil.IsInFunctionWizard())
        return ExcelError.ExcelErrorRef;
    Application app = (Application)ExcelDnaUtil.Application;
    Range targetCell = app.Range[cellAddress];
    Dictionary<string,object> argDic = Enumerable.Range(0,argumentKeys.Length)
      .ToDictionary(key=>argumentKeys.ElementAt(key)?.ToString(),value=>argumentValues.ElementAtOrDefault(value));
    if(targetCell != null)
    {
        new MyApi().getData(argDic,targetCell.Column,targetCell.Row,targetCell.Worksheet);
    }
    return "OK here!";
}

MyApi.cs代码 #

public class MyApi
{
    public void getData(Dictionary<string,object> argDic,int beginColumn,int beginRow,Worksheet worksheet)
    {
        // assume the data is ready and has 2 rows, 4 columns
        object[,] data = new object[2,4];
        // notify the refresh datetime of data
        ExcelAsyncUtil.QueueAsMacro(()=>
        {
            DateTime refreshTime = DateTime.Now.ToLocalTime();
            Range refreshTitleRange = worksheet.Cells[beginRow,beginColumn+1];
            refreshTitleRange.Value = "Refresh time:";
            refreshTitleRange = refreshTitleRange.Offset[ColumnOffset:1];
            refreshTitleRange.NumberFormat = "@";
            refreshTitleRange.Value = refreshTime.ToString("yyyy-MM-dd HH:mm:ss");
            refreshTitleRange.EntireColumn.AutoFit();
        });
        int rowCount = data.GetLength(0);
        int colCount = data.GetLength(1);
        // write data to target worksheet
        Range dataRange = worksheet.Range[worksheet.Application.ConvertFormula($"R{beginRow+1}C{beginColumn}:R{((beginRow+1)+Math.Max(0,rowCount-1))}C{beginColumn+colCount-1}",XlReferenceStyle.xlR1C1,XlReferenceStyle.xlA1)];
        dataRange.Value = data;
    }
}

尝试过通过C API设置volatile状态、处理AfterCalculate和SheetCalculate事件，但无法实现取消特定工作表的计算，寻求可行方案。

Excel本身无直接按工作表设置计算模式的API，需通过以下间接手段实现目标：

1. 移除Volatile标记，改用触发式更新 #

Volatile UDF会在任意单元格变更时触发重算，是循环计算的核心诱因。取消IsVolatile=true，改用触发参数控制重算：

• 给UDF添加触发参数（如时间戳或手动修改的单元格引用），修改该参数即可触发UDF重算
• 配合Worksheet_Change事件，在指定单元格变更时自动更新触发参数，模拟自动更新效果

修改后的UDF示例：

[ExcelFunction(IsVolatile=false)] // 移除volatile标记
public static object xlMyGet(string url,object[] argumentKeys,object[] argumentValues,string cellAddress, object trigger) // 添加触发参数
{
    if(ExcelDnaUtil.IsInFunctionWizard())
        return ExcelError.ExcelErrorRef;
    Application app = (Application)ExcelDnaUtil.Application;
    Range targetCell = app.Range[cellAddress];
    Dictionary<string,object> argDic = Enumerable.Range(0,argumentKeys.Length)
      .ToDictionary(key=>argumentKeys.ElementAt(key)?.ToString(),value=>argumentValues.ElementAtOrDefault(value));
    if(targetCell != null)
    {
        new MyApi().getData(argDic,targetCell.Column,targetCell.Row,targetCell.Worksheet);
    }
    return "OK here!";
}

使用示例：在Sheet1!A1输入=xlMyGet(..., NOW())，或绑定到手动更新的单元格（如Sheet1!B1）。

2. 拦截计算事件，临时切换计算模式 #

利用Excel的SheetCalculate事件，在指定工作表计算前临时切换为手动模式，计算完成后恢复：

实现代码（Excel-DNA加载项中注册事件） #

public class AddIn : IExcelAddIn
{
    public void AutoOpen()
    {
        Application app = (Application)ExcelDnaUtil.Application;
        app.SheetCalculate += App_SheetCalculate;
    }

    private void App_SheetCalculate(object Sh)
    {
        Worksheet targetSheet = (Worksheet)Sh;
        // 替换为包含xlMyGet的工作表名称
        if (targetSheet.Name == "Sheet1")
        {
            XlCalculation originalMode = targetSheet.Application.Calculation;
            targetSheet.Application.Calculation = XlCalculation.xlCalculationManual;
            
            // 执行必要操作后恢复原模式
            targetSheet.Application.Calculation = originalMode;
        }
    }

    public void AutoClose()
    {
        Application app = (Application)ExcelDnaUtil.Application;
        app.SheetCalculate -= App_SheetCalculate;
    }
}

3. 优化数据写入逻辑，避免触发计算循环 #

当前代码中直接写入工作表会触发计算事件，进而导致volatile UDF重算。需将所有写入操作放到异步宏队列，并临时禁用事件：

修改后的MyApi.getData方法：

public void getData(Dictionary<string,object> argDic,int beginColumn,int beginRow,Worksheet worksheet)
{
    object[,] data = new object[2,4];
    // 将所有写入操作放入异步宏
    ExcelAsyncUtil.QueueAsMacro(()=>
    {
        Application app = worksheet.Application;
        bool originalEnableEvents = app.EnableEvents;
        app.EnableEvents = false;

        try
        {
            // 写入刷新时间
            DateTime refreshTime = DateTime.Now.ToLocalTime();
            Range refreshTitleRange = worksheet.Cells[beginRow,beginColumn+1];
            refreshTitleRange.Value = "Refresh time:";
            refreshTitleRange = refreshTitleRange.Offset[ColumnOffset:1];
            refreshTitleRange.NumberFormat = "@";
            refreshTitleRange.Value = refreshTime.ToString("yyyy-MM-dd HH:mm:ss");
            refreshTitleRange.EntireColumn.AutoFit();

            // 写入数据
            int rowCount = data.GetLength(0);
            int colCount = data.GetLength(1);
            Range dataRange = worksheet.Range[worksheet.Application.ConvertFormula(
                $"R{beginRow+1}C{beginColumn}:R{((beginRow+1)+Math.Max(0,rowCount-1))}C{beginColumn+colCount-1}",
                XlReferenceStyle.xlR1C1,XlReferenceStyle.xlA1)];
            dataRange.Value = data;
        }
        finally
        {
            // 恢复事件状态
            app.EnableEvents = originalEnableEvents;
        }
    });
}

4. 使用Excel-DNA异步UDF替代同步UDF #

对于涉及API调用的UDF，推荐使用异步UDF（IsAsync=true），避免阻塞Excel计算线程，同时更好地控制计算时机：

[ExcelFunction(IsAsync=true, IsVolatile=false)]
public static object xlMyGetAsync(string url,object[] argumentKeys,object[] argumentValues,string cellAddress)
{
    if(ExcelDnaUtil.IsInFunctionWizard())
        return ExcelError.ExcelErrorRef;

    return ExcelAsyncUtil.Run("xlMyGetAsync", () =>
    {
        Dictionary<string,object> argDic = Enumerable.Range(0,argumentKeys.Length)
          .ToDictionary(key=>argumentKeys.ElementAt(key)?.ToString(),value=>argumentValues.ElementAtOrDefault(value));
        Application app = (Application)ExcelDnaUtil.Application;
        Range targetCell = app.Range[cellAddress];
        
        if(targetCell != null)
        {
            new MyApi().getData(argDic,targetCell.Column,targetCell.Row,targetCell.Worksheet);
        }
        return "OK here!";
    });
}

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