APEX

That is slick. It worked fine using Firefox v1.0, Excel 2003, on Windows XP. I got the normal Open or Save to disk dialog.

I will ask the obvious, "How'd you do it?"

Mike

Hello Denes,

I've tried it with IE7 and Excel 2003 and it seems to work just fine.

Well done,
Arie.

Works great Denes!

Is it also possible to upload an .xls instead of .csv?
(like Vikas does with .csv here http://htmldb.oracle.com/pls/otn/f?p=38131:1)

Works great on Excel 2007, FF2 and Vista.

great job Denes.

Abe

Hi Denes,

I have Excel 2000 and IE 6.0. Works super.
Your Excel looks as if you applied VBA method Coulmn.AutoFit.
Val

Mike,

It is easy - I generate html.

I made a demo package available here:

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

for testing. As soon as I'm done with it I will open it for modifications. The package is
generic (let us say in 95% of the cases it should work). I didn't test it with PL/SQL
reports but I doubt it will work with those. However, it should work with all normal
reports. The how - to is also attached.

Basically, you need to create three application items and one conditional page process.
After that, you may create links for exporting to excel as you do it with .csv.

Your feedback is welcome.

Denes Kubicek

Hi Denes;

It seems that i don't have clean export. I got the following messages:

Problem During download. Problem came up in the following areas during load:

missing file: c:\i\css\core_v22.css
missing file: c:\i\themes\theme_9\theme_V2.css

Please note that APEX is installed on my D drive.

Thanks

I noticed that this happens when you forget to:

1. pass the right report title

2. forget to pass the page parameter on which report is located or pass the wrong page
number

I still need some time to clean up the package so mistakes like these get handeled.

What is your name? I think it would be fair if you would sign your postings :)

Denes Kubicek

Hi Denes,

i downloaded the package from your page, but i have trouble getting it to work on Oracle XE.

When i try to install the package i get the message that the body was created with a warning.

This is the output of show errors:

Fehler bei PACKAGE BODY EXPORT_EXCEL_PKG:

LINE/COL ERROR
-------- -----------------------------------------------------------------
255/7 PL/SQL: SQL Statement ignored
257/14 PL/SQL: ORA-00942: Tabelle oder View nicht vorhanden


Thanks for help in advance,
Dirk

Hi Denes,

It looks very nice... although I haven't got it running yet :)

I think there is a small error in the sql file

CREATE OR REPLACE PACKAGE BODY developer.export_excel_pkg wrapped

The schema name should not be there... right?

Rob

I downloaded and installed the package.
When trying to download a report to Excel I received the following error:
ORA-01422: exact fetch returns more than requested number of rows / -1422

BTW, looks very nice.

Greets,
Guido

I am still working on this package. Probably, you have two reports on the same page
having the same title?

I corrected the package and I use now region_id instead of the title.

The package works for ApEx 2.2 and higher - it is looking for the region source and
using it to generate the report.

For XE and lower versions, there is no such view where you could get the region source
information (as far as I know) and for those purposes, I would need to create a separate
package which would require some select grants on the corresponding tables.

Eventually, if I have some spare time, I will provide that package.

Denes Kubicek

You were wright. I had two regions having the same title.
But now I'm receiving the following error:
Report Header Error: ORA-00942: table or view does not exist / -942

Greets,
Guido

Life could be easier. Couldn't it?

This error is because you are using either a lower version of ApEx or XE. In the readme
of my demo it says it works on 2.2 and higher only.

However, it was easier to get it working for XE. I provided a download link for XE here:

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

I did some basic testing only and it worked. You need to follow the instructions carefully
since some grants are required prior to installing anything.

Denes Kubicek

The APEX version I'm using is 2.2.1.00.04.
After downloading your latest version, the error message changed to:
Report Header Error: ORA-01403: no data found / 100

Greets,
Guido

Since I changed from Report Title to Region ID, you need to go through the Readme.pdf
again and

a) delete one page item T_REPORT_NAME

b) create another one (T_REGION_ID)

c) and correct your link and your application process by replacing the old item name
with the new one.

Sorry, but as I said, the package is in development and no guarantees therefor.

Denes Kubicek

