X

An Oracle blog about BI Publisher

  • XSL
    March 3, 2010

MultiSheet Excel Output

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.

Update: Please make sure you upload the templates as XSL Stylesheet (HTML, XML, Text) formats rather than as XSLStylesheet (FO). This will ensure the template is processed by the appropriate engine and you do not get an error.

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.

Join the discussion

Comments ( 22 )
  • Satti Friday, March 12, 2010
    Hi Tim,
    Thanks for sharing.
    I have opened BI Publisher Template viewer, and gave the DeptEmp.xml in "data" section, XML2Excel.xsl in template section. And if i have selected output format as excel and when i click start processing, i am getting "org.xml.sax.SAXException: element Workbook is not supported yet. FO Formatting Failed" error. I am using 10.1.3.4.1 desktop tool and excel 2007. did i miss anything. Please let me know.
  • Marco Tuesday, March 16, 2010
    Hi Satti,
    where did you choose Excel as output format? When I set "Template Type" to "XSL Stylesheet (XML)" the only output formats I can choose from in the view mode are Data and XML (creates the report just fine). I'm still on 10.1.3.3.2 though.
    Great post Tim, one small addition from my side which might be helpful for someone, with the following change you can associate the XML output with Excel.
    <xsl:template match="/">

    <xsl:processing-instruction name="mso-application">progid="Excel.Sheet"</xsl:processing-instruction>

    <Workbook ...
  • Kavipriya Thursday, March 25, 2010
    I am also getting the same error as that of Satti.
    The error is:
    [032510_041058515][oracle.apps.xdo.template.FOProcessor][ERROR] End Memory: max=254MB, total=6MB, free=1MB
    [032510_041058531][oracle.apps.xdo.template.FOProcessor][EXCEPTION] org.xml.sax.SAXException: element Workbook is not supported yet.
    I am on Excel 2000 and BIP 10.1.3.4.1.
    Tim could you please guide us on this?
  • anal Thursday, April 22, 2010
    Is Vista's Parental Control a hundred percent effective tool to block porn and filter adult content? The answer is no. However, parents' fears can be alleviated by using these features effectively to block porn and adult content, monitor usage, and set time limits. There are third party filters that also do this job more effectively. The new advanced features offered by other monitoring software are emergency email notification if the computer restrictions are broken, remote control of the home computer from anywhere, video playback of usage time, and key stroke recording. These are handy for outsmarting teenagers who are usually tech savvy or if you need to monitor your child's email and chatting. But, for the younger kids, you may not need these options. If you want to read about advanced monitoring software options check out internet security page. Overall, configuring Vista's parental controls to filter content and block porn is a breeze. The system allows you the ability to ensure that their net surfing time is safe and under your supervision.
  • Janel Wednesday, May 12, 2010
    Hi Tim,
    I am using BI Publisher (not standalone) with Oracle Apps 11i. We use the add-in tool for Word to develop an .rtf template and upload it to Oracle. We don't do anything whatsoever with stylesheets. Is what you describe even possible for this kind of situation? If so, where do I even begin? :o)
  • Shashi Bhushan Wednesday, May 26, 2010
    Hi Tim,
    Thanks for sharing.
    I have opened BI Publisher Template viewer, and gave the DeptEmp.xml in "data" section, XML2Excel.xsl in template section. After that I have submitted the program, its’ completed with warning. Log file details are as follows:
    +--------------------------------------------------------------------+
    Current system time is 26-MAY-2010 06:35:09
    +--------------------------------------------------------------------+
    XDO Data Engine Version No: 5.6.3
    Resp: 52385
    Org ID : 872
    Request ID: 27045559
    All Parameters:
    Data Template Code: MULTI_SHEET_EMP_TEST
    Data Template Application Short Name: GEPSPA
    Debug Flag: N
    {}
    Calling XDO Data Engine...
    [052610_063512324][][STATEMENT] Start process Data
    [052610_063512325][][STATEMENT] Process Data ...
    [052610_063512325][][STATEMENT] Writing Data ...
    [052610_063512329][][STATEMENT] Sql Query :Q1: SELECT

    SD.DEPTNO

    as DEPARTMENT_ID,

    SD.DNAME

    as DEPARTMENT_NAME,

    SE.EMPNO

    as EMPLOYEE_ID,

    SE.ENAME

    as EMP_NAME,

    'shashi.bhushan@patni.com'

    as EMAIL,

    '9867222298'

    as PHONE_NUMBER,

    SE.HIREDATE

    as HIRE_DATE,

    SE.SAL

    as SALARY
    FROM

    SCOTT.EMP

    SE,

    SCOTT.DEPT

    SD
    WHERE

    SE.DEPTNO

    = SD.DEPTNO
    ORDER BY SD.DEPTNO
    [052610_063512345][][EVENT] Data Generation Completed...
    [052610_063512345][][EVENT] Total Data Generation Time 0.0 seconds
    +--------------------------------------------------------------------+
    Start of log messages from FND_FILE
    +--------------------------------------------------------------------+
    +--------------------------------------------------------------------+
    End of log messages from FND_FILE
    +--------------------------------------------------------------------+
    +--------------------------------------------------------------------+
    Executing request completion options...
    +------------- 1) PUBLISH -------------+
    Beginning post-processing of request 27045559 on node TSGSD4900 at 26-MAY-2010 06:35:12.
    Post-processing of request 27045559 failed at 26-MAY-2010 06:35:12 with the error message:
    One or more post-processing actions failed. Consult the OPP service log for details.
    +--------------------------------------+
    +------------- 2) PRINT -------------+
    Not printing the output of this request because post-processing failed.
    +--------------------------------------+
    Please let me know.
  • Clinton Tusa Sunday, July 18, 2010
    just got my iphone and i love it!!! cant get away from the app store though LOL
  • Jin Thursday, July 29, 2010
    Can you please let me know what is the solution for "org.xml.sax.SAXException: element Workbook is not supported yet. FO Formatting Failed" error?
    Thanks!
  • Sath Tuesday, August 10, 2010
    Hi Tim,
    I've succeded to get the above emaple working and showing output data in Excel mulitple worksheets. Thanks for the example.
    I do have one concern. I'm not able to display the company Logo/image in excel outpu, thot I refered/google for the code, But was not successful to achieve.
    First of all, can we display Images/Logo in XML-XSL template converted to Excel output. If Yes chould you please share the XSL piece of code for Image insertation.
    Thanks
    Sath
  • Muthu Tuesday, August 10, 2010
    Hi Sath,
    Can you pls tell me the steps to achieve this .
    As i am getting the same error as previously mentioned by satti and kavipriya.
    Regards
    Muthu
  • Stephane Lapierre Sunday, August 15, 2010
    I probably have same issue as Jin.
    I tried to generate the output with your examples, but I have the following message:
    XDOException element Workbook is not supported yet.
    I'm using xdo56.jar (XDO Data Engine Version No: 5.6.3
    )
    is this an issue with this release or something else is wrong ?
    thanks for your answer.
    /Stephane.
  • kiran Wednesday, September 1, 2010
    Hello Jim,
    Thank you for your detailed presentation.
    I have implemented your approach for one of our features and it works very much fine except the following remarks:
    1. For few of the columns in excel we have to display numbers starting with zero(0). And you might be aware of the problem in excel that left padded zeroes will be truncated. Therefore in order to retain the values we have used the format ' ="" '. But when excel file is generated we have this value displayed as it is i.,e if we have a value "0002" when given in the format ="0002" in a new spreadsheet it is displayed as "0002" but when we do the same using XSL it is displayed as it is ="0002". Please suggest me with an appropriate workaround for this.
    2. We have requirement to display special characters and though encoding format is changed when I try to run the XML with XSL I find special characters are lost.
    3. In the outputs we require to display amounts in Euro format i.e., decimal separator is comma (,). I did make use of the decimal separator tag and format-number function given as below:
    and

    Request you to suggest me with any workarounds for the above mentioned points if any.
    Thank you in advance.
    Regards,
    Kiran
  • nono hair removal Thursday, September 2, 2010
    I switched technicians mainly because after a few years of Intense pulsed light, I believed the results weren't worth their expense. And then I ordered the laser hair remover. They warranty that after 24 months in the case hair grows back again, the treatments are actually free of cost. They've got both laser and also Intense pulsed light, but I am planning to stick with laserlight now as Ipl didn't seem to reduce the quantity of hair coming back.
  • Jay Tuesday, October 12, 2010
    Hi Tim,
    I have implemented the above solution and it is working. However i am facing this problem:
    I have a concurrent program which generates an output of 23 MB (2 Excel Worksheets each having around 3000 records). When i click on the 'View Output' button, it opens a browser which keeps loading the file and terminates eventually.
    I can easily open the file on the server directly without any issue in Excel.
    Is there any solution?
  • Priya Monday, November 1, 2010
    hi - can you someone tell me if there is some easy to way generate this XSL templates instead of writing code like this manually?
    Any help in this is greatly appreciated.
    Thanks!
  • Dhana Wednesday, August 31, 2011

    Hi Tim,

    Thanks for sharing this Explanation.

    Very nice article.

    Here I have one question i.e if my query is having multiple groups[assume more than 4] how can we write XSL code for this.

    For Example,There is a group for company under company there are so many groups for sales reps and under sales rep group we have different groups for customers.I want to display customer information by each salesrep in each sheet in excel.How can we write XSL for this.

    Thanks,

    Dhana


  • Dhana Wednesday, August 31, 2011

    Hi Tim,

    Thanks for sharing this Explanation.

    Very nice article.

    Here I have one question i.e if my query is having multiple groups[assume more than 4] how can we write XSL code for this.

    For Example,There is a group for company under company there are so many groups for sales reps and under sales rep group we have different groups for customers.I want to display customer information by each salesrep in each sheet in excel.How can we write XSL for this.

    Thanks,

    Dhana


  • guest Wednesday, August 31, 2011

    Hi Tim,

    Thanks for sharing this Explanation.

    Very nice article.

    Here I have one question i.e if my query is having multiple groups[assume more than 4] how can we write XSL code for this.

    For Example,There is a group for company under company there are so many groups for sales reps and under sales rep group we have different groups for customers.I want to display customer information by each salesrep in each sheet in excel.How can we write XSL for this.

    Thanks,

    Dhana


  • Tim Wednesday, August 31, 2011

    For those of you using BIP 10.1.3.4.x I would recommend taking a look at the newer excel templates. They are covered elsewhere on the blog. Just search got them.

    Tim


  • guest Thursday, June 20, 2013

    Hi Jay

    Could you please send me the solution to the org.xml.sax.SAXException: element Workbook is not supported yet error

    thank you in advance


  • guest Tuesday, August 5, 2014

    Hi All,

    I can able to generate the multi sheet excel output reports but I'm not able to display the company Logo/image in excel output,can we display Images/Logo in XML-XSL template converted to Excel output. If Yes could you please share the XSL piece of code for Image insertions.

    Harish


  • oshrat Thursday, December 4, 2014

    Hi,

    I have the same problem, is it possible to display company logo/image in excel output wich generated by XSL-XML template converted to EXCEL output format?

    I found the syntax that match to XSL-FO template, but I need the correct sytax for XSL-XML

    <fo:inline>

    <fo:external-graphic content-width="248pt" content-height="33pt" src="url('http://www.oracle.com/admin/header/ora_logo.gif')" />

    </fo:inline>


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.

Recent Content