X

An Oracle blog about BI Publisher

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 URIhtml-image-baseuriBase 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 directoryhtml-image-dirEnter 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.

Join the discussion

Comments ( 33 )
  • Babu Kumar Friday, May 23, 2008
    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?
  • sid Wednesday, May 28, 2008
    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??
  • Sujatha Monday, July 14, 2008
    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
  • Tim Monday, July 14, 2008
    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
  • Rahul Monday, July 21, 2008
    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
  • Rahul Thursday, July 31, 2008
    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
  • Tim Monday, August 4, 2008
    @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
  • Rahul Wednesday, August 13, 2008
    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
  • Nitin Tuesday, August 19, 2008
    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
  • Pradeep Thursday, August 21, 2008
    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
  • Thomas Monday, February 9, 2009
    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
  • Girish Tuesday, March 10, 2009
    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.
  • chaitanya Sagar, Excel Expert Sunday, April 12, 2009
    Even I has these doughts before, right now everything is solved. But it would been nice if the fromule and macros are supported.
  • Rudra Thursday, May 14, 2009
    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?
  • Paul MacDonald Monday, July 6, 2009
    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
  • Shalini Goel Monday, October 12, 2009
    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
  • Ilene Vu Friday, December 18, 2009
    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!
  • Laura Ponder Monday, December 21, 2009
    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
  • BB Wednesday, January 27, 2010
    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
  • Aviansh Z Tuesday, May 11, 2010
    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
  • MLMoushey Monday, August 2, 2010
    @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
  • cool Wednesday, September 1, 2010
    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?
  • Jonesboro Landscaping Thursday, September 2, 2010
    Thank you for a great post
  • cool Thursday, September 16, 2010
    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
  • Hudson Valley Landscaping Saturday, September 25, 2010
    This was a great read although a few points were better than others. Do you mind if i re-tweet? Awesome Read.
  • Thu Tran Tuesday, May 24, 2011
    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
  • satya Tuesday, November 29, 2011

    @Laura did you find resolution to ur issue?

    Regards

    Satya


  • Parag Bhabal Tuesday, February 14, 2012

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

    Regards,

    Parag


  • Mallik Thursday, November 8, 2012

    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.


  • guest Monday, October 14, 2013

    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?


  • guest Monday, October 14, 2013

    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?


  • Satish Monday, September 8, 2014

    Hi Tim,

    I have a query on BI Publisher Excel output, we are currently using 10.1.3.4 version of BI Publisher edition.

    My Doubt is : How do we get rid of empty spaces in the Excel output , these empty space are exactly not the empty ones but these are basically the conditions which i have written based on requirement(For Ex: IF Condition = true then display table1 end if if condition 2 = true then display table2 but here result is coming up properly in PDF without any empty space isue but when i try to generate output in Excel it takes alot of empty space to header and table ) how can i overcome this issue?


  • guest Friday, March 17, 2017

    HI,

    I have an RTF template for report output and an XML data template. When I run the report and generate a PDF - everything looks fine.

    But when I generate the output in EXCEL , then sorting and manipulating data does not work . It shows an error that cells are merged when I am trying to sort the data in excel output. Is there any workaround for this?


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