Tuesday, December 22, 2009

Programmatically creating Word documents in SharePoint with the OpenXML SDK

A requirement on a recent SharePoint workflow development project involved the programmatic creation of a formatted Word document. The Word document itself was simple: a title and a table. The table contains rows for each SPListItem in an SPListItemCollection object. I spent a little while looking at how to achieve this without the use of Word automation/Interop before deciding to examine how quickly I could achieve this using the OpenXML SDK and streaming the contents of the XML into an SPFile object. This blog post describes the chosen approach.

Caveats: This post focuses on concepts rather than posting hundreds of lines of code. The idea is that you can take away a good understanding of the tools available to quickly generate OpenXML compliant documents and write them back to SharePoint. This post assumes a good level of knowledge of the SharePoint API and refactoring complex, auto-generated code from productivity tools such that it's production ready. This post also does not focus on using a .dotx template which in hindsight, would have been more appropriate. We'll leave that as a task for the reader :)

Okay.. so lets get started!

You will need to download the OpenXML SDK 2.0 (OpenXMLSDKv2.msi) and OpenXML Productivity Tool (OpenXMLSDKTool.msi) from the following location:


Step 1. Setup your project

Once you have the SDK and productivity tools installed, create a new Console Application and add a reference to DocumentFormat.OpenXml.dll.


Step 2. Generate your base document

The approach I took to quickly generate a document was to create the skeleton layout of the document as shown below. In this instance, I wanted to generate a table with rows comprised of items from an SPListItemCollection.

Step 3. Generate your code stub

The OpenXML productivity tool now allows us to generate a C# stub of code, albeit quite verbose, that represents that document. Open the productivity tool from it's location on the file system (the actual file is OpenXmlSdkTool.exe) and click "Open File" and locate your previously created Word document. Now you need to click on "Reflect Code". The productivity tool will produce a code stub for you. Copy the contents of this file, obviously amending namespaces as necessary etc into your Visual Studio project.


Step 4. Refactor

What you are now able to do is create multiple instances of that document. This of course is no use, so we need to do some refactoring. The extent and nature of the refactoring process depends upon what you want to do. As such, I'm not going to post large amounts of application specific code, rather I'll focus on concepts.

In my case I needed to modify the constructor to take an SPListItemCollection which I then wanted it to use to generate a document and write that back to a document library. As such I modified the signature and behaviour of the CreatePackage() method such that it looked as follows:

public void CreatePackage(string fileName, SPListItemCollection Items,
string destinationOutputListName)
this.fileName = fileName;
this.items = Items;

SPFolder folder = web.GetFolder(destinationOutputListName);
using (MemoryStream memoryStream = new MemoryStream())
using (WordprocessingDocument package = WordprocessingDocument.Create(memoryStream, WordprocessingDocumentType.Document))

SPFile file = folder.Files.Add(fileName, memoryStream, true);

The next piece of refactoring I did was inside the auto-generated method called GenerateMainDocumentPart1Content(). I created two methods, one called GenerateTable() and another called GenerateTableRows(). By default, the productivity tool will create the Table structure for you, what I needed to do was add multiple instances of TableRow objects to the Table object for my list items.

If you create a basic Word document as I did, and search through the code you'll see exactly how the Table object is being created. Based on the initial structure I had, the auto-generated code stub would create one row for me (the headers) which is great. I simply needed to refactor this code out into it's own method and call it multiple times, with each row being populated from each SPListItem. The end result of this is of course a document that looks something like this:


from a base SPList which may look something like this:


Step 5. Package it up

Exactly what you do here depends also depends upon your requirements. In my case I wrapped up these assemblies to be called from an external workflow, this could easily be a web part or some other SharePoint piece of functionality. Either way, you could quite easily call it in the following way:

        MyWordDoc generatedWordDoc = new MyWordDoc();

SPListItemCollection items = web.Lists["Links"].Items;

generatedWordDoc.CreatePackage("MyWordDoc.docx", items,
"Shared Documents");

Until next time... happy SharePointing.

Wednesday, November 18, 2009

AllowInsecureTransport issue with SharePoint 2010 Public Beta Install

So, after the brouhaha regarding the public beta release of SharePoint 2010 it seems that installing the product is not straight forward when using Windows 7/Windows Server 2008 R2.

There's an issue with WCF in .NET 3.5.1. where SharePoint relies on an attribute that isn't present, namely AllowInsecureTransport. You can simply remove this from the client.config files but this will break other services. There is talk of other work arounds but at the minute, the official advice if you want to run SharePoint 2010 Public Beta on Windows Server 2008 R2 is to wait for the official WCF Fix from Microsoft.

When this is available, it can be downloaded from: KB976462 at present this is not available.

Note: There is a WCF Fix available for Windows Vista/Windows 2008 which will fix this issue which is available from here KB971831 note that this fix is NOT for Windows 7/Windows Server 2008 and if you attempt to install it you will receive the "This update is not applicable to your computer" error and the install will terminate.

Thursday, November 12, 2009

Using the PortalSiteMapProvider to query data from large lists

Okay folks, one from the archives but certainly worth rediscovering.

