|
asp适用ado操作excel
返回所有表所有行所有列数据
新增数据到指定的表
Dim ExcelConn,CreateExcelRecordset,rstSchema,i,OutStr
Set ExcelConn = Server.CreateObject("ADODB.Connection")
ExcelConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='"+Server.MapPath( "upload.xls")+"'; extended Properties=Excel 8.0;"
'ExcelConn.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & Server.MapPath( "Sample.xls")
Set rstSchema=ExcelConn.OpenSchema(20)
Do while Not rstSchema.EOF
Set CreateExcelRecordset = Server.CreateObject("ADODB.Recordset")
CreateExcelRecordset.Open "Select * From ["&rstSchema("Table_name")&"]", ExcelConn, 2, 2
If Not CreateExcelRecordset.Eof Then
Response.write "<strong>Table : " & rstSchema("Table_name")& "</strong><br>"
%><table width="100%" cellpadding="3" cellspacing="1" bgcolor="#CCCCCC" border="0"><tr><%
For i = 0 To CreateExcelRecordset.Fields.Count - 1
Response.write "<td bgcolor=""#EFEFEF""><strong>" & CreateExcelRecordset(i).Name & "</strong></td>"
Next
Response.write "</tr>"
Do while Not CreateExcelRecordset.Eof
Response.write"<tr>"
For i = 0 To CreateExcelRecordset.Fields.Count - 1
Response.write "<td bgcolor=""#FFFFFF"">" & CreateExcelRecordset(i) & "</td>"
Next
Response.write "</tr>"
CreateExcelRecordset.MoveNext
Loop
Response.Write "</table>"
End If
CreateExcelRecordset.Close
rstSchema.MoveNext
Loop
CreateExcelRecordset.Open "Select * From [Sheet1$]", ExcelConn, 2, 3
If Not CreateExcelRecordset.Eof And CreateExcelRecordset.Bof Then CreateExcelRecordset.MoveFirst
'For i = 1 To 20
' CreateExcelRecordset.AddNew
' CreateExcelRecordset("No#") = i
' CreateExcelRecordset("P/N")="Title " & i & " Name"
' CreateExcelRecordset("Description")="The Info of " & i & " Item"
' CreateExcelRecordset("Q'ty") = 5
' CreateExcelRecordset.update
'Next
CreateExcelRecordset.Close
Set CreateExcelRecordset = Nothing
ExcelConn.Close
Set ExcelConn = Nothing |
|