Introduction

I have lost count of how many times I have developed an Excel Upload using APEX_DATA_PARSER. It provides users with a convenient way to mass-upload or update data using a tool they are familiar with. I usually include a link to a static Excel Template File that users can download to understand the structure they need to include in their upload.

Use Case

In a recent project, I was tasked with building a Hierarchy Management Application. This involved loading nodes (and their attributes), then loading hierarchies composed of those nodes. I’ll be focusing on the node (and attribute) upload.

The Nodes APEX UI looks like this:

• A Segment Type is a grouping of like nodes.

• The Bonus Eligible column is a custom node attribute. Users can specify up to 20 custom attributes for each Segment Type.

👉 Enter apex_data_export.

Goal

The goal is to download an Excel file containing the existing rows and columns for a Segment Type, make updates, and re-import it to apply those updates.

Approach

Build SQL

My approach was first to build a function to generate dynamic SQL for both the APEX page shown above and for apex_data_export to use. The function below is intended to show an approximation of the function (it is not the actual code).

FUNCTION get_node_sql 
  (p_segment_type_id IN apx_rdm_segment_type.segment_type_id%TYPE) RETURN CLOB IS

  CURSOR cr_segment_attributes 
   (cp_segment_type_id IN apx_rdm_segment_type.segment_type_id%TYPE) IS
    SELECT attr.attribute_code
    ,      attr.data_type_code
    ,      attr.attribute_name AS display_value
    FROM   apx_rdm_segment_type_attr sta
    ,      apx_rdm_attribute         attr
    WHERE  attr.attribute_id   = sta.attribute_id
    AND    sta.segment_type_id = cp_segment_type_id
    ORDER  BY sta.sort_order;

  (p_segment_type_id IN apx_rdm_segment_type.segment_type_id%TYPE) RETURN CLOB IS
  l_sql               CLOB;
  l_col_count         PLS_INTEGER := 0;
  lc_lf               CONSTANT VARCHAR2(1) := CHR(10);
BEGIN

  -- Build the base columns for the hierarchy node.
  l_sql := 'SELECT hnode.node_code, hnode.node_name, hnode.system_name, hnode.postable_flag, hnode.active_flag' || lc_lf;

  -- Loop through custom attributes and Append a MAX(...) expression per 
  --   attribute assigned to the segment type.
  FOR rec IN cr_segment_attributes (cp_segment_type_id => p_segment_type_id) LOOP
    l_col_count := l_col_count + 1;
    EXIT WHEN l_col_count > 20;
      l_sql := l_sql
        || ',      MAX(CASE WHEN attrval.attribute_code = '''
        || REPLACE(rec.attribute_code, '''', '''''')
        || ''' THEN attrval.attr_value_varchar END) AS "'
        || REPLACE(rec.attribute_code, '"', '""')
        || '"' || lc_lf;
  END LOOP;

  -- Add FROM and WHERE clauses.
  l_sql := l_sql ||
  'FROM   apx_rdm_hierarchy_node_svw hnode
  LEFT JOIN apx_rdm_node_attribute_value_vw attrval 
    ON     attrval.node_id = hnode.node_id
    WHERE hnode.segment_type_id = :SEGMENT_TYPE_ID' || lc_lf;

  -- Add Group By Clause.
  -- Attribute values are aggregated with MAX so they do not belong in the GROUP BY.
  l_sql := l_sql || 'GROUP BY hnode.node_code, hnode.node_name, hnode.system_name, hnode.postable_flag, hnode.active_flag';

  RETURN l_sql;

END get_node_sql;

Fetch SQL, Format Output, Generate & Store Excel

Next, write a procedure to generate the Excel Upload Template File. Again, the code below is an extract of the highlights from the actual code and is not complete.

PROCEDURE generate_node_upload_template
 (p_segment_type_id   IN apx_rdm_segment_type.segment_type_id%TYPE,
  p_segment_type_code IN apx_rdm_segment_type.segment_type_code%TYPE) IS

  -- apex_exec and apex_data_export types.
  l_xlsx_context        apex_exec.t_context;
  l_sql_params          apex_exec.t_parameters;
  l_xlsx_export         apex_data_export.t_export;
  lt_columns            apex_data_export.t_columns;
  l_print_config        apex_data_export.t_print_config;
  l_sql                 CLOB;

