Skip to main content

Additional Features

PDF Document Properties​

Every document has properties such as title ,tags ,created date ,modified date , and comments.
With AOP, you can dynamically assign this documentation information to a document directly within the data.

You can provide data for the title, tags, and comments directly from the data source. However, you need to provide the created date and modified date on INIT PL/SQL region.

Supported for DOCX, XLSX and PPTX outputs.

aop_api_pkg.g_output_modified_date := '2024/11/11';
aop_api_pkg.g_output_created_date := '2024/10/11';

Note: The created and modified dates are set manually for user convenience, allowing users to adjust the dates as they prefer.
EXAMPLE USAGE:

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",
            c.cust_last_name || ' Order_summary' as "title",
            'Order' as "tag",
            'product_name' as "comment",
            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",
                            p.category as "category",
                            'true' as "category_group"
                        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
                        order by p.category asc
                    ) "product"
                from aop_sample_orders o
                where c.customer_id = o.customer_id
            ) "orders"
        from aop_sample_customers c
        where customer_id = 1
    ) as "data"
from dual;

Sample template image for documentation information

alt text

 pdf_document_properties.docx  

Password Encryption​

Available From: v25.1

This feature allows the generated document to be password protected. Supported template and output type is: DOCX, DOCM, XLSX, XLSM, PPTX, PPTM and PDF

You can provide the password using the following Init PL/SQL.

aop_api_pkg.g_output_read_password:= 'read_password';

Example​

The following example demonstrates on how password encryption can be done in the generated output.

Data Source​

In the Init PL/SQL the following commands were passed:

aop_api_pkg.g_output_read_password:= 'aop-pass';

Here by the data source used for the demonstration of Password Encryption.

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", 
                              APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image",
                              --'https://www.apexrnd.be/ords/apexofficeprint/aop/image?product_id='||p.product_id as "image",
                              40 as "image_max_width"
                         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 = 1
  ) as "data"
from dual

Template​

Here are the templates for password encryption.

 template.docx    template.xlsx    template.pptx  

Output​

Upon processing the data source and template, the generated outputs are:
Password For these output: _ aop-pass_

 output.docx    output.xlsx    output.pptx  

Useful Output Types​

APEX Office Print offers various options output types for the processed files. These options are available in the "Output To" field in the "Settings" section. The available options are as follows.

Here are a few of these options, explained below:

Count Tags​

The Count Tags output type in AOP provides an option to get the tags and their counts from the template.
It helps verify the tag names, their case sensitivity, occurrences, and structure within the template.

Example:​

Following is an example on how this could be used:

PLSQL code:​

Here is an example of PL/SQL code used to call AOP, which returns a JSON response in a CLOB.

declare
  l_output_filename varchar2(300);
  l_blob            blob;
  l_clob            clob;
begin
  l_blob := aop_api_pkg.plsql_call_to_aop(
              p_data_type        => aop_api_pkg.c_source_type_none,
              p_template_type    => aop_api_pkg.c_source_type_apex,
              p_template_source  => 'aop_template_d01.docx',
              p_output_type      => aop_api_pkg.c_count_tags,
              p_output_filename  => l_output_filename,
              p_aop_url          => :AOP_URL,
              p_api_key          => :AOP_API_KEY,
              p_app_id           => :APP_ID
            );
      
  l_clob := aop_api_pkg.blob2clob(l_blob);
  
  l_clob := replace(l_clob,'],','],'||CHR(10));
  return l_clob;
end;

Template​

The template includes tags of AOP.

alt text

 template.docx  

Output:​

When the above PL/SQL code and template are provided, the following output is generated by AOP.

{
  "{cust_last_name}": 1,
  "{cust_first_name}": 2,
  "{cust_city}": 1,
  "{#orders}": 2,
  "{#product}": 2,
  "{product_name}": 2,
  "{/product}": 2,
  "{order_total}": 2,
  "{/orders}": 2,
  "{%image}": 1,
  "{#unit_price>80}": 1,
  "{unit_price}": 2,
  "{/unit_price>80}": 2,
  "{^unit_price>80}": 1,
  "{quantity}": 1,
  "{unit_price*quantity}": 1,
}

