Imports System.IO
Imports System.Data.SqlClient
Imports ExcelDataReader
Imports Z.Dapper.Plus

Public Class Form1
    Dim Tables As DataTableCollection
    Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click

        Using ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "Excel 97-2003 Workbook|*.xls|Excel workbook|*.xlsx"}
            If ofd.ShowDialog() = DialogResult.OK Then
                txtFileName.Text = ofd.FileName
                Using stream = File.Open(ofd.FileName, FileMode.Open, FileAccess.Read)
                    Using reader As IExcelDataReader = ExcelReaderFactory.CreateReader(stream)
                        Dim result As DataSet = reader.AsDataSet(New ExcelDataSetConfiguration() With {
                                                                 .ConfigureDataTable = Function() New ExcelDataTableConfiguration() With {
                                                                 .UseHeaderRow = True}})

                        Tables = result.Tables
                        cboSheet.Items.Clear()
                        For Each table As DataTable In Tables
                            cboSheet.Items.Add(table.TableName)
                        Next
                    End Using
                End Using

            End If

        End Using
    End Sub

    Private Sub cboSheet_SelectedIndexChanged(sender As Object, e As EventArgs) Handles cboSheet.SelectedIndexChanged
        Dim dt As DataTable = Tables(cboSheet.SelectedItem.ToString())
        DataGridView1.DataSource = dt
        If dt IsNot Nothing Then
            Dim list As List(Of Customer) = New List(Of Customer)()
            'Dim existencia As Double
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim Customer As Customer  = New Customer()
                Customer.SUBDEPTO         = dt.Rows(i)("SUBDEPTO").ToString()
                Customer.DESCRIPDEPTO     = dt.Rows(i)("DESCRIPDEPTO").ToString()
                Customer.MARCA            = dt.Rows(i)("MARCA").ToString()
                Customer.DESCRIPMARCA     = dt.Rows(i)("DESCRIPMARCA").ToString()
                Customer.PRODUCTO         = dt.Rows(i)("PRODUCTO").ToString()
                Customer.DESCRIPPRODUCTO  = dt.Rows(i)("DESCRIPPRODUCTO").ToString()
                Customer.EXISTENCIA       = Convert.ToSingle(dt.Rows(i)("EXISTENCIA").ToString())
                Customer.COSTOPROMEDIOTDA = Convert.ToSingle(dt.Rows(i)("COSTOPROMEDIOTDA").ToString())
                Customer.PRECIOVENTATDA   = Convert.ToSingle(dt.Rows(i)("PRECIOVENTATDA").ToString())
                Customer.BARRAS           = dt.Rows(i)("BARRAS").ToString()
                Customer.BARRAS2          = dt.Rows(i)("BARRAS2").ToString()
                Customer.BARRAS3          = dt.Rows(i)("BARRAS3").ToString
                Customer.TIPOPRODUCTO     = dt.Rows(i)("TIPOPRODUCTO").ToString()

            Next
            CustomerBindingSource.DataSource = list
        End If

    End Sub

    Private Sub BtnImporta(sender As Object, e As EventArgs) Handles BtnImporta.Click
       
       
        Try
            Dim list As List(Of Customer) = TryCast(CustomerBindingSource.DataSource, List(Of Customer))
            If list IsNot Nothing Then
                DapperPlusManager.Entity(Of Customer).Table("CLIENTES")
                Using db As New SqlConnection(My.Settings.CONECTAR)

                    db.BulkInsert(list)
                End Using

                MessageBox.Show("Finish !!!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If
            Me.DataGridView1.DataSource = ""
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Private Sub Salir_Click(sender As Object, e As EventArgs) Handles Salir.Click
        Me.Close()

    End Sub

   
******************************************************************************************
Public Class Customer
	Public Property SUBDEPTO As String
	Public Property DESCRIPDEPTO As String
	Public Property MARCA As String
	Public Property DESCRIPMARCA As String
	Public Property PRODUCTO As String
	Public Property DESCRIPPRODUCTO As String
	Public Property EXISTENCIA As Single
	Public Property COSTOPROMEDIOTDA As Single
	Public Property PRECIOVENTATDA As Single
	Public Property BARRAS As String
	Public Property BARRAS2 As String
	Public Property BARRAS3 As String
	Public Property TIPOPRODUCTO As String
End Class
 













