使用集算器桌面版的Excel插件，可在Excel中执行SPL脚本并获得结果集后填充到Excel中。

第 1 章 安装与配置

1、环境

要求64位的Windows和Excel，不能用32位的，否则出现异常。

判断Windows和Excel是否为64位的办法：

1、 Windows: 点击操作系统的【设置】-【系统】-【关于】

2、 Excel：打开Excel，点击【文件】-【账户】-【关于Excel】，弹出对话框里会显示当前Excel版本，如下所示：

About Microsoft® Excel® 2019 Microsoft® Excel® 2019MSO (Version 2202 Build 16.0.14931.20116) 64Bit Product ID: 00405-32554-86889-AAOEM Session ID: 5DCA85DD-F5BE-4570-8CE4-4D56EF93273D

语言：请安装与操作系统语言相同的集算器桌面版，即如果操作系统是英文的，则装英文版的集算器桌面版，如果操作系统是中文的，则装中文版的集算器桌面版，否则容易出现乱码等问题。

如果中文windows操作系统安装中文集算器桌面版后，会出现乱码等现象，请检查操作系统如下配置：

【设置】->【时间和语言】->【语言】->【管理语言设置】->【更改系统区域设置】

看看这里是否配置的中文，如果不是，改成中文即可

2、依赖文件

ExcelSpl.xll：插件文件，位于【集算器桌面版安装目录】/esProcDesk/bin

scu-Excel-xll-2.10.jar、xll4j-0.0.2.jar：依赖jar，位于【集算器桌面版安装目录】/esProcDesk/lib

ExcelSpl.ini：配置文件，位于【集算器桌面版安装目录】/esProcDesk/config

esproc_template.xla：宏文件位于【集算器桌面版安装目录】/esProcDesk/bin目录下

3、加载ExcelSpl.xll插件

打开Excel，点击菜单【文件】->【选项】->【加载项】，找到管理Excel加载项，点击【转到】弹出加载项列表选择窗口，点击【浏览】，选择【集算器桌面版安装目录】/esProcDesk/bin下的插件ExcelSpl.xll，确定新增的加载项为勾选状态后，重启Excel生效。成功加载后再查看加载项如下图：

加载成功后，Excel中将增加spl()函数。

当前步骤容易遇到的问题：

情况1：

选择完插件文件ExcelSpl.xll点确定后，出现鼠标一直转，始终不进入下一步，此时可以强制中断Excel并重启Excel。

重启Excel后会提示：某某加载项加载不成功，是否需要禁用？

点击禁用，然后重新按上述操作重新加载ExcelSpl.xll插件即可成功。

情况2：

选择完插件文件ExcelSpl.xll点确定后，出现鼠标一直转，始终不进入下一步，此时可以强制中断Excel并重启Excel。

重启Excel后没有任何提示，此时打开加载项配置页面，会发现插件可能已经加载成功（出现前述加载成功画面）。

情况3：

选择完插件文件ExcelSpl.xll点确定后，出现鼠标一直转，始终不进入下一步，此时可以强制中断Excel并重启Excel。

重启Excel后没有任何提示，此时打开加载项配置页面，会发现插件并没有加载成功，重复操作加载，依旧出现卡死现象，始终无法加载成功

此时解决办法是先关闭Excel，然后再打开windows的注册表，添加如下注册表项：

补充说明：

1、 这里OPEN项的值为字符串/R再加上由双引号括起来的字符串，串的内容是加载项ExcelSpl.xll的全路径文件名

2、 注册表OPEN项的位置，取决于Excel安装版本，不同版本，这个路径可能不一样，比如Excel2019是

【计算机\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options】

低一些的版本可能是

【计算机\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\15.0\Excel\Options】

具体位置和版本相关，根据实际情况灵活应对

添加完后，打开Excel，此时可以发现加载项配置页面里已经有插件了，表示加载成功。

4、在Excel中加载宏文件

打开【集算器桌面版安装目录】\esProcDesk\bin目录，找到esproc_template.xla文件，把它复制到【Excel 安装目录 \XLSTART】目录下，例如：C:\Program Files\Microsoft Office\Office16\XLSTART，只有放在此目录下的宏文件，Excel才会在启动时自动加载，放别的目录无法加载成功。

打开Excel，点击菜单【文件】->【选项】->【信任中心】->【信任中心设置】->【宏设置】中勾选【启用所有宏】，然后重启Excel。

