Generate Excel workbook with multiple worksheets from PL/SQL

By | July 1, 2007

Generate Excel workbook with multiple worksheets from PL/SQL

Version 0.62

I am in apps environment and so assumed the presence of fnd_file package.

I am uploading a version for non-apps environment.

Please find the code Non-apps_gen_xl_xml_package.

The Apps version is here

Please do leave your comments or suggestions here or you may mail me at sanjeev.blog@gmail.com

Regards
Sanjeev

13 thoughts on “Generate Excel workbook with multiple worksheets from PL/SQL

  1. Sanjeev Sapre

    Hi Friedhold,

    That’s cool.

    I saw your blog too. But could not read it cause it is in German.

    Is there a way to read it in English ?

    Regards
    Sanjeev

    Reply
  2. Friedhold Matz

    Hello Sanjeev,

    sorry in the moment there is my blog only in German, I am going to write this blog also in English. Here is a short translation:

    Recently I had discovered the following Blog: http: // sanjeev oracle world.blogspot.com/
    and the server-sided solution introduced in it tested. Then after smaller tests (umlauts – special German characters)
    I have adapted a client version for Oracle Forms 6i: http: // http://www.free-dev.com / create_xls4emp.htm . The demo runs on Scott and provides EXCEL in 2002+ XLS files as a XML. The Sreenshots and the Sourcecode are to be found following..
    ” On ‘i’ in the link page “
    Many thanks to Sanjeev Sapre

    Friedhold

    Reply
  3. Anonymous

    Hi Sanjeev,

    Thank you so much for posting this solution. It is just what I’ve been after. There is only one problem. The organisation I work for only has Excel 2000 installed on pc’s. I’ve opened your multiple sheet sample and it only shows one sheet with multiple rows but each row populated one cell. Do you have any idea how to get around this problem? Would having Excel 2003 definately fix this?

    Regards,
    Mar11b

    Reply
  4. Mar11B

    Hi Sanjeev,

    Thank you so much for posting this solution. It is just what I’ve been after. There is only one problem. The organisation I work for only has Excel 2000 installed on pc’s. I’ve opened your multiple sheet sample and it only shows one sheet with multiple rows but each row populated one cell. Do you have any idea how to get around this problem? Would having Excel 2003 definately fix this?

    Regards,
    Mar11b

    Reply
  5. Sanjeev Sapre

    Hello mar11b
    I think only excel 2003 supports xml structure. So excel 2000 may not be useful in this case.

    Regards
    Sanjeev

    Reply
  6. najeeb

    Hi Sanjeev,
    Can you tell me is there any size limitation using your gen_xl_xml.Here when i try to pull data to excel having records more than 1000 lines my session is hanging.

    Thanks & Regards
    Najeeb.M

    Reply
  7. Sanjeev Sapre

    No there is not. We have developed very huge files, some having multiple worksheets and the size of over 100 mb.

    But there is an issue ( I will not call bug 🙂 ) as the number of cells go high, the checking of if the cell is already used takes time so try disabling the cell used check by replacing the existing code with this

    FUNCTION cell_used ( p_r IN NUMBER , p_c IN number , p_w IN VARCHAR2 ) RETURN BOOLEAN IS
    BEGIN
    RETURN FALSE ;
    END ;

    essentially I am not checking if cell is used. Let me know if this helps.

    Regards
    Sanjeev

    Reply
  8. najeeb

    Hi Sanjeev,
    After applying this it is much faster thanks a lot.I have one more doubt am generating multiple excel files using a for loop here the generated excel files are sheets&workbooks are seems to be Protected,Why is this any idea.

    Reply
  9. Abhinav

    Hi Sanjeev

    The Package Body for the Apps version does not appear to have been saved correctly – when I open the file via notepad to read it it appears to be garbage.

    Can you please re-upload the same or let me know if I am doing something wrong?

    Thanks
    Abhinav

    Reply
  10. Manish Kumar Gupta

    Hi Sanjeev,

    Im executing below code but im getting error in File Close
    ORA-20001: Env not set, ( Apps or not Apps ) Contact Support.
    ORA-06512: at "PMD_LOCAL.GEN_XL_XML", line 454
    ORA-06512: at line 7

    The XML file generated is not opening correctly in excel with error worksheet loading.
    below is the error stored in temp folder
    XML ERROR in Worksheet Setting
    REASON: Bad Value
    FILE: E:UTL_DIRa.XML
    GROUP: Worksheet
    TAG: Table
    ATTRIB: ExpandedColumnCount
    VALUE: 16

    Reply
  11. AnjiReddy

    Appreciate your efforts in giving nice solution for excel book creation using pl/sql

    I have generated an excel work book with two tabs but when i try to open it gives me the below error fortunately it accepts by clicking Yes

    I think i am missing something here.Could you please kindly check and update me.

    Error: "The file you are trying to open, 'Sample.xls', is in a different format than specified by the file extension.

    The mime type i have used is application/vnd.ms-excel.
    Generated the file using Oracle Apps

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *