Export Multiple Reports into a Single Excel Workbook Using AOP in Oracle APEX
When working with Oracle APEX reports, sometimes we need to export data from multiple reports into a single Excel file instead of downloading each report separately.
In this article, we’ll see how to use APEX Office Print (AOP) with a Dynamic Action to download Orders and Order Items into a single Excel workbook, with each dataset in its own worksheet.
1. Configure AOP
First, import the AOP Dynamic Action plug-in into your Oracle APEX application.
Make sure the AOP database package is installed and accessible to your application’s parsing schema. If you’re using AOP Cloud, configure the API key in the AOP Component Settings.
Once AOP is configured, we can prepare the Excel template.
2. Prepare the Excel template
Create an Excel workbook with two worksheets:
OrdersOrder Items
Add the required column headings to each worksheet.
Orders worksheet
Order Items worksheet
Understanding the AOP syntax
AOP uses tags to identify the data that should appear in the Excel template.
{#orders}starts the loop for the Orders dataset.{order_id},{full_name}, and the other field tags identify the columns to populate.{/orders}closes the loop.{#order_items}and{/order_items}do the same for the Order Items dataset.
The loop names must match the cursor names returned by the SQL query, and the field tags must match the column aliases.
Get Kamalaselvi Parameswaran’s stories in your inbox
Join Medium for free to get updates from this writer.
Save the workbook as customer_orders_template.xlsx and upload it to Shared Components → Static Application Files.
3. Use the template in the AOP Dynamic Action
Create a Dynamic Action on your Export Excel button and select the AOP Dynamic Action plug-in.
Configure the action to use the uploaded Excel template and the SQL data source that returns both datasets.
The SQL query uses nested cursors to provide the data for both worksheets:
Here, orders provides the order details, while order_items provides the product-level details. AOP matches these cursor names with the corresponding loops in the template.
The table and column names should match your actual database schema.
4. Download both reports in one workbook
Save your changes and run the APEX application. Click the Export Excel button.
AOP generates a single Excel workbook containing:
- Orders — order details, customer names, store names, and status.
- Order Items — product details, quantities, prices, and line totals.
Instead of downloading two separate files, users can access both datasets from the same workbook.
Conclusion
By combining multiple named cursors with a multi-worksheet Excel template, we can use AOP to export multiple report datasets into a single Excel workbook from Oracle APEX.
This approach can also be reused for other reporting requirements, such as invoices and invoice lines, purchase orders and items, or employee details and transactions.
References
#OracleAPEX #APEXOfficePrint #AOP