补充说明：

1、 不同版本的Excel该目录可能不同，所以一定要打开Excel找到这个目录，再把esproc_template.xla复制过去

2、 esproc_template.xla中定义了一个宏及其热键(ctrl-enter)，用于处理多返回值，有冲突时可以修改热键。

3、 spl()函数返回单值时，不需要用到这个宏，也不必加载此宏文件。

5、配置 SPL IDE 主目录

SPL IDE 中要配置【主目录】，被调用的脚本文件(.splx)必须位于主目录下，否则找不到，配置方法如下：

打开 SPL IDE，点击菜单【工具】->【选项】->选择【环境】->找到【主目录】

配置完主目录后，需要重启 SPL IDE 和Excel才会生效

6、日志

可以通过日志文件查看异常信息，日志文件位于%appdata%\esproc\tmp\Excel.log

第 2 章 在 Excel 中使用 spl() 函数

安装配置好之后，Excel中会增加spl()函数，其功能是在Excel单元格中执行SPL脚本并获得计算后的结果集。

其语法有两种：

l spl(exp, arg1, …)

参数说明：

exp	以=开头的SPL语法的表达式串。其中用问号代表参数引用，?1对应第一个参数，?2对应第二个参数，依此类推
arg1,…	参数值，参数可以有0个或多个，至多30个。可以填入常数，或引用当前Excel单元格，允许单格和片区。注意：Excel 插件识别不了某些参数的数据类型时，可使用 E() 函数进行数据类型转换。比如：日期、日期时间、时间等类型。

复杂计算需求，执行用SPL写好的脚本文件，格式为

l spl(splx(…), … )

参数说明：

splName()	脚本文件名称，无后缀，统一用.splx，且必须加上() 脚本文件中定义的参数，按顺序和后面赋予的参数匹配
arg1,…	同前面规则

spl()函数可能返回单值、一维数组或二维数组。

示例1：无参数

生成100以内的整数随机数：

计算结果：

示例2：单个参数

有如下Excel文件，第一列是随机数的范围，第二列是根据第一列给定的范围，生成一个相应范围内的整数随机数：

计算结果：

把B2单元格拖动复制到每一个格子：

示例3：表达式中有双引号

有如下日期数据：

由于某些原因，这些数据没有日期格式，无法被Excel识别，被当成了普通的数字串，现在需要把它们变成日期数据。

SPL有很简单的写法：=date(A2,"yyyyMMdd")，这样会按照"yyyyMMdd"的格式，把A2解析成日期。但是这样的SPL脚本里本身含有双引号，写到spl()函数里，需要使用Excel的规则对双引号进行转义，即把"写成""，如下所示：

运算结果如下：

然后把B2拖动复制到每一行，结果如下：

补充说明：

1、 需要先把日期数据所在单元格类型设为文本类型，设置方法如下：

选中单元格->鼠标右键点击【设置单元格格式】->在【数字】tab页中选择【文本】

2、 确认设置成功的标准是单元格的左上角有个绿色的小三角，如果按照1中操作，没有出现绿色小三角，可以双击单元格进入编辑状态，然后移开焦点即可

示例4：多个参数

有如下Excel文件，其中r, g, b, a分别代表颜色中的红、绿、蓝及透明分量:

现需要在右边增加一列，算出每一行颜色分量组合成的颜色值，输入SPL代码如下：

代码中分别用?1,?2,?3,?4对应第一个参数A2，第二个参数B2，第三个参数C2，第四个参数D2

输入完上述表达式后，即可算出当前行的颜色值如下：

然后再把E2单元格拖动复制到每一个格子

示例5：一行片区参数

有Excel文件如下图所示，现需要对每行数据去掉最大值和最小值，再求中位数，把结果填在最右侧的列中：

在I2单元格中输入=spl("=?.conj().sort().m(2:-2).median()",A2:H2)，Excel将把A2:H2单元格片区组成的数组传给SPL表达式"=?.conj().sort().m(2:-2).median()"，替代其中的参数?，返回去掉最大值和最小值后的中位数，计算结果：

补充说明：

1、Excel会自动把数组参数处理成二维数组，即使事实上只一行行（或一列）也是如此，因此在SPL脚本里需要用conj()拼成单层序列。

此时再把I2单元格的表达式向下拖拽复制到下面的每一行，即可自动算出所有行的中位数，如下图所示：

