A project I am working on requires values from a C# application to update 6 values in an Excel sheet. This excel sheet then populates a named range of items and prices, driven by a number of VLOOKUP formulas and worksheets within the same Excel File.
我正在处理的项目需要来自C#应用程序的值来更新Excel工作表中的6个值。然后,此Excel工作表将填充指定的项目和价格范围,由同一Excel文件中的多个VLOOKUP公式和工作表驱动。
The two columns in the range are named 'Item' & 'Price'.
范围中的两列名为“Item”和“Price”。
'Item' is a column of items concatenated from formulas on a spreadsheet, and these appear fine in the DataTable. However, none of the values in the 'Price' column (all number values from VLOOKUP formulas) appear in the DataTable.
“项目”是从电子表格中的公式连接的项目列,这些项目在DataTable中显示正常。但是,“价格”列中的值(VLOOKUP公式中的所有数值)都不会出现在DataTable中。
When loading the DataTable in C#, I can see that the two columns' types of string and double are fine. If I replace one of the formula derived values with a hard-coded value the DataTable is fine, so I guess the DataTable itself is working as expected.
在C#中加载DataTable时,我可以看到两列的string和double类型都很好。如果我用硬编码值替换其中一个公式派生值,DataTable就可以了,所以我猜DataTable本身正在按预期工作。
If I open the Excel file after the values are passed into it, I can see that the input values went in fine and the formulas have calculated correctly.
如果我在传递值后打开Excel文件,我可以看到输入值很好并且公式已正确计算。
So, the only thing I can think is causing the problem is that the formulas are not being recalculated after the values are passed into it.
因此,我唯一能想到的是导致问题的是在将值传递给公式之后不会重新计算公式。
Is there a way to open an excel file in C# and get it to recalculate all the formulas?
有没有办法在C#中打开excel文件并让它重新计算所有公式?
1 个解决方案
#1
Never mind,
Replaced the OLEDB update with an Excel Interop, opening the Excel file with the Interop allows the formulas to recalculate.
用Excel Interop替换OLEDB更新,使用Interop打开Excel文件允许公式重新计算。
#1
Never mind,
Replaced the OLEDB update with an Excel Interop, opening the Excel file with the Interop allows the formulas to recalculate.
用Excel Interop替换OLEDB更新,使用Interop打开Excel文件允许公式重新计算。