Excelden Datagride Veri Aktarımı

Kod:

Private Sub Excel2Grid(ByVal sFile As String)

        xlApp = New Excel.Application()

        xlWorkBook = xlApp.Workbooks.Open(sFile)

        xlWorkSheet = xlWorkBook.Worksheets("Sayfa1")

        DataGridView1.Rows.Clear()

        DataGridView1.Columns.Clear()

        Dim iRow, iCol As Integer







        For iCol = 1 To xlWorkSheet.Columns.Count



            If xlWorkSheet.Cells(1, iCol).value Is Nothing Then

                Exit For

            Else

                Dim col As DataGridViewTextBoxColumn = New DataGridViewTextBoxColumn()

                col.HeaderText = xlWorkSheet.Cells(1, iCol).value

                Dim colIndex As Integer = DataGridView1.Columns.Add(col)

            End If

        Next





        For iRow = 2 To xlWorkSheet.Rows.Count - 1

            If xlWorkSheet.Cells(iRow, 1).value Is Nothing Then

                Exit For

            Else

                Dim row As String() = New String() {xlWorkSheet.Cells(iRow, 1).value, xlWorkSheet.Cells(iRow, 2).value.ToString(), xlWorkSheet.Cells(iRow, 3).value}

                DataGridView1.Rows.Add(row)

            End If

        Next











        xlWorkBook.Close()

        xlApp.Quit()

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)

        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)

    End Sub

Kod:

Imports System.Globalization

Imports System.IO

Imports ClosedXML.Excel

Imports ExcelDataReader



Dim tables As DataTableCollection



    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Using ofd As OpenFileDialog = New OpenFileDialog() With {.Filter = "Excel File|*.xlsx;*.xls"}

            If ofd.ShowDialog() = DialogResult.OK Then

                TextBox1.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

                        ComboBox1.Items.Clear()



                        For Each table As DataTable In tables

                            ComboBox1.Items.Add(table.TableName)

                            ComboBox1.Enabled = True

                        Next







                    End Using



                End Using



            End If



        End Using

    End Sub



    Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged

        Try

            Dim dt As DataTable = tables(ComboBox1.SelectedItem.ToString())



            DataGridView1.DataSource = dt

            



        Catch ex As Exception



        End Try





    End Sub