示例6：一列片区参数

有Excel文件如下图所示，现需要对每列数据去掉最大值和最小值，再求中位数，把结果填在最底下的行中：

在A14单元格中输入=spl("=?.conj().sort().m(2:-2).median()",B1:B13)，Excel将把B1:B13单元格片区组成的数组传给SPL表达式"=?.conj().sort().m(2:-2).median()"，替代其中的参数?，返回去掉最大值和最小值后的中位数，计算结果：

补充说明：

1、Excel会自动把数组参数处理成二维数组，即使事实上只一行行（或一列）也是如此，因此在SPL脚本里需要用conj()拼成单层序列。

此时再把B14单元格的表达式向右拖拽复制到右边的每一列，即可自动算出右边所有列的中位数，如下图所示：

示例7：多行多列片区参数

有如下Excel文件：

现在需要在右侧增加一列，计算取样数据的累计平均值，即第一天的记录只算第一天的平均值，第二天的记录则算前两天总的平均值，第三天则算前三天的总平均值，依此类推。同样的，计算平均值之前需要先去掉最大值和最小值，计算结果如下：

在Z3单元格中输入表达式：=spl("=?.conj().sort().m(2:-2).avg()",$B$3:Y3)，此时算出结果5.40

从表达式可以看出，传入的参数$B$3:Y3，第一个单元格加了$符号，因为要算的是累计平均，在向下复制该表达式时要保持始终从B3单元格开始累计，所以加$。

把Z3单元格的表达式拖动复制到每一行，最后算出所有行的累计平均值

示例8：混合参数

有如下Excel文件，为每一天24个小时的整点取样数据：

现在需要在右侧增加一列，找出截止到当天本月所有取样数据中的最大n个值的平均值，n取决于Z列的数据。

在AA3中输入SPL表达式如下：

=spl("=?1.top@2(-?2).avg()", $B$3:Y3, Z3)

第一个参数：=?1.top@2(-?2).avg() 是spl公式，表示对参数?1算topn，n由参数?2决定；然后再对topn算平均值

第二个参数：$B$3:Y3，是传给spl公式的参数，对应?1

第三个参数：Z3，是传给spl公式的参数，对应?2

把AA3的公式拖动复制到每一行，最后结果如下：

示例9：返回一维数组

生成10个100以内的随机整数，按顺序填入第一列中：

输入表达式后，按ctrl-enter执行在esproc_template.xla中定义的宏，将计算结果填充到相邻格中：

表达式返回一维数组时，按ctrl-enter后，会把数组成员以当前格为起始格，向下依次填入一列单元格中。不使用ctrl-enter则只会将第一个成员填入表达式所在格。

示例10：返回二维数组

现有Employee.xlsx文件内容如下：

现需要按部门DEPT字段统计每个部门的人数，在A1格输入SPL脚本如下：

Employee.xlsx最好放在 SPL IDE 主目录下，这样可以不写路径名找到，否则要在表达式中写上全路径名

按ctrl-enter将计算结果填入相邻单元格：

补充说明：

1、 返回二维数组时也需要按ctrl-enter触发填充动作，以当前格为起始格，向右向下依次填入一片单元格中。不使用ctrl-enter时只会在表达式所在格填入数组的第一个成员。

2、 SPL脚本中如果有"，需要按Excel规则进行转义，写成""。

示例11：动态拼SPL表达式

有如下Excel文件：

现在需要在右侧增加一列，计算取样数据的累计汇总值，汇总函数由Z列给出的函数名决定，如果是median，则算中位数；如果是avg则算平均值；sum则求和；依此类推。

在AA3单元格中输入表达式：=spl("=?.conj()."&Z3&"()",$B$3:Y3)，第一个参数"=?.conj()."&Z3&"()"是动态拼出的SPL表达式，第二个参数$B$3:Y3传入从B3到Y3组成的数组。

传入参数$B$3:Y3中，第一个单元格加了$符号，因为算的是累计汇总，在向下复制该表达式时要保持始终从B3单元格开始累计，该单元格不能变化。

把AA3单元格的表达式拖动复制到每一行，最后算出所有行的累计汇总值。

第 3 章 SPL 的编辑

某些计算的SPL表达式可能很复杂，甚至不止一句，直接在Excel中编辑时很不方便，SPL IDE 提供了Excel Copy/Paste功能，可以将编辑好的代码复制到Excel中使用

