X

An Oracle blog about BI Publisher

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.

Join the discussion

Comments ( 6 )
  • Ben Mackin Wednesday, December 7, 2011

    Tim, you state "The limitations can be addressed by using an Excel template more on those in the user docs and elsewhere in this blog". While using BI Publisher 11g, I can't seem to create an Excel template that outputs to Excel 2007 format. The option is not available (only Excel or Excel 2000). The only way I have seen to create Excel 2007 output is via RTF template.

    Is there something I am just missing, or do the Excel templates not yet support Excel 2007?


  • Tim Thursday, December 8, 2011

    Hi Ben

    The Excel templates generate binary xls not xlsx formats. Excel 2007 handles that format no problem. If you use Excel 2007 to build the template you will need to save as xls (and put up with any limitations that imposes on functionality) I believe they are working on xlsx formats for a future release.

    Tim


  • guest Tuesday, December 13, 2011

    Hi Tim,

    What's the best way to turn rows into columns in MS Excel templates? Is there a way to use <?for-each@cell:GRP?> ?

    I couldn'f find a whole lot of information on dynamic column functionality in MS Excel templates. Would I have to create XSL templates to handle things like <?split-column-header?> etc. ?

    Thanks,

    Sunder


  • Multiverse Thursday, March 8, 2012

    Here is a quote of false advertising from the Oracle BI Publisher Blog from May, 2011. The blog is online at https://blogs.oracle.com/xmlpublisher/entry/bi_publisher_11g_11_1

    "

    New Output Format Type – Native Excel 2007 Output

    Now you can download your report as Excel 2007 format and open it with the latest Excel on both Windows and Mac with high fidelity formatting and layout. You can use repeating groups in your report to automatically group data in separate worksheets. With this format option the downloaded Excel file is 70% smaller compared to other Excel output types, which makes it easier to share your report in Excel with your colleagues.

    "


  • Tim Dexter Thursday, March 8, 2012

    Hi Multiverse

    Not false maybe not clear enough on the details but not false. In 11g you can now generate Excel 2007 outputs from your RTF.

    You are able to now produce multi tabbed output from your RTF templates.

    The Excel templates still only support Excel or the older Excel 2000 formats and not the later 2007 format. That I believe is in the works.

    Regards

    Tim


  • Planilhas Excel Sunday, May 6, 2012

    Thanks for explaining this issue


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