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.
Thursday, 3 May 2007
Export to Excel - Unwrapped
Subscribe to:
Post Comments (Atom)
21 comments:
Hi Denes,
looks interesting. I think I will have a closer look at your package.
Greetings
Patrick
Patrick,
Thanks. But, please, no closer looks. It will definitelly be a disaster.
Denes Kubicek
Nice work, Denes.
Any reason why it doesn't support reports with "plsql function body returning SQL query"?
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
Hello Denes,
Thank you for all your effort, like the export function and the demo application!!!
Makes life easier.
Greetings,
Geert
Hi Denes,
Thanks for great export functionality..
When using this funcyion in excel date field tranformed to Number any idea why?
Prabahar
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
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�
Robert
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...
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.
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.
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?
Please write me to hordework at gmail dot com, if you will find some seconds to answer. Thank you bardzo!!! -)
Hello, I have only one problem only the last column is exported in the XLS sheet.
Is someoneelse having this problem ?
Thanks
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
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.
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. :)
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.
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.
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
Post a Comment