示例12：用 SPL IDE 生成复杂SPL脚本

有Excel数据文件如下所示：

现在需要在底下增加一行，计算1月份中，每个时辰的取样数据的平均值，希望写一个表达式，就把整行的平均值都算出来，不需要每个格子都复制一遍表达式。

计算结果如下所示：

本例需要把B2:Y33作为参数，打开 SPL IDE，定义参数如下：

注意：这里参数名称必须是B2:Y33。

编辑脚本：

	A
1	=E('B2:Y33')	/将传入的二维数组参数'B2:Y33'变成序表，第一行当列名。因为参数名和单元格同名，要加上单引号区分
2	=to(24).("avg('"+string(~)+"'):'"+string(~)+"'")	/因为列名为1-24，为了简化书写，动态拼出24个列的汇总表达式
3	=A1.groups(;${A2.concat@c()})	/将汇总表达式拼入groups函数里，算出所有字段的平均值
4	return A3(1).array()	/将结果记录转成单层序列并返回

接着点击菜单【编辑】->【复制】->【Excel 复制】，此时即可把代码复制到剪贴板上。

回到Excel文件，在单元格B34中，按ctrl-v即可把代码粘贴进来，如下所示：

此代码返回的是一维数组，按ctrl-enter，把返回数组值按顺序向右填到一行单元格上，最后结果如下所示：

补充说明：

1、当返回结果为一维数组时，扩展方式默认为纵向，用户可以在 Excel 中点击【加载项】-【SPL】-【Show Setting…】选择扩展方式：【Horizontal display】未勾选状态表示纵向扩展，勾选则为横向扩展。按 ctrl-enter 后，纵向扩展时结果集向下按顺序填充单元格，横向扩展时结果集向右按顺序填充单元格

2、当传给SPL脚本的参数是一片(单行、单列、多行多列)单元格时，会自动组成两层序列，此时在SPL脚本中可以用E()函数转成序表

3、参数的规则和单行代码一样，用?1表示第一个参数，?2表示第二个参数，依此类推

示例13：返回 SPL IDE 中修改脚本

接上例，代码复制到Excel中使用后，我们可能把 SPL IDE 关了，代码也没有保留，此时如果需要修改此代码，直接在Excel里修改肯定不方便，我们可以用 SPL IDE 的Excel Paste的功能把SPL脚本复制回 SPL IDE 中修改，操作方法如下：

在Excel中选中spl完整代码，按ctrl-c把代码复制到剪贴板上，然后打开 SPL IDE，点击菜单【编辑】->【粘贴】->【Excel 粘贴】，此时即可把代码原样恢复到 SPL IDE 中，如下所示：

示例14：超长SPL脚本

现有Employee.xlsx文件（该文件必须放在 SPL IDE 的主目录下）内容如下：

需要对该文件做查询，查询结果只需要显示EID,NAME,GENDER,DEPT,SALARY,BIRTHDAY,HIREDATE,STATE这几个字段，SURNAME字段不用显示，查询条件如下：

此例中需要传到SPL脚本中的参数有B1, B2, D1, D2, F1, F2

打开 SPL IDE，首先定义参数如下：

编辑脚本如下：

接着点击菜单【编辑】->【复制】->【Excel 复制】，此时即可把多行代码复制到剪贴板上。

再打开Excel，在单元格A4中，按ctrl-v，就把代码粘贴进来了，如下所示：

由于Excel表达式中的字符串有长度限制，超长代码复制至Excel时会自动被拆成多段，每段长度不超过240，代码段用\结尾，表示代码尚未结束，下一个参数依旧是代码

输入完上述代码后，按ctrl-enter，结果如下：

示例15：直接调用SPL脚本文件

对于较长的SPL代码，也可以不把SPL脚本复制到Excel中，而是在spl()函数里直接调用SPL脚本文件。如上例，我们把SPL脚本文件存为【SPL IDE 主目录】\Example15.splx，然后在Excel的A4单元格中写入如下表达式：

从表达式中可以看出，直接调用脚本文件的语法是：=spl("脚本文件名(?n……)",参数n….)，脚本有几个参数，括号里就写几个问号，和传入参数的对应关系规则与直接写代码是一样的，都是?1对应第一个参数,?2对应第二个参数，依此类推

输入完上述代码后，按ctrl-enter，结果如下：