Thursday, 3 May 2007

Export to Excel - Unwrapped


The packages for Export to Excel for ApEx and XE are now unwrapped and available for download:

http://htmldb.oracle.com/pls/otn/f?p=31517:108

http://htmldb.oracle.com/pls/otn/f?p=31517:109







You may use them and modify as you need. It has been downloaded arround 300 times in the last two months. Shows that it works and it is needed, despite of the new BI publisher feature available for ApEx 3.0.

The method is quite simple. You need to generate HTML tables and it will be readable as a normal .xls file from 97 to the latest version. This gives an option to create multiple pages in excel and export multiple reports in one run as one file. Of course, it needs some modification within the package to do that.

Have fun.

on

21 comments:

  1. Patrick Wolf3 May 2007 at 20:35

    Hi Denes,

    looks interesting. I think I will have a closer look at your package.

    Greetings
    Patrick

    ReplyDelete
  2. Denes Kubicek3 May 2007 at 23:57

    Patrick,

    Thanks. But, please, no closer looks. It will definitelly be a disaster.

    Denes Kubicek

    ReplyDelete
  3. Anonymous6 May 2007 at 17:08

    Nice work, Denes.

    Any reason why it doesn't support reports with "plsql function body returning SQL query"?

    ReplyDelete
  4. Denes Kubicek7 May 2007 at 17:25

    Well, first of all, I didn't think of that. After all, the code is open and you may try to hack yourself a bit.

    Denes Kubicek

    ReplyDelete
  5. Anonymous8 May 2007 at 10:35

    Hello Denes,

    Thank you for all your effort, like the export function and the demo application!!!

    Makes life easier.

    Greetings,
    Geert

    ReplyDelete
  6. Prabahar14 September 2007 at 13:07

    Hi Denes,
    Thanks for great export functionality..
    When using this funcyion in excel date field tranformed to Number any idea why?

    Prabahar

    ReplyDelete
  7. Archu23 September 2007 at 06:21

    Hi Denes,
    Thanks for this wonderful post.

    I want to know,can we get formatted excel report ( i mean particular background color,font,maillink for email id field etc )?

    If yes, do we need to do some modification in the package ?
    Thanks,
    Archana

    ReplyDelete
  8. Anonymous14 February 2008 at 15:11

    This comment has been removed by a blog administrator.

    ReplyDelete
  9. Anonymous16 April 2008 at 17:12

    Denes,

    I am having some problems with the excel export feature. I did everything stated in your readme file. But I get an Oracle error (ORA-00936) when I open the Excel spreadsheet. I posted it in the APEX Forum (see below). Can you please tell me how to fix the problem.

    http://forum.oracle.com/forums/thread.jspa?messageID=2465340&#2465340


    Robert

    ReplyDelete
  10. Unknown29 August 2008 at 22:46

    Doh! Where was this post a year ago? I was looking all over for APEX to XLS until I figured out a rudimentary version myself. Simple tutorial available here:

    http://spatnark.com/docs/APEX_XLS.pdf

    Thanks for the tip though. Package might be cleaner. Also it seems this thread has some spam...

    ReplyDelete
  11. Unknown27 May 2009 at 12:42

    Great package!!

    Anonymous, I had the same problem that you had. When exporting to excel I got a error message including the sql, which looked something like this:

    select from (my query), the error came because there is a * or a column list missing in the outer sql.

    Looking to figure out why, I found mention of the include in print column functionality. So I entered this tab, removed one column from the printing part, and hit recalculate. I went out to the export and it worked nice. So I went back in to the printing part and included the column again, and reset column with, the export still worked... so some wierd bug, but one that you can get around.

    ReplyDelete
  12. Alex3 August 2009 at 21:11

    Lately I had unpleasant problem,exactly unexpectedly all my xls files were corrupted.And I didn't know what to do.But to my surprise myself helped tool from one soft forum-advanced xlsx recovery.It is free as far as I know,moreover utility recover corrupted xlsx file with the original file.

    ReplyDelete
  13. Unknown10 November 2009 at 08:05

    Hi, Denes. I am very happy to use your package for "Excel" saving data. Though I've got an one little trouble - all the fields are saved as a text (even if it is number or date) by default. What if I would like to save some fields with defined data type (date, for example)? What should I do in this case? May HTML table consist such a value keeping data type?

    ReplyDelete
  14. Unknown10 November 2009 at 08:07

    Please write me to hordework at gmail dot com, if you will find some seconds to answer. Thank you bardzo!!! -)

    ReplyDelete
  15. Lionel16 January 2012 at 09:16

    Hello, I have only one problem only the last column is exported in the XLS sheet.

    Is someoneelse having this problem ?

    Thanks

    ReplyDelete
  16. Romina OJEDA30 January 2012 at 19:35

    Hi Denes,

    I would like to ask about a problem that I'm facing with the export to excel functionality.
    I have a page with a classic report that shows a large number of records (about 200.000) and the user wants to download the information to excel. I have enabled the csv output in the report attributes section but the file generated only has 500 rows. I know that the limit in excel is 65000 rows but why the apex report is pulling only 500 rows? is there a setup anuwhere so I can increase that value? And also, is there a way so I can show a message to the user indicating that 65000 rows is the maximun in excel?

    I have also try to use your package to generate a xls format file but without success. The first problem that I had was stringbuffer too small, that is because my report shows more than 200 colums! I modified the package to build the query in a "hardcoded" way for my report in order to avoid the use of varchar variables but the report never ended. I do not know if it was a performance issue or a problem in the code. After 20 minutes executing I received a timeout error message.

    That it why I am trying to go back to csv output resolving the 500 rows limit issue.

    Hope you can help me!

    Thanks in advace,
    Romina

    ReplyDelete
  17. Anonymous14 February 2012 at 20:52

    Lionel, I promise to suggest some ways to resolve your problem tomorrow. I've made some changes in Denes' code and there is a little bit about columns and columns number in Excel file.

    ReplyDelete
  18. Unknown15 February 2012 at 10:11

    Lionel, look, here is my snippet from the proсedure export_exce_pkg.print_report_header:

    FOR c IN
    (SELECT column_alias, NVL (heading, column_alias) heading, format_mask
    FROM apex_application_page_rpt_cols
    WHERE page_id = p_page_id
    AND application_id = p_app_id
    AND region_id = TO_NUMBER (LTRIM (p_region, 'R'))
    AND NVL(include_in_export, 'Yes') = 'Yes'
    -- and column_is_hidden = 'No'
    ORDER BY display_sequence)
    LOOP
    v_number_of_cols := v_number_of_cols + 1;
    v_column_header_list :=
    v_column_header_list || ';' || REPLACE (c.heading, ';', ' ');
    v_column_alias_list := v_column_alias_list || ';' || c.column_alias;

    -- apply column formatting
    IF c.format_mask IS NOT NULL
    THEN
    v_column_select_list :=
    v_column_select_list || ',to_char(' || c.column_alias || ',''' || c.format_mask || ''') ' || c.column_alias;
    ELSE
    v_column_select_list := v_column_select_list || ',' || c.column_alias;
    END IF;
    END LOOP;

    The most important thing here is that how cursor gets rows for the cycle. Sometimes columns in a report are not marked for export automatically so I added the condition, if column is not marked for an export, cursor should get it as if it would marked for an export:
    ...
    AND NVL(include_in_export, 'Yes') = 'Yes'

    Check field 'include_in_export' for your columns in that table
    apex_application_page_rpt_cols with appropriate application_id, region_id, page_id.
    Probably the decision is out here. :)

    ReplyDelete
  19. Anonymous26 March 2012 at 16:18

    Romina OJEDA,

    you can use the csv output itself by changing the report attributes. under layout and pagination you'll find maximum row count. change it to a 500000. by default value will be 500 and thats why you are getting only 500 rows.

    Hope it helps,
    Kalesh.

    ReplyDelete
  20. Unknown19 March 2017 at 16:09

    Hi Dense,

    I tried to download the export excel package from your blog, but access is denied.


    Kindly help me to download the export excel package.


    URL I tried to download the package

    https://apex.oracle.com/pls/otn/f?p=31517:108:9124761526551:::::

    Thank you in advance.

    Regards,
    Ibrahim Sayyed.

    ReplyDelete
  21. Denes Kubicek19 March 2017 at 19:17

    Ibrahim,

    this is outdated. Use the export to excel plugin instead. Go to apex.world and search for the plugins there.

    https://apex.world/ords/f?p=100:710:16539408186198::::P710_PLG_ID:GPV_IR_TO_MSEXCEL

    Regards,

    Denes

    ReplyDelete