Export (0) Print
Expand All
Expand Minimize

Creating Business Applications by Using Excel Services and Office Open XML Formats

Office 2007

Summary: Learn about methodologies that were used at Microsoft to address internal application user requirements that went beyond the "out-of-the-box" functionality in Microsoft Office SharePoint Server 2007 and Excel Services. You can use these techniques to take applications that are based on Excel Services further by using the extensibility features built into the 2007 Microsoft Office system. (14 printed pages)

Sergei Gundorov, Microsoft Corporation

April 2008

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007, Excel Services, Microsoft Office SharePoint Server 2007


Microsoft Office SharePoint Server 2007 and Excel Services provide a lot of functionality straight out of the box. For example, with a click of a button, business users can publish to the Web interactive Microsoft Office Excel 2007 workbooks that connect to data in secured OLAP (online analytical processing) cubes. This article describes methodologies that the Microsoft IT team used to address internal application user requirements that went beyond what is offered “in the box.” You can use these techniques to extend Excel Services applications.

This article describes how to:

  • Programmatically obtain the active workbook byte stream from the Excel Web Access (EWA) Web Part.

  • Update the workbook contents by using Office Open XML Formats; for example, programmatically add back features that are unsupported in EWA, add automation, and so on.

  • Send updated workbooks to the rich Excel client for more interaction, such as editing and high quality printing.

Why do we want to do all of this? For various reasons (one of which is security), the Microsoft Office team made design decisions that set some limits around the type of content that can be exposed through the Excel Web Access Web Part. These content restrictions are nothing serious for Web-only scenarios, but they can affect cases when you want to return to the rich client from the Web. When users export the workbook back to Excel, they might need to add or restore features that are incompatible with EWA. You can handle most of the by-design content restrictions by using a few lines of code that modify Office Open XML Formats file package parts.

Since June 2003, our team has been working on the Executive Reporting User Interface Infrastructure system that supports top level executives’ decision-making process. The earliest version of this application was written in Microsoft Visual Basic for Applications (VBA), and it evolved from Microsoft Office Excel 2003 to the Excel 2007 managed add-in. This year, executives wanted us to put their application on the Web. We decided to stay in Excel so that we could take advantage of the business intelligence and data visualization capabilities in Office Excel 2007, and we used Excel Services to enable the system for the Web.

Most financial analysts at Microsoft "live" in Excel (that is, they model in Excel, submit their reporting requirements in Excel, reformat and print various reports using Excel, and so on). Because most users are comfortable with Excel formulas and formats, as a bonus, we also put routine system maintenance in the hands of Excel reports content authors. With SharePoint Server 2007 versioning support, plus Excel files and data connection-trusted libraries that can be secured by individual users, Microsoft IT was able to deliver a very agile and stable system. In short: designated business users control all reports content and can always roll back unwanted changes.

This article does not describe how to create pivot tables, or how to use CUBE functions with rich data visualization to create Excel reports and publish them on the Web using SharePoint Server 2007 and the Excel Web Access Web Part. Many other resources describe how to do this. For more information, see Additional Resources in this article.

This article describes the following challenges that we had to overcome to keep IT, financial analysts, and executives happy when we took Excel reports to the Web:

  • Excel workbooks published to the Web must be stripped of all features and elements that are incompatible with EWA. For the latest EWA Compatibility Checker tool, see Shahar Prish’s blog.

  • The browser today is still not as capable as the Excel client application, so printing boardroom presentation-quality Excel reports from the rich-client application is a very good option. Incidentally, the active workbook export option (Open in Excel) is natively supported in EWA.

  • Because you have to go to the rich-client application for printing, what if you want to restore some features that had to be removed to use EWA to improve the user experience with the application and increase their productivity?

We addressed these challenges by using Excel Services extensibility and Office Open XML Formats.

