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 comments:
Hello Sanjeev,
I have modified the package as Forms client version (6i) :
http://www.free-dev.com/create_xls4emp.htm
You can see the screenshots and the source in "i" .
Best
Friedhold
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
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: // 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
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
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
Hello mar11b
I think only excel 2003 supports xml structure. So excel 2000 may not be useful in this case.
Regards
Sanjeev
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
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
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.
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
Hi,
The package body is wrapped. If you compile it on sqlplus package will be created without error.
Regards
Sanjeev
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_DIR\a.XML
GROUP: Worksheet
TAG: Table
ATTRIB: ExpandedColumnCount
VALUE: 16
20160828-DAM-4467748
Post a Comment