Oracle APEX Tutorial: Excel Downloads with Multiple Sheets

When I started working on these requirements, I encountered some challenges. However, through my own research and development, I was able to successfully complete this. If you have any questions, please feel free to ask them here. I'm more than happy to provide further details or clarifications.


Check out on LinkedIn https://in.linkedin.com/in/venkatraman-s-63385719b

Step 1 : 

Create Package with a procedure,

Sample Template : 

create or replace PROCEDURE generate_excel_file (

    p_excel_blob OUT BLOB

)

IS

    l_excel_data CLOB; -- Replace with actual XML content for the Excel file

BEGIN

    -- Generate Excel XML content (replace with actual XML creation logic)

    l_excel_data := '<?xml version="1.0"?>

        <?mso-application progid="Excel.Sheet"?>

        <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

            xmlns:o="urn:schemas-microsoft-com:office:office"

            xmlns:x="urn:schemas-microsoft-com:office:excel"

            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

            xmlns:html="http://www.w3.org/TR/REC-html40">

            <Styles>

                <Style ss:ID="s1">

                    <Font ss:Bold="1"/>

                </Style>

            </Styles>

            <Worksheet ss:Name="Sheet1">

                <Table>

                    <Row>

                        <Cell><Data ss:Type="String">Sheet 1 - Row 1, Cell 1</Data></Cell>

                        <Cell><Data ss:Type="String">Sheet 1 - Row 1, Cell 2</Data></Cell>

                    </Row>

                    <Row>

                        <Cell><Data ss:Type="String">Sheet 1 - Row 2, Cell 1</Data></Cell>

                        <Cell><Data ss:Type="String">Sheet 1 - Row 2, Cell 2</Data></Cell>

                    </Row>

                </Table>

            </Worksheet>

            <Worksheet ss:Name="Sheet2">

                <Table>

                    <Row>

                        <Cell><Data ss:Type="String">Sheet 2 - Row 1, Cell 1</Data></Cell>

                        <Cell><Data ss:Type="String">Sheet 2 - Row 1, Cell 2</Data></Cell>

                    </Row>

                    <Row>

                        <Cell><Data ss:Type="String">Sheet 2 - Row 2, Cell 1</Data></Cell>

                        <Cell><Data ss:Type="String">Sheet 2 - Row 2, Cell 2</Data></Cell>

                    </Row>

                </Table>

            </Worksheet>

        </Workbook>';


    -- Convert CLOB to BLOB

    p_excel_blob := utl_raw.cast_to_raw(l_excel_data);

END generate_excel_file;

/



Modify procedure like below,Modify procedure like below,

create or replace PACKAGE QUERY_TO_EXCEL_CODE

AS

PROCEDURE CREATE_EXCEL_WB(

    l_file_id OUT NUMBER

);

END QUERY_TO_EXCEL_CODE;

/



create or replace PACKAGE BODY QUERY_TO_EXCEL_CODE

AS

PROCEDURE CREATE_EXCEL_WB(

    l_file_id OUT NUMBER

)

