| |
| Exporting a SharePoint
List to Excel with SoftArtisans OfficeWriter |
 |
| Goal: |
| SharePoint “Features” make
it easy to extend your site’s functionality. This walkthrough
will demonstrate how a custom feature can be used to bridge SharePoint
and Office. We will create a Feature that uses SoftArtisans OfficeWriter
to export a SharePoint List to an Excel spreadsheet through a new
item in the “Actions” menu.
We will use a solution package so that our Feature is easily deployable.
Microsoft Windows SharePoint Services 3.0 introduces a deployment
mechanism named "solution packages." A solution package
is a CAB file with a .wsp file-name extension that contains all
the files that must be deployed on the front-end Web server and
a set of XML-based installation instructions.
|
| |
| |
 |
Setting
up the WSP project |
| |
| 1. |
Launch VS.NET and create a new Class Library
project. |
| |
|
| 2. |
Create a set of folders and subfolders under the project’s
root that correspond to folder structure where the feature will be
deployed on the SharePoint Server. This is typically the “C:\Program
Files\Common Files\Microsoft Shared\web server extensions\12”
folder. |
| |
| You will need to create the following
folders: |
 |
Solution |
|
Template |
|
Template/Features |
|
Template/Features/OfficeWriterExcel_ExportList |
|
Template/Images |
|
Template/http://lib.store.yahoo.net/lib/softartisans/Tpg |
|
Layouts |
|
Layouts/OfficeWriterExcel_ExportList |
|
| |
| |
| NOTE:
You will need to create a new GUID for the “Id”
nodes in the “Feature.xml”, “Elements.xml”
and the “Manifest.xml” files. GUIDs can be generated with
guidgen.exe or at http://www.guidgen.com |
| |
|
|
3.
Add a new “Feature.xml” in the project’s TEMPLATE/FEATURES/OfficeWriterExcel_ExportList
folder.
The structure of the xml is as follows: |
 |
| |
|
|
4.
Add a new “Elements.xml” to the same folder. The most
important node in this file is the “UrlAction”. This is
the page that SharePoint will hit when the end user clicks on our
menu item. We need to send the UrlAction page the id of the List that
we are exporting. Note the query string with the “ListId”
variable at the end of the url. Windows SharePoint Services makes
this token available in order to identify the List that initiated
the event. For more information see How
to:Add Actions to the User Interface.
The structure of this xml is as follows: |
 |
| |
|
|
| 5.
If you want to associate an image with the menu item, place it in
the TEMPLATE/http://lib.store.yahoo.net/lib/softartisans/TPG folder. |
 |
For this project, were using the file “spreadsheet.gif”. |
|
| |
|
|
6. Create
a Solution Manifest file, “Manifest.xml”, that gets added
in the SOLUTION folder.
This file specifies the structure of the solution(WSP) file. |
 |
| |
|
|
| 7. Next,
create the DDF file in the SOLUTION directory. This file, “OfficeWriterExcel_ExportList.ddf”,
specifies the order of files within the WSP file. |
 |
