如何根据使用范围剪辑 ExcelReference

本文关键字：ExcelReference 使用范围 何根 | 更新日期: 2023-09-27 17:56:57

我正在使用Excel-DNA在Excel中开发一些UDF。 从 Excel 传递到我的 UDF 的参数之一是范围。 当使用特定范围（如"A1：C50"）时，UDF 可以正常工作。 下面是我的函数定义示例：

[ExcelCommand()]
public static object CalcSMA(object[,] range, int num_points) {
    ...
}

但是，当传递整个列范围（例如"A：C"）时，我收到"内存不足"错误。 我可以通过设置参数属性 AllowReference=true 并将参数类型更改为对象来避免错误，如下例所示： 

[ExcelCommand()]
public static object CalcSMA([ExcelArgument("Range", AllowReference=true)]object range, int num_points) {
    ExcelReference xref = (ExcelReference)range;
    ...
}

但是现在我想知道 UDF 实际上需要多少行。 我可以尝试迭代工作表中的所有行，但这效率非常低。 有没有办法根据使用的范围裁剪Excel参考（外部参照）？ 我想避免使函数易变（IsMacroType=true），但如果需要，我会这样做。

如何根据使用范围剪辑 ExcelReference

在 VBA（或 COM）中，您可以将"范围"参数与"范围"参数的父级的"使用范围"参数相交。但在 XLL 中，获取使用的范围并不简单，因为 XLL 接口不为工作表提供 UsedRange 方法。所以你必须使用COM接口（这在XLL UDF内部是有问题的）。我构建了一个使用 AfterCompute 事件来缓存每个工作表的已用范围的例程。

这里有一些关于这样做的方法的讨论https://fastexcel.wordpress.com/2014/09/26/getting-used-range-in-an-xll-udf-multi-threading-and-com/

注意，如果你愿意让你的UDF成为单线程宏类型的UDF，你可以使用GETDOCUMENT（10） XLL api。但痛苦可能得不偿失。

根据Charles和Govert的建议，我最终实现了以下内容：

public class UsedRangeCache 
{
    protected static Dictionary<IntPtr, ExcelReference> _usedRanges = new Dictionary<IntPtr, ExcelReference>();
    protected static Application _app;
    /// <summary>
    /// Call this method when the XLL is initialized
    /// </summary>
    public static void Initialize(Application app)
    {
        _app = app;
        for (int i = 0; i < app.Workbooks.Count; i++ )
        {
            app_WorkbookOpen(app.Workbooks[i + 1]);
        }
        app.WorkbookOpen += app_WorkbookOpen;
        app.WorkbookBeforeClose += app_WorkbookBeforeClose;
        app.AfterCalculate += app_AfterCalculate;
    }
    // Refresh references
    static void app_AfterCalculate()
    {
        for (int i = 0; i < _app.Workbooks.Count; i++)
        {
            UpdateCache(_app.Workbooks[i + 1]);
        }
    }
    // Remove references
    static void app_WorkbookBeforeClose(Workbook book, ref bool Cancel)
    {
        for (int i = 0; i < book.Worksheets.Count; i++)
        {
            Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
            if (sheet != null)
            {
                ExcelReference xref = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, sheet.Name);
                if (_usedRanges.ContainsKey(xref.SheetId))
                {
                    _usedRanges.Remove(xref.SheetId);
                }
            }
        }
    }
    // Create references
    static void app_WorkbookOpen(Workbook book)
    {
        UpdateCache(book);
    }
    // Update cache
    private static void UpdateCache(Workbook book)
    {
        for (int i = 0; i < book.Worksheets.Count; i++)
        {
            Worksheet sheet = book.Worksheets[i + 1] as Worksheet;
            if (sheet != null)
            {
                ExcelReference xref = (ExcelReference)XlCall.Excel(XlCall.xlSheetId, sheet.Name);
                ExcelReference xused = new ExcelReference(
                    sheet.UsedRange.Row,
                    sheet.UsedRange.Row + sheet.UsedRange.Rows.Count,
                    sheet.UsedRange.Column,
                    sheet.UsedRange.Column + sheet.UsedRange.Columns.Count,
                    xref.SheetId);
                if (_usedRanges.ContainsKey(xref.SheetId)) 
                { 
                    _usedRanges.Remove(xref.SheetId); 
                }
                _usedRanges.Add(xref.SheetId, xused);
            }
        }
    }

    /// <summary>
    /// Get used range
    /// </summary>
    public static ExcelReference GetUsedRange(ExcelReference xref)
    {
        ExcelReference ret = null; 
        _usedRanges.TryGetValue(xref.SheetId, out ret); 
        return ret;
    }
}
相关文章：