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:
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:
<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>
then initiate the download of the newly written excel file.
Dim ADOasptest1conn As Object
Dim ADOasptest1rs As Object
Dim sr As StreamReader
Dim sw As StreamWriter
Dim strinput As String
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
Do While ADOasptest1rs.eof = False
'loop through recordset until data is written out.
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>")
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.
It should give you the .xls file in the exact format you designed it in with the correct data added to it.
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.ContentType = "application/octet-stream"
"attachment; filename=""" & filename & """")
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.