ado|excel| data
ASP query data for the basic operations of Excel
EXECL97/2000/XP is a member of MS Office office software. In enterprise-class applications, we often need to operate on execl, such as reading execl inside the data, inserting data into the execl.
First, the operation Execl should pay attention to matters:
1, server-side Office configuration
For example, for Ms Windows2000+iis, there is no special requirement for a version of Execl,office that has a member of MS Office installed on the server side.
2, server-side Distributed COM configuration
Execute the "DCOMCNFG" command, select the Microsoft execl application-> Properties-> security-> Three options on the Application page, and choose "Use custom Access" to add "everyone" permission.
Second, first of all, will use ASP to read EXECL data (do not create DSN):
We can think of the whole. xsl file as a database, Sheet1, Sheet2 and so on, respectively, as a separate table, the A1, B1, C1 、... N1 as a table field.
--Establish a Connection object instance Execlconn
Set execlconn=server.createobject ("ADODB. Connection ")
--Open the database with the Open method
strconn= "Driver={microsoft Excel Driver (*.xls)};" &_
"DRIVERID=790; Dbq= "& Server.MapPath (" XLS filename ")
Conn. Open strconn
--Set up DataSet object RS and query data
Set Rs = Server.CreateObject ("ADODB.") Recordset ")
Sql= "SELECT * from [sheet1$]"
Rs. Open sql,conn,2,2
Specific examples:
1, establish a table Sheet1 (database name is students)
StudentID
Name
Language
Mathematical
Physical
Chemical
Geographical
1
Li Xieqing
83
84
76
95
66
2
Feng Jiang
87
96
82
100
81
3
Wu Xiaoxia
76
43
37
60
82
4
Shian Hui
80
77
63
71
63
5
Cai Haifei
89
63
92
86
67
2, query and display the table Sheet1 content code
<%
Dim Conn
Dim strconn
Dim RS
Dim SQL
Set conn=server.createobject ("ADODB. Connection ")
strconn= "Driver={microsoft Excel Driver (*.xls)};" &_
"DRIVERID=790; Dbq= "& Server.MapPath (" Students.xls ")
Conn. Open strconn
Set rs = Server.CreateObject ("ADODB.") Recordset ")
Sql= "SELECT * from [sheet1$]"
Rs. Open sql,conn,2,2
%>
<center>
<table border= "1" >
<tr>
<%
For i=0 to Rs. Fields.count-1
%>
<TD bgcolor= "#0099FF" ><%=rs (i). Name%></td>
<%
Next
%>
</tr>
<%
Do but not Rs. Eof
%>
<tr>
<%
For i=0 to Rs. Fields.count-1
%>
<td><%=rs (i)%></td>
<%
Next
%>
</tr>
<%
Rs. MoveNext
Loop
Rs.close
Set rs=nothing
Strconn.close
Set strconn=nothing
%>
</table></center>
3. Operation Result
