Brian Jones: Office Solutions

A look into the user experiences around apps for Office and SharePoint

I'm Brian Jones — Tue, 07 Aug 2012 01:31:40 GMT

We're going to have a few posts this week up on the Office Next blog that drill into the new app model from three perspectives: the end user; the developer; and the administrator. This first post that goes into the end user experience is now live: http://blogs.office.com/b/office-next/archive/2012/08/06/introducing-apps-for-the-new-office-and-sharepoint-and-the-office-store.aspx

Open XML SDK 2.5 is now available

I'm Brian Jones — Fri, 27 Jul 2012 15:50:40 GMT

There is new a CTP available of the Open XML SDK version 2.5: http://www.microsoft.com/en-us/download/details.aspx?id=30425

As I mentioned in my post earlier this week, Open XML is an important piece of our overall application model for Office 365 solutions.

Here are a few of the things that are new in this version of the SDK:

New classes for file format extensions in Microsoft Office 2013 Preview
Built on .NET Framework 4.0
Reads the ISO Strict Format
Fixes to some of the bugs from 2.0 (e.g. whitespaces in .pptx document file)

Take a look!

-Brian

New post up on the apps for Office blog

I'm Brian Jones — Mon, 23 Jul 2012 15:47:43 GMT

I just posted on the apps for Office blog, giving some more background into the new model: http://blogs.msdn.com/b/officeapps/archive/2012/07/23/introducing-apps-for-the-new-office-and-sharepoint.aspx

Test Message

I'm Brian Jones — Tue, 10 Jul 2012 16:44:53 GMT

Test message... haven't used the blog in awhile, so want to make sure it still works! :-)

Writing Large Excel Files with the Open XML SDK

I'm Brian Jones — Tue, 22 Jun 2010 16:37:49 GMT

In my last post, I showed you guys how to use the Open XML SDK to read and parse large Excel files. Today, I am going to show you how to write large Excel files with the Open XML SDK. Like in the case of reading, the Open XML SDK provides two approaches for writing Open XML files: 1. using the DOM, 2. using the SAX-like writing capability. All of my examples on this blog have been around using the Open XML SDK DOM to write Open XML files. The DOM makes it easy because everything is strongly typed. Unfortunately, the DOM approach requires loading entire Open XML parts in memory, which can result in Out of Memory exceptions, especially when dealing with very large files. Let's compare the DOM and SAX-like approaches.

If you want to jump straight into the code, feel free to download the solution here.

Scenario

Imagine a scenario where I work for a financial company that deals with very large amounts of data. This data gets updated on a daily basis and is stored in a database. The company's analysts use Excel as a means of mining and understanding the data. To support the analysts' needs, the company needs to create and update Excel spreadsheets every morning. These spreadsheets typically grow to sizes exceeding hundreds of megabytes.

Solution

The scenario described above uses many of the general concepts described in a previous post on writing data from a database into an Excel file. Today, I will show you how to accomplish the scenario using both the DOM and SAX-like approaches. To accomplish the scenario we will need to take the following actions:

Create an empty Spreadsheet template using Excel
Open the template using the Open XML SDK
Connect to the database that contains all the relevant data (for the sake of simplicity, this blog post will instead write random values to cells)
Get the main workbook part
Get the appropriate worksheet
Get the sheetData element, which will contain the data for a given spreadsheet
For the number of rows and columns that represent the dimensions of data to be inserted, add a new cell with appropriate data
Append rows to the sheetData element
Save and close

Step 1 – Setup the right template

As is the case for most of my blog posts, the easiest way to start any Open XML SDK solution is to create the right starting template. In this case, I am going to create a blank Excel spreadsheet with one sheet in Excel. The template will look like the following:

Open XML SDK DOM Approach

The SDK DOM approach is pretty straight forward. The method below will write random values to cells within a given set of dimensions specified by the number of rows and columns. The random values will be calculated by Excel using the RAND() formula, which is specified by the CellFormula class.

static void WriteRandomValuesDOM(string filename, int numRows, int numCols)

{

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))

{

WorkbookPart workbookPart = myDoc.WorkbookPart;

WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();

for (int row = 0; row < numRows; row++)

{

Row r = new Row();

for (int col = 0; col < numCols; col++)

{

Cell c = new Cell();

CellFormula f = new CellFormula();

f.CalculateCell = true;

f.Text = "RAND()";

c.Append(f);

CellValue v = new CellValue();

c.Append(v);

r.Append(c);

}

sheetData.Append(r);

}

The method above will definitely work for writing cell values within a worksheet. However, if the dimension specified is quite large then the program's memory footprint will also be quite large. In fact, you are left at the mercy of the garbage collector, which may result in the program throwing an Out of Memory exception.

Open XML SDK SAX-Like Approach

The OpenXmlReader and OpenXmlWriter can be used together to alleviate some of the performance and/or memory concerns when developing an Open XML solution. You can create an OpenXmlReader and an OpenXmlWriter object on an entire part without loading the part in memory. The OpenXmlReader class allows you to iterate the entire xml tree of a part one node at a time. Similarly, the OpenXmlWriter class allows you to write xml nodes within a part one node at a time.

In the case of this solution, we will need to read the original worksheet part and then update the worksheet with the appropriate data. We are going to use an OpenXmlReader object to read the original worksheet part and find the place in the file where we will add data. We are then going to use an OpenXmlWriter object to write values to the worksheet part. You may be tempted to create an OpenXmlReader object and an OpenXmlWriter object on the same part. I would recommend against using this strategy. Writing data to a part that you are reading using the OpenXmlReader and the OpenXmlWriter may result in the OpenXmlReader losing its place in the xml tree. Instead, you should create a temporary worksheet part where you can write values to using the OpenXmlWriter class. The following code creates an OpenXmlReader object and an OpenXmlWriter object for worksheet parts:

static void WriteRandomValuesSAX(string filename, int numRows, int numCols)

{

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))

{

WorkbookPart workbookPart = myDoc.WorkbookPart;

WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);

WorksheetPart replacementPart =

workbookPart.AddNewPart<WorksheetPart>();

string replacementPartId = workbookPart.GetIdOfPart(replacementPart);

OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);

OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);

...

}

Once we have the appropriate objects created, the next step is to write everything we read into the new worksheet part until we reach the SheetData object. Once we reach the SheetData object, we can start writing cell values within a given set of dimensions specified by the number of rows and columns. The following code accomplishes this task:

static void WriteRandomValuesSAX(string filename, int numRows, int numCols)

{

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))

{

...

Row r = new Row();

Cell c = new Cell();

CellFormula f = new CellFormula();

f.CalculateCell = true;

f.Text = "RAND()";

c.Append(f);

CellValue v = new CellValue();

c.Append(v);

while (reader.Read())

{

if (reader.ElementType == typeof(SheetData))

{

if (reader.IsEndElement)

continue;

writer.WriteStartElement(new SheetData());

for (int row = 0; row < numRows; row++)

{

writer.WriteStartElement(r);

for (int col = 0; col < numCols; col++)

{

writer.WriteElement(c);

}

writer.WriteEndElement();

}

writer.WriteEndElement();

}

else

{

if (reader.IsStartElement)

{

writer.WriteStartElement(reader);

}

else if (reader.IsEndElement)

{

writer.WriteEndElement();

}

...

}

Notice that the OpenXmlWriter class requires us to write both the start and end elements. Invoking the method WriteEndElement() off of the OpenXmlWriter class ensures that the appropriate start element is closed.

We're almost done. The last step is to close the reader and writer and replace the original worksheet part with the replacement part. Remember that the main workbook part contains the list of worksheets within the workbook, which means we need to update the xml contained in that part. The following code accomplishes these tasks:

static void WriteRandomValuesSAX(string filename, int numRows, int numCols)

{

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))

{

...

reader.Close();

writer.Close();

Sheet sheet = workbookPart.Workbook.Descendants<Sheet>()

.Where(s => s.Id.Value.Equals(origninalSheetId)).First();

sheet.Id.Value = replacementPartId;

workbookPart.DeletePart(worksheetPart);

}

Conclusion

This post gives you more insight into reading and writing large Open XML files, while avoiding performance or Out of Memory exceptions. Let me know if you guys have other Open XML SDK related post requests.

Zeyad Rajabi

Parsing and Reading Large Excel Files with the Open XML SDK

I'm Brian Jones — Thu, 27 May 2010 20:03:43 GMT

I want to thank you guys for leaving blog post requests. Today's post will focus on one of your requests: parsing and reading large Excel files with the Open XML SDK. The Open XML SDK provides two approaches for parsing Open XML files: 1. using the SDK DOM, 2. using the SAX-like reading/writing capabilities. The SDK DOM makes it easy to query and parse Open XML files due to strongly typed classes. Unfortunately, the DOM approach requires loading entire Open XML parts in memory, which can sometimes cause Out of Memory exceptions when dealing with really large files. The SAX-like reading/writing capability of the SDK provides a faster and less memory intensive approach. Let's compare the two approaches.

If you want to jump straight into the code, feel free to download the solution here.

Scenario

Imagine a scenario where I work for a financial company that deals with very large Excel spreadsheets. These spreadsheets are updated daily by analysts and can easily grow to sizes exceeding hundreds of megabytes. At the end of the day a solution is used to read and extract relevant data from every spreadsheet.

Here is a screenshot of an example workbook with millions of cells of data:

Solution

The scenario described above is pretty simple and involves many of the general concepts described in a previous post on reading data from Excel files. The main difference in today's post is that I will show you the solution using both the DOM and SAX-like approaches. To accomplish the scenario we will need to take the following actions:

Open the workbook using the Open XML SDK
Get the main workbook part
Get the appropriate worksheet
Read every cell value in the sheet

Open XML SDK DOM Approach

The SDK DOM approach is pretty straight forward. The method below will read every cell value for each row contained within the sheet data.

Note that code below works for cells that contain numeric values. Cells that contain other types of content will need a different type of solution.

static void ReadExcelFileDOM(string filename)

{

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))

