Today there was a question on how to export data from the Oracle Database and Oracle APEX into one Excel file with multiple sheets. Each sheet contains data from a different table.
With APEX Office Print it's a matter of a few minutes to fulfill this request 😀
If you have never heard of APEX Office Print (AOP), here's one paragraph on it. AOP is the easiest to use product/plug-in that fully integrates with Oracle APEX to give you all the reporting and export functionality you've dreamed of. You create templates in Word, Excel, PowerPoint, HTML, or Text and AOP will merge it with the data coming from your database, APEX components, or REST Web Service and return a document to you in the output format of your choice (PDF, Word, Excel, PowerPoint, HTML, Text). If you want to get started with AOP, here's a quick Getting Started with AOP video.
Let's get back to this example where we want to generate a single Excel file, with multiple sheets and each sheet contains data from a different table.
Step 1: Prepare your SQL statement.
In this example, we want to export the data coming from our customers, products, and orders table.
AOP understands the CURSOR Expressions of SQL. Each cursor will contain the columns of the different tables. You can have as many cursors as you want, look at them as blocks of data.
select
'file1' as "filename",
cursor(
select
cursor(
select
cust_first_name,
cust_last_name,
cust_city
from aop_sample_customers) as "customers"
, cursor(
select
order_id,
order_total
from aop_sample_orders) as "orders"
, cursor(
select
product_name,
product_description,
category,
product_avail,
list_price
from aop_sample_product_info) "product"
from dual) as "data"
from dual
Note that with the cursor expression you can also create more nested data (as seen in the AOP Sample App on page 30). For example, if you want to see all orders and order lines of a specific customer, your SQL statement would look like this:
select
'file1' as "filename",
cursor(
select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city",
cursor(select o.order_total as "order_total",
'Order ' || rownum as "order_name",
cursor(select p.product_name as "product_name",
i.quantity as "quantity",
i.unit_price as "unit_price"
from aop_sample_order_items i,
aop_sample_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from aop_sample_orders o
where c.customer_id = o.customer_id
) "orders"
from aop_sample_customers c
where customer_id = :CUSTOMER_ID
) as "data"
from dual
So you can add the CURSOR() expressions on the same level as well as in a hierarchy.
Now that we understand this, let's get back to our example, where we wanted to export the data from the SQL statement to different sheets in Excel.
Step 2: Create your Excel template
There are a few ways to create your own template:
- You can start from a new empty Excel document,
- or you can start from a template of your company (e.g. with the logo and some house branding),
- or you can download a template you like from Microsoft's Excel template repository,
- or you can let AOP generate a starter template for you.
We will use the last option. Nothing easier than letting AOP create a template for us!
AOP has a PL/SQL API or an Oracle APEX plug-in you can use. In this example, we will use the plug-in. I won't go over how to install the Oracle APEX plug-in, you can find that in the getting started video.
So, on our Oracle APEX page, we create a button and a dynamic action calling the APEX Office Print plug-in. We specify that we want an AOP Template, we add the SQL statement as the data source, and select an Excel file as the output type. Here's what it looks like in Oracle APEX:
When clicking the button, this is the Excel file AOP generated:
The AOP template will add the tags (see lines 4, 7, and 10 between {}) for the data you specified in the SQL file, and give some more explanations below to explain what is possible with AOP. For example, AOP can also generate sheets or use formulas dynamically.
To finish our template, the only thing we need to do is to copy lines 7 and 10 and put them on their own sheets. Finally, we customize the template exactly as we want. For example, we can give it a header, make some tags bold and make it really pretty with our company logo.
Once we are happy with our template, let's upload it to Static Application Files in our app. Note: you can store the template in your own table, the filesystem, online, anywhere that AOP can access.
Step 3: Specify the template and Done!
Finally, specify the new template in the AOP Dynamic Action. The Template Type is Static Application Files and the Template Source is the filename.
And presto, you are done! Click your Excel button again and here's the output:
So any time you need to create a report in Excel with data coming from Oracle APEX or the database, you know you can do it with APEX Office Print (AOP)! 😁
