vb.net 将sqlsever数据导入excel

Imports excel = Microsoft.Office.Interop.Excel

Public Class Form1


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: 这行代码将数据加载到表“FTMESDataSet1.tab1”中。您可以根据需要移动或删除它。
        Me.Tab1TableAdapter.Fill(Me.FTMESDataSet1.tab1)
        'TODO: 这行代码将数据加载到表“FTMESDataSet.WIPLOT”中。您可以根据需要移动或删除它。
        Me.WIPLOTTableAdapter.Fill(Me.FTMESDataSet.WIPLOT)

    End Sub

    Private Sub import_Click(sender As Object, e As EventArgs) Handles import.Click

        Dim ds As New SqlClient.SqlConnection
        Dim dm As New SqlClient.SqlCommand
        'sql连接
        ds.ConnectionString = "Data Source=;Initial Catalog=;User ID=;Password=;"

        dm.Connection = ds
        dm.CommandText = "select  *  from WIPLOT where custproduct = '" & custpro.Text & "'"
        Dim dataAdapter As New SqlClient.SqlDataAdapter
        dataAdapter.SelectCommand = dm
        Dim mydataset As New DataSet()
        ds.Open()
        dataAdapter.SelectCommand.ExecuteNonQuery()

        dataAdapter.Fill(mydataset, "wiplot")

        'excel对象
        Dim appXL As excel.Application
        '工作簿
        Dim wbXl As excel.Workbook
        '工作表
        Dim shXL As excel.Worksheet
        '定义工作区域
        Dim raXL As excel.Range
        ' Start Excel and get Application object.
        appXL = CreateObject("Excel.Application")
        '让excel可见
        appXL.Visible = True
        ' Add a new workbook.
        wbXl = appXL.Workbooks.Add
        shXL = wbXl.ActiveSheet


        ' Add table headers going cell by cell.
        shXL.Cells(1, 1).Value = "Device"
        shXL.Cells(1, 2).Value = "Cust Lot No"
        shXL.Cells(1, 3).Value = "WO NO"
        shXL.Cells(1, 4).Value = "Vendor Lot NO."
        shXL.Cells(1, 5).Value = "PKG Type"
        shXL.Cells(1, 6).Value = "REC_DATE"
        shXL.Cells(1, 7).Value = "Issue Date"
        shXL.Cells(1, 8).Value = "Issue Qty"
        shXL.Cells(1, 9).Value = "Date code"
        shXL.Cells(1, 10).Value = "RCV"
        shXL.Cells(1, 11).Value = "FT1"
        shXL.Cells(1, 12).Value = "FT2"
        shXL.Cells(1, 13).Value = "ReelCode-Trail"
        shXL.Cells(1, 14).Value = "LOSS"
        ' 设置字体格式
        With shXL.Range("A1", "N1")
            .Font.Bold = True
            .VerticalAlignment = excel.XlVAlign.xlVAlignCenter
        End With
        ' Create an array to set multiple values at once.

        Dim n As Integer = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 1).value = mydataset.Tables("wiplot").Rows(n).Item("custproduct").ToString
            n += 1
        Next
        n = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 2).value = mydataset.Tables("wiplot").Rows(n).Item("custlotno").ToString
            n += 1
        Next
        n = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 3).value = mydataset.Tables("wiplot").Rows(n).Item("custorder").ToString
            n += 1
        Next
        n = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 4).value = mydataset.Tables("wiplot").Rows(n).Item("itestlotno").ToString
            n += 1
        Next
        n = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 5).value = mydataset.Tables("wiplot").Rows(n).Item("packageForm").ToString
            n += 1
        Next
        n = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 6).value = mydataset.Tables("wiplot").Rows(n).Item("ReceivingDate").ToString
            n += 1
        Next
        n = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 8).value = mydataset.Tables("wiplot").Rows(n).Item("incomenum").ToString
            n += 1
        Next
        n = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 9).value = mydataset.Tables("wiplot").Rows(n).Item("code").ToString
            n += 1
        Next
        n = 0
        For i = 2 To mydataset.Tables("wiplot").Rows.Count + 1

            shXL.Cells(i, 13).value = mydataset.Tables("wiplot").Rows(n).Item("reelcode").ToString
            n += 1
        Next

        For i = 0 To mydataset.Tables("wiplot").Rows.Count - 1
            If mydataset.Tables("wiplot").Rows(i).Item("status").ToString = "CHECKIN" Then
                shXL.Range(shXL.Cells(i + 2, 1), shXL.Cells(i + 2, 14)).Interior.ColorIndex = 4

            End If

        Next



        shXL.Cells.HorizontalAlignment = 3
        '自动调整单元格行高列高
        shXL.Cells.EntireColumn.AutoFit()
        shXL.Cells.EntireRow.AutoFit()
        ' Make sure Excel is visible and give the user control of Excel's lifetime.
        appXL.Visible = True
        appXL.UserControl = True
        ' Release object references.
        raXL = Nothing
        shXL = Nothing
        wbXl = Nothing
        appXL.Quit()
        appXL = Nothing
        Exit Sub
Err_Handler:
        MsgBox(Err.Description, vbCritical, "Error: " & Err.Number)
    End Sub


End Class