This discussion is archived
1 2 3 4 5 10 Previous Next 146 Replies Latest reply: Dec 2, 2009 10:18 PM by user786836 Go to original post RSS
  • 30. Re: Export Excel instead of .csv
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 10 Previous Next