Create an Excel-file with PL/SQL

136 thoughts on “Create an Excel-file with PL/SQL”

1. I’m running the package from a Linux server with a remote Oracle DB RAC where the package is installed. I replaced the “MY_DIR” with “/stage” which the correct path related to root (/). I get the error below. How does this package handle linux directories? What should I be using?

   ERROR at line 1:
   ORA-29280: invalid directory path
   ORA-06512: at “SYS.UTL_FILE”, line 29
   ORA-06512: at “SYS.UTL_FILE”, line 448
   ORA-06512: at “HPM.AS_XLSX”, line 140
   ORA-06512: at “HPM.AS_XLSX”, line 1837
   ORA-06512: at line 3

2. Anton,
   I know you released this a while ago into the public and I have REALLY gotten a GREAT DEAL of usage from it for my users.

   I need to ask a question about performance. When I try using this package with a query returning 20000 or so rows, the routine seems to get bogged down and takes forever to return a file. Is there anything I can do to improve performance?

1. @Tony Miller. Performance is an issue. The package needs to store everything in memory before it can create an Excel-file. If your database doesn’t have that much memory (XE-database) it can be a problem. If have tried to improve the performance, but haven’t found a way yet.

1. Very strange issue here.. Ran code days ago that was dragging VERYYY slowly, ran the same code today after re-compiling your package and the code I am using with it and I am getting SIGNIFICANT improvement of performance..

   We are running on an 11g r2 Enterprise instance (VM on a Linux server).. I do appreciate what you have done with this package..

   Might have been a server issue with memory getting hogged by other processes and now its free..

   Either way, thanks for the help!!

1. We have had the same performance issues, we solved it by changing the following;

   In function finish, declare a local var

   lc_rows tp_rows;

   See changes below

   ..

   
   
   ‘;
   add1xml( t_excel, ‘xl/theme/theme1.xml’, t_xxx );
   for s in 1 .. workbook.sheets.count()
   loop
   t_col_min := 16384;
   t_col_max := 1;
   /* vvvvvv CHANGED vvvvvvv */
   — t_row_ind := workbook.sheets( s ).rows.first();
   lc_rows := workbook.sheets( s ).rows;

   /* while t_row_ind is not null
   loop
   t_col_min := least( t_col_min, workbook.sheets( s ).rows( t_row_ind ).first() );
   t_col_max := greatest( t_col_max, workbook.sheets( s ).rows( t_row_ind ).last() );
   t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
   end loop;
   */
   t_row_ind := lc_rows.first();
   loop
   exit when t_row_ind is null;
   t_col_min := least( t_col_min, lc_rows( t_row_ind ).first () );
   t_col_max := greatest( t_col_max, lc_rows( t_row_ind ).last() );
   t_row_ind := lc_rows.next( t_row_ind );
   end loop;

   /* ^^^^^^ CHANGED ^^^^^^^ */
   ..
   ..
   ..
   /* vvvvvv CHANGED vvvvvvv */
   /*
   t_xxx := t_xxx || ”;
   t_row_ind := workbook.sheets( s ).rows.first();
   t_tmp := null;
   while t_row_ind is not null
   loop
   t_tmp := t_tmp || ”;
   t_len := length( t_tmp );
   t_col_ind := workbook.sheets( s ).rows( t_row_ind ).first();
   while t_col_ind is not null
   loop
   t_cell := ”
   || to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
   || ”;
   if t_len > 32000
   then
   dbms_lob.writeappend( t_xxx, t_len, t_tmp );
   t_tmp := null;
   t_len := 0;
   end if;
   t_tmp := t_tmp || t_cell;
   t_len := t_len + length( t_cell );
   t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
   end loop;
   t_tmp := t_tmp || ”;
   t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
   end loop;
   */

   /* New */

   t_xxx := t_xxx || ”;
   t_row_ind := lc_rows.first();
   t_tmp := null;
   while t_row_ind is not null
   loop
   t_tmp := t_tmp || ”;
   t_len := length( t_tmp );
   t_col_ind := lc_rows( t_row_ind ).first();
   while t_col_ind is not null
   loop
   t_cell := ”
   || to_char( lc_rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
   || ”;
   if t_len > 32000
   then
   dbms_lob.writeappend( t_xxx, t_len, t_tmp );
   t_tmp := null;
   t_len := 0;
   end if;
   t_tmp := t_tmp || t_cell;
   t_len := t_len + length( t_cell );
   t_col_ind := lc_rows( t_row_ind ).next( t_col_ind );
   end loop;
   t_tmp := t_tmp || ”;
   t_row_ind := lc_rows.next( t_row_ind );
   end loop;

   /* ^^^^^^ CHANGED ^^^^^^^ */

   Another change was made in procedure clear_workbook;

   procedure clear_workbook
   is
   t_row_ind pls_integer;
   t_clear_rows tp_rows;
   begin
   for s in 1 .. workbook.sheets.count()
   loop
   workbook.sheets( s ).rows := t_clear_rows;
   /*
   t_row_ind := workbook.sheets( s ).rows.first();
   while t_row_ind is not null
   loop
   workbook.sheets( s ).rows( t_row_ind ).delete();
   t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
   end loop;
   */
   workbook.sheets( s ).rows.delete();
   ..

   Hope this will help.

3. Anton, thanks for creating this. Is this code released under a specific license?

4. Hi Anton, I just discovered your as_xlsx.

   First of all, thank you VERY much for posting it for us. It really looks like the miracle I’ve been looking for…

   I have one quick question, I hope you ‘ll answer:
   I intend to use as_xlsx to produce excel report sheets from sql queries. A typical query will produce a result like this:
   area_id area_value area_factor
   ————– ————— ————–
   123456789 1234.5 62.5
   234567890 987.8 0
   345678901 12345 35
   456789012 23456 .32 <–(this is 0.32)
   … etc

   Trying to avoid pestering you with details, my question is:
   Is there a way, while using query2sheet, to declare an entire column's format as, say, number with 1 decimal?
   If not, can I invoke cell formatting (always, inside a query2sheet call…)?

   Thank you in advance for your time and patience.

   Regards
   Greg

5. Hi Anton,
   Thanks a lot. This Plugin seem to be very useful, but I need to export data from a TABLE TYPE (and not with select, as you wrote). how can I implement this?

   TYPE Elad_Tbl_T IS TABLE OF Elad_Rec_T index by binary_integer;
   l_my_tbl_inst Elad_Tbl_T;

   In my procedure, I enter all the business logic, in that table type (l_my_tbl_inst).

   instead of:
   as_xlsx.query2sheet( ‘select * from dual’ );

   I want to write, something like that:
   as_xlsx.query2sheet( ‘l_my_tbl_inst’ );

   Thanks in advanced,
   Elad

6. Hi Anton,
   I have been using your xlsx package quite a lot lately and also did some customization.
   Another thing I successfully used your package for is creating an APEX IR Downloader with full coloring and so on.
   If you are interested in the things I did just contact me on twitter.

   A huge thank your for providing this package.

   Moritz

7. Very useful, Anton! Thanks for sharing.
   How i could use query2sheet to write data into an existing excel file.
   Thanks

8. Hi Anton,

   Great to have this package in my ammo.

   But i am tired of getting below error.
   ORA-04030: out of process memory when trying to allocate 65572 bytes (PLS non-lib hp,PL/SQL STACK)
   ORA-06512: at “SYS.UTL_SYS_COMPRESS”, line 17
   ORA-06512: at “SYS.UTL_SYS_COMPRESS”, line 202
   ORA-06512: at “SYS.UTL_COMPRESS”, line 59
   ORA-06512: at “IRMAEXT.AS_XLSX”, line 271
   ORA-06512: at “IRMAEXT.AS_XLSX”, line 1280
   ORA-06512: at “IRMAEXT.AS_XLSX”, line 1342
   ORA-06512: at “IRMAEXT.AS_XLSX”, line 2504
   ORA-06512: at line 14

   I am trying to generate excel with 900000 Records. Memory consumption for oracle exe goes to 2,000,000 K just before getting this error.

   My oracle version is 10.2.0.3.0 Oracle Database 10g Enterprise Edition.

   Looking forward for some help. Tried out most of google suggestions but nither of them worked. Guess the creator himself will show some light on This.

   – Ajit

9. Hi Anton,
   great package. Thanks for sharing. I’m finding a problem between oracle 10 and 11.2. In the first case i create a report with 150000 rows in 132 seconds; in the second case i create the same report, but only 5000 rows, in 900 seconds. can you help me about some different parameter to increase in 11.2?

10. This package has been very helpful to me. In one of the reply below you had said “Build in a limit to restrict the package to generate max 5000 rows so people don’t allocate to much memory?” Could you please point where this limit is set in the package? I am running into an odd situation where it only creates 200 rows in the Excel file whereas the table has 514 rows. My table has 4 clob type columns. If the table has clob type column, do I need to covert the columns into string or varchar2 type before calling the Query2Sheet?

1. i hope someone can help me about the date system too, im using windows, i have an extracted excel and after copying its cell in date format into another excel file, the date becomes different. this is excel’s issue “1904 / 1900″, is there a way that we set the package to just be in 1900? your help is appreciated. Thanks!

1. @Ramkumar. This package uses the Excel 1904 date format. There’s no setting to change that.

11. Hi Anton,

    Great Package…

    I wanted to have different font-size and Font-Color on the title – Few Merged Cells…
    I am able to do it it in XML …

    Just wanted to know if I could do somekind of HTML Format implementation… Similar to the following…
    which works in XML –> Excel…

    as_xlsx.cell( 1, 1, ‘Title – SubTitle‘);

    Currently it treats them as text.. in you procedure…

    as_xlsx.mergecells( 1, 1, 20, 1 )

1. Title‘||
   ‘– SubTitle‘)

