Generate Report with Word/Docx

When using the generateReport action, you may use one of several different engines or template types. One of the engines is the Docx engine. Here, you can read more about the Docx engine and how to use it.

Why Docx?

The output of the action is always a PDF file, regardless of the chosen engine. So, why choose one engine over another?

Engine	Ease of use	Recommended use-case
Docx	Familiar editing experience and minimal setup	Reports, invoices and text-heavy summaries
Xlsx	Familiar, but some setup required	Chart- and table-heavy outputs
Html	Requires technical knowledge	Complex styles and layouts which cannot easily be created in Excel or Word
Markdown	Simple formatting, moderate setup	Fast prototype reports

Specifically, Docx is an engine where the templates are visually intuitive, written in a program which is familiar to many, and is fast to develop and make changes to.

The weaknesses of Docx are that it cannot do as complex calculations as Xlsx, and it is not as flexible or precise as Html. However, Docx is still a good choice for most use-cases.

Building the template

Your template file must be a single Word (.docx) file named index.docx. To insert data from your app into the document, we use {{ and }} syntax. We call these, and the text between them, "placeholders". For example, {{myVariable}} is a placeholder. The text of these placeholders will be replaced with the values of the variables or named range named myVariable in your app file - exactly how is explained in the following sections.

Text

Text and values, which are single-cell in your app file, can be inserted into your template using {{ and }}. For example, if you have a variable named myVariable in your app file, you can insert its value into your template using {{myVariable}}.

Numbers formatted as percentages in Excel will be inserted as percentages in the template.

The inserted text inherits the styling of the placeholder. For example, if you turn the placeholder red and bold in your Docx template, the inserted text becomes red and bold.

Images

To include an image from the app, the image URL must exist as a variable or single-cell named range. Then, you can insert the image via the placeholder {{@ and }}. For example, if the URL of an image is stored in the variable myImage in your app file, then the placeholder {{@myImage}} will insert the image. If using this method, the image will be included in the PDF using default styling and the size of the image at the URL.

If you wish to control the styling of the image, you can instead do the following:

1. Insert any image in your template file
2. Style the inserted image - size, rotation, etc. as you wish the final image to have
3. If the URL to the image in your app file is stored in the variable myImage, then set the "Alternative text" of the image to the placeholder {{myImage}}. Note that there is no @ in the "Alternative text"

Tables

The placeholder for a table uses {{# and }} syntax. The name must be the name of an Excel table. E.g. if you have an Excel table in your app named "myTable", then you can insert the table into your template using {{#myTable}}.

The data of the Excel table is inserted into the table in the template, but the column headers are not.

This creates a page-wide table with default styling. If you wish to control the styling of the table, you can use action options.

Border styling

The cell borders of a table have three properties: color, width, and style. To set a border property of your table {{#myTable}}, use the action option myTable.border.<property>.

For example, to color the borders of myTable red, let the action option be myTable.border.color and its value be red.

Row styling

The rows of a table have five properties: textColor, backgroundColor, bold, fontFamily, and fontSize.

Additionally, when you set a row property, you can set it for the rows all, header, footer, oddRows, evenRows. Specific overrides general, e.g. the styles of myTable.header will override the styles of myTable.all for the first row of the table.

For example, if your table is myTable, you can set the property myTable.header.fontFamily to Comic Sans MS.

Charts

You can use charts created in a template, with data from charts in your app. However, the chart type, styling and title are preserved.

If you have a chart in your app and it has the variable name myChart, then you can insert its data into a chart in your template by setting the "Alternative text" of the template chart to {{myChart}}.

The data of the chart in the template must be of the same height and width as the data of the chart in the app.

The supported chart types are:

Example app and template

Here follows an example of how to use the Word template to create a report. We will create a summary of projects' performances.

You can download the app's .xlsx file, and download the template .docx file.

The app

The app also needs to prepare charts and tables for the export. Two charts and one table are included in the app, but none of them are shown. The chart data is passed to the template by assigning a variable name to the variable= string in the chart's UI cell, and the table is passed to the template by setting the table's name.

The action itself is defined to use the Docx engine, and to apply some styling to the table. The action button is also made to be hidden, and an html button is used to activate the action instead.

The template

Now that the app is ready, we create a template. We give it the same name as in the app's action, and we create a new blank Word document named index.docx.

We add a header to the template, and use Word's page numbering in the top right. We also add the project name placeholder {{project_name}} in the header. Then, the name of the project, which the user inputs in the app, will be shown in the header of each page of the resulting PDF. We italicize the placeholder, so the project's name will be italicized in the header.

We are not limited and may use the same placeholders multiple times. So, we add {{project_name}} as a subtitle as well.

Using the values input into and calculated by the app, we create an introductory paragraph of text. Note that since the formatting of the {{budget_used}} is in percentage in the app's spreadsheet, the value will be rendered as percent in the PDF.

Next, the template shows the project completion as a "gauge chart", similar to a speedometer. Since word does not have a native "gauge chart", we simulate one by creating a doughnut chart, setting the angle of the first slice to 270 degrees, and styling the third series transparent.

The data for the chart is selected to be of the same dimensions as the chart data from the app. The "Alternative Text" of the chart is set to {{chart_completion}} in order to receive the data from the app's chart.

To chart the percentage of budget used vs time used, in two bars, a barchart is used. The chart has one series and two categories. Additionally, the data labels are formatted as percentages, and the "Alternative Text" of the chart is set to chart_budget.

To include the table, the placeholder {{#table_tasks}} is used. The styling of the table is already defined by the action options.

You can test the app and template below:

Troubleshooting

Sometimes, you get an unexpected result of your action. Here are the most common issues, and some steps you can take to fix them.

Missing data

If data does not show in your final report, then look to see if the placeholder is still there.

• If the placeholder is still in the output PDF, then there was no variable or named range in the app which matches the placeholder. In other words, there is probably a typo or misspelling.
• On the other hand, if the placeholder has been replaced, then the variable or named range in the app is set to an empty value. You can use the debug functionality "Download Current State" from the "Debug" section of the app's sidebar to investigate the cause.

Date formatting

When exporting the value of an Excel date/time cell to the Docx engine, dates may sometimes not be formatted the way you wish. To remedy this, you can use the Excel formula TEXT to format the value in your cell to a string in the format you wish to display. For example, =TEXT(A1, "MM/DD/YYYY") would format the date as "month/day/year".

Text being "pushed around"

Often, the length of the placeholder is not the same as the length of the text from the app. For smaller text snippets or data values, that usually does not cause problems, but for longer text, it can cause other text or images to be pushed around.

To remedy this, you can use the Excel formula LEN to find the length of the text, and then use the formula LEFT or RIGHT to trim the text to a length appropriate for the placeholder. For example, =LEFT(A1, 100) would trim the text to at most 100 characters.