Create an Excel-file with PL/SQL

135

For this project I took an Apex-plugin I have written, (IR) Report to Excel (xlsx), and turned it into a PL/SQL package. With this package it’s very easy to create an Excel 2007 file with only a few lines of PL/SQL code.

begin
  as_xlsx.query2sheet( 'select * from dual' );
  as_xlsx.save( 'MY_DIR', 'my.xlsx' );
end;

The main purpose for this package is getting data from the database into an Excel file, so I deliberate did not include some Excel functionality, such as formulas, into the package. Excel itself is a far better tool for such things.

Anton

The source code for the package: as_xlsx
P.S. I have added the possibility to add Comments and MergedCells to the Excel-file
P.S.2 And bold/italic fonts
P.S.3 Fixed issue with timezones with a regionname
P.S.4 Fixed issue with XML-escaping from text
P.S.5 Fixed NLS-issue with column width
P.S.6 Added p_rgb to get_font
P.S.7 Fixed bug in add_string
P.S.8 Fixed set_autofilter (only one autofilter per sheet, added _xlnm._FilterDatabase)
Added list_validation = drop-down
P.S.9 Added freeze_pane

Share.

About Author

135 Comments

  1. Michael Gilmore on

    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?

    • Anton Scheffer on

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

      • 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!!

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

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

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

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

  7. Ajit Bakshi on

    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

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

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

    • 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!

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

    • Anton Scheffer on

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

      • 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!

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

    • Anton Scheffer on

      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.

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

    • Anton Scheffer on

      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.

  13. Anton Scheffer on

    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?

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

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

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

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

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

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

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

    • Anton Scheffer on

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

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

    • Anton Scheffer on

      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.

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

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

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

        • Anton Scheffer on

          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.

          • 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

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

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

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

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

  26. Mike Chambers on

    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

    • 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

  27. Mike Chambers on

    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;

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

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

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

    • 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

      • Anton Scheffer on

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

  31. James Van Zandweghe on

    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.

  32. James Van Zandweghe on

    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.
     

    • Anton Scheffer on

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

  33. Anton Scheffer on

    @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”

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

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

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

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

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

    • Anton Scheffer on

      @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?

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

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

  41. 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 :-)

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

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

    • Anton Scheffer on

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

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

    • Anton Scheffer on

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

  45. Anton Scheffer on

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

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

  47. 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 :-)

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

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

    • Anton Scheffer on

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

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

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

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

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

  53. Partha Sarathi Paul on

    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.

    • @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);”

  54. Dirk Leiendorf on

    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

    • Anton Scheffer on

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

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

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

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

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

  58. 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^/

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

    • Anton Scheffer on

      @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"/>

  60. Anton Scheffer on

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

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

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

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

  64. Anton Scheffer on

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

  65. 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!!! :)

  66. Achim Rosenfeld on

    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!

  67. Achim Rosenfeld on

    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;

    • Anton Scheffer on

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

  68. Anton Scheffer on

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

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

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

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

  72. @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 :)

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

Leave a Reply