【记录】生成Excel 单元格里公式无法自动执行

这几天做了这个命令行的任务，感觉解决的过程太诡异了，必须得记录一下，所以写了本文。

可能对其他朋友也有点参考作用吧。

 

任务要求：

使用一个命令行的程序

1.调用数据库的存储过程，获得一个数据集。

2.利用此数据集生成Excel.

3.把Excel作为附件发送出去。

 

需要注意的是，第一行是列头，要显示那种下拉框的效果(其实就是自动筛选，但并没有筛掉任何数据)。

然后数据集只有10列，而Execl要求有13列，即最后的3列是使用了公式来计算的。

比如 第二行的最后3列， 就是利用Excel公式，根据本行前面几个单元格里的数据来计算出的。（其实只用到了第9和第10格）

如下图：

问题就出在最后3列的公式无法自动计算，一直显示的是 #NAME?

 

公式我提前赋给3个string，然后在遍历的时候，用当前的行号来替换string里的占位符，

这样就可以为3列赋值了。

关键代码如下：

 

string TAT86 = " =IF(NETWORKDAYS(I_RowNumber,J_RowNumber)=0, 0, IF(DATE(YEAR(I_RowNumber),MONTH(I_RowNumber),DAY(I_RowNumber)) = DATE(YEAR(J_RowNumber),MONTH(J_RowNumber),DAY(J_RowNumber)), IF(((HOUR(J_RowNumber)-1)-HOUR(I_RowNumber))<0,0,((HOUR(J_RowNumber)-1)-HOUR(I_RowNumber))), IF(NETWORKDAYS(I_RowNumber,I_RowNumber) = 0, 0, IF(HOUR(I_RowNumber)>=18, 0, IF(HOUR(I_RowNumber)<8, 10, HOUR(((DATE(YEAR(I_RowNumber), MONTH(I_RowNumber), DAY(I_RowNumber)))+TIME(18,0,0))-I_RowNumber))))+IF(NETWORKDAYS(I_RowNumber+1,J_RowNumber-1)<=0, 0, NETWORKDAYS(I_RowNumber+1,J_RowNumber-1)*10) + IF(NETWORKDAYS(J_RowNumber,J_RowNumber) = 0, 0, IF(HOUR(J_RowNumber)>=20, 10, IF(HOUR(J_RowNumber)<8, 0, HOUR((J_RowNumber-TIME(8,0,0)))))))) " ;
string TAT88 = " =IF(NETWORKDAYS(I_RowNumber,J_RowNumber)=0, 0, IF(DATE(YEAR(I_RowNumber),MONTH(I_RowNumber),DAY(I_RowNumber)) = DATE(YEAR(J_RowNumber),MONTH(J_RowNumber),DAY(J_RowNumber)), IF(((HOUR(J_RowNumber)-1)-HOUR(I_RowNumber))<0,0,((HOUR(J_RowNumber)-1)-HOUR(I_RowNumber))), IF(NETWORKDAYS(I_RowNumber,I_RowNumber) = 0, 0, IF(HOUR(I_RowNumber)>= 20, 0, IF(HOUR(I_RowNumber) < 8, 12, HOUR(((DATE(YEAR(I_RowNumber), MONTH(I_RowNumber), DAY(I_RowNumber))) + TIME(20,0,0)) - I_RowNumber)))) + IF(NETWORKDAYS(I_RowNumber + 1,J_RowNumber - 1) <= 0, 0, NETWORKDAYS(I_RowNumber + 1,J_RowNumber - 1) * 12) + IF(NETWORKDAYS(J_RowNumber,J_RowNumber) = 0, 0, IF(HOUR(J_RowNumber) >= 20, 12, IF(HOUR(J_RowNumber) < 8, 0, HOUR((J_RowNumber - TIME(8,0,0)))))))) " ;
string TAT24 = " =IF(NETWORKDAYS(I_RowNumber,J_RowNumber)=0, 0,IF(DATE(YEAR(I_RowNumber),MONTH(I_RowNumber),DAY(I_RowNumber))=DATE(YEAR(J_RowNumber),MONTH(J_RowNumber),DAY(J_RowNumber)),IF(((HOUR(J_RowNumber)-1)-HOUR(I_RowNumber))<0,0,((HOUR(J_RowNumber)-1)-HOUR(I_RowNumber))),IF(NETWORKDAYS(I_RowNumber,I_RowNumber)=0,0,HOUR((DATE(YEAR(I_RowNumber+1),MONTH(I_RowNumber+1),DAY(I_RowNumber+1)))-I_RowNumber))+IF(NETWORKDAYS(I_RowNumber+1,J_RowNumber-1)<=0,0,NETWORKDAYS(I_RowNumber+1,J_RowNumber-1)*24)+IF(NETWORKDAYS(J_RowNumber,J_RowNumber)=0,0,HOUR(J_RowNumber)))) " ;


Excel.Range _row_Range;