| |
|
|
| Be sure that the
DiskDirectory1 value is ”Solution” in order to ensure
that the WSP will be created in the SOLUTION folder of the project. |
| |
|
|
| 8.
Remove the default “class1.cs” file from the project as
it will not be used here. |
| |
|
|
| 9.
Implement the ActionUrl page. The “UrlAction” page is
where the good stuff happens. It is the target of the “UrlAction”
from the Elements.xml file. Be sure this file is in the directory
TEMPLATE/LAYOUTS/ OfficeWriterExcel_ExportList. |
| |
|
|
| |
|
|
| The
Code: |
|
|
| 1. |
Since a user will not
be interacting with this page directly, we do not need to handle
the type ASP.NET events. We simply write the C# to create an
Excel spreadsheet from the SharePoint List. |
| 2. |
We will be using the SharePoint
API to get the contents of the list, so be sure to reference
the SharePoint assembly:
<%@ Import Namespace="Microsoft.SharePoint"
%> |
| 3. |
We will be creating the Excel workbook
using OfficeWriter, so we need to reference the OfficeWriter
assembly:
<%@ Import Namespace="SoftArtisans.OfficeWriter.ExcelWriter"
%> |
| 4. |
We’re writing the contents
of a SharePoint list to the Excel workbook, so first we parse
the List id from the query string we defined in the “UrlAction”
node of Elements.xml:
string listId = Page.Request.QueryString["list"]; |
| 5. |
Next we get the SPList reference
that represents the List we want to export
SPWeb site = SPContext.Current.Web;
SPList list = site.Lists[new Guid(listId)]; |
| 6. |
We then create an OfficeWriter Excel
Workbook reference by calling ExcelApplication.Create(). This
method creates a new Excel workbook.
ExcelApplication xlw = new ExcelApplication();
Workbook wb = xlw.Create(); |
| 7. |
Then we get a reference to the first
worksheet in the workbook
Worksheet ws = wb.Worksheets[0];
ws.Name = "Home Loans"; |
| 8. |
We then iterate over the items in
the SPList and create an new CharacterRun of text for each list
item
SPListItemCollection collListItems = list.Items;
foreach (SPListItem listItem in collListItems)
{
ws.Cells[r,0].Value = listItem["Borrower Name"];
ws.Cells[r,1].Value = listItem["Loan Amount"];
ws.Cells[r,2].Value = Convert.ToDouble(listItem["Annual
Interest Rate"]);
ws.Cells[r,3].Value = Convert.ToInt32(listItem["Loan Period
(years)"]);
ws.Cells[r,4].Value = Convert.ToInt32(listItem["Yearly
Payments"]);
ws.Cells[r,5].Value = listItem["Start Date"];
ws.Cells[r,6].Value = listItem["Optional Extra Payments"];
r+=1;
} |
| 9. |
Finally, we send the new Excel wookbook
to the client using the HttpResponse object of the current Page
wap.Save(wb, Page.Response, "OfficeWriter_SPListExport.doc",
false); |
| |
|
| |
The actual code to create a richly
formatted Excel workbook is a little more complex. However,
the details of the OfficeWriter API are beyond the scope of
this walkthrough. Please see the comments in the sample code
and the OfficeWriter documentation for a more detailed explaination. |
|
| |
|
|
| |
|
|
| Requirements:
|
|
|
It is important
to have an active installation of the OfficeWriter “Enterprise
Edition” (EE) evaluation or licensed version on the server where
this code executes. OfficeWriter can be secured by visiting the “Evaluation
Software” page available on the SoftArtisans website at http://support.softartisans.com/Login.aspx?SiteReturnUrl=eval.
Be sure to request the EE version license key.
The OfficeWriter assembly: “SAWW3NET.dll” must be installed
in the GAC on this server since it’s referenced with the import
statement at the top of the .aspx page, along with the SharePoint
assembly.
Modify the “web.config” for the targeted SharePoint site
collection to include the OfficeWriter assembly, in addition to the
already present SharePoint assembly reference.
<assemblies>
<add assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral,
PublicKeyToken=71e9bce111e9429c" />
<add assembly="SAWW3NET,
Version=3.8.1.483, Culture=neutral, PublicKeyToken=f593502af6ee46ae"
/>
</assemblies>
Make certain that the version property matches the one installed in
the GAC. |
| |
|
|
| |
|
|
| Building
and Deploying the WSP to the SharePoint Server: |
| In order to simplify
the building and deployment process, consider installing the “SPDevMod”
project on your development workstation. It was created by SharePoint
MVP, Scot Hillier and is available for downloading at http://www.codeplex.com/SPDevMod.
A full set of instructions is included. Scot also offers a comprehensive
blog entry entitled: “What's Your Process for Developing SharePoint
Features and Solutions?” at http://scothillier.spaces.live.com/blog/cns!8F5DEA8AEA9E6FBB!197.entry
which provides a step-by-step and insights on the entire build/deploy/modify/redeploy
process. Follow the steps outlined there to build and deploy using
the features added with “SPDevMod”. |
| |
|
|
| |
|
|
| Testing
the Feature: |
|
|
| 1.
Go to any List in your SharePoint installation. |
| |
| 2.
Click on the “Actions” menu. You should see the new menu
item, “Export this list to Excel with OfficeWriter”., |
 |
| |
|
|
3. When
you select the new menu item, the code in the “UrlAction”
page will run and the Excel workbook will be sent to the browser.
The following dialog box should appear: |
 |
| |
| 4. Click
“Open” to open the workbook in Office. |
 |
| |