The Count Tags output provides the count of each tag used in the template, along with the number of times each tag appears.

For example:

  • **{cust_first_name}: 2 ** means the tag {cust_first_name} is used 2 times in the template.
  • **{cust_last_name}: 1 ** indicates the tag {cust_last_name} appears once.

NOTE: It is available for all template types except PDF.

Form Fields​

Form Fields is a special output type that allows you to retrieve the form fields from the PDF template.
This is useful when you want to use the form fields in your template.
The form fields information are returned in JSON format.

Example:​

Following is an example on how this could be used:

PLSQL code​

Here is an example of PL/SQL code used to call AOP, which returns a JSON response in a CLOB.

declare
  l_output_filename varchar2(300);
  l_blob            blob;
  l_clob            clob;
begin
  l_blob := aop_api_pkg.plsql_call_to_aop(
              p_data_type        => aop_api_pkg.c_source_type_none,
              p_template_type    => aop_api_pkg.c_source_type_apex,
              p_template_source  => 'aop_pdf_form_filled.pdf',
              p_output_type      => aop_api_pkg.c_form_fields,
              p_output_filename  => l_output_filename,
              p_aop_url          => :AOP_URL, -- Your url
              p_api_key          => :AOP_API_KEY,
              p_app_id           => :APP_ID
            );
      
  l_clob := aop_api_pkg.blob2clob(l_blob);
  
  l_clob := replace(l_clob,'],','],'||CHR(10));
  
  return l_clob;
end;

Template​

The template consists a form field where, AOP will return form field information such as the field name, its type, and value.

alt text

 template.pdf  

Output​

When the above PL/SQL code and template are provided, the following output is generated by AOP.

{"First Name":[{"type":"string","value":"Dimitri"}],
"Last Name":[{"type":"string","value":"Gielis"}],
"radiolist":[{"type":"radio","options":["List A","List B"],
"value":"List A"}],
"radio":[{"type":"radio","options":["A","B"],
"value":"B"}],
"Agree":[{"type":"boolean","value":true}]}

The returned JSON contains the following information:

  • field name: The name of the form field.
  • type: The type of the form field.
  • value: The value of the form field.

Where,

  • field name: First Name
  • type: string
  • value: Dimitri

Please go through our sample application for implementation details.

Change Locale​

Available From: v25.1

AOP provides the option to change the locale of the documents. Changing locale helps Ms Office and LibreOffice apps to interpret them properly. It does not change the existing text itself. AOP has a map of supported languages and their respective code. Provide the name of the langauage to change the locale. Using language/locale not defined in AOP will result in error.

Available languages
Language Language Tag
Afrikaans af
Albanian sq
Amharic am
Arabic ar
Armenian hy
Assamese as
Basque eu
Belarusian be
Bengali bn
Bosnian bs
Bulgarian bg
Catalan ca
Chinese zh
Croatian hr
Czech cs
Danish da
Dutch nl
English en
Esperanto eo
Estonian et
French fr
Galician gl
Georgian ka
German de
Greek el
Gujarati gu
Hebrew he
Hindi hi
Hungarian hu
Icelandic is
Indonesian id
Irish ga
Italian it
Japanese ja
Kannada kn
Kazakh kk
Khmer km
Korean ko
Kurdish ku
Lao lo
Latvian lv
Lithuanian lt
Macedonian mk
Malayalam ml
Marathi mr
Mongolian mn
Nepali ne
Norwegian no
Oriya or
Persian fa
Polish pl
Portuguese pt
Punjabi pa
Romanian ro
Russian ru
Serbian sr
Sinhala si
Slovak sk
Slovenian sl
Spanish es
Swahili sw
Swedish sv
Tamil ta
Telugu te
Thai th
Turkish tr
Ukrainian uk
Uzbek uz
Vietnamese vi
Welsh cy
Xhosa xh
Zulu zu

Example​

The example below shows you how to change locale of a document. "nepali" is provided as the language in the example below. AOP will add its respective language code that is used and understood by the document to the document.

Data Source​

Hearby the Init PL/SQL code to set the local of the output.

aop_api_pkg.g_output_locale  := "nepali";

