create excel file from MSSQL

derwent · Oct 6, 2005

I have a SQL Server database that collects the info from a form. Every so often I am required to export an excel spreadsheet from the DB for reporting purposes.

Is it possible to create an xls (or csv) file from asp code so that I can point the users to a file which creates the xls for them, taking me out of the loop?

Preferably it need no compnants installed on the webserver.

The data to be exported are the headers then the DB contents.

Thanks

Sheco · Oct 6, 2005

Could you use the SQL Server itself to do this task?

I'm thinking of using Data Transormation Services.... create a DTS package that exports to xls and then schedule it to run every day at midnight.... then your users can get a new "fresh" copy every morning.

DotNetGnat · Oct 6, 2005

why not just use

response.ContentType="application/vnd.ms-excel"

at the top of your asp page...

-DNG

Tarwn · Oct 8, 2005

The CSV page is extrenmely easy. Basically just set your ContentType like DNG suggested above (except use "text/csv") and add the header for Content-Disposition, then use Response.Write. A very simple example would be:

Code:

<%
Option Explicit

Response.ContentType = "text/csv"
Response.AddHeader "Content-Disposition", "filename=yourFileName.csv;"

Response.Write "a,b,c,d" & vbCrLf
Response.Write "a2,b2,c2,d2" & vbCrLf
%>

In writing the CSV file there are a couple things you can do to optimize the process. The best optimization would be the .GetString() function of the recordset object. This is much faster than manually looping through the recordset on your own. More Info here

Now if you want to do an actual excel file, that is a little more complicated, but still doable. There are several examples of how to do this on the web so i will only provide a simple one. Besides setting the ContentType as DNG suggested, you will also want to output your data in a table so that Excel can understand what your trying to give to it. Here is a brief example:

Code:

<% 
Option Explicit

Dim filename, title
filename = "yourFilename.xls"
title = "My Nifty XLS File"

Response.ContentType="application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "filename=" & filename & ";"

'output header info with XLS tags
Response.Write "<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">"
Response.Write "<head>"
Response.Write "<!--[if gte mso 9]><xml>"
Response.Write "<x:ExcelWorkbook>"
Response.Write "<x:ExcelWorksheets>"
Response.Write "<x:ExcelWorksheet>"
Response.Write "<x:Name>"& title &"</x:Name>"
Response.Write "<x:WorksheetOptions>"
Response.Write "<x:Print>"
Response.Write "<x:ValidPrinterInfo/>"
Response.Write "</x:Print>"
Response.Write "</x:WorksheetOptions>"
Response.Write "</x:ExcelWorksheet>"
Response.Write "</x:ExcelWorksheets>"
Response.Write "</x:ExcelWorkbook>"
Response.Write "</xml>"
Response.Write "<![endif]--> "
Response.Write "</head>"

'--- Now write the body that will show up in the XLS sheet
Response.Write "<body>"
Response.Write "<table>"

'--- sample data
Response.Write "<tr><td>Some Data</td><td>Some More Data</td></tr>"
Response.Write "<tr><td>More Data</td><td>Yet More Data</td></tr>"

'--- finish it up
Response.Write "</table>"
Response.Write "</body>"
Response.Write "</html>"
%>

On a sidenote, I went ahead and tested that second one and OpenOffice (I am currently on my Linux box) openedthe xls file just fine, so I guess that one classifies as a working example

In any case, hope this is helpfule,
-T

derwent · Oct 11, 2005

hey that's great thanks tarwn.

Is it possible to automatically enter the rows/columns of data.

The table in the DB has 39 columns and thousands of rows of data.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

create excel file from MSSQL

derwent

Programmer

Sheco

Programmer

DotNetGnat

Programmer

Tarwn

Programmer

derwent

Programmer

Similar threads

Part and Inventory Search

Sponsor