VB 数据库如何读取本地Excel表并将数据存入到数据表中

添加引用

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