APEX Office Print (AOP) - User Manual

Release 3.0 - June 2017

1 About AOP

1.1 Goal

APEX Office Print makes printing and exporting (docx, xlsx, pptx, pdf, html, md) in Oracle Application Express (APEX) or just in PL/SQL a lot easier.

Make a template in Office, HTML or Markdown, choose data from your database and merge them into one. You can easily print PDFs, Office documents, HTML or Markdown in no time. It saves you time and effort by creating templates in which you can easily integrate your data.

APEX Office Print (AOP) is a product of APEX R&D, located in Leuven, Belgium.

We are experienced partner that helps you facilitate, improve and accelerate your business through new and innovative solutions. Built on in-depth technical expertise, our company provides custom-made IT services for managing business data and processes.

1.2 AOP Main Features

APEX Office Print focusses on following:

  1. Simple Printing
    It is fast, easy and saves you money. It's the only solution on the market that is fully integrated with APEX. For example you can print or export your Interactive Reports and Grids data in a second.

  2. Quick and Easy Setup
    Simply Import the APEX Plugin in your application

  3. Any Data Type Support
    Text, images, barcodes, formulas…. it is easy to merge your data with any template you create

  4. Scalable and Secure
    APEX Office Print is a scalable and secure product that fits your every printing requirement.

  5. PL/SQL API support
    It gives you ability to run and retrieve reports from AOP server directly from your PL/SQL code.

  6. Optimized for Performance
    Great design with finest details to enhance your business productivity.

  7. Flexible
    APEX Office Print is a print server allowing you to create templates in Word, Excel, and PowerPoint for any type of data.

1.3 AOP Architecture

It's the database (APEX Plugin, PL/SQL API, REST WebService) that is doing a request to the AOP Server (either on-premise or our cloud). The template together with the data is send in JSON format to the AOP Server. The browser doesn't need to have access to the AOP server.

1.4 How it works (overview)

1.5 How it works (technical)

The APEX Office Print package consists of a server component, an Oracle APEX plugin and PL/SQL API.

The server part returns generated documents upon receiving HTTP POST requests. These requests require a structured JSON file that for example can be inferred from a SQL database. The data from the database in combination with a template will generate relevant output files.

The Oracle Application Express (APEX) plugin will generate the HTTP POST requests that the server part understands. The plugin makes it easy to select a template, your data source and the output format straight from within your APEX application.

The PL/SQL API allows to print directly from the Oracle database. The PL/SQL API fits perfectly if you need to setup automatic printing through a job or do a mail merge.

A sample application comes with the download, so you see plenty of use cases in action.

1.6 Cloud vs On-Premise

There are two type of versions to buy:
- a cloud version, which will send the requests to APEX Office Print in the cloud
- an on-premise version, which includes the server component of APEX Office Print. It's an executable you run on your own server and in the APEX-Plugin you reference your own local version of APEX Office Print. In this case nothing is sent to the AOP cloud.

1.7 License

This is commercial software; you need to obtain a valid license in order to use this software and plugin in your application. For the on-premise version a license is required per server (MAC address) you are running APEX Office Print of or you can go for an Enterprise License which is not tight to the MAC address.

Please visit our website https://www.apexofficeprint.com/ for the different packages.

You can even sign up for a free trial to see AOP in action.

1.8 Signup and Download

  1. Open https://www.apexofficeprint.com/

  2. Click on the "Try it Free" or "Sign Up" button

  3. Enter your details

  4. You'll receive an email with a confirmation link, please click that

  5. At the same time you're logged into your AOP Dashboard. Select Downloads

  6. Choose the package you want and click to download it

Important – API key number activation

In your AOP dashboard your unique API key number can be found under the Account Info region on the home page. This number will need to be added to your Plugin settings in order for it to work. For more information on how to do this check the APEX Plugin Section.

1.9 Quick Install Guide

  1. Unzip the file you downloaded from https://www.apexofficeprint.com
  2. Run aop_db_pkg.sql (in the "db"-directory) in your Oracle Schema (SQL Workshop -> SQL Scripts -> Upload -> Select file and hit Upload)
  3. Import dynamic_action_plugin_be_apexrnd_aop_da.sql and process_type_plugin_be_apexrnd_aop.sql (in the "plugin"-directory) in Shared Components > Plugins
  4. On the page you want to use AOP, add the "APEX Office Print" Dynamic Action or Process

2 AOP APEX Sample Applicaiton

2.1 Prerequisites

APEX Office Print requires Oracle APEX 5.0.4 or higher. Interactive Grid support is included in Oracle APEX 5.1 and above.

In order to run the AOP Sample Application an Oracle APEX Packaged application called Sample Database Application has to be installed first. The reason is that the source and templates are using data from the tables (demo_orders, demo_order_items, demo_product_info, ….) which are installed by the packaged application.

Follow these steps to install the packaged application in your workspace:

  1. Access your target Workspace
  2. Select the Application Builder
  3. Select Create
  4. Select Packaged Application
  5. Select Sample Database Application and click Install Application button
  6. Select to use existing Application ID
    Note: When you selected Auto Reassign Application ID you need to make sure that you change the values of aop_api3_pkg that uses APP_ID and AOP_URL as global variables.
  7. Select to install Supporting Objects
  8. Follow the wizard to complete the installation.

2.2 Import the AOP Sample Application

  1. Access your target Workspace
  2. Select the Application Builder
  3. Select Import
  4. Follow the wizard to finish the import
  5. The AOP Sample Application will also install all supporting objects, so after the import you can simply start using the application.

2.3 Trying the AOP Sample Application

The APEX application that comes with AOP shows different examples of how APEX Office Print can be used inside an application.

By inspecting elements of these pages you can quickly learn how to use AOP Plugin.

Note that the AOP Sample Application for APEX 5.0 and 5.1 are different.

2.3.1 AOP Sample Application for APEX 5.0

AOP Sample application consist out of 19 parts:

  1. Sample Print

    This page contains different buttons all linked to different AOP processes and dynamic actions that show different use cases of the AOP plugin.

  2. Top Features

    This section highlights some of the key differentiators of APEX Office Print compared to other print solutions.

  3. Dynamic Print

    Dynamically you can select your template in Word, Excel or PowerPoint and output depending on the customer you selected.

  4. Reports Print

    This section shows examples where a classic and interactive report and grid is used as the source of the data. There is also an example to print labels. You can even use a combination of multiple classic and interactive reports as your data source. This feature is one of the most impressive of AOP and not found in any other tool.

  5. Chart Print

    This section shows examples how to print charts: Line, Bar, Column, Pie, Radar, Area, ... The charts are native Office charts, so they can even be adapted after creation in Word, Excel and Powerpoint.

  6. Rich Text Editor

    You can also write your markup in a Rich Text Editor and AOP will understand and translate the markup (bold, color, etc.) into native Word markup. In your template you need to put an underscore in front of the tag name e.g. {_htmlcontent}

  7. Images

    APEX Office Print is able to print images in different formats. This screen shows an example how to configure it. In order to show images, you base64 encode those and put a % in your tag e.g. {%image}

  8. QR and Barcodes

    APEX Office Print is able to print different Bar- and QR codes. This screen shows an example how to configure it.

  9. Inline PDF

    If you want to show the PDF inline in a modal dialog or in a certain div (region), this page shows exactly how to do that.

  10. Complex Print

    The Complex Print section shows different use cases for Word, Excel and Powerpoint. There are different templates that show how to create more complex layouts. There are also some examples that show some specific features like how to use line breaks in your data or how to download two documents at the same time.

  11. Mailings Print

    Word has a feature to create labels via the mailings option. This screen shows an example with some specific Avery size labels.

  12. Email Print

    This examples shows how you can call a procedure and send an email as attachment. There is also an option to send the email but also downloading the file.

  13. PL/SQL API Sample

    Here you can see AOP PL/SQL API in action. It shows how to retrieve a document from AOP server and store it in custom table. This is extremely useful when you want to schedule your print jobs and send automated emails.

  14. Scheduled Print

    You can schedule a job to print every day, week, month etc. a specific report by using dbms_scheduler and the AOP PL/SQL API.

  15. Batch Print

    If you want to print documents in batch, this page shows you three ways of doing it.

    First one will generate one document with the pages in here.
    Second will generate multiple documents.
    And last option will generate multiple documents but present it back as a zip file.

  16. Template and Data Types

    This page gives an overview and example of the different template and data types that are possible in the APEX Office Print (AOP) Plugin and API. The template type defines where your template in docx, xlsx or pptx is defined (table, filesystem, url, APEX, ...). The data type defines where the data can be found to be merged with the template (SQL, PL/SQL, JSON, REST, ...).

  17. AOP Usage

    This page gives an overview of the pages where and how the APEX Office Print (AOP) Plugin is used.

  18. Automated Test

    You can automatically test all your documents in this screen and see if all of your defined templates and data sources merged correctly. This option is especially useful after an upgrade of AOP or Office.

  19. Templates

    Here you can upload your templates into a custom table.

    The idea here is that in your own application, you allow people to create or update their own templates and upload them to the application without the need for developer to code other documents.

2.3.2 AOP Sample Application for APEX 5.1

For Oracle Application Express (APEX) 5.1 we wanted to take the AOP Sample App to another level and include more insight how AOP is working. We know reading documentation isn't the most fun, so we put a lot of information into the sample app, so by navigating through it you learn about AOP.

The different parts of the sample app:

  1. Start walks you step-by-step how you can get AOP working in your own application.

  2. Tour shows the different techniques you can use and goes in more detail when to use which plugin, the PL/SQL API or do a manual REST request.

  3. Examples shows different use cases of AOP, so you can see exactly behind the scenes how AOP is used.

  4. Doc is a link to the latest version of our online documentation.

  5. More is a link to our website where you find the release history, FAQ, Tutorials etc.

  6. Support will open your default email client so you can write us.

3 APEX Plugin

3.1 Pre-requisites

The APEX Office Print APEX plugin requires Oracle APEX 5.0.4 or higher.

The AOP plug-ins are relying on a PL/SQL package (aop_api3_pkg) that needs to be compiled first. The plugin itself can only hold 32k of code, and AOP contains more than double since v2.2.

To make the package available, go into SQL Plus, SQLcl, SQL Developer or SQL Workshop and run the SQL Script aop_db_pkg.sql which you find in the db directory of the zip you downloaded.

Alternatively you can run the install.sql file which will install some additional packages which show case how to use AOP from PL/SQL.

If you imported the Sample App, the packages are automatically installed by the Supporting Objects.

3.2 Import and Plugin Attributes

Follow these steps to import the plugin in your application:

  1. Access your target Workspace
  2. Select the Application Builder
  3. Select the Application where you wish to import the plug-in > (plug-ins belong to an application, not a workspace)
  4. Access Shared Components > Plug-Ins
  5. Click Import >
  6. Browse and locate the installer file (plugin/process_type_plugin_be_apexrnd_aop.sql)
  7. Complete the wizard.
  8. Do the same for the dynamic action plugin

