Import Excel File to Grid Using DataSet

untuk mengambil file excel kita menggunakan file dialog dan dialog result.

Dim fd As New OpenFileDialog
Dim result As New DialogResult
Dim loXlApp As New Excel.ApplicationClass
Dim loXlWorkBook As New Excel.Worksheet
Dim loXlWorkSheet As Excel.Workbook = Nothing

Try
With fd
.Filter = "Excel Files|*.xls"
.Title = "Browse Excel File"
result = .ShowDialog()
End With
If result = System.Windows.Forms.DialogResult.OK Then
grdPnisEntry.Columns.Clear()
grdPnisEntry.Clear()

Dim column As PowerGridColumn
Dim row As DataRow

loXlWorkSheet = loXlApp.Workbooks.Open(fd.FileName)
loXlWorkBook = loXlWorkSheet.Worksheets(1)

Dim range As Excel.Range
range = loXlWorkBook.UsedRange
Dim rCnt, cCnt, i As New Integer
Dim Obj As New Object
Dim value As String = String.Empty
Dim mopniDataSet As New System.Data.DataSet
mopniDataSet.Clear()
mopniDataSet.Tables.Add(New System.Data.DataTable)

For rCnt = 3 To range.Rows.Count
row = mopniDataSet.Tables(0).NewRow
For cCnt = 1 To range.Columns.Count
Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)
If rCnt = 3 Then
If cCnt = 1 Then
column = New PowerGridColumn()
column.Name = "clmDefinition"
column.FieldName = "Definition"
column.ReadOnly = True
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
column = New PowerGridColumn()
column.Name = "clmSysKey"
column.FieldName = "SysKey"
column.ReadOnly = False
column.Visible = False
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
miColumnCount = miColumnCount + 2
End If
column = New PowerGridColumn()
column.Name = Obj.value
column.FieldName = Obj.value
column.ReadOnly = True
grdPnisEntry.Columns.Add(column)
mopniDataSet.Tables(0).Columns.Add(column.Name)
miColumnCount = miColumnCount + 1
Else
If Obj.value = String.Empty AndAlso rCnt > 4 Then
row(0) = CType(range.Cells(2, 2), Excel.Range).Value
i = grdPnisEntry.Rows - 1
row(cCnt + 1) = grdPnisEntry.Cell((cCnt + 1), i)
Else
row(0) = CType(range.Cells(2, 2), Excel.Range).Value
row(cCnt + 1) = Obj.value
End If
If cCnt = range.Columns.Count Then
mopniDataSet.Tables(0).Rows.Add(row)
End If
End If
Next cCnt
Next

grdPnisEntry.DataSource = mopniDataSet.Tables(0)
grdPnisEntry.Columns(1).GroupIndex = 1
grdPnisEntry.Columns(1).Width = 100
grdPnisEntry.Columns(1).SetColumnVisibility(False, 2)
grdPnisEntry.Columns(4).Width = 300
grdPnisEntry.Columns(5).Width = 300
grdPnisEntry.Columns(6).Width = 300
grdPnisEntry.DoExpandAllGroups()
Dim liCount As Integer = 0
txtImportExcel.Text = fd.FileName
fd.Dispose()
loXlWorkSheet.Close()
loXlApp.Quit()
Me.lbImportExcel = True
txtAssessmentTypeDesc.Clear()
txtAssessmentTypeDesc.ReadOnly = True
mSetHyperlinks(False)
miCompItemId = CInt(Sync.Item(Me.Name, "COMPETENCY_ITEM_ID").Value)
End If
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Information")
Finally
If result = System.Windows.Forms.DialogResult.OK Then
// untuk melepas resource dari object excel yg kita buat.
Runtime.InteropServices.Marshal.ReleaseComObject(loXlApp)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkBook)
Runtime.InteropServices.Marshal.ReleaseComObject(loXlWorkSheet)
loXlApp = Nothing
loXlWorkBook = Nothing
loXlWorkSheet = Nothing
End If
End Try