Exporting SharePoint lists to Excel, including attachments

Another question from a forum:

I am a newbie to sharepoint and have been tasked by my boss to create an excel or access 2003 file of all the items in a list in sharepoint 2003.

There are about 6000 items in the list with close to 2000 attachments. Using the built in “export to excel” function results in an excel file with no attachments. It is going to take me months to export the list and manually link up the attachments (after i figure how to download the attachments in the first place!).
I have done my duty and prowled the net to search for solutions, but mostly are for exporting to another sharepoint server or so, nothing much written for exporting to either access or excel 2003.

Well, as noted, SharePoint does not really have a good way of ‘offlining’ a list like this. If you use the Export to spreadsheet function, you will be stuck with the attachments field show as either ‘True’ or ‘False’ depending on wether or not you have or do not have attachments on an item.

After a few messages back and forth to clarify the needs (read the forum if you need details) I proposed to write a simple solution to this using code. I’ll explain here.

A few quirks, though… First of all, I am not targeting SP 2003. This code runs on both WSS2.0-based and WSS 3.0-based sites.

Second I do not target Excel 2003. Creating Office 2003 solutions is a pain, or at least more pain than is needed. this is Office 2007. That was the bad news. The good news is that you can easily get an Excel 2007-format reader for Excel 2003, and since the contents are simple you should be able to copy the content to a plain 2003 document. That will take far less time than trying to write a solution for 2003. A lot less time.

So, with those quirks out of the way, here goes…