I just updated the package with a newer version. Whoever may be interested can download
and test. Your feedback is appreciated.

Denes Kubicek

The above mentioned error messages I received where due to the fact that I installed the package in another schema, created a public synonym and granted execute rights to PUBLIC.
When installing the package in the APEX parsing schema it works.
Is it possible to only install the package once and have it available to all APEX parsing schemas?

Another question. Are there any SQL restrictions in using your package. I'm having a rather complex SQL report, and this report generates the following error:
Report Header Error: ORA-01403: no data found / 100

Greets,
Guido

Guido,

I didn't test sharing the package with other schemas. I will do that. Normaly, if you grant
execute to any schema, it should be available there as well. However, I will test.

To your second question. The header error happens, when no SQL behind the report
can be found. This means that the package got the wrong input. I changed the
application names recently (posted in this thread), the application process and the
link for generating the excel file. Check the Readme.pdf once again and make sure
that you have the required items, process and the link as the new version of the package
expects.

Consider also the following:

If you are saying a complex report then I assume the same could also happen as for normal .csv export.
You probably have some items as binds for your report. I experienced
the same behaviour by not having a computation on some items and loading the
page report would show the header but no data. After computing
the items on load or submitting the report (submit button), the export was working.

The package is getting the SQL behind the report, replacing the :p_item variables with
v('p_item') and fetching the values. If the values are not assigned, the header will be
generated but no values will be fetched.

Denes Kubicek

Hi, Denes, i've followed your instruction about exporting report to excel file under XE envronment, the problem is that now when i open the generated file i see an error in the first row before the columns headings. The error is
Report Values Error: ORA-06550: riga 12, colonna 12: PL/SQL: ORA-00942: tabella o vista inesistente
(table or view does not exists..) and also
PLS-00364: uso della variabile di indice ciclo 'C' non valido ORA-06550: riga 17, colonna 100: PL/SQL this error is repeated for many times and last
PLS-00364: uso della variabi / -6550 / select.......
The XE user is flows_020100 , the user that owns and runs the application is vsg so i granted him the execute on dbms_sql and the select on wwv_flow_page_plugs. Have i forgotten something else?
Thanks.

What kind of report are you running? Can you post the code? I assume that report is
showing proper values?

Denes Kubicek

Hi Denes sorry but i've had problems with my adsl line.This is the query i use to obtain my report, it shows correct result in "video printing" and only after i've added the Download_excel feature i have the errors after clicking on download to excel button and opening "my_report.xls" file instead of saving it on my hd

SELECT
"MOVIMENTI_CONTABILI"."DATA_MOVIMENTO",
"MOVIMENTI_CONTABILI"."TC",
"CAUSALI"."DESCRIZIONE",
"SOCI"."COGNOME",
"SOCI"."NOME",
"MOVIMENTI_CONTABILI"."IMPORTO"*decode ("MOVIMENTI_CONTABILI"."TC",0,1) entrate,
"MOVIMENTI_CONTABILI"."IMPORTO"*decode ("MOVIMENTI_CONTABILI"."TC",1,1) uscite,
"MOVIMENTI_CONTABILI"."NOTE"
FROM
"MOVIMENTI_CONTABILI",
"SOCI", "CAUSALI"
WHERE "MOVIMENTI_CONTABILI"."DATA_MOVIMENTO"
BETWEEN :P17_DA_Data AND :P17_A_Data
AND "SOCI"."N.TESSERA_SOCIALE" = "MOVIMENTI_CONTABILI"."OPERATORE"
AND "MOVIMENTI_CONTABILI"."CAUSALE" = "CAUSALI"."CODICE_CAUSALE"
ORDER BY "MOVIMENTI_CONTABILI"."DATA_MOVIMENTO"

You should download the latest version of the package. I found some mistakes in it
and corrected it accordingly. However, apex.oracle.com seems to be down at the moment.

After you reinstall the package, your export will work.

Denes Kubicek

Denes what kind of package are you talking about? If you intend export_to_excel_xe_pkg.sql I've downloaded it next week from
http://htmldb.oracle.com/pls/otn/f?p=31517:64,
is there a new version?
Thanks

