1 2 3 4 5 Previous Next 146 Replies Latest reply on Dec 3, 2009 6:18 AM by user786836 Go to original post
      • 25. Re: Export Excel instead of .csv
        Denes Kubicek
        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
        • 26. Re: Export Excel instead of .csv
          567700
          ok tanks, i'll send you a feedback as soon as possible.
          • 27. Re: Export Excel instead of .csv
            567700
            Denes, it works !! Thanks for your support.
            Bye
            • 28. Re: Export Excel instead of .csv
              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 Kubicek
              • 29. Re: Export Excel instead of .csv
                49453
                "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 :-)
                • 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"
                                    1 2 3 4 5 Previous Next