Exporting the PivotTable to Excel

You can export the PivotTable as a FileName.htm file, together with supporting .xml, .htm, and .css files, to a pseudo-PivotTable in an Excel workbook. This isn't a very exciting feature, because the process exports the data as a static XML rowset (Cachedata.xml in the ...\FileName_files folder). The temporary workbook is read-only and doesn't update as the underlying data from the query changes.

Click the Export to Microsoft Excel button to generate the PivotTable workbook. Figure 12.21 shows the default view of the PivotTable exported to Sheet1 of a workbook with the Excel PivotTable toolbar and PivotTable Field List displayed. Excel's PivotTable toolbar is similar to Access's. Sheet 2 contains data exported by the source query of the PivotTable, which Excel translates from the Cachedata.xml file.

Figure 12.21. This Excel workbook was created by exporting the sample PivotTable from the earlier "Increasing the Level of Detail for Drill-Down" section. The PivotTable can't display detail data, so double-clicking a value opens a new worksheet to display the detail behind the entry.

Clicking Excel's Chart Wizard button automatically generates a PivotChart from the PivotTable. To create a readable chart, select the Name button and click the Hide Detail button to eliminate the Country detail data before you click the Chart Wizard button. Otherwise, the chart is impossible to decipher. Figure 12.22 shows the PivotChart created from the collapsed PivotTable.

Figure 12.22. The Excel Chart Wizard displays in another worksheet a PivotChart based on each row and column displayed in the PivotTable.

Note

The files required to create the Excel workbook described in this section are included in the \Seua11\Chaptr12 folder of the accompanying CD-ROM. Double-click the 1997OrdersByEmployee&CountryPT.htm file to open a static PivotTable in IE 5+. Opening the PivotTableName.htm file of a PivotTable saved as a Web page adds an Edit with Microsoft Excel button to IE's toolbar. The drop-down list also lets you select Edit with Notepad, which displays the HTML code, or Edit with Microsoft Word, which generates a table.