Generate Report with Excel/Xlsx

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

Why Xlsx?

When you use the Generate A PDF Report action, the output is always a PDF file. However, the Xlsx engine is a powerful tool for creating reports with many charts and tables. Here follows an overview of the different engines and their recommended use-cases.

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

The Xlsx engine templates are written in Excel, which you, as a developer of a Molnify application, likely are both familiar with and work quickly in already. The Excel templates excel at visualizing data, creating charts and communicating data-heavy results.

However, the Xlsx engine is not as flexible as the Html engine, nor is it as easy to design a text-heavy page as with the Docx engine.

Building the template

Your template file must be a single Excel (.xlsx) file named index.xlsx. The engine uses tables and named ranges to transfer data from the app into the document. Exactly how is explained in the following sections.

Layout

Your template file determines the layout of the resulting PDF. The following general rules apply:

1. Any hidden sheets are not included in the generated PDF.
2. After the data has been transferred to the template, formulas, charts and conditional formatting are updated before the PDF is created. In other words, you can include formulas and charts in your template that depend on the data sent from the Molnify application.
3. The Excel Page Layout determines where the engine breaks pages.
4. Each new sheet in the template is the start of a new page in the PDF.

Single-cell values

You can transfer single-cell values from the app file to the template. The process of doing so is as follows:

1. Create a single-cell named range in the app file, named e.g. myValue.
2. Create a single-cell named range in the template file with the same name.
3. Then, when the PDF is generated, the value of the single-cell named range in the app file will be copied over to the template and used in the PDF.

Note that the named range must be single-cell. To transfer multiple cells, use tables instead.

Tables

To copy many cells of data, you can use Excel tables instead of single-cell named ranges. Then, the process is the same as for single-cell values, but you name the two tables the same instead.

Example app and template

This section shows an example of a Molnify app and a report template that uses the Xlsx engine. The app will be used to generate investment portfolio summaries.

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

The app

The app lets the user pick a client and one of the client's investment portfolios, and a date. Then, the user can click on a button to generate a PDF report for the chosen portfolio.

We define the inputs of the app as single-cell named ranges to pass their values along to the report template. To format the date, we use the Excel formula =TEXT(G5, "mmmm d, yyyy").

For the client and portfolio data, we create two database tables. One table contains the client information, and the other table contains information about the portfolios. The client table is named data_<appid>_0, and the portfolio table is named data_<appid>_1.

Column Name	Data type	Default value	Nullable	Auto increment	Is Primary Key
_molnify_application_id	VARCHAR		true	false	No
_molnify_timestamp	TIMESTAMP	CURRENT_TIMESTAMP	false	false	No
_molnify_user_email	VARCHAR		true	false	No
_molnify_user_ip	VARCHAR		true	false	No
name	VARCHAR		true	false	No
recordId	INT		false	true	Yes

For our demo, we have ten fictive clients. This CSV file lists the client data. In a real-world use-case, we can imagine this data being entered via a separate Molnify application (or a different tab in the same!), or even ingested via API.

Column Name	Data type	Default value	Nullable	Auto increment	Is Primary Key
_molnify_application_id	VARCHAR		true	false	No
_molnify_timestamp	TIMESTAMP	CURRENT_TIMESTAMP	false	false	No
_molnify_user_email	VARCHAR		true	false	No
_molnify_user_ip	VARCHAR		true	false	No
allocation_bond	DOUBLE		false	false	No
client_id	INT		false	false	No
fees_annual	DOUBLE		false	false	No
name	VARCHAR		true	false	No
net_return_last_year	DOUBLE		false	false	No
recordId	INT		false	true	Yes
std_deviation	DOUBLE		false	false	No
value_beginning	DOUBLE		false	false	No
value_market	DOUBLE		false	false	No

Each of our demo clients has one, two or three portfolios. This CSV file contains the portfolio data for all twenty demo portfolios.

The data from the database tables are read into the app by two autofills, defined in the metadata.

The autofill to read client data, autofill.clients, contains the query: SELECT name, recordId FROM `data_demo-generatereport-xlsx_0`. Note that the backticks (`) are needed in the autofill to escape the dashes (-) in the table name.

The autofill to read portfolio data, autofill.portfolios, instead contains an Excel formula: =IFERROR("SELECT name, recordId, value_beginning, value_market, net_return_last_year, std_deviation, fees_annual, allocation_bond FROM `data_demo-generatereport-xlsx_1` WHERE client_id = "&VLOOKUP(G3,clients,2,FALSE),""). This formula uses the VLOOKUP function to look up the portfolio name based on the selected client's ID. The formula also uses the IFERROR function to return an empty string if no client has been chosen yet.

The results of the two autofills are used to populate the named ranges clients and portfolios in the app.

To read more about autofills and database tables, see the guide to databases in Molnify.

The app also has a table to send portfolio data to the template. To do so, we create a table in the app file with the name table_portfolios. It uses the VLOOKUP function to look up the portfolio's values based on the selected portfolio.

The action is configured to use the Xlsx engine. The action button is also made to be hidden, and an html button is used to activate the action instead.

The template

With the app finished, we can now create a template for the report. We give it the same name as in the app's action, and we create a new blank Excel document named index.xlsx.

First, we prepare a sheet in our template to receive data. On this sheet, we also prepare the names "Stocks" and "Bonds" for a chart, and we calculate the percentage of assets which are stocks: 1-allocation_bonds.

Second, we change the view in Excel to "Page Layout" (under "View"), and we ensure that the page is set to one page wide and tall (under "Page Layout").

Then, we create a header. However, since Excel's built-in header does not permit formulas, we create a "fake" header by placing the title one row down on the page.

We refer to the values on the data sheet to "fetch" the values to be displayed. The "Net Return", often seen as the most important metric, is highlighted in bold.

The pie chart is created by selecting a single series on the values of the "Stocks" and "Bonds" allocation values from the data sheet.

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 check the following:

• Is the named range or table spelled the same in both files?
• If it is a named range, is it a single cell in both the app file and the template file?
• Ensure that you are not attempting to send data from a named range to a table, or from a table to a named range.

Layout and visual anomalies

• If a specific sheet is not appearing in the final PDF, check if that sheet is hidden in the template. Hidden sheets are automatically excluded from the report.
• If the PDF cuts off a chart or table in the middle, check the Print Area settings in the excel template. Keep in mind that any text which is inserted into named ranges may be longer than the cell which it is inserted into. This can cause the text to be cut off or overflow/overlap with other cells/text.

No report is generated

Some setup errors can prevent the report from generating at all:

• If the name of the template file is incorrect, then the report may not be generated. The template must be named exactly index.xlsx.
• Verify that the file is an .xlsx file, and not .xls or .xlsm.