Automatically Filling Excel Sheets with SQL Query Results

I often need to copy SQL query results into an Excel sheet and send them to my colleagues. The Excel spreadsheet to be filled out is similar to the diagram below. The actual spreadsheet is more complex than this one.

Now, I have found a simple way to automate this task. It doesn't require me to write code and allows for changing the format of the spreadsheet at any time.

Note: SQLMessenger 2.0 installation is required before proceeding with the following steps.

First, modify the table template to look like this:

Mark the cells that need to be filled with SQL query results as "Data Cell". The format for a data cell marker is <%DataCellName%>. 

Here, we can use formulas to generate data cell markers. For example, in the "State" (A3) cell in the figure above, we can use the formula ="<%"&A2&"%>" to generate the data 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.

After modifying the Excel template, create a task in SQLMessenger, and add an attachment template of type "Dynamic Attachment File" 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 to add an SQL query to the template.

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

Set corresponding Data Cells for each SQL field that we want to display in the Excel table.

Add another query to fill in the Total row in the same way.

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

The following image shows the Excel sheet filled out after executing the template:

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

At the end, I have also compiled some Q&A about this feature.

Q: Can this system automatically send the filled-out table via email to colleagues?

A: Yes, SQLMessenger can automatically send the table as an email attachment or in the email body to specified recipients. It depends on your configuration.  Setting Recipients for Tasks

Q: Can this task be scheduled to run automatically at specific times I request, such as every day at 8 AM or 2 PM?

A: Yes. You can configure "Task Schedules" for the task to enable it to run automatically at scheduled times. Using Task Schedules

Q: I would like to individually query personal reports (such as sales performance reports) for multiple colleagues and then send them via email to each. Can this be done?

A: Yes. You can use the "Information Distribute" feature to achieve point-to-point distribution of reports.  Using Information Distribution Task

Q: Is it possible to convert SQL query results directly into an Excel spreadsheet without using a template?

A: Yes. You can use the "Simple Table" to do this.  Using Simple Tables