Part 3: Designing the ExcelWriter template and creating the application page

This is part 3 of a 3-part walkthrough describing how to export a SharePoint list as an Excel spreadsheet using ExcelWriter. While SharePoint already has the ability to export a list as a basic Excel spreadsheet, with ExcelWriter you can create complex reports which utlize all of Excel's powerful capabilities such as rich formatting, formula, chart, pivot table, and so on. Additionally, the walkthrough also describes how to use the grouping and nesting feature of ExcelTemplate to create a report containing an arbitrary number of grouped data fields.

The walkthrough works with both SharePoint 2007 and 2010. Included is a SharePoint solution package that can be deployed right away, as well as a Visual Studio project that you can modify and build yourself.

Not a current ExcelWriter user? No problem: you can download a free evaluation and follow along!

Requirements The SharePoint integration and grouping features are only available in ExcelWriter EE version 7.5 and higher. You must install ExcelWriter EE before running the sample. SharePoint integration makes use of extension methods, which require .NET 3.5 and higher.

Step 1: Creating ExcelWriter template

Instead of exporting the list of orders as a plain Excel file, as done by the out-of-the-box Excel export functionality, we want to generate a full-featured Excel report using ExcelWriter. The report should group the orders by year, by state within each year, by city within state, then finally by customer within city. Each grouping level should sub-total the order amount. We will accomplish this goal using the grouping functionality of ExcelTemplate. We will also add a simple bar chart showing the order total by year.

Follow the documentation pages for creating a regular template or grouping template. Here, we use the below grouping template.

A grouped field is marked by the %%group and %%endgroup markers. Only one %%endgroup marker is required, even if there are more than one grouped field. However, for clarity, we include an %%endgroup for each grouped field. In the template, we group over order year, then state, then city, and finally customer. For aesthetic purposes, we want to display the grouped values for order year, state and city in a vertical orientation. To do so, we hide the corresponding data markers with the (hide) modifier. Then we show the hidden grouped values by adding a header row and using the %%value(field) marker.

To add an order amount sub-total for each group, we use Excel's SUBTOTAL formula. The formula should reference a range containing the data marker for the order amount, %%=ds.orderamount, which occupies cell I13 referenced in the formula. In the template, the range includes just a single cell. However, when populating the template with data, ExcelWriter automatically expands the range to encompass all the rows inserted for the group. Consequently, the SUBTOTAL formula will operate over the correct range of values for each group.

To generate data for the chart, we insert a second worksheet. To show just the grouped value for each order year, we hide the data marker row by right-clicking on the row header then selecting Hide (see figure). The data for the chart should span the area between and including the %%group and %%endgroup markers. To unhide a row in Excel 2007, type Control-G and enter a cell address then select Format > Hide & Unhide > Unhide Rows on the Home ribbon.

Step 2: Creating application page

The application page uses ExcelTemplate to export the items in the custom list as an Excel spreadsheet. Using the SharePoint integration extension methods, we can do so with just a few lines of code. Furthermore, the SharePoint-specific methods parallel existing methods, allowing ExcelWriter to behave in a consistent and expected manner.

The steps for exporting the SharePoint list using ExcelWriter are as follows:

1. Reference the ExcelWriter assembly, SoftArtisans.OfficeWriter.ExcelWriter.dll
2. Reference the ExcelWriter SharePoint integration assembly, SoftArtisans.OfficeWriter.ExcelWriter.SharePointIntegration.dll
3. Reference Microsoft.SharePoint.dll, which can be found in the ISAPI folder of the 12 Hive
4. Import the SoftArtisans.OfficeWriter.ExcelWriter and Microsoft.SharePoint namespaces
5. Instantiate ExcelTemplate:
6. Load the template directly from the document library:

   where docLib is the SPList for the template document library.

7. Set the data source for the template:

   The above BindData extension method takes an SPList as well as an SPView. The view allows us to refine the data returned from the list. Since we can bind multiple data sources to a template, we specify that the list data should populate data markers which reference the data source named "ds".

8. Call the Process method to allow ExcelWriter to populate the template:
9. Save the populated spreadsheet to another document library, overwriting an existing file with the same name if necessary:

   where outputLib is the SPList of the output document library. Because the custom action invokes the application page with a GET request, we must temporarily enable AllowUnsafeUpdates before saving the file. It's not necessary to enable AllowUnsafeUpdates when updating a SharePoint site for an application page invoked from a POST request or for a desktop application.

10. Instead of or in addition to saving the file to a document library, you can use other overloads of the Save method. For example, to stream a copy of the spreadsheet to the browser:

This concludes the walkthrough. Please leave a comment if you have any question or suggestion.