通过c#刷新并保存excel文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/392818/
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
refresh and save excel file via c#
提问by
I use this code to open refresh save and close excel file:
我使用此代码打开刷新保存并关闭excel文件：
Application excelFile = new Application();
Workbook theWorkbook = excelFile.Workbooks._Open(Environment.CurrentDirectory + "/WebGate", 0, false, 5, System.Reflection.Missing.Value, System.Reflection.Missing.Value, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value, true, false, System.Reflection.Missing.Value, false);
Sheets sheets = (Sheets)theWorkbook.Worksheets;
theWorkbook.RefreshAll();
theWorkbook.Save();
excelFile.Quit();
the problem is that there is a conflict between the refresh and save commands because the file is been save while the refresh is been done in the background (I guess) can anyone help me with this? i need to know how can I know when the refresh proccess is done or any other indication that will help my to decide when should I save the file without harm the refresh proccess
问题是刷新和保存命令之间存在冲突，因为文件是在后台完成刷新时保存的（我猜）任何人都可以帮助我吗？我需要知道如何知道刷新过程何时完成或任何其他指示可以帮助我决定何时应该保存文件而不损害刷新过程
回答by shahkalpesh
Eran, I am posting this based on what I understand of documentation.
Eran，我是根据我对文档的理解发布的。
I assume you are using pivottables & it uses some kind of query.
Try using Workbook object's PivotTableCloseConnection & see if you can call Save inside it.
我假设您正在使用数据透视表并且它使用某种查询。
尝试使用 Workbook 对象的 PivotTableCloseConnection 并查看是否可以在其中调用 Save 。
In Excel 2007, Application object has AfterCalculate event - which could also be of use.
在 Excel 2007 中，Application 对象具有 AfterCalculate 事件 - 这也可能有用。
How does one simulate this scenario?
如何模拟这一场景？
回答by shahkalpesh
Again, I have looked at the documentation and here is what I can say.
再次，我查看了文档，这是我能说的。
Get hold of the QueryTable object you are refreshing. It has an event named "AfterRefresh" which you can use to take any action.
获取您正在刷新的 QueryTable 对象。它有一个名为“AfterRefresh”的事件，您可以使用它来执行任何操作。
Also, instead of doing Refresh on workbook, do a Refresh on the specific QueryTable (unless you have multiple QueryTables). The QueryTable has a Refresh method, which takes a boolean parameter named BackGroundQuery, which you can set to False.
此外，不要在工作簿上刷新，而是在特定的 QueryTable 上刷新（除非您有多个 QueryTable）。QueryTable 有一个 Refresh 方法，它采用名为 BackGroundQuery 的布尔参数，您可以将其设置为 False。
I guess, this will query the records synchronously.
Does that work for you?
我猜，这将同步查询记录。
那对你有用吗？
回答by Adarsha
I am not familiar with C#, but I am good at Excel VBA. The problem here is most of the Pivot tables will have BackgroundQuery property set to True, making the pivot tables refresh asynchronously so that the Excel file remians responsive when used by end user. If you are not adding any new pivot tables during your transaction with the file then you can fix the file once by unchecking the BackgroundQuery in Pivot Table->Table setting->BackgroundQuery under External Data Options. If you are adding a pivot table you need to set this property to false like
我不熟悉 C#，但我擅长 Excel VBA。这里的问题是大多数数据透视表将 BackgroundQuery 属性设置为 True，使数据透视表异步刷新，以便 Excel 文件在最终用户使用时保持响应。如果您在与文件的交易过程中没有添加任何新的数据透视表，那么您可以通过取消选中数据透视表中的背景查询 -> 表设置 -> 外部数据选项下的背景查询来修复该文件。如果要添加数据透视表，则需要将此属性设置为 false，例如
Dim oPivot As PivotTable
set oPivot=worksheets("xyz").PivotTables("Pivot1")
oPivot.PivotCache.BackgroundQuery = False
If you are not sure which table to fix and have lot of tables in your excel then use the below code in Excel VBA to fix it.
如果您不确定要修复哪个表并且您的 excel 中有很多表，请在 Excel VBA 中使用以下代码来修复它。
Public Sub FixPivotTables()
Dim oPivot As New PivotTable, oSheet As Worksheet
For Each oSheet In ThisWorkbook.Worksheets
For Each oPivot In oSheet.PivotTables
oPivot.PivotCache.BackgroundQuery = False
Next
Next
End Sub
回答by huncyrus
Try to use:
尝试使用：
this.Application.ActiveWorkbook.RefreshAll();
this.Application.ActiveWorkbook.Save();
If necessary use the .ReCalculate()action.
如有必要，请使用该.ReCalculate()操作。
