亚洲天堂在线观看_ 導入Excel電子表格數據到SQL Sever數據庫

<%
‘服務(wù)器端還必須要設置的一點(diǎn)是COM組件的操作權限。在命令行鍵入“DCOMCNFG”，則進(jìn)入COM組件配置界面，選擇MicrosoftExcel后點(diǎn)擊屬性按鈕，將三個(gè)單選項一律選擇自定義，編輯中將Everyone加入所有權限。保存完畢后重新啟動(dòng)服務(wù)器。
‘http://www.blueidea.com/tech/program/2006/3547.asp
‘On Error Resume Next
‘導入Excel電子表格數據到SQL Sever數據庫 By Dicky 2004-12-27 16:41:12 QQ:25941
Function Open_Conn(SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName,SqlConn)
‘創(chuàng )建數據庫連接對象并打開(kāi)數據庫連接
Dim ConnStr
‘SQL Server數據庫連接參數：數據庫名、用戶(hù)密碼、用戶(hù)名、連接名（本地用local，外地用IP）
ConnStr = "Provider=Sqloledb; User ID=" & SqlUsername & "; Password=" & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source=" & SqlLocalName & ";"
Set SqlConn = Server.CreateObject("ADODB.Connection")
SqlConn.Open ConnStr
If Err Then
  Err.Clear
  Set SqlConn = Nothing
  Response.Write "數據連接錯誤!"
  Response.End
End If
End Function

Function Close_Conn(SqlConn)
‘關(guān)閉數據庫連接并清除數據庫連接對象
If IsObject(SqlConn) Then
SqlConn.Close
Set SqlConn = Nothing
End If
End Function

Call Open_Conn("Shat_EDG","","sa","(local)",SqlConn) ‘打開(kāi)SQL Server數據庫連接

Function Get_EMP_CnName(NTACCNT)
‘根據用戶(hù)NT賬號得到用戶(hù)中文名
Dim Sql,Rs
Sql = "Select EMP_CNAME From RF_EMPLOYEE Where EMP_NTACCNT=‘"&NTACCNT&"‘"
Set Rs = Server.CreateObject("Adodb.RecordSet")
Rs.Open Sql,SqlConn,1,1
If Rs.Eof Then
  Get_EMP_CnName = ""
Else
  Get_EMP_CnName = Rs("EMP_CNAME")
End If
Rs.Close
Set Rs = Nothing
End Function %>
<html>
<head>
<title>導入Excel電子表格數據到SQL Sever數據庫</title>
<body bgcolor="#ACD9AF">
<center><b>導入Excel電子表格數據到SQL Sever數據庫</b></center>
<FORM METHOD="POST" name="form1">
請選擇數據源: <input type="file" name="File" size="40" title="請選擇需要導出的Excel文件"> <input type=submit name=submit value="開(kāi)始導出">
<br>請選擇目標表:
<SELECT NAME="Table" title="請選擇需要導入數據的表">
<option></option>
<% Dim RsSqlDatabaseTable
Set RsSqlDatabaseTable = SqlConn.OpenSchema(20)
Do While Not RsSqlDatabaseTable.Eof %>
<option<%If Trim(Request("Table"))=RsSqlDatabaseTable(2) Then Response.Write " selected"%>><%=RsSqlDatabaseTable(2)%></option>
<% RsSqlDatabaseTable.MoveNext:Loop
Set RsSqlDatabaseTable = Nothing %>
</SELECT>
</FORM>
<font color=blue>導出過(guò)程中請不要刷新頁(yè)面！</font><br>
<%
If Right(Trim(Request("File")),3) = "xls" Then
Response.Write "<font color=red>您所導出的Excel文件是："&Trim(Request("File"))&"</font><br>"
End If
‘=====================ASP讀取EXCEL注事項=====================================
‘i)將Excel97或Excel2000生成的XLS文件（book）看成一個(gè)數據庫，其中的每一個(gè)工作表(sheet)看成數據庫表
‘ii)ADO假設Excel中的第一行為字段名.所以你定義的范圍中必須要包括第一行的內容
‘iii)Excel中的行標題（即字段名）不能夠包含數字. Excel的驅動(dòng)在遇到這種問(wèn)題時(shí)就會(huì )出錯的。例如你的行標題名為“F1”
‘iiii)如果你的Excel電子表格中某一列同時(shí)包含了文本和數字的話(huà),那么Excel的ODBC驅動(dòng)將不能夠正常, 處理這一行的數據類(lèi)型，你必須要保證該列的數據類(lèi)型一致
‘E-MAIL:Kaxue@Hotmail.com QQ:484110 HOMEPAGE:www.Flyday.net
‘整理時(shí)間：Thursday, May 23, 2002 5:54 PM WIN2000SERVER+IIS5 測式通過(guò)
‘============================================================================
If Trim(Request("File")) = "" Then
Response.Write "<font color=red>對不起，請選擇需要導出的Excel文件！</font>"
Response.End
ElseIf Right(Trim(Request("File")),3) <> "xls" Then
Response.Write "<font color=red>對不起，請確定您要導出的是Excel文件！</font>"
Response.End
Else
‘列出所選擇的Excel文件中的所有工作表
Dim ExeclFile,objExcelApp,objExcelBook
ExeclFile = Trim(Request("File"))
Set objExcelApp = CreateObject("Excel.Application")
objExcelApp.Quit
objExcelApp.DisplayAlerts = False ‘不顯示警告
objExcelApp.Application.Visible = False ‘不顯示界面
objExcelApp.WorkBooks.Open(ExeclFile)
set objExcelBook = objExcelApp.ActiveWorkBook
reDim arrSheets(objExcelBook.Sheets.Count)
For i=1 to objExcelBook.Sheets.Count
  arrSheets(i) = objExcelBook.Sheets(i).Name