Follow these steps to finish your Plugin installation by adding your Plugin API key:

  1. Login into AOP Application from step 5.1
  2. Copy your API key from Account Info region
  3. Access your target APEX Workspace
  4. Select the Application Builder
  5. Select the Application where you wish to import the plug-in (plug-ins belong to an application, not a workspace)
  6. Access Shared Components > Plug-Ins
  7. Click Other Components > Component Settings
  8. Here select APEX Office Print (AOP) [Plug-in]
  9. Replace the current API key value with the AOP API key that you copied in step 9
  10. Click Apply
  11. Installation is now completed

Installation note:
To use AOP PL/SQL API you may need to configure your ACL (Active Control List) settings to allow access to:
http(s)://www.apexofficeprint.com/api

For more details please refer to Oracle APEX installation guide: https://docs.oracle.com/cd/E59726_01/install.50/e39144/listener.htm#HTMIG29160

In case you want to update some plugin settings, you find the plugin settings in Shared Components > Component Settings > APEX Office Print (AOP) [Plug-in]

You can change the AOP URL, API Key, Remote Debug. If you use the on-premise version of AOP you can also change the Converter.

To generate a PDF, APEX Office Print is using an external converter. By default LibreOffice is used (available on all platforms), but this can be changed to MS Office (Windows only), or Abiword (Linux only).

3.3 How to use the AOP Plugin

Simply add a new process called AOP (plugin) on your APEX page once you completed steps in 3.2.

3.4 Quick Start Demo

Once the plugin has been imported into your APEX 5 application navigate to the Sample Database Application – Page 4.

1) Create a new button called PRINT and place it right of the RESET button.

2) Create a new process (APEX 5.0) or dynamic action (APEX 5.1) called AOP and select as type AOP [Plug-in]

3) Set the Data Type option of the plugin to SQL and paste following select in the Data Source:

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  
              demo_order_items i, demo_product_info p  
            where  
              o.order_id = i.order_id  
              and i.product_id = p.product_id  
                ) "product"  
        from  
          demo_orders o  
        where  
          c.customer_id = o.customer_id  
            ) "orders"  
    from  
      demo_customers c  
    where  
      customer_id = 1  
        ) as "data"  
from dual

4) As Output Type select docx.

5) Add as Condition for the process set only to run when button clicked PRINT.

6) Hit Save and Run the page.

When clicking on the Print button you should now get your first Word document.
This document represents a starting template based on your data. You'll see all tags that you can use and some more explanations how to use expressions and certain features of AOP. You can now create your template in MS Word (or Excel or Powerpoint) and use the tags in your template. Follow the next steps to do this.

7) Create your template in Word and use the tags that came in the first document you got.

8) Go to Shared Components > Static Application Files and upload your template

9) Go back to your page and select the AOP process

10) Change the Template Type to "Static Application Files"

11) In Template Source enter your document you uploaded in step 8 e.g. my_template.docx

12) Run your page again

When clicking on the Print button you should now get your Word document based on your template with the tags replaced by the data coming from the database.

3.5 Plugin options

Since AOP v2.3 there are two plugins included: a process type plugin and a dynamic action plugin. If you are using APEX 5.1, the Dynamic Action plugin will always work, whereas the Process plugin only works if the “Reload on Submit” attribute (of the page) is set to “Always” (note this attribute is new in 5.1). This is due to a change how APEX 5.1 is handling Page Processing. If you would import an APEX 5.0 app in 5.1 by default it’s set to Always reload on submit, but if you create a brand new app in 5.1 it’s set to “Only for Success” and then the process plugin is not working.

There are different configuration options that can be used in AOP.

In subsections to follow you can find more details for each of them.

3.5.1 Template Type

It stands for what will the source of your templates be.

AOP Plugin allows you to:

AOP Template

Will generate a Word document with a starting template based on the data (JSON) that is submitted.

Static Application Files

Will reference files you find in Shared Components > Static Application Files

Static Workspace Files

Will reference files you find in Shared Components > Static Workspace Files

SQL

Query that returns two columns: template_type and file (in this order)

  • template_type can be: docx,xlsx,pptx

  • while file column supports: clob base64 of the blob (hint: use apex_web_service.blob2clobbase64 to convert blob to clob).

PL/SQL Function (returning JSON)

Return JSON object with following format:

{  
"file":"clob base64 data",  
"template_type":"docx,xlsx,pptx"  
}

Filename (with path relative to AOP server)

Enter the path and filename of the template which is stored on the same server AOP is running at.

URL (returning file)

Enter the url to your template in docx, xlsx or pptx.

3.5.2 Template Source

All types except default AOP Template type will allow you to configure this setting. It can represent a name of your file or contain code for your SQL/PLSQL.

Some Template Source examples can be found here:

Static Application Files Example

aop_simple_letter.docx

Static Workspace Files Example

aop_simple_letter.docx

SQL Example

select 
  a.TEMPLATE_TYPE as template_type,
  apex_web_service.blob2clobbase64(a.TEMPLATE_BLOB) as file
from 
  aop_template a
where id = 1

PL/SQL Function (returning JSON) example

declare
  l_return clob;

begin
  l_return := '"file": "",' || ' "template_type": "docx"';
  return l_return;
end;

declare
  l_return clob;
  l_template clob;
  l_template_type aop_template.template_type%type;

begin
  select 
    template_type, 
    apex_web_service.blob2clobbase64(template_blob) template
  into 
    l_template_type, l_template
  from 
    aop_template
  where 
    id = :p4_template;
  l_return := '"file": "' || replace(l_template,'"', '\u0022') || '",' 
    || ' "template_type": "' || replace(l_template_type,'"', '\u0022') || '"';
  return l_return;
end;

Filename (with path relative to AOP server) example

aop_template.docx

Note: Filename supports Substitutions like Application Items, Page Items and System Variables

URL (returning file)

http://apexofficeprint.com/templates/aop_template_d01.docx

3.5.3 Data Type

Data Type states what kind of data source you will be using.

Available options include:

SQL

Select statement in which you can use cursor to do nested records. Use "" as alias column names to force lower case column names.

PL/SQL Function (returning SQL)

Return a select statement as with SQL.

PL/SQL Function (returning JSON)

Return JSON defined in URL example above.

URL (returning JSON)

Source should point to URL that returns JSON object with following format: { "filename": "file1", "data":[{...}] } If the URL is using an APEX ORDS REST call it will automatically be wrapped with additional JSON: {"items":[...]} All of this will be handled for you by AOP Plugin.

Region(s): Classic and/or Interactive Report(s) and/or Interactive Grid(s) and/or Charts

One or more classic and/or interactive reports and/or interactive grids will be used as the source for your data. The Dynamic Action plugin also has the possibility to take a screenshot of for example a JET chart or any other div (svg/canvas).

Note that you can use bind variables like :PX_ITEM as well in your SQL-statement. You find examples when you click on Help in the APEX Plugin when you're on the Data Source field

3.5.4 Data Source

Depends on selected Data Type and it can be:

SQL

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 
              demo_order_items i, demo_product_info p
            where 
              o.order_id = i.order_id
              and i.product_id = p.product_id
                ) "product"
        from 
          demo_orders o
        where 
          c.customer_id = o.customer_id
            ) "orders"
    from 
      demo_customers c
    where 
      customer_id = :P4_CUSTOMER_ID
        ) as "data"
from dual

PL/SQL Function (returning SQL)

declare
  l_return clob;
begin  
  l_return := q'[
                    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 
                                  demo_order_items i, demo_product_info p
                                where 
                                  o.order_id = i.order_id
                                  and i.product_id = p.product_id
                                    ) "product"
                            from 
                              demo_orders o
                            where 
                              c.customer_id = o.customer_id
                                ) "orders"
                        from 
                          demo_customers c
                        where 
                          customer_id = :P4_CUSTOMER_ID
                            ) as "data"
                    from dual  
  ]';
  return l_return;
 end;

PL/SQL Function (returning JSON)

declare
  l_cursor sys_refcursor;
  l_return clob;
begin
  apex_json.initialize_clob_output(dbms_lob.call, true, 2) ;
  open l_cursor for
    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
                 demo_order_items i,
                 demo_product_info p
               where
                 o.order_id = i.order_id
                 and i.product_id = p.product_id
               ) "product"
            from
              demo_orders o
            where
              c.customer_id = o.customer_id
                and rownum < 2
           )"orders"
         from
           demo_customers c
         where
           customer_id = :P4_CUSTOMER_ID
        ) as "data"
    from dual;
  apex_json.write(l_cursor);
  l_return := apex_json.get_clob_output;
  return l_return;
 end;

URL with ORDS RESTful Web Service

For this you need to create a new Webservice following these steps:

  1. Open SQL Workshop -> RESTful services.

    Click Create button and follow the wizard. For RESTful Services Module set

    Name can be of your preference.

  2. For Resource Template enter:

  3. For Resource Handler enter:

    The statement we used in this example is:

select
  file1' as "filename",
  cursor(
    select
      c.cust_first_name,
      c.cust_last_name,
      c.cust_city,
      cursor(
        select 
          o.order_total, 
          'Order ' || rownum as order_name,
          cursor(
            select 
              p.product_name, 
              i.quantity, 
              i.unit_price,
              APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image
            from 
              demo_order_items i,
              demo_product_info p
            where 
              o.order_id = i.order_id
              and i.product_id = p.product_id
                ) product
        from
          demo_orders o
        where 
          c.customer_id = o.customer_id
          and rownum < 2
            ) orders
    from 
      demo_customers c
    where 
      customer_id = :id
        ) as "data"
from dual

4.Finally click Create Module button.

Note: For Resource Handler: GET setting after you created your RESTful service you may need to change Requires Secure Access setting from default Yes to No.

Classic and/or Interactive Report(s) and/or Interactive Grid(s)

not defined in Data Source but in Region Static ID(s) (see 3.5.5)

3.5.5 Region Static ID(s)

Define one or more Static Id(s) of the report region (Classic or Interactive Report or Interactive Grid). Static ids should be separated by a comma. e.g. classic1,ir1,ir2 You can set the Static ID of the region in the region attributes. You can call a specific Interactive Report by adding a pipe next to it e.g. ir1|my_saved_report.

3.5.6 Output Filename

The filename can be a hard coded string or reference an APEX item. It does not need to include the file extension. If a file extension is defined that is different than the output type selected, a new file extension will be appended to the filename.

Filename examples

Static: my_file

APEX Item: &P1_FILENAME.

3.5.7 Output Type

Available options include:

3.5.8 Special

Specific features of APEX Office Print which become available when the data type is SQL or Classic and/or Interactive Report(s)

Available options include:

Treat all numbers as strings There's a limitation in APEX with the cursor() statement in SQL that it doesn't remember which datatype the column is in. So when doing to_char(0.9,'990D00') it will return 0.9 as number instead of as string '0.90'. To resolve this, enable this checkbox and concatenate your number with '!FMT!' e.g. '!FMT!'||to_char(35, '990D00') - !FMT! stands for format.

Alternatively if you format your number with the currency sign to_char(35,'FML990D00') Oracle will recognise it as a string and you don't need to use this checkbox.