IS


    l_excel_data CLOB;


    -- Column name cursor

    CURSOR SUPP_COL_CURSOR IS 

    SELECT SUPP_COLUMN_NAME FROM (

    SELECT 'SUPPLIER_ID' SUPP_COLUMN_NAME, 1 SUPP_ID FROM DUAL

    UNION SELECT 'SUPPLIER_NUMBER' SUPP_COLUMN_NAME, 2 SUPP_ID FROM DUAL

    UNION SELECT 'SUPPLIER_TYPE' SUPP_COLUMN_NAME, 3 SUPP_ID FROM DUAL

    UNION SELECT 'ORGANIZATION_NAME' SUPP_COLUMN_NAME, 4 SUPP_ID FROM DUAL

    UNION SELECT 'COUNTRY_OF_ORIGIN' SUPP_COLUMN_NAME, 5 SUPP_ID FROM DUAL

    UNION SELECT 'TAX_REGISTRATION_NUM' SUPP_COLUMN_NAME, 6 SUPP_ID FROM DUAL

    ) ORDER BY SUPP_ID ASC;


    CURSOR SUPP_ADD_COL_CURSOR IS

    SELECT SUPP_ADDR_COL_NAME FROM (

    SELECT 'SUPP_ADDRESS_ID' SUPP_ADDR_COL_NAME, 1 ADDRESS_ID FROM DUAL

    UNION SELECT 'SUPPLIER_ID'  SUPP_ADDR_COL_NAME, 2 ADDRESS_ID FROM DUAL

    UNION SELECT 'COUNTRY'  SUPP_ADDR_COL_NAME, 3 ADDRESS_ID FROM DUAL

    UNION SELECT 'ADDRESS_LINE1'  SUPP_ADDR_COL_NAME, 4 ADDRESS_ID FROM DUAL

    UNION SELECT 'ADDRESS_LINE2'  SUPP_ADDR_COL_NAME, 5 ADDRESS_ID FROM DUAL

    UNION SELECT 'ADDRESS_LINE3'  SUPP_ADDR_COL_NAME, 6 ADDRESS_ID FROM DUAL

    UNION SELECT 'ADDRESS_LINE4'  SUPP_ADDR_COL_NAME, 7 ADDRESS_ID FROM DUAL

    UNION SELECT 'CITY'  SUPP_ADDR_COL_NAME, 8 ADDRESS_ID FROM DUAL

    UNION SELECT 'STATE'  SUPP_ADDR_COL_NAME, 9 ADDRESS_ID FROM DUAL

    UNION SELECT 'POSTAL_CODE'  SUPP_ADDR_COL_NAME, 10 ADDRESS_ID FROM DUAL

    UNION SELECT 'PHONE_NUMBER'  SUPP_ADDR_COL_NAME, 11 ADDRESS_ID FROM DUAL

    UNION SELECT 'EMAIL_ADDRESS'  SUPP_ADDR_COL_NAME, 12 ADDRESS_ID FROM DUAL

    UNION SELECT 'ADDRESS_PURPOSE'  SUPP_ADDR_COL_NAME, 13 ADDRESS_ID FROM DUAL

    UNION SELECT 'ADDRESS_NAME'  SUPP_ADDR_COL_NAME, 14 ADDRESS_ID FROM DUAL

    ) ORDER BY ADDRESS_ID ASC;


    -- Data cursor

    CURSOR SUPP_DATA IS 

    SELECT SUPPLIER_ID,SUPPLIER_NUMBER,SUPPLIER_TYPE,ORGANIZATION_NAME,COUNTRY_OF_ORIGIN,TAX_REGISTRATION_NUM 

    FROM XX_SUPPLIER;


    CURSOR SUPP_ADDRESS_DATA IS

    SELECT SUPP_ADDRESS_ID,SUPPLIER_ID,COUNTRY,ADDRESS_LINE1,ADDRESS_LINE2,

    ADDRESS_LINE3,ADDRESS_LINE4,CITY,STATE,POSTAL_CODE,PHONE_NUMBER,

    EMAIL_ADDRESS,ADDRESS_PURPOSE,ADDRESS_NAME

    FROM XX_SUPPLIER_ADDRESS;


