Excel Export Plus Introduction

Excel Export Plus is a SharePoint solution that demonstrates a usage of the OfficeWriter API in SharePoint. This solution adds a new ribbon button to SharePoint lists, allowing you to export the list data to a pre-formatted Excel template that can be designed in Excel or automatically generated by Excel Export Plus.

 

Whereas using the out-of-the-box SharePoint export-to-Excel feature allows only for raw, unformatted data dumps, using OfficeWriter and Excel Export Plus supports:

1. Custom Excel templates
2. Charts, formulas, pivot tables, or any other Excel feature
3. Exporting to XLS and XLSX files
4. With developer customization, you can exercise runtime control over just about every aspect of the workbook

After you install and configure Excel Export Plus, you will find a button in the ribbon of your list that exports the list data into a pre-defined template in Excel. The "List to Excel" ribbon button below is an example of Excel Export Plus. You can customize the button text based on your requirements.

How it Works

Excel Export Plus uses the OfficeWriter API under SharePoint's hood to merge SharePoint list item data into a Excel workbook. For example, say you have a list like this one, with CompanyName, Address, City, State, Zip, ContactName, and PhoneNumber columns:

You could design a template like this in Excel. Put anything you want into the Excel workbook document, and type specially formatted data markers that correspond to the names of the columns in the list. These data markers specify where the list data will be merged into the template.

When you choose to export the list data by clicking the ribbon button, Excel Export Plus pulls the values from the list and then uses OfficeWriter to open the template file, replace the data markers with actual data, and stream the populated workbook to the browser:

Fully Customizable by Developers

You can install OfficeWriter and Excel Export Plus and use it as-is, customize the code to fit your own needs, or simply use it as an educational tool to better understand the capabilities of OfficeWriter.

Installation and Configuration

Installation

The first step is to install OfficeWriter and Excel Export Plus on the SharePoint web farm.

Prerequisites and System Requirements

The following is a list of prerequisites for this solution installer.

To run the installer:

1. OfficeWriter Enterprise key - An Enterprise key is required to use an OfficeWriter SharePoint solution. If you do not have an enterprise license key, you can obtain a free 30-day evaluation by visiting http://www.officewriter.com or by contacting SoftArtisans at contact@softartisans.com.
2. SharePoint 2010 - This solution is supported only on SharePoint 2010
3. Local machine administrative privileges - The installer must be run with local administrative privileges
4. Farm administrator privileges - The installer requires farm administrator privileges to run

Installation

All installation steps are assumed to occur on the machine where the SharePoint web app resides.

1. Install OfficeWriter - If you haven't already, install the OfficeWriter product. If you don't have the OfficeWriter product already installed, you can download an evaluation copy by visiting http://www.officewriter.com or by contacting SoftArtisans at contact@softartisans.com.
2. Install the Solution on the farm - Run the Excel Export Plus installer. This will install the solution into the SharePoint farm.
3. Deploy the solution - Once the solution is on the SharePoint farm, deploy it by following these steps:
   1. Navigate to "System Settings" > "Farm Management" > "Manage Farm Solutions"
   2. Click "Excel Export Plus.wsp"
   3. Click "Deploy Solution"
   4. Specify a deployment time
   5. Click "OK"

Configuration

Feature Activation

Once the Installation steps are complete, the solution will appear as a SharePoint Feature that can be activated and managed by a site administrator. Follow these steps to configure Excel Export Plus:

#Browse to the SharePoint site in which you want to use this solution.

1. Under the "Site Actions" menu, select "Site Settings".
2. Select "Manage site features" in the "Site Actions" section.
3. Locate the "OfficeWriter Solution Gallery - Word Export Plus" item, and click "Activate". When it has been activated, it will look like this:

Configuration

In this step, you will enable Excel Export Plus on a specific list, select a template file that it will export the list's contents to, and define the text for the context menu item.

1. From "Site Settings", click "OfficeWriter Solution: Excel Export Plus" in the "Look and Feel" section. This will bring up a configuration screen.
2. In the "SharePoint List" section, click "Browse..." and choose a list in your site on which you want to activate Excel Export Plus.
3. In the "Excel Template" section, select "Create a new template file" and then choose the columns from the list that you want to appear in the export. Excel Export Plus will automatically create an Excel template for you with the data markers that you choose (you can go back later and customize the template in Excel). You can also select a previously created template from any library in the SharePoint site.
4. If creating a new template, specify a document library in which you'd like it to be stored.
5. Type a name for the new template file
6. Finally, in the "Action Name" section specify the text you want to appear on ribbon button, and provide an optional description.

Here is an example the configuration screen for the sample list described above:

Doing More with OfficeWriter

Now that you have your list associated with a template file in the document library, you can edit the template and add more formatting, content, or merge fields that correspond to column names in the list.

For more information about Excel template files, see here:
http://wiki.softartisans.com/display/EW8/How+to+Use+Templates