Is there a Option to dynamically change the worksheet Name of an Excel file? (via APEX, SQL or APEX Office Print)

Question

Apex Office Print uses {#return}{DATA}{/return} as format to call Data from an SQL into the Excel. So you would use this formate to dictate where your data should go. Problem is you're not allowed to use "/" in the sheets name. So I've tried VBA Macro, but did not get very far, as the macro would have to refer to a cell also using this format. Apart from that the Macro would have to work upon opening the file. Ive also read through the AOP site to search for a function like aop_api_pkg.g_output_filename, but had no luck so far. If someone already had the same problem or an idea how to solve this, id appreciate it.

Answer 1

Edit: Since 21.2.1 and onwards, a tag can be specified in the sheet name of the template itself.

Older versions:

It's possible with APEX Office Print, you will need to use {!dynamic_sheet} tag. Given your select statement:

select
  'file1' as "filename", 
  cursor(
    select  
      cursor(
        select
          'Actual sheet name' "sheet_name"
        from dual
      ) as "dynamic_sheet"
     from dual
  ) as "data"
from dual

You can use: {!dynamic_sheet} tag in your sheet (which ever cell, won't matter) where you want the name changed for. You sheet name will now change to Actual sheet name.

You can combine this query with your data query as well like follows:

select
  'file1' as "filename", 
  cursor(
    select  
      cursor(
        select
          'Actual sheet name' "sheet_name"
        from dual
      ) as "dynamic_sheet",
      cust_first_name,
      cust_last_name
     from demo_customers
  ) as "data"
from dual

Hope this helps you out.