vsts 2008操作excel文件并插入sql数据库

最新推荐文章于 2024-10-16 15:30:07 发布

sunliusun

最新推荐文章于 2024-10-16 15:30:07 发布

阅读量556

点赞数

分类专栏： vsts经验总结文章标签： excel sql textbox integer string object

本文链接：https://blog.csdn.net/sunliusun/article/details/4657182

版权

vsts经验总结专栏收录该内容

1 篇文章 0 订阅

订阅专栏

        Dim excelapp As Object = CreateObject("Excel.Application")
        Dim excelbook As Object
        Dim excelsheet As Object
        Dim sheetName As String
        sheetName = TextBox9.Text

        excelbook = excelapp.Workbooks.Open(TextBox1.Text)
        excelsheet = excelbook.Worksheets.Item(sheetName)
        'MsgBox("ok")
        Dim ThisDataRows As Integer = excelsheet.UsedRange.Rows.Count
        Dim ThisDataColumns As Integer = excelsheet.UsedRange.Columns.Count
        TextBox10.Text = ThisDataRows & ";" & ThisDataColumns
        Dim i As Integer = 1
        Dim rowIndex As Integer = 1
        Dim colIndex As Integer = 0

Dim StrSql As String

        '******************数据库连接******************************
        Dim sCnn As String '定义连接串
        Dim cmd As New System.Data.SqlClient.SqlCommand
        sCnn = "user id=sa;data source=EXPSERVER/TEST;persist security info=True;initial catalog=DTScore;password=sa"
        Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection(sCnn)
        sqlConnection1.Open()
        cmd.Connection = sqlConnection1
        cmd.CommandType = System.Data.CommandType.Text
        'cmd.ExecuteNonQuery()

'***********************************************************
Dim ID, CheckYear, CheckQuarter, FactorCode, DutyDeptCode, DutyDeptName, FullScore, GetScore, CheckDeptCode, CheckDeptName, CheckUserCode, CheckUserName, OperTime As String

        CheckYear = TextBox4.Text
        CheckQuarter = TextBox5.Text
        DutyDeptCode = TextBox2.Text
        DutyDeptName = TextBox3.Text
        FullScore = 10
        CheckDeptCode = TextBox3.Text
        CheckDeptName = TextBox2.Text
        CheckUserCode = TextBox7.Text
        CheckUserName = TextBox6.Text
        OperTime = TextBox8.Text
        For j As Integer = 7 To ThisDataRows '单元格行
            i = 1
            For k As Integer = 3 To ThisDataColumns - 1 '单元格列

                GetScore = 0
                GetScore = excelsheet.cells(j, k).value   '取单元格的值
                If GetScore > 0 Then
                    DutyDeptName = excelsheet.cells(j, 2).value
                    DutyDeptCode = "00" & excelsheet.cells(j, 1).value()
                    FactorCode = (100000 + i).ToString
                    ID = "test-" + CheckYear + "-" + CheckQuarter + "-" + DutyDeptCode + "-" + FactorCode + "-" + CheckUserCode
                    StrSql = " insert into khCheckPerFactors (ID,CheckYear,CheckQuarter,FactorCode,DutyDeptCode,DutyDeptName,FullScore,                GetScore,CheckDeptCode,CheckDeptName,CheckUserCode,CheckUserName,OperTime,CheckDeptFlag) values ('" + ID + "'," + CheckYear + "," + CheckQuarter + ",'" + FactorCode + "','" + DutyDeptCode + "','" + DutyDeptName + "'," + FullScore + "," + GetScore + ",'" + CheckDeptCode + "','" + CheckDeptName + "','" + CheckUserCode + "','" + CheckUserName + "','" + OperTime + "','部门互评')"

                    cmd.CommandText = StrSql '"INSERT Into Region (RegionID, RegionDescription) VALUES (6, 'NorthWestern')" '
                    cmd.ExecuteNonQuery()
                    i = i + 1
                End If
            Next k

Next j
excelapp.Workbooks.close()

        GC.Collect()
        sqlConnection1.Close()
        MsgBox("OK")