Exactly. I may update and change the package frequently. The version date you see
in my app on the page where you download it. Yesterday evening I changed it as I said,
becouse of some errors in it.

Denes Kubicek

ok tanks, i'll send you a feedback as soon as possible.

Denes, it works !! Thanks for your support.
Bye

The package has been reworked and now it works for all "normal" SQL reports. Soon, I will
unwrap it and show how I did that.

Denes Kubicek

"The package has been reworked and now it works for all "normal" SQL reports. Soon, I will unwrap it and show how I did that."


Denes, soon please :-)

I will post this end of this month. I am currently bussy and have no time to do it.

Denes Kubicek

Hi Denes

When I run ’export_to_excel_xe_pkg.sql’, I get warning: Package body created with compilation errors.

I have implemented as per your guidelines for a tabular form report but get this error when I try to download in excel.

ORA-06550: line 2, column 4: PLS-00201: identifier 'EXPORT_EXCEL_XE_PKG.PRINT_REPORT' must be declared ORA-06550: line 2, column 4: PL/SQL: Statement ignored

Rajkum

If the package doesn't compile, then you need to see what the error is. Please post the
error message to see why the package doesn't compile instead of looking into the report
error.

Denes Kubicek

Did mistake, installed it in wrong schema.

Now I get different error. I am able to open excel sheet but it does not contain any data only an error line

Report Header Error: ORA-00942: table or view does not exist / -942

The table do exists. The query is

select
"EMP_ID",
"NAME",
"EMAIL",
"PHONE_NUMBER",
from "#OWNER#"."EMPLOYEE"

Message was edited by:
rajkum

Your query syntax is wrong. You don't need comma after phone_number.

Have you tried with this one:

SELECT emp_id, NAME, email, phone_number
  FROM employee

?

Denes Kubicek

Pretty cool Denes.
With the normal export to csv some clients have problems getting it directly in Excel as their seperator is different (regional settings I suppose).
Your solution should work in all cases I think.

Dimitri

Dimitri,

It doesn't work for:

PL/SQL Function Returning Query - but it could be modified for that purpose
and currently, doesn't work for reports with grouping - that could be modified as
well. However, since the printing of reports in 3.0 also offers an Excel export (report), I
didn't touch this solution for a while now.

Denes Kubicek

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. There have been arround 300
downloads in the last two months. Shows that it works and it is needed.

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.

Denes Kubicek

Hi,
I downloaded the latest package and followed the instructions. When I click on download excel I get the following error in the excel file

Report Header Error: ORA-01403: no data found / 100

My Report sql is

SELECT CASE WHEN ename IS NULL THEN 'Total Department:' || deptno
when ename = 'zz' then 'Grand Total:'
ELSE CASE WHEN leading_dept = '1000' THEN TO_CHAR (deptno)