2. You could try
   as_xlsx.cell( 1, 2, ‘Title- SubTitle’, p_fontId => as_xlsx.get_font( ‘calibri’, 2, 15, p_bold =>true ) );

1. Hi Anton,

   thanks for the Quick Response..

   What Ment was –>
   Tiltle using ‘calibri’ 15, p_bold =>true
   SubTitle using ‘calibari’ 15, bold=>false
   both in the same cell

   I tried in 2 different cells (works) but then merging them gets ris of the second cell

   Wonderful Package, and I love it!

   Thanks A lot in Advance!

12. This is really good. Is there an alternative for doing the xls files?

1. @Boydus. Not that I know of

13. I ran below code, and it generated the file successfully.
    But I could not open the file, when I tried it throws an error message “Excel found unreable content in ‘my.xlsx'”

    begin
    as_xlsx.new_sheet( ‘query 1’ );
    as_xlsx.query2sheet( ‘select 1 r from dual’, p_sheet => 1 );
    as_xlsx.new_sheet( ‘query 2’ );
    as_xlsx.query2sheet( ‘select 2 r from dual’, true, ‘ETF_DATA_OUT_DIR’, ‘my.xlsx’, 2 );
    end;

1. That code works for me. And it will probably work for you too if you try it in a new session.
   If you start your code with as_xlsx.clear_workbook;
   you make sure that you start with a “clean” excel file.

14. Anton,

    Great work. Thank you for posting your tool. Your as_pdf3 tool uses dbms_lob.freetemporary when you do a save. Is there any reason to avoid freeing the blob when doing a save in as_xlsx?

    Thanks,
    Ted

1. No reason, I thought that as soon the used blob went out of scope (i.e. after the call to finish) it is freed automatically.

15. What kind of help do you expect? On my 11 XE database the package produces a 73220 rows, with 10 numerical columns, Excel file without a problem in 10 seconds. If I change it to use 2 string columns of size 100 it still works OK. So my guess is that you are using a lot of very large strings on a 10 (XE) database. And it looks like the package isn’t suitable for that. What should I do? Keep guessing and change the package and hope for better results? Build in a limit to restrict the package to generate max 5000 rows so people don’t allocate to much memory?

1. Anton,
   I would say people need to understand you provided a FREE package to be used and they take their OWN risks in using it.. Shesh, you went out of your way to provide the package and we are using it to produce Office 2003 files with some special formatting..

   We are VERY Thankful for what you provided, and what Denes provided (a package to grab the current select for an Interactive report with bind variables dealt with properly… Yes, it has some issues with returning extra columns with the select due to the way interactive reports re-write the query you build for a report.

   But we appreciate someone else contributed this to the community with NOT strings attached (beyond a thank you or a possible contribution to keeping their site up and running..)

16. Hi Anton,

    While exporting 73220 rows, i am getting below error message.

    “ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pmucpcon: tds)
    ORA-04030: out of process memory when trying to allocate 16328 bytes (koh-kghu call ,pmucalm coll)”.

    I think , you package causes excessive allocations of process memory space.

    Please help me.

17. Your package is great , Really it helps me lot.

    I have problem , if am writing more than 10k rows and it is taking for ever. is there any limit to write, please help me

    Thanks

18. Hello,

    Thank you for this source code. it works great and is close to what we are looking for in our system. However, we support clients with previous versions of Excel (mainly 97-03 and above) but not all clients have the compatibility pack installed (some are unable to). The comments are not easy to read through since there is no scrollbar on them but I know it was asked. Is there a way to adapt this to create an .xls file instead of xlsx?

    Maybe i am doing something wrong. When I try to name the file .xls and open it i am prompted that the file type and extension are different. can anyone confirm that you can save .xls files and open them normally without modifying the code.

    Thanks,

    Bill

1. You can’t create xls files with this package.

1. That is what I was afraid of. Thank you for the quick reply and again for the code.

19. Hi Anton,

    first let me thank you for this great package.

    Please note that I had an issue getting ORA-6502. I got this error when appliying “Auto Filter” in procedure “finish”. I debugged the procedure and found out an issue with concatination variable t_xxx.
    When replacing this code using dbms_lob.writeappend resolves the issue.

    I propose to replace all concatinations with pipe by dbms_lob.writeappend for t_xxx.

    — ############################### CHANGED CODE #############
    for a in 1 .. workbook.sheets( s ).autofilters.count()
    loop
    t_tmp:= ”;
    t_len := length( t_tmp );
    dbms_lob.writeappend( t_xxx, t_len, t_tmp );
    end loop;
    — ############################### CHANGED CODE #############

    — ############################### ORIGINAL CODE #############
    for a in 1 .. workbook.sheets( s ).autofilters.count()
    loop
    t_xxx := t_xxx || ”;
    end loop;
    — ############################### ORIGINAL CODE #############

    kind regards

    Ralph

20. Hello Anton,
    Thank you for the update. The package was renamed to match or naming standards. This is only for testing. How can I change the font on the entire sheet and how can I specify the column widths? Can each column have its own column width?

    Newbie.

21. Hello Anton,

    I’ve been using your pl/sql package to create an excel file. It works great !!!
    I have two questions. How do I rename the sheets (ie. Sheet1) and how can I create a excel document with multiple sheets within the same excel document? Below is what I’m trying to attempt. When I run the following query, the output file is created but when I try to open the file with excel I get multiple errors.

    DECLARE

    v_dir VARCHAR2(30) := Course_dir;
    v_file VARCHAR2(30) := ‘my_course.xls’;

    begin
    pwg_create_xlsx.clear_workbook;
    pwg_create_xlsx.new_sheet(‘Course 24967’);
    pwg_create_xlsx.query2sheet(‘select * from courses where course_numb = 24967’ );
    pwg_create_xlsx.new_sheet(‘Course 24293’);
    pwg_create_xlsx.query2sheet(‘select * from courses where course_numb = 24293’ );
    pwg_create_xlsx.save(v_dir, v_file);
    end;

