Create Excel Workbook by PL/SQL

By | June 28, 2007

Create Excel Workbook by PL/SQL

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

115 thoughts on “Create Excel Workbook by PL/SQL

  1. Friedhold Matz

    Hi Sanjeev,

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

    fnd_file.put_line & fnd_file.output ..

    Best Friedhold

    Reply
  2. arun

    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
    =====================

    Reply
  3. udi

    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.

    Reply
  4. Anitha

    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

    Reply
  5. Apster

    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

    Reply
  6. Sanjeev Sapre

    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

    Reply
  7. Sanjeev Sapre

    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

    Reply
  8. Sanjeev Sapre

    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

    Reply
  9. Anonymous

    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.

    Reply
  10. Sanjeev Sapre

    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

    Reply
  11. Sourav

    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

    Reply
  12. Sanjeev Sapre

    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

    Reply
  13. Anonymous

    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

    Reply
  14. oddbjorn lona

    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

    Reply
  15. aynurs

    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

    Reply
  16. Sanjeev Sapre

    Can you please post the complete error message ?

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

    Regards
    Sanjeev

    Reply
  17. aynurs

    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.

    Reply
  18. Sanjeev Sapre

    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

    Reply
  19. Konstadinos

    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);

    Reply
  20. Rami

    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

    Reply
  21. Sanjeev Sapre

    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

    Reply
  22. machinebazaar

    Hi Sanjeev,

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

    Melad

    Reply
  23. najeeb

    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.

    Reply
  24. Sanjeev Sapre

    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

    Reply
  25. najeeb

    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

    Reply
  26. syed

    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

    Reply
  27. Sanjeev Sapre

    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

    Reply
  28. syed

    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?

    Reply
  29. oceanaut

    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

    Reply
  30. Jithender

    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.

    Reply
  31. Jithender

    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

    Reply
  32. Sanjeev Sapre

    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

    Reply
  33. Jithender

    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

    Reply
  34. Jithender

    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

    Reply
  35. Sanjeev Sapre

    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

    Reply
  36. Jithender

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

    Reply
  37. Anonymous

    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

    Reply
  38. Aziz

    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

    Reply
  39. Sanjeev Sapre

    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.

    Reply
  40. Ramana Reddy

    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.

    Reply
  41. Sanjeev Sapre

    Thanks Ramanna.

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

    -Sanjeev

    Reply
  42. Anitha

    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

    Reply
  43. Anitha

    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

    Reply
  44. nnalam

    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

    Reply
  45. Sebastian

    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

    Reply
  46. Andy T

    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?

    Reply
  47. Sanjeev Sapre

    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

    Reply
  48. venki

    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.

    Reply
  49. Sharmila

    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

    Reply
  50. Sanjeev Sapre

    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

    Reply
    1. Sharada

      Hello Sanjeev,

      I am also getting the same error ‘XML PARSE ERROR: Malformed or illegal tag name’.
      Please tell me how to wrap cell contents in CDATA.XML.

      Regards,
      Sharada

      Reply
  51. Sharmila

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

    Sharmila

    Reply
  52. Sanjeev Sapre

    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

    Reply
  53. Anonymous

    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.

    Reply
  54. Anonymous

    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

    Reply
  55. Anonymous

    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.

    Reply
  56. Sanjeev Sapre

    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

    Reply
  57. Sanjeev Sapre

    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

    Reply
  58. Christian

    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));

    Reply
  59. Edna

    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

    Reply
  60. Nayeem Syed

    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

    Reply
  61. Anonymous

    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

    Reply
  62. Anonymous

    Hi Sanjeev,

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

    Really it is a great solution.

    Thanks a lot.

    Sanjeevi

    Reply
  63. LoadRunner

    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

    Reply
  64. ekta

    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.

    Reply
  65. ekta

    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

    Reply
  66. Anonymous

    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

    Reply
  67. Anonymous

    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.

    Reply
  68. Luciano

    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

    Reply
  69. mani

    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..

    Reply
  70. Sanjeev Sapre

    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

    Reply
  71. Sanjeev Sapre

    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

    Reply
  72. Nishesh

    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?

    Reply
  73. Anonymous

    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.

    Reply
  74. buddys

    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

    Reply
  75. Anonymous

    Hi Sanjeev

    Great solution.

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

    Reply
  76. seetha

    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

    Reply
  77. Ronald

    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

    Reply
  78. Praveena

    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?

    Reply
  79. Anonymous

    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

    Reply
  80. Chirag Jhaveri

    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

    Reply
  81. Jack Bruce

    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

    Reply
  82. Usman

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

    Reply
  83. Dipali

    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?

    Reply
  84. bala

    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

    Reply
  85. aravind kumar B

    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

    Reply
  86. Vishal Kaviraj

    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..

    Reply
  87. Anonymous

    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?

    Reply
  88. Anonymous

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

    wolf

    Reply
  89. Anonymous

    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

    Reply
  90. William Wong

    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.

    Reply
  91. gloire

    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(鈥榮tyle name鈥, 鈥楽hort Date鈥);
    Create cell: write_cell_date(p_row, p_column, p_worksheet_name ,to_char(鈥榶our date鈥, 鈥榶yyy-mm-dd鈥) , 鈥榮tyle name鈥);

    Reply
  92. gloire

    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 ;

    Reply
  93. gloire

    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

    Reply
  94. William Wong

    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.

    Reply
  95. bhumin bhalodiya

    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

    Reply

Leave a Reply

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