|
|
Excel.PivotTable.SourceData |
|
Author |
Message |
Mephistopheies
|
Posted: .NET Framework Data Access and Storage, Excel.PivotTable.SourceData |
Top |
Hello all,
I wrote this code
Excel. PivotTable pt = (Excel.PivotTable)wksheet.PivotTables(1);
try
{
pt.SourceData = new string[] { connection, query };
pt.RefreshTable();
}
catch
{
throw;
}
finally
{
System.Runtime.InteropServices. Marshal.ReleaseComObject(pt);
} and it works, but there is one problem. If I open Profiler and debud code, I see that query is executed 4 times. 3 times in line "pt.SourceData = new string[] { connection, query };" and 1 times here "pt.RefreshTable();". It takes too long time. Where is the problem
.NET Development28
|
|
|
|
|
alazela
|
Posted: .NET Framework Data Access and Storage, Excel.PivotTable.SourceData |
Top |
Execution of the query must be triggered in the Excel COM object. ADO.Net (the topic of this forum) is not going to be involved. You should try asking this question on an Excel forum.
|
|
|
|
|
Cindy Meister
|
Posted: .NET Framework Data Access and Storage, Excel.PivotTable.SourceData |
Top |
this question actually has nothing to do with the VSTO technology - the topic of this forum. I'm not an expert for Excel, so I don't know if the issue is .NET-related or whether the problem is inherent to the Excel object model. You could try asking in the excel.programming newsgroup whether setting the SourceData property is known to execute the query multiple times and if there's any way to improve performance. (You want to avoid mentioning .NET, at least at first, as this group is very VBA-centric.) If they answer that there is usually no problem, then you can be fairly sure that the behavior has to do with how the PIAs are interfacing with the Excel type library.
|
|
|
|
|
|
|