BEGIN

  -- Generate the SQL to retrieve the nodes and their attributes.
  l_sql := get_node_sql (p_segment_type_id => p_segment_type_id);

  -- Add Static Colummn Definitions and Headings.
  -- Note the use of p_is_frozen to the first three columns. This will freeze 
  --  these three columns in the Excel output. 
  -- The frozen columns must be the first contiguous columns.
  -- p_heading is the value used in the output Excel.
  apex_data_export.add_column
   (p_columns => lt_columns,
    p_name    => 'NODE_CODE',
    p_heading => 'Node Code',
    p_is_frozen => TRUE);
  apex_data_export.add_column
   (p_columns => lt_columns,
    p_name    => 'NODE_NAME',
    p_heading => 'Node Name',
    p_is_frozen => TRUE);
  apex_data_export.add_column
   (p_columns => lt_columns,
    p_name    => 'SYSTEM_NAME',
    p_heading => 'Source System Name',
    p_is_frozen => TRUE);
  apex_data_export.add_column
   (p_columns => lt_columns,
    p_name    => 'POSTABLE_FLAG',
    p_heading => 'Postable Flag');
  apex_data_export.add_column
   (p_columns => lt_columns,
    p_name    => 'ACTIVE_FLAG',
    p_heading => 'Active Flag');

  -- Loop through attributes for the Segment Type adding dynamic attribute columns.
  FOR lr_segment_attrs IN cr_segment_attributes (cp_segment_type_id => p_segment_type_id) LOOP
    apex_data_export.add_column
     (p_columns => lt_columns,
      p_name    => lr_segment_attrs.attribute_code,
      p_heading => lr_segment_attrs.display_value);
  END LOOP;

  -- Add SQL Parameters / Bind Variable Values
  apex_exec.add_parameter(l_sql_params, 'SEGMENT_TYPE_ID', p_segment_type_id);

  -- Add Branding Colors to the Heading and make Heading Font Size Larger.
  l_print_config := apex_data_export.get_print_config
                     (p_header_bg_color   => '#020381',
                      p_header_font_color => '#FFFFFF',
                      p_header_font_size  => 11);

  -- Open the Query Context, bind the parameters and execute the query.
  l_xlsx_context := apex_exec.open_query_context
                     (p_location       => apex_exec.c_location_local_db,
                      p_sql_parameters => l_sql_params,
                      p_sql_query      => l_sql);

  -- Export to XLSX format
  l_xlsx_export := apex_data_export.export
                     (p_context           => l_xlsx_context,
                      -- ⬇️ Determines the Export File Format
                      p_format            => apex_data_export.c_format_xlsx,
                      -- ⬇️ Defines the Columns and Headings in the Export
                      p_columns           => lt_columns,
                      -- ⬇️ Used for the Excel Tab Name
                      p_page_header       => p_segment_type_code,  
                      -- ⬇️ Text Appears at the top of the Excel Sheet
                      p_supplemental_text => 'This template is to be used for uploading Nodes into the [' || 
                                              p_segment_type_code || '] Segment Type. ',  
                      -- ⬇️ Name of the Exported File
                      p_file_name         => 'Node_Upload_Template',
                      -- ⬇️ Print Configuration to use for the Exported File
                      p_print_config      => l_print_config);
  apex_exec.close(l_xlsx_context);

  -- Store the Exported File in an APEX Collection for Later Use.
  apex_collection.create_or_truncate_collection(p_collection_name => 'NODES_TEMPLATE_COLLN');
  apex_collection.add_member
   (p_collection_name => 'NODES_TEMPLATE_COLLN',
    p_blob001         => l_xlsx_export.content_blob,
    p_c001            => l_xlsx_export.file_name);
END generate_node_upload_template;

• You don’t have to use apex_data_export.add_column. If you do not, then APEX will use the column names (or aliases) from your SQL for the column headers.

