Excel催化剂开源第11波-动态数组函数技术开源及要点讲述

Excel催化剂

发布于 2021-08-19 15:02:43

4360

发布于 2021-08-19 15:02:43

文章被收录于专栏：Excel催化剂

在Excel催化剂中，大量的自定义函数使用了动态数组函数效果，虽然不是原生的Excel365版效果（听说Excel2019版取消了支持动态数组函数，还没求证到位，Excel365是可以用，但也仅限于部分尝鲜用户可以用上，大部分Excel365用户还没推送成功），但对于自定义函数这条路线，可以实现类似效果，已经是非常震撼及使用面非常广大了。

顺便插一句，不是每个中国企业都能承担起高昂的软件更新费用，OFFICE软件非常大的一个坑爹之处是，旧版本升级到新版本，没有补差价升级一说，一律是重新购买，旧的也不能转让其他公司使用（这个没求证过，有错误请指出）。

在这样的OFFICE政策下，让企业每三年紧追OFFICE新版本给员工配置，这个可是极大的软件费用负担和软件使用浪费。

现在中美关系紧张，中国知识产权问题也是摆上台面要考虑的事情，不能动不动类似个人一样可以用盗版软件，企业用的软件是需要购买授权使用的，同样的Excel催化剂也只是对个人用户免费使用，企业用户没有任何承诺过免费使用一说。

在这样的情形下，如果我们能够通过自定义函数的方式，扩展一下原有旧版本的功能，使用户们不必垂帘新OFFICE版本功能而没法使用的打击学习热情，也可以有替代方案完成，间接也帮助企业节省软件成本，为国家减少盗版问题的贸易摩擦，上升高一层，是一种爱国行为了。

动态数组函数实现手段

借助ExcelDna框架来开发自定义函数，其作者也为我们准备了动态数组函数的技术实现，具体原理对于笔者这样的重业务导向的，也不懂其中的高深技术，有兴趣的朋友们可以深入研究后再更多分享出来。

在Excel自定义函数中，例如GetFiles函数，通过简单的.Net的IO类，实现遍历文件夹里的所有文件的功能，返回一个文件全路径的数组。

最终的关键技术是，如何让返回的这个数组结果，在用户在一个单元格中输入函数时，自动对其返回的多个结果进行单元格区域自动扩张，并以数组函数的方式返回。

GetFiles函数实现效果

具体代码

在GetFiles函数中，关键代码为最后将files数组返回到工作表结果的方法。

Common.ReturnDataArray(files, optAlignHorL);

        [ExcelFunction(Category = "文件文件夹相关", Description = "获取指定目录下的文件清单,srcFolder为传入的顶层目录，containsText可用作筛选包含containsText内容的文件夹，isSearchAllDirectory为是否查找顶层目录下的文件夹的所有子文件夹。Excel催化剂出品，必属精品！")]
        public static object GetFiles(
                [ExcelArgument(Description = "传入的顶层目录，最终返回的结果将是此目录下的文件夹或子文件夹下的全路径文件名")] string srcFolder,
                [ExcelArgument(Description = "查找的文件名中是否需要包含指定字符串，不传参数默认为返回所有文件，可传入复杂的正则表达式匹配。")] string containsText,
                [ExcelArgument(Description = "是否查找顶层目录下的文件夹的所有子文件夹，TRUE和非0的字符或数字为搜索子文件夹，其他为否，不传参数时默认为否")] object isSearchAllDirectory,
                [ExcelArgument(Description = "返回的结果是按按列排列还是按行排列，传入L按列排列，传入H按行排列，不传参数或传入非L或H则默认按列排列")] string optAlignHorL)
        {
            string[] files;
            if (Common.IsMissOrEmpty(containsText))
            {
                containsText = string.Empty;
            }
            //当isSearchAllDirectory为空或false，默认为只搜索顶层文件夹
            if (Common.IsMissOrEmpty(isSearchAllDirectory) || Common.TransBoolPara(isSearchAllDirectory) == false)
            {
                files = Directory.EnumerateFiles(srcFolder).Where(s => isContainsText(Path.GetFileName(s), containsText)).ToArray();
            }
            else
            {

                files = Directory.EnumerateFiles(srcFolder, "*", SearchOption.AllDirectories).Where(s => isContainsText(Path.GetFileName(s), containsText)).ToArray();
            }

            return Common.ReturnDataArray(files, optAlignHorL);
        }

