GcExcel操作IRange耗时过长

Ellia.Duan 发表于 2024-5-27 09:30
您好，想问下您，您提到了整个计算过程不超过5秒，这个过程为什么要包括获取值的过程？
以及，需要您提供 ...

取值是为了数据入库，最终的结果（Table数据）是要入库给到前端展示与交互，以便接着处理后续业务流程。

我给的demo就包含了取值的，只是数据体量没真实业务场景那么大，但是过程是一样的，也重现了性能问题。

您好，您提到的场景“Excel模板插入80列以上的Table，加载1000行左右的数据，其中60多列设置Excel公式进行运算，公式引用来自20多个页签的数据，页签数据在1000行~10000行不等"

目前测试demo中 设置值与计算值不到2秒，我们要复现您的问题。
我们需要您提供具体的demo ，时间越长越好。我们来看这个性能。

Ellia.Duan 发表于 2024-5-27 18:19
您好，您提到的场景“Excel模板插入80列以上的Table，加载1000行左右的数据，其中60多列设置Excel公式进行 ...

现在的demo，调用getValue对比时间多出了近2倍，还没有复现问题？
我已经说的很明白了。过程一样，只是数据体量不一样。

您好，我将深入调研下下面两个问题：
1、开启迭代运算后，公式重计算问题。
2、关闭迭代计算后，getValue耗时较长问题。

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

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

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

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

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

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

demo详见《gcexceldemo2.rar》附件

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

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


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




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

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

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

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

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

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

您好，我们经过调研，发现有一个可以优化的公式，优化后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 12:23
您好，我们经过调研，发现有一个可以优化的公式，优化后getValue耗时缩短为12秒，如下图所示：

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