excel導入資料庫的asp
『壹』 ASP.NET中如何將Excel表中的數據導入到資料庫中
ASP.NET中將Excel表中的數據導入到資料庫中的方法是利用可視化界面工具直接選擇欄位映射導入。
1）接受數據導入的表已經存在。
insert into t1 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,
'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$);
2）導入數據並生成表。
select * into t1 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Excel 5.0;HDR=YES;DATABASE=c:\\test.xls',sheet1$);
3) 導入Excel中指定的列到資料庫表中指定的列。
INSERT INTO t1(a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET'MICROSOFT.JET.OLEDB.4.0' ,'Excel5.0; HDR=YES; DATABASE=c:\\test.xls',sheet1$);
需要注意的地方。
1）外圍應用配置器的設置。
從「功能外圍應用配置器」中選擇「啟動 OPENROWSET 和 OPENDATASOURCE 支持」選項。
2）關閉Excel表。
如果在導入時要導入的Excel表格處於打開狀態，會提示：
「無法初始化鏈接伺服器 "(null)" 的 OLE DB 訪問介面 "microsoft.jet.oledb.4.0" 的數據源對象。」
3）導入數據時，Excel的首行會作為表頭，若導入到已存在的資料庫表，則忽略首行。
『貳』 asp如何導入excel內容
DataSet _Table = GetTableName(Server.MapPath(@"upload/" + Request.Cookies["sa"].Value + "/" + this.ListBox1.SelectedValue), this.DropDownList1.SelectedValue); //首先獲取EXCEL文件路徑
string[] list = Request["checkbox"].Split(",".ToCharArray()); 要導入的列，代碼不貼了
string CompanyName = string.Empty, Person_in_charge = string.Empty, Mobile = string.Empty, Phone = string.Empty, Fax = string.Empty, E_mail = string.Empty, Address = string.Empty, IMQQ = string.Empty, AliWW = string.Empty, Website = string.Empty;
if (_Table.Tables[0].Rows.Count > 0)
{
for (int i = 0; i != _Table.Tables[0].Rows.Count; i++)/
{
for (int j = 0; j < list.Length; j++)/判斷每個列的數據，然後處理
{
string[] arr = list[j].ToString().Split("|".ToCharArray());
if (Request[list[j].ToString()] == "CompanyName")
{
CompanyName = CompanyName + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "Person_in_charge")
{
Person_in_charge = Person_in_charge + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "Mobile")
{
Mobile = Mobile + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "Phone")
{
Phone = Phone + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "Fax")
{
Fax = Fax + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "E-mail")
{
E_mail = E_mail + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "Address")
{
Address = Address + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "IMQQ")
{
IMQQ = IMQQ + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "AliWW")
{
AliWW = AliWW + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
if (Request[list[j].ToString()] == "Website")
{
Website = Website + _Table.Tables[0].Rows[i][arr[1].ToString()].ToString();
}
}
db.Update_sql("INSERT INTO Customers([sid],[s_dep],[CompanyName],[Person_in_charge],[Mobile],[Phone],[Fax],[E-mail],[Address],[IMQQ],[AliWW],[Website],[Instry])VALUES('" + Request.Cookies["sa"].Value + "'," + Request.Cookies["dep"].Value + ",'" + CompanyName + "','" + Person_in_charge + "','" + Mobile + "','" + Phone + "','" + Fax + "','" + E_mail + "','" + Address + "','" + IMQQ + "','" + AliWW + "','" + Website + "'," + this.DropDownList2.SelectedValue + ")");
CompanyName = ""; Person_in_charge = ""; Mobile = ""; Phone = ""; Fax = ""; E_mail = ""; Address = ""; IMQQ = ""; AliWW = "";
this.Label3.Text = "導入成功！";
『叄』 asp.net中如何選擇本地計算機里的excel文件並將數據導入資料庫
分成兩塊完成
Asp.net 上傳文件到伺服器
Excel文件導入資料庫
第一步很簡單，網上一找一大堆
第二步可以參考C# Excel 導入資料庫，網上資料就很多了
給你段代碼參考：
(stringpathName,refstringResultInfo)
{
OleDbConnectionOleConn=null;
OleDbDataAdapterOleDaExcel=null;
DataSetds=newDataSet();
FileInfofile=newFileInfo(pathName);
if(!file.Exists)
{
thrownewException("文件不存在");
}
stringstrConn=string.Empty;
stringextension=file.Extension;
switch(extension)
{
case".xls":
strConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+pathName+";ExtendedProperties='Excel8.0;HDR=Yes;IMEX=1;'";
break;
case".xlsx":
strConn="Provider=Microsoft.ACE.OLEDB.12.0;DataSource="+pathName+";ExtendedProperties='Excel12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn="Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+pathName+";ExtendedProperties='Excel8.0;HDR=Yes;IMEX=1;'";
break;
}
OleConn=newOleDbConnection(strConn);try
{
OleConn.Open();
DataTableschemaTable=OleConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,null);
stringsheetName=schemaTable.Rows[1][2].ToString().Trim();
Stringsql="SELECT*FROM["+sheetName+"]";
OleDaExcel=newOleDbDataAdapter(sql,OleConn);
ds=newDataSet();
OleDaExcel.Fill(ds,sheetName);
returnds;
}
catch(Exceptionerr)
{
ResultInfo=err.Message;
returnnull;
}
finally
{
OleConn.Close();
if(OleDaExcel!=null)
{
OleDaExcel.Dispose();
}
}
}
『肆』 如何將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數據導入到資料庫中去呀
這個不難，通過asp獲取到xls的數據，然後再批量寫入資料庫。如果條件允許的話，可以直接在資料庫上面導入xls文件數據。
『陸』 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.net中怎麼直接讀入excel文件到資料庫
/// <summary>
/// Excel數據導入方法
/// </summary>
/// <param name="filePath">獲取文件的路徑</param>
/// <param name="dgv"></param>
public void EcxelToDataGridView(string filePath)
{
//根據路徑打開一個Excel文件並將數據填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//導入時包含Excel中的第一行數據，並且將數字和字元混合的單元格視為文本進行導入
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [test$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
//根據Excel的行逐一對上面構造的DataTable的列進行賦值
Regex reg = new Regex(@"13[0-9]\d{8}|15[0-9]\d{8}|18[079]\d{8}");
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
foreach (Match m in reg.Matches(excelRow[0].ToString()))
{
tx_phone.Text += m.Value + ",";
}
}
}
『捌』 怎麼把excel的表格導入asp
<%
set upload=new upload_5xSoft
set file=upload.file("file1")
formPath="../upload1/"
if file.filename="" then
response.Write "<script language='javascript'>alert('你沒選擇文件！');history.go(-1);</script>"
else
if file.filesize>1000 then
fileExt=lcase(right(file.filename,3))
if fileExt<>"xls" then
Response.Write"文件類型非法"
Response.end
end if
end if
randomize
ranNum=int(9000000*rnd)+10000
filename=formPath&year(now)&month(now)&day(now)&hour(now)&minute(now)&second(now)&ranNum&"."&fileExt
if file.FileSize>0 then
file.SaveAs Server.mappath(FileName)
end if
connstr = "driver={microsoft Access driver (*.mdb)}; dbq=" & server.mappath("../../data/#data.mdb")
set conn3 = server.createobject("ADODB.CONNECTION")
conn3.open connstr
set rs=server.createobject("adodb.recordset")
rs.Open "select * from User where UserName='"&Session("user")&"' " ,conn3,3,3
rs("log")=cstr(FileName)
rs.update
rs.close
conn3.close
'這里用來寫導入資料庫的程序
kl=FileName
op=len(kl)
op2= mid(kl,3,op-2)
strAddr = Server.MapPath(""&kl)
set excelconn=server.createobject("adodb.connection")
'strAddr = file
''輸出源文件名
excelconn.open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & strAddr
''建立excel記錄集
set rs2=server.createobject("adodb.recordset")
set rs3= Server.CreateObject("ADODB.Recordset")
set rs3 = excelconn.OpenSchema(20)
op=rs3("TABLE_NAME")
sql="select * from "+"["+ op+"]"
rs2.open sql,excelconn,1,1
'找到表
connstr = "driver={microsoft Access driver (*.mdb)}; dbq=" & server.mappath("../../data/#data1.mdb")
set conn = server.createobject("ADODB.CONNECTION")
conn.open connstr
strSQL="Delete * From Sheet1 Where user='"& Session("user")&"'"
conn.execute strSQL
connstr = "driver={microsoft Access driver (*.mdb)}; dbq=" & server.mappath("../../data/#data1.mdb")
set conn = server.createobject("ADODB.CONNECTION")
'刪除以前的信息
conn.open connstr
set rs4= Server.CreateObject("ADODB.Recordset")
rs4.open "select * from Sheet1",conn,3,3
if rs4.Fields.Count-3 <> rs2.Fields.Count-1 then
response.write "要上傳的表格格式不對，不能導入統一資料庫！"
response.end
else
'導入過程
for j = 1 to rs2.recordCount
rs4.addnew
for i = 0 to rs2.Fields.Count-1
rs4.Fields("user")=Session("user")
rs4.Fields(i+1)=rs2.Fields(i)
next
rs2.movenext
next
end if
' ............'
kl=""
strSQL="delete FROM Sheet1 where isnull(品名) = true"
conn.execute strSQL
conn.close
response.write "成功 "
response.redirect "../../index.asp"
end if
%>
『玖』 求助:用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