Generating Multi Sheet Excel Files in Oracle APEX

Oracle APEX provides fantastic built-in capabilities for exporting data to Excel via Interactive Reports and Grids. However, we often hit a wall when the requirement becomes more complex: "Can we export multiple reports into different sheets within a single Excel file?"

While third-party plugins or tools like APEX Office Print (AOP) are excellent solutions, sometimes you want a lightweight, "zero-dependency" approach using pure PL/SQL. In this post, I will break down a robust procedure that generates a multi-sheet .xlsx file from scratch by leveraging the APEX_ZIP package and the OpenXML standard.

The Strategy: Understanding the XLSX Structure

An .xlsx file is essentially a zip collection of XML files. To create one manually, we need to generate:

• Worksheet XMLs: The actual data for Sheet 1, Sheet 2, etc.

• Shared Strings: A dictionary of unique strings to reduce file size.

• Metadata Files: workbook.xml, styles.xml, and relationship files (.rels) that tell Excel how the components fit together.

Now, let's look at the procedure generate_multi_sheet_xlsx.

Core Components of the Solution

Let’s look at the logic inside our generate_multi_sheet_xlsx procedure.

1. Handling Large Data with LOBs

When generating XML for thousands of rows, string concatenation (||) will eventually hit the 32KB limit or cause performance issues. We use DBMS_LOB.APPEND for efficiency.

2. The Shared Strings Optimization

Excel doesn't store the word "Apple" in every cell. It stores it once in sharedStrings.xml and references its index. The function get_shared_string_index manages this mapping using a PL/SQL associative array.

3. Column Widths & Formatting

Our procedure allows for dynamic column widths, which is crucial for readability. We define these using the SYS.ODCINUMBERLIST type and inject them into the <cols> section of the worksheet XML.

Conclusion

Generating multi sheet Excel files via PL/SQL gives you absolute control over the output without requiring external services. While the initial setup of the XML structure (the "boilerplate") is tedious, once you have this template, adding a third or fourth sheet is as simple as opening a new cursor and adding one more line to APEX_ZIP.