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.

Comments:

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 on May 23, 2008 at 04:09 AM MDT #

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 on May 28, 2008 at 12:20 PM MDT #

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 on July 13, 2008 at 10:55 PM MDT #

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 on July 14, 2008 at 02:35 PM MDT #

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 on July 21, 2008 at 03:54 PM MDT #

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 on July 31, 2008 at 04:15 PM MDT #

@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 on August 04, 2008 at 02:04 AM MDT #

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 on August 12, 2008 at 08:11 PM MDT #

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 on August 19, 2008 at 09:10 AM MDT #

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 on August 21, 2008 at 05:57 AM MDT #

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 on February 09, 2009 at 07:43 AM MST #

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 on March 10, 2009 at 01:23 PM MDT #

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 on April 12, 2009 at 01:57 PM MDT #

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 on May 13, 2009 at 06:12 PM MDT #

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 on July 06, 2009 at 05:33 AM MDT #

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 on October 12, 2009 at 04:13 PM MDT #

When running an xml report from Concurrent Manager to output to excel, report is done, xhtml is open, then when the business users want to modify this spreadsheet (ex. adding a column) it takes a long time to accomplish. What can I do to make it faster for the users? Thanks!

Posted by Ilene Vu on December 18, 2009 at 03:42 AM MST #

I have found that if you remove the wrap text option from the cells in Excel, it speeds up adding columns. Unfortunately, I have not found a way to get the publisher to read the MS Word settings in the rtf for Wrap Text. Has anyone found a way? Thanks, Laura

Posted by Laura Ponder on December 21, 2009 at 10:13 AM MST #

Hi, I have a xml publisher report set for excel output. The report has to show amounts and totals in the end. The figures show correctly if the Territory is UK or America. But it does not show totals when Territory is Germany because then the numbers show in different format ( #.###,00) which are not recognised as numbers in XHTML ( excel output) generated. Any idea how we can fix this? Thanks BB

Posted by BB on January 27, 2010 at 01:12 AM MST #

Hi , I need to develop Report in Excel Output containing 73 columns, but one cannot create RTF template if number of column is more than 64 -- Limitation. Is there any other alternative to RTF template? Could anyone please help ,what approach I need to follow to generate Excel Report showing all 73 columns. Thanks, Avinash Z

Posted by Aviansh Z on May 10, 2010 at 11:21 PM MDT #

@Aviansh Z: Hi, The 64 column limitation exists in MS Word tables. I tried nested tables in my RTF to see I could effectively exceed that limit but no sucess. I couldn't come up with a way to exceed it using and RTF template so I developed an XSL template to replace the RTF one. The XSL template has no column limitations. Our report is now nearly 90 columns wide and works fine. -Mike

Posted by MLMoushey on August 02, 2010 at 05:06 AM MDT #

There are lot of requests from users regarding the below issue. does any one have a solution for this: I have found that if you remove the wrap text option from the cells in Excel, it speeds up adding columns. Unfortunately, I have not found a way to get the publisher to read the MS Word settings in the rtf for Wrap Text. Has anyone found a way?

Posted by cool on September 01, 2010 at 01:19 AM MDT #

Thank you for a great post

Posted by Jonesboro Landscaping on September 02, 2010 at 04:09 AM MDT #

Can anyone help with the below wrap text when and excel file has been generated via rtf template how to use excel template when there are more than 65 columns

Posted by cool on September 16, 2010 at 10:09 AM MDT #

This was a great read although a few points were better than others. Do you mind if i re-tweet? Awesome Read.

Posted by Hudson Valley Landscaping on September 25, 2010 at 03:36 PM MDT #

I have a problem with the excel output generate from XML. The data come in from the system like a long text: Description 3 year maintenance contract for Powerware UPS systems located at 340 -Albert Street, 3rd floor server room. S/N EA212CBA07 Year 1: $7830 Year 2: $8065 Year 3: $8307 It shown 4 rows in the excel output. How can I make them all in one row at the output like show below: Description 3 year maintenance contract for Powerware UPS systems located at 340 -Albert Street, 3rd floor server room. S/N EA212CBA07 Year 1: $7830 Year 2: $8065 Year 3: $8307 Pleae help. Thank you in advanced. Thu Tran

Posted by Thu Tran on May 24, 2011 at 01:57 AM MDT #

@Laura did you find resolution to ur issue?

Regards
Satya

Posted by satya on November 29, 2011 at 01:31 PM MST #

One more limitation of excel i think is that it does not offer page breaks. Am i wrong in this account?

Regards,
Parag

Posted by Parag Bhabal on February 14, 2012 at 08:29 AM MST #

We have a workaround to print period_name MON-YY format in excel tempalte, what we did was in rtf template we have added a '.' with text color as 'white' that way the data in the excel is .MON-YY and excel doesn't automatically format it. I hope it helps.

Posted by Mallik on November 08, 2012 at 06:10 AM MST #

Can we write Macros in Excel Template when we design it, If its possible, then can we reference the columns from the dataset like 'SAL' or 'DEPTNO' in the macros for calculation purpose?

If its possible, please let me know how to do it, I am unable to reference the columns from dataset in Excel template Macros, actually that column is not used in the table at all, so I cant give the Column no. Can this be solved by Macro?

Posted by guest on October 14, 2013 at 12:21 AM MDT #

Can we write Macros in Excel Template when we design it, If its possible, then can we reference the columns from the dataset like 'SAL' or 'DEPTNO' in the macros for calculation purpose?

If its possible, please let me know how to do it, I am unable to reference the columns from dataset in Excel template Macros, actually that column is not used in the table at all, so I cant give the Column no. Can this be solved by Macro?

Posted by guest on October 14, 2013 at 12:24 AM 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