16,555
社区成员
发帖
与我相关
我的任务
分享
Private Sub dataimport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dataimport.Click
Dim strPath As String = File1.PostedFile.FileName ‘由用户选择所在的文件路径
Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "; Extended Properties=Excel 8.0;"
Dim olecn As New OleDb.OleDbConnection
olecn.ConnectionString = sConnectionString
olecn.Open()
'Console.WriteLine("数据表已经连接上"),获取excel表
Dim tblname As DataTable = olecn.GetOleDbSchemaTable(OleDb.OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
'获取sheet名,其中(0)(1)...(N): 按名称排列的表单元素
Dim k As Integer, l As Integer
Dim tablename As String = ""
Dim TABLEEXCEL As New DataSet
Dim table_name(tblname.Rows.Count - 1) As String
For k = 0 To tblname.Rows.Count - 1
tablename = tblname.Rows(k)(2).ToString().Trim
table_name(k) = "[" & tablename.Replace(" ' ", " ") & "]"
Dim sqlcn As SqlClient.SqlConnection = New SqlClient.SqlConnection("workstation id=WANGQIANG;packet size=4096;user id=sa;" & _
"password=120979120979;data source=WANGQIANG;persist security info=False;initial catalog=assessonline")
sqlcn.Open()
' Dim sqlstr As String = "EXEC master..xp_cmdshell 'bcp assessonline.dbo.basedist_end in c:\三张大表.xls -c -S wangqiang -U sa -P 120979120979'" ’用BCP工具,老提示说字符长度不够,不想改表结构,不用这条语句
Dim sqlstr As String = "insert into basedist_end(contract_id, contract_no, product_sort, client, saledepartment, consign_cost,consign_amount, kaohe_term, dist_type, contract_type)" & _
"SELECT contract_id, contract_no, product_sort, client, saledepartment, consign_cost,consign_amount, kaohe_term, dist_type, contract_type" & _
" FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=" & strPath &"', table_name(k))"
Dim sqlexport As SqlClient.SqlCommand = New SqlClient.SqlCommand(sqlstr, sqlcn)
sqlexport.ExecuteReader()
Next k
sqlcn.Close()
Response.Write(" <script> alert('恭喜,所有数据导入成功!') </script> ")
End Sub
strCmd = "dtsrun " & _
" /S " & ControlChars.Quote & _SQLName & ControlChars.Quote & _
" /U " & ControlChars.Quote & _SQLUser & ControlChars.Quote & _
" /P " & ControlChars.Quote & _SQLPass & ControlChars.Quote & _
" /N " & ControlChars.Quote & "Load_SSMCHDTAP" & ControlChars.Quote & _
" /A " & ControlChars.Quote & "@FileName" & ControlChars.Quote & _
"=" & ControlChars.Quote & strFile & ControlChars.Quote
_DtsCode = Shell(strCmd, AppWinStyle.NormalFocus, True)
If _DtsCode <> 0 Then _Err = "上载商户资料文件时失败."