1. @Newbie. Any reason why you renamed the package?
   Use the parameter p_sheet, for instance
   pwg_create_xlsx.query2sheet(‘select * from courses where course_numb = 24967’, p_sheet => 2 );

22. Works even better w/ autoflush 😉

    utl_file.put_raw( file => t_fh
    , buffer => dbms_lob.substr( p_blob
    , t_len
    , i * t_len + 1
    )
    , autoflush => true
    );

1. I must say I never had any problems without the autoflush = true. But you are right, it’s better. And if you have trouble writing out a file, it might even help to add max_linesize => 32767 to the utl_file.fopen statement.

23. Anton

    Is their a whitepaper on how to implement this from both Apex, where I am just opening a .xlsx file and from a stored Proc where I am emailing the xlsx file and passing in the SQL statement on both instances?

    Chuck

1. No

1. So is it meant to only save to the network?

1. I have been working with Anton to have a modified version of this build a xlsx file and allow the user to download/save it locally..

1. My business requirement is to transition from sending .xls files (we are currently using xml Spreadsheet by Matzberger) to .xlsx files.

   Would love to use this, as it seems to be widely regarded, but not sure how to use.

2. No, I don’t have a whitepaper. But using the function finish you will get a blob. You can store that blob in a table, use it as an attachment for a email, throw it away, what ever you want.

1. Anton is right, once the procedure runs and builds the Finish blob item, you can do what you want with it. In my case I am downloading it via a wpg_docload.download_file call to the user for either a save to disk or open right there option…

   Thank you,

   Tony Miller
   LuvMuffin Software

24. I am trying to use this package with a slight twist.. Since you have an existing plugin that will allow download of a report via Excel 2007 format file, but it can only handle reports up to 26 columns, I was wanting to use this library and just have it determine the active sql select from an interactive report (code already added) and run the query like it does now into a return BLOB and then download t he BLOB via wpg_docload.download_file..

    However, the last step of downloading via wpg_docload.download_file is not producing an error, but also not producing a download to the users browser..

    Any suggestions?

25. I’m using this package very successfully and now have a requirement to replicate an excel spreadsheet that I’ve been presented with. To replicate the format exactly I’m required to change the colour of some of the cell borders. Can anyone tell me if this is possible? I’ve not immediately seen how to do it but thought maybe I’m missing something?

    Thanks
    Adam

26. I found my problem. I formatted the cell for Text using as_xlsx.get_numFmt(‘@’). What I didn’t know was that formulas entered into fields formatted as Text are just that…TEXT. By changing to get_numFmt(‘General’) I was able to get the results I wanted and allow my users to enter formulas after downloading the XLSX file.

27. nice work, i hope someone can help me about the date system too, im using windows, i have an extracted excel and after copying its cell in date format into another excel file, the date becomes different. this is excel’s issue “1904 / 1900”, is there a way that we set the package to just be in 1900? your help is appreciated. Thanks!

28. Nice job.

    /*
    SUM(B1:C1)
    10
    */

29. Anton – this is is a wonderful package. Thank you very much for sharing this with everyone.

    I have run into an issue that I wonder if anyone has resolved. After downloading the Excel file, I am unable to create formulas in cells that have text data. When I enter the formula such as =C2, the result is a text entry of =C2 rather than the value of C2.

    Thank you,

    Mike Chambers

1. Mike,

   You can try something like this in your code:
   Worked for me in XE 10g(not tested for complex formulas or different return type).
   I hope is useful.

   —————————————————————————
   /*
   –Simple test calls:
   as_xlsx.cell(1,1,”,p_type => ‘f’, p_formula => ‘B1+C1’ );
   or
   as_xlsx.cell(1,1,”,p_type => ‘f’, p_formula => ‘SUM(B1:C1)’ );
   */
   —————————————————————————

   while t_col_ind is not null
   loop
   /*Start 14-sep-2013*/
   IF workbook.sheets(s).rows(t_row_ind)(t_col_ind).type = ‘f’ THEN
   /*
   SUM(B1:C1)
   10
   */
   declare
   l_type varchar2(30):=’t=”array”‘;
   begin
   t_cell := ”
   || ”
   || workbook.sheets(s).rows(t_row_ind)(t_col_ind).formula
   || ”
   /* || ”
   || to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
   || ”*/
   || ”;
   end;
   else
   /* End 14-sep-2013 */
   t_cell := ”
   || to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
   || ”;
   END IF; /* 14-sep-2013 */

   if t_len > 32000
   then
   dbms_lob.writeappend( t_xxx, t_len, t_tmp );
   t_tmp := null;
   t_len := 0;
   end if;
   t_tmp := t_tmp || t_cell;
   t_len := t_len + length( t_cell );
   t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
   end loop;

   —————————————————————————

   instead of :

   —————————————————————————

   –changes to function finish: loop through cells. Modified t_cell := statment to include tag for forumla
   while t_col_ind is not null
   loop
   t_cell := ”;

   if workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).type = ‘f’ then
   t_cell := t_cell||”
   || workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).formula
   || ”;
   end if;
   t_cell := t_cell||”
   || to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
   || ”;
   if t_len > 32000
   then
   dbms_lob.writeappend( t_xxx, t_len, t_tmp );
   t_tmp := null;
   t_len := 0;
   end if;
   t_tmp := t_tmp || t_cell;
   t_len := t_len + length( t_cell );
   t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
   end loop;
   t_tmp := t_tmp || ”;
   t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
   end loop;
   —————————————————————————

   Nice job Anton.

   Best regards,

   euelvis

