aspexcel导入sql数据库
① 请问如何用asp实现将Excel文件导入到access数据库中( 急! 急!急!)
在ASP中怎样把EXCEL导入ACCESS
demo.asp
<%
Dim cn,oConn,connstr
'打开XLS.
Set cn = Server.CreateObject("ADODB.Connection")
cn.Provider = "Microsoft.Jet.OLEDB.4.0 "
cn.ConnectionString = "Data Source=" & Server.MapPath("data.xls") & ";" & _
"Extended Properties=Excel 8.0;"
cn.Open
'打开MDB.
connstr="DBQ="+server.MapPath("data.mdb")+";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};"
set oConn=server.CreateObject("ADODB.CONNECTION")
oConn.open connstr
'读取数据.
set rsRead=server.CreateObject("ADODB.Recordset")
rsRead.Open "select * from [Sheet1$]",cn,1,1
do until rsRead.EOF
'写入数据库.
oConn.Execute("Insert into users(userid,password)Values('"& rsRead.Fields("userid")&"','" &rsRead.Fields("password") & "')" )
rsRead.MoveNext
loop
%>
数据库data.mdb
表users
id,uesrid,password
Excel文件data.xls
数据
userid password
wang w521
zhange z254
hong h112
'================!!!!ASP读取EXCEL注事项!!!!======================
'i)将Excel97或Excel2000生成的XLS文件（book）看成一个数据库，其中的每一个工作表(sheet)看成数据库表
'ii)ADO假设Excel中的第一行为字段名.所以你定义的范围中必须要包括第一行的内容
'iii)Excel中的行标题（即字段名）不能够包含数字. Excel的驱动在遇到这种问题时就会出错的。例如你的行标题名为“F1”
'iiii)如果你的Excel电子表格中某一列同时包含了文本和数字的话,那么Excel的ODBC驱动将不能够正常, 处理这一行的数据类型，你必须要保证该列的数据类型一致
'====================================================
其他:
http://bbs.dvbbs.net/dispbbs.asp?boardid=1&id=1124887
② 如何通过asp将excel表导入access数据库中
先导出，再导入
Excel导出函数
<%
Sub ExportToExcel
Response.ContentType = "application/vnd.ms-Excel"
Response.AddHeader "Content-Disposition", "attachment;Filename=Results.xls"
Response.Write "<body>"
Response.Write "<table border=1>"
Call WriteTableData
Response.Write "</table>"
Response.Write "</body>"
Response.Write "</html>"
End Sub
%>
Excel导入数据库
<%
dim FileName
FileName="Excel.xls" '取得文件名，来自项目经理的指定，路径固定在某个虚拟路径中
Dim conn, rs
set conn=CreateObject("ADODB.connection")
conn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=" & Server.mappath(""&FileName&"") & ";" & _
"DefaultDir=G:\"
set rs=createobject("ADODB.recordset")
rs.Open "Select * from [Sheet1$]",conn, 2, 2
if rs.eof then
response.write "Excel表中无纪录"
else
set connDB = Server.CreateObject("ADODB.Connection")
DBPath = Server.MapPath("Excel.mdb")
'RESPONSE.WRITE DBpath
connDB.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Set RsDB = Server.CreateObject("ADODB.Recordset")
sqlDB="Select * from Excel"
RsDB.open SQLDB,connDB,1,3
do while not rs.eof '利用循环读出数据
RsDB.addnew
RsDB("filename")=rs(0)
RsDB("id1")=rs(1)
RsDB("id2")=rs(2)
RsDB("id3")=rs(3)
RsDB("id4")=rs(4)
Rs.update
RsDB.movenext
rs.movenext
loop
'response.redirect FileName
end if
RsDB.movefirst
if RsDB.eof then
response.write "数据库中无记录"
else
do while not RsDB.EOF
response.write RsDB("filename")&" "
response.write RsDB("id1")&" "
response.write RsDB("id2")&" "
response.write RsDB("id3")&" "
response.write RsDB("id4")&" "
response.write "<br>"
RsDB.movenext
loop
end if
rs.close
set rs=nothing
set conn=nothing
%>
③ ASP代码中如何把EXCEL数据导入到SQL SERVER数据库中
'定义打开Excel表格的函数
Function OpenExcel(path)
dim excel,rs,strsql
On Error Resume Next
Set rs = Server.CreateObject("ADODB.RecordSet")
Set excel = Server.CreateObject("ADODB.Connection")
excel.Open "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & path
If Err.number<> 0 Then
Response.Write "请检查上传的Excel文件内部格式，文件无法打开，导入失败！"
Response.End
End If
strsql = "SELECT * FROM [Sheet1$]" '在这里指定工作薄名称,默认是Sheet1$
Set rs = excel.Execute(strsql)
Set OpenExcel = rs
End Function
'读取文件中的内容
Dim rsInfo
Set rsInfo = Server.CreateObject("ADODB.RecordSet")
Set rsInfo = OpenExcel("E:/a.xls") '这里的文件路径请用Server.Path来获取
'检查读取结果
If rsInfo.State<> 1 Then
Response.Write "请检查Excel文件中的工作表命名是否为Sheet1，导入失败！"
Response.End
End If
If rsInfo.EOF And rsInfo.BOF Then
Response.Write "没有找到Excel表中的数据，导入失败！"
Response.End
End If
If IsNull(rsInfo.Fields(0)) or Trim(rsInfo.Fields(0))="" Then
Response.Write "没有找到Excel表中的数据，导入失败！"
Response.End
End If
'这里指定导入数据的列数,列数少了退出
If rsInfo.Fields.Count< 7 Then
Response.Write "Excel表中的数据列数不正确，导入失败！"
Response.End
End If
'创建数据库连接
dim dbrs,conn,sql
Set conn = Server.CreateObject("ADODB.Connection")
Set dbrs = Server.CreateObject("ADODB.Recordset")
'注: G_DB_ConnectString是连接数据库的字符串,自己定义
conn.ConnectionString = G_DB_ConnectString
conn.Open '打开数据库连接
'创建临时表
sql = "IF EXISTS (SELECT * FROM sysobjects WHERE xtype='U' and name='tmp_PartRes') "
sql = sql & "BEGIN Drop table tmp_PartRes END "
sql = sql & "Create table tmp_PartRes([ID] int identity(1,1),"
sql = sql & "PartID varchar(100),Brand varchar(100),[Package] varchar(100),"
sql = sql & "BatchNo varchar(100),[Price] varchar(100),[Stock] varchar(100) default('0'),"
sql = sql & "Brief varchar(100),StockFlag int default(1),"
sql = sql & "SuperFlag int default(1),SaleFlag int default(1))"
conn.execute sql
'取表结构 注意: 只取表的结构, 不要数据, 因为我这个是刚创建的临时表, 没有数据,
'如果表中存在数据, 要注意加上条件句, 防止取到数据 如: where ID = -1
sql = "SELECT * FROM tmp_PartRes"
dbrs.CursorLocation = 3 '这一定要设置为3
dbrs.Open sql,conn, 3, 4 '这里的参数必须是3和4
'取到表结构后, 必须要把活动连接及数据库连接关闭,这个很重要, 否则导入速度特慢.
Set dbrs.ActiveConnection = Nothing
conn.close
'提取Excel中的数据, 将excel中的数据放入到数据库表中.
While Not rsInfo.EOF
If Trim(rsInfo.Fields(0))<> "" Then
dbrs.AddNew
dbrs("PartID") = Ucase(Trim(rsInfo.Fields(0)))
dbrs("Brand") = Trim(rsInfo.Fields(1))
dbrs("Package") = Trim(rsInfo.Fields(2))
dbrs("BatchNo") = Trim(rsInfo.Fields(3))
dbrs("Price") = Trim(rsInfo.Fields(4))
If Trim(rsInfo.Fields(5))<>"" Then
dbrs("Stock") = Trim(rsInfo.Fields(5))
Else
dbrs("Stock") = "0"
End If
dbrs("Brief") = Trim(rsInfo.Fields(6))
End If
rsInfo.MoveNext
Wend
'更新记录集到数据库临时表
conn.Open '打开连接
dbrs.ActiveConnection = conn
dbrs.UpdateBatch '批量更新函数
'更新完成后, 关闭连接
dbrs.Close
Set dbrs = Nothing
rsInfo.Close
Set rsInfo = Nothing
④ asp中把excel中数据导入SQL数据库
private void add()
{
OleDbConnection connread = new OleDbConnection("provider=microsoft.jet.oledb.4.0; data source=D:\\Book1.xls; extended properties=excel 8.0");
connread.Open();
OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", connread);
DataSet ds = new DataSet();
da.Fill(ds);
connread.Close();
SqlConnection conn = new SqlConnection("server=.; integrated security=true; database=demo");
conn.Open();
SqlCommand cmd = new SqlCommand("create table name(name varchar(100),namea varchar(100),nameb varchar(100))", conn);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string p=string.Format("insert into name values('{0}','{1}','{2}')",ds.Tables[0].Rows[i]["name"].ToString().Trim(),ds.Tables[0].Rows[i]["namea"].ToString().Trim(),ds.Tables[0].Rows[i]["nameb"].ToString().Trim());
cmd = new System.Data.SqlClient.SqlCommand(p, conn);
cmd.ExecuteNonQuery();
}
conn.Close();
}