1 Overview

In SQLMessenger, you can create an Excel template and have SQLMessenger fill the query results from SQL statements into the specified cells in the template, thereby achieving automatic filling of Excel sheets. This article introduces how to use the automatic Excel sheet filling feature through a simple example.

Below is the Excel sheet we want to fill and the corresponding SQL queries.

Excel table "Sales Report" and SQL queries

2 Design Template Table

Based on the original sheet, mark the cells that need to be filled with SQL query results as "Data Cell". The format for a data cell marker is <%DataCellName%>. The modified table is as follows:

The modified template table

You can use formulas to generate data cell markers in the data cells. For example, in the "State" (A3) cell in the figure above, you can use the formula ="<%"&A2&"%>" to generate the cell marker. Then, copy the A3 cell to the B3-E3 cells to quickly generate the data cell markers for the B3 to E3 cells.

Use formulas to generate data cell markers

3 Adding Attachment Template to The Task

Create a task in SQLMessenger and add an attachment to the task with the type "Dynamic Attachment File".

Add an attachment to the task

Select "Customize Spreadsheet Template" for the Template Type, then click the "Select File" button to import the designed Excel template sheet.

After importing the template file, click the "New Query" button on the SQL List toolbar to add an SQL query to the template.

Select Template Type and import the designed Excel template sheet

In the Create SQL Query wizard, select the data source and enter the SQL statement, following the wizard's prompts to proceed.

Enter sql statement in the "Create SQL Query" wizard

In the wizard, you need to set corresponding Data Cells for each SQL field that you want to display in the table. The query results for each field will be displayed in the corresponding data cells you set.

Set corresponding Data Cells for each SQL field

Use the above method to add another SQL query statement to the attachment template, which queries the contents of the Total row.

Add another SQL query statement to the attachment template

Set corresponding Data Cells for each SQL field in the second SQL query

3 Creating Template Table

After completing the attachment template configuration, click the "Deploy" button for the new task configuration to take effect.

4 Previewing the Template

After configuring the SQL query statements, click the "Preview" button to preview the template execution results.

Click the "Preview" button to preview the template execution results

The attachment Excel sheet generated after the task runs

After configuration, click the "Deploy" button to deploy the task configuration. In the future, execute this task whenever you need to fill out this sheet.

5 Q&A

5.1 Can I modify the SQL query conditions to retrieve sales reports for different cities?

Yes. You can use variables as query parameters in SQL to achieve retrieving sales reports for different cities. Using Variables in SQL Statements

5.2 Can the system automatically send the attachment table to my colleague?

Yes. You can modify the task type to "HTML Email" or "Text Email" and set the recipient accordingly. After the task is executed, the attachment file will be sent to the specified recipient via email. Task Config

5.4 Can the task be automatically executed at the time I set?

Yes. You can configure "Task Schedules" for the task, and the system will automatically execute the task according to the schedule you configured. Using Task Schedules

Using Custom Excel Templates, Using SQL Query in Templates, Using Task Schedules