Create in-memory OpenXml Document on-the-fly part-1

Requirement: Open XML SDK V2 CTP

Resource: OpenXml Developer

After you install this SDK, it contains 3 very useful tools which you help you a lot.

DocumentReflector – generate code from a document, the code will generate that document if you run it. It is very “hardcode” code but it gives you a good start.

OpenXmlClassesExplorer – tells you about parent-child relationship of those classes, the number of classes is vast, so this is the most import tool.

OpenXmlDiff – when you open your document and it is not correct, use this to compare with the origin version of it. Not really usefull.

I have only create spreadsheet document using open xml sdk, but I am sure the process is quite similar for word and powerpoint document.

It takes me days to understand the new document architecture of open xml, so let’s give you some background to save you some time.

All open xml documents, including spreadsheet, wordprocessing, powerpoint are actually .zip package. You can rename their extension to .zip, unarchive it to see what’s inside. 

The basic structure do not need to have folder, but you can use folder to make it more human understandable. the files that must be present are: .rels, document.xml

Let’s look at an example of .xlsx

_rels–.rels

xl–workbook.xml

xl–_rels–workbook.xml.rels

xl–worksheets–sheet1.xml

.rels is resposible for defining the relationships between contents. Act like glue to put those .xml together into a document. If you have a workbook.xml which reference to some worksheet .xml, then you will have a workbook.xml.rels to define that. Styles, Charts, Drawings, etc all works in the same way.

So, let’s start generating a .xlsx document. First step, I assume you are using some version of visual studio, I use VS 2008. Add two reference to you project. WindowsBase from .NET framework 3.0 and DocumentFormat.OpenXml from the Open XML SDK. It is because the Open XML SDK is base on DocumentFormat.OpenXml.Packaging which is in .NET framework 3.0.

Next Step, create your document package. Add the appropriate using statements, for example: 

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Spreadsheet;

Create a memory stream for the package and add a workbook to it.

//create a package in memory
MemoryStream l_memStrm = new MemoryStream(512);
Package l_memPack = Package.Open(l_memStrm , FileMode.Create, FileAccess.ReadWrite);
SpreadsheetDocument l_package = SpreadsheetDocument.Create(l_memPack, SpreadsheetDocumentType.Workbook);
var workbookPart = l_package.AddWorkbookPart();
GenerateWorkbookPart().Save(workbookPart);

GenerateWorkbookPart() is a method that generate a workbookpart element. I get this from DocumentReflector and then remove unnecessary code. You can use mine if you want.

private static Workbook GenerateWorkbookPart()
{
var element =
new Workbook(   //root element workbook
new FileVersion() { ApplicationName = "xl", LastEdited = "4", LowestEdited = "4", BuildVersion = "4505" },   // fileversion, first child under workbook
new WorkbookProperties() { FilterPrivacy = true, DefaultThemeVersion = (UInt32Value)124226U },   //workbookpr, child under workbook
new BookViews(
new WorkbookView() { XWindow = 0, YWindow = 15, WindowWidth = (UInt32Value)19200U, WindowHeight = (UInt32Value)11865U }), //workbookview, child under bookviews
new Sheets(   //sheets, child under workbook, must have
), //sheet, child under sheets, must have
new CalculationProperties() { CalculationId = (UInt32Value)125725U });  //calcpr, the calculator engine version number, use to avoid implicit prompt for "save changes"
return element;
}

my comment pretty much explain the line of code. In part 2, I am going to add worksheet to make the spreadsheet complete and add data to it. see ya.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s