Create Microsoft Office documents with XML

This blog post will demonstrate how Microsoft Office documents can be created as XML files that can be downloaded, with the use of LINQ to XML and knowing how the Office xml schemas are defined. A simple Excel document will be created as an example.

In a recent project there was a request to be able to save data presented in a web page as a downloadable Excel file. A simple solution that would avoid the use of additional assemblies is to create an xml file following the open xml standards defined for Microsoft Office Documents. With this approach any kind of office document that supports open XML can be created in a very easy way with the use of LINQ to XML. 

To have a better understanding and find in detail what can be achieved with open xml you can check the Microsoft Office 2003 Edition XML Schema References. You also need to understand how XML is structured and have a basic understanding of LINQ so that you can find elements inside elements in the xml structure.

The basic XML structure shown below is what will be created.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<?mso-application progid="Excel.Sheet"?>
<ss:Workbook 
	xmlns="urn:schemas-microsoft-com:office:spreadsheet" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
	xmlns:x="urn:schemas-microsoft-com:office:excel" 
	xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml"
	xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
	xmlns:o="urn:schemas-microsoft-com:office:office"
	xmlns:html="http://www.w3.org/TR/REC-html40"
	xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">
	<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office" />
	<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel" />
	<ss:Worksheet ss:Name="Sheet 1">
		<ss:Table>
			<ss:Row>
				<ss:Cell>
				<ss:Data ss:Type="String">Sample value in A1</ss:Data>
				</ss:Cell>
				<ss:Cell>
				<ss:Data ss:Type="String">Sample value in B1</ss:Data>
				</ss:Cell>
				<ss:Cell>
				<ss:Data ss:Type="String">Sample value in C1</ss:Data>
				</ss:Cell>
			</ss:Row>
		</ss:Table>
	</ss:Worksheet>
</ss:Workbook>

And here is how it will be shown when opened in Excel.
Excel File

The code will be presented in a way that can be distributed in more internal functions so as to achieve better handling if more options are required. In the beginning of the method we define the namespaces for the XML plus we create the first part of the XML which is more or less standard code.

protected void SaveToOpenXmlDocument()
{
	//Define schemas for the XML
	XNamespace x = "urn:schemas-microsoft-com:office:excel";
	XNamespace ss = "urn:schemas-microsoft-com:office:spreadsheet";
	XNamespace x2 = "http://schemas.microsoft.com/office/excel/2003/xml";
	XNamespace c = "urn:schemas-microsoft-com:office:component:spreadsheet";
	XNamespace xsi = "http://www.w3.org/2001/XMLSchema-instance";
	XNamespace o = "urn:schemas-microsoft-com:office:office";
	XNamespace html = "http://www.w3.org/TR/REC-html40";
	//Beginning of XDocument
	XDocument doc = new XDocument( new XDeclaration("1.0", "UTF-8", "yes"),
	new XProcessingInstruction("mso-application", "progid=\"Excel.Sheet\""),
	new XElement(ss + "Workbook",
	//Define the Workbook node with all the required attributes
	new XAttribute("xmlns", ss.NamespaceName),
	new XAttribute(XNamespace.Xmlns + "xsi", xsi.NamespaceName),
	new XAttribute(XNamespace.Xmlns + "x", x.NamespaceName),
	new XAttribute(XNamespace.Xmlns + "x2", x2.NamespaceName),
	new XAttribute(XNamespace.Xmlns + "ss", ss.NamespaceName),
	new XAttribute(XNamespace.Xmlns + "o", o.NamespaceName),
	new XAttribute(XNamespace.Xmlns + "html", html.NamespaceName),
	new XAttribute(XNamespace.Xmlns + "c", c.NamespaceName)));

Below two optional nodes are added that can be totally omitted. DocumentSettings can have as child nodes information like title, subject, author,company etc and ExcelWorkBook contains properties for the created workbook as child nodes. Another parent node, Styles, can be added here that can handle styles that are applied inside the document.

XElement DocumentProperties = new XElement(o + "DocumentProperties",  new XAttribute("xmlns", o.NamespaceName));
doc.Elements(ss + "Workbook").Last().Add(DocumentProperties);

XElement ExcelWorkBook = new XElement(x + "ExcelWorkbook", new XAttribute("xmlns", x.NamespaceName));doc.Elements(ss + "Workbook").Last().Add(ExcelWorkBook);

 

Now we can start writing the main code that includes worksheet and table. Here we can name our worksheet and can define more that one in the same level of node with a loop.

XElement WorkSheet = new XElement("Worksheet", new XAttribute(ss + "Name", "Sheet 1"), new XElement("Table"));
doc.Element(ss + "Workbook").Element(x + "ExcelWorkbook").AddAfterSelf(WorkSheet);

Finally a table can have rows and rows can have cells.By adding these final details the document is almost ready.

XElement Row = new XElement("Row",
 new XElement("Cell", new XElement("Data", new XAttribute(ss + "Type", "String"), "Sample value in A1")),
 new XElement("Cell", new XElement("Data", new XAttribute(ss + "Type", "String"), "Sample value in B1")),
 new XElement("Cell", new XElement("Data", new XAttribute(ss + "Type", "String"), "Sample value in C1")) );
doc.Descendants("Table").Last().Add(Row);

While the document is now ready a loop check is needed to eliminate any xlmns="" elements inside the document. This can be done easily like so.

foreach (XElement xel in doc.Root.DescendantsAndSelf())
{
 if (xel.Name.Namespace == string.Empty)
 {
 xel.Name = ss + xel.Name.LocalName;
 }
}

Finally we have the option either to save the file or download it. Here we will show how it can be downloaded. Mind that since we are using Response the first line containing the XML declaration is omitted and thus the document is unreadable. However with the following trick this can be fixed. 

Response.AddHeader("Content-disposition", "attachment; filename=MyExcelDoc.xml")
Response.ContentType = "application/vnd.ms-excel";
Response.Write(string.Concat(doc.Declaration.ToString(), "\r\n", doc.ToString()))
Response.End(); 
}