30. Robyn,

    I have added formulas to my cells. Here are the changes I’ve applied:

    Forgive me if there is a better way to post my changes. This is my first time posting code changes.

    Mike Chambers

    ———————————–
    —- package changes start
    ———————————–
    procedure cell
    ( p_col pls_integer
    , p_row pls_integer
    , p_value number
    , p_numFmtId pls_integer := null
    , p_fontId pls_integer := null
    , p_fillId pls_integer := null
    , p_borderId pls_integer := null
    , p_alignment tp_alignment := null
    , p_sheet pls_integer := null
    , p_type varchar2 default ‘v’ — if = ‘f’ then add p_formula to cell
    , p_formula varchar2 default null — cell formula
    );
    —
    procedure cell
    ( p_col pls_integer
    , p_row pls_integer
    , p_value varchar2
    , p_numFmtId pls_integer := null
    , p_fontId pls_integer := null
    , p_fillId pls_integer := null
    , p_borderId pls_integer := null
    , p_alignment tp_alignment := null
    , p_sheet pls_integer := null
    , p_type varchar2 default ‘v’ — added M.CHAMBERS: if = ‘f’ then add p_formula to cell
    , p_formula varchar2 default null –added M.CHAMBERS: cell formula
    );
    —
    procedure cell
    ( p_col pls_integer
    , p_row pls_integer
    , p_value date
    , p_numFmtId pls_integer := null
    , p_fontId pls_integer := null
    , p_fillId pls_integer := null
    , p_borderId pls_integer := null
    , p_alignment tp_alignment := null
    , p_sheet pls_integer := null
    , p_type varchar2 default ‘v’ — if = ‘f’ then add p_formula to cell
    , p_formula varchar2 default null — cell formula
    );
    —
    ———————————–
    —- package changes end
    ———————————–

    ———————————–
    —- package body changes start
    ———————————–
    –M.CHAMBERS: modified to_cell to add formula functionality
    type tp_cell is record
    ( value number
    , style varchar2(50)
    , type varchar2(1) default ‘v’ –added M.CHAMBERS
    , formula varchar2(1000) default null –added M.CHAMBERS
    );

    –M.CHAMBERS: modified to remove case sensitive issue for date format
    function get_numFmt( p_format varchar2 := null )
    return pls_integer
    is
    t_cnt pls_integer;
    t_numFmtId pls_integer;
    t_format varchar2(100) default lower(p_format); –added M.CHAMBERS
    begin
    if t_format is null –M.CHAMBERS: changed from p_format
    then
    return 0;
    end if;
    t_cnt := workbook.numFmts.count();
    for i in 1 .. t_cnt
    loop
    if workbook.numFmts( i ).formatCode = t_format –M.CHAMBERS: changed from p_format
    then
    t_numFmtId := workbook.numFmts( i ).numFmtId;
    exit;
    end if;
    end loop;
    if t_numFmtId is null
    then
    t_numFmtId := case when t_cnt = 0 then 164 else workbook.numFmts( t_cnt ).numFmtId + 1 end;
    t_cnt := t_cnt + 1;
    workbook.numFmts( t_cnt ).numFmtId := t_numFmtId;
    workbook.numFmts( t_cnt ).formatCode := t_format; –M.CHAMBERS: changed from p_format
    workbook.numFmtIndexes( t_numFmtId ) := t_cnt;
    end if;
    return t_numFmtId;
    end;

    –M.CHAMBERS: modified to add formula functionality
    procedure cell
    ( p_col pls_integer
    , p_row pls_integer
    , p_value number
    , p_numFmtId pls_integer := null
    , p_fontId pls_integer := null
    , p_fillId pls_integer := null
    , p_borderId pls_integer := null
    , p_alignment tp_alignment := null
    , p_sheet pls_integer := null
    , p_type varchar2 default ‘v’ –added M.CHAMBERS
    , p_formula varchar2 default null –added M.CHAMBERS
    )
    is
    t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
    begin
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).formula := p_formula; –added M.CHAMBERS
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := p_value ;
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).type := p_type; –added M.CHAMBERS
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := null;
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := get_XfId( t_sheet, p_col, p_row, p_numFmtId, p_fontId, p_fillId, p_borderId, p_alignment );
    end;

    –M.CHAMBERS: modified to add formula functionality
    procedure cell
    ( p_col pls_integer
    , p_row pls_integer
    , p_value varchar2
    , p_numFmtId pls_integer := null
    , p_fontId pls_integer := null
    , p_fillId pls_integer := null
    , p_borderId pls_integer := null
    , p_alignment tp_alignment := null
    , p_sheet pls_integer := null
    , p_type varchar2 default ‘v’ –added M.CHAMBERS
    , p_formula varchar2 default null –added M.CHAMBERS
    )
    is
    t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
    t_alignment tp_alignment := p_alignment;
    begin
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).formula := p_formula; –added M.CHAMBERS
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := add_string( p_value );
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).type := p_type; –added M.CHAMBERS
    if t_alignment.wrapText is null and instr( p_value, chr(13) ) > 0
    then
    t_alignment.wrapText := true;
    end if;
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := ‘t=”s” ‘ || get_XfId( t_sheet, p_col, p_row, p_numFmtId, p_fontId, p_fillId, p_borderId, t_alignment );
    end;
    —

    –M.CHAMBERS: modified to add formula functionality
    procedure cell
    ( p_col pls_integer
    , p_row pls_integer
    , p_value date
    , p_numFmtId pls_integer := null
    , p_fontId pls_integer := null
    , p_fillId pls_integer := null
    , p_borderId pls_integer := null
    , p_alignment tp_alignment := null
    , p_sheet pls_integer := null
    , p_type varchar2 default ‘v’
    , p_formula varchar2 default null
    )
    is
    t_numFmtId pls_integer := p_numFmtId;
    t_sheet pls_integer := nvl( p_sheet, workbook.sheets.count() );
    begin
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).formula := p_formula; –added M.CHAMBERS
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).value := p_value – to_date(’01-01-1904′,’DD-MM-YYYY’);
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).type := p_type; –added M.CHAMBERS
    if t_numFmtId is null
    and not ( workbook.sheets( t_sheet ).col_fmts.exists( p_col )
    and workbook.sheets( t_sheet ).col_fmts( p_col ).numFmtId is not null
    )
    and not ( workbook.sheets( t_sheet ).row_fmts.exists( p_row )
    and workbook.sheets( t_sheet ).row_fmts( p_row ).numFmtId is not null
    )
    then
    t_numFmtId := get_numFmt( ‘dd/mm/yyyy’ );
    end if;
    workbook.sheets( t_sheet ).rows( p_row )( p_col ).style := get_XfId( t_sheet, p_col, p_row, t_numFmtId, p_fontId, p_fillId, p_borderId, p_alignment );
    end;
    —

    –changes to function finish: loop through cells. Modified t_cell := statment to include tag for forumla
    while t_col_ind is not null
    loop
    t_cell := ”;

    if workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).type = ‘f’ then
    t_cell := t_cell||”
    || workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).formula
    || ”;
    end if;
    t_cell := t_cell||”
    || to_char( workbook.sheets( s ).rows( t_row_ind )( t_col_ind ).value, ‘TM9’, ‘NLS_NUMERIC_CHARACTERS=.,’ )
    || ”;
    if t_len > 32000
    then
    dbms_lob.writeappend( t_xxx, t_len, t_tmp );
    t_tmp := null;
    t_len := 0;
    end if;
    t_tmp := t_tmp || t_cell;
    t_len := t_len + length( t_cell );
    t_col_ind := workbook.sheets( s ).rows( t_row_ind ).next( t_col_ind );
    end loop;
    t_tmp := t_tmp || ”;
    t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
    end loop;

31. Hello Anton.

    Thank you! It works well.

32. Hi Robyn
    The code I have put into my Post is just an additional procedure to the original package…
    did you download and compile the package from this site first ?

    once done you can add my procedure to the newly created package.

    Regards,
    Gilles

33. Hello,
    I have the need to add excel formulas to the spreadsheet so that the user can see record counts in the spreadsheet following filtering applied by user post spreadsheet creation.

    Has anyone experimented with adding excel formulas? perhaps have a plsql function already created to add this?

    Thanks, Robyn

