GcExcel操作IRange耗时过长

调研编号：DOCXLS-10431

GcExcel操作IRange.setFormula及IRange.setValue方法,在表格较大时耗时过长,


具体请下载提供的示例代码,执行Test.java文件

这里的一个单元格耗时普遍超过200ms,数据量很大的时候非常慢

BND 发表于 2024-5-30 09:59
按你这边给过来的方案，本地测试，耗时减少很多。

方案的核心可不可以按如下理解？

您的理解正确，在公式中尽量避免A:A，使用有效数据区域。

Ellia.Duan 发表于 2024-5-29 18:25
您好，我上传了了一下新的项目，请参考附件

excel文件更新为new_all.xlsx

按你这边给过来的方案，本地测试，耗时减少很多。

方案的核心可不可以按如下理解？

Excel中区域的参数如A:A、B:B、......会被视为A1:A1000000、B1:B1000000、......进行处理。
尽可能将公式中选择区域的参数如A:A、B:B、......替换为A1:A100000、B1:B100000、......
至于后面是100000还是10000或者更小，可视区域数据最大行数决定，只要不小于区域数据最大行数，就不会影响结果。

您好，我上传了了一下新的项目，请参考附件
gcexceldemo5.zip (19.35 MB, 下载次数: 36)

2024-5-29 18:25 上传

点击文件名下载附件
excel文件更新为new_all.xlsx
变化如下：


我说一下此公式的意义。
原来的公式是
=IFERROR（XLOOKUP***））
IFERROR计算1000列，XLOOKUP引用的单元格是A:A&D:D 由于Excel中没有行数的概念，A:A是百万行数据。
也就是说计算此公式，每次要计算1000*1000000
所以，我们对其，设置动态获取当前数据区域的范围

1. =LET(count,MAX(MAX(IF(ISBLANK(A:A),0,ROW(A:A))),MAX(IF(ISBLANK(D:D),0,ROW(D:D))),1),OFFSET(A1,0,0, count,1)&OFFSET(D1,0,0,count,1))

复制代码

上述公式是为了动态获取A:A&D:D的数据有效区域，如果您提前设置好了数据区域，也可以不用此公式。

然后将194行的代码替换为下面的代码

1. (column = columnArray[37]).setFormula2("=IFERROR(ROUND(@IF(OR(AND(LEFT(J2,7)=N2,--RIGHT(J2,2)>15),@XLOOKUP(C2&N2,总裁办计薪_绩效考核异常_线下数据源!K:K,总裁办计薪_绩效考核异常_线下数据源!C:C,"")="否"),W2,XLOOKUP(C2&N2,总办计薪绩效金额0412!Q:Q,总办计薪绩效金额0412!P:P))/O2*P2,2),0)");
   
2.       

复制代码

下图是我的测试数据

在配置好一点的电脑上，getValue可以达到两秒

Ellia.Duan 发表于 2024-5-29 12:23
您好，我们经过调研，发现有一个可以优化的公式，优化后getValue耗时缩短为12秒，如下图所示：

按你说的方式进行了处理，本地测试多次，耗时23~30秒之间，并未有提升。
附上demo《gcexceldemo2.rar》，麻烦看一下。

您好，我们经过调研，发现有一个可以优化的公式，优化后getValue耗时缩短为12秒，如下图所示：



具体优化过程是：
在“总裁办计薪_绩效考核异常_线下数据源”sheet中 在K1单元格输入公式“=A:A&D:D”
然后将您的代码中194行代码

1. //(column = columnArray[37]).setFormula2("=IFERROR(ROUND(@IF(OR(AND(LEFT(J2,7)=N2,--RIGHT(J2,2)>15),@XLOOKUP(C2&N2,总裁办计薪_绩效考核异常_线下数据源!A:A&总裁办计薪_绩效考核异常_线下数据源!D:D,总裁办计薪_绩效考核异常_线下数据源!C:C,"")="否"),W2,XLOOKUP(C2&N2,总办计薪绩效金额0412!B:B&总办计薪绩效金额0412!E:E,总办计薪绩效金额0412!P:P))/O2*P2,2),0)");

复制代码

替换为

1. (column = columnArray[37]).setFormula2("=IFERROR(ROUND(@IF(OR(AND(LEFT(J2,7)=N2,--RIGHT(J2,2)>15),@XLOOKUP(C2&N2,总裁办计薪_绩效考核异常_线下数据源!K:K,总裁办计薪_绩效考核异常_线下数据源!C:C,"")="否"),W2,XLOOKUP(C2&N2,总办计薪绩效金额0412!B:B&总办计薪绩效金额0412!E:E,总办计薪绩效金额0412!P:P))/O2*P2,2),0)");

复制代码

替换的内容是


这样子做的结果是：
每次IFERROR计算，将不会重新对XLOOKUP中数百万行数据重新计算，新增的K列相当于是一个缓存。
关于类似公式，也可以这样子处理
关于其他优化，我们还在调研中，有结论继续会发给您。

Ellia.Duan 发表于 2024-5-29 09:43
您好，解压您的文件，运行时长如下图所示

观察到项目中GcExcel不是最新版本，所以做了下新版本的测试，7 ...

这个事情挺急的，麻烦尽快处理。

我们所有环境已经上线7.1.2版本，我之前提到的实际业务场景给出的整个过程的耗时也是基于该版本。

只是本地demo的没有调整gcexcel版本。

您好，解压您的文件，运行时长如下图所示

观察到项目中GcExcel不是最新版本，所以做了下新版本的测试，7.1.2版本 getValue用时28秒， 6.0.6版本getValue用时117秒。
所以新版本会快很多。


同时，我在Excel中打开生成的xlsx文件，如下动图所示，大概用时23秒




我将在7.1.2版本上继续为您调研，是否还有优化空间。

Ellia.Duan 发表于 2024-5-28 14:48
您好，
首先回答您关于开启迭代运算后，setFormula 耗时较长的原因：

这是基本按实际场景的数据规模与公式，本地测试得到的耗时情况。
麻烦支持，提供优化方案。

demo详见《gcexceldemo2.rar》附件

您好，
首先回答您关于开启迭代运算后，setFormula 耗时较长的原因：

迭代运算主要是为了解决循环引用的问题，开启迭代运算，setFormula时，会有一部分公式计算。再此过程中，因为有公式计算，所以getValue耗时较短。
而关闭迭代运算，设置延迟标脏，此时setFormula ，对此部分内容不标脏，即不计算，等到设置延迟标脏结束，此时getValue ,公式参与计算。

根据您提供的测试数据：
方案一、三 setFormula 、getValue 总体耗时大致相同 ，说明此耗时是当前文件公式计算的整个耗时，已经排除了重复计算的时间。
是目前GcExcel可以提供最优性能方案了。

对于您提供的demo ,方案三总体耗时1.8s ，目前来看是正常的。
最后，由于没有您复杂的报表，所以想了解下，此报表在Excel中打开需要多久，或者公式计算有多久？