Report as Labels Check this box in case you want to use the Classic or Interactive Report data source but print them as Labels (using the Mailings feature in Word). * this option is deprecated since AOP 3.0 as AOP understands labels depending the template.

IR: Show Filters on top When there're filters applied to the Interactive Report, this checkbox will print them above the report.

IR: Show Highlights on top When there're highlights applied to the Interactive Report, this checkbox will print them above the report.

IR: Show header with filter (Excel) When checked AOP will make the header of the Interactive Report in Excel a filter.

IR: Use Saved Report instead of Report in Session If you want to make sure you always call a specific Interactive Report, without taking into account what the person is doing with it, you can check this box and the named (saved) Interactive Report will be taken. e.g. ir1|my_saved_report would be in your source.

3.5.9 Output To

Available options include:

Browser

The browser will download the file to your Downloads folder.

Procedure

This option will call a procedure in a specific format e.g. send_email_prc. This option is useful in case you do not need the file on your own hard drive, but for example you want to mail the document automatically. In that case you can create a procedure that adds the generated document as an attachment to your apex_mail.send.

In the database the procedure needs to have following parameters:

procedure send_email_prc(
    p_output_blob in blob,
    p_output_filename in varchar2,
    p_output_mime_type in varchar2)

Procedure and Browser

This option allows you to call a procedure first and next download the file to your hard drive. An example is when you first want to store the generated document in a table before letting the browser to download it.

Inline Region (pdf/html/md only)

If you want to output a PDF in a certain region or as a pop-up modal dialog, use this option.

4 PL/SQL API

4.1 Pre-requisites

APEX 5.0.4 or higher needs to be installed in the database as the PL/SQL API uses some packages that come with APEX e.g. apex_json

4.2 Install in the Database

Go into SQL Plus, SQLcl, SQL Developer or SQL Workshop and make sure following packages exist aop_api3_pkg and aop_plsql3_pkg. As part of the installation those packages should have been compiled.

4.3 How to use

You can call AOP straight from PL/SQL. APEX Office Print comes with two different ways of calling AOP; one package is aop_api3_pkg which is also used behind the scenes in the APEX Plugin and the other on is aop_plsql3_pkg. aop_plsql3_pkg is especially useful when you just want to do the call to the AOP server component or cloud.

Next to the above two packages we also provide two additional packages that show how to do the pl/sql calls. See the packages aop_sample3_pkg and aop_test3_pkg.

4.4 Parameters

aop_api3_pkg

Global variables which can be set

-- Call to AOP
g_proxy_override          varchar2(300) := null;  -- null=proxy defined in the application attributes
g_transfer_timeout        number(6)     := 180;   -- default is 180
g_wallet_path             varchar2(300) := null;  -- null=defined in Manage Instance > Instance Settings
g_wallet_pwd              varchar2(300) := null;  -- null=defined in Manage Instance > Instance Settings
g_output_filename         varchar2(100) := null;  -- output
g_language                varchar2(2)   := 'en';  -- Language can be: en, fr, nl, de
g_logging                 clob          := '';    -- ability to add your own logging: e.g. "request_id":"123", "request_app":"APEX", "request_user":"RND"  
-- AOP settings for Interactive Report (see also Printing attributes in IR)
g_rpt_header_font_name    varchar2(50)  := '';    -- Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_header_font_size    varchar2(3)   := '';    -- 14
g_rpt_header_font_color   varchar2(50)  := '';    -- #071626
g_rpt_header_back_color   varchar2(50)  := '';    -- #FAFAFA
g_rpt_header_border_width varchar2(50)  := '';    -- 1 ; '0' = no border
g_rpt_header_border_color varchar2(50)  := '';    -- #000000
g_rpt_data_font_name      varchar2(50)  := '';    -- Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_data_font_size      varchar2(3)   := '';    -- 14
g_rpt_data_font_color     varchar2(50)  := '';    -- #000000
g_rpt_data_back_color     varchar2(50)  := '';    -- #FFFFFF
g_rpt_data_border_width   varchar2(50)  := '';    -- 1 ; '0' = no border 
g_rpt_data_border_color   varchar2(50)  := '';    -- #000000
g_rpt_data_alt_row_color  varchar2(50)  := '';    -- #FFFFFF for no alt row color, use same color as g_rpt_data_back_color
-- Call to URL data source
g_url_username            varchar2(300) := null;
g_url_password            varchar2(300) := null;
g_url_proxy_override      varchar2(300) := null;
g_url_transfer_timeout    number        := 180;
g_url_body                clob          := empty_clob();
g_url_body_blob           blob          := empty_blob();
g_url_parm_name           apex_application_global.vc_arr2; -- := empty_vc_arr;
g_url_parm_value          apex_application_global.vc_arr2; --:= empty_vc_arr;
g_url_wallet_path         varchar2(300) := null;
g_url_wallet_pwd          varchar2(300) := null;

Function call

function plsql_call_to_aop(
  p_data_type             in varchar2 default c_source_type_sql,  
  p_data_source           in clob,
  p_template_type         in varchar2 default c_source_type_apex,
  p_template_source       in clob,
  p_output_type           in varchar2,
  p_output_filename       in out nocopy varchar2,
  p_output_type_item_name in varchar2 default null,
  p_output_to             in varchar2 default null,
  p_procedure             in varchar2 default null,
  p_binds                 in wwv_flow_plugin_util.t_bind_list default c_binds,
  p_special               in varchar2 default null,
  p_aop_remote_debug      in varchar2 default 'No',
  p_output_converter      in varchar2 default null,
  p_aop_url               in varchar2,
  p_api_key               in varchar2,
  p_app_id                in number default null,
  p_page_id               in number default null,
  p_user_name             in varchar2 default null,
  p_init_code             in clob default 'null;',
  p_output_encoding       in varchar2 default c_output_encoding_raw))
  return blob;

Note that some other procedures and functions are available in the package which could be helpful.

aop_plsql3_pkg

function make_aop_request(
  p_aop_url          in varchar2 default g_aop_url,
  p_api_key          in varchar2 default g_api_key,
  p_json             in clob,
  p_template         in blob,
  p_output_type      in varchar2 default null,
  p_output_filename  in varchar2 default 'output',
  p_aop_remote_debug in varchar2 default 'No')
  return blob;

4.5 Example

Example with aop_api3_pkg

declare
  l_binds           wwv_flow_plugin_util.t_bind_list;
  l_return          blob;
  l_output_filename varchar2(100) := 'output';
begin  
  -- define bind variables
  l_binds(1).name := 'p_id';
  l_binds(1).value := '1';

  l_return := aop_api3_pkg.plsql_call_to_aop (
                p_data_type       => 'SQL',
                p_data_source     => q'[
                  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 demo_order_items i, demo_product_info p
                                          where o.order_id = i.order_id
                                            and i.product_id = p.product_id
                                        ) "product"
                                 from demo_orders o
                                where c.customer_id = o.customer_id
                              ) "orders"
                      from demo_customers c
                      where customer_id = :p_id
                    ) as "data"
                  from dual                
                ]',
                p_template_type   => 'SQL',
                p_template_source => q'[
                   select template_type, template_blob
                    from aop_template  
                   where id = 1
                ]',
                p_output_type     => 'docx',
                p_output_filename => l_output_filename,
                p_binds           => l_binds,
                p_aop_url         => 'http://www.apexofficeprint.com/api/',
                p_api_key         => '1C511A58ECC73874E0530100007FD01A',
                p_app_id          => 232);
end;

Example with aop_plsql3_pkg

declare 
  l_template        blob;
  l_output_file     blob;
begin
  select template_blob
    into l_template
    from aop_template
   where id = 1;

  l_output_file := aop_plsql3_pkg.make_aop_request(
                     p_json        => '[{ "filename": "file1", "data": [{ "cust_first_name": "APEX Office Print" }] }]',
                     p_template    => l_template,
                     p_output_type => 'docx',
                     p_aop_remote_debug => 'Yes');                     
end;

In aop_sample3_pkg you find the above examples and you can run them with following command:

begin
    aop_sample3_pkg.call_aop_plsql3_pkg;
    aop_sample3_pkg.call_aop_api3_pkg;
end;

Note that there are more examples in the aop_sample3_pkg, so it's worthwhile to check it out.

5 Manual (REST) call

5.1 Overview

Behind the scenes the APEX plugin is creating a JSON file that will be sent to the AOP server. Below you find more details about the JSON structure that is used. By understanding this JSON structure you can manually interact with the AOP server in case you wanted to code in PL/SQL or other languages.

5.2 JSON File

