c# - Create an Excel PivotTable from a DataTable -

i have webservice hosting large mass of pricing data, , returns data relevant prescribed query parameters. data comes datatable object (in c#) - object type doesn't matter fact data goes directly memory , not on spreadsheet in host excel object.

now, want create pivottable off of data.

i've been looking high , low on web, , can't see explaining how this. impossible? seems foolish suggest vsto supported way of consuming webservice data going forward, make pivottables off of data impossible.

the solutions have kludges, , want make sure there isn't graceful solution before 1 of these ugly things:

1. dump datatable excel sheet , point pivottable excel range. far ideal because i'm either doing rowwise deletion on entire dataset (slow heck) or peaking @ 2x memory consumption
2. dump datatable filesystem , point pivottable flatfile. worse @ least doesn't have memory drawback.

are these ways operation? there has more graceful.

datatable: http://msdn.microsoft.com/en-us/library/system.data.datatable.aspx

pivotcache: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pivottable.pivotcache(v=office.11).aspx

excel has able see , access data make pivottable it. have make sure data someplace pivottable loader can read. further, excel com-based , can neither see nor process .net objects.

it's pretty simple.

your choices are:

1. load data excel range

2. save data file

3. store data database (access, sql server, etc.)

4. store data in data warehouse (ssas, offline cube, etc.)

that's it. other remotely possible way implement com interfaces necessary present ole db or odbc data source, 1 heck of lot of work.