添加引用
Imports System.ComponentModel
Imports System.Data.SqlClient
Imports System.IO
代码块
''' <summary>
''' 导入数据
''' </summary>
''' <param name="filePath">文件路径位置 D:\test1</param>
''' <param name="connStr">数据库连接字符串 Persist “Security Info=False;UID=sa;PWD=sa;Initial Catalog=Test;Connect Timeout = 60;Data Source=192.168.113.1”</param>
Private Sub importData(ByVal filePath As String ,ByVal connStr As String)
Dim strSql As String = ""
Dim strFields As String = ""
Dim sheetName As String = "Sheet1"
Dim conn As New SqlConnection(connStr)
Try
writeLog(Now & vbTab & "开始导入文件:" & filePath & vbCrLf)
Dim thisYear = Now.Year.ToString
Dim lastYear = (Now.Year - 1).ToString
'连接数据库
conn.Open()
Dim cmd(5) As SqlCommand
Dim trans As SqlTransaction
Dim i = 0
'表头
strFields = "[F1],[1月],[2月],[3月],[4月],[5月],[6月],[7月],[8月],[9月],[10月],[11月],[12月]"
trans = conn.BeginTransaction() '开启事务
'删除当前年度数据
strSql = " delete from report.dbo.table1 where year> " + lastYear
cmd(i) = New SqlCommand(strSql, conn)
cmd(i).Transaction = trans
i = i + 1
'插入sheet表的数据到table1表
strSql = " insert into Test.dbo.table1 " &
" SELECT " + strFields + " ,'" + thisYear + "' as 'year' " &
" FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=" + filePath + "', 'SELECT * FROM [" + sheet + "$]') "
cmd(i) = New SqlCommand(strSql, conn)
cmd(i).Transaction = trans
i = i + 1
'更新本年度table1表
strSql = " update Test.dbo.table1" &
" set [1月]=isnull([1月],0),[2月]=isnull([2月],0),[3月]=isnull([3月],0),[4月]=isnull([4月],0),[5月]=isnull([5月],0),[6月]=isnull([6月],0)" &
" ,[7月]=isnull([7月],0),[8月]=isnull([8月],0),[9月]=isnull([9月],0),[10月]=isnull([10月],0) ,[11月]=isnull([11月],0),[12月]=isnull([12月],0) " &
" where year > " + lastYear
cmd(i) = New SqlCommand(strSql, conn)
cmd(i).Transaction = trans
Try
For j = 0 To i
cmd(j).ExecuteNonQuery()
Next
trans.Commit() '事务完成
Catch ex As Exception
trans.Rollback()
Throw '抛出异常
End Try
conn.Close()
MsgBox("数据导入完成!")
writeLog(Now & vbTab & "完成文件导入:" & filePath & vbCrLf)
Catch ex As Exception
conn.Close()
writeLog(Now & vbTab & "文件:" & filePath & " 导入错误:" & ex.Message & vbCrLf)
MsgBox("出现错误!" & ex.Message)
End Try
End Sub
''' <summary>
''' 日志写入
''' </summary>
''' <param name="strContent">日志内容</param>
Private Sub writeLog(ByVal strContent As String)
If Not Directory.Exists(Application.StartupPath & "\Log") Then
Directory.CreateDirectory(Application.StartupPath & "\Log")
End If
File.AppendAllText(Application.StartupPath & "\Log\" & Format(Now(), "yyyyMMdd") & ".log", strContent)
End Sub