As many of you know BIP can generate Excel output from RTF template. However, being able to generate a multi-sheet Excel output is a bit more tricky.
For those of you using the standalone release the Excel Analyzer button can help you out. You can build Excel templates that can then be loaded back to the server and used as a template, at runtime generating binary multisheet Excel output. These templates rely on the data being dropped into a single worksheet and then building new sheets on top of the first worksheet. this approach provides masses of freedom in terms of adding native Excel functionality. However, you do need to generate flat data for the base sheet.
For those of you that don't use a standalone release and don't generate flattened data, what your options?
From around the Excel 2002, it has supported an XML format. Thankfully new versions of Excel support the older XML formats. I got a question today asking how do I create an XSL template to generate multisheet Excel output. It took some digging to find information about the Excel XML format. Being the consummate lazybones, I went off and found an XSLT stylesheet to get me started. It was just a simple case of then modifying that to work with my data and to get multiple sheets.
BI Publisher does not supply any XSL editor, because I'm so hard core, I used notepad. But there are several exercises out there some of them are even free, just choose your poison.
The basic structure of Excel XML is as follows:
<Workbook>
<Styles>
<Style>
<Alignment/>
<Borders>
<Border/>
</Borders>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Names>
<NamedRange/>
</Names>
<Worksheet>
<Names>
<NamedRange/>
</Names>
<Table>
<Column/>
<Row>
<Cell>
<NamedCell/>
<Data>
<Font/>
<B/>
<I/>
<U/>
<S/>
<Sub/>
<Sup/>
<Span/>
</Data>
<PhoneticText/>
<Comment>
<Data>
<Font/>
<B/>
<I/>
<U/>
<S/>
<Sub/>
<Sup/>
<Span/>
</Data>
</Comment>
<SmartTags>
<stN:SmartTag/>
</SmartTags>
</Cell>
</Row>
</Table>
<WorksheetOptions>
<DisplayCustomHeaders/>
</WorksheetOptions>
<WorksheetOptions>
<PageSetup>
<Layout/>
<PageMargins/>
<Header/>
<Footer/>
</PageSetup>
</WorksheetOptions>
<AutoFilter>
<AutoFilterColumn>
<AutoFilterCondition/>
<AutoFilterAnd>
<AutoFilterCondition/>
</AutoFilterAnd>
<AutoFilterOr>
<AutoFilterCondition/>
</AutoFilterOr>
</AutoFilterColumn>
</AutoFilter>
</Worksheet>
<ComponentOptions>
<Toolbar>
<HideOfficeLogo/>
</Toolbar>
</ComponentOptions>
<SmartTagType/>
</Workbook>
It's a big structure, but I doubt you'll need to use that Much of it. You can see how you can build the loop to generate multiple worksheets. Here's my complete XSLT stylesheet, I'll work through some of the features you can understand them better.
Here's the data I'm working with:
<EMPLOYEES>
<LIST_G_DEPT>
<G_DEPT>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<LIST_G_EMP>
<G_EMP>
<EMPLOYEE_ID>200</EMPLOYEE_ID>
<EMP_NAME>Jennifer Whalen</EMP_NAME>
<EMAIL>JWHALEN</EMAIL>
<PHONE_NUMBER>515.123.4444</PHONE_NUMBER>
<HIRE_DATE>1987-09-17T00:00:00.000-06:00</HIRE_DATE>
<SALARY>4400</SALARY>
</G_EMP>
</LIST_G_EMP>
<TOTAL_EMPS>1</TOTAL_EMPS>
<TOTAL_SALARY>4400</TOTAL_SALARY>
<AVG_SALARY>4400</AVG_SALARY>
<MAX_SALARY>4400</MAX_SALARY>
<MIN_SALARY>4400</MIN_SALARY>
</G_DEPT>
...
Just a employee by department data set with some structure provided by a data template.
<xsl:stylesheet version="1.0" xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="urn:my-scripts"
xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<xsl:output method="xml" encoding="utf-8" indent="yes" omit-xml-declaration="no"/>
<xsl:template match="/">
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<ss:Styles>
<ss:Style ss:ID="Default" ss:Name="Normal">
<ss:Font ss:Color="black" ss:Size="10" ss:Bold="1" ss:FontName="Arial"/>
</ss:Style>
<ss:Style ss:ID="x1">
<ss:Font ss:Color="black" ss:Size="10" ss:FontName="Arial"/>
</ss:Style>
<ss:Style ss:ID="xdo2">
<ss:Font ss:Color="black" ss:Size="10" ss:FontName="Arial"/>
<ss:NumberFormat Format="$#,##0;[Red]$#,##0" Bold="0" />
</ss:Style>
</ss:Styles>
<xsl:apply-templates select="EMPLOYEES"/>
</Workbook>
</xsl:template>
<xsl:template match="EMPLOYEES">
<xsl:for-each select=".//G_DEPT">
<Worksheet Name="{.//DEPARTMENT_NAME}">
<Table x:FullColumns="1" x:FullRows="1">
<Row>
<Cell>
<Data ss:Type="String">Employee ID</Data>
</Cell>
<Cell>
<Data ss:Type="String">Employee Name</Data>
</Cell>
<Cell>
<Data ss:Type="String">Email</Data>
</Cell>
<Cell>
<Data ss:Type="String">Phone Number</Data>
</Cell>
<Cell>
<Data ss:Type="String">Hire Date</Data>
</Cell>
<Cell>
<Data ss:Type="String">Salary</Data>
</Cell>
</Row>
<xsl:for-each select=".//G_EMP">
<Row>
<Cell ss:StyleID="x1">
<Data ss:Type="String">
<xsl:value-of select="EMPLOYEE_ID"/>
</Data>
</Cell>
<Cell ss:StyleID="x1">
<Data ss:Type="String">
<xsl:value-of select="EMP_NAME"/>
</Data>
</Cell>
<Cell ss:StyleID="x1">
<Data ss:Type="String">
<xsl:value-of select="EMAIL"/>
</Data>
</Cell>
<Cell ss:StyleID="x1">
<Data ss:Type="String">
<xsl:value-of select="PHONE_NUMBER"/>
</Data>
</Cell>
<Cell ss:StyleID="x1">
<Data ss:Type="String">
<xsl:value-of select="substring(HIRE_DATE,1,10)"/>
</Data>
</Cell>
<Cell ss:StyleID="xdo2">
<Data ss:Type="Number">
<xsl:value-of select="SALARY"/>
</Data>
</Cell>
</Row>
</xsl:for-each>
</Table>
</Worksheet>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
We start with some standard style sheet declarations about the output format, the encoding, etc.
- here there are a bunch of namespace declarations. I have added in some Styles so that you can see how they are used in the worksheets. I then make a call out to the EMPLOYEES template. We could create the complete template in a contiguous fashion but just to break things out so you can see the separate sections have a separate template for the worksheets and their contents.
With the template match for EMPLOYEES, you can then see I am doing a simple for-each over the G_DEPT level in the data. Inside that loop you can see the Worksheet element.
<xsl:for-each select=".//G_DEPT">
<Worksheet Name="{.//DEPARTMENT_NAME}">
this is how we create a worksheet for every department. You can also see I can set the name of the worksheet to the department name, so it's completely dynamic.
Inside the department loop, you can see I just lay out the title row cells. Then I create a new for-each loop over the G_EMP level of the data.
<xsl:for-each select=".//G_EMP">
<Row>
<Cell StyleID="x1">
<Data Type="String">
<xsl:value-of select="EMPLOYEE_ID"/>
</Data>
</Cell>
...
I will now get a row in my spreadsheet for every employee for a given department. You'll also notice at the Cell level, I can define a style ID that references the styles defined at the workbook level.
Finally I close out my for each loops, remember you have two of them, G_DEPT and G_EMP.
That's it, it's a simple XSLT stylesheet from which you could probably build on quite easily. Microsoft has a few references to the XML format, this particular format, is for Excel 2002. As I mentioned they upwardly compatible. MSExcel XML Spreadsheet Reference
Deploying the templates is straightforward, no matter flavor of BI Publisher you running, it's just a simple case of uploading them into the template manager. There is a wrinkle thou, when running the template you are going to be generating XML output. The browser is not going to know that you have generated Excel XML and therefore needs to open Excel to view it. This can be seen as a limitation of the BI Publisher server. An enhancement to allow me to set what the output type is going to be would be great. For now, your users are going to have to export and save the XML to the client machine. Then, get Excel to open up the XML file, they will then have multisheet Excel coming from BI Publisher.
XSL template and data available here.
RSS
Email