Transfer Data dari Excell ke Database

Kode ini aku temukan di folder.  Daripada kode menghilang, lebih baik aku publish di sini, siapa tau nantinya diperlukan. Karena belum pernah menerapkan kode ini, jadi saya belum tahu cara menerapkan kode ini di dunia nyata. Tiga methode berikut akan membantu mentransfer data excell ke database.

Method untuk mendapatkan koneksi string

Private Function GetConnection(ByVal fstrExcelFileName As String) As String
   Return "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & fstrExcelFileName & ";" & _
          "Extended Properties=""Excel 8.0;"""
End Function

method untuk mendapatkan data dari file Excell

Public Function GetDataFromExcel(ByVal foQuery As ArrayList, ByVal fstrDataSetName As String) As DataSet
     Dim oMyData As OleDb.OleDbDataAdapter = Nothing
     Dim oMyTable As DataTable = Nothing
     Dim oMyDataSet As DataSet = Nothing
     Dim strQuery As String
     Dim oConn As OleDb.OleDbConnection = Nothing
     Dim strXLFileName As String = ""
     Try
        oConn = New OleDb.OleDbConnection()
        oConn.ConnectionString = GetConnection(strXLFileName)
        oConn.Open()
        oMyDataSet = New DataSet()

        If Not IsNothing(foQuery) AndAlso foQuery.Count > 0 Then
           Dim iLoopIndex As Integer
           Dim iLoopCount As Integer = foQuery.Count - 1

           For iLoopIndex = 0 To iLoopCount
              strQuery = foQuery(iLoopIndex)
              oMyData = New OleDb.OleDbDataAdapter(strQuery, oConn)
              oMyTable = New DataTable()
              oMyTable.TableName = Replace(strQuery.Substring(strQuery.LastIndexOf(CChar("[")) + 1), "$]", "")
              oMyData.Fill(oMyTable)
              oMyDataSet.Tables.Add(AddRowNo(oMyTable))
           Next
        End If
        oMyDataSet.DataSetName = fstrDataSetName
        Return oMyDataSet
     Catch oEx As Exception
        Throw oEx
     Finally
        oMyTable = Nothing
        oMyDataSet = Nothing
        oMyData.Dispose()
        oMyData = Nothing
        oConn.Dispose()
        oConn = Nothing
     End Try
End Function

Private Function AddRowNo(ByVal foTable As DataTable) As DataTable
    Try
        Dim iLoopIndex As Integer
        Dim iLoopCount As Integer = foTable.Rows.Count - 1
        For iLoopIndex = 0 To iLoopCount
            foTable.Rows(iLoopIndex).Item("ROW_NO") = iLoopIndex + 2
        Next
        Return foTable
    Catch oEx As Exception
        Throw oEx
    End Try
End Function

Semoga membantu. Happy Coding

About Yuniar

Penulis sekarang lebih banyak bekerja menggunakan teknologi Microsoft .NET secara umum dan TIDAK lagi menggunakan aplikasi MapXtreme.net dari MapInfo. Karena itu mohon maaf, jika pertanyaan-pertanyaan mengenai MapXtreme sudah tidak bisa saya jawab lagi.

Posted on 14 Oktober 2009, in .NET and tagged , , , . Bookmark the permalink. 1 Komentar.

  1. Cara menggunakan gimana? trus database apa saja yang bisa.

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: