Thursday, June 28, 2007

Create Excel Workbook by PL/SQL

I am moving this blog to my own word-press site. So all new blogs and updates will be published there. Please use this new link.



Further to my previous post..

I have uploaded the actual package code here
Compile the code on 9i or above and it should compile w/o error.

Package Name : gen_xl_xml
Version : 0.62

You can download samples for demo of how to create a simple and multi-sheet workbook from here

Here I explain how to use this package...

1. First step is to open the file. You do this by using the procedure

If you not using the package in Oracle Applications then use
create_excel - You need to pass the directory object and the file name to create.

If you are under Oracle Applications then use
create_excel_apps - This does not have any parameter as it writes to the out file.
You can not call both procedures at a time.

2. Then you can create styles , worksheet by using the respective procedures.

3. To Write the cells you call write_cell_char and write_Cell_num procedures
4. If you just want to apply style but no contents then use write_cell_null
5. Use set_row_height and set_column_width to set the height and width of row and column respectively.
6. At then end you call close file method. At this point the package will actually write to the file.


Debugging :

There is a package level variable debug_flag which is false by default.

If your excel file is not opening or there are any errors you may set it on and try running from sqlplus to see all the steps that the package executes like creating worksheet , setting row height etc.


Known Limitations.

1. As of now Rows have to be inserted in ascending order.
2. Not all attributes in style are available now. For Example : Borders.
3. Currently formulas are not supported. You may try passing formula strings , I have not checked this but excel's XML structure does not write the formulas that way.
4. Date format is not supported as of now.
5. Column height changes has to be inserted in the ascending order. For example you can not call height change for column c after column e.

Note : This code is not using any version specific features but if you are using 8i I think directory object is not there rather you have to use path set by utl_dir.

I request you to use this code and pass on to whoever requires it. If you get any errors please write to me and I will try to sort them out. I plan to fix the above limitations as I go ahead.

Regards
Sanjeev

116 comments:

Friedhold Matz said...

Hi Sanjeev,

great solution, many thanks.
I deactivated the lines with :

fnd_file.put_line & fnd_file.output ..

Best Friedhold

Arun DIixt said...

Hi Sanjeev,

Thanks for your scripts!
but i am facing a small issue in creating sheets.

if there is any limitation in the number of columns as i can not create sheets with more than 16 columns. report will be created but i am unable to open it. it prompts error data can not be loaded
====================
XML ERROR in Worksheet Setting
REASON: Bad Value
FILE: C:\EVEREST_HOLD.xls
GROUP: Worksheet
TAG: Table
ATTRIB: ExpandedColumnCount
VALUE: 16
=====================

Unknown said...

Hi Sanjeev,

Thanks for the solution,

I need that oracle application will open the file after i press 'view output' button.

that's mean the file name must be a variable.

do u have any idea how to do it ?

thanks,

Udi.

Unknown said...

Hi Sanjeev,
Awesome solution. Could you explain where in the master_detail.sql code, did you get to name the worksheet depending on the name of the department.

Thanks

Unknown said...

Hi Sanjeev,

I run the report in oracle application and got only the xml file.
i set the viewer option
file format: XML
MIME type : application/vnd.ms-excel
but still the only thing that have been opened is the xml browser.

what should i do more than that ?

thanks

Sanjeev Sapre said...

Hi Arun,

I think in the old version of my package I had hardcoded the value of ExpandedColumnCount
to 16. Actually it should be 256.
Please download the latest package or change the code in the old package and make it 256.

Sanjeev

Sanjeev Sapre said...

Hi Apster

I also faced this problem on one apps instance. Just to check the validity of the output I saved the output as a file and tried to open in excel and it worked. If you find any solution please publish it. I have so far no luck.

Sanjeev

Sanjeev Sapre said...

Hi Anitha

Thanks for your feedback. Yes the name of worksheet is based on department name.
You need to pass the character value to create_worksheet procedure.

Sanjeev

Anonymous said...

Hi i useed your code. It is working good. But whatever style i give is not replicated in the Excel. Can you please tell me what is the problem asap.

Sanjeev Sapre said...

Thanks for using the code. May I know some more details on the problem ?
1. Have you created the styles ?
2. check if there is no change in case of styles that you created and those you used.

Or if possible send me some part of code where you are facing problem

Regards
Sanjeev

Sourav said...

Hi Sanjeev,
The code is cool,what about the reverse process that is if we want to get data from a excel to the oracle tables.And also how this code will help us if we want to populate a templete already created in excel i.e to populate fields of a default templete.
Thanks

Sanjeev Sapre said...

Hi Sourav
In order to load data from Excel to Oracle the preferred option is to use SQL Loader. First convert the excel to csv or similar.
Also have alook at APEX. It has a cool feature to import data.

As this code generates styles etc. It is not easy to use template.


Regards
Sanjeev

Anonymous said...

Hi Sanjeev,
Thanks for the great solution i am able to use your package for my requirements. Just i have small question can we use this for Formula values. I passed the formula as a string but in the excel output showing the formula string. Can you help me how can i implement formula..

Thanks in advance
Maheshwar

Anonymous said...

Thanks for aweseme script. I found this script after trying to impmlement something myself, but so much easier to use already-made code :)

Do you know if its possible to set the file so that excel will recognise different character sets, ie. scandinavian style letters...(o with a slash)

Thanks,

Oddy

Unknown said...

Hi ,

thanks for scripts,
I have some problems with excel.
when I try to open excel it gives me error :Problems During Load...

and I can't open excel.

do you have any idea about this problem.

thanks

Sanjeev Sapre said...

Can you please post the complete error message ?

Does your data contains any of the CDATA element ? like <, > and so on ?

Regards
Sanjeev

Unknown said...

Error message is:
Problems During Load
Problems came up in the following areas during load:
- Table

These files cannot be opened because of errors. Errors are listed in .....log and the specified log file doesn't exsist in the specified folder.

My data includes Turkish characters but it doesn't include any <,> .. characters.

Sanjeev Sapre said...

Hello

This generally means that the file is not in correct excel format.
That is somewhere some tag is missing for say worksheet,row, style and so on.

Following are possible problems/solutions.

1. In my tests and use in few of projects I never faced this problem as the code takes care to close tags. But this could be a undetected bug.

2. If you have generated file on some server and you are doing FTP on desktop then check if file is completely copied. I faced this problem when files were too big.

The best course of action will be to check the error file that is generated. That will give some hint.

If Possible, send me code or part of code where you are generating excel and error file, I can have look at it.

Regards
Sanjeev

Unknown said...

Hi
I have sent the eror file and code to ypur email.

Thanks

Regards

Unknown said...

Hey aynurs,
Use the CONVERT function. I had the same problem, and UTF8 was the solution.
gen_xl_xml.write_cell_char (r, 6, 'sheet', CONVERT(rec.field,'UTF8','FROMYOURCHARSET'), null);

Sanjeev Sapre said...

hi Konstadinos,
Thanks for this inputs

Regards
Sanjeev

Rami said...

Hey Sanjeev ,

Thx so much for ur wonderful code. I had a small query as to how could the excel cell format be changed.. As in , the requirement is to display the value of the number upto 2 decimals in the cell but display its original value upto 15 or 18 decimals in the Formula Bar... It may be a little too trivial ..Sorry ...:)

Kindly advise me on the same :)

Thx a ton..

Vivek

Sanjeev Sapre said...

Thanks for your good words.

Unfortunately this is not a quick one.

Any formatting should be included in style and this should also be done in the similar way.

There are 2 ways you can do this
1. In the package you have to modify the create_style procedure.
This is time consuming but the best method.

2. Change your SQL queries to send only 2 decimals. I am not sure on this however. But should work.

I will try to add this soon.

Regards
Sanjeev

Rami said...

Thanks so much Sanjeev.. will tro and modify the create_style proc.

:)

Vivek

Melad NL said...

Hi Sanjeev,

Thanky for the code. I love it.
I modified you code to not have rows to be inserted in ascending order.

Melad

Unknown said...

Hi Sanjeev,
This is something grate, we are started using this in our production, but unfortunately some of our users having office 2000,When they try to open the generated excel the data is not displaying. We know the limitations of this package, still we would like to know is there any option to save dynamically this file for backward compatibility(for eg Excel 2000).
Regards,
Najeeb.M.

Sanjeev Sapre said...

Hello Najeeb
This is not limitation of the package rather the older ( before 2003) office versions do not support XML format.

The only way is open in 2003 somewhere, save as xls and then open in Office 2000 as Office itself is backward compatible.

Regards
Sanjeev

Unknown said...

Hi Sanjeev,
Yaa i am sorry i meant that only.As you said the only way is open in 2003 somewhere, save as xls.
Thanks alot

syed said...

Hi Sanjeev!
I created package,using Oracle Application server,for that i'm using create_excel_apps procedure;
so how can i pass the file name or where can i get my output file(pls. let me know the sample code)
syed

Sanjeev Sapre said...

Can you please elaborate, The file is generated in the directory ( object ) that you have to pass when you call create_excel procedure.

Directory object has to be created beforehand.

-Sanjeev

syed said...

I'm using gen_xl_xml.create_excel_apps; instead of gen_xl_xml.create_excel('UTL_DIR', 'employees.xls').so in create excel procedure we pass the parameter directory name & file name but in create_excel_apps procedure there is no parameter so how can i pass the directory n& file name?

Anonymous said...

Hello Sayed,
Nice library.I d like to make few contributions it mya help you and others

I have 2007excel.While I open excel file it gives warning I think this is because of "xls" extension

If I try to change "sheet1" instead of custom sheet name it cannot create excel file.

Howmany options are there for colours,styling etc? Here is the function

PROCEDURE create_style( p_style_name IN VARCHAR2
, p_fontname IN VARCHAR2 DEFAULT NULL
, p_fontcolor IN VARCHAR2 DEFAULT 'Black'
, p_fontsize IN NUMBER DEFAULT null
, p_bold IN BOOLEAN DEFAULT FALSE
, p_italic IN BOOLEAN DEFAULT FALSE
, p_underline IN VARCHAR2 DEFAULT NULL
, p_backcolor IN VARCHAR2 DEFAULT NULL ) is


Thanks

Jithender said...

Hi sanjeev,

I have two question.

1) where are we declaring UTL_DIR parth. I want write my output on my desktop or unix server (any one of the directory.

2) I have to dump different spread sheets for each record number. For example I have 10 records number and Records number having 100 rows... those 100 rows needs to go one TAB of spread sheet.

How can do that?

Thanks a lot for your help in advance.

Jithender said...

Hi Sanjeev,

Could please let me know why I am getting these erros while running same package?


6/9 PL/SQL: Item ignored
6/9 PLS-00201: identifier 'UTL_FILE' must be declared
115/5 PL/SQL: Statement ignored
119/9 PL/SQL: Statement ignored
119/9 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

122/10 PLS-00201: identifier 'UTL_FILE' must be declared
429/13 PL/SQL: Statement ignored
429/32 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

Thanks,
Jithender

Sanjeev Sapre said...

You are getting these errors because you do not have access / grants to use UTL_FILE package.

Your DBA can help in this.

Regards
Sanjeev

Jithender said...

Thanks Sanjeev.

Can you please share some ideas about below?

I have to dump data to different spread sheets for each record number. For example I have 10 to 100 records number and Records number having 500+ rows... those 500 + rows needs to go one TAB of spread sheet.

Thanks a lot for your help

Jithender said...

As per our company security policy, no users are given permission to execute this UTL_FILE package.

Please let me know if I can proceed with other options.

Thanks

Sanjeev Sapre said...

Hi,

