Import File Excel Ke Vb.Net ?

Pertanyaan :

Gimana Sih Nampilin Data Dari File Excell Ke DataGrid ? Trus Insert Ke Database (Newbie Nih ) Mohon Pencerahan Kk Geek.Net

Thanks 

Jawab 1: (Sumber : http://netindonesia.net/forums/p/10386/81675.aspx)

You can use the OleDb - Namespace. The rest is like Access, Sql Server etc.


Dim _oConn As OleDbConnection, _
_oCmd As OleDbCommand, _
_oDA As OleDbDataAdapter, _
_ds As New DataSet()

_oConn = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;Data Source=e:\temp\teilnehmer.xls;Extended Properties=""Excel 8.0""")
_oCmd = New OleDbCommand("Select * From [q_export_Teilnehmer$]", _oConn)
_oDA = New OleDbAdapter(_oCmd)

_oConn.Open()
_oDA.Fill(_ds, "Data")
_oConn.Close()

Console.WriteLine(_ds.GetXml())

q_export_Teilnehmer is the name of a table, so add '$'.

Jawab2 : (Sumber : http://netindonesia.net/forums/p/10386/81675.aspx)

Private Sub BtnImport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnImport.Click
        Dim opendialog As New OpenFileDialog

        If opendialog.ShowDialog = Windows.Forms.DialogResult.OK Then
            TextBox1.Text = opendialog.FileName
            ImportAttendence(TextBox1.Text, DataGridView1)
        End If
    End Sub



    Public Function ImportAttendence(ByVal PrmPathExcelFile As String, ByVal dgv As DataGridView)
        Dim MyConnection As System.Data.OleDb.OleDbConnection

        Try

            Dim DtSet As System.Data.DataSet
            Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
            MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " & "data source='" & PrmPathExcelFile & " '; " & "Extended Properties=Excel 8.0;")


            MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [sheet1$]", MyConnection)
            MyCommand.TableMappings.Add("Table", "Attendence")
            DtSet = New System.Data.DataSet
            MyCommand.Fill(DtSet)

            dgv.DataSource = DtSet.Tables(0)
            MyConnection.Close()
        Catch ex As Exception
            MyConnection.Close()
        End Try
    End Function

Sekarang Dibalik Export Dari Datagridview -> Excell (VB9)

Jawaban 3 : (Sumber : http://vb.net-informations.com/datagridview/vb.net_datagridview_import.htm)

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim DtSet As System.Data.DataSet
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
        MyCommand.TableMappings.Add("Table", "Net-informations.com")
        DtSet = New System.Data.DataSet
        MyCommand.Fill(DtSet)
        DataGridView1.DataSource = DtSet.Tables(0)
        MyConnection.Close()

    End Sub
End Class