Last post Sep 08, 2014 09:53 AM by Huk
Sep 05, 2014 12:45 AM|Huk|LINK
I have an xlsx document with pivotal tables, I need to open it, find all tables, list all filters (slicers, as well as table specific ones), then replace some of them with the values provided by our code and then refresh the table. I use Microsoft.Office.Interop.Excel
but either I'm doing something wrong, or I don't understand something, because I get weird exceptions. For example: I use the following to get current filter strings:
foreach (Microsoft.Office.Interop.Excel.Worksheet worksheetItem in Worksheets)
Microsoft.Office.Interop.Excel.PivotTables pivotTables = worksheetItem.PivotTables();
int pivotTablesCount = pivotTables.Count;
if (pivotTablesCount > 0)
for (int i = 1; i <= pivotTablesCount; ++i)
string objectName = pivotTables.Item(1).Name;
Microsoft.Office.Interop.Excel.PivotFields activeFilters = pivotTables.Item(i).get_PageFields(Type.Missing);
int count = activeFilters.Count;
IList<Microsoft.Office.Interop.Excel.PivotField> activeFiltersList = new List<Microsoft.Office.Interop.Excel.PivotField>();
foreach (Microsoft.Office.Interop.Excel.PivotField activeFilter in activeFilters)
var currentFilterString = activeFilter.CurrentPageName;
While the above works when our pivotal table is filtered by single value, it throws the following:
An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll
Additional information: Wyjątek od HRESULT: 0x800A03EC
when there is more then one value applied to filter, what is even weirder is that when I expand "activeFilter" in the Visual debugger, I can see a lot of other exceptions inside the object:
What am I doing wrong here? Is it at all advisable to use Microsoft.Office.Interop on the server? If not - what alternatives would you recommend? I know there is NPOI, but I fear it may not support all the features of pivotal tables we use...
Thanks in advance.
Sep 05, 2014 05:40 PM|markfitzme|LINK
You would be better to ask this in the office developer forums but to answer one question, no, it is absolutely never advisable to use office interop on the server. Microsoft has been recommending against this since the late 1990's. The threading models
are all wrong for a web application for one thing, permissions for the web application need to be expanded to call the office apps (which is never advisable), they can end up leaking memory because of garbage collection timing, etc.. I've seen apps that use
office ruin a server installation before.
Best option is to use a managed code library that can work with the files themselves without the need to open the office app. The OpenXML SDK can be used with Office 2007 and later versions.
Other third-party components are available. Infragistics, Syncfusion, ComponentOne, and Aspose, all make components that can communicate with Excel but are commercial products.
Sep 08, 2014 09:53 AM|Huk|LINK
Thanks for the answer, and sorry for my late replay.
I tried using OpenXML SDK, but it seems, that editing xlsx in a way I want to achieve is not an easy task, I will direct other questions to the Office developer forum. Thanks again.