I'm building an ASP.NET application that needs to generate an Excel
spreadsheet containing a pivot table. The user will define the fields
that appear in the pivot table as well as the search criteria that will
generate the source data for the pivot table. The user will need to be
able to view the spreadsheet in a disconnected fashion, i.e. the
spreadsheet may not connect to a web service, Analysis Services box,
database server, etc. for the data. All of the source data for the
pivot table must all be contained within the spreadsheet. I've looked
at numerous approaches, and none of them seems to be acceptable:

1. Use the Office interop libraries on the server to build the
spreadsheet. Unacceptable for scalability reasons related to
threading, and because the Office libraries could show a modal dialog
at any point on the server, bringing the application to a screeching
halt.

2. Build XML spreadsheets from scratch on the server without using
anything other than the .NET System.XML framework. This is the
approach I've been trying for several weeks. However, I'm having
difficulty building the XML for a valid pivot table that Excel can
understand. This is my own problem I suppose, in understanding the
underlying XML SS schema and object model, but it's prevented me from
shipping.

3. Build an XML spreadsheet containing the data, but using a VB macro
on the client to generate the pivot table. This would be ideal, because
I could use the Excel API directly in my VB code to ensure I have a
valid pivot table. I was pursuing this today until I discovered Excel
won't save macros along with an XML spreadsheet.

4. Build a standard .XLS spreadsheet which will use a macro to connect
to the server the first time it's opened, pull down the data from an
..aspx page, then delete the macro. This doesn't exactly meet my
requirements, but it may be what I have to do to get it done.

Has anybody had to solve a similar problem to this before? It seems a
little unusual in that the client machine is not allowed a direct
connection to the data source. This is what is causing the
difficulties.

Paul Schofield
paul.schofield<attrue.com

I just remembered the other option I was pursuing, which was to use the
Office Web Components to generate the XML definition for the pivot
table, which would then be embedded in an XML Spreadsheet and streamed
to the client. The problem with this is that OWC uses the ancient ADO
recordset as its datasource, and converting a DataSet to a recordset
was a major nightmare.

Is Microsoft going to update OWC for the now mature .NET platform at
any point?

Paul