The product team anticipated this scenario and created a JavaScript function to support it. This section contains an example of how to obtain the active session workbook byte stream from the browser, manipulate it, and return it to the rich client. We use this method to enable advanced printing, so the code example contains "Print in Excel" references.

If you plan to alter the Office Open XML workbook contents, your SharePoint Server 2007 Web Parts page must intercept user requests to move the workbook into the rich client and manipulate it on the server before sending it. To put your modified workbook onto the user’s computer, you must stream the bytes back across the wire. You want to do this in an ASPX page instead of in a Web Part.

This solution consists of two pieces:

  1. A Print in Excel button on the page opens a new window that points to the ASPX page with the session ID.

  2. The ASPX page that gets the active workbook, manipulates its contents by using the Office Open XML package object model, and then streams the bytes back to the client.

To complete the first piece, you can add a Content Editor Web Part to your Web Part page, open the Source Editor, and then embed the following code.

<script language="JavaScript">
function LaunchWindow() {   
    window.open('/_layouts/ExcelPrint.aspx?sesid=' + encodeURIComponent(EwaGetSessionId('WebPartWPQ6')), 'mywindow', '');}
<input type="button" value="Print" onclick="LaunchWindow();"> 

You use the EwaGetSessionId JavaScript method to obtain the session ID from the EWA Web Part. Note that it returns null if the EWA has not yet finished loading the workbook. It takes as a parameter the Web Part ID of the EWA that the user is using.

You can retrieve the Web Part ID by doing the following:

  1. Right-click the page and select View Source (outside the EWA).

  2. Search for “Excel Web Access.”

  3. The first result (or you can continue if you want a different EWA) is a line that looks like the following:

    …'Excel Web Access - Test 1', false)" relatedWebPart="WebPartWPQ6"…

    The relatedWebPart is the ID of the EWA (it was WebPartWPQ6 in the previous example).

To complete the second piece, your ASPX page (ExcelPrint.aspx from the previous example) must read in the session ID from the query string, and then run the following code.


This code example was provided by Dan Parish, Microsoft Office Team Program Manager.

. . .
Status[] status;

// Initialize the Excel Web service, which you must add as a //resource.
ExcelService es = new ExcelService(); 
if (sessionId == null)

    // Something went wrong.

// Get the workbook.
byte[] workbook = es.GetWorkbook(sessionId, WorkbookType.FullWorkbook, out status); 

// Load byte stream into an OOXML package and manipulate its parts here. 

// Return the modified workbook stream back to the user: // Response.Clear(), setup header, and write
// OOXML package stream into the response stream 
      es.CloseWorkbook(sessionId, out status);
. . .

Here is the application code segment, ExcelPrint.aspx.cs, which uses the method described in the previous code.

namespace ExecutiveReporting
    public partial class ExcelPrint : System.Web.UI.Page
        /// <summary>
        /// Loads the Excel workbook from the current session, 
        /// inserts VBA and will stream the file to the client 
        /// </summary>
        protected void Page_Load(object sender, EventArgs e)
            Status[] status;
            MemoryStream targetStream = null;
            string binTarget;

            // Target Excel file name (randomly generated).
            binTarget = "ER" + DateTime.Now.Ticks.ToString() + ".xlsm";

            if (Request.QueryString.Count > 0)
                    if (Request.QueryString["sesid"].Equals("null"))
                            "Please try again after scorecard " +
                            "loading is complete");
                        // Create an instance of ExcelService object.
                        ExcelService es = new ExcelService();

                        // Get the workbook bytes for that session.
                        byte[] workbook = es.GetWorkbook(
                            WorkbookType.FullWorkbook, out status);
                        using (targetStream = new MemoryStream())
                            // Write the obtained workbook to the stream.
                            targetStream.Write(workbook, 0,

                            VBAInserter vbaInserter = 
                                new VBAInserter();

                            // Open the Source package of the Excel file
                            // containing VBA to be inserted.
                            vbaInserter.SourcePackage =
                                FileMode.Open, FileAccess.Read);

                            // Open the target package of the Excel file 
                            // containing current session workbook.
                            vbaInserter.TargetPackage = 
                                FileMode.Open, FileAccess.ReadWrite);

                            // Inserts VBA from sourcepackage to
                            // targetpackage.

                            // Clear Response headers and contents.

                            // Add Excel as content type and attachment.
                            Response.ContentType = 
                                "attachment; filename=" + binTarget);

                            // Reset the stream position to zero 
                            // to avoid file corruptions.
                            targetStream.Position = 0;

                            // Write the VBA inserted stream to response.

                catch (System.Web.Services.Protocols.SoapException ex)
                        "Error: Please check the Web service URL");
                catch (System.Net.WebException ex)
                        "Error: Please check the Web service URL");
                catch (Exception ex)
                    if (targetStream != null)
                Response.Write("Invalid Session ID");

