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.

Introducing the UpdatePanel plug-in.

A while ago I blogged about Combining jQuery and Ms Ajax UpdatePanels. (http://bloggingdotnet.blogspot.com/2009/03/combining-jquery-and-updatepanels.html). The main problem is that the UpdatePanel contents (a Div element) are replaced during each post back from the server and this means that jQuery looses all the references to the elements that have now been replaced in the DOM. My initial work around (and commonly found on the internet) is a little clumsy as it involves hooking the Ms Ajax pageLoad event and placing all your jQuery code in a separate function which is then called from $(document).ready or pageLoad. Not pretty and not clever, especially when you have lots of jQuery and you only want to rerun the jQuery for the panel that has actually changed.

My subsequent workarounds involved looking into the LiveQuery plug-in and looking at Live events which are new to jQuery 1.3, however I wasn't satisfied with the performance, and the code still looked ugly. And when it looks ugly that usually means there is a better way. What I wanted was code that looked and worked like any other jQuery plug-in. I needed to dig more deeply into the Ms Ajax client runtime. What I found was the PageRequestManager which is created once per page by Ms Ajax and fires the pageLoaded event on each post back. Hooking into this event allows us to examine the panels that have been updated or created. Using this information, it was then relatively straightforward to wrap a jQuery-style functional interface around these events.

The UpdatePanel plug-in has the following three callbacks:

  • .panelCreated(fn) - called when the UpdatePanel is created on a page
  • .panelUpdated(fn) - called when the panel is updated during a asynchronous (ajax) postback.
  • .panelReady(fn) - called when the UpdatePanel is first created or updated

You would generally use the panelReady event like so:

$(document).ready(function() {
  //Place jQuery code here for elements selected outside the update panel
  $('#UpdatePanel1').panelReady(function() {
    //Place jQuery code here for elements selected inside the update panel
  });
});

Generally, you would only want to use panelReady, which is called when the page is first loaded, or on each postback. A sample project as well as the script is available at http://updatepanelplugin.codeplex.com/ or http://plugins.jquery.com/project/updatepanelplugin

Thursday 14 May 2009

Simple OOXML featured on OpenXmlDeveloper

Simple OOXML has been picked up by http://openxmldeveloper.org - Microsoft's website for promoting the Office Open Xml standard - and is mentioned on their home page. Expect to see an article appear on the site over the next few days.