If you are on Oracle Applications then you use the create_excel_apps but if you are not on Apps then utl_file is the best option to go.
Try to find out why utl_file is not allowed. ( I can't think of a reason)

You may try dbms_output on sql plus instead of utl_file.



Regards
Sanjeev

Jithender said...

Thanks Sanjeev.
I will try to get the access for utl_file.

Anonymous said...

Hi Sanjeev,
This is a very good utility. Thank you.

When I run the API, all the data is stored in only once cell. I am properly incrementing the row and column numbers and passing those values to the API's. Have you faced this issue any time?

Thanks in advance,
Anil

Aziz said...

Hi Sanjeev,

thank you for this beautifull package.

I've started using it in production to create reports that users need but I have a problem with it: for example to create a sheet of 7500 rows, it needs 20min.
I have tried to run the same sql query in toad and it takes few secondw to get date but using the package, it's two slow.

any ideas?

thx again,
Aziz

Sanjeev Sapre said...

Aziz,

In all write cell procedures there is a call to cell_used function to check if the cell has been already used. Comment that portion and it will run faster. Let me know if this solves your problem.

Regards
sanjeev.

Unknown said...

Dear Sajeev,

Beautiful code, I am trying to use the same. I am using Excel 2000 and Oracle 10x. In this combination entire data is coming in first row only even though i am increasing the rows properly. Please help me.

Sanjeev Sapre said...

Thanks Ramanna.

Only Excel 2003 onwards support XML format. This could be the reason why it is not working properly.

-Sanjeev

Unknown said...

Sanjeev,

I have cdata like < and > in my data while writing to excel and I am getting load excel file error. How do I fix this.

thanks for you help

Anitha

Unknown said...

Hi Sanjeev,

I tried looking up to see if there is a way to fix the CDATA issue I have where I am getting excel loading error. But am not successful. Any suggestions from you would be of great help.

Thanks in advance.
Anitha

Unknown said...

Hi Sanjeev,
I downloaded your Package and am able to create Excel Workbooks. But, it looks like the workbooks are generated as older version of Excel. When I try to open with Excel 2007, I am getting an error that format is not ok. Ultimately it recognises it. I was just wondering if you have a Package with the newer version of Excel.

Thanks

Naresh

Sebastian said...

Hi Sanjeev!

Great job you did with that package! I am not sure though if it is suitable for me needs. In fact, I need to insert the data into an existing workbook, into several sheets and into different regions. hence, I must also be able to specify the Cell Ranges precisely. In your package it is possible to declare the columns (r)but would it be also possible to define the rows? And how could I open a Spreadsheet that is stored in my database and enter the values there? Do you have any idea how and if that can be done?

Thanks for your help mate!

bye,

Sebastian

Andy T said...

Hi,

Are you still available?

Is there any way to put an Excel formula into a cell by revising your PL/SQL gen_xml_xl code?

Sanjeev Sapre said...

Hello,

Sorry I could not respond to few posts because of busy schedule.

There is no specific provision for adding excel formula. The formula will be just like a text so for example '=a1+b1'
Then for second row we expect '=a2+b2' this has to be done pro grammatically

e.g. '=a'||r||'+b'||r

where r is the row identifier. As we write to excel 'r' is always required so it is very much available.

Regards
Sanjeev

Venkateshkarthikeyan said...

Hi Sanjeev,

I need to import data from Excel to Oracle table. I want to read the data from particular worksheet of Excel. how can i do this?

Thanks.

Unknown said...

Hi Sanjeev,

I am using the gem_xml in case of smaller volume it is working fine.But in case of lines more than 6000 the excel is not opening.

When I try to open the excel it gives an error log and when I open the error log this is the error.

XML PARSE ERROR: Malformed or illegal tag name
Error occurs at or below this element stack:






g_data_count value was 56000 for this

Can you please help me out to resolve this error as I need to deliver.

Thanks,
Sharmila

Sanjeev Sapre said...

Hello Sharmila,
Your comment says
"Error occurs at or below this element stack:" I can not see that stack so the only thing I can do is guess. The XML file is made up of tags enclosed in < and > , if your data somewhere contains these values then XML fails. The option is to wrap your cell contents in CDATA. XML ignores any characters in CDATA. You need to add this before and after cell data.
Hope this helps.

Regards
Sanjeev

Unknown said...

Thanks Sanjeev! that worked...
Can excel 7.0 solve the restriction of having 60,000 per sheet...

Sharmila

Sanjeev Sapre said...

Yes, It can, however the XML format of Office 2007 is different and so the code has to be changes to generate that format.

Regards
Sanjeev

Anonymous said...

Dear Sir,

Thank you very much, for the packagage you have written.

Sir, I have one query:
I want to user PROCEDURE write_cell_char, instead of char i want to use the date value.
Can you please help me in this regard.

Once again thank you for the package.


Vivek More.

Anonymous said...

Sir,

I have read the file genxlxml.txt -how to use the package.

I have converted the date columns into char and its working properly without any errors.

Thank you for great solution,

Vivek More

Unknown said...

Can you suggest some sites that would help me rewriting the XMl to suit the excel 2007.

Thanks,
Sharmila

Anonymous said...

Dear Sir,

Thank you for the Package. I am using it.

Can i use the same for to read Excel Worksheet data in Oracle table, without converting the Worksheet in CSV format ?
If yes, then How ?

Vivek More.

Kailas warade said...

i want to insert excel files data in to oracle table, without sql loader on linux platform,
Kindly help me on this.

Sanjeev Sapre said...

Vivek,

SQL Loader is preferred way of loading data into Oracle tables. If for some reasons you can not use it and do it from Excel itself, try using ODBC/VBA. I don't know how exactly it is done.

--Sanjeev

Sanjeev Sapre said...

Sharmila,

To find out the structure of excel 2007, you can save excel file as xml in 2007 and open it in text editor.

--Sanjeev

Unknown said...

Hi Sanjeev, I try to run your script but I've got an error I'm starting with oracle, I user PL/SQL Developer program so I unzip the package gen_xl_xml_code_0.62 I open the files
gen_xl_xml.pkb and gen_xl_xml.pks and the I unzip the gen_xl_xml_samples1 package and run the example of employee so appears an error saying : unit program gen_xl_xml not found and when I search in package body, I found it but with errors

Compilation errors for PACKAGE BODY ANDRYI.GEN_XL_XML

Error: PLS-00201: el identificador 'UTL_FILE' se debe declarar
Line: 6
Text: l_file utl_FILE.file_type ;

Error: PL/SQL: Item ignored
Line: 6
Text: l_file utl_FILE.file_type ;

Error: PLS-00320: la declaración de tipo de esta expresión está incompleta o tiene un formato incorrecto
Line: 133
Text: l_file := utl_file.fopen( p_directory, p_file_name , 'w') ;

Error: PL/SQL: Statement ignored
Line: 133
Text: l_file := utl_file.fopen( p_directory, p_file_name , 'w') ;

Error: PLS-00201: el identificador 'UTL_FILE' se debe declarar
Line: 136
Text: WHEN utl_file.write_error THEN

Error: PL/SQL: Statement ignored
Line: 129
Text: BEGIN

Error: PLS-00320: la declaración de tipo de esta expresión está incompleta o tiene un formato incorrecto
Line: 444
Text: utl_FILE.put_line( l_file, g_excel_data(i ));

Error: PL/SQL: Statement ignored
Line: 444
Text: utl_FILE.put_line( l_file, g_excel_data(i ));

Error: PLS-00320: la declaración de tipo de esta expresión está incompleta o tiene un formato incorrecto
Line: 446
Text: utl_file.fclose( l_file );

Error: PL/SQL: Statement ignored
Line: 446
Text: utl_file.fclose( l_file );

Error: PLS-00201: el identificador 'FND_FILE.OUTPUT' se debe declarar
Line: 450
Text: fnd_file.put_line( fnd_file.output , g_excel_data(i));

Error: PL/SQL: Statement ignored
Line: 450
Text: fnd_file.put_line( fnd_file.output , g_excel_data(i));

Error: PLS-00201: el identificador 'FND_FILE.LOG' se debe declarar
Line: 451
Text: fnd_file.put_line( fnd_file.log , g_excel_data(i));

Error: PL/SQL: Statement ignored
Line: 451
Text: fnd_file.put_line( fnd_file.log , g_excel_data(i));

Edna Pereira said...

Boa tarde, Estou usando o seu código.

Mas o meu problema é que quero exportar a partir de ecran ou de formulário dados de uma pesquisa feita.

Estou usando Oracle 10g e PL/SQL.

Agradeça desde já a sua ajuda.

Edna

Me

Anonymous said...

Thanks a Lot Sanjeev.

Nayeem Syed

Nayeem Syed said...

Hi Sanjeev,
Thanks for the great solution i am able to use your package for my requirements. Just i have small question "Is there a way to I freeze the Column". i.e, When we scroll to the bottom row of the worksheet , we still be able to see the Column Heading.

If you can reply to My email address "syed.nayeem316@gmail.com", I will appreciate it.

Thanks
Nayeem

Anonymous said...

Hi Sanjeev,

I am getting the following error. Then i tried to comment this two lines still i get the error.
fnd_file.put_line( fnd_file.output , g_excel_data(i));
fnd_file.put_line( fnd_file.log , g_excel_data(i));

LINE/COL ERROR
-------- -----------------------------------------------------------------
450/32 PLS-00201: identifier 'FND_FILE.OUTPUT' must be declared
450/13 PL/SQL: Statement ignored
451/32 PLS-00201: identifier 'FND_FILE.LOG' must be declared
451/13 PL/SQL: Statement ignored

Thanks
Sanjeevi

Anonymous said...

Hi Sanjeev,

I got the non_apps genxlxml code and it is working fine.

Really it is a great solution.

Thanks a lot.

Sanjeevi

Unknown said...

Sanjeev,
Great Package, have been able to generate user requested reports easily. Is there a way to justify and format columns that you can think of. For example we want the number columns to be right justified with commas.

thanks for the great solution

WhyteHousieBala said...
This comment has been removed by the author.
Ekta Chhabra said...

Hi Sanjeev, i am a oracle developer. Ihave got a requirement to create a worksheet with two tabs. one tab will have 19 coloumns with 9000 rows and the other one will have 7 coloumns with around 2000 rows.

I have seen the GEN_XL_XML code u provided. but i m not able to create the report as it says, the error : max number of characters in line is 255. length exceeded.

I have to send my code for UAT urgently. Kinldy help me asap plzz.

Sanjeev Sapre said...

Hello Ekta,

If you can send me the code, I could take a look at that.

-Sanjeev

Ekta Chhabra said...

I m creating a excel with two worksheets, named as "Summary By Type Of Hours" and "All Resource Detailed Hours" . first worksheet is doing great, as it has only 500 records.. but wen i insert in to second tab 9000 records through my code, its not getting inserted, only 2000 around get inserted. Kindly help me asap.


Can you mail me ur id on "veronicaangel55@gmail.com" , so that i can share wid you my code.

Thanks

Ekta Chhabra said...

Hi sanjeev , i have shared my code with you. Any updates sir ??

Anonymous said...

Hi sanjeev,

I have the same problem that ekta, I couldn't created more rows.

I tell you that I'm using your package from Reports Builder to work with a query that have parameters.

Thanks in advance.

Arantxa

Sanjeev Sapre said...

If you can send me the code, I could take a look at that.

-Sanjeev

Anonymous said...

hi there. i'm trying your script in my database. but it didn't work. it says " identifier gen_xl_xml.create_excel must be declared" would u help me. i'm totally new in this.

Unknown said...

Hi Sanjeev,

I am trying to use this pkg, but I'm gotting the error when open excel file...it allows add 16 columns in the excel, if I try include more, get an error and do not open file...
do you know why it happen ?

Tksm
Luciano

mani said...

Hello Sanjeev, thanks for this pkg.

I would need some more help here.

My export fetches more than 1 millions records, so how can we create multiple worksheets on the same excel when max rows (65000) is reached.

Help is much appreciated..

Sanjeev Sapre said...

Hi Mani,

When you are writing the rows, you know the row number, so when you reach 65000 you need to create and use another worksheet. The package will not take care automatically, you have to keep track of row number and change worksheets.

HTH.
-Regards
Sanjeev

Sanjeev Sapre said...

Hello Tksm Luciano,

I need to see the code, I have created reports with more than 100 columns, so I am sure this works fine, so if possible share me your id so that we can communicate over mail.

Regards
Sanjeev

Unknown said...

Sanjeev,

I just sent you email to

sanjeev.blog@gmail.com

Thank you
Luciano.

Unknown said...
This comment has been removed by the author.
Unknown said...

I already fixed the issue.

Thank you,

Luciano

Unknown said...

Hi Sanjeev,

I am unablre to create mutiple tabls with the example you gave, I am getting error.....'Not in the worksheet' for second tab. If I wish to create one tab it works fine. Is there something I am missing?

Anonymous said...

Hi Sanjeev,
I need to create a report in excel using multiple worksheet. In each worksheet I need place two set of datas. eg: R1:C1, R1:C2
R2:C1, R2:C2
R2:C1, R2:C2
and then R1:C4, R1:C5 here I am not able to set the row index value to 1.. is there any option to set the row index back to 1? I need to submit the report by Monday.

buddys said...
This comment has been removed by the author.
buddys said...

hi sanjeev

great code! thanks

i was easily able to add cell formatting capability, and formula capability even though i am not a sqlplus programmer.

thanks again.

buddy

Anonymous said...

Hi Sanjeev

Great solution.

when using the procedure for creating i ma getting this error
ORA-20101: UTL_FILE raised others exception

seetha said...

hi

is there any possibility to read the data from excel and populate to the database table using pl/sql.
Could you please look into this ASAP.I've looked into many sites but in vain

Seetha

Ronald said...

Sanjeev,

Am a novice in PL/SQL, no document tells how to run the PKS and PKB file?? pls tell me how to do it

Regards
Ronald

Praveena said...

When I open a excel file it is giving as error as below for more then 43 rows.

XML PARSE ERROR: Missing end-tag
Error occurs at or below this element stack:







What can we do this?

cnraja said...

Hi Sanjeev,
Is there any update for Date format and applying borders to ach cell?

Anonymous said...

can we edit existing excel file using this method?

Anonymous said...

Hi Sanjeev,

We are facing a problem when the data is converted to excel format using through PL/SQL. It gives us the below erorr. This is due to security feature in MS Office 2007 and above. Could you please suggest any workaround for this through PL/SQL. I do not want to change registry setting to overcome this issue.

"The file you are trying to open is in a different format than specified by the file extension."

Regards,
Sunil

Unknown said...

Hi Sanjeev,

I have created the file successfully. However, I can't view the formatting when I open on iPad or Android device.

Can you advise how to view the file on iPad/Android?

Thanks
Chirag

Unknown said...

Hi Sanjeev,

Thanks for the great code.

I get an error on the following line:
l_font := ' <Font ';

The error message is:
Error(174,5): PLS-00201: identifier 'L_FONT' must be declared

Could you please assist with this.

Many thanks
Jack

Usman said...

Hi Sanjeev,
Nice solution!
I have succesfully used formulas as well.

Dipali said...

Hi sanjeev
I have developed and package to generate excel the same package is working in one instance and on other instance the excel is not opening giving problems during load..
can you please help?

bala said...

Hi Sanjeev,

I want to upload excel sheet through my JDBC driver. Can you help me in identifying correct tool which hit my JDBC and insert records in the tables

Unknown said...

Dear Sanjeev,

I have a requirement to read a cell value from excel as IN variable and write the OUT variables to the adjacent cell for Eg: if am reading from A1 then I have to write in A2,A3 can this package be rewritten in such way. Can you please send it to my mail id: aravind.psg@gmail.com

Vivencias said...

Great job Sanjeev, are there any recent update to your package, like cell alignement.
Again thank you a lot.
Amilkar

Unknown said...

how can we generate xlsx file instead of xls?Please advice

Unknown said...

Hi Sanjeev,


I went thru your blog and took the code for "create excel by p/sql".


It runs fine but when I retreive "date" it gives me an error and worksheet is blank. I know you have mentioned that currently it doesn't support DATE. Any alternative you can suggest ?


I tried converting it to char but no luck..Please do revert asap as this is an urgent deliverable for me..

Anonymous said...

Great script Sanjeev.

I am trying to create excel using 10000 records, its too slow.

As suggested by you in one of others post,I have commented the cell_used function in write cell procedure. Still no luck. Any help on this?

Anonymous said...

Thank you for your code. I'am impatient to try it out!

wolf

Anonymous said...

I am using this code in an application.

I am using this code in the page:-

DECLARE
r NUMBER := 0 ;
BEGIN
-- gen_xl_xml.create_excel('UTL_DIR', 'employees.xls') ;
gen_xl_xml.create_worksheet( 'sheet1');
gen_xl_xml.create_excel_apps ;
gen_xl_xml.create_style( 'sgs1' , 'Courier', 'red',16, TRUE , p_backcolor => 'LightGray', P_underline => 'Single' );
gen_xl_xml.create_style( 'sgs2' , 'Courier', 'blue',12,NULL );
gen_xl_xml.create_style( 'sgs3' , 'Courier', 'green',14,TRUE );

-- increase width OF colum b that IS no 2
gen_xl_xml.set_column_width( 1, 140, 'sheet1' );
gen_xl_xml.set_column_width( 2, 145 , 'sheet1' );
gen_xl_xml.set_column_width( 3, 145, 'sheet1' );
gen_xl_xml.set_row_height( 1, 30 ,'sheet1' );

-- writing the headers
r := r+1 ;
gen_xl_xml.write_cell_char( r,1, 'sheet1', 'Employee id' ,'sgs1' );
gen_xl_xml.write_cell_char( r,2, 'sheet1', 'First Name' ,'sgs1' );
gen_xl_xml.write_cell_char( r,3, 'sheet1', 'Last Name', 'sgs1' );
-- gen_xl_xml.write_cell_char( r,3, 'Last Name', 'sgs1' );
--
FOR y IN (SELECT 1 OEM_CODE, OEM_NAME , OEM_USER_CREATED FROM OEMS where ROWNUM < 10 ) LOOP

r := r+1 ;
gen_xl_xml.write_cell_num( r,1, 'sheet1' , y.OEM_CODE, 'sgs3' );
gen_xl_xml.write_cell_char( r,2, 'sheet1' , y.OEM_NAME, 'sgs2' );
gen_xl_xml.write_cell_char( r,3, 'sheet1' , y.OEM_USER_CREATED, 'sgs2');

END LOOP ;

gen_xl_xml.close_file ;

END ;

But got this error :-
ORA-20008: No worksheets have been created, this version does not support automatic worksheet creation.

Can you please tell me what code should i
use in the page?i want excel output of a sql query with a custom header row.

Thanks
Subhojit

MC said...

Love ur code.. many thnx!!

Unknown said...

Your code is cool and simple to apply. Great Thanks. I used to create csv files but is trying to create excel this time because I want to put a company logo on the top of the report. However, I cannot figure out a way to put an image (either from a jpg file or from a BLOB column in a table) into your code to make it work. I think I can pre-create a excel with that logo header and open it for append in your code. But since our database server is not in the application server, this is not easy to do. Can you give some advise? Thank you.

gloire said...

hi i used the package and i update it to support date form. i added write_cell_date function
and updated create_style function.
------------------------------------------------------------------------------
How to use :
Create new style : create_style(‘style name’, ‘Short Date’);
Create cell: write_cell_date(p_row, p_column, p_worksheet_name ,to_char(‘your date’, ‘yyyy-mm-dd’) , ‘style name’);

gloire said...

PROCEDURE write_cell_date(p_row NUMBER, p_column NUMBER, p_worksheet_name IN VARCHAR2, p_value IN VARCHAR2, p_style IN VARCHAR2 DEFAULT NULL ) IS
l_ws_exist BOOLEAN ;
l_worksheet VARCHAR2(2000) ;
BEGIN

-- CHECK IF this cell has been used previously.
IF cell_used( p_row , p_column , p_worksheet_name ) THEN
RAISE_application_error( -20001 , 'The cell ( Row: '||p_row ||' Column:'||p_column ||' Worksheet:'||p_worksheet_name ||') is already used.Check if you have missed to increment row number in your code. ');
END IF;

-- IF worksheet NAME IS NOT passed THEN use first USER created sheet ELSE use DEFAULT sheet
-- this PROCEDURE just adds the data INTO the g_cells TABLE
g_cell_count := g_cell_count + 1 ;
g_cells( g_cell_count ).r := p_row ;
g_cells( g_cell_count ).c := p_column ;
g_cells( g_cell_count ).v := p_value ;
g_cells( g_cell_count ).w := p_worksheet_name ;
g_cells( g_cell_count ).s := p_style ;
g_cells( g_cell_count ).dt := 'DateTime' ;

END ;

gloire said...

sorry about create_style procedure i can't put the text here because it contains some HTML tags. If anyone have an idea how can i attach ite file

Unknown said...

I have recently enhanced this package to include (1) filter (2) pane (3) subtotal (4) comma and $ format. If anyone is interested please let me know.

Unknown said...

Hi,

i have one very similar code to generate the excel file from pl/sql code.

Though this was fully working in IE-8 browser but when i try to run the same code through IE-11 then suddenly it changes the behavior.

It gives the error in exporting the excel file that 'This File Couldn't be downloaded' and when i RETRY to download the same then it downloads successfully.

could you please derive me what is going wrong with it ?

Thanks in advance,
Bhumin

Unknown said...

Hello,
I am trying to execute your procedure by Applications EBS, but I do not know how to make it output in concurrent, it gives a structure error in XML.

Att
Vanderlei

Krishna Optif said...

very nice and provide me informative content thanks for sharing for more information about how to Create new Workbook