To sum up this is a very neat way to create an excel document and while this example is very simple the xml schemas provide information to achieve detailed and rich excel documents. This can also be a guide to create other types of office documents as it presents a standard method and requires little deviation in the way of main information is presented.

Create a visual TinyMCE Plugin

This post will guide you how to extend the TinyMCE editor EPiServer with a custom button and dialog.

Create a new class and apply the attribute TinyMCEPluginButton to it. This will register the plugin as a visual plugin, which is exactly what we need for this scenario.  

$ SRCustomTablePlugin.cs

using EPiServer.Editor.TinyMCE;

namespace EPiServer.Plugins.TinyMCE
{
    [TinyMCEPluginButton(
        PlugInName = "SRCustomTablePlugin", // Internal name and also name of the plugin folder where rest of files must be placed
        Description = "Inserts three different tables of choice",
        DisplayName = "CustomTablePlugin",
        ButtonName = "btnCustomTable", // Name of button that the user will press in UI (will be explained later)
        GroupName = "table", // Where the button will be grouped
        IconUrl = "Editor/tinymce/plugins/SRCustomTablePlugin/img/customtable.gif")]
    public class SRCustomTablePlugin
    {
    }
}

EPiServer will automatically find the class and register the plugin. So the class could be placed anywhere, but I recommend putting it with the rest of the files.

Project structure used in this example:

Project structure in Visual Studio for the client script files and html

TinyMCE in EPiServer will automatically assume (by default) that the client plugin file are located in (in this scenario) /Util/Editor/TinyMCE/Plugins/SRCustomTablePlugin/editor_plugin.js

$ editor_plugin.js

(function (tinymce) {
    tinymce.create('tinymce.plugins.SRCustomTablePlugin', {
        init: function (ed, url) {
            // Register commands
            ed.addCommand('mceCustomTable', function () {
                ed.windowManager.open({
                    file: url + '/dialog.html',
                    width: 320,
                    height: 200,
                    inline: 1
                }, {
                    plugin_url: url
                });
            });

            // Register buttons
            ed.addButton('btnCustomTable',
                {
                    title: 'Insert Custom Table', 
                    cmd: 'mceCustomTable',
                    image: '/util/editor/tinymce/plugins/srcustomtableplugin/img/customtable.gif'
                });
        },

        getInfo: function () {
            return {
                longname: 'Custom Tables',
                author: 'SoftResource AB',
                authorurl: 'http://www.softresource.se',
                infourl: 'http://www.softresource.se',
                version: tinymce.majorVersion + "." + tinymce.minorVersion
            };
        }
    });

    // Register plugin
    tinymce.PluginManager.add('SRCustomTablePlugin', tinymce.plugins.SRCustomTablePlugin);
})(tinymce);

This code will init the plugin, so in this example we will register a button (which will be available for the toolbar) and a button command (that will open dialog.html).
We could do lots of other stuff here aswell, so check out the tinyMce documentation!
Now we are free to do normal html/js actions in the dialog.html.

Whats important is that the dialog includes tiny_mce_popup.js so we can access the active editor and manipulate it.

$ dialog.html

<script type="text/javascript" src="../../tiny_mce_popup.js"></script>

We also have a button which we bind a onclick function to:

$ dialog.html

<input type="button" onclick="InsertTable()" value="Insert Table"/>

$ script.js

