It may not be the best but it worked like a champ for me. This is a page I've written to select data from an SQL server database and write out an excel file server side with streams and then initiate a download for the user.

First off create the template file in excel. Format it exactly how you'd like it to appear once it's downloaded, give it borders around the data and add a row of fake data to it that you'll recognize. Now save this as HTML. Open the template file and find the html table rows where you see the fake data inputed.(may have to scroll down quite far.)

Once you get to the line that shows your data copy everything about it and save it as a "rptheader.txt" into a text file.

Next go below the last line that appears that has your data and copy everything to the end that and save that as "rptFooter.txt"

the code where the data is inputed should looks something like this:
HTML Code:
<tr height=17 style='height:12.75pt'>
    <td height=17 class=xl31 style='height:12.75pt;border-top:none'>COLUMN1DATA</td>
     <td class=xl32 style='border-left:none'>COLUMN2DATA</td>
</tr>
Then open a streamreader and streamwriter, read in the header, loop through the data you want to add and write out these lines modified to hold your dataset results, then read the footer into it like so:

Code:
Dim ADOasptest1conn As Object
        Dim ADOasptest1rs As Object
        Dim sr As StreamReader
        Dim sw As StreamWriter
        Dim strinput As String
        Dim strSql

        ADOasptest1conn = CreateObject("ADODB.Connection")
        ADOasptest1conn.Open("Provider=SQLOLEDB.1;Persist Security Info=False;User ID=xx;Password=xx;Initial Catalog=xx;Data Source=xx")
  
        strSql = "Select column1,column2 from tbltest"
        ADOasptest1rs = ADOasptest1conn.execute(strSql)
        If ADOasptest1rs.eof = False Then
            sw = File.CreateText("\\server\reports\newreport.xls")  'creates new .xls report
            sr = File.OpenText("\\server\reports\Templates\rptheader.txt")
            strinput = sr.ReadToEnd  'reads in header text template
            sw.WriteLine(strinput)  ' writes out header template contents to the new file
            sr.Close()
            Do While ADOasptest1rs.eof = False
    'loop through recordset until data is written out.
                '.xls format
                sw.WriteLine("<tr height=17 style='height:12.75pt'>")
                sw.WriteLine("<td height=17 class=xl31 style='height:12.75pt;border-top:none'>" & ADOasptest1rs("column4").value & "</td>")
                sw.WriteLine("<td class=xl32 style='border-left:none'>" & ADOasptest1rs("column1").value & "</td>")
                sw.WriteLine("</tr>")
                ADOasptest1rs.movenext()
            Loop
            sr = File.OpenText("\\server\reports\Templates\rpt1footer.txt")
            strinput = sr.ReadToEnd  ' reads in footer template
            sw.WriteLine(strinput) ' writes out contents of footer template to the new file.
            sw.Close()
            sr.Close()
            ADOasptest1rs.close()
            ADOasptest1conn.close()
then initiate the download of the newly written excel file.

Code:
Dim filepath As String = "\\server\reports\newreport.xls"
            If Not filepath Is Nothing Then
                If File.Exists(filepath) Then
                    Dim filename As String = "newreport.xls"
                    Response.Clear()
                    Response.ContentType = "application/octet-stream"
                    Response.AddHeader("Content-Disposition", _
                      "attachment; filename=""" & filename & """")
                    Response.Flush()
                    Response.WriteFile(filepath)
                End If
            End If
It should give you the .xls file in the exact format you designed it in with the correct data added to it.

You can do this with anything that will save as .html (word,excel etc etc.) and I've found it's a great way to create dynamic reports based on user input. Always seems people want excel so they can sort and modify things. That's how I stumbled onto this. Just be careful to place limits on how much data the user can select in the report. Testing with this returned 15000 rows and initiated the download in less than 2 seconds. You certainly dont want to allow selecting millions of records and having your server write that out.

It saved me lots of time running redundant reports. Now they enter values into a form and create the report themselves.

I've also used this with MS Word to generate very professional invoice documents, BOL's etc automatically or just to create a simple web report (.html)


Hope this helps somebody out.

I'm always looking to improve my stuff so if you can suggest a better way I'd be glad to hear it.