C# 如何自动刷新excel公式?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1200208/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-06 10:44:23  来源:igfitidea点击:

How to automatically refresh excel formulas?

c#.netexcel

提问by Victor Rodrigues

I'm experiencing big problems using ExcelPackage, because sometimes the formulas are not updated (even if I remove the value).

我在使用 ExcelPackage 时遇到了大问题,因为有时公式没有更新(即使我删除了该值)。

I've tried using ExcelInterop to save xlsx as xls, thinking that this could resolve the issue, but it didn't.

我尝试使用 ExcelInterop 将 xlsx 保存为 xls,认为这可以解决问题,但没有。

The way I've found to resolve it is by pressing CTRL + ALT + F9. The user will not like doing it everytime, so, I want to do it programatically. Do you know any effective way to ensure all xlsx formulas are updated?

我发现解决它的方法是按 CTRL + ALT + F9。用户不会喜欢每次都这样做,因此,我想以编程方式进行。您知道确保所有 xlsx 公式更新的有效方法吗?

采纳答案by Scoregraphic

Call Application.CalculateFullfrom a VBA macro (or over the COM object)

从 VBA 宏(或通过 COM 对象)调用Application.CalculateFull

回答by Adriaan Stander

I have found this to work for me before:

我以前发现这对我有用:

for (int iWorksheet = 1; iWorksheet <= workbook.Worksheets.Count; iWorksheet++)
                {
                    Worksheet ws = ((Worksheet)workbook.Worksheets[iWorksheet]);
                    ws.UsedRange.Formula = ws.UsedRange.Formula;
                    ws.Calculate();
                }

回答by Joe Erickson

SpreadsheetGear for .NETcan open, calculate and save xls and xlsx workbooks without Excel and tends to be easier to work with from .NET applications because it is entirely .NET code (written in C#).

SpreadsheetGear for .NET可以在没有 Excel 的情况下打开、计算和保存 xls 和 xlsx 工作簿,并且更容易从 .NET 应用程序使用,因为它完全是 .NET 代码(用 C# 编写)。

You can see some examples hereand download the free trial hereif you want to try it out.

你可以看到一些例子在这里和下载免费试用这里,如果你想尝试一下。

Disclaimer: I own SpreadsheetGear LLC

免责声明:我拥有 SpreadsheetGear LLC

回答by da_m_n

You can always set Application.Calculation = xlAutomatic. That way you don't rely on your own code to perform the calculation and it's called only when needed.

您始终可以设置Application.Calculation = xlAutomatic. 这样您就不必依赖自己的代码来执行计算,并且仅在需要时才调用它。

Note that this setting affects the entire Excel instance. So if your client doesn't want other workbooks work like this - they should open it in a separate Excel instance.

请注意,此设置会影响整个 Excel 实例。因此,如果您的客户不希望其他工作簿像这样工作 - 他们应该在单独的 Excel 实例中打开它。

回答by andoke

In C#, use Application.Calculate()for evaluating all open workbooks, Workbook.Calculate()in order to evaluate a workbook or Worksheet.Calculate()for a worksheet.

在 C# 中,Application.Calculate()用于评估所有打开的工作簿,Workbook.Calculate()以便评估工作簿或Worksheet.Calculate()工作表。

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application.calculate.aspx

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application.calculate.aspx