The JSON file is a file that follows the standard JSON structure (http://json.org/).

This JSON file contains an JSON object meaning it starts with { and ends with }. This JSON object contains four compulsory JSON objects namely "template", "output", "files", "api_key" and one optional object "version". The purpose of each object will now be explained.

5.2.1 The "version" object:

This object contains the version of AOP JSON format used. The current version is 2.2 and backwards compatible thus optional. This will be used for future upgrades and if the newer version is not backwards compatible.

5.2.2 The "template" object:

This object has the necessary information about the template. It contains:

JSON Explanation
"file" This contains the template to be used, encoded in base64.
"filename" This option is for on-premise users. The template defined by this object will be used for processing. Note that the filename either contains a relative path from where the server is running from (or the folder/directory containing application) or an absolute path.
"template_type" This states what kind of template is being used. It must be either "docx", "pptx" or "xlsx".

If "file" and "filename" objects are not present in the "template" object then the server will respond with a custom template file of "template_type" made from this given JSON file. Currently only word and powerpoint template generation is supported.

5.2.3 The "output" object:

This object has the necessary information for producing an output file from the given JSON file. It contains:

JSON Explanation
"output_type" This states what kind of output file type is required. It can be either the same as template_type ("docx", "pptx" or "xlsx"), "pdf" or any other output file supported by libreoffice/openoffice. Default: the same as template_type.
"output_encoding" This states what kind of output encoding is wished for the output file. It must be either "raw" or "base64".
"output_converter" This states which software should be used to convert to pdf. The ApexOfficePrint server usages LibreOffice. On premise users may use msoffice but will need to do some configuration first. Available values: "officetopdf" (Windows only), "libreoffice" (Windows, Linux, OSX) or "abiword" (Linux only)

5.2.3 The "api_key":

The value of this key is the api key given by ApexOfficePrint. Only applicable for service users.

5.2.4 The "file" array:

This array contains the data that will be used for the given template. If this array contains more than one object then the output files, which are generated using the same template for each item, will be returned in a zip file. This array contains JSON objects with following fields:

JSON Explanation
"filename" This field contains the name of the output file.
"data" This array (containing one object) or object contains the data that will be replaced in the template.

Example showing a basic JSON file which should be populated with data:

{
    "template": {
           "filename"               :"template.docx",
           "template_type"          :"docx"
    },
    "output": { 
           "output_encoding"        :"raw",
           "output_type"            :"docx"
    },
    "files": [{
             "filename"             :"file1",
             "data"                 : [{...}]
    }]
}

6 Templates

The templates are standard office files in which tags will be replaced by structured data from the JSON file. These tags are quite standardized for either .docx, .pptx or .xlsx, however there are some subtle differences. They are all able to handle JavaScript angular expressions, on which some of the basics will follow.

6.0 Tag Overview

In the table below is an overview of the available tags and their support in Word, Excel and PowerPoint respectively. For more information on each tag, see the sections that follow.

Tag Word Excel PowerPoint HTML Markdown
{data_string} Yes Yes Yes Yes Yes
{data_string_with_cell_markup$} Yes Yes No No No
{-labels} Yes No No No No
{_htmlcontent} Yes No No Yes (by default) Yes (by default)
{!slide_loop} No No Yes No No
{#data_loop} … {/data_loop} Yes Yes No Yes Yes
{=table_row_loop} … {/table_row_loop} No No Yes No No
{%imageKey} Yes Yes Yes Yes Yes
{|barcode} Yes Yes Yes No No
{|qrcode} Yes Yes Yes No No
{$chart} Yes No Yes No No
{&interactive_report} Yes Yes No No No
{&interactive_grid&} Yes Yes No No No

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

6.1 Word (docx) template

These templates can contain the following tags:

6.1.1 {data_string} tags

This tag, {data_string}, will be replaced by the value of the key "data_string" in the JSON file.

6.1.1.1 Data string example:

Given the JSON file with following content:

    {
        "template": {
               "filename"               :"template.docx",
               "template_type"          :"docx"
        },
        "output": { 
               "output_encoding"        :"raw",
               "output_type"            :"docx"
        },
        "files": [{
                 "filename"             :"output",
                 "data": [{
                         "first_name"   :"DemoName",
                         "last_name"    :"DemoLastName",
                         "city"         :"DemoCity"
                  }]
        }]
    }

and template.docx with following content:

                                             {last_name} {first_name}
                                             {city}

will result in:

                                              DemoLastName DemoName
                                              DemoCity

Since the template and the output objects from the json will not change, these will be omitted from the examples given below.

6.1.2 {%imagekey} tags

This tag is the same as {data_string} but instead getting replaced by a string this tag is replaced by an image that is placed in the value of key "imagekey" after base64 encoding. Note that imagekey is just a variable. An image tag is represented by "%" after curly bracket. You can also specify a fixed width and the height by adding following values "imagekey_width" and "imagekey_height". If you want to scale your image proportionally you can use the "imagekey_max_width" and "imagekey_max_height" tags. An example is created in the AOP Sample Application which shows how it works. The imagekey is pic, so we can use pic_max_width for example.

6.1.2.1 Image tag example:

Replace /...code..../ for the base64 encoded string of the image

    {
        ...
        "files":[{
                 "filename"        :"output",
            "data":[{
                  "product_name"   :"Wallet",
                  "price"          :"$500.00",
                  "pic"            :"/...code..../",
                  "pic_max_width"  : 100,
                  "pic_max_height" : 100                  
            }]
        }]
    }

with template:

Product Image Price
{product_name} {%pic} {price}

will result in:

Tip: in case you want to make your images equal in size, you can use PL/SQL to resize your images. Here's an example to put the size of the image to a maximum of 200px.

declare
  l_img blob;
begin
  select image 
    into l_img 
    from my_table
   where id = 1 for update;
  ordsys.ordimage.process(l, 'maxscale=200 200');
  update my_table 
     set image = l 
   where id = 1;
end;

6.1.3 {|barcode} / {|qrcode} tags

This tag will be replaced by the barcode or QR code created using the data of the key named "barcode" or "qrcode". Notice that these are also variable. Barcode or qrcode tags are represented by | after curly bracket. If this tag is found in the document there must be additional supporting keys containing the options for these tags. These options are:

  1. key_type, e.g. "barcode_type" if the key used is "barcode".
    This field contains the type of barcode required.
    The options are:
    "codabar"
    "code128"
    "code39"
    "ean13"
    "upc-a"
    "upc-e"
    "qrcode"

  2. key_height, e.g. "barcode_height" if the key used is "barcode".
    This field contains the height for the generated image.
    Default is 200 for QR, 50 for the rest.

  3. key_width, e.g. "barcode_width" if they key used is "barcode". This field contains the width for the generated image.
    Default is 200.

  4. key_version (only for QR code), e.g. "qrcode_version" if the key used is "qrcode". This field contains the version of QR code to be used.
    Default value 4.
    Extra info: http://www.qrcode.com/en/about/version.html

  5. key_errorcorrectlevel (only for QR code), e.g. "qrcode_errorcorrectlevel" if the key used is "qrcode". This field contains the level of which the QR code should be recoverable.
    The options are:
    "L" (up to 7% damage)
    "M" (up to 15% damage)
    "Q" (up to 25% damage)
    "H" (up to 30% damage)
    Extra info: http://www.qrcode.com/en/about/error_correction.html

  6. key_cellsize (only for QR code), e.g. "qrcode_cellsize". This field contains the dot size of a module.
    Default is 4. Extra info: http://www.qrcode.com/en/howto/cell.html

(*) In order for barcodes to work in AOP on-premise GraphicMagick or ImageMagick needs to be installed. QR codes don't need this extra software.

6.1.3.1 Barcode tag example:

Given the JSON file:

    {
        ...
        "files":[{
                 "filename"           :"output",
            "data":[{
                  "product_name"      :"Wallet",
                  "product_code"      :"18965478321456",
                  "product_code_type" :"ean13",
                  "product_link"      :"https://www.google.be/search?q=wallet",
                  "product_link_type" :"qrcode"
            }]
        }]
    }

and template as:

will result in:

For barcodes, instead of doing the above, you could also choose to install a barcode font, for example Free 3of9 or http://www.dafont.com/3of9-barcode.font. Barcode fonts are more performant than images. See the other section for more information about language and font support.

6.1.4 {$chart} tags

This tag will be replaced by chart created using the data of the key named "chart". Notice that this key is a variable. Charts are represented by $ after curly bracket. If this tag is found in the document the chart object in the json must follow certain structure. Firstly an optional object with key "options" to determine the options for the chart. The default options are:

    defaultOptions = {
            width: 5486400 / 9525, // width of the chart
            height: 3200400 / 9525, // height of the chart
            grid: true, // if a grid should be shown
            border: true, // if a border should be shown
            title: false,
            legend: {
                showLegend: true, // if the legend should be shown
                position: 'r'  // 'l' for left, 'r' right, 'b' bottom, 't' top 
            },
            axis: {
                x: {
                    orientation: 'minMax', // or "maxMin"
                    min: undefined, // a specific number
                    max: undefined, // a specific number
                    type: undefined, // or "date"
                    date: { //date options
                        format: 'unix',
                        code: 'mm/yy',
                        unit: 'months',
                        step: '1'
                    },
                    showValues: true //options to disable showing the values in axis

                },
                y: {
                    orientation: 'minMax',
                    mix: undefined,
                    max: undefined,
                    showValues: true // options to disable showing the values in axis
                 }
            }
        }

Secondly the type of chart should be determined by "type" key. The generation of following types of charts is supported:

6.1.4.1 Line

This is a normal chart where the data's are connected with lines. Multiple lines can be generated. The chart key should contain lines array inside with the data of the lines that should be generated and the name of the line. E.g.:

    "chart": {
            "lines": [
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "4.3"
                        },
                        {
                            "x": "day 2",
                            "y": "2.5"
                        },
                        {s
                            "x": "day 3",
                            "y": "3.5"
                        }
                    ],
                    "name": "line 1"
                },
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "2.4"
                        },
                        {
                            "x": "day 2",
                            "y": "4.4"
                        },
                        {
                            "x": "day 3",
                            "y": "1.8"
                        }
                    ],
                    "name": "line 2"
                }
            ],
            "type": "line"
        }

will result in :

6.1.4.2 bar

In order to generate the bar chart, the chart object should then contains array names bars. This array contains the objects with data about the bar and the name of the bar. For example, given the following:

    ...
    "chart": {
        "bars": [
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "4.3"
                    },
                    {
                        "x": "day 2",
                        "y": "2.5"
                    },
                    {
                        "x": "day 3",
                        "y": "3.5"
                    }
                ],
                "name": "bar 1"
            },
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "2.4"
                    },
                    {
                        "x": "day 2",
                        "y": "4.4"
                    },
                    {
                        "x": "day 3",
                        "y": "1.8"
                    }
                ],
                "name": "bar 2"
            }
        ],
        "type": "bar"
    }
    ...

will result in:

Single bar chart is generated as follows:

    ...
    "chart": {
        "bars": [
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "4.3"
                    },
                    {
                        "x": "day 2",
                        "y": "2.5"
                    },
                    {
                        "x": "day 3",
                        "y": "3.5"
                    }
                ],
                "name": "bar 1"
            }
        ],
        "type": "bar"
    }
    ...

will result in:

6.1.4.3 barStacked

This is like bar chart but the bars from same category will be stacked. For example, given the following:

    ...
    "chart": {
        "bars": [
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "4.3"
                    },
                    {
                        "x": "day 2",
                        "y": "2.5"
                    },
                    {
                        "x": "day 3",
                        "y": "3.5"
                    }
                ],
                "name": "bar 1"
            },
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "2.4"
                    },
                    {
                        "x": "day 2",
                        "y": "4.4"
                    },
                    {
                        "x": "day 3",
                        "y": "1.8"
                    }
                ],
                "name": "bar 2"
            }
        ],
        "type": "barStacked"
    }
    ...

will result in:

6.1.4.4 barStackedPercent

This is like bar stacked chart but the x axis will be in expressed in percentage. For example, given the following:

    ...
    "chart": {
        "bars": [
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "4.3"
                    },
                    {
                        "x": "day 2",
                        "y": "2.5"
                    },
                    {
                        "x": "day 3",
                        "y": "3.5"
                    }
                ],
                "name": "bar 1"
            },
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "2.4"
                    },
                    {
                        "x": "day 2",
                        "y": "4.4"
                    },
                    {
                        "x": "day 3",
                        "y": "1.8"
                    }
                ],
                "name": "bar 2"
            }
        ],
        "type": "barStacked"
    }
    ...

will result in:

6.1.4.5 column

This will produce a normal column chart. The chart object should then contain an array named columns with objects containing the data and name of the column. E.g.:

        "chart": {
            "columns": [
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "4.3"
                        },
                        {
                            "x": "day 2",
                            "y": "2.5"
                        },
                        {
                            "x": "day 3",
                            "y": "3.5"
                        }
                    ],
                    "name": "column 1"
                },
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "2.4"
                        },
                        {
                            "x": "day 2",
                            "y": "4.4"
                        },
                        {
                            "x": "day 3",
                            "y": "1.8"
                        }
                    ],
                    "name": "column 2"
                }
            ],
            "type": "column"
        }

will result in:

A single column chart can be generated by following json:

        "chart": {
            "columns": [
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "4.3"
                        },
                        {
                            "x": "day 2",
                            "y": "2.5"
                        },
                        {
                            "x": "day 3",
                            "y": "3.5"
                        }
                    ],
                    "name": "column 1"
                }
            ],
            "type": "column"
        }

will result in:

6.1.4.6 columnStacked

