【记录】生成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))))))))
码字累，收工！坐等下班
