Creating Excel files with Enonic CMS
Every now and then the requirement to create spreadsheets dynamically from websites comes up. There are several strategies to achieve this and also several pitfalls. This tutorial provides a simple and feasible solution to the problem!
Choosing format
Excel and other spreadsheet solutions are capable of interpreting a wide variety of file formats. So choosing the proper file format is naturally the first thing we need to address.
First of all - using one of Excel's "native" formats (typically known by the file extension xls and xlsx) is really not an option in this case as they can be hard to understand.
A typical strategy would then be to use the classical CSV (Comma Separated Values) format - which Excel easily understands. However Excel has a few problems in handing CSV - in particularily related to the UTF8 charset (which is the native output of Enonic CMS) - this causes problems with international characters. Additionally, the CSV format provides limited capacity in respect to formatting or other advanced spreadsheet features.
Good old HTML to the rescue!
As it turns out, Excel and other spreadsheet processors are actually fully capable of interpreting HTML tables - or preferably and more precisely XHTML. If we simply get Excel to open our "almost regular" html page, it should also be able to render a regular HTML table within that page as an excel spreadsheet!
Step 1: Set the Content Type header
By forcing the HTTP content-type for our page to "application/vnd.ms-excel" it should open direcly from the browser and to excel. To achieve this technically with Enonic CMS - simply create a regular xls stylesheet, producing a plain html document with a single table in the body. Next modify the <xsl:output> by adding a media-type attribute of the output element as specified below:
<xsl:output indent="yes" method="xhtml" media-type="application/vnd.ms-excel"/>
Then configure a page to use this underlying stylesheet and perform a simple request to the page with your browser - the browser will now automatically try to open the page with your default spreadsheet processor - typically Excel.
We have now solved two problems - Opening the file in excel directly, and just as important - this also solves the UTF-8 charset problems so all international characters will also be interpreted correctly by Excel.
Note: It appears that IE may not respond properly to this, still opening the document within the browser. However if simply make sure the URL to the page ends with .xsl - even IE will also force the document to open in Excel.
Step 2: Define the Excel namespace and document settings
To take advantage of some of the more advanced we need to add at least one of microsofts namespaces to our XHTML document.
<html xmlns:x="urn:schemas-microsoft-com:office:excel"> <head/> <body/> </html>
Next, add some default Excel settings for the document.
..
<head>
<!--[if gte mso 9]>
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Medlemseksport</x:Name>
<x:WorksheetOptions>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveRow>9</x:ActiveRow>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>13170</x:WindowHeight>
<x:WindowWidth>21900</x:WindowWidth>
<x:WindowTopX>120</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml>
<![endif]-->
</head>
..
Note: The XML above will not be interpreted by your browser (if displaying the document directly as HTML) due to that fact that it is actually an HTML/XML comment. This makes it convenient to actually implement your table using the browser - omitting the need to open the document in Excel for every change you make.
Step 3: Add some style
Excel supports cell formatting in that is pretty much similar to plain css! Below is a basic sample which will do for a start. Include this within the head part of your HTML document.
table {
mso-displayed-decimal-separator:"\,";
mso-displayed-thousand-separator:" ";
}
@page {
margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;
}
tr {
mso-height-source:auto;
}
col {
mso-width-source:auto;
}
br {
mso-data-placement:same-cell;
}
td {
mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Verdana;
mso-generic-font-family:auto;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;
}
.longdate {
mso-style-parent:style0;
mso-number-format:"General Date";
}
.shortdate {
mso-style-parent:style0;
mso-font-charset:0;
mso-number-format:"Short Date";
white-space:normal;
}
.number {
mso-style-parent:style0;
mso-font-charset:0;
mso-number-format:"0";
white-space:normal;
}
.center {
mso-style-parent:style0;
font-weight:700;
text-align:center;
}
.right {
mso-style-parent:style0;
text-align:right;
}
.header {
mso-style-parent:style0;
font-weight:700;
}
Now simply add the classes you like to the specific parts of your table, and voila!
"Advanced" Excel features
Fortunately, since the early days of Office 2000 (aka Office 9) - Microsoft has implemented support for defining and utilizing more advanced features of Excel - amongst others formulas, cell formatting and styling in combination with such XML formats as this.
Inline formulas
Excel provides a simple solution to adding inline formulas to your table. Just add an x:fmla attribute to the cell - containing the formula and it should work just like that! Naturally you can now also generate dynamic functions just like you've generated the table itself :)
<tr> <td x:fmla="=sum(a1:a10)">0</td> </tr>
Forcing cell type interpretation
A common problems when opening up non-native Excel documents (or even native ones) is the interpretation of the value contained within a cell. A typical problem is that Excel tends to be way to "smart" and for instance removes all leading zero's from a string (for instance an ID - thinking it's a number), messing up dates etc, etc.
Fortunately for us, we have already done our homework by adding all settings necessary in order to specify how excel should interpret our table.
Simply by adding an x:str attribute to the part of the table we desire - this will instruct Excel to interpret it as a string. An other attribute you may use is for instance x:num (number).
To have Excel interpret the entire table as string, simply add the attribute to the table element:
<table x:str=""> .. </table>
Finally - wrapping it all in XSLT
Finally, here's a complete XSLT sample which will generate the a basic table. All you have to do is to customize the table generated to your requirements - good luck! :)
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet exclude-result-prefixes="saxon xs xdt xsl portal" version="2.0"
xmlns:portal="http://www.enonic.com/cms/xslt/portal"
xmlns:saxon="http://icl.com/saxon"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdt="http://www.w3.org/2003/11/xpath-datatypes"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:x="urn:schemas-microsoft-com:office:excel">
<xsl:output indent="yes" method="xhtml" media-type="application/vnd.ms-excel"
omit-xml-declaration="yes"/>
<xsl:template match="/">
<html>
<head>
<xsl:call-template name="styles"/>
<title>My Generated Excel file</title>
</head>
<body>
<table border="0" cellpadding="0" cellspacing="0" x:str="">
<tr>
<th>ContentKey</th>
<th>DisplayName</th>
<th>Created</th>
</tr>
<xsl:apply-templates select="/result/contents/content"/>
</table>
</body>
</html>
</xsl:template>
<xsl:template match="content">
<tr>
<td>
<xsl:value-of select="@key"/>
</td>
<td>
<xsl:value-of select="title"/>
</td>
<td>
<xsl:value-of select="@created"/>
</td>
</tr>
</xsl:template>
<xsl:template name="styles">
<style>
table {
mso-displayed-decimal-separator:"\,";
mso-displayed-thousand-separator:" ";
}
@page {
margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;
}
tr {
mso-height-source:auto;
}
col {
mso-width-source:auto;
}
br {
mso-data-placement:same-cell;
}
td {
mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Verdana;
mso-generic-font-family:auto;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;
}
.longdate {
mso-style-parent:style0;
mso-number-format:"General Date";
}
.shortdate {
mso-style-parent:style0;
mso-font-charset:0;
mso-number-format:"Short Date";
white-space:normal;
}
.number {
mso-style-parent:style0;
mso-font-charset:0;
mso-number-format:"0";
white-space:normal;
}
.center {
mso-style-parent:style0;
font-weight:700;
text-align:center;
}
.right {
mso-style-parent:style0;
text-align:right;
}
.header {
mso-style-parent:style0;
font-weight:700;
}
</style>
<xsl:comment>
<![CDATA[
[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Medlemseksport</x:Name>
<x:WorksheetOptions>
<x:Selected/>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
<x:ActiveRow>9</x:ActiveRow>
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>13170</x:WindowHeight>
<x:WindowWidth>21900</x:WindowWidth>
<x:WindowTopX>120</x:WindowTopX>
<x:WindowTopY>60</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]
]]>
</xsl:comment>
</xsl:template>
</xsl:stylesheet>
Change to mobile version
Comments
If you want to comment on this article you need to be logged in.