This will produce a column stacked chart. The chart object should then contain an array named columns with objects containing the data and the name of the column. E.g:

        "chart": {
            "columns": [
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "4.3"
                        },
                        {
                            "x": "day 2",
                            "y": "2.5"
                        },
                        {
                            "x": "day 3",
                            "y": "3.5"
                        }
                    ],
                    "name": "column 1"
                },
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "2.4"
                        },
                        {
                            "x": "day 2",
                            "y": "4.4"
                        },
                        {
                            "x": "day 3",
                            "y": "1.8"
                        }
                    ],
                    "name": "column 2"
                }
            ],
            "type": "columnStacked"
        }

will result in:

6.1.4.7 columnStackedPercent

This will product a column stacked percent chart. The chart object should then contain an array named columns with objects containing the data and the name of the column. E.g:

        "chart": {
            "columns": [
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "4.3"
                        },
                        {
                            "x": "day 2",
                            "y": "2.5"
                        },
                        {
                            "x": "day 3",
                            "y": "3.5"
                        }
                    ],
                    "name": "column 1"
                },
                {
                    "data": [
                        {
                            "x": "day 1",
                            "y": "2.4"
                        },
                        {
                            "x": "day 2",
                            "y": "4.4"
                        },
                        {
                            "x": "day 3",
                            "y": "1.8"
                        }
                    ],
                    "name": "column 2"
                }
            ],
            "type": "columnStackedPercent"
        }

will result in:

6.1.4.8 pie

This will produce a pie chart. The chart object should then contain an array named pies with one element containing the data and name of the pie chart. E.g:

        "chart": {
            "pies": [
                {
                    "data": [
                        {
                            "x": "Order 1",
                            "y": 1890
                        },
                        {
                            "x": "Order 2",
                            "y": 2380
                        },
                        {
                            "x": "Order 3",
                            "y": 1640
                        },
                        {
                            "x": "Order 4",
                            "y": 1090
                        },
                        {
                            "x": "Order 5",
                            "y": 950
                        },
                        {
                            "x": "Order 6",
                            "y": 1515
                        },
                        {
                            "x": "Order 7",
                            "y": 905
                        },
                        {
                            "x": "Order 8",
                            "y": 1060
                        },
                        {
                            "x": "Order 9",
                            "y": 730
                        },
                        {
                            "x": "Order 10",
                            "y": 870
                        }
                    ],
                    "name": "pie 1"
                }
            ],
            "type": "pie"
        }

will result in:

6.1.4.9 radar

This will produce a radar chart. The chart object should then contain an array named radars with one element containing the data and name of the radar chart. E.g:

        "chart": {
            "radars": [
                {
                    "data": [
                        {
                            "x": "Order 1",
                            "y": 1240
                        },
                        {
                            "x": "Order 2",
                            "y": 380
                        },
                        {
                            "x": "Order 3",
                            "y": 840
                        },
                        {
                            "x": "Order 4",
                            "y": 490
                        },
                        {
                            "x": "Order 5",
                            "y": 1230
                        }
                    ],
                    "name": "radar 1"
                }
            ],
            "type": "radar"
        }

will result in:

6.1.4.10 area

This will produce an area chart. The chart object should then contain an array named areas with one element containing the data and name of the area chart. E.g:

            "chart": {
        "areas": [
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "4.3"
                    },
                    {
                        "x": "day 2",
                        "y": "2.5"
                    },
                    {
                        "x": "day 3",
                        "y": "3.5"
                    }
                ],
                "name": "area 1"
            },
            {
                "data": [
                    {
                        "x": "day 1",
                        "y": "2.4"
                    },
                    {
                        "x": "day 2",
                        "y": "4.4"
                    },
                    {
                        "x": "day 3",
                        "y": "1.8"
                    }
                ],
                "name": "area 2"
            }
        ],
        "type": "area"
    }

will result in:

