Wednesday Dec 07, 2011

Excel 2007 Warnings!

As many of you have found out, everything is not as it seems with the Excel output that gets generated from an RTF template. It has non-Excel like limitations and the files are quite big. Those of you using Excel 2007 will have noticed another niggle. When you open an 'Excel' output you get a warning along the lines of:

The file you are trying to open, '*.XLS', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

This is down to BIP actually generating HTML and setting the mime type and extension to get Excel to open it. This is the cause of the limitations and the cause of the warnings. The limitations can be addressed by using an Excel template more on those in the user docs and elsewhere in this blog. Addressing the warning is going to require a modification to your users' registries.

Huge thanks to Abhishek Gupta for digging up a note from Oracle Support on it and thanks to whoever wrote the original. The note number is 1077728.1. I have uploaded Abhishek's snippet form the note here.

Update: Leslie has reminded me; for she is my Jimney Cricket to my Pinocchio, constantly prodding my conscious :0)
As an additional option in 11.1.1.5 Excel 2007 is a new output type from RTF that generates .xlsx.
Excel 2007 is a new output type available for reports. When selected, BI Publisher generates the output in Excel XML format (.xlsx). If you have Excel 2007 or later installed, this option provides the best preservation of layout and formatting.

As noted in the comments, the Excel templates generate binary xls output not xlsx format and therefore have xls limitations in their abilities.

Friday Sep 02, 2011

Blog Post Bites Man!

Its funny how a post you wrote a few years ago can come back and bite you on the bum, rather hard. Im not even going to link to the post. Suffice to say that, it covered how you might write an XSL template to generate Excel output that was better and more robust and offered more features than the Excel generated by the RTF templates. Please, dont go and look for it, it will end in tears. In fact I ought to delete it except it still has some merit, if only in the fact that it covers a little XSL code that is still useful these days in Publisher.

But that post came and bit me, a customer found it and not knowing about the newer; more shiny and altogether more friendly Excel templates, gave it a whirl. They struggled to get it working and consequently logged an SR with support. Who found me and asked the obvious, I of course did not 'click' as to what context they were running the report in and came back with the glib reply that 'it works for me!' Not very helpful I know but I did follow up.

You see in the small print of that post is a caveat about being able to run and then view the output. The XSL created XML not an xls file so the user needed to download the file and force Excel to open it. Trying to view it from the report viewer interface resulted in misery for the user ... lesson for me, make my small print larger!

Once we had clarified what was actually needed, we turned the customer on to the all singing, all dancing lead on a Broadway show, Excel templates. Now I'll be the first to admit, we still do not have any formal doc out there on them, it's coming I promise.
Update: Leslie has has slapped me upside the head and reminded me that the 11g doc is available here. For you 10g'ers, they are documented in the new new features guide here.

In lieu of that, Robear (super support person) has put together a really great Note on building multisheet Excel templates. Packed full of info and more importantly examples - check it out on Oracle support site here.  Yes its on the flash site but it will come up eventually I promise, if not look for

Note: 1352000.1 : BI Publisher: How to create Multisheet in Single Excel file using Excel Template

If per chance, you are in Boulder, CO this Saturday (9/3) and want to see a 'not quite so fat as he used to be guy' swim (Im not bad), ride (I'm awesome) and run (I know I'll be crawling again) around Boulder Reservoir drop by. Not the most inviting image I know but we can have a laugh and joke about it afterwards and I promise to wear a shirt. We hit the water at 2pm! If you come, please bring, oxygen, beer and a spanking new Cervelo P4 .... I'm dribbling! I'll be the bald guy, trying to look slim in lycra shorts, looking even sillier in a swim cap. Hang on, that's what most of us look like ... just shout TIMMMMM!

Wednesday Mar 03, 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.

 

Tuesday Jul 07, 2009

Better Excel Output

[Read More]

Monday May 05, 2008

Excel Limitations

[Read More]
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