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;
5 using System.IO;
6 using OfficeOpenXml;
7 using Microsoft.SharePoint;
9 namespace ConsoleApplication1
11 class Program
13 static void Main(string args)
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)
20 fi = new FileInfo(@"c:\files\test.xlsx");
23 ExcelPackage excel = new ExcelPackage(fi);
24 ExcelWorksheet ws = excel.Workbook.Worksheets.Add("Exported SP items");
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/))
28 using (SPWeb web = site.OpenWeb())
30 SPList list = web.Lists["SampleList"];
31 int rowIterator = 0;
33 foreach (SPListItem item in list.Items)
36 int columnIterator = 1;
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)
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;
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/);
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.
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!