Sunday, July 01, 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

14 comments:

Friedhold Matz said...

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

Sanjeev Sapre said...

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

Friedhold Matz said...

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

Anonymous said...

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

Mar11B said...

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

Sanjeev Sapre said...

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

Regards
Sanjeev

najeeb said...

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

Sanjeev Sapre said...

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

najeeb said...

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.

Abhinav said...

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

Sanjeev Sapre said...

Hi,

The package body is wrapped. If you compile it on sqlplus package will be created without error.

Regards
Sanjeev

Manish Kumar Gupta said...

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

AnjiReddy said...

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

Anonymous said...

20160828-DAM-4467748