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

Published by

Bjørn Furuknap

I previously did SharePoint. These days, I try new things to see where I can find the passion. If you have great ideas, cool projects, or is in general an awesome person, get in touch and we might find out together.

7 thoughts on “Exporting SharePoint lists to Excel, including attachments”

  1. Non-formatted source code:

    using System;
    using System.Collections.Generic;
    using System.Text;

    using System.IO;
    using OfficeOpenXml;
    using Microsoft.SharePoint;

    namespace ConsoleApplication1
    {
    class Program
    {
    static void Main(string[] args)
    {
    // Open or create xlsx-file.
    FileInfo fi = new FileInfo(@”c:\Files\test.xlsx”);
    if (fi.Exists)
    {
    fi.Delete();
    fi = new FileInfo(@”c:\Files\test.xlsx”);
    }

    ExcelPackage excel = new ExcelPackage(fi);
    ExcelWorksheet ws = excel.Workbook.Worksheets.Add(“Exported SP items”);

    using (SPSite site = new SPSite(“http://lab-s02:12000/”))
    {
    using (SPWeb web = site.OpenWeb())
    {
    SPList list = web.Lists[“SampleList”];
    int rowIterator = 0;

    foreach (SPListItem item in list.Items)
    {
    rowIterator++;
    int columnIterator = 1;
    ws.InsertRow(rowIterator);

    ws.Cell(rowIterator, columnIterator++).Value = item[“Title”].ToString();
    ws.Cell(rowIterator, columnIterator++).Value = item[“Second field”].ToString();
    ws.Cell(rowIterator, columnIterator++).Value = item[“Some Date”].ToString();

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

    // Add to Excel column
    ws.Cell(rowIterator, columnIterator).Value = file.Name;
    Uri attachmentUri = new Uri(“file://” + fileName, UriKind.Absolute);
    ws.Cell(rowIterator, columnIterator).Hyperlink = attachmentUri;
    columnIterator++;
    }

    }
    }
    }

    excel.Workbook.Properties.HyperlinkBase = new Uri(“http://furuknap.blogspot.com/”);
    excel.Save();
    }
    }
    }

  2. if i read this correctly, you are just trying to get the file url into the Excel dump. do you have a SQL query tool that can export (even copy / paste) into Excel? if so, this is probably quicker / easier. obviously, you'll have to use the sites / webs / lists tables to get your guids. this was written to extract from SPS 2003 for subsequent import into 2007, so if you're trying to extract from 2007 you'll have to re-examine the structures.

    select itm.tp_id, itm.nvarchar1 as Title, dateadd(hh, -6, itm.tp_created) as Created, itm.tp_Author, usr.tp_title, docs.leafname,
    'http://domain.com/' + docs.dirname + '/' + docs.leafname as FileURL
    from userdata itm, docs, userinfo usr
    where itm.tp_listid = 'C7DBE99A-9F60-4907-AE31-DADF41C1797A'
    and docs.siteid = 'D4CE34F8-B610-4B56-B55D-D32DBCEC9F48'
    and docs.listid = 'C7DBE99A-9F60-4907-AE31-DADF41C1797A'
    and docs.dirname = 'sites/somesite/Lists/somelist/Attachments/' + cast(itm.tp_id as varchar)
    and itm.tp_author = usr.tp_id
    and usr.tp_siteid = 'D4CE34F8-B610-4B56-B55D-D32DBCEC9F48'

  3. Hello,

    I have almost none experience with programming. I tried to use your code in C # from Visual Studio 10. Initially I got error for 2 lines:

    using OfficeOpenXml;
    using Microsoft.SharePoint;

    Some how I managed to add reference to SharePoint dll and downloaded EPPlus from codeplex to add reference to EPPlus.dll. It did remove error messages from 2 lines above.

    Now I am getting error message for these 2 lines below:

    ws.InsertRow(rowIterator);
    ws.Cell(rowIterator, columnIterator++).Value = item[“Created”].ToString();

    Error for ws.InsertRow:-
    No Overload For Method ‘InsertRow’ takes 1 arguement

    Error for ws.cell:-
    ‘OfficeOpenXML.ExcelWorkSheet’ does not contain a definition for “cell” and no extension method “Cell” accepting a first argument of type ‘OfficeOpenXML.ExcelWorkSheet’ could be found

    Please help!

  4. Hmm..if you have the folder/site structure cant you just first export it to excel and then in excel put together the URL needed to access the file from the information in the excel..
    http://mycompany.local/sites/my site/my subsite/Shared Documents/folder/subfolder/filename.doc

Leave a Reply

Your email address will not be published.