I have been involved in a lot more Sharepoint development at work lately – moreso than .Net, so I’ve been kind of busy reading up SDKs and playing with APIs.

On that note, I got a chance to get my hands dirty with some OpenXML 2.0. The cool thing about that was that it was @ Microsoft. Not only did I get to play with it, but met the guys that developed parts of it and even wrote 2,000 of the 6,000 pages of documentation – Tristan Davis and Zeyad Rajabi – who helped me a great deal.

The story that I worked on involved gathering data from Sharepoint lists (some of it fed from the BDC(business data catalog)) and using OpenXML to populate a Word template. The real world scenario behind it is at my company, like I’m sure at a lot of other companies, we have administrative assistants who end up generating the documents for meetings. They just sit there copying and pasting data from various data sources and reports into this document. And there are as many documents generated as there are assistants multiplied by weeks in the year. Seriously – these people end up working 60 hour workweeks.

The program utilizing OpenXML to generate these documents for them is actually pretty simple. I had some issues deploying it as a web app onto Sharepoint because of various little issues that arose along the way (running sharepoint on a 64-bit windows 2008 server w/ IIS 7 – some assemblies were 32-bit, so couldn’t get it to run; other times, there were IIS7 issues. Note to self: improve knowledge of IIS7). Because of that, I wrote it as a WinForms app.

The interface is pretty boring – it has 2 listboxes.
The first listbox contains key data – in this case, a list of product names. This field is used as a foreign key to relate it to a bunch of other lists – sales data list, product manager list, and even a Sharepoint picture library.
The second listbox contains a list of template documents from a document library that was created.
There is also a textbox for indicating what the new filename is going to be once the document is generated, and a button that generates the document.

So how does this all work?
First of all, none of this would be possible if Microsoft hadn’t created the new .docx, .xlsx, .pptx formats in Office 2007. As much as I cursed it for doing so – I have seen the light last week. The new format is purely XML based. Hence, OpenXML allows us to manipulate the hell out of our documents without ever opening up Microsoft Word.

The one caveat is that Microsoft Word’s Markup Language (WordML) is a bit cumbersome to learn right off the bat. The good news is that with a bit of blog reading from Brian Jones on OpenXML and an extremelypowerful tool that comes with the SDK called DocumentReflector, you will be generating documents in no time. Here is a video by Alistair Spears that gives a demo of this wonderful tool: [http://video.msn.com/video.aspx/?vid=d50a0a13-836b-4849-9bbc-4c9134a63754&ifs=true&fr=msnvideo&mkt=en-US&from=writer]

So, how did I get data in from sharepoint lists into a word document? Let’s take a look at the process step-by-step.

Traversing Sharepoint Lists/Doc Libraries/Picture Libraries
1. The first thing you will need is to connect to a sharepoint list. The code is the easy part – it’s just 2 lines:

//declarations
protected SPListItemCollection product_owner_items;
protected SPListItemCollection template_doc_items;
protected SPListItemCollection marketing_info_items;
protected SPPictureLibrary product_model_pics;
protected SPSite mysite;
protected SPWeb myweb;
mysite = new SPSite("http://server/Docs/Lists/Product%20Owners/AllItems.aspx");
myweb = mysite.AllWebs["Docs"];

SPSite’s constructor can actually determine what site you are connecting to in your Sharepoint farm based on the URL passed in. You just have to make sure that whichever user your app is running as, must have access to these libraries and to the Sharepoint site.

Here is the code used to get my Sharepoint list objects ready for access:

private void initialize_sharepoint_lists()
{
product_owner_items = mysite.AllWebs["Docs"].Lists["Product Owners"].Items;
marketing_info_items = mysite.AllWebs["Docs"].Lists["Marketing Info List"].Items;
template_doc_items = mysite.AllWebs["Docs"].Lists["Documents"].Items;
product_model_pics = (SPPictureLibrary)mysite.AllWebs["Docs"].Lists["Product Model Images"];
}

In this case, “Docs” is the name of the sharepoint site and the Lists indexers contain the names of the libraries that I am working with in sharepoint.

Once I have the data I need to be inserted into the Word document, I need to create a template in Microsoft Word. This is the easiest part of the operation. You simply open up Microsoft Word, enable the Developer toolbar by right-clicking on the ribbon and insert a text-box field wherever you will have data inserted. Make sure you assign an alias to the text-box – this is how you will access your placeholder for insertion.

Before attempting this exercise, you should find a sample Word 2007 document and open it in DocumentReflector so that you can get an idea of how the XML is formatted because the code below can get confusing REAL quickly if you don’t.

The fields that you created using the developer tab can be accessed one of two ways. If the field is on a new line, it will live in a element. If the field is on the same line as some text, say, a label, it will live in a element. Now, the trick is to locate these elements and pass the data into them.

Here is the method that will take a string, the alias of the field you are manipulating, and a WordProcessingDocument object which contains the open word document. This method will modify a SdtRun block – the element that lives on the same line as other text:

private void populate_run_control(WordprocessingDocument wordDoc,string alias, string value)

{
var contentControls = wordDoc.MainDocumentPart
.Document
.Descendants()
.Where(s => s.SdtProperties.GetFirstChild().Val.Value == alias);

if (contentControls.Count() != 0)
{
var sdt = new SdtContentRun(
new Run(
new RunProperties(
new RunStyleId() { Val = "PlaceholderText" }),
new Text(value)));

contentControls.First().SdtContentRun.RemoveAllChildren();
contentControls.First().SdtContentRun.InsertAt(sdt, 0);

}
}

The method below has the same task in mind, but modifies an SdtContentBlock element that starts on a new line. Another difference is that it takes in an html Table object, which can be easily formed just like you are used to in ASP.Net/C# – Table, TableRow [tr], TableCell, etc. This could be as simplistic or as complex as you want.

private void populate_block_table(WordprocessingDocument wordDoc, string alias, Table value)
{
var contentControls = wordDoc.MainDocumentPart
.Document
.Descendants()
.Where(s => s.SdtProperties.GetFirstChild().Val.Value == alias);

if (contentControls.Count() != 0)
{
var sdt = new SdtContentBlock(
value);

contentControls.First().SdtContentBlock.RemoveAllChildren();
contentControls.First().SdtContentBlock.InsertAt(sdt, 0);

}
}

I won’t bore you with the rest of the code used to open files into MemoryStreams or save them, but if you would like to see the inner workings anyway, I recommend taking a look at Brian Jones’s blog post about Office Extensibility: [http://blogs.msdn.com/brian_jones/archive/2009/01/19/pushing-data-from-a-database-into-a-word-document.aspx]