1 2 3 4 5 Previous Next 146 Replies Latest reply: Dec 3, 2009 12:18 AM by user786836 Go to original post RSS
      • 30. Re: Export Excel instead of .csv
        Denes Kubicek
        I will post this end of this month. I am currently bussy and have no time to do it.

        Denes Kubicek
        • 31. Re: Export Excel instead of .csv
          406133
          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
          • 32. Re: Export Excel instead of .csv
            Denes Kubicek
            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
            • 33. Re: Export Excel instead of .csv
              406133
              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
              • 34. Re: Export Excel instead of .csv
                Denes Kubicek
                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
                • 35. Re: Export Excel instead of .csv
                  Dimitri Gielis
                  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
                  • 36. Re: Export Excel instead of .csv
                    Denes Kubicek
                    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
                    • 37. Re: Export Excel instead of .csv
                      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
                      • 38. Re: Export Excel instead of .csv
                        538515
                        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.
                        • 39. Re: Export Excel instead of .csv
                          538515
                          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"
                          • 40. Re: Export Excel instead of .csv
                            538515
                            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?
                            • 41. Re: Export Excel instead of .csv
                              apex_disco
                              Some weird message requesting verify the extension, but it looks fine if you ignore the message.

                              Using IE7, Office 07, XP.
                              • 42. Re: Export Excel instead of .csv
                                572131
                                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
                                • 43. Re: Export Excel instead of .csv
                                  Denes Kubicek
                                  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
                                  • 44. Re: Export Excel instead of .csv
                                    569964
                                    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!
                                    1 2 3 4 5 Previous Next