First, get a copy of ExcelPackage from CodePlex (http://www.codeplex.com/ExcelPackage). This free library makes working with Excel 2007 files from code very easy. I assume you have the rest of the SharePoint development environment set up.

Second, start your Visual Studio of choice, create a new Console Application and include references to both Microsoft.SharePoint.dll and ExcelPackage.dll. Add the using statements as required, and make sure you include System.IO as you will need to send a FileInfo object to ExcelPackage. It should look something along the lines of this:

    1 using System;
    2 using System.Collections.Generic;
    3 using System.Text;
    4 
    5 using System.IO;
    6 using OfficeOpenXml;
    7 using Microsoft.SharePoint;
    8 
    9 namespace ConsoleApplication1
   10 {
   11 class Program
   12     {
   13 static void Main(string[] args)
   14         {
 

BTW, code copy is courtecy of CopySourceAsHTML, available from http://www.jtleigh.com/people/colin/software/CopySourceAsHtml/

Now, the next step is to add the code to create the Excel file. I’ll just use the spirit of the ExcelPackage samples available from the CodePlex site above:

   15 // Open or create xlsx-file.
   16 FileInfo fi = new FileInfo(@"c:\files\test.xlsx");
   17 if (fi.Exists)
   18             {
   19                 fi.Delete();
   20                 fi = new FileInfo(@"c:\files\test.xlsx");
   21             }
   22 
   23 ExcelPackage excel = new ExcelPackage(fi);
   24 ExcelWorksheet ws = excel.Workbook.Worksheets.Add("Exported SP items");
   25 

From this point on we are able to do ws.Cells(x,y).Value=something in order to manipulate the spreadsheet. Thus, our next, logical step would be to iterate through the list to add the list contents to the spreadsheet:

   26 using (SPSite site = new SPSite(http://lab-s02:12000/))
   27             {
   28 using (SPWeb web = site.OpenWeb())
   29                 {
   30 SPList list = web.Lists["SampleList"];
   31 int rowIterator = 0;
   32 
   33 foreach (SPListItem item in list.Items)
   34                     {
   35                         rowIterator++;
   36 int columnIterator = 1;
   37 
   38                         ws.Cell(rowIterator, columnIterator++).Value = item["Title"].ToString();
   39                         ws.Cell(rowIterator, columnIterator++).Value = item["Second field"].ToString();
   40                         ws.Cell(rowIterator, columnIterator++).Value = item["Some Date"].ToString();
 

Of course, which fields you want included is up to you.

If you just finished the using-brackets here you would basically have a simple Export to Excel 2007 function, or, more correctly, Office Open XML SpreadsheetML, or something along those lines.

Now, attachments are a bit different. They are basically files stored in virtual folders in the SP Database. To access them you need to find the correct folder and iterate through the SPFiles objects that are there. Luckily, Karthick’s Blog has helped us out by posting most of what we need:

 
   43 // Get the attachments
   44 SPFolder folder = web.Folders["Lists"].SubFolders["SampleList"].SubFolders["Attachments"].SubFolders[item.ID.ToString()];
   45 foreach (SPFile file in folder.Files)
   46                         {
   47 // Download to directory
   48 string fileName = "c:\\Files\\" + file.Name;
   49 byte[] binFile = file.OpenBinary();
   50                             System.IO.FileStream fstream = System.IO.File.Create(fileName);
   51                             fstream.Write(binFile, 0, binFile.Length);
 

Finally, or allmost, add the links to the files to your rows. I’ve opted to add multiple files to consecutive columns, but you may do as you please.

   53 // Add to Excel column
   54                             ws.Cell(rowIterator, columnIterator).Value = file.Name;
   55 Uri attachmentUri = new Uri("file://" + fileName, UriKind.Absolute);
   56                             ws.Cell(rowIterator, columnIterator).Hyperlink = attachmentUri;
   57                             columnIterator++;
   58                         }
   59 
   60                     }
   61                 }
   62             }

Last order of business is to close up. Make sure that you add the HyperlinkBase value as noted below, or your links will not work. You may, of course, substitute the actual Uri with anything you like.

   64             excel.Workbook.Properties.HyperlinkBase = new Uri(http://furuknap.blogspot.com/);
   65             excel.Save();
   66         }
   67     }
   68 }

There! Compile, run and woohoo you have an Export to excel on steroids.

I’ve added the non-formatted source to the comments to this post, in case you want to get a head start and not read the entire post.

Good luck!

.b

Found this article valuable? Want to show your appreciation? Here are some options:

a) Click on the banners anywhere on the site to visit my blog's sponsors. They are all hand-picked and are selected based on providing great products and services to the SharePoint community.

b) Donate Bitcoins! I love Bitcoins, and you can donate if you'd like by clicking the button below.

c) Spread the word! Below, you should find links to sharing this article on your favorite social media sites. I'm an attention junkie, so sharing is caring in my book!

Pin It

Question from a forum post

Question from a forum post at http://www.sharepointu.com/forums/p/4055/10709.aspx:

When creating a site template, is there a way to name a set of lists and/or document libraries based on the name of the site that is chosen when creating a new site based on a custom site template? For example, creating a new site called “Peter Pan” and then having the list and doc library (in the template) named “Peter Pan List” and “Peter Pan Doc Library” automatically after creation of the site.

/End question

Answer:
You can do this relativly easy in a featureactivated event handler, but it does require a bit of coding and provisioning the lists or sites as features or site definitions. If programming is ok with you and you are able to work with the SharePoint OM, you would go about this in one of two ways.

First you can provision the site as a site definition and have a webfeature or sitefeature that has an event handler to rename the lists. Doing this the feature way can in turn be done in one of two ways. First, you can have a ‘rename lists’ feature that renames lists when the feature is activated through the site definition. One downside to this is that any future lists added will not be renamed. One upside, amongst others, is that you can rename lists regardless of the lists themselves, that is, any list, regardless of how that list is provisioned through the site definition, can be renamed. The other way to do this features based is to add all lists as features and have the event handlers attached to those features. The downside is that you wont be using the ‘Create’ option of the site actions or view all site content page. The upside is that you can do this also after the site has been provisioned so that all new lists will be named accordingly.

The other option for renaming lists is to use a little known site provisioning object model. This technique is used by the collaboration portal in MOSS, but is available to WSS if you are willing to walk the coding lines. Again, this only works when you are provisioning sites, not for creating lists after the fact. To do this you would use the ProvisionData, ProvisionAssembly and ProvisionClass as attributes to your site definition in webtemp*.xml. If you want to go down this route, and it may be usefull anyways if only for the exercise, Anders Jacobsen has written a short article that gets you started:
http://www.pings.dk/blog/archive/2007/10/18/create-a-web-provisioner-to-enable-quote-post-site-creation-actions-quote.aspx

The huge benefit of the latter option, using the SPWebProvisioningProvider, is the flexibility you get from having complete programmatic access to site provisioning. You can add users, create on-the-fly lists, build workflows and connect them to content types or lists based on any criteria (how about appending I HATE MONDAYS to all items on a site created on Monday mornings?) or basically anything else you can do to a site. Best, or worst, part is, you dont even need a site definition outside the webtemp*.xml, you can create your entire site inside code. In fact, this is what the collaboration portal does, you will not find a SPSPORTAL site definition anywhere, but rather a reference to an assembly that does the entire provisioning through code.

Good luck!

.b

Pin It

CAML is the foundation of any good SharePoint developer

CAML is the foundation of any good SharePoint developer and should be taught in kindergarten. CAML is really not hard, nor are views, but it does represent a different way of thinking.

Lots of tools make it easy to both generate and manipulate CAML, for instance U2U CAML Query Builder (http://www.u2u.info/SharePoint/U2U%20Community%20Tools/Forms/AllItems.aspx) or even SharePoint Manager 2007 (http://www.codeplex.com/spm), the latter is especially good for getting view and list CAML. Getting intellisense in VS is not easy, however, but you do have tools such as John Holliday’s CAML.net which basically gives you a somewhat cumbersome, but incredibly powerful method of doing CAML the old OO way (http://www.codeplex.com/camldotnet).

EDIT: Oh, don’t forget to check out my article on getting intellisense for CAML, at least for many parts of it: http://furuknap.blogspot.com/2008/04/intellisense-in-onet.html

Pin It