What exactly is this code doing to the workbook byte stream, and why does it contain references to VBA?

The previous example shows how you can read the active EWA workbook byte stream into a server-side memory stream—veryimportant, because you avoid caching the file to the disk—and then into an Office Open XML package. After you have your package, you can read and write cell values, read metadata that is stored in CustomXMLParts (EWA ignores CustomXMLParts, so their presence does not prevent the file from opening), and restore features that are incompatible with EWA before you send the workbook back to the client, insert incompatible controls such as text boxes, and so on.

We chose to insert digitally signed VBA code. When you consider the fact that most applications in Office nowadays use managed code, why did we go this way? Executive leadership wanted the new system user interface (UI) to require no client code installation, and yet the end users expected to interact with the workbook and easily print it in Excel. Our users do detailed drilling, and so they requested that we dynamically adjust the print area depending on the visible details, pull user commentary (embedded in the worksheet object) for the active pivot report filter values, and combine the commentary and data view on the same sheet. After several iterations, we concluded that VBA automation was the easiest way to provide the desired user experience and seamlessly deliver code to the client. If the next version of Microsoft Office continues to support VBA as planned (for more information, see the Bill Gates Keynote Speech at the Office System Developers Conference 2008), this approach should also work in a future version of Excel. The following code example shows how to insert digitally signed VBA code into an Excel workbook and then convert its type from XLSX (the file format supported by EWA) to XLSM (macro-enabled file).

using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.IO;
using System.IO.Packaging;
using System.Diagnostics;

