Sunday 17 May 2009

Getting started with the Simple OOXML library.

The Simple OOXML library allows you to create Word Processing (.docx) and Spreadsheet (.xlsx) documents quickly and easily, without having to understand the complex nature of the underlying Xml formats. Because the documents are (mostly) pure xml, these documents can be created in environments even where there is no Microsoft Office installation, such as on a web server.

Whilst the Open Xml Format SDK provides a convenient object wrapper around the xml specification, it is still far from easy to create these documents. Even with a sound understanding of the specification, the Simple OOXML library tool is still useful in providing wrapper functionality to developers, without having a noticeable performance overhead.

To use the Simple OOXML library, you need to be using .Net Framework 3.5 (or later). You'll also need to download the Open Xml Format SDK v 2.0. The current release is the April 2009 CTP and is available from the Microsoft website here: http://www.microsoft.com/downloads/details.aspx?FamilyId=C6E744E5-36E9-45F5-8D8C-331DF206E0D0. Obviously this is pre-release code but so far I've found the CTP releases very stable and haven't found any problems or bugs. Finally, you will need to download a copy of the latest Simple OOXML library release, available at CodePlex: http://simpleooxml.codeplex.com/Release/ProjectReleases.aspx. Since Simple OOXML is open source and distributed under the LGPL licence, you can use and distribute the binary with any application, commercial or otherwise. You can also have a look at the C# code to see how the library operates.

To demonstrate the capabilities of the Simple OOXML library, I'll show you how a number of ways to work with Spreadsheet documents by creating a new ASP.NET Web Application in Visual Studio 2008. Follow these steps to get started:

  1. Create a new ASP.NET Web Application.
  2. Include references to DocumentFormat.OpenXml from the SDK and the DocumentFormat.OpenXml.Extensions.dll from the Simple OOXML release.
  3. Finally, add an asp:Button control to the default.aspx page that was created for you, so that we have somewhere to run our code from.

Make sure that you are using the following namespaces:

using System.IO;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Extensions;

Creating a new Spreadsheet (.xlsx) document.

In the click event handler for the button added above, add the following code:

protected void Button1_Click(object sender, EventArgs e)
{
   MemoryStream stream = SpreadsheetReader.Create();
   SpreadsheetDocument doc = SpreadsheetDocument.Open(stream, true);
   WorksheetPart worksheetPart = SpreadsheetReader.GetWorksheetPartByName(doc, "Sheet1");
   WorksheetWriter writer = new WorksheetWriter(doc, worksheetPart);

   writer.PasteText("B2", "Hello World");

   //Save to the memory stream
   SpreadsheetWriter.Save(doc);
          
   //Write to response stream
   Response.Clear();
   Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", "performance.xlsx"));
   Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

   stream.WriteTo(Response.OutputStream);
   Response.End();
}

The first four lines of code create a stream containing a new document. This is loaded into a standard SpreadsheetDocument object. A WorksheetPart is then retrieved using a sheet name. Finally a WorksheetWriter is created to enable us to write content to the worksheet. Next, the writer is used to paste the text "Hello World" into cell reference "B2". The static Save method of the SpreadsheetWriter class is used to update the stream with the document. The final five lines write the document stream to the browser setting the correct header values for a spreadsheet document.

In the next part of this series I'll demonstrate how to add numeric values, dates, shared text and datatables to worksheet documents.

6 comments:

Almir Begovic said...

Hi James

I am working on the project requiring production of a large number of the word documents from predefined templates.

Your library looks very promising, but I was not able to figure out how to open and manipulate a word document. One of the requirements is inserting tabular repeating data.

Is it possible with simple OOXML library and are you going to cover it in the future?

Thank you.

James Westgate said...

Have a look at the unit test project for some sample code to open Word Processing Documents as templates. A the moment these documents only support pasting text, not tables.

Download the SDK, create a simple doc with a table, and use reflector to look at the xml - it may help you.

Anonymous said...

Hi James,

Could not get your code to work.
I copied the DocumentFormat.OpenXml.dll and the DocumentFormat.OpenXml.Extensions.dll to Visual Studios project bin directory.
Only 'using DocumentFormat.OpenXml.Packaging;' could be added.
'using DocumentFormat.OpenXml.Spreadsheet;'and
'using DocumentFormat.OpenXml.Extensions;'
could not resolved by VS2008.
'SpreadsheetWriter' and 'stream' are not recognized either.

What am I missing?

Regards, Tom

James Westgate said...

@Tom

Have you added the references to the project in visual studio?

Sig said...

Hi James,

I'm evaluating your OOXML library to see if it can be of use for my Excel document generation needs. The design looks very interesting and I was able to start using the library right away. However, I am struggling to figure out how to do some more advanced cell formatting. For example, I need to be able to format a cell to use the Left horizontal alignment WITH Indent set to 4. Any suggestions would be appreciated.

Thanks for your contribution.

Regards,
Sig

Sandeep said...

Hi,

How can read docs file with OOXML?

I need to compare 2 docx files and write the difference to another docx files.
I had achieved this with Interop, but there i cant customize the output.

Thank you