拆解此方法可知，其实最关键的部分已经出来了 return ArrayResizer.Resize(resultArr);

 public static object ReturnDataArray(object[] srcArrData, string optAlignHorL)
        {

            int resultCount = srcArrData.Count();

            if (Common.IsMissOrEmpty(optAlignHorL) || optAlignHorL.Equals("H", StringComparison.CurrentCultureIgnoreCase) == false)
            {
                optAlignHorL = "L";
            }
            else
            {
                optAlignHorL = "H";
            }
            //直接用从下标为0开始的数组也可以
            if (optAlignHorL == "L")
            {
                object[,] resultArr = new object[resultCount, 1];
                for (int i = 0; i < resultCount; i++)
                {
                    resultArr[i, 0] = srcArrData[i];
                }
                //return resultArr;
                return ArrayResizer.Resize(resultArr);
            }

            else
            {
                //横排时，直接用一维数组就可以识别到
                object[,] resultArr = new object[1, resultCount];
                for (int i = 0; i < resultCount; i++)
                {
                    resultArr[0,i] = srcArrData[i];
                }
                return ArrayResizer.Resize(resultArr);
            }

        }

最后贴上这个帮助类的源代码，是从ExcelDna作者的示例代码中抄过来的，笔者是看不懂的，但确实是起作用了，用了异步函数的方法返回结果。