for ( int excel_row = 2 ; excel_row <= dt.Rows.Count + 1 ; excel_row ++ )
{
for ( int excel_col = 1 ; excel_col <= 8 ; excel_col ++ )
{
excelWorksheet.Cells[excel_row,excel_col] = dt.Rows[excel_row - 2 ][excel_col - 1 ].ToString(); // dt[excel_row-2,excel_col-1];
}

for ( int excel_col = 9 ; excel_col <= 10 ; excel_col ++ )
{
excelWorksheet.Cells[excel_row,excel_col] = Convert.ToDateTime(dt.Rows[excel_row - 2 ][excel_col - 1 ].ToString()) ; // dt[excel_row-2,excel_col-1];
}

excelWorksheet.Cells[excel_row, 11 ] = TAT86.Replace( " _RowNumber " , Convert.ToString(excel_row ));
excelWorksheet.Cells[excel_row, 12 ] = TAT88.Replace( " _RowNumber " , Convert.ToString(excel_row ));
excelWorksheet.Cells[excel_row, 13 ] = TAT24.Replace( " _RowNumber " , Convert.ToString(excel_row ));

}

当然，公式的赋值也可以用如下的方式：

 

//should use method get_range(start cell, end cell) to get the range.
_row_Range.Formula = " =xxx " ;

只是在我的这个版本里，Cell 木有 ‘Formula’这个属性，而且转换成RANGE的话也会增加额外的代码，

所以直接在遍历CELL的时候就赋给公式了。

btw, 不管是给cell赋值还是RANGE都不影响显示为 #NAME?  囧...

 

主要的过程就是上面贴的。

运行出来的结果就是 最后3列显示为 #NAME?

但是点击一个cell，然后在编辑公式那里按回车，结果就能显示了。

难得重新截图，直接把邮件截张图上来算了（真懒。。。）：

这就说明了Excel能计算这公式，但在生成的时候却没计算。。。

（奇怪的是，我明明勾选了自动计算，但打开的时候没见到效果啊，不解）

 

昨天下午，我实在没办法了，就把代码从A服务器拿到B服务器去试试。

A服务器安装的是VS2002和VS2005，以及EXCEL2003.

没错，是VS2002，不是2003！

B服务器是 VS2005 & VS2008 以及EXCEL 2007.

结果在B服务器上生成的文件没问题！

 

严格的话这样说，A服务器生成的文件，不管是在A服务器上查看，还是在我本机上看都是 #NAME?  （本机有EXCEL2003 &EXCEL 2007）

B服务器生成的文件不管在哪里看都是正确的。

马上给上面反映了，结果回复说产品服务器是用的excel 2003，让我想办法。。。

 

//邪恶的分割线========================

这几天也查了资料，试了很多办法都不行。。。

就在今天上午，突然想到了可能是插件没发挥作用。

终于快接近真相了。。。

 

看下图：

坑爹的MSDN啊，尼玛这哪里是可选参数啊啊！！

（MSDN是 for EXCEL 2003,明显我的也是2003。。。）

 

然后也试过用程序来加载ANALYS32.XLL 和ATPVBAEN.XLA，但一直报错。

这两个文件其实就是下面的两个插件：

 

我遍历了实例化出来的Excel对象所有的Add-ins，才发现这两个都已经加载而且 Install 属性等于 True了。

无奈又去查资料，话说今天公司的网络很不错啊。

下面的这图提示了我：

 

我加了2行代码：

excelApp.RegisterXLL(_AddIn_Anal);              

excelApp.RegisterXLL(_AddIn_Anal_VBA);

结果就搞定了！

注意，_AddIn_Anal_VBA 是指向的ATPVBAEN.XLA文件，这个XLL方法居然也能加载，表示很给力哈。

打字真累，比敲代码累多了。。。

 

差点忘了，这3列里的公式有点特殊，其中的 NETWORKDAYS() 方法 需要ANALYS32.XLL 和ATPVBAEN.XLA的支持才能运行。

（或许只要后面的.XLA，但最好两个都加上）。

这才是引起问题的源头！

顺带附上公式如下：

=IF(NETWORKDAYS(I5,J5)=0, 0, IF(DATE(YEAR(I5),MONTH(I5),DAY(I5)) = DATE(YEAR(J5),MONTH(J5),DAY(J5)), IF(((HOUR(J5)-1)-HOUR(I5))<0,0,((HOUR(J5)-1)-HOUR(I5))), IF(NETWORKDAYS(I5,I5) = 0, 0, IF(HOUR(I5)>= 20, 0, IF(HOUR(I5) < 8, 12, HOUR(((DATE(YEAR(I5), MONTH(I5), DAY(I5))) + TIME(20,0,0)) - I5)))) + IF(NETWORKDAYS(I5 + 1,J5 - 1) <= 0, 0,NETWORKDAYS( I5 + 1,J5 - 1) * 12) + IF(NETWORKDAYS(J5,J5) = 0, 0, IF(HOUR(J5) >= 20, 12, IF(HOUR(J5) < 8, 0, HOUR((J5 - TIME(8,0,0))))))))

 

码字累，收工！坐等下班

转载于:https://www.cnblogs.com/SeraphWU/articles/2094766.html