Working with Large Lists in Office SharePoint Server 2007 from Microsoft. The performance metrics for querying large lists in SharePoint using the PortalSiteMapProvider object are quite impressive. I often see customers wanting to manage large lists in SharePoint but hardly ever see implementations using the PortalSiteMapProvider. Well worth a (re)read!

Sunday, October 25, 2009

VHD Boot and Windows Server 2008 R2 for SharePoint 2010 Development

OK, I think I may have came to the party slightly late with VHD Boot. It's one of those things that I've been meaning to look at for quite a while but never got around to. The performance hit that my VM took when running SharePoint 2010 on Windows Server 2008 inside VMWare/Sun VirtualBox served as something of a catalyst.

Simply put, VHD Boot allows you to boot from a VHD running Windows 7/Windows Server 2008 R2 operating system natively. My base setup is a Dell Latitude D630 with 4GB RAM running Windows 7 as the host OS. The steps I took were as follows:

  1. Booted from a Windows Server 2008 R2 CD
  2. Chose the "Repair my Computer" option
  3. Opened diskpart from the command prompt
  4. Created and mounted a new VHD file on my local disk, note: not an external USB drive, by doing the following:

    create vdisk file="d:\vhdboot\sp2010.vhd" type=expandable maximum=40960

    select vdisk="d:\vhdboot\sp2010.vhd"

    attach vdisk
  5. I then closed the command prompt window, closed the repair options and went back to setup
  6. The newly created drive was available as a resource to install to
  7. Install Windows Server 2008 R2 as usual
  8. Your boot menu will now have Windows Server 2008 R2 available as a bootable resource

Enjoy all

Saturday, August 15, 2009

Extending Enterprise Search: Programmatically creating Keywords and Best Bets from existing business data

I've been working with a number of customers lately on strategies to increase the value that enterprise search delivers to their users. I'm interested in "quick wins" with SharePoint: Low cost, high visibility solutions that increase user adoption and add real value to a customer's implementation. I recently had a conversation with some members of the SharePoint community who said that Enterprise Search was a good example of a quick win. I disagree strongly on this point. Simply enabling search within SharePoint will not, in my experience, enhance user adoption or deliver an intuitive and helpful user experience. The implementation of enterprise search is an iterative process of plan, design, implement and review. The correct implementation of keywords, best bets, scopes, managed properties and any number of customisations make search work in an individual environment but it is close to impossible to get enterprise search working optimally first time around. Today I'm going to talk about keywords and best bets and why there is value in creating these programmatically.

N.B. for examples of how to achieve this programmatically, please see Stefan Goßner's blog post listed in the references at the end of this article.

Keywords and Best Bets

Keywords provide a way to supplement search results to deliver a context specific definition for a specific search term. Best bets provide a way to add URLs to these keywords that point to where the user is most likely going to want to go to find the information, given their original search term. The screenshot below shows Keywords and Best Bets in action.

Figure 1: Keywords and Best Bets in action

Why create them programmatically?

The reason to create keywords and best bets programmatically is to increase the value of enterprise search by supplementing search results with information the user is likely to want to know from information that already exists within the business – and there may be a large number of these! This point is worth examining a little further. SharePoint delivers true enterprise search capable of connecting to a number of different types of data sources. In practice every data store in the business is hardly ever indexed by SharePoint, so there is nearly always a gap between what the business knows and what SharePoint knows.

Exactly what keywords and best bets you will need to create will of course depend upon what data you have and whether that is already indexed by SharePoint. One good example is to create keywords of all customers from a CRM system, An internal user then can search for "Customer ABC" and be immediately presented with the customer's name (Keyword), the customer contact details (Keyword Description) and a link to the customer BDC profile page or some other location within SharePoint. The ability to make enterprise search ubiquitous in SharePoint means that users can tap in a customer name from anywhere in SharePoint and immediately get important contact information and links to additional information about the entity.

This is one example and the real value is how this works in individual scenarios with real data. The important "take home" point is that we should think about how we can use keywords and best bets to enhance the user experience with information we already have available. This information may reside within SharePoint already, it may exist in the SharePoint index via BDC crawls or it may exist completely outside of SharePoint altogether. Wherever it exists the important thing is that we make best use of it to extend enterprise search functionality and enhance the user experience.


Stefan Goßner: How To: create Keywords and Best Bets for MOSS Search programmatically

Figure 1 Image sourced from http://technet.microsoft.com/en-us/library/bb905371.aspx

Saturday, August 8, 2009


Blogging is something that I've played around with a few times in the past, and never really followed through on. The principal reason for this is that there has never really been a focus. Well, all that is to change!

As some of you may, or may not know, I'm a Microsoft SharePoint consultant working for a leading Gold Partner in the UK. The work I undertake with customers ranges from helping them develop a business case for SharePoint, through to infrastructure/health-check work and development. The emphasis of the development work I'm currently involved in is around Enterprise Search and general WCM and Publishing. Naturally, there are a lot of things that I see during my time working with customers where I think "you know... I think some other folks really would be interested in that". This blog will serve as a place for recording them in the hope they may help others. More soon...