using ExcelDna.Integration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExcelCuiHuaJi
{
    public class ArrayResizer : XlCall
    {

        // This function will run in the UDF context.
        // Needs extra protection to allow multithreaded use.
        internal static object Resize(object[,] array)
        {
            var caller = Excel(xlfCaller) as ExcelReference;
            if (caller == null)
                return array;

            int rows = array.GetLength(0);
            int columns = array.GetLength(1);

            if (rows == 0 || columns == 0)
                return array;

            if ((caller.RowLast - caller.RowFirst + 1 == rows) &&
                (caller.ColumnLast - caller.ColumnFirst + 1 == columns))
            {
                // Size is already OK - just return result
                return array;
            }

            var rowLast = caller.RowFirst + rows - 1;
            var columnLast = caller.ColumnFirst + columns - 1;

            // Check for the sheet limits
            if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||
                columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)
            {
                // Can't resize - goes beyond the end of the sheet - just return #VALUE
                // (Can't give message here, or change cells)
                return ExcelError.ExcelErrorValue;
            }

            // TODO: Add some kind of guard for ever-changing result?
            ExcelAsyncUtil.QueueAsMacro(() =>
            {
                // Create a reference of the right size
                var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId);
                DoResize(target); // Will trigger a recalc by writing formula
            });
            // Return what we have - to prevent flashing #N/A
            return array;
        }

        //public static double[,] ResizeDoubles(double[,] array)
        //{
        //    var caller = Excel(xlfCaller) as ExcelReference;
        //    if (caller == null)
        //        return array;

        //    int rows = array.GetLength(0);
        //    int columns = array.GetLength(1);

        //    if (rows == 0 || columns == 0)
        //        return array;

        //    if ((caller.RowLast - caller.RowFirst + 1 == rows) &&
        //        (caller.ColumnLast - caller.ColumnFirst + 1 == columns))
        //    {
        //        // Size is already OK - just return result
        //        return array;
        //    }

        //    var rowLast = caller.RowFirst + rows - 1;
        //    var columnLast = caller.ColumnFirst + columns - 1;

        //    if (rowLast > ExcelDnaUtil.ExcelLimits.MaxRows - 1 ||
        //        columnLast > ExcelDnaUtil.ExcelLimits.MaxColumns - 1)
        //    {
        //        // Can't resize - goes beyond the end of the sheet - just return null (for #NUM!)
        //        // (Can't give message here, or change cells)
        //        return null;
        //    }

        //    // TODO: Add guard for ever-changing result?
        //    ExcelAsyncUtil.QueueAsMacro(() =>
        //    {
        //        // Create a reference of the right size
        //        var target = new ExcelReference(caller.RowFirst, rowLast, caller.ColumnFirst, columnLast, caller.SheetId);
        //        DoResize(target); // Will trigger a recalc by writing formula
        //    });
        //    // Return what we have - to prevent flashing #N/A
        //    return array;
        //}

        static void DoResize(ExcelReference target)
        {
            // Get the current state for reset later
            using (new ExcelEchoOffHelper())
            using (new ExcelCalculationManualHelper())
            {
                ExcelReference firstCell = new ExcelReference(target.RowFirst, target.RowFirst, target.ColumnFirst, target.ColumnFirst, target.SheetId);

                // Get the formula in the first cell of the target
                string formula = (string)Excel(xlfGetCell, 41, firstCell);
                bool isFormulaArray = (bool)Excel(xlfGetCell, 49, firstCell);
                if (isFormulaArray)
                {
                    // Select the sheet and firstCell - needed because we want to use SelectSpecial.
                    using (new ExcelSelectionHelper(firstCell))
                    {
                        // Extend the selection to the whole array and clear
                        Excel(xlcSelectSpecial, 6);
                        ExcelReference oldArray = (ExcelReference)Excel(xlfSelection);

                        oldArray.SetValue(ExcelEmpty.Value);
                    }
                }
                // Get the formula and convert to R1C1 mode
                bool isR1C1Mode = (bool)Excel(xlfGetWorkspace, 4);
                string formulaR1C1 = formula;
                if (!isR1C1Mode)
                {
                    object formulaR1C1Obj;
                    XlReturn formulaR1C1Return = TryExcel(xlfFormulaConvert, out formulaR1C1Obj, formula, true, false, ExcelMissing.Value, firstCell);
                    if (formulaR1C1Return != XlReturn.XlReturnSuccess || formulaR1C1Obj is ExcelError)
                    {
                        string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);
                        Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - formula might be too long when converted to R1C1 format.");
                        firstCell.SetValue("'" + formula);
                        return;
                    }
                    formulaR1C1 = (string)formulaR1C1Obj;
                }
                // Must be R1C1-style references
                object ignoredResult;
                //Debug.Print("Resizing START: " + target.RowLast);
                XlReturn formulaArrayReturn = TryExcel(xlcFormulaArray, out ignoredResult, formulaR1C1, target);
                //Debug.Print("Resizing FINISH");

                // TODO: Find some dummy macro to clear the undo stack

                if (formulaArrayReturn != XlReturn.XlReturnSuccess)
                {
                    string firstCellAddress = (string)Excel(xlfReftext, firstCell, true);
                    Excel(xlcAlert, "Cannot resize array formula at " + firstCellAddress + " - result might overlap another array.");
                    // Might have failed due to array in the way.
                    firstCell.SetValue("'" + formula);
                }
            }
        }
    }

    // RIIA-style helpers to deal with Excel selections    
    // Don't use if you agree with Eric Lippert here: http://stackoverflow.com/a/1757344/44264
    public class ExcelEchoOffHelper : XlCall, IDisposable
    {
        object oldEcho;

        public ExcelEchoOffHelper()
        {
            oldEcho = Excel(xlfGetWorkspace, 40);
            Excel(xlcEcho, false);
        }

        public void Dispose()
        {
            Excel(xlcEcho, oldEcho);
        }
    }

    public class ExcelCalculationManualHelper : XlCall, IDisposable
    {
        object oldCalculationMode;

        public ExcelCalculationManualHelper()
        {
            oldCalculationMode = Excel(xlfGetDocument, 14);
            Excel(xlcOptionsCalculation, 3);
        }

        public void Dispose()
        {
            Excel(xlcOptionsCalculation, oldCalculationMode);
        }
    }

    // Select an ExcelReference (perhaps on another sheet) allowing changes to be made there.
    // On clean-up, resets all the selections and the active sheet.
    // Should not be used if the work you are going to do will switch sheets, amke new sheets etc.
    public class ExcelSelectionHelper : XlCall, IDisposable
    {
        object oldSelectionOnActiveSheet;
        object oldActiveCellOnActiveSheet;

        object oldSelectionOnRefSheet;
        object oldActiveCellOnRefSheet;

        public ExcelSelectionHelper(ExcelReference refToSelect)
        {
            // Remember old selection state on the active sheet
            oldSelectionOnActiveSheet = Excel(xlfSelection);
            oldActiveCellOnActiveSheet = Excel(xlfActiveCell);

            // Switch to the sheet we want to select
            string refSheet = (string)Excel(xlSheetNm, refToSelect);
            Excel(xlcWorkbookSelect, new object[] { refSheet });

            // record selection and active cell on the sheet we want to select
            oldSelectionOnRefSheet = Excel(xlfSelection);
            oldActiveCellOnRefSheet = Excel(xlfActiveCell);

            // make the selection
            Excel(xlcFormulaGoto, refToSelect);
        }

        public void Dispose()
        {
            // Reset the selection on the target sheet
            Excel(xlcSelect, oldSelectionOnRefSheet, oldActiveCellOnRefSheet);

            // Reset the sheet originally selected
            string oldActiveSheet = (string)Excel(xlSheetNm, oldSelectionOnActiveSheet);
            Excel(xlcWorkbookSelect, new object[] { oldActiveSheet });

            // Reset the selection in the active sheet (some bugs make this change sometimes too)
            Excel(xlcSelect, oldSelectionOnActiveSheet, oldActiveCellOnActiveSheet);
        }
    }
}

结语

以上涉及的所有代码已经进行开源，并且整个自定义函数项目也已经开源了，甚至不需要重新开项目，重新复制粘贴，直接在源项目上增删内容，即可完成自定义函数的开发，Excel催化剂开源作贡献是认真的。

通过动态数组函数技术开发自定义函数，不必再烦恼于用户不懂数组函数的复杂输入方式及数组函数返回结果不确定时，不知道该选定多少单元格的烦恼。也不必羡慕Excel365用户可以用上的动态数组函数，在Excel自定义函数中，比官方提供的函数使用场景更广，门槛列低，通用性更强，在Excel2007及以后所有版本都可使用，方便作文件的分享。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2019-01-10，如有侵权请联系 cloudcommunity@tencent.com 删除

编程算法