Scouring the forum while listening to web demos today and came across a post from 576489, a mystery poster - who are you Sir or Madam?
Anyhoo, the title of the post was 'Limitations in excel output' he/she was asking me to provide some more info on the Excel output we provide. As many of you know or have noticed - our Excel output for all flavors of BIP/XMLP is not true binary Excel output. It's actually XHTML with the mime type set to get Excel to open it. Understand its not Excel HTML but XHTML - read non lazy HTML. So, there is going to be a limit in what you can generate in the Excel output. Some Q & A:
Q1. Can I have formulae or macros in the Excel ouput?
A1. No
Q2. Can BIP use multiple sheets in the Excel output?
A2. No
Q3. What happens when the number of rows generated exceeds the Excel maximum?
A3. BIP will truncate the data
Q4. Where are the images in the Excel, I had some in my template?
A4. You need to set a couple of properties to allow use to access them. Yes, you need to be connected to the network to get the images.
| Base image URI | html-image-baseuri | Base URI which is inserted into the src attribute of the image tag before the image file name. This works only when the image is embedded in the template. |
| Image file directory | html-image-dir | Enter the directory for XML Publisher to store the image files that are embedded in the template. |
With standalone 10.1.3.4 the Excel output is actually MHTML where we can embed the images directly in the HTML file so you don't need the properties set.
Q5. Can I generate master-detail Excel outputs?
A5. Yes, you can build multi level report outputs in the Excel output.
Q6. I have an account number in my data but Excel treats it as a numuric value (right aligning, etc) - I want it left as a string value?
A6. Either add a <space> character next to the field holding the account value or use the concat function to add the space to the value i.e. concat(MYVALUE,' '). This will force Excel to treat the value as a string.
Q7. When opening an Excel output in IE7 from BIP the browser starts to open a window, then beeps and closes the window?
A7. Thanks to Ed on this one:
1. Select 'Tools' -> 'Internet Options' -> 'Security' (tab) -> 'Trusted Sites' -> 'Custom Level' (button) -> 'Downloads' from the browser menu.
2. Set 'Automatic prompting for file downloads' to 'Enable'.
3. Save the setting and close the browser window.
4. Start a new browser session and try again.
Q7. Are things going to get better with Excel formats?
A7.
a. With standalone 10.1.3.4 the Excel output is actually MHTML where we can embed the images directly in the HTML file so you don't need the properties set.
b. The standalone release also has the Excel Analyzer that allows you to create a binary Excel template that can be run on the server and delivered to users.
c. We have Excel templates in EBS R12 and discussions to get things going in 11i.
Our Excel is not true Excel and it has a limited set of functionality but for many users its enough just to get the data out into Excel. If you have more questions, post em in comments and I'll update the document.
Comments (16)
I have an stylesheet (xsl) which can create an Excel xml file (Excel 2003 XML format) form the Data XML.
Can I use this stylesheet in BI Publisher to generate the Excel output?
Posted by Babu Kumar | May 23, 2008 10:09 AM
Posted on May 23, 2008 10:09
Tim,
thanks for your post,your post was really helpful and useful,and i am the mystery poster(Sid) posted this question on the forum,
'Mystery Poster' - this made me land in a state of confussion,was something wrong tim??
Posted by sid | May 28, 2008 6:20 PM
Posted on May 28, 2008 18:20
Hi Tim,
We have a requirement to display the period name (the default format being MMM-yy) in our report.
When the report output is RTF , HTML or PDF, the output is displayed as intended. But when the same needs to be displayed in an Excel report output, it gets converted to a date.
This works fine if we have leading spaces after the field. But our requirement is not to have any spaces in the report.
Kindly suggest.
Regards,
Sujatha
Posted by Sujatha | July 14, 2008 4:55 AM
Posted on July 14, 2008 04:55
Hi Sujatha
So Excel formats to different date format to that required? Or makes a complete mess of the formatting?
Without the spaces you are limited to the html we generate and its limitations. The only option I can think of is to write a global macro that would reside on the client Excel and would reformat the date contents you want converted.
Regards
Tim
Posted by Tim | July 14, 2008 8:35 PM
Posted on July 14, 2008 20:35
Hi Tim,
I am putting Image in my Template and I want output in MS Excel format
I have created dummy image and in web tab I have given url:{hard_code_path}
It's work fine for PDF,RTF output(Image is coming in output) but when I select HTML and Excel output
It is not showing Image ,it is showing only small red color empty cross writtn 'An Image'
also when I past Image in template It is not showing in Excel output
pls note this is in EBS.
Now when I do it in oracle BI Publisher Template for Word 10.1.3.3.2
Build 115
I can see the image in output
need your help
Thanks
Rahul
Hi Rahul
I think I answered this in your other comments
Tim
Posted by Rahul | July 21, 2008 9:54 PM
Posted on July 21, 2008 21:54
Hi Tim,
I want Image to show on Excel output in EBS fo that I have done these steps
1) Base image URI = http://cvd-erp-dev-01.limac.com:8011/OA_MEDIA/comp_logo.gif
and
2) Image file directory = /u11/orafindv/findvcomn/java/oracle/apps/media/comp_logo.gif
Is it correct?
Now please suggest me what should I write in rtf Template?
shall I take dummy Image? or something in form field?
Kindly suggest
Thanks
Rahul
Posted by Rahul | July 31, 2008 10:15 PM
Posted on July 31, 2008 22:15
@Rahul
You only need to set the two properties if you are embedding an image in the RTF template directly. At runtime we extract the image and place it in the directory stated and then use the URI to access it.
IF you just want to show the image via a URL then just put a dummy image in the template and use the url: function to point to the correct image - check the user guide for details.
Regards, Tim
Posted by Tim | August 4, 2008 8:04 AM
Posted on August 4, 2008 08:04
Hi Tim
Thanks for your reply
As I want to show Image in Excel
1) I have embeded Image in RTF template
2) In Administrator Tab HTML Output >> Image file directory I have given /u11/orafindv/findvcomn/java/oracle/apps/media/
(so run time image will be stored in this path)
3)Again in In Administrator Tab HTML Output >> Base image URL I have given http://cvd-erp-dev-01.limac.com:8011/OA_MEDIA/
Here I am getting only small red cross in place of image
pls suggest where I doing wrong
Rahul
Posted by Rahul | August 13, 2008 2:11 AM
Posted on August 13, 2008 02:11
Hi Tim,
You mentioned that there are excel templates in EBS R12. The EBS R12 (12.0.0; BIP 5.6.3) I am working on doesn't provide options for excel templates. Is there some patch that needs to be applied?
Best regards,
Nitin
Posted by Nitin | August 19, 2008 3:10 PM
Posted on August 19, 2008 15:10
Hi Tim,
I am trying to generate HTML output from XMLP. My requirement is to have embedded images in the output. I mean embedded but not reference. Is there a way to achieve this as we are to send this out put as an email to the customers outside our network. If we refer to the images they will not be able to locate.
Thanks,
Pradeep
Posted by Pradeep | August 21, 2008 11:57 AM
Posted on August 21, 2008 11:57
Hi Tim,
I have a RTF template and when output is Excel following formatting gets wrong according to the customers requirements
(with PDF output everything works fine):
1/ Texts i table cells with vertical text direction gets horizontal in Excel
2/ RTF template is landscape but page layout in Excel is set to Portrait
3/ The table cell Wrap-text option is ignored
Are these issues related to the limitations in Excel output or are there any work-arounds available?
(PeopleTools 8.48 and BIP Enterprise 10.1.3.4)
Regards
Thomas
Posted by Thomas | February 9, 2009 3:43 PM
Posted on February 9, 2009 15:43
Hi Tim,
Page breaks(R12) don't work when EXCEL is set as output format, any idea on how to get this working.
Thanks,
Girish.
Posted by Girish | March 10, 2009 7:23 PM
Posted on March 10, 2009 19:23
Even I has these doughts before, right now everything is solved. But it would been nice if the fromule and macros are supported.
Posted by chaitanya Sagar, Excel Expert | April 12, 2009 7:57 PM
Posted on April 12, 2009 19:57
Hi, refering to question 4 and also to Tim's answer to Rahul , someone please explain what are the exact two properties we need to set to get the picture in excel output?
Posted by Rudra | May 14, 2009 12:12 AM
Posted on May 14, 2009 00:12
I have an EBus Suite (xdo 5.6.3) customer wanting to put
vertical text in Excel output from an RTF template.
Is this possible?
If Yes, what might be the xsl code and syntax?
thanks, Paul
Posted by Paul MacDonald | July 6, 2009 11:33 AM
Posted on July 6, 2009 11:33
Hi Tim,
Are there any properties like "pdf-security" and "pdf-open-password" for excel formats also? I want to make my excel sheets password protected and readonly. Is it possible to make excel sheets secured just like PDF reports are made in Oracle BIP?
Thanks !
Shalini
Posted by Shalini Goel | October 12, 2009 10:13 PM
Posted on October 12, 2009 22:13