• The p_is_frozen parameter apex_data_export.add_column has to be applied to all of the columns you want frozen.

• For my use case, I call the generate procedure above from a Dynamic Action, temporarily store the Excel file in a Collection, and then download it using the Download Dynamic Action (check out this post from Louis Moreaux for more).

More on apex_data_export

In this post, I focused on a specific use case and some specific features of apex_data_export. In this section, I will cover additional key features and user cases.

Other Key Features

Column Grouping

The apex_data_export.add_column_group API allows you to organize columns into groups with group headings to generate something like this:

Aggregating

The apex_data_export.add_aggregate API allows you to calculate totals for numeric values. This can be used in conjunction with the p_is_column_break parameter of apex_data_export.add_column to generate group totals.

Highlights

The apex_data_export.add_highlight API allows you to highlight cells in your output based on columns in your data source. The way this works is a little different.

In your SQL, return the highlight ID based on the criteria you want to use. In the example below, I have two highlights with IDs 1 and 2.

SELECT order_number
,      order_total
,      due_date
,      CASE WHEN order_total > 1000 THEN 1 END AS order_total_highlight
,      CASE WHEN due_date > SYSDATE THEN 2 END AS due_date_highlight
FROM   orders

-- Define Highlight for Order Total
apex_data_export.add_highlight(
        p_highlights          => l_highlights,
        p_id                  => 1,         -- Order Total Highlight from SQL
        p_value_column        => 'ORDER_TOTAL_HIGHLIGHT',
        p_display_column      => 'ORDER_TOTAL',  -- Where to put the highlight
        p_text_color          => '#FF0000' );

-- Define Highlight for Due Date
apex_data_export.add_highlight(
        p_highlights          => l_highlights,
        p_id                  => 2,         -- Due Date Highlight from SQL
        p_value_column        => 'DUE_DATE_HIGHLIGHT',
        p_display_column      => 'DUE_DATE',  -- Where to put the highlight
        p_text_color          => '#FF0000' );

• If the SQL returns a 1 for the order_total_highlight column, then the highlight is applied; otherwise, it is not. The same goes for due_date_highlight, except it needs to return a 2 for the highlight to be applied.

Download

The apex_data_export.download API allows you to initiate a download of the file generated by apex_data_export (or any other BLOB for that matter). Check out this post from Haniel Burton for more.

Print Config

The apex_data_export.get_print_config API, when used in conjunction with the p_print_config parameter of apex_data_export.export, is used to drive print formatting for your export. Here are the settings applicable to Excel exports:

• Page Header (p_page_header) is used for the tab name, unless overridden in the parameter with the same name in the call to apex_data_export.export.

• Page footer-related parameters (p_page_footer_…): add and format text below the last cell in the Excel output.

• Heading-related parameters (p_header_…): apply formatting to the Excel column headings.

• Body-related parameters (p_body_…): apply formatting to the rows in the Excel output.

• Border-related parameters (p_border_…): apply formatting to the cell borders in the Excel output.

Other Use Cases for APEX_DATA_EXPORT

In this post, I focused on a specific use case, but there are many others for apex_data_export.

• Exporting Setup Data in JSON for import into other systems or moving setups from DEV > TEST > PROD.

• Generate downloads of data triggered from a button press, where you don’t have an Interactive Report or Grid to handle the download.

• Generate a report from an APEX Automation and attach it to an email for distribution.

• Generate specialized “operational snapshot” files for downstream teams or auditors: Use the API to produce point-in-time exports (with highlights, aggregates, or formatting) from PL/SQL jobs, ideal for monthly financial freezes, audit cycles, or HR snapshots.

APEX_EXEC

Part of the flexibility of apex_data_export stems from its use of the powerful apex_exec API. This allows you to generate exports from REST APIs just as easily as from database tables.

Conclusion

Dynamic Excel template generation is one of those APEX features that solves a real problem: keeping users aligned with data structures that aren’t static. By pairing apex_data_export with apex_exec, you eliminate the maintenance headache of versioning static spreadsheets and give users templates that consistently reflect the current configuration; columns, attributes, sample values, instructions, everything.