asp上传excel到数据库
/*时间： 2009-6-26 13:30
*获得Excel中的所有sheetname。
*/
public ArrayList ExcelSheetName ( string filepath )
{
ArrayList al = new ArrayList ();
OleDbConnection conn = new OleDbConnection(filepath);
conn.Open ();
DataTable sheetNames = conn.GetOleDbSchemaTable
(System.Data.OleDb.OleDbSchemaGuid.Tables,new object[]{null,null,null,"TABLE"});
conn.Close ();
foreach ( DataRow dr in sheetNames.Rows )
{
al.Add ( dr[2] );
}
return al;
}
/*时间： 2009-6-26 14:10
*将Excel导入到DataSet
*/
public DataSet ExcelDataSource(string filepath, string sheetname)
{
OleDbConnection conn = null;
try
{ conn = new OleDbConnection(filepath);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "select count(模块名称) from [" + sheetname + "]";
int i = int.Parse(cmd.ExecuteScalar().ToString());
cmd.CommandText = "select count(*) from (select distinct 模块名称 from [" + sheetname + "])";
int n = int.Parse(cmd.ExecuteScalar().ToString());
if (i != n)
{
return null;
}
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", conn);
DataSet ds = new DataSet();
oada.Fill(ds);
return ds;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return new DataSet();
}
finally
{
conn.Close();
} }
㈡ 怎样通过ASP代码把excel数据导入到数据库中去呀
这个不难，通过asp获取到xls的数据，然后再批量写入数据库。如果条件允许的话，可以直接在数据库上面导入xls文件数据。
㈢ 求助:用ASP将excel导入到数据库中
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=; "
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
12345 67890
'=====================ASP读取EXCEL注事项===========================
'i)将Excel97或Excel2000生成的XLS文件（book）看成一个数据库，其中的每一个工作表(sheet)看成数据库表
'ii)ADO假设Excel中的第一行为字段名.所以你定义的范围中必须要包括第一行的内容
'iii)Excel中的行标题（即字段名）不能够包含数字. Excel的驱动在遇到这种问题时就会出错的。例如你的行标题名为“F1”
'iiii)如果你的Excel电子表格中某一列同时包含了文本和数字的话,那么Excel的ODBC驱动将不能够正常, 处理这一行的数据类型，你必须要保证该列的数据类型一致
'===========================================================
代码都给全了 完全正确的代码 你还不满足?
有其他问题重新提问吧，
最后说下，
建议你重新安装ADO的驱动，MDAC的2.8版
也许是你没有注册ADO组件，
㈣ ASP里，将Excel导入到Access数据库
下面连接到test.xls表sheet1$
'表后面要加个$的,一定要加这个符号
Set
conn
=
Server.CreateObject("ADODB.Connection")
conn.Open
"Driver={Microsoft
Excel
Driver
(*.xls)};ReadOnly=0;DBQ="
&
Server.MapPath("test.xls")
sql1="select
*
from
[sheet1$]"
Set
rs
=
Server.CreateObject("ADODB.Recordset")
rs.Open
SQL1,
conn,
3,
3
'下面连接到atest.mdb表user
curDir
=
Server.MapPath("atest.mdb")
Set
conn1
=
Server.CreateObject("ADODB.Connection")
conn1.Open
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source="
&
curDir
Set
rs1
=
Server.CreateObject("ADODB.Recordset")
Set
rs1.ActiveConnection
=
conn1
rs1.Source
=
"select
*
from
user"
rs1.CursorType
=
3
'
adOpenKeyset
rs1.LockType
=
3
'adLockOptimistic
rs1.Open
Do
While
Not
rs.Eof
rs1.AddNew
rs1(0)=j
for
i=0
to
rs.Fields.Count-1
rs1(i)=Trim(rs(i))
Next
rs1.Update
rs.MoveNext
j=j+1
Loop
rs.Close
rs1.Close
conn.Close
conn1.Close
Set
rs=nothing
Set
conn=nothing
㈤ 如何将excel数据通过asp导入数据库
将excel数据通过asp导入数据库：
参考代码：
wenjian=request.Form("floor")
fileext=mid(wenjian,InStrRev(wenjian,".")+1)
if lcase(fileext)<>"xls" then
response.write "<script>alert ('文件格式不对，请上传Excel文件');window.location.href='updateFloor.asp';</script>"
response.end
end if
set conne=server.CreateObject("ADODB.Connection")
connStre="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath( ""&wenjian&"" )&";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';"
conne.open connStre
Sqle="select * from [sheet1$] "
Set rse = Server.CreateObject("ADODB.Recordset")
rse.open sqle,conne,1,1
'验证
hang=2
do while not rse.eof
'名称不能为空
if trim(rse(0))<>"" then
else
mess="第"& hang &"行名称为空，请检查！"
response.Write"<script>alert('"& mess &"').window.location.href='updateFloor.asp'</script>"
response.End()
end if
rse.movenext
hang=hang+1
loop
rse.movefirst
do while not rse.eof
set rst=server.CreateObject("adodb.recordset")
sqlt="select * from Sellman"
rst.open sqlt,conn,1,3
rst.addnew()
rst("CompanyName")=c2(rse(0))
rst("CompanyInfo")=c2(rse(1))
rst("address")=c2(rse(2))
rst("tel")=c2(rse(3))&" "&c2(rse(7))
rst("Fax")=c2(rse(4))
rst("linkman")=c2(rse(5))
rst("Homepage")=c2(rse(8))
rst("Email")=c2(rse(6))
rst.update()
rst.close
set rst=nothing
rse.movenext
loop
rse.close
set rse=nothing
response.Write "<script>alert('导入成功!');location.href='updateFloor.asp';</script>"
㈥ 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到sqlserver数据库中
一般是上传excel文件到服务器中，然后以该excel为数据库读取数据，
再保存读取的数据记录就OK了
㈧ asp 怎样上传Excel文件后把数据存储在数据库
将excel数据通过asp导入数据库：
参考代码：
wenjian=request.Form("floor")
fileext=mid(wenjian,InStrRev(wenjian,".")+1)
if lcase(fileext)<>"xls" then
response.write "<script>alert ('文件格式不对，请上传Excel文件');window.location.href='updateFloor.asp';</script>"
response.end
end if
set conne=server.CreateObject("ADODB.Connection")
㈨ 怎么在ASP页面上传EXCEL数据到ACCESS数据库中
'access数据连接conn就不写了。这里举个简单例子。
strAddr=server.MapPath("data.xls")
set excelconn=server.createobject("adodb.connection")
excelconn.open "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = "+strAddr+";Extended Properties='Excel 8.0;HDR=NO;IMEX=1'"
set rs=server.CreateObject("adodb.recordset")
set rs1=server.CreateObject("adodb.recordset")
sql="select * from [Sheet1$]"
rs.open sql,excelconn,1,1
if not(rs.bof and rs.eof) then
rs.movenext
do while not rs.eof
sql1="select * from student"
rs1.open sql1,conn,1,3
rs1.addnew
rs1("name")=rs(0)
rs1("bj")=rs(1)
rs1("xb")=rs(2)
rs1("xh")=rs(3)
rs1.update
rs1.close
rs.movenext
loop
end if
rs.close()
set rs=nothing
set rs1=nothing
excelconn.Close()
set excelconn=nothing