1 6 7 8 9 10 Previous Next 146 Replies Latest reply: Dec 3, 2009 12:18 AM by user786836 Go to original post RSS
      • 135. Re: Export Excel instead of .csv
        Postie
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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