Although, there are many possibilities for transforming query results into an Excel file, I have found that there are no options that allow us to start a query in a batch and transform result into Excel file. If I were to use the Microsoft.Jet.OLEDB.4.0 provider, you have no control over transformation process. We could also transform a query result into an Excel file by using Microsoft DLLs, i.e. Microsoft.Office.Interop.Excel.dll and its companions Microsoft.Vbe.Interop.dll, office.dll, and stdole.dll. The drawback is that we must have Excel installed, which is not the case with this technique.

That was the reason why I try to develop custom solution with a help of well-known Excel provider e.g. third party tool. In the article, I will show how to develop custom .NET solution, which has as a result .NET assembly 'QueryToExcelSpreadSheetGear.dll'. By publishing our custom .NET assembly on the database server, we will extend the database model with a new stored procedure. The stored procedure '[Excel].[QueryToExcelSSG]' has many parameters and one of them is a T-SQL query that should be transformed into an Excel file. You can find .NET source code in the attachment of the article. 

Because this is a SQLCLR solution, you must enable the CLR if not already enabled.  To do this, execute T-SQL script displayed on the listing bellow.

-- Enable & Check CLR
sp_configure N'clr enabled', 1
GO
RECONFIGURE
GO
SELECT
 sc.*
FROM sys.configurations AS sc
WHERE sc.[name] = N'clr enabled'

We have to download the Excel library from this site: SpreadsheetGear 2017 for .NET (8.0.72.101) Note this is third party library. I have tested many Excel libraries and have found that this software, although commercial, provides the best performance. After downloading the package, please make sure that you know the correct path to the SpreadsheetGear2017.Core.dll.

Next, we have to register ‘SpreadsheetGear 2017.Core.dll’ on our database server and add the reference in the Visual Studio solution file attached with the article. In order to achieve the first step we have to execute T-SQL script shown bellow.

Microsoft designers divide SQLCLR assemblies according the permission set to ‘SAFE’,’EXTERNAL_ACCESS’, and ‘UNSAFE’. This assembly should be ‘UNSAFE’.  In this solution, we are playing with AdventureWorks2014 database. 

USE MASTER;
GO
--Replace 'D:\SpreadSheetGearNew\SpreadsheetGear2017.Core.DLL' with your path
CREATE ASYMMETRIC KEY akSpreadSeetGear FROM EXECUTABLE FILE = 'D:\SpreadSheetGearNew\SpreadsheetGear2017.Core.DLL';
GO
CREATE LOGIN sqlClrLogin FROM ASYMMETRIC KEY akSpreadSeetGear;
GO
GRANT UNSAFE ASSEMBLY TO sqlClrLogin;
GO
USE AdventureWorks2014;
GO
CREATE ASSEMBLY [SpreadsheetGear2017.Core] FROM 'D:\SpreadSheetGearNew\SpreadsheetGear2017.Core.DLL' WITH PERMISSION_SET = UNSAFE;  
GO

In order to achieve the second step, start Visual Studio Community Edition (or some other licensed Edition), open the solution file (you can find it in the attachment of the article) and add missing reference. As I wrote earlier, SpreadSheetGear is commercial software that cannot be distributed without license.

However, I found that after that process of publishing the Visual Studio solution takes much longer. That was the reason why I split the process  into two steps.

There are several parts of the solution on which I should comment. The first thing to do is to transform the result of the query into dataset. This is accomplished in ‘DataAccess.cs’ file as shown on the listing below.

 public static DataSet GetDataSet(string Query, bool isSp, SqlParameter[] listOfParams, string TableMapping,ref string html)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlConnection cnn = new SqlConnection("context connection=true"))
                {
                    using (SqlCommand command = new SqlCommand(Query, cnn))
                    {
                        cnn.Open();
                        if (isSp)
                            command.CommandType = CommandType.StoredProcedure;
                        if (listOfParams != null)
                        {
                            foreach (SqlParameter p in listOfParams)
                            {
                                command.Parameters.Add(p);
                            }
                        }
                        string[] tm = TableMapping.Split(';');
                        int i = 0;
                        using (SqlDataAdapter sqlAdp = new SqlDataAdapter())
                        {
                            sqlAdp.SelectCommand = command;
                            foreach (string s in tm)
                            {
                                string addOn = i == 0 ? "" : i.ToString().Trim();
                                sqlAdp.TableMappings.Add("Table" + addOn, s);
                                i++;
                            }
                            sqlAdp.Fill(ds);
                        }
                        cnn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                html += ex.Message;
            }
            return ds;
        }

