Excel催化剂开源第11波-动态数组函数技术开源及要点讲述
在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及以后所有版本都可使用，方便作文件的分享。
技术交流QQ群
QQ群名：Excel催化剂开源讨论群， QQ群号：788145319
Excel催化剂开源讨论群二维码
关于Excel催化剂
Excel催化剂先是一微信公众号的名称，后来顺其名称，正式推出了Excel插件，插件将持续性地更新，更新的周期视本人的时间而定争取一周能够上线一个大功能模块。Excel催化剂插件承诺个人用户永久性免费使用！
Excel催化剂插件使用最新的布署技术，实现一次安装，日后所有更新自动更新完成，无需重复关注更新动态，手动下载安装包重新安装，只需一次安装即可随时保持最新版本！
Excel催化剂插件下载链接：https://pan.baidu.com/s/1Iz2_NZJ8v7C9eqhNjdnP3Q
联系作者
公众号
取名催化剂，因Excel本身的强大，并非所有人能够立马享受到，大部分人还是在被Excel软件所虐的阶段，就是头脑里很清晰想达到的效果，而且高手们也已经实现出来，就是自己怎么弄都弄不出来，或者更糟的是还不知道Excel能够做什么而停留在不断地重复、机械、手工地在做着数据，耗费着无数的青春年华岁月。所以催生了是否可以作为一种媒介，让广大的Excel用户们可以瞬间点燃Excel的爆点，无需苦苦地挣扎地没日没夜的技巧学习、高级复杂函数的烧脑，最终走向了从入门到放弃的道路。
最后Excel功能强大，其实还需树立一个观点，不是所有事情都要交给Excel去完成，也不是所有事情Excel都是十分胜任的，外面的世界仍然是一个广阔的世界，Excel只是其中一枚耀眼的明星，还有其他更多同样精彩强大的技术、工具等。*Excel催化剂也将借力这些其他技术，让Excel能够发挥更强大的爆发！
关于Excel催化剂作者
姓名：李伟坚，从事数据分析工作多年（BI方向），一名同样在路上的学习者。
服务过行业：零售特别是鞋服类的零售行业，电商（淘宝、天猫、京东、唯品会）
技术路线从一名普通用户，通过Excel软件的学习，从此走向数据世界，非科班IT专业人士。
历经重重难关，终于在数据的道路上达到技术平原期，学习众多的知识不再太吃力，同时也形成了自己的一套数据解决方案（数据采集、数据加工清洗、数据多维建模、数据报表展示等）。
擅长技术领域：Excel等Office家族软件、VBA&VSTO的二次开发、Sqlserver数据库技术、Sqlserver的商业智能BI技术、Powerbi技术、云服务器布署技术等等。
2018年开始职业生涯作了重大调整，从原来的正职工作，转为自由职业者，暂无固定收入，暂对前面道路不太明朗，苦重新回到正职工作，对Excel催化剂的运营和开发必定受到很大的影响（正职工作时间内不可能维护也不可能随便把工作时间内的成果公布于外，工作外的时间也十分有限，因已而立之年，家庭责任重大）。
和广大拥护者一同期盼：Excel催化剂一直能运行下去，我所惠及的群体们能够给予支持（多留言鼓励下、转发下朋友圈推荐、小额打赏下和最重点的可以和所在公司及同行推荐推荐，让我的技术可以在贵司发挥价值，实现双赢（初步设想可以数据顾问的方式或一些小型项目开发的方式合作）。
技术交流QQ群
QQ群名：Excel催化剂开源讨论群， QQ群号：788145319
Excel催化剂开源讨论群二维码
关于Excel催化剂
Excel催化剂先是一微信公众号的名称，后来顺其名称，正式推出了Excel插件，插件将持续性地更新，更新的周期视本人的时间而定争取一周能够上线一个大功能模块。Excel催化剂插件承诺个人用户永久性免费使用！
Excel催化剂插件使用最新的布署技术，实现一次安装，日后所有更新自动更新完成，无需重复关注更新动态，手动下载安装包重新安装，只需一次安装即可随时保持最新版本！
Excel催化剂插件下载链接：https://pan.baidu.com/s/1Iz2_NZJ8v7C9eqhNjdnP3Q
联系作者
公众号
取名催化剂，因Excel本身的强大，并非所有人能够立马享受到，大部分人还是在被Excel软件所虐的阶段，就是头脑里很清晰想达到的效果，而且高手们也已经实现出来，就是自己怎么弄都弄不出来，或者更糟的是还不知道Excel能够做什么而停留在不断地重复、机械、手工地在做着数据，耗费着无数的青春年华岁月。所以催生了是否可以作为一种媒介，让广大的Excel用户们可以瞬间点燃Excel的爆点，无需苦苦地挣扎地没日没夜的技巧学习、高级复杂函数的烧脑，最终走向了从入门到放弃的道路。
最后Excel功能强大，其实还需树立一个观点，不是所有事情都要交给Excel去完成，也不是所有事情Excel都是十分胜任的，外面的世界仍然是一个广阔的世界，Excel只是其中一枚耀眼的明星，还有其他更多同样精彩强大的技术、工具等。*Excel催化剂也将借力这些其他技术，让Excel能够发挥更强大的爆发！
关于Excel催化剂作者
姓名：李伟坚，从事数据分析工作多年（BI方向），一名同样在路上的学习者。
服务过行业：零售特别是鞋服类的零售行业，电商（淘宝、天猫、京东、唯品会）
技术路线从一名普通用户，通过Excel软件的学习，从此走向数据世界，非科班IT专业人士。
历经重重难关，终于在数据的道路上达到技术平原期，学习众多的知识不再太吃力，同时也形成了自己的一套数据解决方案（数据采集、数据加工清洗、数据多维建模、数据报表展示等）。
擅长技术领域：Excel等Office家族软件、VBA&VSTO的二次开发、Sqlserver数据库技术、Sqlserver的商业智能BI技术、Powerbi技术、云服务器布署技术等等。
2018年开始职业生涯作了重大调整，从原来的正职工作，转为自由职业者，暂无固定收入，暂对前面道路不太明朗，苦重新回到正职工作，对Excel催化剂的运营和开发必定受到很大的影响（正职工作时间内不可能维护也不可能随便把工作时间内的成果公布于外，工作外的时间也十分有限，因已而立之年，家庭责任重大）。
和广大拥护者一同期盼：Excel催化剂一直能运行下去，我所惠及的群体们能够给予支持（多留言鼓励下、转发下朋友圈推荐、小额打赏下和最重点的可以和所在公司及同行推荐推荐，让我的技术可以在贵司发挥价值，实现双赢（初步设想可以数据顾问的方式或一些小型项目开发的方式合作）。