6.1.5 {#data_loop} … {/data_loop} tags

Using the opening tag {#data_loop} and closing tag {/data_loop}, we can loop inside the JSON array named "data_loop", meaning the items that are inside the tags will be repeated for each array element. Again like in image tag "data_loop" is a variable. A loop tag is represented by "#" after the curly bracket.

6.1.5.1 General Data loop example:

Given the JSON file with following content:

    {
        ...
        "files":[{
               "filename"           :"output",
                "data":[{
                       "people":[{
                              "first_name" :"DemoName1",
                              "last_name"  :"DemoLastName1",
                              "city"       :"DemoCity1"},
                       {
                              "first_name" :"DemoName2",
                              "last_name"  :"DemoLastName2",
                              "city"       :"DemoCity2"},
                       {
                              "first_name" :"DemoName3",
                              "last_name"  :"DemoLastName3",
                              "city"       :"DemoCity3"}
                       ]
                }]
        }]
    }

and template.docx with following content:

The name of the employees are:
{#people}{first_name}{last_name} from city {city}.
{/people}

will result in:

The name of the employees are:
DemoName1 DemoLastName1 from City DemoCity1.
DemoName2 DemoLastName2 from City DemoCity2.
DemoName3 DemoLastName3 from City DemoCity3.

6.1.5.2 Loops in table example:

Using the previous data, if the template is changed to:

the result will then be:

6.1.5.3 Loops with numbering example:

Using the previous data, if the template is changed to:

the result will be:

6.1.5.4 Loops in bullets:

The same can be done with bullets:

will result in:

6.1.6 Simple angular like expressions

Simple angular like expressions are also supported that can perform simple mathematical operations.

A full list of Angular expression can be found at https://docs.angularjs.org/guide/expression.

6.1.6.1 Simple Expressions

Expressions like {num1 + num2} will print the sum of num1 and num2. Division, multiplication, addition and subtraction are allowed.

If the given key has a boolean value then you can use {#booleankey}…{/booleankey} to print the content inside tags if the value of booleankey is true. For negation {^booleankey}…{/booleankey} is used. The content inside these tags will be printed if the booleankey has false as value or if booleankey does not exists or if booleankey is an empty array.

6.1.6.2 String/Number Comparison

You can also check the value of keys with {#key== 'value '}…{/key== 'value '}. The content inside the tags will be only printed if the value of the key is equal to "value". Note here in {#key== 'value '}, that straight single quotes are used!

The numbers can also be compared in the same way as strings {#key>;50}…{/key>;50}.The content inside the tags will be only printed if the value of the key is greater than 50. The following operators are supported: <, >, <=, >=, ==, !=

6.1.6.3 Conditional and Comparison operator:

Another way to compare variables is as follows: {key == 'value' ? key2 : key3}. This way other keys/variables can also be inserted. This will print out value of key2 if the expression is true and key3 if the expression is false.

Possible conditional operators are:

6.1.6.4 Conditional example:

For the JSON given in example below {currency == 'EUR' ? '€'+price : price+'$'} prints €50 if true and 50$ if false and given price variable is 50.

Given the JSON file with following content:

    {
        ...
                 "data":[{
                       "product":[{
                              "product_name" :"Business Shirt",
                              "quantity"     :3,
                              "unit_price"   :50,
                              "onstock"      :true,
                              "cur"          :"EUR"
                       },
                       {
                              "product_name" :"Trousers",
                              "quantity"     :3,
                              "unit_price"   :80,
                              "onstock"      :false,
                              "cur"          :"USD"
                       },
                       {
                              "product_name" :"Jacket",
                              "quantity"     :3,
                              "unit_price"   :15,
                              "onstock"      :true,
                              "cur"          :"USD"
                       },
                       {
                              "product_name" :"Blouse",
                              "quantity"     :3,
                              "unit_price"   :60,
                              "onstock"      :false,
                              "cur"          :"EUR"
                       }]
                }]
    }

and template with following content:

will result in:

6.1.7 Labels printing

AOP also provides a way to print the labels in word document. To do so you can create a document with labels from going to Mailings options and then to Labels. Fill in the tags in address field and choose the type of label by clicking in 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 containining labels and no other information, and that the tag keys are not used more than once. Given the JSON file:

    ...
        "data": {
            "labels": [
                {
                    "city": "city1",
                    "first_name": "firstname1",
                    "last_name": "lastname1",
                    "title": "Mr.",
                    "tracking_number_text": "TN49775377172",
                    "tracking_number": "TN49775377172",
                    "tracking_number_type": "code128",
                    "zip_code": 6981
                },
                ...
                {
                    "city": "city12",
                    "first_name": "firstname12",
                    "last_name": "lastname12",
                    "title": "Mr.",
                    "tracking_number_text": "TN49709864775",
                    "tracking_number": "TN49709864775",
                    "tracking_number_type": "code128",
                    "zip_code": 9740
                }
            ]
        }
    ...

and template:

will produce:

Note that in the first cell you need to add the {labels} tag. See also the Sample app.

6.1.8 HTML tag:

Since version 2.1 limited HTML tags can also be converted to Word. The tag that has to be used is {_key}, thus underscore followed by the key which has HTML content in JSON. Note that this function is still under development. The tags that are currently supported are:

<br />                  : in order to introduce breaks (newline)
<p> .. </p>             : represents a paragraph
<strong> .. </strong>   : bold text
<s> .. </s>             : strike through
<u> .. </u>             : underline
<em> .. </em>           : italics
<h1> .. </h1>           : heading 1
<h2> .. </h2>           : heading 2
<h3> .. </h3>           : heading 3
<sub> .. </sub>         : subscript
<sup> .. </sup>         : superscript
<ul> .. </ul>           : unordered list
<table> .. </table>     : table
<span style="..">..</span>: text between the span will have the style defined, background-color, color, font-size and font-family are supported.

Eg: Given the following key inside data:

    "htmlcontent": "<p>This is text coming from the database / session in HTML format.
    <br />It supports:
    </p>
    <ol>
    <li>Lists</li>
    <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>

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

and the template:

will produce:

6.1.9 Interactive Report (IR) tag:

{&interactive} will be replaced in the given template by the interactive report that has been selected in APEX. Other than the simple interactive report, there are three other options that can be selected: Control Break, Group By, Pivot. Those will be illustrated below. Keep in mind that the tag that gets replaced is {&interactive}, no matter which of the above options is selected in APEX. For a concrete example, see section 11.

Control Break: By clicking on Actions and selecting Control Break, one or more columns can be selected on which the table should be broken. For the purpose of illustration, assume we select Quantity.

This splits the table up into partitions based on the value of the selected column. Thus rows sharing the same value for Quantity, will be grouped together into the same partition. The resulting table can be seen below:

As a final note, this option also supports the application of aggregates on the table data.

Group By: By clicking on Actions and selecting Group By, one or more columns can be selected on which the table data should be grouped by. For the purpose of illustration, assume we select Quantity. Furthermore, one or more functions can be applied on the table data. In this example, we request the sum of the unit price for every group.

The result is a new table with one column for every selected column on which the data should be grouped by, and one column for every selected function to be applied on the group data.

The resulting table can be seen below:

Additionally, a custom label and format may be selected for each function column and a sum can be performed over the values of each function column.

Pivot: By clicking on Actions and selecting Pivot, the user is prompted to select at least one pivot column, at least one row column and at least one function over a particular column. Important to note is that the row column, pivot column and function column need to be different. For the sake of the example, we select Quantity as a pivot column, Product Name as a row column and sum over Unit Price as a function.

The resulting table can be seen below:

The additional options supported in this case are the same as the ones in Group By.

Chart: AOP can also print your Interactive Report Chart view directly in your template by using {\$interactive}.

The result in Powerpoint can be seen below:

Note that this is a native Powerpoint chart, so you can adapt, make bigger, change colors, etc. directly in Powerpoint.

6.1.10 Multiple Interactive Reports in one template:

In this case, the template needs to have tags of the form {&interactive_1},{&interactive_2} and these tags will be replaced by the corresponding interactive reports. Many interactive reports can be selected by giving in their static IDs in a particular order (ir1,ir2,...) and it is in that order that their data will be inserted in the template. Thus, {&interactive_1} gets replaced by the data in the first interactive report (static Id: ir1) and similarly for {&interactive_2} that will get replaced by the data in the second interactive report (static Id: ir2) and so on.

The result can be seen below:

6.1.11 Table cell background:

You can give a background to a cell in a table in Word by using the column followed by _cell_background_color

 cursor(select p.product_name as "product_name", 
                              i.quantity as "quantity",
                              case 
                              when i.quantity between 1 and 2 then '#00FF00'
                              when i.quantity between 2 and 3 then '#00FF00'
                              else '#0000FF'
                              end as "quantity_cell_background_color", 
                              i.unit_price as "unit_price"
                         from demo_order_items i, demo_product_info p
                        where o.order_id = i.order_id
                          and i.product_id = p.product_id
                      ) "backcolor"             

6.2 Powerpoint (pptx) template

For pptx template files same tags are supported as in docx template. Attention should be paid when using "#", "%", "dollarsign" and "\^" tags, namely they should be on their own text field. The position of the text field will determine where the top left of the chart and image will start. Repeating a template slide is done by specifying which array should be looped with "!" tag after the curly bracket for example {!products} to repeat over product array. If none is declared the root object is taken as array. Multiple "!" tags are not allowed in one slide.

6.2.1.1 Example:

Given the JSON file with following content:

    {
        "template": {
            "template_type": "pptx",
            "filename": "demo.pptx"
        },
        "output": {
            "output_encoding": "raw",
            "output_type": "pptx"
        },
        "files": [
            {
                "filename": "output",
                "data": [
                    {
                        "slide_title": "Slides Per Product",
                        "company_name": "Company A",
                        "product": [
                            {
                                "product_name": "Business Shirt",
                                "quantity": 3,
                                "unit_price": 50
                            },
                            {
                                "product_name": "Trousers",
                                "quantity": 3,
                                "unit_price": 80
                            },
                            {
                                "product_name": "Jacket",
                                "quantity": 3,
                                "unit_price": 15
                            },
                            {
                                "product_name": "Blouse",
                                "quantity": 3,
                                "unit_price": 60
                            }
                        ]
                    }
                ]
            }
        ]
    }

and the pptx template as follows:

will result the following for the first slide:

Since there is only one item in root JSON object this is repeated only once. In template slide 2 we have specified product array as our loop array with {!product} tag. Since there are 4 objects in our product array this template will be repeated four times. The result is as follows:

6.3 Excel (xlsx) template

In the xslx templates, some of the same tags as the previous types of templates can be used, as shown in the overview table at the start of this section. The JSON format should contain the same meta information elements (e.g. template_type, filename, …) and the data information is stocked under files/data.

Regular tags can be used with corresponding keyword in the JSON file between brackets, like in the docx and pptx templaters. The xlsx templater can loop over elements in a list by using the same technique as in the docx template: {#loop_element} to enter the loop and {/loop_element} to close it. The cells that are in the rectangle bounded by these two tags will be looped over and filled in using the data available in the JSON file. Nested loops are possible (however keep in mind to remain within the rectangle formed by the "parent" loop – see note after example below). Style will be copied from the template to the generated file accordingly. As with the docx templater, simple angular expressions can be used to perform mathematical operations.

Xlsx example:

Given the JSON file with following content:

    {
        "template": {
        "template_type": "xlsx",
        "filename": "simple.xlsx"
        },
        "output": {
        "output_encoding": "raw",
        "output_type": "xlsx"
        },
        "files": [
        {
            "filename": "file1",
            "data": [
                {
                    "cust_first_name": "Albert",
                    "cust_last_name": "Lambert",
                    "cust_city": "St. Louis",
                    "orders": [
                        {
                            "order_total": 310,
                            "order_name": "Casual Shop's Order",
                            "product": [
                                {
                                    "product_name": "Shirt",
                                    "quantity": 3,
                                    "unit_price": 50
                                },
                                {
                                    "product_name": "Trousers",
                                    "quantity": 2,
                                    "unit_price": 80
                                }]
                        },
                        {
                            "order_total": 200,
                            "order_name": "Sport Shop's order",
                            "product": [
                                {
                                    "product_name": "Sport's shoes",
                                    "quantity": 2,
                                    "unit_price": 100
                                }]
                        }]
                }]
        }]
    }

and the xlsx template as follows:

Note: in the example above the {/orders} closing tag must be in the last column. Otherwise the child loops inside {#orders}…{/orders}, in this case {#product}…{/product}, would not be able to generate inside the rectangle formed between the tags of its parent loop ({#orders}…{/orders}).

Interactive Report in Excel: As of version 2.2, all of the functionalities supported in Word regarding the interactive report are also supported in Excel. For the purpose of illustration, let's take this template as input:

The resulting output is then:

AOP 3.0 and above also supports cell settings from JSON by using _ for your tag. For example when you have a column "tag", if you use "tag_font_color" : "#1782A6" it will set the Font color. When using "tag_cell_background":"#1DF248" you set the background color, when using "text_wrap":"y" will set if text is wrapped etc. A full list of features can be found in the Sample App for APEX 5.1.

7 SQL Structure

7.1 How to write SQL queries and map them to template documents

Please refer to the image below containing SQL query and matching template.

AOP Plugin supports PL/SQL cursor expressions which can be used in your templates as "data loops".

Left side corresponds to the SQL query that we used in our demo example. Right side is our Word template.

In SQL query you will notice three inner cursors called data, orders and product.

Idea is for each customer to find orders and products and display it as an invoice letter.

Data cursor is a standard part of AOP JSON structure and it needs to be present in all SQL queries. At the same time you do not have to specify it as a loop in your template. For more details about this please reference Section 9 of this Manual.

Other two cursor names can be of your preference as long as they are reflected in your template.

Why do we have cursor syntax?

This is the way AOP engine merges your data with your templates.

In our template example you want to display customer details (Data cursor) and then display all their orders (Orders cursor) with products (Product cursor) so each represents a separate cursor.

This means that we want to be able to create a loop for orders and a separate loop for products. This is exactly what we have done in this template.

The way AOP template loop works is by using {#loop element}….{/loop element} notation. In the template notice that we are using the following:

"Thank you for shopping at. We have following products reserved for you:

{#orders}{#product}  
    - {product_name}{/product}  
The total amount of your order is €{order_total}.
{/orders}"

First open Orders loop then open Product loop then doing a bullet numbering for all products. Before closing the Product loop and then closing the Orders loop.

Once you create your SQL query it is very easy to implement it in your template following few simple tagging rules depending on a template type you are creating.

8 Server (only On-Premise)

8.1 Installation

APEX Office Print is using an external converter. By default LibreOffice is used (available on all platforms), but this can be changed to MS Office (Windows only), or Abiword (Linux only). One of these products must be installed on the same server where you will also install APEX Office Print.

You can register and then login at https://www.apexofficeprint.com. Go to Downloads and download On Premise package for your plaftorm. Currently we support Linux, Windows and OSX.

Unzip the downloaded file, you will see a directory v3.0

Go to the v3.0/server directory and copy executable file and views folders on the server where you want AOP running in a directory of your choice

Executable files:

Decide on which port you want to let AOP run. By default it is using port 8010. You can change that later (for example APEXOfficePrintRH64 -p 5555).

If you want to run the On-Premise Trial version of AOP you can skip the activation step. 
The on-premise version of AOP in Trial mode will work with Word and PDF, but it contains a trial watermark. 
The cloud version of AOP (free 100 days) has no limitations.

Following step is only necessary when you bought AOP, it's not necessary to run in Trial mode (Linux example):

To start APEX Office Print:

PDF converter options:

LibreOffice can be downloaded from https://www.libreoffice.org. Make sure you download LibreOffice 5 or higher and no older version of LibreOffice is installed. Once the installation of LibreOffice is complete check if you can run soffice --version in a terminal.

You should see something like:

LibreOffice 5.2.3.3 d54a8868f08a7b39642414cf2c8ef2f228f780cf

If that fails make sure your environment variable PATH contains the directory of LibreOffice (bin directory). You can also create a symbolic link to soffice e.g.

ln -s /opt/libreoffice5.0/program/soffice /usr/sbin/soffice

If you're on Windows, you can choose for LibreOffice or MS Office. In case you want to use MS Office, make sure you have MS Office 2003 or higher installed and also install OfficetoPDF https://officetopdf.codeplex.com, which allows to run conversions from the command line. Note: Place the OfficetoPDF executable in the same folder than AOP.

After installing LibreOffice or MS Office, restart AOP.

Barcode option:

If you want AOP to generate barcodes GraphicMagick (http://www.graphicsmagick.org) or ImageMagick (http://www.imagemagick.org) needs to be installed. The binary folder of GraphicMagick software with PNG support should be placed on PATH environment variable if barcode generation is required (not needed for QR code). If set right running “gm –version” from command line should show you the version of GraphicMagick and if the PNG support is installed. Alternative to this imagemagick software can also be used.

Testing the connection to AOP:

Make sure the database server can connect to the port where AOP is running. If not, open the port so the database server can connect to the webserver:port (note you don't need to open it up to everybody, just the database server is fine)

For example by running curl webserver:port

$ curl 127.0.0.1:8010

The installation on the server part is now complete.

In the downloaded zip file there is also a sample application (app directory) that can be imported or you can just import the AOP plugin (plugin directory) into your application. The only configuration there is to define the webserver:port where AOP is running and which converter you are using (LibreOffice, MS Office). You can do that under Shared Components > Other Components > Component Settings. Then select APEX Office Print (AOP) Plug-in, change AOP URL and select appropriate converter.

8.2 Configuration

Following parameters can be used:

-p or --port can set a different port the server should be running at

Example: to run AOP on port 8015 run:

APEXOfficePrintRH64 –p 8015 (or ./APEXOfficePrintRH64 –p 8015)

8.3 Unlock Trial version

By default AOP will run in TRIAL version.

Only Word templates can be created in the TRIAL version of AOP. Once you have decided on a license level, the product can be activated by running -a or --activate:

\$ APEXOfficePrintRH64 –a

It will prompt for an email. Please use the same email you used when you subscribed for a license on https://www.apexofficeprint.com/

When running AOP again, it will show the new licensed templates you can use.

If there’s no internet connection, or something goes wrong with the activation, you can contact support@apexofficeprint.com and send us your MAC address of the server you want to install it on. We’ll send you the license file per email. Add that file in the same directory of AOP and restart AOP to get the full version.

To obtain the MAC address you can use following commands:

8.4 Server Options

To view all available option, run ./APEXOfficePrintRH64 -h

Following parameters can be used:                           
  -a or --activate  : Activate the software                    
  -h or --help      : Show this menu                               
  -p or --port      : For giving in the running port default: 8010 
                      Example: APEXOfficePrint -p 5555                            
  -i or --instances : The number of instances of AOP to start 
                      Giving 0 will start max instances available
  -s or --startat   : Directory to start at
  -v or --version   : Show the current version of AOP    
  -d or --debug     : Save stack trace       
  --logging         : Log data about the printjobs to server_printjob.log
  --silent          : Do everything quietly even start message
  --verbose         : Log what AOP is currently doing (console needed)

8.5 Running as Service in Windows

Using Microsoft Office as PDF converter

If you wish to use Microsoft office as PDF converter for your documents we have to do some tweaks before we can use ApexOfficePrint as service.

32-Bit Windows

Directory Creation

Create a directory "Desktop" if it does not already exist under \ "C:\Windows\system32\config\systemprofile\Desktop"

DCOM config:
  1. From Run (Windows + R), type dcomcnfg

  2. Navigate to Component Services > Computers > My Computer > DCOM Config

  1. Locate "Microsoft Excel Application" and right click properties of it

  2. Navigate to Security Tab and Customize the Launch and Activation Permissions and Edit

  3. Check that no instances of excel.exe are running before changing the properties, either close the applications that are running the Excel or go to task manager and kill the excel.exe processes.

  1. Add the current user (type the current user, click check names, and click ok) and grant permission for Local Launch and Local Activation

  2. Go to Identity Tab and change the radio button to **"The

  3. User"**

  4. Do the same for "Microsoft PowerPoint Slide".

  5. Do the same for "Microsoft Word 97-2003 Document".
Setting directory permissions:
  1. Navigate to: "C:\Windows\System32\config\systemprofile\AppData\Roaming\Microsoft".

  2. Right click and go to Properties

  3. Under Security Tab add the current user and grant the Modify, Read & Execute, List Folder Content, Read, and Write permissions.

  4. Do the same for "C:\Windows\Temp\".

64-Bit Windows

Directory Creation

Create a directory "Desktop" if it does not already exist under:

"C:\Windows\system32\config\systemprofile\Desktop" and "C:\Windows\SysWOW64\config\systemprofile\Desktop"

DCOM config:
  1. From Run (Windows + R), type dcomcnfg (if your Microsoft Office is 32 bit then open command line and change directory to "C:\Windows\SysWOW64" and run "mmc comexp.msc /32" command)

  2. Navigate to Component Services > Computers > My Computer > DCOM Config

  1. Locate "Microsoft Excel Application" and right click properties of it

  2. Navigate to Security Tab and Customize the Launch and Activation Permissions and Edit

  3. Check that no instances of excel.exe are running before changing the properties, either close the applications that are running the Excel or go to task manager and kill the excel.exe processes.

  1. Add the current user( type the current user, click check names, and click ok) and grant permission for Local Launch and Local Activation

  2. Go to Identity Tab and change the radio button to "The Interactive User"

  3. Do the same for "Microsoft PowerPoint Slide".

  4. Do the same for "Microsoft 97-2003 Document".
Setting directory permissions:

Navigate to:

  1. "C:\Windows\System32\config\systemprofile\AppData\Roaming\Microsoft".

  2. Right click and go to Properties

  3. Under Security Tab add the current user and grant the Modify, Read & Execute, List Folder Content, Read, and Write permissions.

    Do the same for:

  4. "C:\Windows\System32\config\systemprofile\AppData\Local\Microsoft"

  5. Do the same for "C:\Windows\Temp\".

Using LibreOffice as pdf converter

No extra steps are necessary.

Managing the service

8.6 Run APEX Office print at startup or as service in Linux

After reboot of the server where APEX Office print is installed, you have to make sure that you also run APEXOfficePrintRH64 command. If you want to automate the startup of AOP you can create in init.d. For example: /etc/init.d/aop-server

#!/bin/bash
#
# Apex Office Print (AOP) Server
#
# chkconfig: 345 70 30
# description: AOP is a print server for Oracle Application Express and PL/SQL
# processname: APEXOfficePrint

# Source function library.
. /etc/init.d/functions

RETVAL=0
AOP_HOME=/opt/aop/v3.0/server
AOP_PROCESS_NAME=APEXOfficePrint
AOP_EXECUTABLE_NAME=APEXOfficePrintRH64
AOP_PORT=8010
LIBREOFFICE_HOME=/opt/libreoffice5.3

PATH=$LIBREOFFICE_HOME/program:$PATH
export PATH

start() {
        echo -n "Starting $AOP_PROCESS_NAME "
        echo -n "Current path is $PATH"
        $AOP_HOME/$AOP_EXECUTABLE_NAME -p $AOP_PORT -s $AOP_HOME &
        RETVAL=$?
        echo
        return $RETVAL
}

stop() {
        echo -n "Shutting down $AOP_PROCESS_NAME: "
        pkill $AOP_PROCESS_NAME
        RETVAL=$?
        echo
        return $RETVAL
}

status() {
        echo -n "TODO: Print $AOP_PROCESS_NAME status here... "
        RETVAL=$?
        return $RETVAL
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;
    status)
        status
        ;;
    restart)
        stop
        start
        ;;
    *)
        echo "Usage: $prog {start|stop|status|restart}"
        exit 1
        ;;
esac
exit $RETVAL

In case you need to install LibreOffice on Linux, here're the steps (see also other section):

# download LibreOffice for the PDF converter
cd /tmp
wget http://download.documentfoundation.org/libreoffice/stable/5.3.2/rpm/x86_64/LibreOffice_5.3.2_Linux_x86-64_rpm.tar.gz

# make sure no old versions exist
yum remove openoffice* libreoffice*

# extract tar
tar -xvf LibreOffice_5.3.2_Linux_x86-64_rpm.tar.gz

# install
cd /tmp/LibreOffice_5.3.2.2_Linux_x86-64_rpm/RPMS/
yum localinstall *.rpm

# install some missing dependencies (depending your linux version this is not necessary)
yum install cairo.x86_64
yum install cups.x86_64
yum install mesa-libGL.x86_64

# install Java dependency (not necessary if you already have Java)
yum install java-1.8.0-openjdk.x86_64

8.7 Installation errors

Linux installation port error

If you encounter this error:

-------------------------------------------------
Error on Thu Feb 04 2016 19:33:35 GMT-0800 (PST)
-------------------------------------------------
listen EADDRINUSE

It means that the port you tried running AOP server is already taken and you need to change it by running this command:

./APEXOfficePrintRH64 –p xxxx (replace xxxx by a free port)

9 Tutorials

You can check the APEX Office Print tutorial page to see tutorials and videos.

10 Troubleshooting

10.1 Output generates invalid Office documents

In case Office documents are not opening as expected, probably there was an issue with the JSON that was send to the AOP server. If you run in the AOP Cloud, you can enable Remote Debugging, which you can access when you login in your APEX Office Print dashboard at https://www.apexofficeprint.com. If you're running the on-premise version of AOP, on the server there is a server_error.log file that contains the errors it found during rendering. First step is to check that file for more information. Next make sure that your JSON is valid by validating the JSON in for example http://jsonlint.com or by doing Remote Debug (see further on in this document)

10.2 Invalid JSON generated by apex_webservice.make_rest_request

Make sure APEX 5.0.4 or higher is installed. To download it login to support.oracle.com.

10.3 Invalid JSON generated by apex_json

Make sure you install an additional patch for APEX 5.0.4 PSE 24341756 or for APEX 5.1.0 PSE 25650850 or for APEX 5.1.1 PSE 25853436 available at https://support.oracle.com. Those will fix issues with apex_json which is used behind the scenes in AOP.

You also might hit Oracle database bug (in 12.1.0.2). A patch is made available on https://support.oracle.com; search for patch #21424376. You won't experience this issue in 11.2.0.4 or 12.2.

When using PL/SQL Function returning JSON, you might hit a bug in apex_plugin_util, which can be fixed by applying patch 26048323.

Finally if you still encounter issues, use ORDS (SQL Workshop > RESTful Services) as your datasource. ORDS will generate the JSON instead of apex_json and AOP will use that JSON instead (see 3.5.4 Data Source - URL with ORDS RESTful Web Service).

10.4 Running on Oracle Cloud

If you are running on Oracle Cloud you have to use HTTPS in the AOP Plugin Component setting and when you call the aop_api3_pkg package. Use https://www.apexrnd.be/aop as the url.

10.5 Invalid PDF file

If you're running the on-premise version of AOP, check a file on the server called server_error.log or server_uncaugt_exceptions.log and see which error you get. Try to run your example in the same format as your template. If this also fails, there's an issue with the JSON or the template. Check your template and data again. If you receive your document fine, but PDF is not working, there's an issue finding LibreOffice or MS Office. Make sure those programs are installed correctly. Try to do a manual conversion on the command line in those tools and see if that works. If not, check the error and try to reinstall. You can also do Remote Debugging (see further on in this document), which will help identifying the real issue. If everything fails, please contact support@apexofficeprint.com.

10.6 ORA-29273: HTTP request failed

ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-24247: network access denied by access control list (ACL)

Make sure APEX_050000 schema has the rights to connect to APEX Office Print (http(s)://www.apexofficeprint.com for the Cloud version or your local url in case of the on-premise version). The script to correct the issue can be found here: https://docs.oracle.com/cd/E59726_01/install.50/e39144/listener.htm#HTMIG29162 (use the correct script for your database version - for XE use prior to 12c)

10.7 ORA-29024: Certificate validation failure

ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1130 ORA-29024: Certificate validation failure

In case you run over HTTPS you need to take into account the certificates. There're two ways to get around this:

Here's an example when in the plugin you would specify a local address: http://apexrnd.localdomain/aop/

<VirtualHost *:80>
ServerName  apexrnd.localdomain
ServerAlias apexrnd.localdomain 
RewriteEngine On
ProxyVia On
ProxyRequests Off
SSLProxyEngine On
ProxyPass        /aop/   https://www.apexofficeprint.com/api/
ProxyPassReverse /aop/   https://www.apexofficeprint.com/api/
</VirtualHost>

10.8 Error generating Excel and Powerpoint with AOP Trial

If you don't activate the on-premise version of AOP it will run in Trial mode. This mode is not able to generate xlsx and pptx files, but it will work with docx and pdf, but those outputs will contain a trial watermark. The 100 day free cloud version is not limited and you can do everything on there. When you buy APEX Office Print you can activate your on-premise version and all restrictions will be removed.

10.9 How to read and convert documents (docx, xlsx, pptx, pdf) on Linux

You need to install LibreOffice https://www.libreoffice.org/download/libreoffice-fresh/. The steps to do this are outlined below:

Make a connection to your server with ssh or putty, but make sure you don't forward or tunnel your X Display.

Install supporting packages:

yum install wget

Download LibreOffice

cd /tmp
wget http://download.documentfoundation.org/libreoffice/stable/5.2.2/rpm/x86_64/LibreOffice_5.2.2_Linux_x86-64_rpm.tar.gz

Install LibreOffice

tar xzvf LibreOffice_5.2.2_Linux_x86-64_rpm.tar.gz
cd LibreOffice_5.2.2.2_Linux_x86-64_rpm/RPMS
yum localinstall *.rpm
- or you can do rpm -ivh *.rpm

Add LibreOffice to the profile for your user (as it needs to be able to find soffice)

vi /etc/profile
export PATH=$PATH:/opt/libreoffice5.1/program
source /etc/profile

Or add a symbolic link to LibreOffice

ln -s /opt/libreoffice5.2/program/soffice /usr/sbin/soffice

Check the version of LibreOffice and try to run a conversion

soffice --version
soffice --headless --invisible --convert-to pdf --outdir /tmp aop_interactive.docx

If you get: Fontconfig warning: ignoring UTF-8: not a valid region tag

echo "$LC_CTYPE" 
|-> you probably have UTF-8 defined; unset it
export LC_CTYPE=""

Make sure you restart APEX Office Print after installing LibreOffice.

Note: LibreOffice 5.3 is not yet compatible with APEX Office Print, due to a bug in LibreOffice 5.3. Note: Depending the version of LibreOffice converting to HTML from docx, xlsx, pptx might include the images as base64 or include a link.

10.10 ORA-31011: XML parsing failed, ORA-19202: Error occurred in XML processing, LPX-00651: VM Stack overflow

You need to install an additional patch for APEX 5.0.4 which will fix this issue. Search in https://support.oracle.com for PSE 24341756.

10.11 Error occured while aquiring license

You receive: "Error occured while aquiring license. Please make sure that your API key is correct and that you have enough printing credits. Contact AOP if the problem persists."

This means you ran out of credits. Go to https://www.apexofficeprint.com and upgrade your package or send an email to support@apexofficeprint.com to see what we can do for your case.

10.12 SyntaxError: unexpected token P in JSON at position 0

If you are using APEX 5.1, the Dynamic Action plugin will always work, whereas the Process plugin might give this error. When you put the process in the Processing part, it will only work if the “Reload on Submit” attribute (of the page) is set to “Always” (note this attribute is new in 5.1). This is due to a change how APEX 5.1 is handling Page Processing. If you would import an APEX 5.0 app in 5.1 by default it’s set to Always reload on submit, but if you create a brand new app in 5.1 it’s set to “Only for Success” and then the process plugin is not working. Alternative you can put the AOP Process to be After Header, and make it conditional, than it will work regardless of the setting of "Reload on Submit".

10.13 Issues with Oracle XE

When using Oracle XE, make sure you have the execute grant on the UTL_HTTP package.

GRANT EXECUTE ON SYS.UTL_HTTP TO my_user;

If you receive "ORA-20000: Issue returned by AOP Service (REST call). Please verify the logs on the server. Returned HTTP code: . (code: -29273)", see section 10.6.

10.13 Chinese and other language and font support

If you need special characters or language support, make sure the necessary fonts and languages are on your system. For example to add Chinese support on RHEL Linux do:

yum install "@Chinese Support"

Further more if you want to install additional fonts here's a good link. Our AOP Cloud is also supporting Google Noto fonts.

Installing a font is nothing more than installing the font on your system. So for example on (RedHat) Linux we copy the *.ttf files (or directory) to /usr/share/fonts/ and run "fc-cache -f -v"

Depending if you have a GUI (Linux/Windows) you can just double click on the font and it will install it in your system. If your system recognises is, MS Office or LibreOffice should be able to use it for the PDF conversion.

For barcodes, you could also choose to install a barcode font, for example Free 3of9 or http://www.dafont.com/3of9-barcode.font. Barcode fonts are more performant than images.

10.14 Running AOP installed in a single schema but shared across multiple schemas

It is possible to install AOP only once and call it from other places. In order to do this, after you install AOP - go to the package AOP_api3_PKG and AOP_plsql3_PKG and remove the AUTHID CURRENT_USER and recompile.

10.15 Running on-premise AOP under HTTPS

We recommend setting up an Apache Reverse Proxy which is doing the SSL in front of AOP. From Apache to AOP it would be unencrypted, but if it’s on the same machine as Apache and the port of AOP is not open and only accessible by localhost, we believe you're save.

To prevent access to AOP other than the Apache Reverse Proxy, you can do (on Linux):

iptables -A INPUT -p tcp --dport 8010 -s 127.0.0.0/8 -j ACCEPT
iptables -A INPUT -p tcp --dport 8010 -j DROP

(instead of DROP you can use REJECT too)

So that would mean only a program on localhost (like Apache) can connect to port 8010, all others are rejected.

10.16 The requested URL has been prohibited.

If in APEX you force all outgoing connections to be HTTPS by setting: Manage Instance -> Security -> HTTP Protocol -> Require Outbound HTTPS -> No.

make sure you're calling AOP also with HTTPS. If you're calling the AOP cloud (https://www.apexofficeprint.com/api) make sure to load the certificate in your database or setup a proxy on your end.

10.17 PDF output suddenly stopt working

Probably LibreOffice or MS Office process is stuck. A way to solve this is running a script that checks for long running processes and that kills those.

11 Debugging in APEX

AOP is fully instrumented with APEX debug messages, so when you turn debugging on in your APEX application you will see many AOP: ... calls.

12 Debugging in PL/SQL

If you're scheduling reports or calling the AOP packages with PL/SQL you can debug straight from PL/SQL. Here's an example:

declare
  l_binds           wwv_flow_plugin_util.t_bind_list;
  l_return          blob;
  l_output_filename varchar2(100) := 'output';
begin  
  -- remove previous debug
  apex_debug.remove_debug_by_age(
    p_application_id  => 232,
    p_older_than_days => -1);  

  -- create an APEX session with the debug enabled
  aop_api3_pkg.create_apex_session(
    p_app_id       => 232, 
    p_enable_debug => 'Y');

  l_return := aop_api3_pkg.plsql_call_to_aop (
                p_data_type       => aop_api3_pkg.c_source_type_rpt,
                p_data_source     => 'ir1',
                p_template_type   => aop_api3_pkg.c_source_type_apex,
                p_template_source => 'aop_interactive.docx',
                p_output_type     => 'pdf',
                p_output_filename => l_output_filename,
                p_binds           => l_binds,
                p_aop_url         => 'http://www.apexofficeprint.com/api/',
                p_api_key         => '1C511A58ECC73874E0530100007FD01A',
                p_app_id          => 232,
                p_page_id         => 5,
                p_init_code       => 'aop_api3_pkg.g_language := ''en'';');                
end;
/

13 Remote Debugging

If you receive an error and you need some help of us, do following steps:

1) Go in your application to Shared Components > Component Settings > APEX Office Print (AOP) [Plug-in] and enable remote debugging (see parameters screenshot)

Note 1: you find your API key when you login in your dashboard on https://www.apexofficeprint.com

Note 2: Make sure APEX_050000 schema has the rights to connect to http(s)://www.apexofficeprint.com

2) Run your report again.

3) Login on https://www.apexofficeprint.com and go to Remote Debug

4) Click on the magnifying glass, and the JSON that was generated behind the scenes which is send to the AOP server component will be shown.

5) Investigate the received JSON:

-) JSON is valid, and valid output: this is how it should be.

-) JSON is invalid: this is probably due to your version of APEX. You need APEX 5.0.4 or 5.0 with all the patches linked to apex_json.

-) JSON is valid, but invalid Office file (Word, Excel, Powerpoint): this means that AOP couldn't merge your data with the template you provided. If the template and requested output is the same format (e.g. your template is in Word and you request a Word document); check your template again if all substitution strings are correct. If you believe everything is ok, click the button "Sent to Support" and contact support@apexofficeprint.com.

-) JSON is valid, but invalid PDF (or other output format): if the template and output format are different, a conversion is going on handled by LibreOffice or MS Office. Either the conversion goes wrong, or most likely the initial file before the conversion was already wrong. To debug further, set the output format to the same format as your template and run your report again. If the output is invalid, follow previous steps (see JSON is valid, but invalid Office file). If the output is ok, click the button "Sent to Support" and send an email to support@apexofficeprint.com as it means there's a bug in the conversion.

14 FAQ

-) Does AOP have to go on its own server, the database server or the application (ORDS) server?

You can choose. Having AOP on the same server as the database machine is most performant and easiest as you don’t have network connections to other servers. If you install AOP on it’s own server you can size and monitor that server better. AOP on the Application server is another option, as long as the database can connect to the AOP server component, the AOP server doesn't need to be accessible from the outside (clients). So it’s whatever you are most comfortable with.

-) Is there anything special we have to do with AOP to get it to work with SSL?

You would need to add a reverse proxy for the SSL, the AOP server itself doesn’t have SSL. Most people don’t do SSL as it’s only the database that needs to access the AOP server component, so typically you don’t even pass the network where others are.

-) How resource intensive is it? Does it eat up a lot of CPU, RAM, or hard drive space?