'DataAccess.cs' uses almost classical ADO.NET approach. The most interesting thing here is the so-called context connection. According to the Microsoft documentation, the context connection allows you to execute T-SQL statements in the same context that was used when your code was invoked in the first place. In order to obtain the context connection, you must use the 'context connection' connection string keyword.

After getting the dataset, I apply some Excel formatting in 'ExcelMaker.cs'. This mean formatting columns, freezing the first column, apply some custom styles, and so on. 

My aim was to have every Excel file uniformly formatted. This mean that in the first row the title should be displayed. Some useful information such as the host name, the database server name, the date and time when Excel file is generated, the user  who is generated the file should be displayed in the second row. Then in the third row, column names should be displayed in the filter mode. 

Also, it is very useful when working with large Excel file to have first column frozen as well as first N rows. I was able to freeze the first column but unfortunately there is a bug in SSG assembly when you try to freeze the top row. In that case an exception is raised, telling us that 'System.Drawing.dll' should be loaded as well.

The code that transforms the dataset into Excel file is displyed in the listing bellow. 

public class ExcelMaker
    {
        public static bool ExportToExcel(DataSet ds, string mFile,  string title,ref string errorString)
        {
            bool retValue = true;
            try
            {
                SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(CultureInfo.CurrentCulture);
                int i = 0;
                string[] tm = title.Split(';');
                int i1 = 0;
                string errorMessage = string.Empty;
                string dataSource = DataAccess.GetResult("SELECT @@servername + '(' + DB_NAME() + ')';");
                string userName = DataAccess.GetResult("SELECT SYSTEM_USER");
                int counter2 = ds.Tables.Count;
                foreach (DataTable t in ds.Tables)
                {
                    int startRow = 2;
                    SpreadsheetGear.IWorksheets worksheets = workbook.Worksheets;
                    worksheets.Add();
                    worksheets.Name = t.TableName;
                    SpreadsheetGear.IWorksheet workSheet = worksheets;
                    //make a decoration
                    try
                    {
                        workSheet.WindowInfo.ScrollRow = 0;
                        //
                        //try
                        //{
                        //    //workSheet.WindowInfo.SplitRows = startRow;
                        //    workSheet.WindowInfo.SplitRows = startRow;
                        //}
                        //catch ( Exception ex)
                        //{
                        //}
                        workSheet.WindowInfo.ScrollColumn = 0;
                        workSheet.WindowInfo.SplitColumns = 1;
                        workSheet.WindowInfo.FreezePanes = true;
                    }
                    catch ( Exception ex)
                    {
                        errorMessage = ex.Message;
                    }
                    //The title
                    string localTitle = tm.Length > i1 ? tm[i1] : "unknown";
                    SpreadsheetGear.IRange rangeTitle = workSheet.Cells[0, 0, 0, t.Columns.Count - 1];
                    ApplyFirstRow(rangeTitle, localTitle);
                    SpreadsheetGear.IRange rangeSecondRow = workSheet.Cells[1, 0, 1, t.Columns.Count - 1];
                    ApplySecondRow(rangeSecondRow, mFile, dataSource, userName);

                    SpreadsheetGear.IRange range2 = workSheet.Cells[startRow, 0, startRow, t.Columns.Count - 1];
                    //range2.AutoFilter();
                    int endIndex = t.Rows.Count + startRow + 1;
                    int counter = 0;
                    IRange header = workSheet.Cells[startRow, 0, startRow, t.Columns.Count-1];
                    header.Interior.ColorIndex = 34;
                    for (int j = 0; j <= t.Columns.Count - 1; j++)
                    {
                        workSheet.Cells[startRow, counter].NumberFormat = "@";
                        workSheet.Cells[startRow, counter].Value = t.Columns[j].ColumnName.Trim();
                        //try
                        //{
                        //    workSheet.Cells[startRow, counter].EntireColumn.AutoFit();
                        //}
                        //catch (Exception ex)
                        //{
                        //}
                        //workSheet.Cells[startRow, counter].AutoFilter(1, Type.Missing, AutoFilterOperator.And, Type.Missing, true);

                        //Napravi output format
                        if (((DataColumn)t.Columns[j]).DataType.Name.ToUpper().Contains(cDATE))
                        {
                            workSheet.Cells[startRow + 1, counter, endIndex, counter].NumberFormat = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
                           
                        }
                        else if (((DataColumn)t.Columns[j]).DataType.Name.ToUpper().Contains(cDECIMAL) || ((DataColumn)t.Columns[j]).DataType.Name.ToUpper().Contains(cINT))
                        {
                            if (((DataColumn)t.Columns[j]).DataType.Name.ToUpper().Contains(cDECIMAL))
                            {
                                workSheet.Cells[startRow + 1, counter, endIndex, counter].NumberFormat = "#,##0.00";
                            }
                            else
                            {
                                workSheet.Cells[startRow + 1, counter, endIndex, counter].NumberFormat = "#,##0";
                            }
                        }
                        else
                        {
                            workSheet.Cells[startRow + 1, counter, endIndex, counter].NumberFormat = "@";
                        }
                        counter += 1;
                    }
                    i += 1;
                    //Sadržaj
                    for (int j = 0; j <= t.Rows.Count - 1; j++)
                    {
                        DataRow currRow = t.Rows[j];
                        counter = 0;
                        //
                        SpreadsheetGear.IRange rangeRow = workSheet.Cells[j + startRow + 1, 0, j + startRow + 1, t.Columns.Count - 1];
                        //rangeRow.AutoFit();
                        //SpreadsheetGear.IInterior interiorRowColor = rangeRow.Interior;
                        //interiorRowColor.Color = System.Drawing.Color.WhiteSmoke;
                        //
                        for (int k = 0; k <= t.Columns.Count - 1; k++)
                        {
                            if (((DataColumn)t.Columns[k]).DataType.Name.ToUpper().Contains(cDATE))
                            {
                                workSheet.Cells[j + startRow + 1, counter].Value = object.ReferenceEquals(t.Rows[j][k], DBNull.Value) ?
                                    string.Empty : Convert.ToDateTime(t.Rows[j][k]).Hour.Equals(0) ?
                                    Convert.ToDateTime(t.Rows[j][k]).ToString(CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern) :
                                    Convert.ToDateTime(t.Rows[j][k]).ToString(System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern + " " + System.Globalization.CultureInfo.CurrentCulture.DateTimeFormat.LongTimePattern);
                            }
                            else if (((DataColumn)t.Columns[k]).DataType.Name.ToUpper().Contains(cBOOL))
                            {
                                if (!object.ReferenceEquals(t.Rows[j][k], DBNull.Value))
                                {
                                    workSheet.Cells[j + startRow + 1, counter].Value = Convert.ToBoolean(t.Rows[j][k]) == true ? "Yes" : "No";
                                }
                                else
                                {
                                    workSheet.Cells[j + startRow + 1, counter].Value = "NULL";
                                }
                            }
                            else if (((DataColumn)t.Columns[k]).DataType.Name.ToUpper().Contains(cDECIMAL) || ((DataColumn)t.Columns[k]).DataType.Name.ToUpper().Contains(cINT))
                            {
                                if (!object.ReferenceEquals(t.Rows[j][k], DBNull.Value))
                                {
                                    if (((DataColumn)t.Columns[k]).DataType.Name.ToUpper().Contains(cDECIMAL))
                                    {
                                        workSheet.Cells[j + startRow + 1, counter].Value = Convert.ToDecimal(t.Rows[j][k]);
                                    }
                                    else
                                    {
                                        workSheet.Cells[j + startRow + 1, counter].Value = Convert.ToInt32(t.Rows[j][k]);
                                    }
                                }
                            }
                            else
                            {
                                workSheet.Cells[j + startRow + 1, counter].Value = t.Rows[j][k].ToString().Trim();
                            }
                            counter += 1;
                        }
                    }
                    i1++;
                    workSheet.Cells[startRow, 0].EntireRow.AutoFilter(0, Type.Missing, AutoFilterOperator.And, Type.Missing, true);
                    for (int j = 0; j <= t.Columns.Count - 1; j++)
                    {
                        workSheet.Cells[startRow, j].ColumnWidth = workSheet.Cells[startRow, j].ColumnWidth * 2;
                    }
                           
                   
                }
                workbook.Sheets[0].Select();
                workbook.Sheets[workbook.Sheets.Count-1].Delete();
                workbook.FullName = mFile;
               
                workbook.Save();
                //workbook.SaveAs(mFile, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
            }
            catch (Exception ex)
            {
                retValue = false;
                errorString = ex.Message;
            }
            return retValue;
        }

In the solution, there are several T-SQL scripts. The SQLCLR framework allow us to define only one T-SQL as pre-deployed. It means to be executed before the assembly is published. Similarly, there is a post-deploy script that is executed after the assembly is published. In the pre-deployed script, named PreDeployment.sql, I first create the EXCEL schema, then I create an asymmetric key based on the solution snk file. Then I create a login and grant the unsafe assembly permission to that login. In the post deployment script, I transfer the resulting stored procedure from the default DBO schema to newly created EXCEL schema. To test installation, expand Programmability->Assembly node and make sure you have both assemblies there (as shown in the image below).

Then you check Programmability->Stored Procedures and make sure that the stored procedure, EXCEL.QueryToExcelSSG, is there, as shown in the image below.

The stored procedure takes five parameters as shown in the image below

In the table below you can find the description of the parameters.

Finally, let's do some testing. Execute the following T-SQL script, which will transform your query results into an Excel file with one worksheet

EXEC [Excel].[QueryToExcelSSG] 'SELECT BusinessEntityID , PersonType , FirstName , MiddleName , LastName , ModifiedDate
FROM PERSON.PERSON;
' , NULL , 'C:\TMP\AdventureWorksTest.XLSX' , 'Person.Person listing' , 'Person';

You can see the results in the image below.

Of course, ‘SELECT *’ is allowed too. Therefore, our second test should be:

EXEC [Excel].[QueryToExcelSSG] 'SELECT * FROM PERSON.PERSON; ' , NULL , 'C:\TMP\AdventureWorksTest.XLSX' , 'Person.Person listing' , 'Person';

We could specify multiple queries. Each query should be separated with a semicolon. In addition, we could provide sheets name separated by semicolon, as well as titles. In the example bellow we combine three T-SQL queries. The result of the first query is the table 'Person.Person'. The second query will return the table 'HumenResource.Employee', and the third query will return the table 'Sales.SalesOrderHeader'.

EXEC [Excel].[QueryToExcelSSG] 'SELECT BusinessEntityID , PersonType , FirstName , MiddleName , LastName , ModifiedDate
FROM PERSON.PERSON;
SELECT BusinessEntityID , NationalIDNumber , BirthDate , MaritalStatus , Gender , HireDate
FROM HumanResources.Employee;
SELECT SalesOrderID , salesordernumber , purchaseordernumber , OrderDate , ShipDate , SubTotal , TaxAmt , TotalDue
FROM Sales.SalesOrderHeader;
' , NULL , 'C:\TMP\AdventureWorksTest.XLSX' , 'Person.Person listing;HumanResources.Employee listing;Sales.SalesOrderHeader' , 'Person;Employee;SalesOrderHeader'

You can see the result in the image below. You can notice the three sheets outlined with red in the image below.  

Although Excel has very powerful filter features, sometimes it is necessary to apply a filter at the query level. That is our next test. Let us execute the T-SQL script in the listing below. In the script we filter the table by passing in two parameters in the WHERE clause. Parameters definition in the WHERE clause are displayed bolded in the script.

EXEC [Excel].[QueryToExcelSSG]
'SELECT SalesOrderID , salesordernumber , purchaseordernumber , OrderDate , ShipDate , SubTotal , TaxAmt , TotalDue
FROM Sales.SalesOrderHeader
WHERE SalesOrderId >= @salesId1 AND SalesOrderId <= @salesId2;
',
'@salesId1 int=43665,@salesId2 int=43671',
'C:\TMP\AdventureWorksTest.XLSX',
'Sales.SalesOrderHeader',
'SalesOrderHeader';

You can see the result on the image below.

In order to do some testing with stored procedures, let us create a stored procedure. The reason why I create one is that AdventureWorks2014 has no suitable one.

CREATE PROCEDURE dbo.GetPeopleByLastName (@LastName NVARCHAR(50))
AS
SELECT
                PersonType
                ,FirstName
                ,LastName
FROM Person.Person
WHERE LastName = @LastName
ORDER BY BusinessEntityID

After the stored procedure is created, let us try to use it. Execute following T-SQL script, which filteres 'Person.Person' table based on the 'LastName' column. 

EXEC [Excel].[QueryToExcelSSG]
'EXEC dbo.GetPeopleByLastName @lastName=@lastName ;' ,
'@LastName nvarchar(50)=Alexander' ,
'C:\TMP\AdventureWorksTest.XLSX' ,
'Stored procedure sample' ,
'StoredProcedure';

If we pass, for example 'Alexander' as a parameter, the result will look similar as the one shown in the image bellow. 

Summary

There are many possibilities to extend this solution. For example, we could create a color table, which would contain information about how you want to format your exported Excel worksheet. For instance, which color font will you use for the first row, which background color will you use for the headings, etc. Then we could specify a data filter to apply as an option, decide to freeze the first column, etc.

What are the solution disadvantages? It comes with an extra cost, since you need to buy a third party tool. Furthermore, there are some minor bugs, which I commented in the solution. When I try to freeze the first row, an exception occures. An exception also occurs when trying to apply AutoFit.

What are the solution advantages? The obvious advantages of the solution are superior performance and the control over this process. In addition, in order to transform a query result into an Excel file, there is no need to install Excel.