asp中怎么导入excel到SQL中？

用OLEDB进行Excel文件数据读取并返DataSet数据集其几点需要注意：e68a84e8a2ade799bee5baa6e997aee7ad94336

1.连接字符串参数IMEX 值：
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
IMEX3值：IMEX=2 EXCEL文档同含字符型数字型比第C列3值2数值型 1231字符型 ABC导入
页面报错库显示数值型123字符型ABC则呈现空值IMEX=1述情况发库确呈现 123 ABC.
2.参数HDR值：
HDR=Yes代表第行标题做数据使用 用HDR=NO则表示第行标题做数据使用系统默认YES
3.参数Excel 8.0
于Excel 97版本都用Excel 8.0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

#region 获取excel dataset ,即名excel_ds
string excelFilePath = openFileDialog1.FileName;//弹文件选取窗口获取EXCEL文件路径
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + excelFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
OleDbConnection Oleconn = new OleDbConnection(strConn);
string strExcel = "";
OleDbDataAdapter excelCommand = null;
DataSet excel_ds = new DataSet();
strExcel = "select * from [sheet1$]";
try
{
Oleconn.Open();
excelCommand = new OleDbDataAdapter(strExcel,Oleconn);
excelCommand.Fill(excel_ds,"exdtSource");//dataset
}
catch (System.Exception ex)
{
MessageBox.Show("导入错：" + ex, "错误信息");
}
finally
{
Oleconn.Close();
Oleconn.Dispose();
}
#endregion

Set db = Server.CreateObject("ADODB.Connection")
db.Open "Driver={Microsoft Excel Driver (*.xls)};Dbq=" & Server.MapPath(FileName)
'打开记录集，表名e799bee5baa6e997aee7ad94e78988e69d83361一定要以"[表名$]"的格式
strSql="Select * From [Sheet1$]"
Set rsexcel=db.Execute(strSql)
Set rs = Server.CreateObject("ADODB.RecordSet")
SQL = "Select * From [users]"
rs.Open SQL, Conn, 1, 3
N = rs.fields.count-1'数据表字段数
M = rsexcel.fields.count-1'excel表里字段数
Redim L(N)'定义数据表数组
Redim arr(M)'定义excel表数组

'把数据表里的字段表存入数组L中
For k=0 To N
L(k) = Rs(k).name
next
'把excel表里的字段存入数组arr中
for p=0 to M
arr(p) = rsexcel(p).name
next
'取数组长度
x=UBound(l)
y=UBound(arr)

j=0
startime=timer() '开始时间
Do While Not rsexcel.EOF

rs.AddNew
'循环读取所有行
for i=6 to x'对数据表进行循环 rs(0)是 id 所以跳过
if l(i)=arr(j) then'通过数组判断两表字段是否相同，如相同对其进行赋值，不相同置0
if Trim(rsexcel(j))<>"" then
'response.write Trim(rsexcel(j))&"|"
rs(i)=Trim(rsexcel(j))
else
rs(i)=0
end if
if j< M then '当execl表数据是最后一项时不能在加1了
j=j+1
else
j=j
end if
else
rs(i)=0
end if
rs(3)=typeid
Next
rsexcel.MoveNext
rs.update
j=0
Loop
endtime=timer()
'关闭对象
rsexcel.Close
Set rsexcel=nothing
db.Close
Set db=Nothing
只要修改数据库连接
不用修改其它代码

这段代码已经测试过：excel导入到access数据库
<%
dim FileName
FileName="data.xls" '取得文件名，来自项目经理的指定，路径固定在某个虚拟路径中
Dim conn, rs
set conn=CreateObject("ADODB.connection")
conn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=" & Server.mappath(""&FileName&"") & ";" & _
"DefaultDir=c:\"

set rs=createobject("ADODB.recordset")
rs.Open "Select * From [snew$]",conn, 2, 2
if rs.eof then
response.write "Excel表中e799bee5baa6e79fa5e98193e4b893e5b19e363无纪录"
else
set connDB = Server.CreateObject("ADODB.Connection")
DBPath = Server.MapPath("data1.mdb")
'RESPONSE.WRITE DBpath
connDB.Open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Set RsDB = Server.CreateObject("ADODB.Recordset")
SQLDB="Select * from test"
RsDB.open SQLDB,connDB,1,3
do while not rs.eof '利用循环读出数据
RsDB.addnew
RsDB("0")=rs(0)
RsDB("1")=rs(1)
RsDB("2")=rs(2)
RsDB("3")=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("0")&" "
response.write RsDB("1")&" "
response.write RsDB("2")&" "
response.write RsDB("3")&" "
'response.write RsDB("id4")&" "
response.write ""
RsDB.movenext
loop
end if
rs.close
set rs=nothing
set conn=nothing
%>