ELSE NULL
END
END deptno,
CASE WHEN ename IS NULL THEN ''
ELSE ename
END ename,EMP_TITLE,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (JAN)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (JAN)
END JAN,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (FEB)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (FEB)
END FEB,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (MAR)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (MAR)
END MAR,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (APR)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (APR)
END APR,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (MAY)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (MAY)
END MAY,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (JUN)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (JUN)
END JUN,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (JUL)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (JUL)
END JUL,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (AUG)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (AUG)
END AUG,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (SEP)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (SEP)
END SEP,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (OCT)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (OCT)
END OCT,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (NOV)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (NOV)
END NOV,
CASE WHEN ename LIKE ' ' THEN 'Salary'
WHEN ename IS NULL THEN TO_CHAR (SUM (DEC)
OVER (PARTITION BY deptno ORDER BY ename)
)
ELSE TO_CHAR (DEC)
END DEC
FROM (
SELECT deptno,
LAG (deptno, 1, '1000')
OVER (PARTITION BY deptno
ORDER BY ename ) leading_dept,
ename,EMP_TITLE,JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC
FROM
(

select (case when emp_dept_code ='AH' then 'Client Services'
when emp_dept_code ='AFN' then 'Finance'
when emp_dept_code ='CRE' then 'Creative'
when emp_dept_code in ('PDT', 'SD','PDT') then 'Production/Studio/Traffic' end) deptno,
EMP_ID, emp_name ename, EMP_TITLE,
SUM (case when to_char(tim_start_date, 'MON') = 'JAN' THEN TIM_HRS END) JAN,
SUM (case when to_char(tim_start_date, 'MON') = 'FEB' THEN TIM_HRS END) FEB,
SUM (case when to_char(tim_start_date, 'MON') = 'MAR' THEN TIM_HRS END) MAR,
SUM (case when to_char(tim_start_date, 'MON') = 'APR' THEN TIM_HRS END) APR,
SUM (case when to_char(tim_start_date, 'MON') = 'MAY' THEN TIM_HRS END) MAY,
SUM (case when to_char(tim_start_date, 'MON') = 'JUN' THEN TIM_HRS END) JUN,
SUM (case when to_char(tim_start_date, 'MON') = 'JUL' THEN TIM_HRS END) JUL,
SUM (case when to_char(tim_start_date, 'MON') = 'AUG' THEN TIM_HRS END) AUG,
SUM (case when to_char(tim_start_date, 'MON') = 'SEP' THEN TIM_HRS END) SEP,
SUM (case when to_char(tim_start_date, 'MON') = 'OCT' THEN TIM_HRS END) OCT,
SUM (case when to_char(tim_start_date, 'MON') = 'NOV' THEN TIM_HRS END) NOV,
SUM (case when to_char(tim_start_date, 'MON') = 'DEC' THEN TIM_HRS END) DEC
From PN_EMPLOYEES, PN_TIMESHEET_DETAILS
where tim_emp_id = EMP_ID
AND to_char(tim_start_date, 'YYYY') = 2007
group by emp_dept_code, EMP_ID, emp_name , emp_title )
UNION ALL
SELECT DISTINCT null deptno, NULL leading_dept,
'zz' ename, NULL ,
SUM (case when to_char(tim_start_date, 'MON') = 'JAN' THEN TIM_HRS END) JAN,
SUM (case when to_char(tim_start_date, 'MON') = 'FEB' THEN TIM_HRS END) FEB,
SUM (case when to_char(tim_start_date, 'MON') = 'MAR' THEN TIM_HRS END) MAR,
SUM (case when to_char(tim_start_date, 'MON') = 'APR' THEN TIM_HRS END) APR,
SUM (case when to_char(tim_start_date, 'MON') = 'MAY' THEN TIM_HRS END) MAY,
SUM (case when to_char(tim_start_date, 'MON') = 'JUN' THEN TIM_HRS END) JUN,
SUM (case when to_char(tim_start_date, 'MON') = 'JUL' THEN TIM_HRS END) JUL,
SUM (case when to_char(tim_start_date, 'MON') = 'AUG' THEN TIM_HRS END) AUG,
SUM (case when to_char(tim_start_date, 'MON') = 'SEP' THEN TIM_HRS END) SEP,
SUM (case when to_char(tim_start_date, 'MON') = 'OCT' THEN TIM_HRS END) OCT,
SUM (case when to_char(tim_start_date, 'MON') = 'NOV' THEN TIM_HRS END) NOV,
SUM (case when to_char(tim_start_date, 'MON') = 'DEC' THEN TIM_HRS END) DEC
From PN_EMPLOYEES, PN_TIMESHEET_DETAILS
where tim_emp_id = EMP_ID and to_char(tim_start_date, 'YYYY') = 2007

union all
SELECT DISTINCT (case when emp_dept_code ='AH' then 'Client Services'
when emp_dept_code ='AFN' then 'Finance'
when emp_dept_code ='CRE' then 'Creative'
when emp_dept_code in ('PDT', 'SD','PDT') then 'Production/Studio/Traffic' end) deptno,
NULL leading_dept,
null ename, NULL ,
NULL,NULL, NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL , NULL
From PN_EMPLOYEES, PN_TIMESHEET_DETAILS
where tim_emp_id = EMP_ID and to_char(tim_start_date, 'YYYY') = 2007

ORDER BY deptno ASC, ename ASC
)

The sql runs correctly in APEX.
Please help to see what's wrong.

Sorry, my mistake. I was following the screen shot in the documentation which stated ":T_REPORT_NAME" when i should be using ":T_REPORT_ID"

