Using Custom Excel Templates
In SQLMessenger, if the Simple Table cannot meet your requirements, you can design an Excel table according to your needs, and then SQLMessenger can fill in the SQL query results into the specified locations in the table. You can use formulas, charts, and other content in the table.
Custom Excel template
The Excel attachment generated by the task
Note:
Custom Excel templates do not support the use of variables. Contents like "#VariableName#" will be kept as is and not processed.
If the custom Excel template includes pivot tables, you need to install Microsoft Excel 2010 or a later version to properly refresh the pivot table content.
2 Designing Custom Excel Templates
When designing an Excel template file, you can first create a table file using Excel software. In the table, add a "Data Cell" label to the cells where you want to populate SQL query results, and set up formulas, charts, and other content for the table. Then, in the SQLMessenger task editor, create an attachment template, select "Customize Spreadsheet Template" as the template type, and import your designed template file into SQLMessenger. After that, configure the SQL statement corresponding to the template and set up the mapping between the SQL fields and the data cells in the template.
You can set Data Cell markers in the cells of your Excel template table where you need to fill in the SQL query results.
The format for a data cell marker is <%DataCellName%>.
In this marker, "<%;" and "%>" are the delimiters for the data cell. DataCellName is the name of the data cell.
The data cells in the Excel template
After importing this template in the task configuration, SQLMessenger will identify these data cells in the template, and then you can set the corresponding SQL fields for these data cells.
Set the corresponding SQL fields for the data cells
After the task is executed, the system will fill the data from the fields of the SQL query results into the corresponding data cells.
The query result data is filled into the corresponding data cells
In SQLMessenger, if your Excel template's formulas reference data cells that correspond to SQL statements that return multiple rows of data, you need to modify the parameters of the formulas to ensure the accuracy of the calculation results.
For example, cell B6 in the table uses the "SUM(B5)" formula to calculate the total "Sales Volume". After the task is executed, the result of the SUM formula in cell B6 may not match the expected outcome.
The result of the formula calculation is incorrect
To rectify this issue, we can create a Name "Sales_Volume" for cell B5 in the template file. Then, modify the formula in cell B6 to "SUM(Sales_Volume)".
Select the B5 cell, press Ctrl+F3 to open the "Name Manager" dialog box and click the "New" button
In the "New Name" dialog box, enter "Sales_Volume" in the "Name" text box, and select "=Sheet1!$B$5" in the "Refers To" field.
Set the corresponding range for the "Name"
Modify the formula in cell B6 to "SUM(Sales_Volume)"
After making the changes, import the updated template and re-run the task. We will then see that the calculation result of the formula is now correct.
The result after re-running the task
2.3 Using "Name" for the data source in Charts
Just like with formulas, when charts are used in a template, the data source of the chart also needs to be set to a "Name".
Edit data source for the chart
Here, the "Series Values" of the selected series should be set to "='SalesReport(template).xlsx'!Sales_Volume". The format of "Series Values" is "Template file name.xlsx!Name".
Set "Series Values" for the selected series
2.4 Use "Name" to Expand Cell Blocks
When not all cells in a row are filled by SQL fields, there might be cases where the cells are not fully copied, leading to incorrect table formatting.
The cells were not fully copied to the new area, and the table format is incorrect
To address this, you can create a Name in the template, include the data field cells and adjacent cells in that Name, and then set the Named Area of the SQL statement to the newly created Name. When the system processes this SQL statement, it will treat the entire Name's cells as a whole for copying, ensuring the table format remains correct.
Create a "Name" for "Sheet1!$A$5:$D$5"
In the task editor, re-import the template file and select the newly created Named Area ("List") for the SQL statement
The result after re-running the task