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.

Comments:

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?

Posted by Ben Mackin on December 07, 2011 at 01:12 PM MST #

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

Posted by Tim on December 08, 2011 at 02:35 AM MST #

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

Posted by guest on December 13, 2011 at 09:41 AM MST #

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

Posted by Multiverse on March 08, 2012 at 11:42 AM MST #

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

Posted by Tim Dexter on March 08, 2012 at 02:23 PM MST #

Thanks for explaining this issue

Posted by Planilhas Excel on May 06, 2012 at 04:22 PM 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