34. Hi Anton,
    thanks a lot for this very usefull package.
    I took time to add a personnal “Cursor2Sheet” procedure which works also on Oracle 10 (using XMLType in between

    I Post it here incase it is of any use to anybody :

    /************************************/
    procedure cursor2sheet
    ( p_sql in sys_refcursor
    , p_column_headers boolean := true
    , p_directory varchar2 := null
    , p_filename varchar2 := null
    , p_sheet pls_integer := null
    , p_footer boolean := true
    )
    is

    ctx dbms_xmlgen.ctxHandle;
    tmpXml XMLType ;

    cursor cData is
    Select
    t2.Column_Value.GetRootElement() ColName
    , ExtractValue(t2.Column_Value, ‘node()’) Value
    From Table(XMLSequence(tmpXml)) t
    , Table(XMLSequence(Extract(t.Column_Value,’/ROWSET/ROW/node()’))) t2
    Order by RowNum ;

    tsColHeaders SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL();
    tsValues SYS.DBMS_DEBUG_VC2COLL := SYS.DBMS_DEBUG_VC2COLL();
    t_sheet pls_integer := 1;
    t_cur_row pls_integer := 1;
    colId pls_integer := 1;
    nColNumber pls_integer;
    n pls_integer;

    aTmpVal SYS.AnyData ;
    nNumVal Number ;
    tTSVal TimeStamp ;
    dDateVal Date ;
    sVarcharVal Varchar2(4000) ;
    bGotType boolean ;
    eTypeConvert Exception ;
    eTypeDateFormat Exception ;
    eTypeNonNumeric Exception ;
    eTypeNotDefined Exception ;
    pragma exception_init(eTypeConvert,-6502);
    pragma exception_init(eTypeDateFormat,-1830);
    pragma exception_init(eTypeNonNumeric,-1858);

    begin

    — XML Creation from the sys_refcursor
    ctx := dbms_xmlgen.newContext(p_sql);
    — this is important in order to get all the column headers, even if all data are null
    dbms_xmlgen.SetNullHandling(ctx, dbms_xmlgen.EMPTY_TAG);
    dbms_xmlgen.getXMLType(ctx, TmpXml);

    if p_sheet is null
    then
    new_sheet;
    end if;

    — Load Columns and Values into Arrays
    Open cData ;
    Fetch cData bulk collect into tsColHeaders, tsValues ;
    Close cData ;

    — get distinct headers
    tsColHeaders := set(tsColHeaders) ;
    — get number of headers (of columns)
    nColNumber := tsColHeaders.count ;

    — Create column headers if wanted
    If p_column_headers
    Then

    — set headers into sheet
    For i in tsColHeaders.first .. tsColHeaders.last
    Loop
    cell(i, t_cur_row, tsColHeaders(i), p_fontId => get_font(‘Calibri’, p_bold => true), p_sheet => t_sheet);
    End Loop ;
    t_cur_row := 2;

    End if;

    t_sheet := nvl(p_sheet, workbook.sheets.count());

    — fill cells
    For i in tsValues.first .. tsValues.last
    Loop
    — check if we must reset col to 1 and go to next line
    If i > nColNumber
    and mod(i ,nColNumber) = 1
    Then
    — reset colId to 1 and go to next line
    colId := 1 ;
    t_cur_row := t_cur_row + 1;
    End If ;

    — find the good type and insert into Cell
    — initialize “checker”
    bGotType := false ;

    — Number ?
    If Not bGotType
    Then
    Begin
    aTmpVal := Sys.AnyData.ConvertNumber(tsValues(i)) ;
    bGotType := true ;
    n := aTmpVal.GetNumber(nNumVal) ;
    — load data into cell
    cell(colId, t_cur_row, nNumVal, p_sheet => t_sheet ) ;

    — if conversion fails
    Exception
    When eTypeConvert or eTypeDateFormat or eTypeNonNumeric
    Then
    bGotType := false ;

    End ;
    End If ;

    — TimeStamp ?
    If Not bGotType
    Then
    Begin
    aTmpVal := Sys.AnyData.ConvertTimestamp(tsValues(i)) ;
    bGotType := true ;
    n := aTmpVal.GetTimeStamp(tTSVal) ;
    — load data into cell
    cell(colId, t_cur_row, to_date(tTSVal), p_sheet => t_sheet ) ;

    — if conversion fails
    Exception
    When eTypeConvert or eTypeDateFormat or eTypeNonNumeric
    Then
    bGotType := false ;

    End ;
    End If ;

    — Date ?
    If Not bGotType
    Then
    Begin
    aTmpVal := Sys.AnyData.ConvertDate(tsValues(i)) ;
    bGotType := true ;
    n := aTmpVal.GetDate(dDateVal) ;
    — load data into cell
    cell(colId, t_cur_row, dDateVal, p_sheet => t_sheet ) ;

    — if conversion fails
    Exception
    When eTypeConvert or eTypeDateFormat or eTypeNonNumeric
    Then
    bGotType := false ;

    End ;
    End If ;

    — Varchar2 ?
    If Not bGotType
    Then
    Begin
    aTmpVal := Sys.AnyData.ConvertVarchar2(tsValues(i)) ;
    bGotType := true ;
    n := aTmpVal.GetVarchar2(sVarcharVal) ;
    — load data into cell
    cell(colId, t_cur_row, sVarcharVal, p_sheet => t_sheet ) ;

    — if conversion fails
    Exception
    When eTypeConvert or eTypeDateFormat or eTypeNonNumeric
    Then
    bGotType := false ;

    End ;
    End If ;

    — unsupported type
    If Not bGotType
    Then
    raise eTypeNotDefined ;
    End If ;

    — go to next col
    colId := colId + 1 ;

    End Loop ;

    If p_footer
    Then
    — set footer
    cell(1, t_cur_row+2 , ‘Generated ‘||sysdate||’ by ‘||user, p_sheet => t_sheet ) ;
    End If ;

    if ( p_directory is not null and p_filename is not null )
    then
    save( p_directory, p_filename );
    end if;

    exception
    when eTypeNotDefined
    then
    raise_application_error(-20999,’one data has an unsupported type’, false);
    raise;
    when others
    then
    raise_application_error(-20999,’Export to XLSX failed’, true);

    end;

1. Hi ,
   When i tried to install the package it seems that something is missed (package, library..) .For example : “get_font invalid identifier ” or “new_sheet invalid identifier”…

   Can you help in order to compile this very usefull package?

   Many thanks

1. @Btissam The source consists pf one package specification and one packakage body. If you run them unaltered they should compile OK

35. Hello,
    Is it possible to generate xls file (Office 97-2003) ?
    Regards,
    Stefan Pastrilov

36. I know.
    We needed the overload function to get the index of a sheet so we can use that index in the other functions like cell.

37. Hello,
    Great package and functionality but i think i found a possible bug in the query2sheet function of the package.
    In that function you have the t_sheet pls_integer variable.
    you use that variable before a value is assigned to it.
    Because of this you can’t specify what sheet you want to use for the results.
    I think its better you put the following code at the beginning of your function
    begin 
    if p_sheet is null then
    t_sheet := new_sheet;
    else
    t_sheet := nvl(p_sheet, workbook.sheets.count());
    end if;
    As you can see i also needed to overlead the new_sheet so that it returns the index of the new sheet.
     

1. Moving the following line in the procedure a couple of lines up will do, no need for overloading new_sheet
   t_sheet := nvl( p_sheet, workbook.sheets.count() );

   So change it to:
   if p_sheet is null
   then
   new_sheet;
   end if;
   t_sheet := nvl( p_sheet, workbook.sheets.count() );

38. Anyhow this is a very good starting point and helps me a lot!! 🙂

39. @dieterdaniel
    This package does produces the “native” xlsX Excel-format. But at the moment it can’t produce formulas, graphs or macro’s , just “data”

40. Understandable, it’s not the “native” Excel format that PL/SQL is working with.
    I keep playing around with it. As I’m just starting out more examples would be aprecciated if other people already developed more “fancy” code to generate XLS sheets. Especially putting formulas or stuff like graphs into the results.

41. This package is really helpful!! Thanks for sharing it!
    One question: would it be possible to open an existing XLS to edit data in there?

1. @dieterdaniel
   No, that’s not possible.

42. Finally a litle more time to experiment.

    I have modified it to the following:
    <workbookPr date1904=”false” dateCompatibility=”true” defaultThemeVersion=”124226″/>

    Dates seem to be displaying correctly in Excel/win and Excel/Mac.

    Dates are displaying as their decimal value in openOffice, but I can live with that, at least for now.

43. Got it!

    Had a look at the openOffice preferences. Seems that openOffice allows three seperate start dates as the basis for calculating the decimal date.

    – 12/30/1899
    – 01/01/1900 (StarCalc 1.0)
    – 01/01/1904
    The last of these options was checked.
    I noticed that the as_xlsx package used the following formular for date cell contents:
    (p_value – to_date(’01-01-1900′,’DD-MM-YYYY’)

    Changing the openOffice preference to 01/01/1900 yielded immediate results, the dates which previously were years off are now correct.

    Googling “xlsx date format 1900 1904” yields a lot of results, the main difference seems to be between mac/win. I’m on a Mac which may be why the default was 1904.

    I wonder if there is a way of specifying the date initalisation point in the actual xlsx file.

1. @TonyReed
   In the package I use: <workbookPr date1904=”true” defaultThemeVersion=”124226″/>

44. Seems that way.
    Will have to double check the date in office.

    Actually 4 years + 1 day off.
    Very Strange.

45. There seems to be a nls issue as well, possibly related.

    modified the query in the above procedure to:
    select sysdate, to_char(sysdate,”DD/MM/YYYY HH24:Mi”) to_charSysdate from dual

    Result is:
    SYSDATE              TO_CHARSYSDATE
    40881.6090856482    06/12/2011 14:37
    converted decimal to date:
    SYSDATE                TO_CHARSYSDATE
    12/05/2015 14:37:05    06/12/2011 14:37

    Somethings definitely rotten in the state of Norway.
     

1. @TonyReed
   I don’t think it’s a NLS problem. Your to_charsysdate is a String and not a date.
   The problem is in my opinion that OpenOffice doesn’t recognise the used Excel dateformating.
   And did you notice that your converted decimal is one day off?

46. Hi Anton,
    Looks like this might be a specific problem with openOffice. I just tested with a copy of office 2010 – works nicely.
    In openOffice the following procedure returns:
    SYSDATE
    40881.4262384259
    Formatting the decimal as a date turns it into:
    12/05/2015 10:13:47
     
    procedure test_xlsx is
    v_xlsx blob;
    v_mime_type varchar2(255):=’application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’;
    begin
    as_xlsx.query2sheet(p_query=> ‘select sysdate from dual’);
    v_xlsx:=as_xlsx.finish;
    owa_util.mime_header(CCONTENT_TYPE=>v_mime_type, BCLOSE_HEADER=>false, CCHARSET=>’UTF-8′);
    htp.p(‘Expires: ‘||to_char(sysdate, ‘Dy, DD Mon YYYY HH24:Mi:ss’)||’ GMT’);
    htp.p(‘Cache-Control: no-cache’);
    htp.p( ‘Content-length: ‘ || length(v_xlsx) );
    htp.p( ‘Content-Disposition: filename=”‘ || ‘test-‘||to_char(sysdate,’YYYYMMDDHH24miss’)||’.xlsx’ || ‘”‘ );
    owa_util.HTTP_HEADER_CLOSE;
    wpg_docload.download_file( v_xlsx );
    end;
    The procedure can be run at:
    https://production.presstogo.com/mars/xxtest.test_xlsx

    Any ideas what the issue is?

47. Thnx Anton, Now I successfully compiled the package

48. Hi Anton & co,
    I’m having trouble with dates.
    The files return a cryptic decimal value for all date fields.
    e.g. 40734.7118171296

    When manually formatting the cell to a date this is displayed as:
    07/11/2015 17:05:01
    Database parameters are:

    NLS_DATE_FORMAT            DD-MON-RR
    NLS_DATE_LANGUAGE          AMERICAN
    NLS_CHARACTERSET           AL32UTF8

    hope you can point me in the right direction.

1. @TonyReed
   How do you generate cryptic values? Do you use date parameters?

49. Hi, I’m back 🙂
    Some time ago I realized that 11.1 has problems with “utl_compress.lz_compress”, in fact, does not compress at all, therefore large sheets can be really big. This is replacement:
    create or replace and compile java source named zip_blob as
    import java.io.OutputStream;
    import java.util.zip.GZIPOutputStream;
    import java.util.zip.Deflater;
    import java.sql.Blob;
    public class zip_blob
    {
    public zip_blob()
    {
    }
    public static int zip_blob_compressed_out(java.sql.Blob in_blob,java.sql.Blob[] out_blob, float compression)
    {
    final int comp = (int)compression;
    try{
    //zipoutputstream from out_blob
    Blob  zip_out = out_blob[0];
    OutputStream os = zip_out.setBinaryStream(1);
    GZIPOutputStream zos = new GZIPOutputStream(os)    {
    {
    def.setLevel(comp);
    }
    };
    int chunksize = 32768;
    //add data to zip
    long len = in_blob.length();
    long offset = 1;
    byte[] buffer;
    while (offset < len) {
    buffer = in_blob.getBytes(offset, chunksize);
    if (buffer == null)
    break;
    zos.write(buffer, 0, buffer.length);
    offset += buffer.length;
    }
    zos.close();
    out_blob[0] = zip_out;       
    }catch (Exception e) {
    e.printStackTrace(System.out);
    return 1;
    }
    return 0;
    } 
    }
    CREATE OR REPLACE PACKAGE zip_blob AS

    FUNCTION zip_blob_compress(in_blob IN BLOB, out_blob in out nocopy BLOB, comp number)  RETURN number
    AS LANGUAGE JAVA
    NAME ‘zip_blob.zip_blob_compressed_out(java.sql.Blob, java.sql.Blob[], float) return java.lang.int’;

    END zip_blob;
    Commenting out:
    t_blob := utl_compress.lz_compress( p_content);
    and adding:
    t_zip_res integer;
    ….
    t_zip_res := zip_blob.zip_blob_compress(p_content,t_blob,9);
    plus
    do the trick …

    Cheers 🙂

50. Hi ,
    I got following error  while compiling the package.

    Compilation errors for PACKAGE BODY NANO.AS_XLSX
     
    Error: PLS-00201: identifier ‘UTL_FILE’ must be declared
    Line: 95
    Text: T_FH  UTL_FILE.FILE_TYPE;
     
    Error: PL/SQL: Item ignored
    Line: 95
    Text: T_FH  UTL_FILE.FILE_TYPE;
     
    Error: PLS-00320: the declaration of the type of this expression is incomplete or malformed
    Line: 98
    Text: T_FH := UTL_FILE.FOPEN(P_DIRECTORY, P_FILENAME, ‘wb’);
     
    Error: PL/SQL: Statement ignored
    Line: 98
    Text: T_FH := UTL_FILE.FOPEN(P_DIRECTORY, P_FILENAME, ‘wb’);
     
    Error: PLS-00320: the declaration of the type of this expression is incomplete or malformed
    Line: 100
    Text: UTL_FILE.PUT_RAW(T_FH, DBMS_LOB.SUBSTR(P_BLOB, T_LEN, I * T_LEN + 1));
     
    Error: PL/SQL: Statement ignored
    Line: 100
    Text: UTL_FILE.PUT_RAW(T_FH, DBMS_LOB.SUBSTR(P_BLOB, T_LEN, I * T_LEN + 1));
     
    Error: PLS-00320: the declaration of the type of this expression is incomplete or malformed
    Line: 102
    Text: UTL_FILE.FCLOSE(T_FH);
     
    Error: PL/SQL: Statement ignored
    Line: 102
    Text: UTL_FILE.FCLOSE(T_FH);
     
    Error: Hint: Value assigned to ‘T_IND’ never used in ‘NEW_SHEET’
    Line: 299
    Text: T_IND := GET_FONT(‘Calibri’);
     
    Error: Hint: Value assigned to ‘T_IND’ never used in ‘NEW_SHEET’
    Line: 302
    Text: T_IND := GET_FILL(‘none’);
     
    Error: Hint: Value assigned to ‘T_IND’ never used in ‘NEW_SHEET’
    Line: 303
    Text: T_IND := GET_FILL(‘gray125’);
     
    Error: Hint: Value assigned to ‘T_IND’ never used in ‘NEW_SHEET’
    Line: 306
    Text: T_IND := GET_BORDER(”, ”, ”, ”);
     
    Error: Hint: Variable ‘TS’ is declared but never used in ‘FINISH’
    Line: 833
    Text: TS        TIMESTAMP := SYSTIMESTAMP;
    Can you please tell me how to fix it?

    Thnx

1. @Amit. It looks like you are missing the execute privilege on package sys.utl_file

51. Hi…Package is so useful…can you let us know to download the generated .xlsx file using the mime_header.

52. Anton,
    I meant that when I use Your code as_xlsx.cell( 2, 4, 12345, p_numFmtId => as_xlsx.get_numFmt( ‘# ##0′) );
    and excel is produced, in cell there’s formating #\ ##0 used  not # ##0. Package changes ‘ ‘ into ‘\ ‘.
    Regards,
    Peter
     

1. @Peter
   Strange, it doesn’t do that for me. The package puts the format you enter unchanged in the generated xml.

53. Thank You Anton,
    but it’s not working properly for large number (for instance 2 000 000). In excel there’s formatting #\ ##0 not # ##0 :/
    Regards,
    Peter

1. @Peter.
   This pacakge puts whatever format you use in the Excel. If you think that #\ ##0 is a valid Excel number format, use that. If you need something else, use something else.

54. Anton,
    how format number columns in xlxs output for excel  # ##0 ?
    Regards,
    Peter
     

1. Use as_xlsx.cell( 2, 4, 12345, p_numFmtId => as_xlsx.get_numFmt( ‘# ##0’) );

55. Thanks a lot 🙂

56. @Peter
    The funcion finish returns a blob containing the “excel-file”. This blob can be sent as an attachment from a mail

57. Anton,
    What if i’d like to send it by mail without saving?
    Which object should be sent?
    Regards,
    Peter

58. Anton, Further to Morten’s “refcursor2sheet” request.
    Could you refactor query2sheet into a separate “query2sheet” and “cursor2sheet”. The former would do the open cursor/parse then pass the opened cursor to the latter (which would do the describe columns onwards).
    In 11g we can covert the refcursor to a DBMS_SQL cursor and call “cursor2sheet” directly.
    I’ve put those changes in on:
    http://www.sydoracle.com/Codespace/as_xlsx9.txt
     

59. I’v tried that solution but it relay mess up with shared strings mapping in workbook when you have null columns between not null. Additionally it generates a lot of redundant cell data with same shared string that is mapped to  null. Generally  I think better way is to stop generation of null cells  at all, since you got cell reference like ‘A1’ etc in sheet.xml exel can handle this and all xlsx is smaller 🙂

60. damn tags 🙂

    when t_desc_tab( c ).col_type in ( 1, 8, 9, 96, 112 )
    then
    dbms_sql.column_value( t_c, c, v_tab );
    for i in 0 .. t_r – 1
    loop
    if v_tab( i + v_tab.first() ) is not null then
    cell( c, t_cur_row + i, v_tab( i + v_tab.first() ), p_sheet => t_sheet );
    end if;
    end loop;
    v_tab.delete;
    else
    null;
    end case;

    added: if v_tab( i + v_tab.first() ) is not null then … end if;

61. Some troubles with NULL values in query result, it seems like string is the same type as NULL (96) so cannot create shared strings table my quick fix for that is between –> if v_tab( i + v_tab.first() ) is not null then t_sheet );
    –> end if; <–
    end loop;
    v_tab.delete;
    else
    null;
    end case;

1. @jareeq
   You found a bug.
   Changing a line in procedure add_string solves it too
   workbook.strings( nvl( p_string, ” ) ) := t_cnt;
   It looks that the empty string ” isn’t the same as null all the time. At least on my 11XE database.

62. @hUGO
    You can use the p_theme parameter of get_font. But that relates to the standard themes of excel, and so indirect to font-color.
    But I’ve added a rgb parameter to the procedure. That would make it a little bit easier to change font-color.

63. Hi Anton!

    great work!!
    it is possible to give color to the font?

    thanks!

64. At the end, I took a different approach. I loaded the Apache POI jars into the database and wrote a simple java procedure that takes your query as the input and returns the generated xlsx in a blob.

65. Thanks, I tried it, but to no avail. 🙁 I will look into it, if I find a way I am going to let you know.

66. I tried your package but it was slow (2 mins 30 seconds for an excel with column A-Q and 2900 rows). Is it just me or is this normal? This is a package I have been looking for for ages,  but with this speed I cannot use it.  Thank you for your effort.

1. @prunoki See some comments back for an easy way to get it faster

67. hola
    Felicitaciones por tu blog.
    Como puedo cambiar el nombre a las hojas de un libro de excel
    Gracias

1. I prefer comments in Dutch, but German or English are OK to, but Spanish no 🙂
   Do you mean procedure new_sheet( p_sheetname varchar2 := null );???

   begin
   as_xlsx.new_sheet( ‘query 1’ );
   as_xlsx.query2sheet( ‘select 1 r from dual’, p_sheet => 1 );
   as_xlsx.new_sheet( ‘query 2’ );
   as_xlsx.query2sheet( ‘select 2 r from dual’, true, ‘MY_DIR’, ‘my.xlsx’, 2 );
   end;

68. Hi Anton,

    It’s really very nice thing. you just save a lot of time for us. But one thing I have found that while I’m trying to create a large excel file  , suppose it has 4000 rows  470 coulms (i.e. A-RB) it takes 12 minutes to create the file. Could the time be reduced?

    Please help.

    Thanks you once more for your great endeavour.

1. @Partha I think it is possible to get that time down to 3-4 minutes. But I don’t think you will get it fast with that many columns.
   Try changing “t_tmp clob;” to “t_tmp varchar2(32767);”

69. Hi Anton,
    really great work!
    Correct me if I’m wrong, but after taking a quick look at your code it seems you put every string as new string in the SharedStrings.xml. A while ago just out of curiosity I took a closer look at the Office Open XML file format. I remember that one of the reasons MS gave for writing strings to a different file was that in spreadsheets “highly repetitive strings are  very common”. So it should be fairly easy to greatly reduce the file size of the resulting xlsx file by only adding new strings to your tp_strings and just reference it for duplicates.
    Don’t forget to differentiate between count and uniqueCount in your sharedStrings.xml 🙂
    Cheers

1. @Dirk. You are right about the way I handle strings. And you are right about the other things too 🙂 . I will look into it (some day)

70. Excelent work with this library Anton!
    There’s only one thing that confuses me – is it possible that your zip implementation creates files six times larger then those created with Excel 2007? After opening & saving a file created with your library its size decreased from 12MB to 2MB.

1. @Tomaž
   I don’t think it has anything to do with the zip implementation. I place the (formatting) tags around every cell in the sheet. And I suppose Excel does it a little bit smarter, formating a complete column or row in one time.

71. Thanks, Anton.  Great tool…I’ve been doing this the hard way with ult_file and || chr(13) forever.
     
    Cheers.
     
    -abe

72. Hi Anton,
    great! we tried to implement it by our self. then in stumbled across you package. great!
    but very soon we’ve got  an issue:
    2 out of 21 Columns do not appear when opening the xlsx file in excel. they are marked as hidden. to show them youhave to  expand them manually. both colums are filled with date values and are empty (null) in the first couple of rows.
    using excel i saved the xlsx as xml, without any changes and found this section “<Worksheet ss:Name=”Sheet1″>
    <Table ss:ExpandedColumnCount=”24″ ss:ExpandedRowCount=”1563″ x:FullColumns=”1″
    x:FullRows=”1″ ss:DefaultRowHeight=”15″>
    <Column ss:Index=”14″ ss:Hidden=”1″ ss:AutoFitWidth=”0″/>
    <Column ss:Index=”21″ ss:Hidden=”1″ ss:AutoFitWidth=”0″/>”
    in the very beginning of the xml file.
    any idea?
    grtz
    Peter

73. Dear Anton
    Thanks for your help.  It worked after I changed the schema val as non.
    It took me a lot of time tracing this problem before asking you. Thanks again, you did me a great help!!  \^Q^/

74. I also tried other fonts like Arial, Times New Roman but still cannot work….
    My Oracle is installed in the remote server(unix without excel). After generating excel file,  I will get the excel file to my PC. I have no idea whether those fonts were in the server or not but can make suer my pc can display those fonts well?
    Do you mean I should install those fonts in my server? Is that in the Oracle packages? 
      

1. @Stevenquave
   The fonts should be on your client PC. If have tried some different fonts, some did work and some didn’t. After changing one line, 893, in the package body all fonts were working for me.
   Try changing the line <scheme val="minor"/>
   to
   <scheme val="none"/>

75. @Stevenquave
    Strange, that code works for me. Do you have the Calibri -font installed on your computer?

76. Your sample code is quite useful ..  Before that, I used .Net to make excel file, but it took much of my time waiting for generating files through connecting database and file formatting. 
    Tried to set the font name as the code below, but the font name cannot display well and always show as the default font setting of my pc.
    Do you have any suggestions about that? Look forward for your reply and thanks in advance.

    cell( c, t_cur_row + i, v_tab( i + v_tab.first() ), p_sheet => t_sheet,p_borderId => as_xlsx.get_border( p_bolder, p_bolder, p_bolder, p_bolder),p_fontId=>as_xlsx.get_font(‘Calibri’,p_fontsize=>8));

    Stevenquave

77. Thank you for updating. It works now.

78. Hi Anton,
    very useful package. Thanks for sharing.
    I try to put “&” in a column but it shows “&amp;”. Is there any tricks to do this in your package.
     
    Thanks

    David

1. @dpigetyphie Oops, you found a bug. Doing XML-escaping twice isn’t double so good. I fixed it, see as_xlsx

79. @Morton: You should be able to solve your SYS_REFCURSOR problem with this function:
    DBMS_SQL.TO_CURSOR_NUMBER
    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_sql.htm#CHDJDGDG
    As fare as I know its only available in 11g

80. Hi Anton,
    really great package. I only found one little problem when using time zones. If you execute this example:
    begin
    EXECUTE IMMEDIATE ‘ALTER SESSION SET TIME_ZONE =”Australia/Sydney”’;
    as_xlsx.query2sheet( ‘select rownum, x.*
    , case when mod( rownum, 2 ) = 0 then rownum * 3 end demo
    , case when mod( rownum, 2 ) = 1 then ”demo ” || rownum end demo2 from dual x connect by rownum <= 5’ );
    as_xlsx.save( ‘MY_DIR’, ‘my.xlsx’ );
    end;
    You get an invalid file. I suggest to use this code instead:
    <dcterms:created xsi:type=”dcterms:W3CDTF”>’ || to_char( current_timestamp, ‘yyyy-mm-dd”T”hh24:mi:ssTZH:TZM’ ) || ‘</dcterms:created>
    <dcterms:modified xsi:type=”dcterms:W3CDTF”>’ || to_char( current_timestamp, ‘yyyy-mm-dd”T”hh24:mi:ssTZH:TZM’ ) || ‘</dcterms:modified>

1. @Björn: Thanks for the feedback and the solution! I will change it, see new version as_xlsx

81. Thank you for your support. Very nice work!
    I will wait for new releases 🙂

82. @Vasya I have added bold and italic fonts. I had already parameters for that, but I didn’t used those parameters. But now it functions OK. I have no plans for adding row_height or PageSetup Orientation. But who knows, maybe if I have some spare time and I get bored ….

83. Anton!
    Thank you very much for mergecells!!!
    But not work bold font.
    And desirable features, if possible:
    – set_row_height
    – set PageSetup Orientation and FitToPagesWide.
    With these possibilities it would be amazing package!!! 🙂

84. We still use Excel 2003 with a xlsx-converter. That seems to mess up the autofilter without the definedNames tag. When I use Excel 2010 it works fine. Sorry!

85. Thank you for this post, Anton. You are saving me the trouble to figure out the new excel xml structure on my own. I did it once with the former 2003 spreadsheetml…
    While playing around with it I came across two problems:
    – our database language is set to German, thus decimals are represented by ‘,’ instead of ‘.’ That messes up alle non-integer number cells and date cells. When I set the nls_language to american it works.
    – the autofilter function is missing a <definedNames> tag in the workbook.xml file. so the autofilter will be rendered but doesn’t work. I added the following code between the </sheets> and the <calcPr> tags
    bFirst := true;
    for s in 1 .. workbook.sheets.count() LOOP
    if workbook.sheets( s ).autofilters.count() > 0 THEN
    if bFirst then
    t_xxx := t_xxx || ‘
    <definedNames>’;
    end if;

    t_col_min := 1;
    t_col_max := 1;
    t_row_ind := workbook.sheets( s ).rows.first();

    while t_row_ind is not null loop
    t_col_min := least( t_col_min, workbook.sheets( s ).rows( t_row_ind ).first() );
    t_col_max := greatest( t_col_max, workbook.sheets( s ).rows( t_row_ind ).last() );
    t_row_ind := workbook.sheets( s ).rows.next( t_row_ind );
    end loop;

    for a in 1 ..  workbook.sheets( s ).autofilters.count() loop
    t_xxx := t_xxx || ‘
    <definedName name=”_xlnm._FilterDatabase” localSheetId=”‘ || (s – 1) || ‘” hidden=”1″>Sheet’ || s || ‘!$’ ||
    fk_alfan_col( nvl( workbook.sheets( s ).autofilters( a ).column_start, t_col_min ) ) || ‘$’ ||
    nvl( workbook.sheets( s ).autofilters( a ).row_start, workbook.sheets( s ).rows.first() ) || ‘:$’ ||
    fk_alfan_col( nvl( workbook.sheets( s ).autofilters( a ).column_end, t_col_max ) ) || ‘$’ ||
    nvl( workbook.sheets( s ).autofilters( a ).row_end, workbook.sheets( s ).rows.last() ) ||
    ‘</definedName>’;
    end loop;

    if bFirst then
    t_xxx := t_xxx || ‘
    </definedNames>’;
    bFirst := false;
    end if;
    end if;
    end loop;

1. @Vasya: You can’t merge cells, like I said in the blog I build this package for unloading data, not to create fancy spreadsheets.
   @Achim: You are right about the NLS-settings, I will fix that some day. But Autofilters work for me without definedNames, at least with Excel 2010. I will test it with Excel 2007.

86. Anton, how can I merge cells?

87. @Pradeep. Images is also one of the things I deliberately left out of this package. It takes quite some coding to add this functionality.

88. Thanks Anton for the post. I already did some minor version of this and there I was having a problem to include a picture/.jpeg to my excel file.
    Can you please let me know if you know any solution for that.

89. @Marc, I have added the possiblity to set aligments for a data cell, this also include the possibilty to add the wrapText option. And it also automaticly sets the wrapText option when a data cell contains a chr(13). as_xlsx

90. Thanks Anton.
    Is there a way I can specify a data cell (or column of cells) that contain carriage-returns  – chr(13) – to have the Wrap Text option in the xlsx set to True?

91. @Morten. In this case it’s a bit different, to read a (unknown) refcursor every column is turned into a string, even numbers and dates. And its (very) difficult to detect a number or date and show these in the right way in the Excel file. But feel free to do it yourself, and I’m happy to include it in the official version 🙂

92. Very nice and very useful, Anton! Thanks for sharing.
    I have a small enhancement request: Can you add a “refcursor2sheet” procedure? It should work along the same lines as “refcursor2pdf” from the example to your PDF package. I could add it myself, but I’m sure others would find this useful as well, so it’s perhaps better if you add it to the “official” version of your package.
    – Morten

Comments are closed.