Using the ExcelWriter, it just takes the following lines of code, in VB.NET, to create an Excel file based on a template.

Dim oExcelTemplate As New ExcelTemplate
Dim myRdr As SqlDataReader
Dim ConnStr As String = ConfigurationSettings.AppSettings("ConnectionString")
Dim myConnection As SqlConnection = New SqlConnection(ConnStr)
Dim myCommand As SqlCommand = New SqlCommand("sp_authors_sel", myConnection)
Dim myAdapter As New SqlDataAdapter(myCommand)
Dim AuthDt As New DataTable
myAdapter.Fill(AuthDt)
oExcelTemplate.Open("c:\inetpub\wwwroot\writer\Excel\auth_template.xls")
oExcelTemplate.SetDataSource(AuthDt, "authors")
oExcelTemplate.Process()
oExcelTemplate.Save("c:\inetpub\wwwroot\writer\Excel\auth_output.xls")

It took me around 15 minutes to write the above code. This included creating the stored procedure, sp_authors_sel, which simply dumps all rows from the author table under the pubs database in SQL Server 2000, and creating the template file shown in Figure 1.

All we are doing here is executing a stored procedure and creating a new Excel file based on the template, auth_template.xls. The file path in the above example can also be a virtual path rather than a physical path. Instead of saving the new file in the server, you could also stream the output as an HTTP response to the client machine.