关于使用EPPlus插入列，名称管理器公式失效问题案列分析

 

1.背景描述

   昨天收到同事反馈，说epplus使用模板导出后，多级联动失效。一时来了兴趣，针对此问题分析，后来发现联动失效并不是epplus使用上的问题，而且此问题出现的主要原因是因为对sheet上的列进行了增加或者减少。

   此问题在百度和goole上都找了一下，发现均没有答案。只能自己动手分析了。

2.分析过程

    第一个想法就是读取epplus源码，从源码中发现问题。所以下载了epplus的源码，由于电脑系统刚装，epplus的源码又是使用vs2017的发布的。所以打开源码后报错太多。只能在下班后，下载安装vs2017。

    第二天早上，vs2017已安装完毕，愉快的打开epplus源码，经过分析，发现epplus在读取execl的时候，名称管理器的公式已经固化了。所以在最终保存的时候，如果出现增加或者删除列的情况，跟列相关的公式就会失效。

3.解决过程

    因为以前就稍微了解execl，所以知道execl其实就是压缩文件。所以第一步，我先把execl文件重命名为zip文件，然后对其解压。

    

    解压之后就得到了如图的几个文件，接着打开xl文件夹，打开workbook.xml文件。这是我们可以看到我们的公式都在里面

    

    经过读取公式，我们发现$O1其实代表的就是execl的列位置。当我们得到列位置，那么如果我们增加列或者减少列，那么我们就可以修改这里的公式，让其保存之后的公式仍然有效。

    接下来我们在epplus中写入如下代码：

    

ws.Workbook.Names.FirstOrDefault(j => j.Name == "SubCommodity").Formula = "OFFSET(Commodity!$C$1,MATCH(BOM!$Y1,Commodity!$B:$B,0)-1,,COUNTIF(Commodity!$B:$B,BOM!$Y1))";
ws.Workbook.Names.FirstOrDefault(j => j.Name == "Commodity_Description").Formula = "OFFSET(Commodity!$D$1,MATCH(BOM!$Z1,Commodity!$C:$C,)-1,,,COUNTA(OFFSET(Commodity!$D$1:$IV$1,MATCH(BOM!$Z1,Commodity!$C:$C,)-1,)))";

 

    这里代码的意思就是重新写入公式，然后根据自己的情况，把正确的列位置修改。

    至此，多级联动失效的问题得到解决。此解决方案为临时方案。