熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> .NET編程 >> 正文

導入Excel電子表格數據到SQL Sever數據庫的方法

2013-11-13 10:12:31  來源: .NET編程 

  完整代碼如下
  
  <%
  On Error Resume Next
  導入Excel電子表格數據到SQL Sever數據庫 By Dicky ::
  Function Open_Conn(SqlDatabaseNameSqlPasswordSqlUsernameSqlLocalNameSqlConn)
  創建數據庫連接對象並打開數據庫連接
   Dim ConnStr
   SQL Server數據庫連接參數數據庫名用戶密碼用戶名連接名(本地用local外地用IP)
   ConnStr = Provider=Sqloledb; User ID= & SqlUsername & ; Password= & SqlPassword & ; Initial Catalog = & SqlDatabaseName & ; Data Source= & SqlLocalName & ;
   Set SqlConn = ServerCreateObject(ADODBConnection)
   SqlConnOpen ConnStr
   If Err Then
   ErrClear
   Set SqlConn = Nothing
   ResponseWrite 數據連接錯誤!
   ResponseEnd
   End If
  End Function
  
  Function Close_Conn(SqlConn)
  關閉數據庫連接並清除數據庫連接對象
   If IsObject(SqlConn) Then
   SqlConnClose
   Set SqlConn = Nothing
   End If
  End Function
  
  Call Open_Conn(Shat_EDGsa(local)SqlConn) 打開本地SQL Server數據庫連接
  Call Open_Conn(Shat_EDGsasshSqlConn) 打開遠程SQL Server數據庫連接
  
  Function Get_EMP_CnName(NTACCNT)
  根據用戶NT帳號得到用戶中文名
   Dim SqlRs
   Sql = Select EMP_CNAME From RF_EMPLOYEE Where EMP_NTACCNT=&NTACCNT&
   Set Rs = ServerCreateObject(AdodbRecordSet)
   RsOpen SqlSqlConn
   If RsEof Then
   Get_EMP_CnName =
   Else
   Get_EMP_CnName = Rs(EMP_CNAME)
   End If
   RsClose
   Set Rs = Nothing
  End Function %>
  <html>
  <head>
  <title>導入Excel電子表格數據到SQL Sever數據庫</title>
  <body bgcolor=#ACDAF>
  <center><b>導入Excel電子表格數據到SQL Sever數據庫</b></center>
  <FORM METHOD=POST name=form>
  請選擇數據源(本地庫):
  <SELECT NAME=Table title=請選擇需要導入數據的表>
   <option></option>
  <% Dim RsSqlDatabaseTable
   Set RsSqlDatabaseTable = SqlConnOpenSchema()
   Do While Not RsSqlDatabaseTableEof %>
   <option<%If Trim(Request(Table))=RsSqlDatabaseTable() Then ResponseWrite selected%>><%=RsSqlDatabaseTable()%></option>
  <% RsSqlDatabaseTableMoveNext:Loop
   Set RsSqlDatabaseTable = Nothing %>
  </SELECT><input type=submit name=submit value=開始導出>
  <br>請選擇目標表(遠程庫):
  <SELECT NAME=Table title=請選擇需要導入數據的表>
   <option></option>
  <% Dim RsSqlDatabaseTable
   Set RsSqlDatabaseTable = SqlConnOpenSchema()
   Do While Not RsSqlDatabaseTableEof %>
   <option<%If Trim(Request(Table))=RsSqlDatabaseTable() Then ResponseWrite selected%>><%=RsSqlDatabaseTable()%></option>
  <% RsSqlDatabaseTableMoveNext:Loop
   Set RsSqlDatabaseTable = Nothing %>
  </SELECT>
  </FORM>
  <font color=blue>導出過程中請不要刷新頁面!</font><br>
  <%
  If Trim(Request(Table)) <> Then
   Dim SqlRs
   Sql = Select * From Sheet$ Sheet$是我們實現用SQL Server自身數據轉換功能得到的表名
   Set Rs = SqlConnExecute(Sql)
   If RsEof And RsBof Then
   Responsewrite 沒有找到您需要的數據!!<br>
   Else
   Do While Not RsEof
    SqlInsert = Insert Into &Trim(Request(Table))& (EDG_Project_NameEDG_Project_NoEDG_Project_VMEDG_Project_VM_CnNameEDG_Project_MEDG_Project_M_CnNameEDG_Project_DirectorEDG_Project_Director_CnName) Values (&Trim(Rs(工程名稱))&&Trim(Rs(工程編號))&&Trim(Rs(項目副理))&&Trim(Rs(項目副理))&(&Get_EMP_CnName(Trim(Rs(項目副理)))&)&&Trim(Rs(項目經理))&&Trim(Rs(項目經理))&(&Get_EMP_CnName(Trim(Rs(項目經理)))&)&&Trim(Rs(項目總監))&&Trim(Rs(項目總監))&(&Get_EMP_CnName(Trim(Rs(項目總監)))&)&)
    ResponseWrite SqlInsert
    此處插入的值根據實際數據庫結構進行調整
    SqlConnExecute(SqlInsert)插入Excel表格裏所有東東
   RsMoveNext
   Loop
   ResponseWrite <font color=red>恭喜成功導出數據!^_^</font><br>
   End If
  
   RsClose
   Set Rs = Nothing
  End If
  
  Call Close_Conn(SqlConn) 關閉Excel數據庫連接
  Call Close_Conn(SqlConn) 關閉SQL Server數據庫連接 %>
  </body>
  </html>
From:http://tw.wingwit.com/Article/program/net/201311/12903.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.