Options for PivotTables in Excel


I need to design a small project for generating excel reports in .NET, which will be sent to users to use.

The excel reports will contain PivotTables.

I don't have much experience with them, but I can think of three implementation alternatives:

  1. Set a query for it, populate it, send it disconnected. This way the user will be able to group values and play a little, but he will not be able to refresh the data.
  2. Generate a small access database and send it along with the excel file, connect to it.
  3. Copy the data to the excel (perhaps in some other sheet) and connect to the data there. This will make the excel file very large I think.

What would be the best alternative in regards to performance vs usability? Is there another alternative I don't know about?


Since it's a small project, you can rely on Excel for data storage from the application. It'll be easier to develop and test, and simpler to maintain.


Maybe... the problem with that is that there are limitations as to how much data a single sheet can contain... I'm thinking option 2, unless someone tells me it is not a good idea in regards to performance

By : juan

Option 3 sounds the simplest and I would have thought Excel is as if not more efficient than Access for storing the data. The trouble with two files is getting the links between them to work even in a different location.

This video can help you solving your question :)
By: admin