It depends how many prints you do… AOP initially consumes about 200MB RAM, but depending the prints it can go up to 2GB (a limit we put). You can sent an email to support@apexofficeprint.com if you want an AOP Server which can consume more memory (up to 4, 8 or 16GB). CPU is not much compared to the specs these days and hard drive space is about 200MB. The executable is about 150MB and we might create some temporary files (during pdf conversion) and a log and error file.

-) Do I have to install LibreOffice when we need Word and Excel output only?

No - LibreOffice or MS Office is only used when you want to convert from one format to another. So Word-Word or Excel-Excel is working without. But if you want to do for example Word->PDF you need LibreOffice or MS Office.

-) Can AOP print directly to a printer

AOP 3.1 will include the ability to print directly to an IP Printer, as long as it's available from the server AOP is running on. If you want to print to a local printer, check the Sample app how to print to an inline region, by adding a little bit of JavaScript to the dynamic action you can let the browser come up with a print window automatically.

-) Can you repeat the Table header in Word and Excel?

Yes, this is a setting in Office. When you right click on your header row in Word, you can go to Table Properties - Row and check the "Repeat as header row at the top of every page."

-) Does AOP support Pivots in Excel

Yes, you can use the transpose function in Excel to pivot your data.

15 Example Templates

Below we illustrate some example templates and the generated output.

Example 1

This is the input template:

This is the result after AOP has processed the template and the given data:

Example 2

This is the input template:

This is the result after AOP has processed the template and the given data:

Example 3

This is the input template:

This is the result after AOP has processed the template and the given data:

Example 4

This is the input template:

This is the result after AOP has processed the template and the given data:

Copyright

Copyright © 2015-2017, APEX R&D

All rights reserved.

Authors: Dimitri Gielis, Sunil Tandan and Lino Schildenfeld

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure that are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or de-compilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.