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.

 

Comments:

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.

Posted by Satti on March 12, 2010 at 02:08 AM MST #

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 ...

Posted by Marco on March 15, 2010 at 07:42 PM MDT #

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?

Posted by Kavipriya on March 24, 2010 at 09:50 PM MDT #

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.

Posted by anal on April 21, 2010 at 07:08 PM MDT #

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)

Posted by Janel on May 12, 2010 at 08:51 AM MDT #

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.

Posted by Shashi Bhushan on May 25, 2010 at 11:04 PM MDT #

just got my iphone and i love it!!! cant get away from the app store though LOL

Posted by Clinton Tusa on July 18, 2010 at 12:20 AM MDT #

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!

Posted by Jin on July 29, 2010 at 04:36 AM MDT #

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

Posted by Sath on August 10, 2010 at 03:21 AM MDT #

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

Posted by Muthu on August 10, 2010 at 03:05 PM MDT #

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.

Posted by Stephane Lapierre on August 14, 2010 at 09:28 PM MDT #

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

Posted by kiran on August 31, 2010 at 08:45 PM MDT #

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.

Posted by nono hair removal on September 02, 2010 at 01:51 AM MDT #

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?

Posted by Jay on October 12, 2010 at 08:44 AM MDT #

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!

Posted by Priya on November 01, 2010 at 07:41 AM MDT #

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

Posted by Dhana on August 31, 2011 at 12:00 AM MDT #

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

Posted by Dhana on August 31, 2011 at 12:24 AM MDT #

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

Posted by guest on August 31, 2011 at 12:24 AM MDT #

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

Posted by Tim on August 31, 2011 at 02:37 AM MDT #

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

Posted by guest on June 20, 2013 at 01:06 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today