namespace ExecutiveReporting
    /// <summary>
    /// Converts OpenXML Excel 2007 package from XLSX to 
    /// XLSM and inserts digitally signed VBA code to 
    /// support secure file export to client for offline 
    /// use with custom automation.
    /// </summary>
    public class VBAInserter
        #region Package Source/Target Variables

        // VBA project binary package part source file name.
        private Package sourcePackage = null;
        // VBA project binary package part target file name.
        private Package targetPackage = null;

        public Package SourcePackage
            get { return sourcePackage; }
            set { sourcePackage = value; }

        public Package TargetPackage
            get { return targetPackage; }
            set { targetPackage = value; }


        #region Public Methods

        /// <summary>
        /// Adds digitally signed (if signature is present in the 
        /// source package) or unsigned VBA code from the source 
        /// (sourcePkgName) to target (targetPkgName) 
        /// Excel 2007 OpenXML package.
        /// </summary>
        public void AddVbaToXlsx()

            // Instantiating XlsmPackageBuilder.
            XlsmPackageBuilder pkgBuilder = new XlsmPackageBuilder();

            // Opening the Source package.
            // Adding vbaProject.bin "AS IS" in source.
            pkgBuilder.AddPackagePart(new Uri("/xl/vbaProject.bin", 
                sourcePackage, targetPackage);

            // Adding VBA project relationship to package.

            // Authenticode section.
            Uri uriSignaturePart = new 
                Uri("/xl/vbaProjectSignature.bin", UriKind.Relative);

            // If digital signature is present in the source package, 
            // transferring both signature related parts to the 
            // target package.
            if (sourcePackage.PartExists(uriSignaturePart))

                // Adding vbaProjectSignature.bin "AS IS" in source.
                    sourcePackage, targetPackage);

                // Adding vbaProject.bin.rels "AS IS" in source.
                    new Uri("/xl/_rels/vbaProject.bin.rels", 
                    sourcePackage, targetPackage);

            // Changing content type from XLSX to XLSM if wasn't XLSM.


        /// <summary>
        /// Adds digitally signed (if signature is present in the 
        /// source package)or unsigned VBA code from the source to 
        /// target Excel 2007 OpenXML package.
        /// </summary>
        /// <param name="sourcePkgName">OpenXML Excel 2007 package 
        /// path containing digitally signed VBA code</param>
        /// <param name="targetPkgName">OpenXML Excel 2007 package 
        /// (XLSX) path to insert VBA code into</param>
        public void AddVbaToXlsx(string sourcePkgName, 
            string targetPkgName)
            this.sourcePackage = Package.Open(
                sourcePkgName, FileMode.Open, FileAccess.Read);
            this.targetPackage = Package.Open(
                targetPkgName, FileMode.Open, FileAccess.ReadWrite);


    /// <summary>
    /// Open XML Excel 2007 XLSM package builder.
    /// </summary>
    public class XlsmPackageBuilder
        #region Common Office Open XML Constants

        // OpenXML main workbook part content type constant.
        private const string wbkMacroContentType = 

        // OpenXML relationship part content type constant.
        private const string wbkRelContentType = 

        // OfficeOpenXML vbaProject part content type constant.
        private const string wbkVbaContentType = 

        // Uri for workbook.xml package part.
        private readonly Uri uriWbkMainPart = 
            new Uri("/xl/workbook.xml", UriKind.Relative);

        // Uri for workbook.xml.rels package part.
        private readonly Uri uriWbkRelPart = 
            new Uri("/xl/_rels/workbook.xml.rels", UriKind.Relative);

        // Uri for vbaProject.bin package part.
        private readonly Uri uriVbaWbkPart = 
            new Uri("vbaProject.bin",UriKind.Relative);


        /// <summary>
        /// Adds VBA relationship to an Excel file; increments 
        /// rId# to next available index value
        /// </summary>
        /// <param name="targetPackage">OpenXML Excel 2007 
        /// package</param>
        public void AddVBARelationship(Package targetPackage)
            // Obtaining main workbook part.
            PackagePart partWbkMainPart = 

            // Adding VBA relationship to the workbook part.
            string vbaRelId = "rId" + GetNextrId(targetPackage, 
                TargetMode.Internal, wbkVbaContentType, vbaRelId);

        // Method to take in XLSX package and convert to XLSM.
        // NOTE: the only way to alter content type is by re-adding 
        // new type.
        // [Content_Types].xml cannot be manipulated directly.
        /// <summary>
        /// Converts XSLX OpenXML package (Excel Services output) 
        /// to XLSM OpenXML package that supports VBA client-
        /// side automation.
        /// </summary>
        /// <param name="targetPackage">OpenXML Excel 2007 
        /// package</param>
        public void ConvertToXLSM(Package targetPackage)

            // Storing original contents of the package in XmlDocs.
            XmlDocument sourceRelXDom = new XmlDocument();

            XmlDocument sourceWbkXDom = new XmlDocument();

            // Converting part type to macro-enabled
            // Re-adding workbook part with macro-enabled content type
            // reason: cannot manipulate [Content_Types].xml directly.
            ReAddPart(uriWbkMainPart, sourceWbkXDom, 
                wbkMacroContentType, targetPackage);

            // Re-adding relationship part lost as a result of 
            // deletion of the workbook part.
            ReAddPart(uriWbkRelPart, sourceRelXDom, 
                wbkRelContentType, targetPackage);


        /// <summary>
        /// Deletes specified part and re-adds with specified 
        /// content type.
        /// Used to convert XLSX package to XLSM.
        /// </summary>
        /// <param name="partUri">part Uri</param>
        /// <param name="partContent">XmlDocument with part 
        /// content</param>
        /// <param name="partType">part type content</param>
        /// <param name="targetPackage">OpenXML Excel 2007 
        /// package</param>
        public void ReAddPart(Uri partUri, 
            XmlDocument partContent, 
            string partType, 
            Package targetPackage)

            // Deleting original part
            // TODO: review and see if can reuse original stream ref
            // NOTE: method recommended in 
            // http://msdn.microsoft.com/en-us/library/aa982683.aspx

            PackagePart newWbkPart = targetPackage.CreatePart(partUri,
                partType, CompressionOption.Maximum);

            using (Stream sourcePartStream = newWbkPart.GetStream())

        /// <summary>
        /// Copies entire package part from source package to target 
        /// package. 
        /// Use targetPackage.DeletePart if source part already 
        /// exists in targetPackage
        /// </summary>
        /// <param name="uri">URI of the package part to 
        /// copy from source</param>
        /// <param name="sourcePackage">OpenXML Excel 2007 
        /// package containing source part</param>
        /// <param name="targetPackage">OpenXML Excel 2007 
        /// package not containing source part</param>
        public void AddPackagePart(Uri uriBinPartSource, 
            Package sourcePackage, Package targetPackage)
            // Reading in source part.
            PackagePart sourceBinPart = 

            using (Stream sourcePartStream = sourceBinPart.GetStream())

                // Creating new part.
                PackagePart targetPart = targetPackage.CreatePart(

                // Writing new part to target package.
                CopyStream(sourcePartStream, targetPart.GetStream());


        /// <summary>
        ///Package stream handler helper method 
        /// </summary>
        /// <param name="source">source stream</param>
        /// <param name="target">target stream</param>
        public void CopyStream(Stream source, Stream target)
            const int bufSize = 0x1000;
            byte[] buf = new byte[bufSize];
            int bytesRead = 0;

            while ((bytesRead = source.Read(buf, 0, bufSize)) > 0)
                target.Write(buf, 0, (int)bytesRead);


        /// <summary>
        /// Returns index for next relationship ID in the supplied
        /// Office Open XML package relationship part.
        /// </summary>
        /// <param name="targetPackage">Open XML Excel 2007 package 
        /// containing relationship part</param>
        /// <param name="uriRelPart">Relationship part URI</param>
        /// <returns>index for next relationship ID</returns>
        internal int GetNextrId(Package targetPackage, Uri uriRelPart)
            // Reading relationship part.
            PackagePart relPart = targetPackage.GetPart(uriRelPart);

            // Loading part in XmlDocument for manipulation.
            XmlDocument relsXDoc = new XmlDocument();

            // Initializing relationship index.
            int index = 0;

            // Standard office pattern for document part Ids: "rId"
            foreach (XmlNode relNode in
                int nodeIndex = Convert.ToInt32(
                    relNode.Attributes["Id"].Value.Replace("rId", ""));
                if (index < nodeIndex) index = nodeIndex;

            // Incrementing to next avalaible index.
            return index + 1;

Even though this code example shows how to insert VBA and its digital signature parts, the concepts covered apply to most package part modifications: adding or removing the part and its corresponding relationship.


Never deploy unsigned source VBA. Why does the example contain a test for the presence of a digital signature? This is done to facilitate the development process by enabling fine-tuning of the source VBA prior to signing it. We do not allow unsigned source VBA in our production components.


The Welcome to the Open XML Format SDK 1.0 makes package manipulation easier than it is with these methods, which directly use System.IO.Packaging. However, they are not very complex, and they provide insight into the package structure: the parts, their relationships, content types, and so on. It is helpful to know how everything fits together.

What else can we do to enhance the application and the user experience? The previous example shows how you can manipulate Office Open XML packages. We discussed the addition of custom VBA code. This actually opens the door to another possibility that we already successfully prototyped but have not yet incorporated into our production application. Because we are already manipulating the package and adding automation code, it is easy to go one step further and add a custom Office Fluent Ribbon to the file that you return to the client. That makes it look much more like a true custom Office Business Application complete with its own UI. The following are methods that add customUI.xml to the package.

. . .
// Open XML custom UI relationship type.
private const string customUIContentType =

// Uri for main package relationship part.
private readonly Uri uriMainRelPart = new Uri(@"/_rels/.rels",    UriKind.Relative);
. . .

public void AddCustomUIToXlsm(
            string customUISource,
            string targetPkgName)
        using (Package targetPackage =
            Package.Open(targetPkgName, FileMode.Open,

            // Custom UI part check.
            Uri uriCustomUIPart = new Uri("/customUI/customUI.xml",

            // Deleting Custom UI part & relationship if it exists
                uriCustomUIPart, customUIContentType);

            // Loading custom UI Dom.
            // Loading part in XmlDocument for manipulation.
            XmlDocument customUIXDoc = new XmlDocument();

            // Adding Custom UI to target package.
            PackagePart customUIPart = targetPackage.CreatePart(
                "application/xml", CompressionOption.Normal);
            using (Stream sourcePartStream = customUIPart.GetStream())

                uriCustomUIPart, TargetMode.Internal,
                "rId" + GetNextrId(targetPackage, uriMainRelPart));

            // AddCustomUIRelationship(targetPackage);  

    public void RemoveSinglePackagePart(Package targetPackage,
        Uri partUri,
        string relType)
        if (targetPackage.PartExists(partUri))

            // Removing relationship.
            foreach (PackageRelationship rel in

                // Removing relationship for single occurance part.

            // Necessary to update package to ensure 
            // correct count of relationships (that is, customUI 
            // rId is removed)
            // NOTE: minor inefficiency - no need to delete rel 
            // since re-adding part.
            // Benefits: method is generic for any deletion and 
            // potentially fixes out of sequence ids.

For many years VBA was the language for building custom Microsoft Office applications. However, most new Office applications are now written by using managed code: for example, Microsoft Visual Tools for the Microsoft Office system applications, managed add-ins, and ASP.NET applications that create server-side documents using OOXML.

In his book Professional Excel Services, Shahar Prish explains the reasons for the lack of VBA support in the product (by the way, we highly recommend this book to anyone working with Excel Services). After we worked with Excel Services and Office Open XML Formats for a while, we realized that it does not have that big of an impact. Excel Services supports user-defined functions. The example in the previous section shows that by using OOXML, you can manipulate server-side workbooks content any way you want. And if you absolutely must have VBA and can push it to the client, you can use the approach described here.

We could have made all our workbook updates necessary for rich-client printing on the server, but in this specific case, for productivity reasons, it made sense to move automation to the client application. Our users change data filters, print, change filter values again, and print again. It did not make much sense to make them repeatedly go back and forth between the browser and Excel. As you can see, SharePoint Server 2007 and Office Open XML Formats provide a new extensible Web-enabled platform with many options to build the next generation of Microsoft Office applications.

This example covers only one very specific Office Business Application scenario that takes advantage of a fraction of SharePoint Server 2007 capabilities. In Microsoft IT, we explored a very powerful combination of Excel Services and Office Open XML Formats:

  • Excel Services enables Web delivery of Excel-authored content to the authorized users.

  • It is easy to go back to the client application for scenarios that are better suited for the rich client (such as printing).

  • Office Open XML Formats enable almost any type of workbook content modification before returning it to the requesting client.

When we demonstrated the addition of the custom UI to the workbook, even experienced Microsoft IT people were impressed. We are looking forward to the future alternatives to VBA in Microsoft Office.

© 2014 Microsoft