471,338 Members | 952 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,338 software developers and data experts.

C#-Excel: Cannot set PivotCache Recordset with adUseServer


I am trying to create pivot tables using data from CSV files. Here is an example:

Expand|Select|Wrap|Line Numbers
  1. String conStr = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\\";
  2. String query = "SELECT * FROM content0.csv";
  3. Recordset content = new Recordset();
  4. Connection cn = new Connection();
  5. cn.Open(conStr, "", "", 0);
  6. content.Open(query, cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockOptimistic , 0);
  8. Application xlApp = new Application();
  9. Workbook xlWb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
  10. xlApp.Visible = true;
  11. Worksheet xlWs = (Worksheet) xlWb.Sheets.get_Item(1);
  13. PivotCaches xlPivCaches = xlApp.ActiveWorkbook.PivotCaches();
  14. PivotCache xlPc = xlPivCaches.Add(XlPivotTableSourceType.xlExternal, Missing.Value);
  15. xlPc.Recordset = content;
  16. PivotTable xlPt = xlPc.CreatePivotTable(xlWs.Cells[1, 1], "test", Missing.Value, Missing.Value);
  18. content.Close();
  19. cn.Close();
Running this code gives the following exeption:

Exception from HRESULT: 0x800A03EC
at System.RuntimeType.ForwardCallToInvokeMember(Strin g memberName, BindingFla
gs flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
at Microsoft.Office.Interop.Excel.PivotCache.set_Reco rdset(Object )
at Project1.Sandbox.Main() in C:\Documents and Settings\vvishakh\My Documents
\Visual Studio 2005\Projects\Project1\Project1\Sandbox.cs:line 43

The above is caused by the line
Expand|Select|Wrap|Line Numbers
  1. xlPc.Recordset = content;
If I add the line below right before opening the Recordset, the code works and a pivot table shows up:

Expand|Select|Wrap|Line Numbers
  1. cn.CursorLocation = CursorLocationEnum.adUseClient;
However, I want to use asUseServer since some of the files I read in are as large as 100 MB and can contain more than half a million rows. adUseClient uses up all my memory, I get an OutOfMemoryException and the pivot table doesn't load.

I have been grappling with the problem over the past few days and would be very grateful if someone could tell me how to get Excel pivot caches to accept Recordsets using adUseServer.
May 16 '07 #1
1 4811
I've just hit the same problem :)

The only workaround I can think of for the moment is to stay with adUseServer/adOpenForwardOnly and load the data initially into a normal SHEET (allowing up to 1 million rows in XL12, the same limit as an xlExternal pivotcache).

You can then build your PIVOT table on this table data, which works very well with these cursor settings, but initially leads to "double-storing" of the data.

It appears you can then DELETE the data sheet afterwards and SAVE the file, but without refreshing the Pivot. Result looks similar to the xlExternal method and appears to preserve all the pivot table functionaility except REFRESH (which personally I don't plan to use, as we are doing single-use/canned reports).

But I too would appreciate a better solution...

Jul 16 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Wayne Wengert | last post: by
4 posts views Thread by Thomas Scheiderich | last post: by
2 posts views Thread by ano1optimist | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.