function InsertTable(){
  var activeEditor = tinyMCEPopup.editor;
  var htmlToInsert = "<tbody><tr class="bluebackground"><td></td></tr><tr><td></td></tr></tbody>";
  tinyMCEPopup.executeCommand('mceInsertContent', false, activeEditor.dom.createHTML('table',{width: 100%} , htmlToInsert);
  tinyMCEPopup.close();
}

So when we, in the dialog popup, click Insert Table we populate the active editor with the generated html code.

All we need to do now is enable the button for the TinyMCE Editor. Go to Admin Mode > Page Type > Edit Custom Property Types.
Edit XHTML String (>255) and create/edit a setting. Include the newly created button in the configuration and save.

And here is the result! 
Custom content inserted into the textarea

With this technique you could do all sorts of cool plugins to the TinyMCE editor and not just for EPiServer.

Create non-visual TinyMCE Plugin: Paste as Plain Text

For a customer we wanted the TinyMCE editor in EPiServer to paste everything from the clipboard as Plain Text. The customer copy/pastes from Word alot and didn't want the formatting to transcend into EPiServer.

Create a new class and apply the attribute TinyMCEPluginNonVisual to it. This class can be placed anywhere in the project since EPiServer scans all loaded assemblies for classes with Plugin-attributes.

using EPiServer.Editor.TinyMCE;

namespace Customer.Util.Editor.TinyMCE.Plugins
{
    [TinyMCEPluginNonVisual(
        PlugInName = "PlainTextPastePlugin", 
        Description = "Removes all formatting when pasting from clipboard.",
        DisplayName = "PlainTextPaste",
        DefaultEnabled = true,
        EditorInitConfigurationOptions = 
        @"{
            paste_text_sticky : true
        }"
        )]
    public class PlainTextPastePlugin
    {
    }
}

We configured the plugin to be DefaultEnabled so that it affects all the text fields.
The magic is in the EditorInitConfigurationOptions and paste_text_sticky : true. This will remove all formatting from the Paste.

After a testrun in IE everything seemed to work just fine. But in Firefox the whole TinyMCE editor crashed! It did not even display, i.e. uneditable.

Quick-look in FireBug we could see that TinyMCE still wanted a editor_plugin.js file, even though it's a NonVisual plugin.

The file TinyMCE was looking for was /util/Editor/TinyMCE/Plugins/PlainTextPastePlugin/editor_plugin.js
We are using this default location in this example.
Folderview for editor_plugin.js

We added this to the project structure to override the VPP folder in c:\program files...

editor_plugin.js

(function (tinymce) {
    tinymce.create('tinymce.plugins.PlainTextPastePlugin', {
        init: function (ed, url) {
        },

        getInfo: function () {
            return {
                longname: 'Plain Text Paste',
                author: 'SoftResource AB',
                authorurl: 'http://www.softresource.se',
                infourl: 'http://www.softresource.se',
                version: tinymce.majorVersion + "." + tinymce.minorVersion
            };
        }
    });

    // Register plugin
    tinymce.PluginManager.add('PlainTextPastePlugin', tinymce.plugins.PlainTextPastePlugin);
})(tinymce);

This is just an "empty" script file which you normally would use in a Visual Plugin, which will be covered later on.

Use an animated gif on long running post backs

With all the new jQuery and client callback methods available to .net developers, it has become a lot easier to refresh the DOM of a running page without a reload. However, sometimes you just want to display an animated gif after the user clicks a button until the page reloads, in order to display some sort of visual indication to the user that something is happening.

This is not as easy as it sounds and I think I tried 5-6 options before I found a good solution.

So in this case I am importing pages to EPiServer from a sitemap XML. I was importing 820 pages and it took too much time so I needed to display some kind of animation to show that the import still was in progress.

So we have our html code. When the user clicks the btnImport we start to import the pages from the sitemap.

<div class="epi-buttonContainer">
    <span class="epi-cmsButton">
        <asp:Button ID="btnImport" 
           runat="server" 
           CssClass="epi-cmsButton-text epi-cmsButton-tools epi-cmsButton-Import" 
           Text="Import" 
           OnClick="btnImport_Click" 
           OnClientClick="showLoading();" />
    </span>
    <div id="loading" style="float: left; visibility:hidden;">
        <img src="../../Img/loading.gif" alt="" />
    </div>
</div>

We have our div containing the animation. By default we set the visibility to hidden and when the user clicks the button we use the OnClientClick event to call the javascript function showLoading(); to display the div before postback.

<script type="text/javascript">
        function showLoading() {
            setTimeout("document.getElementById('loading').style.visibility = 'visible'", 200); 
        }
</script>

As you can see we are just using ordinary JavaScript to change the visibility of the div, but we are calling the function via JavaScript setTimeout function. If we don't use the setTimeout the gif animation will just freeze, but if we set a little 200 ms wait on the function the animation will be loaded correctly. Once the post back is ready and the page is reloaded the animation will be hidden again.

This is a really easy way to display an animated gif on post back without including jQuery or another typ of AJAX.