{

WorkbookPart workbookPart = myDoc.WorkbookPart;

WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

SheetData sheetData =

worksheetPart.Worksheet.Elements<SheetData>().First();

foreach (Row r in sheetData.Elements<Row>())

{

foreach (Cell c in r.Elements<Cell>())

{

string text = c.CellValue.Text;

}

The method above will definitely work for reading cell values within a worksheet. However, if the worksheet is quite large then the program's memory footprint will also be quite large. In fact, you are left at the mercy of the garbage collector, which may result in the program throwing an Out of Memory exception.

Open XML SDK SAX-Like Approach

If you have performance and/or memory concerns when developing an Open XML solution then you should look into using the OpenXmlReader and OpenXmlWriter. In today's post I will cover how to use the OpenXmlReader. I'll try to cover the OpenXmlWriter in a future post.

The method below will read every cell value for each row contained within the sheet data using the SAX-like approach.

Note that code below works for cells that contain numeric values. Cells that contain other types of content will need a different type of solution.

static void ReadExcelFileSAX(string filename)

{

using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))

{

WorkbookPart workbookPart = myDoc.WorkbookPart;

WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();

OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);

string text;

while (reader.Read())

{

if (reader.ElementType == typeof(CellValue))

{

text = reader.GetText();

}

The method above takes advantage of the OpenXmlReader class, which allows you to read an entire part without loading the part in memory. The Create method has the following signatures:

public static OpenXmlReader Create(OpenXmlElement openXmlElement);
public static OpenXmlReader Create(OpenXmlPart openXmlPart);
public static OpenXmlReader Create(Stream partStream);
public static OpenXmlReader Create(OpenXmlElement openXmlElement, bool readMiscNodes);
public static OpenXmlReader Create(OpenXmlPart openXmlPart, bool readMiscNodes);
public static OpenXmlReader Create(Stream partStream, bool readMiscNodes);

As you can see, the Create method allows you to read either a part or an xml subtree given a specific element. There are methods that allow you to skip miscellaneous nodes that are not associated with the Open XML format. Using the OpenXmlReader class allows you to iterate the entire xml tree one node at a time.

Conclusion

Hopefully, this post gives you more insight into reading large Open XML files, while avoiding performance or Out of Memory Exceptions. If you guys have other Open XML SDK related post requests, let me know.

Thanks,

Zeyad Rajabi

Improvements Made to the Final Open XML SDK 2.0 compared to the December 2009 CTP

I'm Brian Jones — Mon, 03 May 2010 17:54:00 GMT

In a previous post, where I announced the release of the Open XML SDK 2.0, I promised to give you guys a list of improvements and breaking changes made to the SDK compared to the December 2009 CTP.

We made a few tweaks to the SDK based on some of the great feedback we received from you guys. The following sections below outline these changes.

Autosave for Creating Documents

One of the changes we made to the SDK is around supporting autosave for document creation. Back in August 2009 we released a CTP that included autosave functionality when modifying parts within the Open XML package. This functionality allowed for changes to be automatically saved into the package, without the need to call Save() methods. We also provided a mechanism to turn off this functionality. This functionality worked great when opening existing files. However, we never added such functionality for newly created documents. That's what we added this time around for the final version of the SDK. Here are the new Create() methods:

public static WordprocessingDocument Create(Package package, WordprocessingDocumentType type, bool autoSave);
public static WordprocessingDocument Create(Stream stream, WordprocessingDocumentType type, bool autoSave);
public static WordprocessingDocument Create(string path, WordprocessingDocumentType type, bool autoSave);
public static SpreadsheetDocument Create(Package package, SpreadsheetDocumentType type, bool autoSave);
public static SpreadsheetDocument Create(Stream stream, SpreadsheetDocumentType type, bool autoSave);
public static SpreadsheetDocument Create(string path, SpreadsheetDocumentType type, bool autoSave);
public static PresentationDocument Create(Package package, PresentationDocumentType type, bool autoSave);
public static PresentationDocument Create(Stream stream, PresentationDocumentType type, bool autoSave);
public static PresentationDocument Create(string path, PresentationDocumentType type, bool autoSave);

Improved Namespace Processing

The older CTPs used a predefined mapping between the XML prefixes and the different namespaces. In other words, the SDK assumed a specific prefix for a specific namespace. For example, a WordprocessingML Paragraph would always write out the element <p> with the prefix w, such as <w:p>. The issue, that some of you brought up, is that the SDK didn't handle custom prefixes defined in XML fragments if those prefixes conflicted with the predefined list of prefixes of the SDK. With the final version of the SDK we fixed this issue. The final version of the SDK should be a lot more robust.

Breaking Changes in the Final Release of the Open XML SDK 2.0

The following table outlines changes made to Open XML SDK classes and attributes where there is a difference in the December 2009 CTP vs. the final RTW version of the Open XML SDK:

December 2009 CTP Class/Attribute Name	Final RTW SDK Class/Attribute Name
DocumentFormat.OpenXml.Spreadsheet.ObjectAnchor::Z_order	DocumentFormat.OpenXml.Spreadsheet.ObjectAnchor::ZOrder
DocumentFormat.OpenXml.AdditionalCharacteristics	DocumentFormat.OpenXml.AdditionalCharacteristicsInfo
DocumentFormat.OpenXml.Wordprocessing.Alias	DocumentFormat.OpenXml.Wordprocessing.SdtAlias
DocumentFormat.OpenXml.Wordprocessing.LatentStyleException	DocumentFormat.OpenXml.Wordprocessing.LatentStyleExceptionInfo
DocumentFormat.OpenXml.Drawing.Diagrams.Else	DocumentFormat.OpenXml.Drawing.Diagrams.DiagramChooseElse
DocumentFormat.OpenXml.Drawing.Diagrams.If	DocumentFormat.OpenXml.Drawing.Diagrams.DiagramChooseIf
DocumentFormat.OpenXml.Math.Function	DocumentFormat.OpenXml.Math.MathFunction
DocumentFormat.OpenXml.Vml.Handle	DocumentFormat.OpenXml.Vml.ShapeHandle
DocumentFormat.OpenXml.Vml.Handles	DocumentFormat.OpenXml.Vml.ShapeHandles
DocumentFormat.OpenXml.Office2010.Ink.Property	DocumentFormat.OpenXml.Office2010.Ink.ContextNodeProperty
DocumentFormat.OpenXml.CustomProperties.Property	DocumentFormat.OpenXml.CustomProperties.CustumDocumentProperty
DocumentFormat.OpenXml.Spreadsheet.Set	DocumentFormat.OpenXml.Spreadsheet.TupleSet
DocumentFormat.OpenXml.VariantTypes.VTOStream	DocumentFormat.OpenXml.VariantTypes.VTOStreamData
DocumentFormat.OpenXml.VariantTypes.VTStream	DocumentFormat.OpenXml.VariantTypes.VTStreamData
DocumentFormat.OpenXml.VariantTypes.VTVStream	DocumentFormat.OpenXml.VariantTypes.VTVStreamData
DocumentFormat.OpenXml.CustomProperties.CustumDocumentProperty	DocumentFormat.OpenXml.CustomProperties.CustomDocumentProperty

Note: The change from Alias to SdtAlias will affect the most people.

Zeyad Rajabi

Zeyad Rajabi’s Open XML SDK Blog Map

I'm Brian Jones — Tue, 06 Apr 2010 17:38:00 GMT

This post will represent the most up-to-date blog map for topics on the Open XML SDK.

Open XML SDK Basics

Topic	Description
Open XML SDK… the Basics	Learn about classes of the SDK and their first class properties. This post is a great introduction to the Open XML SDK.
Traversing in the Open XML DOM	Learn the basic techniques of traversing in the Open XML DOM tree using the Open XML SDK.
Open XML SDK Code Snippets	Learn how to leverage the Open XML SDK code snippets to create rich solutions.
Finding Open XML Errors with Open XML SDK Validation	Learn how to use the Open XML SDK to validate your Open XML files.
Open XML SDK Productivity Tool	Learn how to use the Open XML SDK Productivity Tool. This tool is the easiest way to learn about the Open XML file formats and the Open XML SDK.

Word

Topic	Description
The Easy Way to Assemble Multiple Documents	Learn how to leverage altChunks to easily create a robust document assembly solution in less than a thirty lines of code.
Taking Advantage of Bound Content Controls	Learn how to leverage bound content controls to bind to custom XML.
Pushing Data from a Database into a Word Document	Learn how to build a repeating table within WordprocessingML based on data within a database.
Removing Comments from a Wordprocessing Document Programmatically	Learn how to remove comments from a Word document.
Retrieving Word Content Based on Styles	Learn how to retrieve content in a document based on style information.
Removing Page and Section Breaks from a Word Document	Learn how to remove page and section breaks in a Word document.

Excel

Topic	Description
Document Assembly Solution for SpreadsheetML	Learn how to populate a spreadsheet with data from a real SQL database and create a cool looking chart based on that data.
Read Data from SpreadsheetML	Learn how to read data from a spreadsheet and insert it into some data source, like a database.
Writing Semantically Aware LINQ Queries Against Excel Tables	Learn how to write a generic LINQ query that can work against any data source, including an Excel table.
How to Copy a Worksheet within a Workbook	Learn how to copy a worksheet within the same workbook.
Removing Comments from Excel and PowerPoint Files	Learn how to remove comments from an Excel workbook.
Read Large Excel Files using the Open XML SDK	Learn how to use the SAX-like capabilities of the Open XML SDK to read very large Excel files.

PowerPoint

Topic	Description
Creating a Presentation Report Based on Data	Learn how to create a rich presentation report based on data from a database.
How to Assemble Multiple PowerPoint Decks	Learn how to merge multiple PowerPoint decks together.
Removing Comments from Excel and PowerPoint Files	Learn how to remove comments from a PowerPoint deck.
Adding Repeating Data to PowerPoint	Learn how to create repeating data within a table on a PowerPoint slide.

Multiple Applications (Word, Excel, and PowerPoint)

Topic	Description
Importing Charts from Spreadsheets to Wordprocessing Documents	Learn how to import a chart from a spreadsheet to a Word document.
Importing a Table from WordprocessingML to SpreadsheetML	Learn how to import a table from a Wordprocessing document into a spreadsheet.
Importing SmartArt from PowerPoint to Word	Learn how to import SmartArt from a PowerPoint deck into a Word document.
Embedding an Open XML File in another Open XML File	Learn how to embed Open XML files within other Open XML documents.
Embedding Any File Type, Like PDF, in an Open XML File	Learn how to embed any file within Open XML documents.
The Open XML SDK and Fluent UI Extensibility	Learn how to add custom UI to a set of documents within a directory.
Document Assembly: Merging Excel, PowerPoint, and Word Content Together	Learn how to take multiple Word, Excel and PowerPoint documents and merge them all together to form a final Word document.

Office Services

Topic	Description
Extending the Reach of Excel Services with the Open XML SDK	Learn how to extend the reach of Excel Services and hosted charts to gather data from within a library of Word documents.
Leveraging the Power of Word Automation Services and the Open XML SDK	Learn how to use Word Automation Services to update a table of contents of several documents have been merged with the Open XML SDK
Open XML SDK + Office Services: Better Together	Learn how to leverage all three components, the SDK and the two Office services, to create a very cool mash up document assembly solution

Presentations

Year + Conference + Session	Presentation
2008 PDC - Open XML Format SDK: Developing Open XML Solutions
2009 SPC – Deep Dive Open XML and Open XML SDK
2009 PDC – Open XML SDK + Office Services

Zeyad Rajabi

Announcing the Release of the Open XML SDK 2.0

I'm Brian Jones — Fri, 12 Mar 2010 22:49:00 GMT

Today, I am really excited to announce the worldwide availability of the Open XML SDK 2.0 for Microsoft Office! The Open XML SDK plays an integral part in creating Office document solutions that work on the client or server. The Open XML SDK allows you to create, consume, and manipulate Open XML files without needing to automate Office client applications. Combining the power of Office Services and the Open XML SDK enables even more scenarios, such as rich server-side document assembly solutions. Using the Open XML SDK and Office Services, you now have an alternative for many of the scenarios where previously you would have run into one of Office's most popular knowledge base articles that talks about how automating Office client applications on the server is not supported (http://support.microsoft.com/kb/257757).

Development of the Open XML SDK 2.0 spanned almost two years and involved the release of four Technical Previews in addition to the final release announced today. Your feedback via the blogs, MSDN forums, www.openxmldeveloper.com forums, and Microsoft Connect site helped shape the overall design of the SDK. Thank you so much for your support and feedback! Please feel free to continue sending us feedback with respect to the Open XML SDK.

Download the SDK

Download the Open XML SDK 2.0 for Microsoft Office
This download provides strongly typed part and content classes for use with Office 2007 & Office 2010 Open XML documents. http://www.microsoft.com/downloads/details.aspx?FamilyID=c6e744e5-36e9-45f5-8d8c-331df206e0d0

Release Notes

Open XML SDK 2.0 is freely redistributable by any solution provider implementing the formats
Office applications are not required to use the Open XML SDK 2.0, and no software purchase is required. Developers can freely use the Open XML SDK to build document processing solutions for Microsoft Office files and for other implementations of the IS29500 standard

Learn More about the Open XML SDK

Over the past year and a half, I have shown you guys a lot of real world scenarios/solutions that are built with the SDK. Here is a summary of links that will help you learn more about the Open XML SDK:

MSDN
- Open XML Developer Center
  - This site is your one-stop shop for finding all information related to Open XML and the Open XML SDK
- Office Developer Center
- Office Visual-How-To sites
- An overview of Open XML SDK 2.0 for Microsoft Office
- Class Library References
- Microsoft Office File Format Documents
YouTube Videos
Forum & Blogs
- http://openxmldeveloper.org/ - A place to learn and share more about Open XML Formats, news, information, and getting questions answered
- http://social.msdn.microsoft.com/Forums/en-US/oxmlsdk - A discussion forum for developer topics related to the Open XML Format SDK
- http://blogs.msdn.com/ericwhite/ - Eric White's blog that focuses on Open XML and SharePoint development. This blog contains a lot of rich information on the Open XML file formats as well as sample code related to the Open XML SDK
Conference Presentations
- Open XML SDK + Office Services Presentation at PDC
- Deep Dive Open XML and the Open XML SDK Presentation at SPC

In a future post I will outline the improvements we made to the Open XML SDK 2.0 compared to the December 2009 CTP.

Zeyad Rajabi

Open XML SDK + Office Services: Better Together

I'm Brian Jones — Fri, 26 Feb 2010 21:35:05 GMT

I am probably starting to sound like a broken record, but I am really excited about the different types of solutions that are possible when combining the Open XML SDK with Office Services. In the past, I showed you how to leverage the Open XML SDK to accomplish three main scenarios:

Push data into Open XML files
Pull data out of Open XML files
Manipulate Open XML files

In a previous post, I showed you how to use Word Automation Services to update fields, such as a table of contents, within a document. Today, I am going to show you another functionality of Word Automation Services that will allow you to perform file format conversions on the server. For example, you can leverage the service to convert Word documents into PDF. In addition to Word Automation Services, I am going to show you how to leverage Excel Services to recalculate charts and formulas within a workbook.

In today's post, I am going to show you how to leverage all three components, the SDK and the two Office services, to create a very cool mash up document assembly solution.

If you want to jump straight into the code, feel free to download the solution here.

Scenario

Imagine a scenario where I am working for a company that deals with mortgages. One of the services our company provides is creating custom mortgage reports for customers wishing to take out a loan. These reports include rich information and charts that are specific to the customer's mortgage specifics, such as mortgage amount, number of years of the mortgage, and interest rate of the loan. My company uses Excel workbooks to calculate the different financial and mortgage calculation models. In fact, for the sake of this blog post we will use the same workbook mentioned in this post on the Excel team blog. My company has asked me to create a server-side solution that is able to leverage the Excel workbook that contains the mortgage calculation model and generate a customer ready PDF report, which contains rich content, such as charts and data pulled in from the workbook.

Solution

The scenario described above is very similar to the scenario described by Brian Jones in his post that first talked about combining Open XML and Office Services. To accomplish this scenario we will need to take the following actions:

Create an Excel workbook that represents the company's mortgage calculation model
Create a Word document that represents the look and feel of the final report. This document will contain content controls to demarcate regions where content and data will be merged
Create a web part for SharePoint that includes three text fields, which will allow users to input a loan amount, number of years of the loan, and interest rate, and a button, which will allow users to generate the final mortgage report in PDF format
Using the Open XML SDK, open the Excel workbook, which contains the mortgage calculation model, and inject the values specified within the three text fields
Leverage Excel Services to recalculate the Excel workbook
Use the Open XML SDK to pull data and content from the recalculated Excel workbook and push the content into the Word template document
Leverage Word Automation Services to convert the Word document into a PDF

Step 1 & 2 – Creating the Right Excel and Word Templates

The right template makes all the difference when creating Office document solutions. The Excel workbook that contains the mortgage calculation model requires three values to perform calculations:

Mortgage amount
Term (years)
Fixed interest rate

In order to make it easy to insert these three values into the workbook, I am going to leverage Defined Names. Defined Names provides a mechanism to demarcate one or more cells in a workbook, which makes it really easy to find content and regions within workbooks. Here is a screenshot of my Excel workbook template:

Notice that this workbook contains pretty rich formulas in order to calculate monthly payments and rich charts.

The Word template document that represents the look and feel of the final report looks pretty similar to other templates I've shown you in the past that leverage content controls. Here is a screenshot of my Word document template:

You can find these two template files here.

Step 3 – Create a Web Part with a Button Command and Text Fields

Our end goal is to create a Web Part that contains three textboxes and a button command that will look like the following:

The easiest way to accomplish this task is to create a Web Part within Visual Studio 2010. We can create a user control ascx page that represents our web part with the following code:

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>

<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>

<%@ Import Namespace="Microsoft.SharePoint" %>

<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="VisualWebPart1UserControl.ascx.cs" Inherits="WordExcelServicesDemo.VisualWebPart1.VisualWebPart1UserControl" %>

.style1

{

width: 235px;

}

</style>

<tr>

<td>

Amount:</td>

<td>

<asp:TextBox ID="AmountTextBox" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Term (Years):</td>

<td>

<asp:TextBox ID="YearsTextBox" runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

Interest:</td>

<td>

<asp:TextBox ID="InterestTextBox" runat="server"></asp:TextBox>

</td>

</tr>

</table>

<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Generate Report" />

The code behind this ascx page will contain functionality behind the Generate Report button command:

public partial class VisualWebPart1UserControl : UserControl

{

protected void Page_Load(object sender, EventArgs e)

{

}

protected void Button1_Click(object sender, EventArgs e)

{

//Code to perform pushing/pulling/generating of data/content

...

}

So now we're ready to add the Open XML SDK and Office Services code. For the sake of this solution I am going to add my two template documents within a specific templates directory within my SharePoint library called "Excel Template".

Step 4 – Add Data to Excel Workbook

This step is all about inserting data into specific regions within our Excel workbook. I am going to leverage a couple of Open XML SDK code snippets to help out with this task. Specifically, I am going to reuse the following snippets:

XLGetWorksheetPartByName
InsertCellInWorksheet

Here is the code necessary to push data into specific regions of an Excel workbook:

protected void Button1_Click(object sender, EventArgs e)

{

SPWeb myWebSite = SPContext.Current.Web;

string outputExcelFilename = myWebSite.Url + @"/Shared%20Documents/output.xlsx";

string outputWordFilename = myWebSite.Url + @"/Shared%20Documents/output.docx";

string amountTxBx = AmountTextBox.Text;

string interestTxBx = InterestTextBox.Text;

string termTxBx = YearsTextBox.Text;

if (amountTxBx == "")

amountTxBx = "200000";

if (interestTxBx == "")

interestTxBx = ".09";

if (termTxBx == "")

termTxBx = "30";

//Copy templates

SPWeb myWebsite = SPContext.Current.Web;

SPFile templateExcelFile = myWebsite.GetFile(myWebSite.Url + @"/Excel%20Template/Mortgages.xlsx");

templateExcelFile.CopyTo(outputExcelFilename, true);

SPFile templateWordFile = myWebsite.GetFile(myWebSite.Url + @"/Excel%20Template/Template.docx");

templateWordFile.CopyTo(outputWordFilename, true);

//Update Excel output file

SPFile outputExcel = myWebsite.GetFile(outputExcelFilename);

byte[] byteArray = outputExcel.OpenBinary();

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(mem, true))

{

WorksheetPart sheet1 = XLGetWorksheetPartByName(spreadsheetDocument, "Sheet1");

AddCellValues(sheet1, amountTxBx, interestTxBx, termTxBx);

}

outputExcel.SaveBinary(mem);

}

...

}

protected void AddCellValues(WorksheetPart sheetPart, string amount, string interest, string term)

{

I am going to cheat a bit here and not calculate the Column/Row index of the defined names (You can look http://blogs.msdn.com/brian_jones/archive/2008/11/10/reading-data-from-spreadsheetml.aspx for more information on how to accomplish this task

<definedName name="AMOUNT">Sheet1!$A$2</definedName>

<definedName name="FIR">Sheet1!$A$8</definedName>

<definedName name="TERM">Sheet1!$A$5</definedName>

* */

Excel.Cell cell = InsertCellInWorksheet(sheetPart.Worksheet, "A2");

cell.CellValue.Text = amount;

cell = InsertCellInWorksheet(sheetPart.Worksheet, "A5");

cell.CellValue.Text = term;

cell = InsertCellInWorksheet(sheetPart.Worksheet, "A8");

cell.CellValue.Text = interest;

}

Step 5 – Recalculate Workbook using Excel Services

Step 4 helped push data into the Excel workbook. Our next task is to recalculate the workbook. This task is pretty easy to do with Excel Services:

protected void Button1_Click(object sender, EventArgs e)

{

...

//RECALC workbook

ExcelService es = new ExcelService();

Status[] status;

string sessionId = es.OpenWorkbookForEditing(outputExcelFilename, "en-US", "en-US", out status);

es.CalculateWorkbook(sessionId, CalculateType.CalculateFull, out status);

es.SaveWorkbook(sessionId, out status);

es.CloseWorkbook(sessionId, out status);

...

}

Step 6 – merge Excel Content Data into Word Document

I've shown this step a number of times in previous blog posts. For this task I am going to leverage the following Open XML SDK code snippets:

XLGetWorksheetPartByName
WDGetContentControl
XLGetCellValueRowCol

Here is the necessary code to accomplish this task:

protected void Button1_Click(object sender, EventArgs e)

{

...

outputExcel = myWebsite.GetFile(outputExcelFilename);

byte[] byteArrayExcel = outputExcel.OpenBinary();

using (MemoryStream memExcel = new MemoryStream())

{

memExcel.Write(byteArrayExcel, 0, (int)byteArrayExcel.Length);

using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(memExcel, true))

{

WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;

WorksheetPart sheet1 = XLGetWorksheetPartByName(spreadsheetDocument, "Sheet1");

WorksheetPart sheet2 = XLGetWorksheetPartByName(spreadsheetDocument, "Sheet2");

SPFile outputWord = myWebsite.GetFile(outputWordFilename);

byte[] byteArrayWord = outputWord.OpenBinary();

using (MemoryStream memWord = new MemoryStream())

{

memWord.Write(byteArrayWord, 0, (int)byteArrayWord.Length);

using (WordprocessingDocument myDoc = WordprocessingDocument.Open(memWord, true))

{

MainDocumentPart mainPart = myDoc.MainDocumentPart;

Word.SdtElement monthlyPaymentsChartSdt = WDGetContentControl(mainPart,

"MonthlyPaymentsChart");

ImportChartFromSpreadsheet(mainPart, monthlyPaymentsChartSdt, sheet2, "rId2");

Word.SdtElement totalPaymentsChartSdt = WDGetContentControl(mainPart,

"TotalPaymentsChart");

ImportChartFromSpreadsheet(mainPart, totalPaymentsChartSdt, sheet2, "rId1");

Word.SdtElement interestRateChartSdt = WDGetContentControl(mainPart,

"PaymentsInterestRateChart");

ImportChartFromSpreadsheet(mainPart, interestRateChartSdt, sheet1, "rId1");

Word.SdtElement mortgageAmountSdt = WDGetContentControl(mainPart, "MortgageAmount");

AddTextToSdt(mainPart, mortgageAmountSdt, "$" + XLGetCellValueRowCol(wbPart, sheet1,

"A", 2));

Word.SdtElement termSdt = WDGetContentControl(mainPart, "Term");

AddTextToSdt(mainPart, termSdt, XLGetCellValueRowCol(wbPart, sheet1, "A", 5));

Word.SdtElement interestRateSdt = WDGetContentControl(mainPart, "InterestRate");

AddTextToSdt(mainPart, interestRateSdt, XLGetCellValueRowCol(wbPart, sheet1, "A",

8));

Word.SdtElement monthlyPaymentSdt = WDGetContentControl(mainPart, "MonthlyPayment");

string amount = XLGetCellValueRowCol(wbPart, sheet1, "B", 11);

decimal decAmount;

Decimal.TryParse(amount, out decAmount);

decAmount = Math.Round(decAmount, 2);

AddTextToSdt(mainPart, monthlyPaymentSdt, "$" + decAmount);

Word.SdtElement paymentTableSdt = WDGetContentControl(mainPart, "PaymentTable");

ImportTableFromSpreadsheet(mainPart, paymentTableSdt, wbPart, sheet2, 3, 17, 10, 50);

}

outputWord.SaveBinary(memWord);

}

...

}

protected void AddTextToSdt(MainDocumentPart mainPart, Word.SdtElement sdt, string text)

{

sdt.SdtProperties.RemoveAllChildren<Word.SdtPlaceholder>();

Word.Paragraph oldP = sdt.Descendants<Word.Paragraph>().First();

Word.Paragraph p = new Word.Paragraph(

new Word.Run(

new Word.Text(text)));

OpenXmlElement parent = oldP.Parent;

parent.ReplaceChild<Word.Paragraph>(p, oldP);

}

One method I am not going to show in this post is how to import a table from a spreadsheet into a Word document since I've already showed you how to accomplish this task in my previous post showing you how to merge Excel, PowerPoint, and Word content together.

Step 7 – Convert Word Document into PDF

Our last step is to convert our Word document into PDF. This task is really easy to accomplish with Word Automation Services. Here is the necessary code to accomplish this task:

protected void Button1_Click(object sender, EventArgs e)

{

...

ConversionJob pdfJob = new ConversionJob("Word Automation Services");

pdfJob.Name = "Mortgage Report";

pdfJob.UserToken = myWebsite.CurrentUser.UserToken;

pdfJob.AddFile(outputWordFilename, outputWordFilename.Replace(".docx", ".pdf"));

pdfJob.Start();

...

}

Only five lines of code!

End Result

Putting everything together and running the code we end up with a PDF that contains all the content from our Word and Excel documents:

Zeyad

Office Developer Atlas

I'm Brian Jones — Wed, 17 Feb 2010 00:04:00 GMT

John Durant just announced a new interactive developer training tool, based on Silverlight, called Office Developer Atlas. This tool is a great starting point for learning how to develop Office solutions. The current version of the tool concentrates on the following:

Building solutions with VBA (Visual Basic for Applications)
Customizing the Ribbon Fluent UI
Customizing the Office 2010 Backstage View

Let John or I know if you have requests for other modules in the training environment.

Zeyad

Leveraging the Power of Word Automation Services and the Open XML SDK

I'm Brian Jones — Tue, 09 Feb 2010 21:58:44 GMT

Have you ever wanted to update page number fields or a table of contents within Word documents on the server? The Open XML SDK provides functionality that allows you to easily add or remove content within a Word document. However, as mentioned in the announcement of the Open XML SDK 2.0, the SDK does not provide runtime application behaviors such as layout and recalculation. In order to recalculate a table of contents you need to run some kind of layout engine in order to properly determine pages within a document.

Word Automation Services is designed to handle tasks that require application logic, such as file conversion and layout. As Brian mentioned in a previous post, Open XML and Office Services are really meant to work better together. In this post, I am going to show you how to leverage Word Automation Services to update a table of contents after a document has been modified by the Open XML SDK.

If you want to jump straight into the code, feel free to download the solutions here.

Scenario

Imagine a scenario where I am working with a group of people to create a book about the solar system. We've divided up the book into separate chapters where each chapter is assigned to a particular author. Once everyone is done authoring their assigned chapter we want to merge all the documents into one final document. In fact, this scenario is very similar to the scenario I talked about in a previous post titled the easy way to assemble multiple Word documents. The big difference is that in this scenario I want to make sure all page references, including the table of contents, are properly set in the final document.

Solution

The scenario discussed above requires two actions:

Assemble multiple Word documents into one final document using the Open XML SDK
Leverage Word Automation Services, which is part of SharePoint 2010, to updates any fields, like the table of contents, within the document

As is the case with many of my previous posts setting up the right template is the most important step in starting Office document solutions. Once we have the template setup, our next task is to come up with an easy way for users to run the document assembly solution. Since this solution will run on SharePoint, we will create a custom action, which users will be able to access right from a drop down menu off of the template document. This custom action will run the code necessary to assemble the document as well as call into Word Automation Services to update fields.

In summary, we will need to take the following actions:

Create the right template
Create SharePoint libraries that will store the template as well as chapter documents
Create a custom action that can be invoked from the SharePoint document drop down menu. This custom action will allow users to assemble all the documents together
Using the Open XML SDK, open the template document and look for all content controls
For every content control found, find the corresponding document in the library and merge that content into the final document
Once the document assembly is complete, invoke Word Automation Services to update the fields in the final document

Step 1 – Creating the Right Template

The template will represent the final look of the document we want to create. In this template we will merge a specific chapter in a specific location within the template. We will leverage content controls as an easy mechanism for specifying semantic regions within a document. In other words, content controls allow us to uniquely identify a specific region within a document. Here is a screenshot of the template we will use:

In the example above, we will use the content control named "SolarOverview" to represent the location where the solar system overview document will be merged. The content of the content control, in this case, "Planets/SolareOverview.docx", represents the SharePoint library location of the document to be merged.

Step 2 – Leveraging SharePoint Libraries

The template document will exist in its own SharePoint library, while the chapters of the solar system book will be stored in the Planets SharePoint library:

Note: There is no technical reason to separate the location of the template document from the chapter documents.

Step 3 – Create a Custom Action within SharePoint

There are several ways to provide UI to users to allow them to invoke our document assembly solution. For the sake of this blog post, we are going to create a custom action that can invoke the document assembly solution straight off of the drop down menu for our template document. Here is a screenshot of the custom action we will create:

Notice that this custom action menu has two Assemble Open XML Document commands. The difference between these two commands is that one of the commands will also invoke Word Automation Services to update fields within the document. In order to create a custom action within SharePoint we will need to create our own custom feature. Here is the xml necessary to create such a feature:

<Feature

Id="2119559A-5740-42fa-83E5-C02FB46FC701"

Title="Open XML Demo"

Description="Open XML Demo"

Version="1.0.0.0"

Scope="Web"

Hidden="FALSE"

ImageUrl="menuprofile.gif"

xmlns="http://schemas.microsoft.com/sharepoint/">

</ElementManifests>

</Feature>

Our next task is to define what this feature looks like via the elements.xml file:

<CustomAction Id="Assemble Document"

RegistrationType="List"

RegistrationId="101"

ImageUrl="/_layouts/images/GORTL.GIF"

Location="EditControlBlock"

Sequence="101"

Title="Assemble Open XML Document" >

</CustomAction>

<CustomAction Id="Assemble Document (New)"

RegistrationType="List"

RegistrationId="101"

ImageUrl="/_layouts/images/GORTL.GIF"

Location="EditControlBlock"

Sequence="101"

Title="Assemble Open XML Document (New)" >

</CustomAction>

</Elements>

The above xml defines two custom actions, called "Assemble Open XML Document" and "Assemble Open XML Document (New)". These commands will direct users to two different ASP.NET urls, which will allow users to specify the name of the merged document. Both urls will contain a text field as well as an Assemble Document button:

Step 4 – Merge Documents with the Open XML SDK

The document assembly solution will be invoked via the Assemble Document button command from the ASP.NET url mentioned above. This command will perform the following actions:

Retrieve the template document from SharePoint library
Open the template document with the Open XML SDK
Retrieve all content controls and their content from the template document
Retrieve all referenced documents from the appropriate SharePoint library
Replace the content control within the template with the merged content from the SharePoint library (using altChunks)
Save the final document with the name/path specified by the text field from the ASP.NET url

The following code snippet accomplishes these actions:

protected void AssembleDocumentBtn_Click(Object sender, EventArgs e)

{

SPSite siteCollection = this.Site;

SPWeb site = this.Web;

site.AllowUnsafeUpdates = true;

lblTemplateDocument.Text = tbNewDocumentName.Text + "<=====";

string ListId = Request.QueryString["ListId"];

SPList list = site.Lists[new Guid(ListId)];

string ItemId = Request.QueryString["ItemId"];

SPListItem item = list.Items.GetItemById(Convert.ToInt32(ItemId));

if (list is SPDocumentLibrary)

{

SPDocumentLibrary documentLibrary = (SPDocumentLibrary)list;

SPFile file = site.GetFile(item.Url);

byte[] byteArray = file.OpenBinary();

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

using (WordprocessingDocument myDoc =

WordprocessingDocument.Open(mem, true))

{

var contentControls = myDoc.MainDocumentPart

.Document

.Descendants<SdtBlock>()

.Select(b => GetTextFromContentControl(b));

var insertList = contentControls

.Select(s => s.Trim().Split('/'))

.Where(g => g.Count() == 2)

.Select(g =>

new

{

DocumentLibrary = g[0],

DocumentName = g[1]

}

);

var libraryList = insertList.Select(c => c.DocumentLibrary)

.Distinct().ToList();

List<InsertDocument> insertDocumentList =

new List<InsertDocument>();

foreach (var lib in libraryList)

{

SPList clauseList = site.Lists[lib];

SPListItemCollection listItems = clauseList.Items;

// get list of all insert documents from the document

// libraries

for (int i = 0; i < listItems.Count; ++i)

{

insertDocumentList.Add(

new InsertDocument()

{

DocumentLibraryName = lib,

DocumentName =

listItems[i]["LinkFilename"]

.ToString(),

ListItems = listItems

}

);

}

ReplaceContentControls(myDoc, insertDocumentList);

}

// write it back to the document library

SPFolder fldr = site.GetFolder(list.RootFolder.Url);

SPFileCollection files = fldr.Files;

files.Add(tbNewDocumentName.Text, mem, true);

}

string libraryRelativePath = documentLibrary.RootFolder

.ServerRelativeUrl;

string libraryPath = siteCollection.MakeFullUrl(libraryRelativePath);

Response.Redirect(libraryPath);

}

private void ReplaceContentControls(WordprocessingDocument myDoc,

List<InsertDocument> insertDocumentList)

{

MainDocumentPart mainPart = myDoc.MainDocumentPart;

List<SdtBlock> sdtList = mainPart.Document.Descendants<SdtBlock>()

.ToList();

foreach (var sdt in sdtList)

{

string[] text = sdt.InnerText.Trim().Split('/');

InsertDocument insertDocument = GetInsertDocument(insertDocumentList,

text[0], text[1]);

if (insertDocument != null)

{

// create unique AltChunkId

string altChunkId = "AltChunkId" + (insertDocument.Idx).ToString();

// grab the file from SharePoint

SPFile insertFile = insertDocument

.ListItems[insertDocument.Idx].File;

byte[] insertDocByteArray = insertFile.OpenBinary();

// create the new chunk part

AlternativeFormatImportPart chunk =

mainPart.AddAlternativeFormatImportPart(

AlternativeFormatImportPartType.WordprocessingML,

altChunkId);

// create a memory stream from the byte array, and feed the

// memory stream into the newly created chunk

using (MemoryStream insertMem = new MemoryStream())

{

insertMem.Write(insertDocByteArray, 0,

(int)insertDocByteArray.Length);

insertMem.Seek(0, SeekOrigin.Begin);

chunk.FeedData(insertMem);

}

AltChunk altChunk = new AltChunk();

altChunk.Id = altChunkId;

// add the chunk element and remove the content control

OpenXmlElement parent = sdt.Parent;

parent.InsertAfter(altChunk, sdt);

sdt.Remove();

}

mainPart.Document.Save();

}

static string GetTextFromContentControl(SdtBlock contentControlNode)

{

return contentControlNode.Descendants<Paragraph>()

.Select

(

p =>

{

var t = p.Elements()

.Where(z => z is Run || z is InsertedRun)

.SelectMany(r => r.Elements<Text>());

return t.StringConcatenate(text => text.Text) +

Environment.NewLine;

}

).StringConcatenate();

}

private class InsertDocument

{

public string DocumentLibraryName { get; set; }

public string DocumentName { get; set; }

public SPListItemCollection ListItems { get; set; }

public int Idx { get; set; }

}

private InsertDocument GetInsertDocument(List<InsertDocument> source,

string library, string document)

{

return source.Select(

(s, i) => new InsertDocument()

{

DocumentLibraryName = s.DocumentLibraryName,

DocumentName = s.DocumentName,

ListItems = s.ListItems,

Idx = i

}

)

.Where(s => s.DocumentLibraryName == library &&

s.DocumentName == document)

.FirstOrDefault();

}

The above code snippet will merge the Open XML documents together and will ensure that all formatting and content are preserved. Here is a screenshot of how the merged document looks like:

That being said, the code snippet will not update your table of contents, as shown below:

Step 5 – Updating Fields with Word Automation Services

Instead of requiring users to manually update their table of contents, we can perform this action automatically with Word Automation Services. That's where our second custom action command will come into play. The second custom action command is exactly the same as our first command except that it will also invoke Word Automation Services. Here is the code snippet for the second custom action command:

protected void AssembleDocumentNewBtn_Click(Object sender, EventArgs e)

{

...

if (list is SPDocumentLibrary)

{

...

string outputFileUrl = site.Url + "/" + tbNewDocumentName.Text;

ConversionJob convJob = new ConversionJob("Word Automation Services");

convJob.Name = "Document Assembly";

convJob.UserToken = site.CurrentUser.UserToken;

convJob.Settings.UpdateFields = true;

convJob.AddFile(outputFileUrl, outputFileUrl);

convJob.Start();

string libraryRelativePath = documentLibrary.RootFolder

.ServerRelativeUrl;

string libraryPath = siteCollection.MakeFullUrl(libraryRelativePath);

Response.Redirect(libraryPath);

}

As you can see, calling into Word Automation Services is pretty easy; it's only six lines of code!

End Result

Using the above code we should end up with a merged document that has an updated table of contents:

I am excited about this solution because it shows you the power of combining the Open XML SDK with Word Automation Services.

Zeyad Rajabi

Open XML SDK Code behind the Custom XML Markup Detection Tool

I'm Brian Jones — Wed, 27 Jan 2010 17:12:10 GMT

Recently on Gray's blog he posted about a scanning tool that can be used to detect custom XML markup in Word Open XML files (*.docx, *.docm, *.dotm, and *.dotx). The tool is built using the Open XML SDK and we wanted to take the opportunity to show you how the solution works in this blog.

Solution

The scenario we want to support is, given a directory, find all Word Open XML documents that contain custom XML markup (w:customXml elements). In order to accomplish this scenario we will need to take the following actions:

Given a directory, get all Word Open XML files
For each file found, open the document with the Open XML SDK
Get all xml parts contained with the document
For each part, count the number of occurrences of custom XML markup. Count the total across all the parts contained within the document
Show results of scan

If you want to jump straight into the code, feel free to download this solution here.

The solution uses the December 2009 CTP of the Open XML SDK 2.0 for Microsoft Office, which you can learn more about in this introduction to the Open XML SDK. You can certainly use version 1.0 of the Open XML SDK, but version 2.0 makes things a lot easier, especially with all the improvements added to the December 2009 CTP as described in the CTP announcement blog post.

Step 1 – Find all Word documents in a directory

For the sake of simplicity we are going to build a command line solution that expects one argument, which will represent the directory that will be scanned by the tool. Given this directory and all of its subdirectories, we are going to look for all Word Open XML documents, which have the following extensions: .docx, .docm, .dotx, and .dotm. Performing this task is pretty simple with the class DirectoryInfo and the method GetFiles. The only issue is that the GetFiles method only allows you to search for one extension at a time. Files or directories that cannot be scanned, for example due to file permissions, will be reported in a separate list. Here is a code snippet to solve this issue and look for all files given multiple extension types:

private static string lookfor = "*.docx;*.docm;*.dotx;*.dotm";

private static string[] extensions = lookfor.Split(new char[] { ';' });

// Files with the above extensions will be added to this list

private static List<string> files = new List<string>();

// Track files with errors (such as IRM-protected documents we cannot load) in a //separate list

private static Dictionary<string, object> errors = new Dictionary<string, object>();

private static int folderCount = 0;

static void ShowHelp()

{

Console.WriteLine();

Console.WriteLine("DetectCustomXMLMarkup.exe - Detect Custom XML Markup in

Documents Tool");

Console.WriteLine("Usage:");

Console.WriteLine(" DetectCustomXMLMarkup.exe [Path To Folder]");

Console.WriteLine(@"Example: DetectCustomXMLMarkup.exe C:\temp");

Console.WriteLine("For more information about Custom XML Markup, please see

Microsoft Knowledge Base article 978951");

Console.WriteLine();

}

//Recursive method to get all files in a directory and all it's sub-directories

static void GetAllFiles(string parentFolder)

{

folderCount++;

if (folderCount % 10 == 0)

Console.Write(".");

try

{

foreach (string ext in extensions)

{

files.AddRange(Directory.GetFiles(parentFolder, ext,

SearchOption.TopDirectoryOnly));

}

foreach (string subFolder in Directory.GetDirectories(parentFolder))

{

GetAllFiles(subFolder);

}

//all files/directories that cannot be accessed will be reported as an error

catch (Exception e)

{

errors.Add(parentFolder, e.Message.Replace("\n",

string.Empty).Replace("\r", string.Empty));

}

static void Main(string[] args)

{

if (args.Length != 1)

{

ShowHelp();

return;

}

//args[0] represents the directory path to scan

DirectoryInfo di = new DirectoryInfo(args[0]);

if (di.Exists)

{

Console.Write("Scanning for Word Open XML files under {0}", di.FullName);

Console.WriteLine();

GetAllFiles(di.FullName);

Console.WriteLine();

Console.WriteLine("Found {0} Word Open XML files...", files.Count);

...

}

else

{

Console.WriteLine("Path is incorrect or cannot be accessed. Try again or

specify another path.");

Console.WriteLine();

ShowHelp();

Console.WriteLine();

}

Step 2 – Open Word documents with the Open XML SDK

Now that we have all the Word files to scan, our next step is to open each of these documents with the Open XML SDK. The Open XML SDK should be able to handle most Word Open XML files. However, there are occasions where the Word document may have issues that prevent it from being opened with the SDK. For example, IRM documents cannot be opened with the Open XML SDK. To ensure our solution continues to function despite these types of issues we can simply wrap the SDK Open method with a try and catch. Any errors detected will be reported in the errors list. Here is the code snippet to accomplish this task:

static void Main(string[] args)

{

...

int fCount = 0;

bool addFinalLine = false;

//Go through all files found

foreach (string file in files)

{

if (fCount++ % 100 == 0)

{

if (addFinalLine)

{

Console.WriteLine();

}

Console.Write("Scanning files [{0}/{1}]", fCount, files.Count);

addFinalLine = true;

}

else if (fCount % 5 == 0)

{

Console.Write(".");

addFinalLine = true;

}

string thisFile = string.Empty;

try

{

using (WordprocessingDocument myDoc = WordprocessingDocument.Open(file,

false))

{

//Get all parts within the package

//Count occurrences of custom XML markup

...

}

catch (Exception e)

{

errors.Add(file, e.Message.Replace("\n", string.Empty));

}

...

}

Step 3 – Get all parts within a document

At this point we have the file opened with the Open XML SDK. The next step is to get all the parts within the package. For this task we are going to leverage some source code from Eric White's post on how to create a list of all parts in an Open XML document. Essentially we are going to use two methods GetAllParts and AddPart to recursively find all XML based parts within a package. Here is the code snippet necessary to accomplish this task:

static void Main(string[] args)

{

...

using (WordprocessingDocument myDoc =

WordprocessingDocument.Open(file.FullName, false))

{

//Get all parts within the package

List<OpenXmlPart> parts = GetAllParts(myDoc);

...

}

...

}

//This method is used to recursively find all parts within a package

static void AddPart(HashSet<OpenXmlPart> partList, OpenXmlPart part)

{

if (partList.Contains(part))

return;

//only add parts that are xml based

if (part.ContentType.EndsWith("+xml"))

partList.Add(part);

foreach (IdPartPair p in part.Parts)

AddPart(partList, p.OpenXmlPart);

}

//This method is used to recursively find all parts within a package

static List<OpenXmlPart> GetAllParts(WordprocessingDocument doc)

{

HashSet<OpenXmlPart> partList = new HashSet<OpenXmlPart>();

foreach (IdPartPair p in doc.Parts)

AddPart(partList, p.OpenXmlPart);

return partList.ToList();

}

Step 4 – Count the occurrences of Custom XML markup

Now that we have all the XML related parts contained within our Word document, the next step is to scan each of those parts for Custom XML markup. This task should be pretty easy with the Open XML SDK. All files with detected custom XML markup will be reported in the results list. Here is the code snippet necessary to accomplish this task:

// Files with detected custom XML markup will be added to this list

private static Dictionary<string, object> results = new Dictionary<string, object>();

static void Main(string[] args)

{

...

using (WordprocessingDocument myDoc =

WordprocessingDocument.Open(file.FullName, false))

{

int count = 0;

//Get all parts within the package

List<OpenXmlPart> parts = GetAllParts(myDoc);

foreach (OpenXmlPart part in parts)

{

count += NumberOccurrencesCustomXMLMarkup(part);

}

if (count > 0)

{

results.Add(file, count);

}

...

}

//Count all instances of custom XML markup within a given part

static int NumberOccurrencesCustomXMLMarkup(OpenXmlPart part)

{

int count = 0;

if ((part != null) && (part.RootElement != null))

{

count += part.RootElement.Descendants<CustomXmlBlock>().Count();

count += part.RootElement.Descendants<CustomXmlCell>().Count();

count += part.RootElement.Descendants<CustomXmlRow>().Count();

count += part.RootElement.Descendants<CustomXmlRuby>().Count();

count += part.RootElement.Descendants<CustomXmlRun>().Count();

}

return count;

}

The NumberOccurrencesCustomXMLMarkup method simply looks for the following Custom XML Markup related SDK objects:

CustomXmlBlock
CustomXmlCell
CustomXmlRow
CustomXmlRuby
CustomXmlRun

Pretty easy stuff!

Step 5 – Reporting the results

The last step in the solution is to report the results as a text based log file. Here is the code snippet to accomplish this task:

static void Main(string[] args)

{

...

foreach (string file in files)

{

...

}

if (addFinalLine)

{

Console.WriteLine();

}

File.Delete("output.log");

Console.WriteLine("Writing results file 'output.log'...");

//Output results to log file

using (StreamWriter sw = new StreamWriter("output.log"))

{

sw.WriteLine("Scanned folder and sub-folders: {0}", di.FullName);

sw.WriteLine();

sw.WriteLine("Files with Custom XML Markup: [{0}/{1}]", results.Count, files.Count);

if (results.Count != 0)

{

sw.WriteLine("File Name\tCustom XML Markup References");

foreach (string filename in results.Keys)

{

sw.WriteLine("{0}\t{1}", filename, results[filename]);

}

if (errors.Count != 0)

{

sw.WriteLine();

sw.WriteLine("Errors reported: [{0}]", errors.Count);

sw.WriteLine("File Name\tError message");

foreach (string filename in errors.Keys)

{

sw.WriteLine("{0}\t{1}", filename, errors[filename]);

}

Console.WriteLine();

Console.WriteLine("Scan completed.");

Console.WriteLine();

Console.WriteLine("Files with Custom XML Markup: [{0}/{1}]", results.Count,

files.Count);

Console.WriteLine("Errors reported: [{0}]", errors.Count);

Console.WriteLine();

Console.WriteLine("See 'output.log' for more details.");

Console.WriteLine();

Console.WriteLine("Press any key to continue...");

Console.ReadKey();

...

}

End Result

Running this code on a directory we end up with a tab delimited log file that shows all the files that contain Custom XML markup. Here is a screenshot of how the log file looks like when opened in Microsoft Excel:

Hopefully this solution shows you how easy it is to interrogate an Open XML file with the Open XML SDK.

Brian Jones + Zeyad Rajabi

Document Assembly: Merging Excel, PowerPoint, and Word Content Together

I'm Brian Jones — Mon, 04 Jan 2010 16:49:00 GMT

Document assembly seems to be a hot topic these days especially when combined with the power of SharePoint. Today, I want to show you a pretty rich document assembly solution that is able to take multiple Word, Excel and PowerPoint documents and merge them all together to form a final Word document. I showed this solution at both PDC and SPC, so I am going to take the opportunity in this blog to discuss some of the details around the solution.

If you want to jump straight into the code, feel free to download the solution here.

Scenario

Imagine a scenario where I work for a company that analyzes stocks and generates reports for every company/stock analyzed. These reports are typically quite rich and usually involve more than one person contributing to the content. Content is separated out into multiple Word, Excel, and PowerPoint documents where each document is assigned to an individual. Once all the content has been written, the content is all assembled into a final report as a Word document. My company has asked me to write a solution that will be able to merge all these documents programmatically.

Solution

Before I get into the details of my solution, I want to talk about a brand new feature of SharePoint 2010, called Document Sets, which I will leverage to help solve this problem. Document Sets gives users a new way to manage a collection of documents as a single object. Think of this feature as allowing for a binder of related content.

In the case of this solution, I have defined a custom Document Set as having a set of files (six in my case) that correspond to the various components of the final analysis report. Here is a screenshot of a Document Set for a company called Contoso:

Using Document Sets and given the scenario I talked about above we will need to take the following actions:

Create a set of documents that represent the Document Set for my solution. One of the documents will be a Word template that represents the look and feel of the final report
Create and add a custom Document Set to a SharePoint library
Create a web part for the SharePoint library that has a button labeled "Assemble Documents", which will be used to merge all the content together into a final document. This web part will be added to the Document Set library
Using the Open XML SDK, open the template document from the Document Set and look for all content controls
For every content control found, find the corresponding document content in the library and merge that content into the final document
Once the document assembly is complete, offer the user the ability to open or save the report

Step 1 – Creating the Right Template

Setting up the right template makes all the difference when creating Office document solutions. In the case of my solution I have a Document Set with six files, where one of the files is my template file. All these documents are empty except for the template document. As in many of my previous posts, the template document represents the final look of the final report. I will leverage content controls within my template to specify semantic regions within my document to be used to merge content together. The title of my content controls will represent the type of content to be merged. For example, a Word document, a chart from a spreadsheet, a table from a spreadsheet, or a SmartArt graphic from a presentation. The content of my content control will represent the name of the file that contains the content to be merged. For example, here is a screenshot of my template document highlighting one of my content controls labeled "Word:Document" with content set to "Introduction":

This content control will represent the region where I will merge the Introduction Word document into my template file. For sake of completeness the other content controls are labeled "Spreadsheet:Chart", "Spreadsheet:Table", and "Presentation:SmartArt".

You can find the files that represent my Document Set here.

Step 2 – Setting Up Document Sets in SharePoint 2010

Perform the following steps to enable Document Sets on your SharePoint 2010 site:

Site Actions | Site Settings (top level site settings)
Site Collections Administration | Site collection features
Activate Document Sets feature, if not already activated
Go to the Document Library where you want to add the Document Sets feature
Site Actions | Site Settings
Galleries | Site content types
Create
Set "Select parent content type from:" to "Document Set Content Types"
Specify a name and click OK
In the Site Content Types list click on the link to the created Document Set
Document Set settings
Specify the default content (in my case I added six documents to the set) and click OK
Go to the Document Library where you want to add the Document Sets feature
Library Tools | Library | Library Settings
Advanced Settings
Choose "Yes" for "Allow management of content types?" and click OK
Content Types | Add from existing site content types
Add your custom content type for the Document Set

At this point you should have a library set up with a Document Set content type.

Step 3 – Create a Web Part with an Assemble Documents Command

In order to make this solution usable, we need to add a command within our document library that allows users to merge documents together. The easiest way to accomplish this task is to create a Web Part within Visual Studio 2010. Once you've created the Web Part modify the CreateChildControls

method as follows:

protected override void CreateChildControls()

{

Control control = this.Page.LoadControl(_ascxPath);

Controls.Add(control);

base.CreateChildControls();

Button btnSubmit = new Button();

btnSubmit.Text = "Assemble Documents";

btnSubmit.Click += new EventHandler(btnSubmit_Click);

Controls.Add(btnSubmit);

}

This method will add a new button control where we can add our logic to merge the documents contained within a given Document Set (the merge code will be called from void btnSubmit_Click(object sender, EventArgs e).

Once we've created this web part, the next step is to add the button to our Document Set. The easiest way to accomplish this task is to use SharePoint Designer 2010. Perform the following steps to add your custom web part to the Document Set library:

Open your SharePoint site within SharePoint Designer 2010
Navigate to your document library that contains the Document Set
All Files | Custom document library name | Forms | Document Set name | XXXXhomepage.aspx
Click on the bottom of WebPartZone_CenterMain
Insert | Web Part | select your custom web part created from Visual Studio
Save and Close

At this point you should see an "Assemble Documents" command show up for any created Document Set:

Step 4 – Finding the Content Controls

Finding content controls within a document involves the following steps:

Open template document from the SharePoint site
Load document into memory
Open document (from memory) with the Open XML SDK
Loop through all content controls within the document
For every content control figure out the title
Based on the title perform a particular action

The following code accomplishes the steps outlined above:

void btnSubmit_Click(object sender, EventArgs e)

{

SPFolder folder = SPContext.Current.ListItem.Folder;

char[] splitter = { '/' };

string[] folderName = folder.Name.Split(splitter);

string filePrefix = @"Stock Analysis Demo/" + folderName[0] + "/" + folderName[0];

SPFile template = folder.Files[filePrefix + " - Template.docx"];

SPFile file;

byte[] byteArray = template.OpenBinary();

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

using (WordprocessingDocument myDoc = WordprocessingDocument.Open(mem, true))

{

MainDocumentPart mainPart = myDoc.MainDocumentPart;

foreach (Word.SdtElement sdt in mainPart.Document

.Descendants<Word.SdtElement>().ToList())

{

Word.Alias alias = sdt.Descendants<Word.Alias>()

.FirstOrDefault();

if (alias != null)

{

string sdtTitle = alias.Val.Value;

if (sdtTitle == "Spreadsheet:Table")

{

file = folder.Files[filePrefix + " - " +

sdt.InnerText + ".xlsx"];

ImportTableFromSpreadsheet(mainPart, sdt, file);

}

else if (sdtTitle == "Spreadsheet:Chart")

{

file = folder.Files[filePrefix + " - " +

sdt.InnerText + ".xlsx"];

ImportChartFromSpreadsheet(mainPart, sdt, file);

}

else if (sdtTitle == "Presentation:SmartArt")

{

file = folder.Files[filePrefix + " - " +

sdt.InnerText + ".pptx"];

ImportSmartArtFromPowerPoint(mainPart, sdt, file);

}

else if (sdtTitle == "Word:Document")

{

file = folder.Files[filePrefix + " - " +

sdt.InnerText + ".docx"];

AddAltChunk(mainPart, sdt, file);

}

...

}

Step 5 – Merging Content Together

In this solution, there are four types of content to be merged:

Word documents
SmartArt graphics from presentations
Charts from spreadsheets
Tables of data from spreadsheets

Let's talk about each of these content types.

Step 5a – Merging Word Documents Together

By far the easiest way to assemble Word documents together is to take advantage of altChunks, which I have already blogged about in the past. In any case, here is the code necessary to merge documents together on SharePoint:

protected int id = 1;

void AddAltChunk(MainDocumentPart mainPart, Word.SdtElement sdt, SPFile filename)

{

string altChunkId = "AltChunkId" + id;

id++;

byte[] byteArray = filename.OpenBinary();

AlternativeFormatImportPart chunk = mainPart.AddAlternativeFormatImportPart(

AlternativeFormatImportPartType.WordprocessingML, altChunkId);

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

mem.Seek(0, SeekOrigin.Begin);

chunk.FeedData(mem);

}

Word.AltChunk altChunk = new Word.AltChunk();

altChunk.Id = altChunkId;

//Replace content control with altChunk information

OpenXmlElement parent = sdt.Parent;

parent.InsertAfter(altChunk, sdt);

sdt.Remove();

}

Pretty easy stuff!

Step 5b – Import SmartArt Graphics from PowerPoint to Word

Again, I've already blogged about importing SmartArt from PowerPoint to Word. Here is the code necessary to accomplish this task:

void ImportSmartArtFromPowerPoint(MainDocumentPart mainPart, Word.SdtElement sdt, SPFile filename)

{

string docLayoutPartId = "";

string docDataPartId = "";

string docColorsPartId = "";

string docStylePartId = "";

byte[] byteArray = filename.OpenBinary();

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

using (PresentationDocument myPres = PresentationDocument.Open(mem, true))

{

PresentationPart presPart = myPres.PresentationPart;

//Get the slide that contains the SmartArt graphic

SlidePart slide = (SlidePart)presPart.GetPartById("rId3");

//Get all the appropriate parts associated with the SmartArt

DiagramLayoutDefinitionPart layoutPart =

slide.DiagramLayoutDefinitionParts.First();

DiagramDataPart dataPart = slide.DiagramDataParts.First();

DiagramColorsPart colorsPart = slide.DiagramColorsParts.First();

DiagramStylePart stylePart = slide.DiagramStyleParts.First();

//Get some of the appropriate properties off the SmartArt graphic

PPT.GraphicFrame graphicFrame =

slide.Slide.Descendants<PPT.GraphicFrame>().First();

PPT.NonVisualDrawingProperties drawingPr = graphicFrame

.Descendants<PPT.NonVisualDrawingProperties>().First();

Draw.Extents extents =

graphicFrame.Descendants<Draw.Extents>().First();

//Import SmartArt into Word document

//Add the SmartArt parts to the Word document

DiagramLayoutDefinitionPart docLayoutPart =

mainPart.AddPart<DiagramLayoutDefinitionPart>(layoutPart);

DiagramDataPart docDataPart =

mainPart.AddPart<DiagramDataPart>(dataPart);

DiagramColorsPart docColorsPart =

mainPart.AddPart<DiagramColorsPart>(colorsPart);

DiagramStylePart docStylePart =

mainPart.AddPart<DiagramStylePart>(stylePart);

//Get all the relationship ids of the added parts

docLayoutPartId = mainPart.GetIdOfPart(docLayoutPart);

docDataPartId = mainPart.GetIdOfPart(docDataPart);

docColorsPartId = mainPart.GetIdOfPart(docColorsPart);

docStylePartId = mainPart.GetIdOfPart(docStylePart);

//Use the document reflector to figure out how to add a SmartArt

//graphic to Word

//Change attribute values based on specifics related to the SmartArt

Word.Paragraph p = new Word.Paragraph(

new Word.Run(

new Word.Drawing(

new WP.Inline(

new WP.Extent() { Cx = extents.Cx, Cy = extents.Cy },

new WP.EffectExtent()

{

LeftEdge = 0L,

TopEdge = 0L,

RightEdge = 0L,

BottomEdge = 0L

new WP.DocProperties()

{

Id = drawingPr.Id,

Name = drawingPr.Name

new WP.NonVisualGraphicFrameDrawingProperties(),

new Draw.Graphic(

new Draw.GraphicData(

new Dgm.RelationshipIds()

{

DataPart = docDataPartId,

LayoutPart = docLayoutPartId,

StylePart = docStylePartId,

ColorPart = docColorsPartId

}

) { Uri = "http://schemas.openxmlformats.org/drawingml/2006/diagram" })

)

{

DistanceFromTop = (UInt32Value)0U,

DistanceFromBottom = (UInt32Value)0U,

DistanceFromLeft = (UInt32Value)0U,

DistanceFromRight = (UInt32Value)0U

})));

//Swap out the content control for the SmartArt

OpenXmlElement parent = sdt.Parent;

parent.InsertAfter(p, sdt);

sdt.Remove();

}

The only difference between the code above and the code I showed you in my previous post is that the code above works with files on SharePoint.

Step 5c – Import Charts from Excel to Word

Again, I am going to leverage the same code I showed you in a previous post called importing charts from spreadsheets to Word documents. Here is the same code modified to work with files that exist within SharePoint:

void ImportChartFromSpreadsheet(MainDocumentPart mainPart, Word.SdtElement sdt, SPFile spreadsheetFileName)

{

//create a new paragraph that has an inline drawing object

Word.Paragraph p = new Word.Paragraph();

Word.Run r = new Word.Run();

p.Append(r);

Word.Drawing drawing = new Word.Drawing();

r.Append(drawing);

//These dimensions work perfectly for my template document

WP.Inline inline = new WP.Inline(

new WP.Extent() { Cx = 5486400, Cy = 3200400 });

byte[] byteArray = spreadsheetFileName.OpenBinary();

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

//Open Excel spreadsheet

using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(mem, true))

{

//Get all the appropriate parts

WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;

WorksheetPart worksheetPart = XLGetWorksheetPartByName(mySpreadsheet,

"Sheet2");

DrawingsPart drawingPart = worksheetPart.DrawingsPart;

ChartPart chartPart = (ChartPart)drawingPart.GetPartById("rId1");

//Clone the chart part and add it to my Word document

ChartPart importedChartPart = mainPart.AddPart<ChartPart>(chartPart);

string relId = mainPart.GetIdOfPart(importedChartPart);

//The frame element contains information for the chart

GraphicFrame frame =

drawingPart.WorksheetDrawing.Descendants<GraphicFrame>().First();

string chartName = frame.NonVisualGraphicFrameProperties.NonVisualDrawingProperties.Name;

//Clone this node so we can add it to my Word document

Draw.Graphic clonedGraphic = (Draw.Graphic)frame.Graphic.CloneNode(true);

ChartReference c =

clonedGraphic.GraphicData.GetFirstChild<ChartReference>();

c.Id = relId;

//Give the chart a unique id and name

WP.DocProperties docPr = new WP.DocProperties();

docPr.Name = chartName;

docPr.Id = GetMaxDocPrId(mainPart) + 1;

//add the chart data to the inline drawing object

inline.Append(docPr, clonedGraphic);

drawing.Append(inline);

}

OpenXmlElement parent = sdt.Parent;

parent.InsertAfter(p, sdt);

sdt.Remove();

}

WorksheetPart XLGetWorksheetPartByName(SpreadsheetDocument document, string sheetName)

{

WorkbookPart wbPart = document.WorkbookPart;

// Find the sheet with the supplied name, and then use that Sheet object

// to retrieve a reference to the appropriate worksheet.

Excel.Sheet theSheet = wbPart.Workbook.Descendants<Excel.Sheet>()

.Where(s => s.Name == sheetName).FirstOrDefault();

if (theSheet == null)

{

throw new ArgumentException("sheetName");

}

return (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

}

uint GetMaxDocPrId(MainDocumentPart mainPart)

{

uint max = 1;

//Get max id value of docPr elements

foreach (WP.DocProperties docPr in mainPart.Document.Descendants<WP.DocProperties>())

{

uint id = docPr.Id;

if (id > max)

max = id;

}

return max;

}

Step 5d – Import Table of Data from Excel to Word

In a previous post I showed you how to import a table from Word to Excel. Today, I will show you how to do the reverse. Here are the steps necessary to accomplish this task:

Create a Word table with the Open XML SDK that will hold the data from Excel
Open the Excel file with the Open XML SDK
Retrieve the specific sheet that contains the table of data
In the found sheet, locate the SheetData object, which contains the table of data
For every row in the SheetData object, retrieve all the cell data
Once all data in the row is retrieved, create a Word row with the same data
Append the created Word row to the Word table

To help with the tasks listed above I am going to take advantage of some of the Open XML SDK code snippets that were published. Here is the code necessary to accomplish the tasks outlined above:

void ImportTableFromSpreadsheet(MainDocumentPart mainPart, Word.SdtElement sdt, SPFile spreadsheetFileName)

{

ArrayList cellText = new ArrayList();

//create Word table

Word.Table tbl = new Word.Table();

Word.TableProperties tblPr = new Word.TableProperties();

Word.TableStyle tblStyle = new Word.TableStyle();

tblStyle.Val = "LightShading-Accent1";

tblPr.AppendChild(tblStyle);

Word.TableWidth tblW = new Word.TableWidth();

tblW.Width = 5000;

tblW.Type = Word.TableWidthUnitValues.Pct;

tblPr.Append(tblW);

tbl.AppendChild(tblPr);

byte[] byteArray = spreadsheetFileName.OpenBinary();

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(mem, true))

{

WorkbookPart workbookPart = mySpreadsheet.WorkbookPart;

WorksheetPart worksheetPart = XLGetWorksheetPartByName(mySpreadsheet, "Sheet1");

Excel.SheetData sheetData =

worksheetPart.Worksheet.GetFirstChild<Excel.SheetData>();

foreach (Excel.Row r in sheetData)

{

foreach (Excel.Cell c in r)

{

cellText.Add(XLGetCellValue(c, workbookPart));

}

Word.TableRow tr = CreateRow(cellText);

tbl.Append(tr);

cellText = new ArrayList();

}

//Swap out the content control for the SmartArt

OpenXmlElement parent = sdt.Parent;

parent.InsertAfter(tbl, sdt);

sdt.Remove();

}

Word.TableRow CreateRow(ArrayList cellText)

{

Word.TableRow tr = new Word.TableRow();

//create cells with simple text

foreach (string s in cellText)

{

Word.TableCell tc = new Word.TableCell();

Word.Paragraph p = new Word.Paragraph();

Word.Run r = new Word.Run();

Word.Text t = new Word.Text(s);

r.AppendChild(t);

p.AppendChild(r);

tc.AppendChild(p);

tr.AppendChild(tc);

}

return tr;

}

// Get the value of a cell, given a file name, sheet name, and address name.

string XLGetCellValue(Excel.Cell c, WorkbookPart wbPart)

{

string value = null;

// If the cell doesn't exist, return an empty string:

if (c != null)

{

value = c.InnerText;

// If the cell represents an integer number, you're done.

// For dates, this code returns the serialized value that

// represents the date. The code handles strings and booleans

// individually. For shared strings, the code looks up the corresponding

// value in the shared string table. For booleans, the code converts

// the value into the words TRUE or FALSE.

if (c.DataType != null)

{

switch (c.DataType.Value)

{

case Excel.CellValues.SharedString:

// For shared strings, look up the value in the shared strings table.

var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

// If the shared string table is missing, something's wrong.

// Just return the index that you found in the cell.

// Otherwise, look up the correct text in the table.

if (stringTable != null)

{

value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;

}

break;

case Excel.CellValues.Boolean:

switch (value)

{

case "0":

value = "FALSE";

break;

default:

value = "TRUE";

break;

}

break;

}

return value;

}

Step 6 – Offer Assembled Document to User

The last step is to offer the assembled document to the user with an Open/Save/Cancel dialog as follows:

Here is the code snippet to create this dialog based on a document that is in memory:

void btnSubmit_Click(object sender, EventArgs e)

{

...

HttpResponse resp = HttpContext.Current.Response;

resp.ClearContent();

resp.ClearHeaders();

resp.AddHeader("Content-Disposition", "attachment; filename=Assembled Document.docx");

resp.ContentEncoding = System.Text.Encoding.UTF8;

resp.OutputStream.Write(mem.ToArray(), 0, (int)mem.Length);

resp.Flush();

resp.Close();

resp.End();

}

End Result

Putting everything together and running the code we end up with a document that contains all the content contained within our library merged into a final report:

Pretty cool stuff!

Zeyad Rajabi

Announcing the Release of the December 2009 CTP for the Open XML SDK

I'm Brian Jones — Mon, 14 Dec 2009 23:09:00 GMT

I'm really happy to announce the 4^th CTP for the Open XML SDK 2.0 for Microsoft Office! There were four major improvements we made to the SDK:

Full support for the Office 2010 Open XML formats
Office 2010 schema and semantic level validation
General improvements based on a recent Open XML SDK usability study
Open XML SDK tools improvement

Full Support for the Office 2010 Open XML Formats

With the latest CTP you are able to create, edit, and consume Office generated Open XML formats for either Office 2007 or Office 2010. Looking back at the original Open XML SDK architecture diagram I showed you when we first announced the Open XML SDK, we have extended the base level layer to include Office 2010 support:

What does that mean? Well, let me show you some examples.

The Open XML Packaging API component allows you to add/remove parts within an Open XML package. This component functions by providing you strongly typed classes for every part within a package. In Office 2010 we've added new parts to the package in order to support some of our new features. With this CTP you are able to deal with these new parts with classes. For example, Word 2010 added a new part called stylesWithEffects. This part is used to help round trip styles that are based on Word's new text effects feature.

The Open XML Low Level DOM component allows you to create, edit, or consume xml contained in parts contained within an Open XML package. Like the packaging API component, this component functions by providing you with strongly typed classes for every element supported within the xml. For example, imagine we have the following text in a Word document, which uses the new text effects feature glow:

This text is represented in xml and the Open XML SDK as the following:

XML

Open XML SDK Code

using W14 = DocumentFormat.OpenXml.Office2010.Word;

...

Run run2 = new Run();

RunProperties runProperties1 = new RunProperties();

W14.Glow glow1 = new W14.Glow() { GlowRadius = 228600L };

W14.SchemeColor schemeColor1 = new W14.SchemeColor()

{ Val = W14.SchemeColorValues.ExtraSchemeColor5 };

W14.Alpha alpha1 = new W14.Alpha() { Val = 60000 };

W14.SaturationModulation saturationModulation1 =

new W14.SaturationModulation() { Val = 175000 };

schemeColor1.Append(alpha1);

schemeColor1.Append(saturationModulation1);

glow1.Append(schemeColor1);

runProperties1.Append(glow1);

Text text2 = new Text();

text2.Text = "text effects";

...

As you can see from the code snippet above, leveraging the Office 2010 Open XML functionality of the SDK is as simple as including the appropriate set of namespace references.

Office 2010 Schema and Semantic Level Validation

In a previous post I talked about how to find Open XML errors with the Open XML SDK validation functionality. In that post, I talked about finding both schema and semantic (constraints defined in the prose of the documentation that are not represented in schema markup) validation errors. With the latest SDK CTP you are now able to differentiate between errors specific to Office 2007 or Office 2010. The code to validate a file is pretty much the same as the code I showed in the previous post mentioned. The major difference is that we moved the validation functionality to be under its own namespace reference: "using DocumentFormat.OpenXml.Validation". We moved this functionality to its own namespace to improve discoverability of the feature. The OpenXmlValidator class now takes in an enumeration called FileFormatVersions that allows you to specify either Office 2007 or 2010:

The validation functionality also has the ability to automatically deal with extensibility markup, like alternate content blocks, as defined by the Open XML standard.

General Open XML SDK Improvements

We recently conducted a usability study for the Open XML SDK where we asked participants to complete a set of tasks. Based on the results of the study we found that users had issues around the complexity of the Open XML SDK Open methods. In the August 2009 CTP of the SDK, each of the classes WordprocessingDocument, SpreadsheetDocument and PresentationDocument had nine overloaded Open methods. For example, let's look at the WordprocessingDocument class as an example:

public static WordprocessingDocument Open(Package package);
public static WordprocessingDocument Open(Package package, bool autoSave);
public static WordprocessingDocument Open(Stream stream, bool isEditable);
public static WordprocessingDocument Open(string path, bool isEditable);
public static WordprocessingDocument Open(Package package, MCMode mode, bool processMCInWholePackage);
public static WordprocessingDocument Open(Stream stream, bool isEditable, bool autoSave);
public static WordprocessingDocument Open(string path, bool isEditable, bool autoSave);
public static WordprocessingDocument Open(Stream stream, bool isEditable, MCMode mode, bool processMCInWholePackage);
public static WordprocessingDocument Open(string path, bool isEditable, MCMode mode, bool processMCInWholePackage);

Now with the latest CTP, the number of overloaded methods for Open has been reduced to six methods:

public static WordprocessingDocument Open(Package package);
public static WordprocessingDocument Open(Package package, OpenSettings openSettings);
public static WordprocessingDocument Open(Stream stream, bool isEditable);
public static WordprocessingDocument Open(string path, bool isEditable);
public static WordprocessingDocument Open(Stream stream, bool isEditable, OpenSettings openSettings);
public static WordprocessingDocument Open(string path, bool isEditable, OpenSettings openSettings);

Advanced open settings are now part of a new class called OpenSettings. This class allows you to pre-process documents according to markup compatibility, auto save files, or caps the open method to deal with a specific number of characters within a part. Hopefully this change will make it easier to deal with the Open method.

Open XML SDK Tool Improvements

The last major improvement I want to call out is around the free Open XML SDK tool that comes with the SDK. In the August 2009 CTP, the Open XML SDK shipped with three separate tools:

Document Reflector
Open XML Class Explorer
Open XML Diff

Based on feedback we have separated the download of the tools from the SDK dll. In other words, the Open XML SDK download page now has two download links: one for the SDK dll and one for a new consolidated Open XML SDK tool. As of the December 2009 CTP, the Open XML SDK tools are consolidated into one tool. In addition we've added some new functionality to the SDK. Here is a video that shows you an overview of the new tool:

The new tool adds the following functionality:

Documentation for Office 2010 Open XML formats
Validation functionality. This feature allows you to validate Open XML files according to Office 2007 or Office 2010
New reflection feature that combines reflection with the Open XML Diff feature. This new feature allows you to automatically generate SDK code that transforms one document into another document

Let us know what you think of the new tool. I think of the new tool as the Open XML version of Macro Recording.

More Feedback Always Welcome

Please continue to send us your feedback, either on this blog or at our Microsoft Connect site for the Open XML SDK. We look forward to hearing from you.

Zeyad Rajabi

Extending the Reach of Excel Services with the Open XML SDK

I'm Brian Jones — Thu, 03 Dec 2009 21:08:00 GMT

Note: Updated post to point to a YouTube video that is shorter than 10 minutes

In Brian's last blog he talked about Open XML and Office Services as a one-two punch to complete many types of solutions. I am going to start writing some posts that showcase these two technologies working together. Excel is a great tool for connecting to multiple types of data sources, like databases or XML files. Unfortunately, Excel has no concept of connecting to other types of Office documents, like Word files. This fact is important because information workers are pumping out more and more Office documents that contain lots of relevant business data. These documents are becoming just as important as data contained within databases. In today's post, I am going to show you how to extend the reach of Excel Services and hosted charts to gather data from within a library of Word documents.

If you want to jump straight into the code, feel free to download this solution here.

Scenario

As a Program Manager (PM) in Office, one of our tasks is to write specifications for features that we are designing for the product. These specifications exist in different stages depending on how far along we are in the design. For example, a stage 0 specification is essentially just a placeholder document with no content. A stage 5 specification is a document that contains the full design for a feature and is ready to be coded by developers. Along the way we inevitably run into open issues that must be addressed before the feature can be coded properly. In order to help us track open issues within a document we included a custom style called "Issue" as part of our specification template. This style applies a yellowish background color to a paragraph so that the paragraph easily sticks out. Here is a screenshot of what this style looks like:

Whenever a PM encounters an open issue he/she applies this custom style to the content within the specification. Wouldn't it be great if you can generate a dynamic chart that shows the number of open issues for every PM per given specification stage? This chart would represent the relative health of a particular specification library.

Solution

The scenario I listed above talks about interrogating a repository of Word documents and counting the number of open issues within each of the documents. In order to accomplish this scenario we will need to take the following actions:

Create a template Excel spreadsheet that represents the look and feel of the chart we want to display
Add an Excel Services web part to a SharePoint site that displays a chart hosted in our template Excel spreadsheet
For every Word document in a SharePoint library find the following information:
1. Program Manager name assigned to the specification
2. Specification stage
3. The number of open issues within the specification
When all the data has been gathered, inject the data into the appropriate location in the template Excel spreadsheet
Create a web part button that allows users to refresh the chart (the refresh button will perform steps 3 and 4)
1. I am going to skip on showing this part since the link to my solution includes source code that shows you how to do this task

The Template

For the sake of this post and the scenario described above I have created the following template Excel spreadsheet:

This spreadsheet contains only one sheet with a blank chart. The chart will display the number of open issues across all the program managers listed within a SharePoint library broken down by the specification status. The chart is given a specific name so that we can reference it within our Excel Services web part. Add this template document to a SharePoint library.

Creating an Excel Services Web Part in SharePoint

Note: I am going to show you how to create a web part within SharePoint 2010, but you should be able to do the same thing within SharePoint 2007 (since Excel Services is not new to 2010).

On a SharePoint site where you are an admin, click on the menu command with your name and select Personalize this Page option:

At this point you have the ability to change the look of the page. Click on the Add a Web Part link:

In the Categories section select Office Client Applications. In the Web Parts section select Excel Web Access:

Now we need to link our Excel Services web part to our template Excel spreadsheet. Click the link to open the tool pane, which allows you to reference the correct Excel spreadsheet. In the Excel Services web part properties pane add the following information:

Name of the referenced workbook
Name of the chart to be added in the Named Item field

At this point we have an Excel Services web part that shows the chart in our Excel spreadsheet template.

Gathering Data from a Library of Word Documents

The first step in this task is to iterate through all the Word documents in a given specification library. Here is a code snippet to accomplish this task:

SPWeb myWebSite = SPContext.Current.Web;

SPDocumentLibrary specLibrary = myWebSite

.Lists["Sample Spec Library"] as SPDocumentLibrary;

//Read all Word documents in the library

foreach (SPListItem item in specLibrary.Items)

{

byte[] byteArray = item.File.OpenBinary();

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

//Read the Word document into the Open XML SDK

using (WordprocessingDocument myDoc =

WordprocessingDocument.Open(mem, true))

{

//Do something

}

Now that we have access to the Word documents we need to count the number of paragraphs that have the style "Issue" applied. I have already written a post on how to accomplish this task. Check out retrieving Word content based on styles. I am going to reuse the same methods I talked about in that previous post for this scenario. I will reuse the method called GetParagraphListByStyle, which returns a list of paragraphs given a main document part and a specific style name.

Gathering the PM associated with the specification and the spec status is a matter of retrieving content within a specific content control. In my solution I am going to store the PM name, the number of open issues in the specification and the status of the specification within a hash table. Here is the code snippet I used to store the information:

Hashtable specTable = new Hashtable();

//Read all Word documents in the library

foreach (SPListItem item in specLibrary.Items)

{

string pmName = "";

int numOpenIssues = 0;

int status = 0;

...

//Read the Word document into the Open XML SDK

using (WordprocessingDocument myDoc =

WordprocessingDocument.Open(mem, true))

{

MainDocumentPart mainPart = myDoc.MainDocumentPart;

//Find out the program manager name associated with the spec

Word.DataBinding binding = mainPart.Document

.Descendants<Word.DataBinding>()

.Where(d => d.XPath.Value.Contains("PM2"))

.First();

pmName = binding.Ancestors<Word.SdtCell>().First().InnerText;

//Get all paragraphs in the spec that have a style

//applied on the paragraphs called "Issue"

IEnumerable<Word.Paragraph> paraIssueList =

GetParagraphListByStyle(mainPart, "Issue");

numOpenIssues = paraIssueList.Count();

//Find out the spec status (either 0, 1, 2, 3, 4, or 5)

IEnumerable<Word.Paragraph> paraSpecStatusList =

GetParagraphListByStyle(mainPart, "SpecStatus");

foreach (Word.Paragraph p in paraSpecStatusList)

{

string specStatus = p.InnerText;

if (specStatus.Contains("0"))

status = 0;

else if (specStatus.Contains("1"))

status = 1;

else if (specStatus.Contains("2"))

status = 2;

else if (specStatus.Contains("3"))

status = 3;

else if (specStatus.Contains("4"))

status = 4;

else if (specStatus.Contains("5"))

status = 5;

}

//Once we find all the data lets add it to a hashtable

//We will use this data to build up our chart

if (specTable.Contains(pmName))

{

int[] specStatusValues = (int[])specTable[pmName];

specStatusValues[status] += numOpenIssues;

}

else

{

int[] specStatusValues = { 0, 0, 0, 0, 0, 0 };

specStatusValues[status] += numOpenIssues;

specTable.Add(pmName, specStatusValues);

}

Notice that the specification status can be one of six values, from Stage 0 to Stage 5.

Adding Data to my Spreadsheet

At this point we have all the data from our document library. All that is left is to insert this data into our Excel spreadsheet. This part of the solution is analogous to my previous post on document assembly for SpreadsheetML. We are simply going to create new rows and cells in our spreadsheet for every piece of data within our hash table. Notice that we are adding two types of cells: value based cells and text based cells. These two types of cells are stored differently in Excel. Here is the code snippet used to accomplish this task:

void AddDataToExcel(Hashtable specTable)

{

SPWeb myWebsite = SPContext.Current.Web;

SPDocumentLibrary templateLibrary = myWebsite

.Lists["Excel Template"] as SPDocumentLibrary;

SPFile template = myWebsite

.GetFile(@"http://wordteamserver/excelservices/Excel%20Template/template.xlsx");

template.CopyTo(outputFileName, true);

SPFile output = myWebsite.GetFile(template);

byte[] byteArray = output.OpenBinary();

//Open the output workbook so we can add data to it

using (MemoryStream mem = new MemoryStream())

{

mem.Write(byteArray, 0, (int)byteArray.Length);

using (SpreadsheetDocument myWorkbook =

SpreadsheetDocument.Open(mem, true))

{

//Get the first sheet in the workbook

WorkbookPart workbookPart = myWorkbook.WorkbookPart;

WorksheetPart worksheetPart =

GetWorkSheetPart(workbookPart, "Sheet1");

//Sheet data contains all the data in the rows/cells

SpreadSheet.SheetData sheetData =

worksheetPart.Worksheet.GetFirstChild<SpreadSheet.SheetData>();

//Go through our spec hashtable so we can add the data to workbook

IDictionaryEnumerator enumerator = specTable.GetEnumerator();

while (enumerator.MoveNext())

{

string pmName = (string)enumerator.Key;

int[] specStatusValues = (int[])enumerator.Value;

//create a new row for each program manager

SpreadSheet.Row r = new SpreadSheet.Row();

//Add a cell to the row with the program manager name

//note that the name is a string

SpreadSheet.Cell firstCell = new SpreadSheet.Cell();

firstCell.DataType = SpreadSheet.CellValues.InlineString;

SpreadSheet.InlineString inlineString =

new SpreadSheet.InlineString();

SpreadSheet.Text t = new SpreadSheet.Text();

t.Text = pmName;

inlineString.Append(t);

firstCell.Append(inlineString);

r.Append(firstCell);

//Let's add values to the other cells

//specifically we will add the number of open issues

//across the different spec status values

for (int i = 0; i < 6; i++)

{

SpreadSheet.Cell sheetCell = new SpreadSheet.Cell();

SpreadSheet.CellValue v = new SpreadSheet.CellValue();

string textValue = specStatusValues[i].ToString();

v.Text = textValue;

sheetCell.Append(v);

r.Append(sheetCell);

}

sheetData.Append(r);

}

worksheetPart.Worksheet.Save();

//Once we have added all the values we need to update the

//chart ranges

FixChartData(worksheetPart, specTable);

}

//Add output file to library

SPFolder fldr = myWebsite.Folders["Excel Template"];

fldr.Files.Add(outputFileName, mem, true);

}

The GetWorkSheetPart method is the same method I wrote about in my previous post on how to copy a worksheet within a workbook.

Fix Chart Data

The last step in the solution is to fix the chart data. Again this code snippet is taken directly from my previous post on document assembly for SpreadsheetML. In any case, here is the code snippet used to accomplish this task:

//Fix the range of the chart data

void FixChartData(WorksheetPart worksheetPart, Hashtable specTable)

{

int totalCount = specTable.Count + 1;

ChartPart chartPart = worksheetPart.GetPartsOfType<DrawingsPart>()

.First().ChartParts.First();

foreach (Charts.Formula formula in chartPart.ChartSpace

.Descendants<Charts.Formula>())

{

//Range needs to be updated such that we reference the number of rows

if (formula.Text.Contains("$2"))

{

string s = formula.Text.Split('$')[1];

formula.Text += ":$" + s + "$" + totalCount;

}

chartPart.ChartSpace.Save();

}

End Result

Running this code I will end up with a chart that represents the number of open issues across a library of Word documents. Clicking Update Chart will ensure the chart reflects the data contained within the library.

I am excited about this solution because it shows how you can extend the reach of our services to accomplish other types of solutions. In this case, having Excel reflect data contained within Word documents.

Zeyad Rajabi

Open XML SDK + Word Automation Services Presentation at PDC

I'm Brian Jones — Mon, 23 Nov 2009 18:38:00 GMT

I just got back from PDC and am excited to share with you guys the presentation, deck, and code samples I showed at my session. I also want to thank everyone who attended my session. Here is my recorded session:

You can find my session deck here: http://ecn.channel9.msdn.com/o9/pdc09/ppt/PR09.pptx

You can find all my code samples for the demos I showed here: http://zeyadrajabi.members.winisp.net/sourcecode/PDC%20Open%20XML%20demos.zip

In Brian's last post, he gave a good overview of using the Open XML SDK and Office Services together. In the next couple of months I am going to start blogging more about scenarios and solutions that leverage both the SDK and Office Services to create rich Office document solutions.

Note: I am still waiting for my two sessions at SPC to get published. As soon as they do, I will share them out with you guys.

Zeyad Rajabi

Open XML SDK Sessions at the Professional Developers Conference (PDC)

I'm Brian Jones — Tue, 10 Nov 2009 23:53:00 GMT

I'm pretty excited, next week I'll be at PDC in Los Angeles giving another presentation on the Open XML SDK. I'll be at the conference from Nov 17-19, so if you're at PDC do come by and say hi. Here's the description and time of the session I'll be presenting:

Session Title

Time & Date

Session Code

Description

Document Assembly and Manipulation on Microsoft SharePoint Server 2010 Using Word Automation Services and Open XML

11/19/2009 11:30AM

@ 408B

PR09

Come learn about the Open XML SDK, which provides a set of .NET APIs to help developers create and manipulate documents in the Open XML Formats, and the Microsoft Office services available in SharePoint Server 2010. Hear how Word Automation Services and Excel Services can be used to build solutions for server environments without the need of the Office clients. Come and check out all the demos and free source code.

Let me know if there are specific demos you would like to see. Besides the presentation, you will be able to find me at the Office client booth during the week of PDC and at the "Ask the Experts" event. I'm looking forward to hearing feedback from you guys at the conference.

Zeyad Rajabi

Open XML and Office Services

I'm Brian Jones — Tue, 03 Nov 2009 20:16:00 GMT

If you didn't see the news up on the Word team blog announcing Word Automation Services, and you have any interest in server side conversion of .docx files into .pdf or .xps, you should definitely go take a look: http://blogs.msdn.com/microsoft_office_word/archive/2009/10/26/introducing-word-automation-services.aspx

Capturing Business Processes in Office

I see a lot of different types of solutions people build to make their workgroups run more accurately and efficiently. Many of the solutions are created in order to codify one or more pieces of a business process, and as you can imagine, Office plays a huge role in these processes. For example, there may be people using Outlook for communication; Excel for analysis; Word for documentation; and just about any process ends in PowerPoint where you present the results of the work.

As we see the shift from a focus on an individual's productivity to a focus on the entire workgroup's productivity, it also means the way in which people program against our applications changes. This is why you see a lot more SharePoint development in addition to traditional Office development (I briefly mentioned this over the summer). It's also why the Open XML formats and the Open XML SDK were such important investments for us… they help the Office applications continue to play an important role in these workgroup level scenarios.

Printing and Re-Calcing on the Server

For example, one request we've heard from folks working with the Open XML formats on the server is that they want access to some core client functionality, like printing/layout in Word, or calculations in Excel. A couple weeks ago at the SharePoint conference we announced the next version of Excel Services, as well as a new service called Word Automation Services. These two services are great resources for people doing server side document assembly/manipulation with Open XML. The Open XML formats are great for consuming or generating content for dynamic document assembly scenarios, but often there is also the need to recalc the model, refresh the charts, or print the document, and you don't want to necessarily call back to the client to do this.

Server Side Document Assembly Example

A quick example to help explain this is a process I've seen a number of times, where banks were trying to do bulk generation of loan applications (thousands a day). There are end users involved in creating the template for the loan application, as well as building out the financial models for how the terms of the loan are determined, but the generation of the individual applications was a bulk server-side process based an incoming list of applicants. The combination of Open XML and content controls makes it pretty easy to create .docx files on the server, but in this scenario the banks wanted to send out either PDFs or hard copies, which meant you needed to include the Word client in the process (and it was obviously the big bottle neck).

With the new services announced at the SharePoint conference though, you get the perfect mix of software + services to solve the scenario. The end users can continue to work in the applications they are familiar with (Word & Excel) to build out the template and financial model, and the bulk generation can all happen on the server in an automated process:

Here's a quick step by step explanation of the process:

Client-side: Loan Template author generates the template for the loan application, and uses content controls to specify where the data should go. He saves it up to SharePoint so others can collaborate with him.
Client-side: Folks from the Legal department are able to work with in the document at the same time as the Template author because of the new co-authoring functionality in Word 2010.
Client-side: A financial analyst builds up a model for determining whether an applicant should be considered and what the terms of the loan should be. This model is saved up to SharePoint as an .xlsx
Server-side: As new applicants request an application, a server side process takes their data, and uses the Open XML SDK to inject it into xlsx. (Example 1; Example 2)
Server-side: The financial model (.xlsx) is then sent off to Excel Services to perform calculations and pull out the results
Server-side: The process takes the results of the calculation, and injects them into the Word template, using the content controls to determine what data should go where, producing a .docx file. (Example 1; Example 2)
Server-side: The .docx file is passed off to Word services where a .pdf file is generated, which can either then be sent on to a high volume printer, or e-mailed directly to the applicant.
Client-side: Any of the users can make updates to the documents (assuming this is allowed as part of the workflow), and those changes will automatically make their way into the bulk generation process.

So, you can see this is a pretty basic but also extremely power scenario, and if you've been reading Zeyad's examples over the past year or so you know how easy this can be with the Open XML SDK. I've been excited about these technologies for a few years now, and it's great that we can finally start talking publicly about them. Zeyad had a couple great demos at the SharePoint conference that really helped show the value of these services in combination with the Open XML formats. We'll post a video of the presentation as soon as it comes available, and will also have separate blog posts that drill into each one over the coming months.

-Brian

Migrating your custom solutions to Office 14

I'm Brian Jones — Fri, 23 Oct 2009 00:11:54 GMT

For those folks who have custom Office solutions within their enterprise, one area that you often need to focus on during the upgrade cycle is testing out those custom solutions to make sure they'll run in the latest version. With Office 14, we're going to provide a great set of tools to make it even easier for folks tasked with deployment. Michael Kiselman provided a great overview today up on Gray Knowlton's blog: http://blogs.technet.com/gray_knowlton/archive/2009/10/22/announcing-the-office-2010-application-compatibility-program.aspx

We always focus heavily on testing application compatibility from version to version, and in most cases applications will continue to run just like they did in the older version. From time to time though there are cases where a custom application may need to be updated. The tools Michael describes help with both the discovery and migration processes.

-Brian

Open XML SDK Demos Shown at the SharePoint Conference (SPC)

I'm Brian Jones — Thu, 22 Oct 2009 04:45:00 GMT

First off, I want to thank everyone who attended my two sessions at SPC on developing solutions on top of the Open XML SDK. The sessions were recorded, so as soon as they are published to the web I will send you guys some links. In the meantime, I want to share with you guys some really cool demos and source code I presented at the sessions.

Here is a link to all the demos that I showed, plus a bunch more: http://zeyadrajabi.members.winisp.net/sourcecode/Open XML demos.zip

This zip file includes demos I have already blogged about plus a few new solutions around using the Open XML SDK with Office Services. Expect to see future blog posts for the demos that haven't been blogged about yet.

Zeyad Rajabi

Open XML SDK Sessions at the SharePoint Conference (SPC)

I'm Brian Jones — Fri, 09 Oct 2009 23:51:00 GMT

We're just a couple of weeks away before the start of the SharePoint Conference (SPC) being held in Las Vegas. I want to give you guys a heads up that I will be at the conference from Oct 19-22. I'm pretty excited because I will be giving two different presentations on the Open XML SDK at the conference. Here's the description and times of the two sessions I'll be doing:

Session Title

Time & Date

Session Code

Description

SharePoint 2010 Based Document Assembly and Manipulation using Word Automation Services and Open XML

10/20/2009 10:30 AM

@ Mandalay Bay H

SPC349

The Open XML Formats are the new default file formats for Word, Excel and PowerPoint (docx, xlsx, pptx). With the Open XML SDK, which provides a set of .NET APIs that allows developers to create and manipulate documents in the Open XML Formats, and the Office services available on SharePoint 2010 (Word Automation Services, which performs high-performance bulk document conversions, and Excel Services, which provides server-side support for spreadsheet calculation), developers can now build solutions for server environments without the need of the Office clients. With just a few lines of code you will be able to create rich solutions, like document assembly. Come and check out all the demos and free source code.

Deep Dive Open XML and the Open XML SDK

10/20/2009 1:15 PM

@ Mandalay Bay J

SPC402

This session will introduce you to Open XML and the tools you use to develop Office document solutions. Using Open XML you have the power to create and edit documents on the server without needing to resort to COM based Office automation. You will get a basic understanding of how to use Open XML for authoring documents, spreadsheets and presentations. You will learn how to leverage the Open XML SDK and other various tools to make your development life easier. After this session is complete you will know everything you need to get going with building document automation processes. Come and check out all the demos and free source code.

Let me know if there are specific demos you guys would like to see and I'll try to work on getting them included. After my presentations, I will make available all my demos and source code on this blog. Besides the presentations, you will be able to find me at the Office client booth during the week of SPC and at the "Ask the Experts" event on Wednesday October 21. If you're at SPC do come by and say hello. I always look forward to hearing feedback from you guys at these conferences.

Zeyad Rajabi

Finding Open XML Errors with Open XML SDK Validation

I'm Brian Jones — Fri, 25 Sep 2009 00:36:00 GMT

In a previous post, I gave you an overview of the functionality added to the Open XML SDK 2.0 August 2009 CTP. Today, I want to deep dive into the schema and semantic level validation support within the SDK. Specifically, I am going to show you guys the Open XML SDK code needed to actually validate your Open XML files.

If you've played around with manipulating Open XML files there is a good chance at one point in time your resulting document was considered invalid or corrupt by the applications. You've probably even seen one of these dialogs:

What do you do when you get into this state? A lot of the time the application error dialogs don't really help you debug the issue. Well, that's where the Open XML SDK can help you out. With just a few lines of code you can identify key pieces of information that tell you what the error is and where to find it within the package. Validation with the Open XML SDK 2.0 is accomplished via the OpenXmlValidator class. This class allows you to enumerate all the errors within a file, where each error is represented via the ValidationErrorInfo class. The ValidationErrorInfo class stores the following information:

User friendly description of the error
An XPath to the exact location of the error
The part where this error exists
Other elements or parts that are related to this error

Here is a code snippet you can reuse to validate Word documents:

try

{

OpenXmlValidator validator = new OpenXmlValidator();

int count = 0;

foreach (ValidationErrorInfo error in validator.Validate(WordprocessingDocument.Open("InvalidFile.docx", true)))

{

count++;

Console.WriteLine("Error " + count);

Console.WriteLine("Description: " + error.Description);

Console.WriteLine("Path: " + error.Path.XPath);

Console.WriteLine("Part: " + error.Part.Uri);

Console.WriteLine("-------------------------------------------");

}

Console.ReadKey();

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

The same code can be used to validate Excel and PowerPoint documents. All you need to do is change the Open method to be one of the following:

foreach (ValidationErrorInfo error in validator.Validate(PresentationDocument.Open("InvalidFile.pptx", true)))

foreach (ValidationErrorInfo error in validator.Validate(SpreadsheetDocument.Open("InvalidFile.xlsx", true)))

Pretty simple stuff! If you want to jump straight into the code, feel free to download this solution here.

Let's walk through an example of validating and fixing an example corrupt Word document. Given this corrupt document, the Open XML SDK detects the following errors:

Let's look at each of these errors.

Error 1

Description: The attribute 'http://schemas.openxmlformats.org/wordprocessingml/2006/main:rsidR' has invalid value '006B4C'. The actual length according to datatype 'hexBinary' is not equal to the specified length. The expected length is 4.
Path: /w:document[1]/w:body[1]/w:p[1]
Part: /word/document.xml

Let's take a look at the xml within the main document part:

The error indicates that the length of the value for rsidR is not correct. We can fix this issue by changing the value to 00006B4C.

Error 2

Description: Element 'DocumentFormat.OpenXml.Wordprocessing.Footnote' referenced by 'footnoteReference@id' does not exist in part '/word/footnotes.xml'. The reference value is '3'.
Path: /w:document[1]/w:body[1]/w:p[6]/w:r[2]/w:footnoteReference[1]
Part: /word/document.xml

Let's take a look at the xml within the main document part:

Let's take a look at the xml within the footnotes part:

The error indicates that there is a reference to a footnote using the value "3", but no such value exists in the footnotes part. Let's go ahead and change the footnoteReference to have a value of "2".

Error 3

Description: Attribute 'id' should have unique value. Its current value '1' duplicates with others.
Path: /w:endnotes[1]/w:endnote[4]
Part: /word/endnotes.xml

Let's take a look at the xml within the endnotes part:

The error indicates that that more than one endnote specify the same id value. Let's go ahead and change the values to be unique.

End Result

After making these fixes we should be able to open the fixed document with no issues as shown below:

Try out the validation functionality and let us know what you think.

Zeyad Rajabi

Open XML SDK Code Snippets

I'm Brian Jones — Thu, 17 Sep 2009 22:44:00 GMT

In my previous post, I announced the release of the Open XML SDK August 2009 CTP. Today, I want to announce the release of the Open XML SDK code snippets. This package of code snippets provides over fifty reusable code samples, in both C# and VB.NET, which accomplish many common tasks involving Excel, PowerPoint, or Word documents. Looking back at the architecture diagram for the Open XML SDK 2.0, these code snippets are part of the high level helper functions:

Erika Ehrli provided a quick summary on all the code snippets in the following post.

Using the Open XML SDK Code Snippets

Let's walk through a quick example of using the Open XML SDK code snippets. In this example, we are given a spreadsheet with a table of data and are asked to read and change a particular cell value. Here is a screenshot of the spreadsheet:

Let's say we are asked to read the value of C4 and then change the value from "Austin" to "Houston".

Here is how you would accomplish this scenario using the Open XML SDK code snippets and the Open XML SDK 2.0:

Create a solution in Visual Studio 2008
Add references to the Open XML SDK 2.0 (DocumentFormat.OpenXml.dll) and WindowsBase.dll
Enable the code snippets for your solution by following these steps
To read a cell value, add a new method to your solution based on the Open XML SDK code snippets. In particular, add the "Excel: Get cell value given row and column" code snippet, which retrieves a cell value given its row and column numbers, or a row number and column name
Use the following code to read and display the value for C4:

string c4Value = XLGetCellValueRowCol("output.xlsx", "Sheet1", "C", 4); Console.WriteLine("The value for C4 is: " + c4Value);
To change a cell value, add a new method to your solution based on the "Excel: Insert string into cell" code snippet, which given a document name, a worksheet name, a cell name, and a value, inserts text into the specified cell
Use the following code to change the value of C4 to "Houston"

XLInsertStringIntoCell("output.xlsx", "Sheet1", "C4", "Houston");

At the end of step #7 we end up with the following Excel spreadsheet:

Pretty easy with the code snippets! If you are interested in the full solution you can find it here.

Zeyad Rajabi

Announcing the Release of the August 2009 CTP for the Open XML SDK

I'm Brian Jones — Thu, 27 Aug 2009 16:21:00 GMT

I'm really happy to announce the release of the 3^rd CTP for the Open XML SDK 2.0 for Microsoft Office! So what did we do in this CTP? Well, there were three main improvements we made to the SDK:

Add semantic level validation support
Add markup compatibility/extensibility support
General improvements based on your feedback

Semantic Level Validation Support

Let's go back to the Open XML SDK architecture diagram I showed you when we first announced the Open XML SDK:

As mentioned in a previous post, the April 2009 CTP of the Open XML SDK added schema level validation support for Office 2007 Open XML files. In the August 2009 CTP, one of the big things we added is semantic level validation support for Office 2007 Open XML files:

Semantic level validation goes beyond restrictions or rules defined by schemas. Semantic level validation allows developers to validate files against restrictions defined within the prose of the Open XML documentation. These are restrictions, which cannot be expressed in an XSD language.

Let's look at a semantic level restriction example. Specifically, let's look at the element endnote (Section 17.11.2 of Part 1 in the ISO/IEC-29500 specification). In the standard, it states that the id attribute of endnote, "specifies a unique ID which shall be used to match the contents of a footnote or endnote to the associated footnote/endnote reference mark … If more than one footnote shares the same ID, then this document shall be considered non-conformant. If more than one endnote shares the same ID, then this document shall be considered non-conformant." As you can see, having more than one endnote with the same id value will result in a non-conformant document. This non-conformant document may not be interpreted properly by a consuming application, like Word.

The Open XML SDK can now help you find these types of problems and will report the error to you by giving you the following information:

User friendly description of the error
- In this case, imagine seeing the following error "Attribute 'id' should have unique value. Its current value '1' duplicates with others."
An Xpath to the exact location of the error
- In this case, imagine seeing the following path "/w:endnotes[1]/w:endnote[4]," which indicates that the problem exists in the fourth endnote element
The part where this error exists
- In this case, imagine seeing the following part information "DocumentFormat.OpenXml.Packaging.EndnotesPart"

We hope that you can use this type of information to more easily find and fix problems. I will devote at least one blog post in the future to go into details on the validation functionality.

Markup Compatibility/Extensibility Support

As defined by the ISO/IEC-29500 specification, there are several ways to extend markup within the Open XML formats. Some of the extension mechanisms, like ignorable content and alternate content blocks, may result in differences within the XML tree structure of a document. Here is an example of markup that contains an alternate content block:

<w:document mc:Ignorable="w14 wp14">

<w:body>

<w:p w:rsidR="00FA0A01" w:rsidRDefault="00AF5A8F">

<w:r>

<w:rPr/>

<mc:AlternateContent>

<mc:Choice Requires="wps">

<w:drawing>…… </w:drawing>

</mc:Choice>

<mc:Fallback>

<w:pict>

<v:roundrect id="Rounded Rectangle 1" o:spid="_x0000_s1026" style="position:absolute… " arcsize="10923f" o:gfxdata="…" fillcolor="#4f81bd" strokecolor="#385d8a" strokeweight="2pt">

<v:textbox style="mso-rotate-with-shape:t"/>

</v:roundrect>

</w:pict>

</mc:Fallback>

</mc:AlternateContent>

</w:r>

</w:p>

<w:sectPr w:rsidR="00FA0A01">…… </w:sectPr>

</w:body>

</w:document>

In the example above, the expected child of the run element differs depending on the chosen alternate content choice. The fallback choice is what one would expect from a document created in Office 2007, while the choice requiring the wps namespace is from a document created in Office 2010. Imagine you are a solution developer working with Open XML who has deployed a solution that works perfectly on top of Office 2007 Open XML files. How would your solution work with files coming in from Office 2010? Specifically, would your solution work with documents that contain these types of extension mechanisms?

As part of the August 2009 CTP we have added functionality that allows developers to abstract away some of the difficulty intrinsic with markup compatibility and extensibility. This feature allows you to preprocess the content of Open XML files based on specific Office versions. Using the example above, if we use the August CTP to open the document based on Office 2007 we will only see the following XML markup:

<w:document>

<w:body>

<w:p w:rsidR="00FA0A01" w:rsidRDefault="00AF5A8F">

<w:r>

<w:rPr/>

<w:pict>

<v:roundrect id="Rounded Rectangle 1" o:spid="_x0000_s1026" style="position:absolute… " arcsize="10923f" o:gfxdata="…" fillcolor="#4f81bd" strokecolor="#385d8a" strokeweight="2pt">

<v:textbox style="mso-rotate-with-shape:t"/>

</v:roundrect>

</w:pict>

</w:r>

</w:p>

<w:sectPr w:rsidR="00FA0A01">…… </w:sectPr>

</w:body>

</w:document>

If your solution expected a pict element as a child of a run element, then your solution would work perfectly with this file. In other words, using this feature, solutions won't break when future versions of Office introduce new markup into the format.

General Improvements

First off we want to thank everyone for their feedback and suggestions! Based on your feedback we made the following big changes to the SDK:

AutoSave: By default, previous CTPs of the SDK forced you to perform a manual save for changes made to specific parts within the package. We have now introduced the concept of AutoSave, where changes would automatically be saved into the package, without the need to call Save() methods. For those not interested in this functionality, there is a way to turn off this feature
Base Classes for Sdt objects: The SDK currently has multiple classes to represent Sdt objects based on the different types of elements specified in the standard. The August 2009 CTP has introduced one base class for each of these objects in order to make it easier for you to develop solutions. In other words, your solution can now just work on the following abstract class: SdtElement for Sdt objects
Simple types for Boolean type attributes: The standard specifies the concept of a simple type called ST_OnOff, which allows for values like "On", "Off", "True", "False", "0", and "1." We have updated the SDK to allow you to directly get/set such attributes using standard C# Boolean values. For example, you can now set attribute values to false or true. Without this enhancement you were forced to compare values using the enum BooleanValues

What's Next?

Our next task for the SDK is to add Office 2010 Office Open XML support. Expect to see another CTP in the next several months released with this functionality. Our goal is to be done with the Open XML SDK 2.0 around the same time as Office 2010 ships (date not public yet).

More Feedback Always Welcome

Please continue to send us your feedback, either on this blog or at our Microsoft Connect site for the Open XML SDK https://connect.microsoft.com/site/sitehome.aspx?SiteID=589. We look forward to hearing from you.

Zeyad Rajabi