HOW TO: Use ASP to Query and Display Database Data in Excel
Posted On June 25, 2007 by Krishna Language:
This article describes how to build a tab delimited text file dynamically from a SQL Server or Access database query, that can be opened in Excel from within Internet Explorer using Active Server Pages.
Users will be able to save the file created on their computers. The following code shows how to create a tab delimited text file. The file is saved with the extension .xls so that the file will be associated with Excel, and when opened it will automatically invoke Excel.
<%@ LANGUAGE="VBSCRIPT" %>
<TITLE>Create Tab Delimited Text File</TITLE>
'Create a randome Filename
nRandom = Int((1000000 - 1 + 1) * Rnd + 1000000)
fileExcel = "t" & CStr(nRandom) & ".xls"
'Replace 'MyWeb' with your virtual directory name or just the
'slash if it is at the wwwroot.
filename=filePath & "\" & fileExcel
'Create the File with extension .xls using the FileSytemObject
'If the file does not exist, the TRUE parameter will allow it
'to be created. Make sure the user* impersonated has write
'permissions to the directory where the file is being created.
Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set MyFile = fs.CreateTextFile(filename, True)
'Open the connection and retrieve data from the database
Set cn = Server.CreateObject("ADODB.Connection")
'The following open line assumes you have set up a System
'DataSource by the name of Pubs pointing to the Pubs database.
'And that the sa password is blank. If you use a different
'username and password, make sure that the user has permissions to
'the pubs database, and select permissions on the Authors Table.
'If you do not have SQL Server, see notes below for how to modify
'sample to work with an Access database
Set rs = cn.Execute("SELECT
strLine="" 'Initialize the variable for storing the filednames
For each x in rs.fields
'Separate field names with tab so that these appear in
'different columns in Excel
strLine= strLine & x.name & chr(9)
'Write this string into the file
'Retrieve the values from the database and write into the database
Do while Not rs.EOF
for each x in rs.Fields
strLine= strLine & x.value & chr(9)
'Show a link to the Excel File.
link="<A href=" & fileExcel & ">Open Excel</a>"
The following describes how to modify the above sample to work on the Adventure Works Access Database that is installed as part of Internet Information Server 3.0 and 4.0. Change the following line.
and make sure that a System DSN exists named AdvWorks. Change the following line.
Set rs = cn.Execute("SELECT
Set rs = cn.Execute("SELECT * from Employees")
* In this case user refers to the user that the Web server is using to make the request. For example, if your web site is using anonymous authentication and the user profile for anonymous access is IUSR_MachineName, then IUSR_MachineName must have write permission to the directory specified in the createTextFile.
For the latest Knowledge Base articles and other support information on Visual InterDev and Active Server Pages, see the following page on the Microsoft Technical Support site:
Well, this is all about program ! Happy Programming! !