How to use ASP to transform SQL Server data into EXECL files

Source: Internet
Author: User
Tags chr
server 1, ASP files:

<%@ language= "VBSCRIPT"%>
<%option explicit%>
<%
' EXAMPLE as: Put a 24-hour online number in the database into an Excel file
' AUTHOR: Steel Workers
' Email:hello_hhb@21cn.com
' Date:2001-3-25
' Test: Testing through in Nt4,sp6,sql SERVER 7.0,excel2000
%>
<HTML>
<HEAD>
<meta content= "text/html; charset=gb2312 "http-equiv=" Content-type ">
<TITLE> Generate Excel File </TITLE>
</HEAD>
<body>
<a href= "Dbtoexcel.asp?act=make" > Generation of online population excel</a>
&LT;HR size=1 Align=left width=300px>
<%
If Request ("act") = "" Then
Else

Dim conn
Set Conn=server. CreateObject ("Adodb.connection")
Conn. Open "Test", "sa", ""
' Conn. Open application ("ConnStr")

Dim Rs,sql,filename,fs,myfile,x,link

Set fs = server. CreateObject ("Scripting.FileSystemObject")
'--suppose you want the resulting Excel file to be stored as follows
filename = "C:\online.xls"
'--delete it if the original Excel file exists
If Fs. FileExists (filename) Then
Fs. DeleteFile (filename)
End If
'--Create Excel file
Set myfile = fs. CreateTextFile (Filename,true)



Set rs = Server.CreateObject ("ADODB.") Recordset ")
'--Find out from the database the data you want to put in Excel
sql = "Select Population,hourpos,datepos from Populationperhour ORDER by datepos,hourpos ASC"
Rs. Open Sql,conn
If Rs. EOF and Rs. BOF Then

Else

Dim strline,responsestr
Strline= ""
For each x in Rs.fields
Strline= StrLine & X.name & Chr (9)
Next

'--Writes the table's column name first to Excel
MyFile.WriteLine StrLine

Do but not Rs. Eof
Strline= ""

For each x in Rs. Fields
Strline= StrLine & X.value & Chr (9)
Next
'--Writes the table's data to Excel
MyFile.WriteLine StrLine

Rs. MoveNext
Loop

End If

Rs. Close
Set rs = Nothing
Conn.close
Set conn = Nothing
Set myfile = Nothing
Set fs=nothing

link= "<a href=" & filename & ">open the Excel file</a>"
Response.Write Link
End If
%>
</BODY>
</HTML>


2, Database Related:

CREATE TABLE [Populationperhour] (
[Population] [INT] Not NULL,
[Hourpos] [INT] Not NULL,
[Datepos] [DateTime] Not NULL
);
INSERT into populationperhour values (' 936 ', ' 1 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 636 ', ' 2 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 106 ', ' 3 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 177 ', ' 4 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 140 ', ' 5 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 114 ', ' 6 ', ' 2001-1-11 ');
INSERT into Populationperhour values (' 94 ', ' 7 ', ' 2001-1-11 ');
INSERT into Populationperhour values (' 49 ', ' 8 ', ' 2001-1-11 ');
INSERT into Populationperhour values (' 88 ', ' 9 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 215 ', ' 10 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 370 ', ' 11 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 550 ', ' 12 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 629 ', ' 13 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 756 ', ' 14 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 833 ', ' 15 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 923 ', ' 16 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 980 ', ' 17 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 957 ', ' 18 ', ' 2001-1-11 ');
INSERT into Populationperhour values (' 812 ', ' 19 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 952 ', ' 20 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1379 ', ' 21 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1516 ', ' 22 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1476 ', ' 23 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1291 ', ' 24 ', ' 2001-1-11 ');
INSERT into populationperhour values (' 1028 ', ' 1 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 687 ', ' 2 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 462 ', ' 3 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 317 ', ' 4 ', ' 2001-1-12 ');
INSERT into Populationperhour values (' 221 ', ' 5 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 158 ', ' 6 ', ' 2001-1-12 ');
INSERT into populationperhour values (' 127 ', ' 7 ', ' 2001-1-12 ');
INSERT into Populationperhour values (' 81 ', ' 8 ', ' 2001-1-12 ');
INSERT into Populationperhour values (' 96 ', ' 9 ', ' 2001-1-12 ');
Inse



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.