I have a large amount of data that is possibly more than a million rows. I want to be able to offer users the ability to download an Excel spreadsheet with a Pivot Table over this data, disconnected from any database.
Is it possible to have the data source that the pivot table works over embedded in a spreadsheet?
The answer to your direct question is YES, data can be embedded in a PivotTable. Yet since you specify "more than a million rows" and "disconnected from any database", the answer to the implied application is NO. You aren't going to get more than one million rows in an Excel spreadsheet. And I would venture to say that one million rows don't belong in a spreadsheet.
I think one of the purposes of a PivotTable is to allow you to summarize data from an external source. If you can (or think you can) combine data with a PivotTable and download the entire mess, why not do the summary yourself, and download just the summary data? it would be interesting to hear more about the actual application.
If you create a pivot table from an external data source, then email it to someone, they can see and interact with your pivot table. They can't refresh unless they have access to the original data. But that seems to be almost what you're asking.
Excel creates a PivotCache that is embedded in the workbook. That's why the aforementioned emailed spreadsheet can be seen by people without the source data. The PivotCache doesn't necessarily hold the source data in its entirety and it's not directly accessible (as far as I know).
I'm not sure if that answers your question. It seems like what you're after is what PivotTables do normally.