When I export numeric data to Excel, whole numbers are shown correctly. But numbers with decimals are formatted as text. Is there any way to control this?

Some weird message requesting verify the extension, but it looks fine if you ignore the message.

Using IE7, Office 07, XP.

Working well in Oracle 10g XE + ApEx 3.01. (WinXP+Office 2003).

Just some details:
1. You can´t use select column AS "something" from table
2. If you use select a, b, c, d, e, f from table and set Report to show only the columns a, b and c, the exported file will have columns a, b, c, d, e and f. Why am I saying something so obvios? To remember those who set a column in Report as display as "Escape special characters...", that the exported excel file will not be "escaped". But that is simple to solve, just create a function to replace those characters by &lt ; , ...
3. Data fields are not displayed in correct format inside the excel. Simple to solve as well. Just ajust the format cell.

Thanks for this great help Denes! I´m a big fan of your work.

Felipe

Felipe,

Thanks for the comments. Only within the last couple of days, I received several emails on this topic.
Funny, but I also thought on expanding this package to cover the rest:

1. exporting reports created by a function returning SQL,

2. enabling hiding and showing report columns, similar to the .csv export,

3. including XML option into the package,

4. enabling character encoding for different languages and some other features.

Initialy, the package is a result of couple of hours of work. I didn't have time to think of
all details and programming best practices (if you look at the code, there are no private
and public functions or procedures). It was related to my needs - simple exporting of
"normal" reports. However, it has been downloaded almost 1000 times and I want to
make a new "release" now. Let us see how much time I can spend on it and what
will be the result. I am quite busy at the moment doing my daily stuff.

Once again, thanks for the recognition and greetings to Brazil from Germany.

Denes Kubicek

Hey Denes

This is excellent work :)

I was just wondering... how do you get the values for the report output from APEX?

(eg. it returns a result set - how do you access this from within a package?)

I understand how the excel document is generated - I'm just not sure where the data comes from :)

Thanks!
Sam

-EDIT-

Ah, ok I've figures some of it out :)

Now I know how you get the columns and the data... but I'm wondering why you need to do the following...

(print_report_values, lines 355 and 362)

HTMLDB_CUSTOM_AUTH.define_user_session ( ...
and
HTMLDB_APPLICATION.g_flow_id := ...

?

Cheers!

Hi Denes,

Many thanks for providing this feature, I am using this in my application and it’s working pretty well without any problem.

But I have a small problem, when user try to download data more than 65536 rows (Excel limit) then it will discard the rows, which crossed the upper limit. I am just thinking that is there is any possibility that the remaining rows will automatically move to the new worksheet?

Thanks once again for all your help, really such a great stuff.

Rajat

Hi Denes..
I installed your package on XE, APEX 3.1.
Everything is OK, except the columns heading in xls file.
In explanation, every second column has no heading, but has data (data are displayed correctly), so my table looks incorrectly because heading name dont match with data below it.
I checked all installation procedure and i think i did everything correctly.

I appreciate your feedback.

Kreso..

Hi Denes ...

I installed this package in APEX 3.1 on 10gR2. Everything is OK, even columns heading.
I have a question about then new interactive report feature in APEX 3.1. It would be very nice if i could use this package instead of Oracles CSV export.. (I don't have BI publisher). Do you have any plans for that?

Thank's

Göran Back

Just downloaded and installed package. Works great, but would like some assistance with one small annoyance (probably something that I have caused). Some of my columns in the Excel file are displayed as text fields rather than numbers (which is how they are stored in the DB table).

Any thoughts on helping me troubleshoot?

Thanks in advance,

David

David,

This excel package needs to be updated. However, I am too busy at the moment. Maybe
in April-May I will modify this and get rid of some nasty side-effects.

Denes Kubicek
-------------------------------------------------------------------
http://deneskubicek.blogspot.com/
http://www.opal-consulting.de/training
http://htmldb.oracle.com/pls/otn/f?p=31517:1
-------------------------------------------------------------------

Hi Denes,

I install excel package and when I download excel I have this error: Report Header Error: ORA-06502: PL/SQL: numerischer oder Wertefehler: character string buffer too small / -6502.
I need help to fixe this error !

Many thanks for your help.

Christ