‘  Response.Write arrSheets(i)
Next
objExcelApp.Quit
Set objExeclApp = Nothing
‘列出所選擇的Excel文件中的所有工作表
End If

Dim Conn,Driver,DBPath,Rs,SqlInsert,SqlDelete
‘建立Connection對象
Set Conn = Server.CreateObject("ADODB.Connection")
Driver = "Driver={Microsoft Excel Driver (*.xls)};"
‘DBPath = "DBQ=" & Server.MapPath( "HRB.xls" )
DBPath = "DBQ=" & Trim(Request("File"))
‘調用Open 方法打開(kāi)數據庫
Conn.Open Driver & DBPath

‘DSN連接方式
‘Conn.Open "Dsn=test"
‘注意表名一定要以下邊這種格式 "[表名$]" 書(shū)寫(xiě)
For i = 1 To UBound(arrSheets)‘開(kāi)始循環(huán)所有工作表
Sql = "SELECT * FROM ["& arrSheets(i) &"$] "
Set Rs = Conn.Execute(Sql)

If Rs.Eof And Rs.Bof Then
  Response.write "沒(méi)有找到您需要的數據!!<br>"
Else
‘  Response.Write "<font color=blue>工作表:</font><font color=green>"&arrSheets(i)&"</font><br>"
  Do While Not Rs.Eof
   SqlInsert = "Insert Into "&Trim(Request("Table"))&" (EDG_Project_Name,EDG_Project_No,EDG_Project_VM,EDG_Project_VM_CnName,EDG_Project_M,EDG_Project_M_CnName,EDG_Project_Director,EDG_Project_Director_CnName) Values (‘"&Trim(Rs(0))&"‘,‘"&Trim(Rs(1))&"‘,‘"&Trim(Rs(2))&"‘,‘"&Trim(Rs(2))&"("&Get_EMP_CnName(Trim(Rs(2)))&")"&"‘,‘"&Trim(Rs(3))&"‘,‘"&Trim(Rs(3))&"("&Get_EMP_CnName(Trim(Rs(3)))&")"&"‘,‘"&Trim(Rs(4))&"‘,‘"&Trim(Rs(4))&"("&Get_EMP_CnName(Trim(Rs(4)))&")"&"‘)"
   ‘此處插入的值根據實(shí)際數據庫結構進(jìn)行調整
   SqlConn.Execute(SqlInsert)‘插入Excel表格裏所有東東
  Rs.MoveNext
  Loop
  Response.Write "<font color=red>恭喜，成功導出Excel文件中工作表[</font><font color=blue>"&arrSheets(i)&"</font><font color=red>]的數據到SQL Server數據表[<font color=blue>"&Trim(Request("Table"))&"</font>]中！^_^</font><br>"
End If

Rs.Close
Set Rs = Nothing
Next‘循環(huán)所有工作表結束

Call Close_Conn(Conn) ‘關(guān)閉Excel數據庫連接
Call Close_Conn(SqlConn) ‘關(guān)閉SQL Server數據庫連接 %>
</body>
</html>

本站僅提供存儲服務(wù)，所有內容均由用戶(hù)發(fā)布，如發(fā)現有害或侵權內容，請點(diǎn)擊舉報。

欧美性猛交XXXX免费看蜜桃,成人网18免费韩国,亚洲国产成人精品区综合,欧美日韩一区二区三区高清不卡,亚洲综合一区二区精品久久