Skip to main content

Creating Open XML Documents using SharePoint Object Model

Introduction

Open XML files are Zip files that contain XML, and it is very easy to generate or modify Open XML documents programmatically. Using the programmability features of Open XML and SharePoint, we can put together a small document-generation system.

Here we will see how to develop a document-generation system that uses SharePoint lists to populate tables in an Open XML word-processing document.

Sharepoint List

Two SharePoint list Policy and PerminsAndForms are created that contains data we want create the table in the Word document.

openxmlsharepoint01openxmlsharepoint02

Template Document

We need to create a template document which defines format of the output document like Styles, Settings, Fonts, columns of the table etc. Below figure shows the template we created for this sample.

openxmlsharepoint03

Programming for Open XML Using the .NET Framework

Here we need to use Open XML SDK (ver 2.5) and it is available for free download. We should refer documentformat.openxml.dll of the SDK in our project before get into coding.

We also need to refer Microsoft.SharePoint.Client.dll and Microsoft.SharePoint.Client.Runtime.dll to access the SharePoint list and read data from the above two lists.

We have created two classes in this sample project, SPDocumentGenerator class contains the logic to create the word document and SPDataRepository class for communicating to Sharepoint and get the data.

We will see important methods of these classes and the logic involved in generating the final output.

openxmlsharepoint04

CreatePackage method of the SPDocumentGenerator creates a blank document package in the specified path that is passed as parameter to this method. All parts of the document such as Body, Font, Style, Settings, and Themes are created in the CreateParts method. Each of these child parts are created in their respective methods like GenerateMainDocumentPart1Content creates Body of the document, GenerateFontTablePart1Content creates fonts, and GenerateStyleDefinitionsPart1Content creates Styles etc.

GenerateMainDocumentPart1Content is the important method for us now because it creates content of the document and in our case it will create some plain text and table of data from the Sharepoint list.

openxmlsharepoint05

In this method, to begin with, we are creating the body object, and then the paragraph. The most basic unit of block-level content when creating word document from OpenXML is paragraph. A paragraph can contain optional paragraph properties, inline content (Run object that we see in the above code).

After the paragraph, the next level document hierarchy is Run which defines a region of text.  Run can also have properties (RunProperties object). Some examples of run properties are bold, border, character style, color, font, font size, italic, and underline.

The Text object is the container for the text that makes up the document content. We need to use Text object to place text in a Wordprocessing document.

In the above code text “Creating Open XML Documents using SharePoint Object Model” is added to the main document. A Run object contains a region of text within the paragraph and then a RunProperties object is used to apply bold formatting to the run.

Now we will see how to create table.

In the below code we can see that Table object is created and then style, width of the table is defined through TableProperties object.  Required columns and its width are created by using TableGrid object. Then we need start creating TableRow and Cell in the each row.  In our example we have table with four columns with static column header. This Column header is created by using Paragraph, Run and Text object as explained above and then Paragraph object will be added to Cell of the TableRow. “Sl No.” is the first column we created in this table.

openxmlsharepoint06

openxmlsharepoint07

Now we will see how to create data row in this table. In the below code snippet we have created instance of SPDataRepository class which has the logic to retrieve data from the Sharepoint using CSOM (Client Side Object Model). We will explore that code in next section.

We are using getListItems method of the SPDataRepository to get the required SharePoint list. First parameter in getListItems is the SharePoint list name, and other two parameters are useful if we want to fetch list items that match certain criteria. Second parameter is for criteria field name and third is for matching value.

Return values of getListItems method are stored in a collection (lstPolicies). Then we need to create TableRow for each item, and TableCell for each field in the list item. Then as we did for static text, we need to create Paragraph, Run, and Text object for assigning SharePoint List Item field value to the table cell.

openxmlsharepoint08

openxmlsharepoint09

The figure below displays getListItems method of the SPDataRepository. Firstly, we need to get access to SharePoint and this is done by creating ClientContext object with the URL of the SharePoint site. Then using GetByTitle method we are retrieving the specific List whose name is passed to this method.

The next part of the code defines a CAML (Collaborative Application Markup Language) query that specify which items of the List is to be returned. Created query string is to be assigned to Viewxml property of the CamlQuery object.

GetItems() method takes a CamlQuery input to return the items which meets the CamlQuery criteria.

One thing to note here is the client side object model (CSOM) uses the same pattern as SQL, i.e To start with, we need to Build a query, then execute and then Read the result. So here ClientContext object Load method builds the query and ExecuteQuery submits the prepared query to the SharePoint server and retrieves the data.

openxmlsharepoint10

Finally returned result will be sent to the calling object, in our case it is SPDocumentGenerator

Output document which is generated from our application looks like below.

openxmlsharepoint11

Conclusion

One of the important features of Open XML is Programmability. In this blog, we explored a very simple solution to study how we can use the Open XML to create Office documents by referring SharePoint as the data source. This also demonstrates how to reuse information between the applications and the information system.

Balachandra B M

Balachandra B M

Balachandra.M. works as Technical Lead with Trigent Software. He has experience in various Microsoft technologies such as ASP.NET, C#, VB.Net, WCF, MVC, Silverlight, MVVM, RIA services, Web Services, Entity Framework and SSIS. He is an expert in designing applications with n-tier, layered, service-oriented (SOA) architecture. Balachandra is also familiar with integrating .Net application to Sharepoint and CRM.