Skip to main content

DOCX Templates

Introduction​

This section will go through all the tags that are available for docx template, together with the appropriate data selection query. In this documentation, curly braces {...}are used as delimiters for the tags. Please check check general template for how to change the delimiters . The templates can be made using various softwares like Microsoft Word, Libreoffice Writer, APEX Office Edit, or Google Docs. The files should be in .docx or .docm format.

Microsoft Word Libreoffice Writer Google Docs

Tag Overview​

The following tables shows the available tags in docx template. The three dot in the format column shows what is variable, they should be either replaced by cursor name or column name. Please note that the column names are case sensitive, you can use double quotes to force the casing.

TagFormatExampleShort Description
Normal Substitution{...}{normal}Normal Substitution, the data from the given column will be replaced.
Image Tag{%...}{%imageKey}Tag to be used when the content is an image. (can point to url, file, base64encoded string) Options
Loop Tag{#...} {/...}{#data_loop} … {/data_loop}Tag that loops over the given cursor name and repeats everything in between the tags for each record.
Uncertain Loop Tag{#...} {/...}{#data_loop?} … {/data_loop?}Special tag, similar to the Loop tag, functions in the same way but avoids leaving an empty paragraph in cases where the record is null, undefined, or empty.
Horizontal Tabular Looping{:...} {/...}{:data_loop_horizontal} … {/data_loop_horizontal}Tag to be used when inside a table column. Will repeated horizontally in the given column(s) for the given cursor.
Conditions{#...} {/...}{#condition} … {/condition}Tag that checked whether or not the given condition is truthy and falsy,if falsy the content between the tags is removed.
Table Cell Style Tag{...$}{normal_with_style$}Special tag to be used inside the table of word. Will be replaced like normal tag but extra styling can be provided.
Style Tag {style ...}{style name}Tag to be used when the replacing content must have different style than the tag style.
HTML Content Tag{_...}{_htmlcontent}Tag to be used when the content of a column is HTML content.
Barcodes and Qr codes{|...}{|barcode}Tag to use when a barcode/qrcode needs to be generated in it's place.
QR Code Image ReplacingQR code of TagQR code of TagReplacing QR code with an image,barcode or qr code when custom styling is needed.
Chart{$...}{$chart}Tag to be used when a native word chart should be generated, see details for specific format of what this cursor should contain.
Chart Templating{aopchart ...}{aopchart chartData}Tag to be used near a chart that is defined and style in the template, see details for passing the data for this dummy chart.
D3 Images{$d3 ...}{$d3 image_data}Tag to be used to insert d3 images.
Page Breaks{?...}{?pageBreak}Tag that will insert a pagebreak when the provided condition evaluates to true.
Column Break Tag{?"columnbreak"}{?"columnbreak"}Tag that will insert a column break. If the tag is provided with condition and it evaluates to true then only column break is inserted.
Watermark Tag{watermark ...}{watermark watermark_string}Tag that will put the watermark in the document.
Markdown Tag{_..._}{_markdownContent_}Tag to be used when the provided data is markdown.
Raw Tag{@...}{@raw}Tag that replaces the given tag with the xml provided. For Advanced users only.
Hyperlink Tag{*...}{*hyperlink}Substitution like normal tag, but will contain a hyperlink so that a user can be directed.
Auto Hyperlink Tag{*...}{*auto text}Tag that automatically detect URLs and mail addresses and convert them to hyperlinks from one single value.
Internal link Tag{*link ...} {*target...}{*link /*target}Tags that should be used in pair, will generate a link from link to the given target pair.
Table of Content Tag{~...}{~toc}Tag to be used for generating table of content. Options availabe see details.
Footnote tag{+...}{+footnote}Tag to be used when a dynamic footnote should be created.
Labels{-...}{-labels}Special tags to be used for labels mail merging.
Form Tag{?form ...}{?form name}Tag to be used in order to generate PDF forms. Output must be PDF.
Attach Tag{?insert ...}{?insert insertDocument}Tag that will attach the given document inside the template/output.
Embed Tag{?embed ...}{?embed fileToEmbed}Tag that will take the content of the document provided and merge it with the template. Tags inside the embedded document won't be rendered.
Subtemplate Tag{?include ...}{?include subtemplate}Tag that will insert the subtemplate into the main template after rendering.
Classic Report Tag{&...&}{&classic_report&}Tag to be used the data source is a classic report.
Interactive Report Tag{&interactive}{&interactive} or {&interactive_1}Tag to be used when the data source is an interactive report. This will recreate the given interactive report in Word.
Interactive Grid Tag{&...&}{&interactive_grid&}Tag to be used the data source is an interative grid.
Calendar Tag{?CALENDAR ...}{?CALENDAR cal}Calendar region export from APEX
note

Tags can't start with a number and should start with an alphabetical character (a-z,A-Z)

Special Tags

Other special tags can be used. Please refer to the general template section: Special Tags

Normal Substitution​

Available From: v1.0

These kind of tags are the simplest tag to use. These tags are enclosed in curly braces and can include variables (name of column) that will be replaced with actual data when the output is generated. The replaced value will have the same style of the tag itself.

Styling

To be more specific on the style of the replaced content, the style of the starting curly brace is taken. When the tag becomes long, you can style the starting curly braces and change the font of remaining tag to a smaller size. eg: {cust_first_name}

Example​

Date Source​

Hereby example data source for different options.

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"
         from aop_sample_customers c
         where c.customer_id = 1
       )       as "data"
from dual;

Template​

The template should contain the name of the columns that were provided in the query above. For example we have the template normal_substitution.docx with following content:

Dear {cust_first_name} {cust_last_name},

Thank you for your recent purchase. We hope you're enjoying your new product.

If you have any questions, please don't hesitate to contact us.

Best regards,
AOP Team

Output​

When the above data source (which results in one row with John as cust_first_name and Dulles as cust_last_name) together with the given template is passed to AOP, the output will be as follows. 

Dear John Dulles,

Thank you for your recent purchase. We hope you're enjoying your new product.

If you have any questions, please don't hesitate to contact us.

Best regards,
AOP Team

Image Tag​

Available From: v1.0

Image tags are a useful way to insert images into your reports. These tags are denoted by a percent sign (%) and allow you to include images from a file path, URL (http, https, ftp, sftp), or base64 encoded data. In order to use a file path, you will need to enable local resource in AOP server configuration. See here //TODO:

Given the following simple example SQL Query:

select 'file1' as "filename",
       cursor (
         select 'https://picsum.photos/seed/picsum/200/300.jpg' as "random_image"
         from dual
       )       as "data"
from dual

The url is named random_image , the tag to be used will be {%random_image}.

Image Tag Options​

Usages

The following options should be prepended with the column name being used followed by an "_". For example, if the column name is "image", then the options would be "image_width", "image_height", "image_alt"_text, etc. See example below.

Case Sensitive

The options are case sensitive. For example, "_width" is valid, but "_Width" is not. You will need to wrap the column name with double quotes in order to force the casing.

Units

AOP 20.3 introduced other units than px (default), in, cm, pt, and em.

  • width: Specifies the fixed width of the given image. Units: px (default), in, cm, pt, and em. If not specified, the inserted image's width will be actual image width or available width of the paragraph, whichever is smaller. eg: "_width" : "80px"

  • height: Specifies the fixed height of the given image. Units: px (default), in, cm, pt, and em. If not specified, the inserted image's height will be actual image height or available height of the paragraph, whichever is smaller. eg: "_height" : "80px"

  • alt_text: Specifies the alternative text of the inserted image. Blank if not specified. eg: "_alt_text" : "This is an image"

  • max_height: Maximum height for scaling image proportionally. Units: (default) px, in, cm, pt, and em.

  • max_width: Maximum width for scaling image proportionally. Units: (default) px, in, cm, pt, and em.

  • transparency: For specifying transparency of image. Values are in percentage like "90%". default: 0%

  • rotation: For specifying rotation to image, in degrees for example (90, 180)

  • ignore_error: true, if default image should be inserted if image cannot be fetched, if false, throws error. default: true

  • url : For specifying hyperlink url for this image, the user will be redirected to this url when clicked on image.

  • maintain_aspect_ratio: whether or not to maintain aspect ration of images, width option or height option should be specified for this option to work.

  • wrap_text : Wrap text option, can be: front, behind, top-bottom, square or default (default meaning in line with text).

Example usages in the SQL query:

select 'file1' as "filename",
       cursor (
         select 'https://picsum.photos/seed/picsum/200/300.jpg' as "image",
                '60px'                                          as "image_width",
                'Random picture on the internet'                as "image_alt_text",
                'square'                                        as "image_wrap_text",
                '90%'                                           as "image_transparency",
                '90'                                            as "image_rotation",
                1                                               as "image_ignore_error"
         from dual
       )       as "data"
from dual
Image Size

When manual height and width is not provided and the dimensions of image are greater than the page width or page height, then image size is reduced, such that it fits in page without changing the aspect ratio of image.

If the image is stored as blob in your table, you can use apex_web_service.blob2clobbase64 function to convert blob to base64 encoded image, example:

select p.product_name                                    as "product_name",
       apex_web_service.blob2clobbase64(p.product_image) "product_img",
       500                                               "product_img_max_width",
       500                                               "product_img_max_height"
from aop_sample_product_info p

Supported Image Format Types​

FormatExtensionMimeType
JPEG.jpg, .jpeg, .jfif, .pjpeg, .pjpimage/jpeg
PNG.pngimage/png
GIF.gifimage/gif
BMP.bmpimage/bmp
TIFF.tiff , .tifimage/tiff
SVG.svgimage/svg+xml
WEBP.webpimage/webp
HEIC.heicimage/heic

Example​

select 'file1' as "filename",
       cursor (
         select product_name                                    as "product_name",
                apex_web_service.blob2clobbase64(product_image) as "pic",
                500                                             as "price",
                100                                             as "pic_max_height",
                'alternative text'                              as "pic_alt_text"
         from aop_sample_product_info
         where product_name = 'Wallet'
       )       as "data"
from dual;

with template:

ProductImagePrice
{product_name}{%pic}{price}

will result in:

Extra example GIF for different source:

Frequent Errors:​

  • You might sometimes get the following error (might occur when the tag is inside loop):
    The tag "image" is not inside a paragraph

    In this case, just change the style of the tag (the whole {%image}), for example change the text color to red, and the error should be resolved.

Loop Tag​

Available From: v1.0

AOP enables the iteration through a set of records using the loop tag. The loop tag consists of an opening and closing tag, denoted by # and / respectively, followed by the record or column name enclosed within delimiters, such as {#record1}{name}... {/record1}.

By using the opening tag {#data_loop} and closing tag {/data_loop}, we can iterate through the elements of the column "data_loop". This means that the content within the tags will be repeated for each element in the array. "data_loop" is a column/record.

Example​

Data Source​

Hearby the example of data source for different options.

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"
                           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​

The following template shows the iteration of orders and product columns with the help of loop tag, where the orders and product are iterated outside and inside of table respectively. When loop tags are used inside the table, it will create a new row for each records.

 loop-tags-template.docx  

Output​

The given data source and template gives the following output when processed by AOP.

 loop-tags-output.docx  

Uncertain Loop Tag​

Available From: v22.2

Uncertain Loop Tag is same as the normal loop tag available in AOP. However if the loop fails (i.e condition fails) and this results in an empty paragraph, this empty paragraph will be removed. This tag should start with (#) and end with question mark (?). Ex {#loop?} ... {/loop?}.

Example​

Data Source​

Hearby the example of data source for different options.

select 'file1' as "filename",
       cursor (
         select cursor (
           select product_name as "prod_name",
                  category     as "category"
           from aop_sample_product_info
         ) as "products"
       )       as "data"
from dual

Template​

The template contains the normal loop tag and uncertain loop tag to demonstrate the difference between them.

 uncertain-data-loop-template.docx  

Output​

Upon processing the provided template and data using AOP, the resulting output is as follows.

 uncertain-data-loop-output.docx  

Horizontal Tabular Looping​

Available From: v18.1

Horizontal Tabular Looping interates over the records and creates the column for each records. It also consists opening and colosing tag, denoted by : and / followed by tagname enclosed inside delimiters respectively. For example: {:record1} ... {/record1} . However, this tag can be used to repeat only one row in Word(In Excel, this works like normal loop tag and repeats the cells defined by the rectangular boundary of starting and closing tag).

Since AOPv22.1.5, _distribute can be used with tag name to distribute the data evenly among the columns. For example, the following tag will distribute the data evenly among the columns:

'true' as "tagname_distribute"

It's value can be boolean true or false depending on the use case.

Example​

Following is an example on how this could be used:

Data Source​

Hearby the example of data source for different options.

select 'file1' as "filename",
       cursor (
         select cursor (
           select product_name as "product_name"
           from aop_sample_product_info p
           where rownum < 5
         )      as "product_a",
                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
                        and product_id > 5
                )      as "product_b",
                "true" as "product_b_distribute",
                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
                                 and rownum < 5
                         )                  "product"
                  from aop_sample_orders o
                  where o.customer_id < 3
                )      "orders"
         from dual
       )       as "data"
from dual

Template​

The template consists usage of horizontal looping with and without _distribute, and combination of vertical and horizontal loops as shown below.

 horizontal-tabular-looping.docx  

Output​

With the provided data source and the template, following output is generated by AOP.

 horizontal-tabular-looping-output.docx  

Conditions​

The Loop tag can also serve as a conditional tag by utilizing comparison operators, as demonstrated in the example below. It supports iteration of data in bullets and numbering as well. For more detailed explanation on conditions, You can visit Simple Angular like Expressions section of General Tags.

Example​

Data Source​

Hearby the example of data source for different options.

select 'file1' as "filename",
       cursor (
         select cursor (
           select product_name as "prod_name",
                  list_price   as "price",
                  category     as "category",
                  case
                    when mod(rownum, 2) = 0 then
                      'true'
                    else
                      'false'
                  end          as "in_stock"
           from aop_sample_product_info
         ) as "products"
       )       as "data"
from dual

Templates​

The following template shows the utilization of loop tags in various conditions, such as boolean, string, and numeric value checks, to retrieve the desired data within a table. It showcases examples of using bullets and numbering as well.

 conditions-template.docx  

Outputs​

 conditions-output.docx  

Table Cell Style Tag​

Available From: v21.1.1

Table cell style tag can be used to specify the table cell markup/style on top of replacing the tag with text. Background color, cell width and row merging are supported.

The tag will end with a $ sign at the end. This dollar sign should be prepended with the column name that holds the value of the replacing text.

This tag in Word is to be used inside a table cell. The options for cell markup are limited in Word. The background color and width for the given column can be set. Width manipulation is available since AOPv20.2. Giving a width of 0 will remove the whole column. The background information should be specified by appending "_cell_background_color" and the width info by appending "_width". The data for the background color should be in hex format. The cell-width can be specified in "in", "cm", "px", "pt", "em" and "%" (in respect to the initial width of the table).

Table Cell Style Options​

The options should be appended with the column name that holds the value. The options are case sensitive.

  • _cell_background_color : provides the backgound color of current cell. hex value.
  • _width: provides the width of the cell. The values can be in cm, in, px, pt, em, and "%" (in respect to the initial width of the table). Setting it to 0 will remove the whole column.
  • _preserve_total_width_of_table: If the column is to be removed(setting the width of column to 0) but the total width of table should not be decreased, then set it to true (false by default.) The width of column to be removed is evenly distributed among the columns of the table.
    (Ex. true as "columnName_preserve_total_width_of_table").

Example Cell Styling​

Here is an example showcasing the usage of the Table Cell Style Tag to demonstrate cell styling within a table.

Data Source​

Hearby the example data sources for different options. 

select 'file1' as "filename",
       cursor (
         select 'Country'          as "c1_title",
                'Nepal'            as "c1_data",
                '#eb9b34'          as "c1_title_cell_background_color",
                '6cm'              as "c1_title_width",
                'Continent'        as "c2_title",
                'Asia'             as "c2_data",
                '5cm'              as "c2_title_width",
                '#5c803b'          as "c2_title_cell_background_color",
                'Highest Altitude' as "c3_title",
                '8848m'            as "c3_data",
                '5cm'              as "c3_title_width",
                '#803b3b'          as "c3_title_cell_background_color",
                'Will be removed'  as "c4_title",
                ''                 as "c4_data",
                '0%'               as "c4_title_width"
         from dual
       )       as "data"
from dual;

Template​

In the above source, we have four columns. We will be giving 4 columns different colors and width. The last column will be removed, as the width provided from data source is 0%. The template looks as follows:

Template example for cell styling in word

Output​

Upon processing the provided template and data using AOP, the resulting output is as follows.

Output Example for cell styling in Word

Example Row Merging​

Available From: v21.1

Table Cell Style can be also be used to merge rows.
_group should be appended with the column name to merge two or more cells, if the values in those cells are same. As for example

Example​

Data Source​

Hearby the example data sources for different options. 

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",
                                  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
Template​

The template shows the merging of category {category$} column that have same value.

 row-merge-template.docx  

Output​

Upon processing the provided template and data using AOP, the resulting output is as follows.

 row-merge-output.docx  

Style Tag​

Available From: v20.3

The {style ...} tag enables users to style their text in Word. It functions similar to a regular substitution, while also allowing users to provide the desired style from their side using SQL. It currently supports 7 different styling options, users can choose from following options. These options should be included in the data by appending _ to the name of the column that holds the replacing text value. If the value is in column progress column, The font family option would be progress_font_family. The options are case sensitive.

  • _font_family: Determines the font family. Valid font names like: AnjaliOldLipi, Z003, Liberation Sans
  • _font_size: Determines the font size of the replacing text. Values should be numbers or Points like 20pt , 100. Note: If the number is used like (50,100,200) then the actual text size in output reduces by 25% whereas using “pt” results in the same size as given input.
  • _font_color: Determines the font color of the replacing text. Hex Color Codes like #ff00ff #002060
  • _bold : Determines whether or not the replacing text should be bold. Can be: true,false,”true”,”false”,0,1
  • _italic : Determines whether or not the replacing text should be italic. Values can be true,false,”true”,”false”,0,1
  • _underline : Determines whether or not the replacing text should be underlined. Values can be: true,false,”true”,”false”,0,1
  • _strikethrough : Determines whether or not the replacing text should be strikethrough. Values can be true,false,”true”,”false”,0,1
  • _highlight : Determines the color for highlighting the color of replacing text. Values should be thename of colors which are available for highlighting in Word. Currently 15 colors are supported: yellow, green, cyan, magenta, blue, red, darkBlue, darkCyan, darkGreen, darkMagenta, darkRed, darkYellow, darkGray, lightGray, black.
Note

Users can omit including above tags for styling which results in the same styling as defined in the template for these features.

Example​

Data Source​

Hearby the example data sources for different options. 

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_street_address1 as "cust_st_addr",
                c.cust_email           as "cust_email",
                c.cust_state           as "state",
                c.cust_postal_code     as "postal_code",
                'NanumMyeongjo'        as "cust_first_name_font_family",
                '25pt'                 as "cust_first_name_font_size",
                '#ff00ff'              as "cust_first_name_font_color",
                1                      as "cust_last_name_bold",
                1                      as "cust_city_italic",
                1                      as "cust_st_addr_underline",
                1                      as "cust_email_strikethrough",
                'black'                as "cust_st_addr_highlight",
                'white'                as "cust_st_addr_font_color",
                ''                     as
         from aop_sample_customers c
         where c.customer_id = 1
       )       as "data"
from dual

Template​

The template consist of multiple style tags used for the styling of different column values.

 style-tag-template.docx  

Output​

The provided data and template will result in:

 style-tag-output.docx  

HTML Content Tag​

Available From: v2.1

AOP also provides option to insert the HTML contents in Word using HTML tag( _ followed by column name enclosed by the delimiters eg. {_htmlContent}). This enabled render of HTML contents within word. The tags that are currently supported are:

Supported HTML Tags
TagDescription
<br /> in order to introduce breaks (newline)
<p> .. </p> represents a paragraph
<strong> .. </strong> bold text
<b> .. </b> bold text
<s> .. </s> strike through
<u> .. </u> underline
<em> .. </em> italics
<h1> .. </h1> heading 1
<h2> .. </h2> heading 2
<h3> .. </h3> heading 3
<h4> .. </h4> heading 4
<h5> .. </h5> heading 5
<h6> .. </h6> heading 6
<sub> .. </sub> subscript
<sup> .. </sup> superscript
<ol> .. </ol> ordered list
<ul> .. </ul> unordered list
<li> .. </li> list item
<table> .. </table> table (including th, tr, td)
<caption> .. </caption> caption
<img> image
<pre> .. </pre> preformatted text
<blockquote>.</blockquote> quoting for multiple lines
<q> .. </q> quoting for single line
<dfn> .. </dfn> definition element
<span style="..">..</span> text between the span will have the style defined, background-color, color, font-size and font-family are supported.

Table​

The HTML table tag is supported by AOP. Here are the table elements that are supported:

<table> ... </table>
<tr> ... </tr>
<th> ... </th>
<td> ... </td>
<thead> ... </thead>
<tbody> ... </tbody>
<tfoot> ... </tfoot>

The <thead> tag defines the header row and the <tfoot> tag defines the "total row".

While creating a template for an HTML table we can specify a table style. We can do this either by creating a new design and naming it like "customTable" or by modifying an existing table style and giving an alias to that table.

Now, Customizing the table and providing Name for the table.

To use the custom table, you can add _custom_table_style in the prefix of the column name and table name to its value.

The sql to use custom table will be as follows :

        ...
        '/.... table content here ..../' as "tableContent",
        'CustomTableAOP' as  "tableContent_custom_table_style",
        ...

Ordered Lists and Unordered Lists​

APEX Office Print supports HTML ordered <ol> and unordered <ul> tags. These two tags can also be nested. The example below shows the usage of these two tags.

Word is capable of changing the styling of the symbols for <ol> and <ul> tags. This can be achieved through adding an additional parameter to the json file provided. For the <ol> tag this is _ordered_list_style and for the <ul> tag this is _unordered_list_style. These properties take numeric values.

If the styling in the red box choosed that would correspond to styling number 1 which is indicated in the "Document Number Formats" with green box. If another style were to choosen that would have style number 2.
In short every added style is enumarated starting from 1. For example if the below JSON is provided with the above HTML code,

'......' as "listContent",
'1' as "listContent_ordered_list_style",

Ordered list starting value can be user defined with HTML start parameter. Given the example HTML code below:

<ol start="10">
    <li>Item 1</li>
    <li>Item 2</li>
    <li>Item 3</li>
</ol>

Furthermore the list hierarchary can be customize and style attribute of <ol> tag are supported. Style attributes which are supported are listed below:

    <ol style="list-style-type:decimal">...</ol>                : starting with 1.
    <ol style="list-style-type:decimal-leading-zero">...</ol>   : strrting with 01.
    <ol style="list-style-type:lower-alpha">...</ol>            : starting with a)
    <ol style="list-style-type:lower-latin">...</ol>            : starting with a.
    <ol style="list-style-type:lower-roman">...</ol>            : starting with i.
    <ol style="list-style-type:upper-alpha">...</ol>            : starting with A)
    <ol style="list-style-type:upper-latin">...</ol>            : starting with A.
    <ol style="list-style-type:upper-roman">...</ol>            : starting with I.
HTML Image dimension

When manual image size is not provided and size of image is larger than the dimension of page, then size of image is reset to fit in page without changing the aspect ratio of the image.

Example​

Data Source​

Hereby example data source for different options. 

select 'file1' as "filename",
       cursor (
         select nvl(:p172_htmlcontent, 'no <b>apex</b> session')                                                                                          as
         "overview",
                cursor (
                  select cust_first_name           as "first",
                         '<p><strong><span style="color:green">'
                         || cust_first_name
                         || '</span></strong></p>' as "name_st_green"
                  from aop_sample_customers
                )                       as "cust_names",
                '<ol> <li> Level 1.1 <ul> <li>Level 2.1</li> <li> Level 2.2 <ol> <li>Level 3.1</li> <li>Level 3.2</li> </ol> </li> </ul> </li> <li>Level 1.2</li> <li>Level 1.3</li> </ol>' as "nestedList",
                '<ol start="10"> <li>Item 1</li> <li>Item 2</li> <li>Item 3</li> </ol>'         as "list_start_10",
                '<ol> <li> Level 1.1 <ol type="A"> <li>Level 2.1</li> <li> Level 2.2 <ol style="list-style-type:lower-alpha"> <li>Level 3.1</li> <li>Level 3.2</li> </ol> </li> </ol> </li> <li>Level 1.2</li> <li>Level 1.3</li> </ol>' as "list_with_style",
                '<ol> <li>Cat</li> <li>Dog</li> </ol>'  as "list2",
                '1'                     as "list2_ordered_list_style",+
                '<table border="1" > <tr> <th>Name</th> <th>Age</th> <th>Country</th> </tr> <tr> <td>Harry Depp</td> <td>28</td> <td>Britain</td> </tr> <tr> <td>John Smith</td> <td>35</td> <td>USA</td> </tr> <tr> <td>Ram Krishna</td> <td>19</td> <td>Nepal</td> </tr> </table>' as "html_table_1",
                '<table border="1"> <thead> <tr> <th>Company</th> <th>Contact</th> <th>Country</th> </tr> </thead> <tbody> <tr> <td>Alfreds Futterkiste</td> <td>Maria Anders</td> <td>Germany</td> </tr> <tr> <td>Centro comercial Moctezuma</td> <td>Francisco Chang</td> <td>Mexico</td> </tr> <tr> <td>Ernst Handel</td> <td>Roland Mendel</td> <td>Austria</td> </tr> <tr> <td>Island Trading</td> <td>Helen Bennett</td> <td>UK</td> </tr> <tr> <td>Laughing Bacchus Winecellars</td> <td>Yoshi Tannamuri</td> <td>Canada</td> </tr> <tr> <td>Magazzini Alimentari Riuniti</td> <td>Giovanni Rovelli</td> <td>Italy</td> </tr> </tbody> <tfoot> <tr> <td>Company 2</td> <td>Contact 2</td> <td>Country 2</td> </tr> </tfoot> </table>' as "html_table_2",
                'CustomTableAOP'        as "html_table_2_custom_table_style",
                '<img src="https://picsum.photos/200/150">' as "image1",
                '<img src="https://picsum.photos/300/300" width="75px" height="75px">' as "image2"
         from dual
       )       as "data"
from dual

The region :p172_htmlcontent in the sql contains the following HTML.

<p>This is text coming from the database / session in HTML format.<br />
It supports:</p>

Lists with numbers:
<ol>
    <li><strong>Bold</strong></li>
    <li><em>Italic</em></li>
    <li><u>Underline</u></li>
    <li><s>Strikethrough</s></li>
    <li>Sub<sub>script</sub></li>
    <li><sup>Super</sup>script</li>
    <li><span style="color:#FF0000">Text Color</span></li>
    <li><span style="background-color:#FFFF00">Background Text Color</span></li>
</ol>

Lists without numbers:
<ul>
    <li>item 1</li>
    <li>item 2</li>
</ul>

<h1>Heading 1</h1>

<h2>Heading 2</h2>
<h3>Heading 3</h3>
<h4>Heading 4</h4>

<p>Normalt text with<span style="font-family:comic sans ms,cursive"> Font Change</span></p>

<p><span style="font-family:courier new,courier,monospace">Code font</span></p>

<table border="1" cellpadding="1" cellspacing="1" style="width:500px">
    <thead>
        <tr>
            <th scope="col">THeader 1</th>
            <th scope="col">THeader 2</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>some text</td>
            <td>&nbsp;</td>
        </tr>
        <tr>
            <td>&nbsp;</td>
            <td>some other text</td>
        </tr>
    </tbody>
</table>

<p>Other table with style:</p>

<table border="1" cellpadding="1" cellspacing="1" style="border:1px solid green; width:700px">
    <thead>
        <tr>
            <th scope="col">THeader 1</th>
            <th scope="col">THeader 2</th>
        </tr>
    </thead>
    <tbody>
        <tr>
            <td>some text</td>
            <td>&nbsp;</td>
        </tr>
        <tr>
            <td>&nbsp;</td>
            <td>some other text</td>
        </tr>
    </tbody>
</table>

<p>&nbsp;</p>

Use Tag Style​

While processing HTML contents, AOP uses the default paragraph format/styling of Word. To apply the paragraph styling from the template, you should specify a new column with _use_tag_style as a suffix for an HTML content tag and set true as its value.
A simple example for its usage :

    "htmlcontent": "<p>Hello World</p><ol>\n    <li> Level 1.1 \n        <ol type=\"A\">\n            <li>Level 2.1</li>\n            <li> Level 2.2 \n                <ol start=\"50\" style=\"list-style-type:lower-alpha\">\n                    <li>Level 3.1</li>\n                    <li>Level 3.2</li>\n                </ol>\n            </li>\n        </ol>\n    </li>\n    <li>Level 1.2</li>\n    <li>Level 1.3</li>\n</ol> ",
    "htmlcontent_use_tag_style": false

By default, use_tag_style is set to false, and the default paragraph styling will be used while processing. In cases where _use_tag_style is true, any changes in the below options (Paragraph setting in Word), made in the template file will be used for the processing. Despite the changes made in below options, if htmlKey_use_tag_style is set to false, the paragraph styling will always be set to default regardless of any modifications made.

Template​

The templates consists the usage of html tag in different ways like large html content, ordered and unorderedd lists with detault and custom list style, table with default style and custom table style.

Output​

Upon processing the provided template and data using AOP, the resulting output is as follows.

Barcodes and QR Codes​

Available From: v3.1

Barcode and QR code tags allows the user to insert barcode or QR code in the document. The barcode and QR code start with a pipeline sign. For example {|barcode}. The data must also contain the information for the type of barcode or QR code.

Frequent Errors:
  • You might sometimes get the following error (might occur when the tag is inside loop):
    The tag "qrcode" is not inside a paragraph

    In this case, just change the style of the tag (the whole {|qrcode}), for example change the text color to red, and the error should be resolved.

When the AOP identifies one of these tags in a template, it replaces it with the corresponding barcode or QR code.

Please visit Barcode and QR code of general tags documentation for detailed information.

QR Code Image Replacing​

Available From: v18.1

AOP allows you to replace a QRcode image with another image. The QR code can contain the image tags and barcode tags, i.e {%key} or {|key}. This method can be used if you are inserting a floating image, or need to have certain border styles on the image. The tag replacing will work as look as there are no artistic effects used for the image themselves.

Please visit QR Code Image Replacing in general tags documentation for detailed information.

Chart​

Available From: v2.0

It is possible to insert charts in Word using $ tag followed by cursor name inside delimiters. Ex {$chart}.

Please refer to Chart Tags in General Tags for detailed information and implementation.

Chart Templating​

Available From: v20.3

It is possible to create a chart by providing the chart style in template itself. You can provide a chart in the template and mark it with a preceding {aopchart chartData} where aopchart is the tag identifier and chartData is the cursor in the data which contains information about the chart.

Using this method, the chart in the template can be styled through MS Office or LibreOffice as an alternative to passing the style options as a part of the input data. This allows the use of style options we do not support, but moves the chart styling from the data to the template (e.g. loops containing a chart with different style on each iteration would not be possible using this tag).

Please visit Chart Templating of general tags documentation.

D3 Images​

Available From: v18.1

You can insert a d3 image in your report using the d3 tag, which follows the syntax $d3 followed by the column with the javascript d3 code within delimiters (for ex {$d3 test} considering test has javascript d3 code). When AOP runs the code for the d3 image, it will replace the tag with the resulting image.

Please visit D3 Images of general tags documentation.

Page Breaks​

Within AOP, there are two methods for inserting page breaks: using the page break tag {?...} and implementing conditional page breaks. Let's explore each method with examples below.

Page Break Tag​

Available From: v21.1

Using AOP, you can utilize the pagebreak tag {?...} (a tag that starts with "?") to dynamically insert page or column breaks. For example, if you want to insert a page break using {?pageBreak}, the corresponding value in the pageBreak column should be set to true or 1. If no break should be inserted, the value should be 0 or false.

Basically, the ? appended with condition that is either true or false enclosed by delimiters are used for a dynamic page break. After processing, a page break is inserted by AOP where the tag is located.

Expressions

This tag can be used together with expressions that AOP supports. Please click here for more info about the expressions and conditions.
The given expression when resolves to a truthy value, AOP will insert a page break.
For example, the following expression will insert a page break only when it's not the last record. {?(customers.length - 1 !== current_child_index)}

Example​

Data Source​

Hereby examples of data source for different options.

select 'file1' as "filename",
       cursor (
         select cursor (
           select c.cust_first_name as "cust_first_name",
                  c.cust_last_name  as "cust_last_name",
                  case
                    when c.customer_id = 3 then
                      'false'
                    else
                      'true'
                  end               as "pageBreak"
           from aop_sample_customers c
           where c.customer_id < 4
         ) as "customers"
         from dual
       )       as "data"
from dual

Template​

The template below show the usage of page break tag {?...}.

 page-break-tag.docx  

Output​

Upon processing the data and output, following output is obtained.

 page-break-tag-output.docx  

Conditional Page Break​

This is more of a trick, in which you can manually insert a page break in the template and enclose it within a condition. The page break will be included only if the condition evaluates to true; otherwise, it will be omitted.

Example​

Data Source​

Hereby examples of data source for different options.

select
  'file1' as "filename",
  cursor(
    select
      cursor(
          select
          c.cust_first_name as "cust_first_name",
          c.cust_last_name as "cust_last_name"
          from aop_sample_customers c where c.customer_id < 4
      ) as "customers"
      from dual
) as "data"
from dual

Template​

The provided template demonstrates the implementation of a conditional page break, where a page break is inserted for each customer. The page break is placed within a condition that specifies it should be inserted until reaching the last index of the customer's cursor. In other words, the page break will not be inserted on the last page.

 page-break-conditional-template.docx  

Output​

Upon processing the data and template using AOP, the ouput obtained as follows:

The complete output is available here:  page-break-conditional-output.docx  

Column Break Tag​

This tag can be used to introduce a column break in the template if there are multiple columns available. This tag starts with a question mark sign and the name of the column that holds the value 'columnbreak'.

Conditional Expression

If you want to make this tag conditional, you can use expressions for example: {?(customers.length - 1 !== current_child_index) && "columnbreak"}. Here the first part is the condition and the second part will return "columnbreak". This means the retruning value or tag value will be "columnbreak" when the condition succeeds and false otherwise.

Example​

Data Source​

Hereby examples of data source for different options.

select 'file1' as "filename",
       cursor (
         select cursor (
           select product_id                                      as "p_id",
                  product_name                                    as "prod_name",
                  product_description                             as "prod_desc",
                  category                                        as "category",
                  product_avail                                   as "prod_avai",
                  list_price                                      as "price",
                  apex_web_service.blob2clobbase64(product_image) as "product_image",
                  20                                              as "PRODUCT_IMAGE_max_width",
                  20                                              as "PRODUCT_IMAGE_max_height"
           from aop_sample_product_info
           where product_id < 7
         ) as "products"
       )       as "data"
from dual

Template​

The template consist of three column, in which the info of products are to be shown in each column. Here {?"columnbreak"} is used to break the column for each product.

 column-break-template.docx  

Output​

Upon processing the template and data, the following output is obtained.

 column-break-output.docx  

Watermark Tag​

Available From: v21.2.1

AOP allows insertion of native text watermarks by using the {watermark data} tag in the header of the document, where the data field determines the watermark text.
Using section breaks to produce different headers allows the specification of watermarks on a per-page basis.

An angular expression can be used instead of a data reference, e.g. {watermark "my watermark"} will produce a watermark with text my watermark, but this does not allow for the specification of options. Options can be specified by appending _optionName to the data field containing the watermark text. The supported options are:

OptionDescriptionAllowedDefault
_colorwatermark text colorCSS notation"silver"
_fontwatermark text fontfont nameCalibri
_heightheight to scale watermark text tosize in px, pt, in, cm or emautomatically determined by AOP
_opacityopacity of the watermark textdecimal number between 0 and 11
_rotationrotation of the watermark textan integer, interpreted in degreescalculated to lie along the bottom-left to top-right diagonal
_widthwidth to scale watermark text tosize in px, pt, in, cm or emautomatically determined by AOP

The rotation angle is oriented counterclockwise from the horizontal axis: watermark_angle

Example​

Data Source​

Hearby the example data sources for different options. 

select 'file1' as "filename",
       cursor (
         select 'sample watermark' as "w_text",
                'green'            as "w_text_color",
                0.2                as "w_text_opacity",
                '-60'              as "w_text_rotation",
                'Comic Sans MS'    as "w_text_font",
                '17cm'             as "w_text_width",
                '3cm'              as "w_text_height"
         from dual
       )       as "data"
from dual

Template​

The template contains the watermark tag {watermark w_text} in the header as shown in below:

 watermark-template.docx  

Output​

With the data source and template provided above, AOP gives the output as shown below.

 watermark-output.docx  

Markdown Tag​

Available From: v3.0

Markdown styled text can be converted to docx file format. The syntax for this tag is {_key_}. The "key" tag holds the Markdown content in the JSON file. To get a better idea about the basics of Markdown styling syntax, this documentation will be helpful. For extended Markdown styling syntax this documentation will be helpful.

From the basic syntax documentation, we are supporting:

From the extended syntax documentation, we are supporting:

Example​

Data Source​

Hearby the example data sources for different options.

select 'file1' as "filename",
       cursor (
         select nvl(:p172_markdown, 'no apex session') as "markdowncontent",
                cursor (
                  select cust_first_name as "first",
                         '**'
                         || cust_first_name
                         || '**'         as "cust_name_bold"
                  from aop_sample_customers
                )                                      as "cust_names"
         from dual
       )       as "data"
from dual

The :p172_markdown region used in the sql contains the following markdown contains:

# Heading level 1

## Heading level 2

===============

I just love **bold text**.  

Italicized text is the *cat's meow*.

1. First item
2. Second item
3. Third item
4. Fourth item

---

* First item
* Second item
* Third item
* Fourth item

| Syntax    | Description |
| --------- | ----------- |
| Header    | Title       |
| Paragraph | Text        |

<strike>The world is flat.</strike> We now know that the world is round.

Template​

The template contains {_markdowncontent_} for the markdown from the region and {_cust_name_bold_} for the customer name returned as markdown from table.

 markdown-template.docx  

Output​

The given template and data when process by AOP gives the following output.

 markdown-output.docx  

Raw Tag​

The raw tag is a special tag that allows you to insert raw xml that specifies the openxml content of docx format. This tag starts with @ sign followed by the column name that holds the value. For example {@raw}. This can be used for wide variety of purposes. This can for example be used to introduce a page break, different styling and so on.

Caution

Please be careful using this tag as the xml value that is inserted into the document is not validated. The place where this xml is inserted is inside the text element of the word run eleemnt of the document. Be sure to close this before applying any other tags and reopen it once done. See: http://officeopenxml.com/WPtext.php

Example​

Data Source​

Hereby examples of data source for different available options.

select 'file1' as "filename",
       cursor (
         select '<w:p>'
                || '<w:pPr>'
                || '<w:pStyle w:val="NormalWeb"/>'
                || '<w:spacing w:before="120" w:after="120"/>'
                || '</w:pPr>'
                || '<w:r><w:rPr><w:b/><w:i/></w:rPr>'
                || '<w:t>'
                || cust_first_name
                || '</w:t>'
                || '</w:r>'
                || '</w:p>' as "bold"
         from aop_sample_customers
         where customer_id = 1
       )       as "data"
from dual

Template​

In the above code, the xml is inside the bold column. The template becomes: {@bold}

 raw-tag-template.docx  

Output​

The given data source and template gives the following output.

 raw-tag-output.docx  

Available From: v3.3

The hyperlink tag is a special tag that allows you to insert a hyperlink into the document. This tag starts with a * (asterisk) sign followed by the column name that holds the value. For example {*link} where link is the column name. You can also specify the text that should be displayed for the hyperlink. If none specified, the URL itself will be shown. AOP auto detects the URL and also supports mail hyperlinks.

Available options:

  • preserve_tag_style : The hyperlink will take the styling of hyperlink text defined in the template (blue and underlined by default). Enabling this option will take the styling of the tag instead of default styling of hyperlink. Values(true or false). It is false by default.

Example​

Data Source​

Hereby examples of data source for different options.

select 'file1' as "filename",
       cursor (
         select cust_first_name as "cust_first_name",
                'https://www.apexofficeprint.com' as "website",
                'United Codes'                    as "website_text",
                'support@apexofficeprint.com'     as "mail"
         from aop_sample_customers where customer_id = 2
       )       as "data"
from dual

Template​

In the above data source, the links are inside website and mail column name. The tags to use become: {*website} and {*mail}

Our template looks like: hyperlink template

Please download the template below to have a look at the template:  template.docx  

Output​

The output is as follows:

hyperlink output

You can also download the output to check it out:  output.docx  

The hyperlink tag expects single hyperlink content to be provided in the data source. The auto hyperlink tag will automatically detect URLs and mail addresses and convert them to hyperlinks from one single value. The auto tag starts with *auto followed by space and the column name that holds the value. eg {*auto textWithLink}.

Available options:

  • preserve_tag_style :The hyperlinks in the text will take the styling of hyperlink text defined in the template (blue and underlined by default). Enabling this option will take the styling of the tag instead of default styling of hyperlink. Values(true or false). It is false by default. Ex. (textWithLink_preserve_tag_style).

Example​

Data Source​

Hereby examples of data source for different options.

select 'file1' as "filename",
       cursor (
         select 'You can visit us at https://www.apexofficeprint.com or mail us at support@apexofficeprint.com' as "contact"
         from dual
       )       as "data"
from dual;

Template​

In the above data source, the link containing text is inside contact column. The tag to use becomes: {*auto contact}

The template looks as follows:

hyperlink template

Please download the template below to have a look at the template:  template.docx  

Output​

The output is as follows:

hyperlink output

You can also download the output to check it out:  output.docx  

Available From: v21.2This tag allows you to place a link to a target inside the same document. To point a link tag to its target both of these tags should have same unique id (uid). If the uid is not provided, a new uid will be generated uniquely for every different link and target pair having the same value after the space in the tag.

{link name:uid} is a tag where a link will be placed. The tag itself will be replaced by the value of key after the space, in this case the value of name. This can also be for example: {link "Click here":1}. This will link or redirect to the section where a target tag is placed with the same uid. Multiple link tag may target one target tag. But one link tag cannot target multiple target tags.

{target name:uid} is a tag where a bookmark will be placed and is thus the target of link tag having same unique id. The replacing value will be determined by the key after space, in this case, name. This is a variable and can be for example: {target "To go here":1}.

Example​

Data Source​

Hearby the example data sources for different options.

select 'file1' as "filename",
       cursor (
         select cursor (
           select c.customer_id         as "id",
                  c.customer_id * 91509 as "invoicenumber",
                  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"
                    from aop_sample_orders o
                    where c.customer_id = o.customer_id
                  )                     "orders"
           from aop_sample_customers c
           where c.customer_id < 4
         ) as "invoice"
         from dual
       )       as "data"
from dual

Template​

The template shows the link of invoices from first page of the document to the targeted invoice page.

 template.docx  

tip

Here the uid number is not really necessary as the our replacing value is already unique. So our pairs could have been {link invoicenumber} and {target invoicenumber}.

Output​

Upon processing the provided data and template, the resulting outcome is as follows. By clicking on the invoice number located on the first page, it redirects to the corresponding invoice page.

 output.docx  

Table Of Content Tag​

Deprecated

This tag is going to be deprecated in the future. Please insert the table of content in the template itself. With the use of macros, you can update the page numbers using pre conversion commands. Please see server preconversion command configuration.

Available From: v3.5

To create a table of contents, a tag starting with a ~ character followed by the name of the column that holds the options is used. By placing the {~tockey} tag, a table of contents will be generated at the location where the tag is inserted.

Furthermore, since AOP version 18.2.3, users are now able to customize the table of content, which provides greater flexibility and control when creating documents.The following options are available:

  • title : The title to show at the top of table of content. Deafult "Contents".
  • show_level: The level of heading to show in the table of content. Default 3
  • tab_leader: the character to use after providing the headings. default '.', can be either ".", "_", "-".
Page Numbers

Please note that AOP itself does not render the content, thus the page numbers will not be updated. If your output type docx, you can update the page number after opening.

If your output type is PD and are using MS Office as PDF converter, the table of content will be automatically updated with page number. Please ntoe that this is only available after AOP 22.2. Alternatively, if you are using a prior version, you will need to configure a custom converter. See server section for more information.

If your output type is PDF and are using Libreoffice as PDF converter, the table of content might not be updated with the page number. You can use 'soffice-macro' pdf converter to convert the document to PDF. You can specify the converter for this specific request in the init PLSQL section of the dymaic action or providing it to the AOP PLSQL API. 

aop_api_pkg.g_output_converter := 'soffice-macro';

Alternatively, you can also use the inbuilt table of content in Word.

Example​

Data Source​

Hearby the example data sources for different options. The templates contains toc_demo as the table of content key thus toc_demo_title, toc_demo_show_level and toc_demo_tab_leader for table of contents title, show level and tab leader in the sql.

select 'file1' as "filename",
       cursor (
         select cursor (
           select c.cust_first_name                    as "cust_f_name",
                  c.cust_last_name                     as "cust_l_name",
                  c.cust_city                          as "city",
                  c.cust_street_address1               as "addr_1",
                  c.cust_street_address2               as "addr_2",
                  c.cust_state                         as "state",
                  c.cust_email                         as "email",
                  c.phone_number1                      as "phone_number_1",
                  c.phone_number2                      as "phone_number_2",
                  c.url                                as "url"
           from aop_sample_customers c
         )                                             as "cust_list",
                  '<h1>Heading 1 from html</h1><h2>Heading 2 from html</h2><h3>Heading 3 from html</h3> AOP also handles the headings from html' as "sample_html",
                  'Table of Contents - Generated by AOP'as "toc_demo_title",
                  2                                    as "toc_demo_show_level",
                  '-'                                  as "toc_demo_tab_leader"
         from dual
       )       as "data"
from dual

Template​

In the above data source, the options are inside the key toc_demo. Thus the tag to use becomes:

{~toc_demo}

Example template can be downloaded from:  table-of-contents.docx  

Output​

The output will look like the following:

The complete output is available here :  table-of-contents.docx  

Footnote Tag​

Footnote tags are a way to add footnotes to your report. Footnotes are added to the bottom of the page and are numbered sequentially. The footnote tag starts with a + sign, followed by the column name that holds the value. For example {+footnote}

Example​

Data Source​

Hereby examples of data source for different available options.

select 'file1' as "filename",
       cursor (
         select 'Only I can change my life. No one can do it for me.' as "quote",
                'Carol Burnett'                                       as "person"
         from dual
       )       as "data"
from dual;

Template​

In the above data source we want the person to be the footnote and the quote should be the part of document. We use normal substitution tag for quote and footnote tag for person. The template becomes:

Footnote Template

 footnote.docx  

Output​

The expected output is as follows: FootnoteResult

 footnote-output.docx  

Labels​

The label tag allows you to do mail merging with the Word template. The labels tag starts with a minus sign followed by the name of the cursor that holds the records. In order to perform mail merging, create a document with labels by going to Mailings options and then to Labels. Fill in the tags in the address field and choose the type of label in the Label option. A document can then be generated by clicking New document. Currently however if labels are getting printed then we expect the document only containing labels and no other information, and that the tag keys are not used more than once.

Tag placement

Note that in the first cell you need to add the {-labels} tag.

Caution

This mail merge option is very simple. If you want to include conditions, images, barcodes or qrcodes, please make a custom table with the right margin and use loop tags.

Example​

Data Source​

Hereby examples of data source for different options.

select 'file1' as "filename",
       cursor (
         select cursor (
           select 'Mr'                      as "Title",
                  cust_first_name           as "FirstName",
                  cust_last_name            as "LastName",
                  'Developer'               as "JobTitle",
                  'APEX RnD'                as "Company",
                  cust_street_address1      as "Address1",
                  cust_street_address2      as "Address2",
                  cust_city                 as "City",
                  cust_state                as "State",
                  to_char(cust_postal_code) as "PostalCode",
                  'US'                      as "Country",
                  phone_number1             as "HomePhone",
                  phone_number2             as "WorkPhone"
           from aop_sample_customers
           order by 1,
                    2
         ) as "labels"
         from dual
       )       as "data"
from dual

Template​

Please have a look at the template below, you can follow the procedure here: https://www.youtube.com/watch?v=_gGNXhPNfdk

 labels-template.docx  

Output​

The output will look as follows:

 labels-output.docx  

Example using Loop Tag​

For the insertion of images, barcodes, qrcodes and for custom styling, you can use loop tag for the labels. This examples shows the application of loop tag inside table to generate the Lables lookalike output including images.

Data Source​

Hereby examples of data source for different options.

select 'file1' as "filename",
       cursor (
         select cursor (
           select 'Mr'                      as "Title",
                  cust_first_name           as "FirstName",
                  cust_last_name            as "LastName",
                  'Developer'               as "JobTitle",
                  'APEX RnD'                as "Company",
                  cust_street_address1      as "Address1",
                  cust_street_address2      as "Address2",
                  cust_city                 as "City",
                  cust_state                as "State",
                  to_char(cust_postal_code) as "PostalCode",
                  'US'                      as "Country",
                  phone_number1             as "HomePhone",
                  phone_number2             as "WorkPhone",
                  'https://api.apexofficeprint.com/logo-aop.png' as "img",
                  '100px'                   as "img_height",
                  'true'                    as "img_maintain_aspect_ratio",
                  "square"                  as "img_text_wrap
           from aop_sample_customers
           order by 1,
                    2
         ) as "labels"
         from dual
       )       as "data"
from dual

Template​

In the provided template, a table is first created using the labels in Microsoft Word's Label Options. This allows for the incorporation of table properties from the chosen Product Number in Label Options. Following this, data tags are inserted within the table, formatted as needed.

Additional columns can be added based on the requirement. Depending on the number of columns, corresponding tags and conditions should be included, as demonstrated in the template below.

 labels-using-loop-template.docx  

Output​

The output will look as follows:

 labels-using-loop-output.docx  

Form Tag​

Available From: v20.3

The form tag allows users to create froms in PDF documents. The form tag starts with ?form string, followed by the name of the column that holds the value and options. AOP currently allows generation of three types of form elements:

  • textboxes
  • radio buttons
  • check boxes.

If you are looking to generate a signature field, please have a look here. TODO: Link

Note That

The output should be PDF when form tag is used, otherwise an error will be thrown.

Form tag options​

All three types of form elements have common fields that must be provided in order to create them.

  • type (required): Indicates which type of form element to be created. Options are text, radio, and checkbox.
  • name (required): Unique ID of a form element.
  • height (optional): User defined height of a form element. If not specified automatically assigned.
  • width (optional): User defined width of a form element. If not specified automatically assigned.
  • value (optional): The default value a certain form element will have.
  • text (optional): A label for certain form elements.
  • selected (optional): Boolean value (true/false) that checks or unchecks radio button elements.

Only form elements fields indicated with required, height and width are common for every element. The remaining elements are discussed in each form elements section below.

Textboxes​

To create a textbox form element in a PDF Form, the following fields must be included in the data :

  • type (required)
  • name (required)
  • value (optional)
  • height (optional)
  • width (optional)

Data Source

SELECT
    'file1' AS "filename",
    CURSOR (
        SELECT
            CURSOR (
                SELECT
                    'text'       AS "type",
                    ''           AS "value",
                    'first_name' AS "name"
                FROM
                    dual
            ) AS "first_name",
            CURSOR (
                SELECT
                    'text'      AS "type",
                    'Apex R&D'  AS "value",
                    'last_name' AS "name",
                    '20'        AS "height",
                    '200'       AS "width"
                FROM
                    dual
            ) AS "last_name"
        FROM
            dual
    )       AS "data"
FROM
    dual;

Template

The template consists of a textbox element with the name first_name and a textbox element with the name last_name, with the form tag represented by

    Name: {?form first_name}   Surname: {?form last_name}

 template.docx  

Output

It will result in two textbox elements in the PDF Form, one with the name first_name and one with the name last_name. The textbox element with the name first_name will be empty with default size, while the textbox element with the name last_name will have the value Apex R&D and will have a height of 20 and a width of 200.

textboxresult

 output.pdf  

Radio Buttons​

To create a radio button element, radio type can be used. Extra options for radio type are as follows:

  • type (required)
  • name (required)
  • heigth (optional)
  • width (optional)
  • value (optional)
  • text (optional)
  • selected (optional)

There are two ways to create a radio button element. The first way is to create a single radio button element by Referencing. The second way is to create multiple radio button elements by Looping. Multiple radio buttons or Radiolists are grouped together as a single unit, and only one radio button can be selected at a time.

Looping​

This technique is used to create a group of radio buttons that are interconnected to each other. A list of items are expected with options for radio type.

SELECT
    'file1' AS "filename",
    CURSOR (
        SELECT
            CURSOR (
                SELECT
                    'radio'         AS "type",
                    'List A'        AS "value",
                    'Radiolist'     AS "name",
                    'List Option A' AS "text",
                    1               AS "selected"
                FROM
                    dual
                UNION ALL
                SELECT
                    'radio'         AS "type",
                    'List B'        AS "value",
                    'Radiolist'     AS "name",
                    'List Option B' AS "text",
                    1               AS "selected"
                FROM
                    dual
            ) AS "radiolist"
        FROM
            dual
    )       AS "data"
FROM
    dual;

and the template as following picture:

radiotemplate

Result will look like this:

radiotemplateresult

Referencing​

This technique is used to create a single radio button element. The radio button element is referenced by the name of the radio button element. If multiple radio element are defined, they are not interconnected and will behave as an independent element.

SELECT
'pdf_form' AS "filename",
CURSOR ( SELECT
    CURSOR (
        SELECT
            CURSOR (
                SELECT
                    'radio'    AS "type",
                    'Radio'    AS "name",
                    'A'        AS "value",
                    'Option A' AS "text",
                    15         AS "height",
                    100        AS "width"
                FROM
                    dual
            ) AS "a",
            CURSOR (
                SELECT
                    'radio'    AS "type",
                    'Radio'    AS "name",
                    'B'        AS "value",
                    'Option B' AS "text",
                    15         AS "height",
                    100        AS "width"
                FROM
                    dual
            ) AS "b"
        FROM
            dual
    ) AS "radio",
FROM
dual
) AS "data" FROM dual;

and template :

    {?form radio[0].a}
    {?form radio[0].b}

Result will look like this:

radiotemplateresult

 template.docx    output.pdf  

Checkboxes​

The checkbox format generates a from woth checkboxes. It has several other options such as :

  • type (required)
  • name (required)
  • heigth (optional)
  • width (optional)
  • value (optional)
  • text (optional)

In this case, the value field can only accept a boolean value, which sets the default state of the checkbox as either checked or unchecked.

Given the data below:

SELECT
    'file1' AS "filename",
    CURSOR (
        SELECT
            CURSOR (
                SELECT
                    'checkbox'  AS "type",
                    1           AS "value",
                    'Checkbox'  AS "name",
                    'IsChecked' AS "text",
                    20          AS "height",
                    200         AS "width"
                FROM
                    dual
            ) AS "checkbox"
        FROM
            dual
    )       AS "data"
FROM
    dual;

Template

{?form checkbox}

Output

checkboxresult

 template.docx    output.pdf  

Attach Tag​

Available From: v22.2

AOP offers a feature that allows the attachment of Word, Excel, PowerPoint, and PDF files within a Word document. To utilize the attach tag, you must include "?insert" followed by the column name and enclose them with delimiters like {?insert fileToInsert}. The column_name (in this case, fileToInsert) should return a base64 encoded string.

IMPORTANT NOTE

PDF and PowerPoint embedding will not work when the output file is opened in macOS. This is due to the limitation of Microsoft Office/ Libreoffice itself.

Example​

Data Source​

Here are the different options for example data sources, where templates in DOCX, PPTX, and XLSX formats are selected from the static application files.

select 'file1' as "filename",
      cursor (
        select (
          select apex_web_service.blob2clobbase64(file_content)
          from apex_application_static_files
          where application_id = :app_id
                and file_name = 'angular_expressions.docx'
          ) as "fileToInsert1",
          (
            select apex_web_service.blob2clobbase64(file_content)
            from apex_application_static_files
            where application_id = :app_id
                  and file_name = 'aop_classic_template.xlsx'
          ) as "fileToInsert2",
          (
            select apex_web_service.blob2clobbase64(file_content)
            from apex_application_static_files
            where application_id = :app_id
                  and file_name = 'aop_d3_template.pptx'
          ) as "fileToInsert3"
      )       as "data"
from dual

Template​

The templates contain three {?insert fileToInsert} tags, which will be replaced by their respective files returned by the data source.

 attach-tag.docx  

Output​

Upon processing the provided template and data source, the following output is obtained.

 output.docx  

Embed Tag​

Available From: v22.2

AOP provides the ability to include the contents of another Word file using the Embed tag. To accomplish this, you need to include "?embed" followed by the column name, enclosed within delimiters like "{?embed fileToInsert}". The fileToInsert should contain either the Base64 encoded representation of the file or the path to the document (FTP, SFTP, or URL). Please note that only Word files can be embedded using this feature.

Example​

Data Source​

Here are the different options for example data sources, where base64encoded string of sample DOCX template is returned from the table aop_template table from Sample Application.

select 'file1' as "filename",
       cursor (
         select (
           select apex_web_service.blob2clobbase64(template_blob)
           from aop_template
           where id = 1
         ) as "fileToEmbed"
       )       as "data"
from dual

Template​

The templates contains the {?embed fieToInsert} which will be replaced by the contents of the file returned from the column fileToEmbed.

 embed-tag-template.docx  

Output​

Upon processing the provided template and data source, the following output is obtained.

 embed-tag-output.docx  

Word Subtemplating​

Available From: v18.1

AOP allows the reference of other templates within the Word template. The referenced templates is known as a sub-template. The goal of sub-templates is to make it easier for you to reuse common templates in different files. To reference a sub-template in the Word template, you use the syntax ?include followed by the filename, like {?include Subtemplate}.

To include the sub-template(s), you need to set the g_sub_templates_sql variable. In the Init PL/SQL section add for example: 

aop_api_pkg.g_sub_templates_sql := q'[select filename, mime_type, template_blob as file_blob from aop_template where filename like 'SUB-TEMPLATE' order by filename]';

This tag can be used inside loops, and tags inside the subtemplate will also be replaced.

For example:

{#products}
Product {name}
    {?include Subtemplate1}

{/products}

Should the subtemplate just consist of the content Amount: {amount}, then this could be an example result:

Product Product1
    Amount: 5

Product Product2
    Amount: 36

From AOPv22.1.4, It is possible to include a subtemplate from another subtemplate.

For example: If there are two subtemplates with the name Subtemplate1 and Subtemplate2, then tag {?include Subtemplate2} can be used in the template Subtemplate1 to include the content of Subtemplate2 or vice versa.

Note: Maximum circular references is limited to 10.

Subtemplating is only available for Word Templates

This tag is currently only available inside Word templates, and the subtemplates can currently only be Word documents (templates) too.

Example​

Data Source​

Hereby examples of data source for different options.

select
    'file1' as "filename",
    cursor
    (select 
         'John' as "firstname",
         'Doe' as "lastname",
          'https://d2x5ku95bkycr3.cloudfront.net/App_Themes/Common/images/profile/0_200.png' as "image",
          200 as "image_height",
          200 as "image_width",
          "yes" as "includeConditionalTemplate"     
         from dual
    ) as "data"
from dual

Init PL/SQL for the subtemplate files.

aop_api_pkg.g_sub_templates_sql := q'[select filename, mime_type, blob_content as file_blob from apex_application_files where filename in ('subtemplate1.docx','subtemplate_conditional.docx') order by filename]';

Template​

The template and the subtemplate files are as shown below:

  • Template
    It contains the tags to include the subtemplate1 {?include subtemplate1} and the conditional subtemplate {?include subtemplate_conditional}.
    The subtemplate1 is always rendered in the output whereas the contents of subtemplate_conditional is only rendered if the condition {#includeConditionalTemplate=='yes'} is true.  template.docx  

  • Subtemplate1  subtemplate1.docx  

  • subtemplate_conditional  subtemplate_conditional.docx  

Output​

Upon processing the given data source, template and subtemplate files, Following output is obtained. For conditional subtemplate, the conditional statement includeConditionalTemplate=='yes' is true. Thus, the output also contains the contents of subtemplate_conditional.docx .

 output.docx  

Interactive Report​

AOP is capable of handling exported data from interactive reports, and users can obtain an interactive report by using the {&interactive} tag within a specific template. This tag will be replaced with the chosen interactive report in APEX.

Please visit Interactive Report of apex feature for detailed information.

Interactive Grid​

Using the interactive grid tag {&...&}, user can get the exported IG in the designated template. The tag consist of interactive grid static id, with & on both sides enclosed by the delimiters.
Like the Interactive reports, it also supports Highlights, Filters, Aggregates, Break, Charts and many more. For more detail on its uses, please refer to AOP Sample Application > APEX Features > Interactive Grid.

Please visit Interactive Grid of apex feature for detailed information.

Classic Report​

AOP also supports the data exported of classic reports. The tag is similar to interactive grid tag where static id, with & on both sides enclosed by the delimiters like {&crStaticID&}. Upon processing, the tag is replaced by the data from the classic report.

Please visit Classic Grid of apex feature for detailed information.