This discussion is archived
1 6 7 8 9 10 Previous Next 146 Replies Latest reply: Dec 2, 2009 10:18 PM by user786836 Go to original post RSS
  • 135. Re: Export Excel instead of .csv
    Postie Newbie
    Currently Being Moderated
    Hi Denes

    I sure have it in my schema.. I even put the schema name in front .. and it still didn't find it?

    Dean
  • 136. Re: Export Excel instead of .csv
    CKLP Journeyer
    Currently Being Moderated
    Hi denes,

    i am getting follwing error while opening the excel file


    Report Values Error: ORA-06550: line 1, column 175: PL/SQL: ORA-00936: missing expression ORA-06550: line 1, column 167: PL/SQL: SQL Statement ignored / -6550 / select from (SELECT ou.first_name ,pp.PDID ,pp. PROJECT_NAME,PP.CREATION_DATE AS FROM pdat_project pp,pdat_deliverable pd,odf_user ou, pdat_review_requirement prr WHERE pp. STATUS ='Active' AND pp. PROJECTSEQ =pd. PROJECTSEQ(+) and pd.DELIVERABLE_NAME=prr.DELIVERABLE_NAME and prr.DELIVERABLE_NAME_DISPLAY='Test Pre-tollage Approval' AND pp. TEST_ANALYST = ou. USER_ID AND pd. REVIEW_NAME ='DSAT-3' and ou.user_id=(SELECT v('P801_SELECT_TEST_ARCH') FROM DUAL) AND (pd.APPROVAL_STATUS <>'Approved') ORDER BY TEST_ANALYST,CREATION_DATE


    Could you please tell me what could be the problem.

    Appreciate the help,
    Cheriyan
  • 137. Re: Export Excel instead of .csv
    627206 Explorer
    Currently Being Moderated
    Hi Denes,

    This Download to Excel is application is too good and fast !!!!!!!.

    I made some modifications and created new package so that now it works for Interactive Report too :

    export_excel_pkg.print_report_header follwoing potions are replaced with :

    SELECT region_source
    INTO v_region_sql
    FROM apex_application_page_regions
    WHERE region_id = TO_NUMBER (LTRIM (p_region, 'R'))
    AND page_id = p_page_id
    AND application_id = p_app_id;

    ===================
    SELECT region_source
    INTO v_region_sql
    FROM apex_application_page_regions
    WHERE
    region_name in (select region_name from apex_application_page_regions where source_type='Interactive Report')
    AND page_id = p_page_id
    AND application_id = p_app_id
    and source_type='Interactive Report';
    ====================
    and


    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

    with

    ==================
    FOR c IN (SELECT column_alias, NVL (column_alias, column_alias) heading,
    format_mask
    FROM apex_application_page_ir_col
    --apex_application_page_rpt_cols
    WHERE page_id = p_page_id
    AND application_id = p_app_id
    AND interactive_report_id = TO_NUMBER (LTRIM (p_region, 'R')))
    )
    LOOP
    =================

    But when I do a download to Excel, it gives me the etire reccords which my underlaying query for Interactive Report can retrurn.
    Filtering I have done on the interface is not taken into consideration.
    How can I modify the code such a way that if I do a filtering , so that download to Excel will give only records which are displayed on the screen


    Regards,
    Benz
  • 138. Re: Export Excel instead of .csv
    jariola Guru
    Currently Being Moderated
    Hi,

    I do not have answer to your question, sorry.

    But I like ask from you,
    does your code work when user save own report(s) and make changes to report layout?
    I mean changing what columns are displayed.

    Br, Jari
  • 139. Re: Export Excel instead of .csv
    627206 Explorer
    Currently Being Moderated
    Hi,

    I have almost cracked the issue myself, but few more tunings might be required. for regular filtering its working based on the user Logged in..

    I have modifeid the application process to accept user session : export_excel_demo_pkg.print_report (:t_region_id, :t_excel_name, :t_page, &SESSION.);

    Modified the Procs to accept the extra parameter

    PROCEDURE print_report (
    p_region IN VARCHAR2,
    p_file_name IN VARCHAR2 DEFAULT 'excel_report',
    p_page_id IN NUMBER DEFAULT v ('APP_PAGE_ID'),
    p_session   IN      NUMBER
    );

    PROCEDURE print_report_header (
    p_region IN VARCHAR2,
    p_page_id IN NUMBER,
    p_app_id IN NUMBER,
    p_session   IN      NUMBER,
    p_error OUT VARCHAR2
    );


    and modifed the code inside print_report_header as follows to have " v_region_sql " with filter clause

    =============================

    SELECT region_source
    INTO v_region_sql
    FROM apex_application_page_regions
    WHERE
    region_name in (select region_name from apex_application_page_regions where source_type='Interactive Report')
    AND page_id = p_page_id
    AND application_id = p_app_id
    and source_type='Interactive Report';

    FOR cur IN
    (
    SELECT
    COND.CONDITION_NAME,
    COND.CONDITION_COLUMN_NAME,
    COND.CONDITION_OPERATOR,
    COND.CONDITION_EXPRESSION,
    COND.CONDITION_EXPRESSION2,
    COND.CONDITION_SQL,
    replace(COND.CONDITION_SQL,'#APXWS_EXPR#',''''||COND.CONDITION_EXPRESSION||'''') res
    FROM
    APEX_APPLICATION_PAGE_IR_COND COND,
    APEX_APPLICATION_PAGE_IR_RPT RPT
    WHERE
    RPT.SESSION_ID= p_session
    AND
    RPT.REPORT_ID= COND.REPORT_ID
    AND
    COND.APPLICATION_ID=p_app_id
    AND
    COND.PAGE_ID=p_page_id

    ) LOOP


    v_Where_Cond := v_Where_Cond ||' AND '||CHR(10)||cur.res;



    END LOOP;

    v_region_sql := v_region_sql || v_Where_Cond;

    ==============================


    Its working for me..

    Regards,
    Benz

    Edited by: Benz on Nov 2, 2009 4:19 PM
  • 140. Re: Export Excel instead of .csv
    580618 Newbie
    Currently Being Moderated
    Hello,
    Denes I'm using your code and it works perfectly fine.
    But I would like to know if somebody else has faced the following error:

    The file you are trying to open, "filename.xls", is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?.

    I get this error on MS Office 2007 when I open an excel file created using your code Denes. Is there anything I can correct in the code in order to avoid getting this message?
    I also must add that if I use this file to upload data to the database using Apache POI, I get a Java exception that informs that the file is not an excel file.

    Please, let me know if there is something that I can do to fix this.
    Regards.
  • 141. Re: Export Excel instead of .csv
    TexasApexDeveloper Guru
    Currently Being Moderated
    This is a new "feature" added to Excel 2007.. Since the file you are trying to open is not a TRUE Excel file, but either a csv or a html table made to look like an Excel file..

    See this link for a possible solution: [http://www.itexperience.net/2008/03/17/excel-2007-error-different-format-than-specified-by-the-file-extension/]

    Thank you,

    Tony Miller
    Webster, TX
  • 142. Re: Export Excel instead of .csv
    user786836 Newbie
    Currently Being Moderated
    Hi Denis,

    SELECT column_alias, NVL (heading, column_alias) heading,
    format_mask,nvl(include_in_export,'Yes')
    FROM apex_application_page_rpt_cols
    WHERE page_id = :p_page_id
    AND application_id = :p_app_id
    AND region_name =:p_region
    AND nvl(include_in_export,'Yes') = 'Yes'
    -- and column_is_hidden = 'No'
    ORDER BY display_sequence

    This query is exicuting fine, however i am still getting error Report Values Error: ORA-06550: line 1, column 182: PL/SQL: ORA-00936: missing expression ORA-06550: line 1, column 174: PL/SQL: SQL Statement ignored / -6550 / select from (SELECT REF_T_NAME, UPLOAD_DATE, IMPORT_TYPE where REF_T_NAME = v('P1_FN'))
  • 143. Re: Export Excel instead of .csv
    627206 Explorer
    Currently Being Moderated
    Hi 786836,

    It seems like your base query has syntatical error.

    SELECT REF_T_NAME, UPLOAD_DATE, IMPORT_TYPE where REF_T_NAME = v('P1_FN')

    "From" clause is missing..

    Please check it out.

    Regards,
    Benz
  • 144. Re: Export Excel instead of .csv
    user786836 Newbie
    Currently Being Moderated
    I apologize, the error was

    ORA-06550: line 1, column 182: PL/SQL: ORA-00936: missing expression ORA-06550: line 1, column 174: PL/SQL: SQL Statement ignored / -6550 / select from (SELECT REF_T_NAME, UPLOAD_DATE, IMPORT_TYPE from cmu_import_template where REF_T_NAME = v('P1_FN'))
  • 145. Re: Export Excel instead of .csv
    627206 Explorer
    Currently Being Moderated
    Hi 786836 (please put ur name),

    select from (SELECT REF_T_NAME, UPLOAD_DATE, IMPORT_TYPE from cmu_import_template where REF_T_NAME = v('P1_FN'))

    The query still has a syntatical error "Select From(" -- here column values or * is missing..
    What is your current region query?


    Regards,
    Benz
  • 146. Re: Export Excel instead of .csv
    user786836 Newbie
    Currently Being Moderated
    Hi Benz

    Sorry i was on vaction

    Here is my region source query

    SELECT REF_T_NAME, UPLOAD_DATE, IMPORT_TYPE from cmu_import_template where REF_T_NAME = v('P1_FN')

    Yes you are correct columns are missing

    the query should have been

    select REF_T_NAME, UPLOAD_DATE, IMPORT_TYPE from (SELECT REF_T_NAME, UPLOAD_DATE, IMPORT_TYPE from cmu_import_template where REF_T_NAME = v('P1_FN'))

    unfartunately column names are missing could you please help me on this.

    Regards,
    Rajesh.
1 6 7 8 9 10 Previous Next