BEGIN

    -- To Generate Excel XML content

    l_excel_data := '<?xml version="1.0"?>

        <?mso-application progid="Excel.Sheet"?>

        <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

            xmlns:o="urn:schemas-microsoft-com:office:office"

            xmlns:x="urn:schemas-microsoft-com:office:excel"

            xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

            xmlns:html="http://www.w3.org/TR/REC-html40">

            <Styles>

                <Style ss:ID="s1">

                    <Font ss:Bold="1"/>

                </Style>

            </Styles>

            <Worksheet ss:Name="SUPPLIER">

                <Table>

                    <Row>';

        

        -- Table Column Headings

        BEGIN

        FOR SUPPLIER_COLUMNS IN SUPP_COL_CURSOR

        LOOP

            l_excel_data := l_excel_data ||'<Cell><Data ss:Type="String">'||SUPPLIER_COLUMNS.SUPP_COLUMN_NAME||'</Data></Cell>';

        END LOOP;

        END;        

       

        l_excel_data := l_excel_data ||'</Row>';


        -- Table Data

        BEGIN

        FOR SUPPLIER_ROW IN SUPP_DATA

        LOOP

            l_excel_data := l_excel_data||'<Row>

                        <Cell><Data ss:Type="String">'||SUPPLIER_ROW.SUPPLIER_ID||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPPLIER_ROW.SUPPLIER_NUMBER||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPPLIER_ROW.SUPPLIER_TYPE||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPPLIER_ROW.ORGANIZATION_NAME||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPPLIER_ROW.COUNTRY_OF_ORIGIN||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPPLIER_ROW.TAX_REGISTRATION_NUM||'</Data></Cell>

                    </Row>';

        END LOOP;

        END;          

        

        --Close Table and Worksheet 1

        l_excel_data := l_excel_data||'</Table></Worksheet>'; 


        -- Create worksheet 2

        l_excel_data := l_excel_data||'<Worksheet ss:Name="SUPPLIER_ADDRESS">

                <Table>

                    <Row>';

        

         -- Table Column Headings

        BEGIN

        FOR SUPP_ADDR_COLUMNS IN SUPP_ADD_COL_CURSOR

        LOOP

            l_excel_data := l_excel_data ||'<Cell><Data ss:Type="String">'||SUPP_ADDR_COLUMNS.SUPP_ADDR_COL_NAME||'</Data></Cell>';

        END LOOP;

        END;        

       

        l_excel_data := l_excel_data ||'</Row>';


        -- Table Data

        BEGIN

        FOR SUPP_ADDR_ROW IN SUPP_ADDRESS_DATA

        LOOP

            l_excel_data := l_excel_data||'<Row>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.SUPP_ADDRESS_ID||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.SUPPLIER_ID||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.COUNTRY||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.ADDRESS_LINE1||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.ADDRESS_LINE2||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.ADDRESS_LINE3||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.ADDRESS_LINE4||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.CITY||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.STATE||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.POSTAL_CODE||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.PHONE_NUMBER||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.EMAIL_ADDRESS||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.ADDRESS_PURPOSE||'</Data></Cell>

                        <Cell><Data ss:Type="String">'||SUPP_ADDR_ROW.ADDRESS_NAME||'</Data></Cell>

                    </Row>';

        END LOOP;

        END;          

        

        -- Close Table and Worksheet 2

        l_excel_data := l_excel_data||'</Table></Worksheet>'; 


        -- Close Workbook

        l_excel_data := l_excel_data||'</Workbook>';


    BEGIN

        -- TO GENERATE FILE ID SEQUENCE

        SELECT to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') INTO l_file_id FROM DUAL;


        -- TO DELETE EXISTING FROM THE TEMP TABLE

        DELETE FROM XX_QUERY_TO_EXCEL;

    END;


    BEGIN

        -- TO STORE FILE CONTENT & FILE DETAILS IN TEMP TABLE

 INSERT INTO XX_QUERY_TO_EXCEL VALUES(l_file_id,'SUPPLIER_DTLS.xlsx',l_excel_data,'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

    END;

END CREATE_EXCEL_WB;

END QUERY_TO_EXCEL_CODE;

/





Temp Table DDL (XX_QUERY_TO_EXCEL)


 CREATE TABLE "XX_QUERY_TO_EXCEL" 

   ( "FILE_ID" NUMBER, 

"FILE_NAME" VARCHAR2(100), 

"FILE_CONTENT" CLOB, 

"FILE_TYPE" VARCHAR2(200), 

CONSTRAINT "XX_QUERY_TO_EXCEL_CON" UNIQUE ("FILE_ID")

  USING INDEX  ENABLE

   ) ;



  • The above code will generate an Excel file containing multiple sheets using xml code and SQL Code and stored as clob data, 

  • then the clob data, file name, file id and file type are stored into a temp Table.


Step 2 : 

Create a Button,

Create a page item ‘File_id’,

Create a dynamic action to that button,



Create true action - 1,

Select action - Execute Server side code,

Pl/Sql Block - 

Write the below code to call and execute procedure,

          DECLARE

-- declare a variable to store file id returns from the procedure

p_file_id NUMBER;

BEGIN


-- call procedure with out parameter

QUERY_TO_EXCEL_CODE.CREATE_EXCEL_WB(p_file_id);


-- To store returned file id into page item

SELECT TO_CHAR(p_file_id) INTO :P4_FILE_ID FROM DUAL;

END;



Create true action - 2,

Select action - download (download action to download file using filename,filetype and   filecontent(clob or blob). it is available only in 24th version),

Sql Query Block - 

Write the below code to fetch the file content,


SELECT file_content,

       file_name,

       file_type

FROM XX_QUERY_TO_EXCEL

WHERE file_id = TO_NUMBER(:P4_FILE_ID)


Demo: https://apex.oracle.com/pls/apex/r/venkat_vs22/excel-download-multiple-sheet/home?

https:


T

Location: Chennai, Tamil Nadu, India

Comments

Post a Comment

Popular posts from this blog

Guide to APEX Working Copies & Merging

Image
  1. Introduction: Why Working Copies? Start with the "Why." Before this feature, developers often exported/imported applications with different IDs or—worse—edited the same page at the same time. The Problem: Merge conflicts, overwritten code, and "Development Freezes." The Solution: Working Copies allow for isolated development environments within the same Application ID.         In Oracle APEX, this is handled through two actions: Merge into Main Refresh Working Copy           How to Merge     Steps: Open your Working Copy. Click on the Working Copy menu. Select Merge into Main. Review the list of changed components. Confirm merge.      You will see:   Changed components   Newly added components   Deleted components   This is where you must be careful.    Selecting Impacted or Changed Components    The merge screen does not force you to merge everything.   ...
Read more