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