Output​

The selected output type is pdf, and upon processing the following result is obtained.

 changed_locale.pdf  

Matrix Template (Dynamic row and columns)​

Matrix template refers to template that prints rows and columns in a table that are only available in the data. If you have rows and columns that keep changing based on the data and you want to print only the available rows and columns, then you can use matrix template. This is done by using combination of following tags:

  • Horizontal loop tag: {:columnName}...\{/columnName}
  • Vertical loop tag: {#columnName}...\{/columnName}
  • Special tags: {#columnName|keys}\{.}\{/columnName|keys}, \{#columnName|values}\{.}\{/columnName|values}
    OR
  • AOP 25.1.2 Special tag: {#columnName|keys:true}{key}\{/columnName|keys}

Using {#columnName|keys:true}{key}\{/columnName|keys} to create a matrix template works well only for xlsx templates. Using this tag in docx might give unexpected results. There are two examples below that show how to create a matrix template using the above tags. The first example Example 1 works in both docx and xlsx templates and does not use AOP 25.1.2 Special tag, while the second example Example 2 works only in xlsx templates and does not use Special tags.

Example 1​

Below is an image of matrix template created with the combination of above mentioned tags (excluding AOP 25.1.2 Special tag). All of these tags should be inside a table cell. The horizontal loop tag can not be used after vertical loop tag in the same table cell

Matrix Tempalte DOCX Initial

Here {:product|keys}\{.}\{/product|keys} prints the keys of the product (all the rows available in product), so we get all the headers that are available horizontally.
Then to print each and all the values of product in a row we combine the vertical and horizontal loop tags as {#product}\{:product[current_child_index]|values}\{.}\{/}\{/}. This means that for each product record we will print the values of the product in a row. The {#product} is the vertical loop tag that loops through each product record, and {:product[current_child_index]|values}\{.}\{/} is the horizontal loop tag that prints all the values of the current product record in a row. {/}\{/} is the closing tag for {:product[current_child_index]|values} and \{#product}.

Data Source​

Below is the data source used for template. You can try removing some of the keys and values from the data source to see how the template adapts to the changes.

select 'file1' as "filename",
       cursor (
         select "Jack"as "customer_name",
                 "123 Main St, Springfield" as "customer_address",
                 cursor (
                     select p.product_name as "Product Name",
                            p.list_price   as "Price",
                            p.category     as "Category"
                     from aop_sample_product_info p
                     where rownum < 5
                 )      as "product"   
         from dual
       )       as "data"
from dual

Template​

The templates consists of a table with the above mentioned tags. The docx template below looks different than the example template above as left table cells and their text are styled differently so that they are not visible in the output.

Matrix Tempalte DOCX

 template.docx  

Matrix Tempalte DOCX

 template.xlsx  

Output​

When the above data source together with the given template is passed to AOP, the output will be as follows.

Matrix Tempalte DOCX

 matrix_template.docx  

Matrix Tempalte DOCX

 matrix_template.xlsx  

Example 2​

Below is an image of matrix template created with the combination of above mentioned tags (excluding Special tags).

Matrix Tempalte DOCX

Here {:product|keys:true}{key} prints the key of the product (row available in product) horizontally, then since its not closed and right after that we have the vertical loop tag with current index of the product and its key {#products}{products[products_index][key]} this tag is executed which means, for that key of the product record we will print all of its values vertically.
After printing all the values of the key, outer loop (horizontal loop) is executed again and the next key is printed horizontally, then inner loop (vertical loop) is executed again to print all of its values vertically. This continues until all the keys and values of the product record are printed.{/}\{/} is the closing tag for {:product|keys:true}{key} and {#product}.

This methods handles the case where a certain record might not have all the key values that other records have.

Data Source​

Below is the data source used for template. You can try removing some of the keys and values from the data source to see how the template adapts to the changes.

select 'file1' as "filename",
       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
       )       as "data"
from dual

Template​

The template is given below and is same as the template shown above.

Matrix Tempalte DOCX

 template.docx  

Output​

When the above data source together with the given template is passed to AOP, the output will be as follows.

Matrix Tempalte DOCX

 matrix_template.xlsx