Import dan mengolah text document bag.4

Sebelum codeng tambahkan reference excel 11. dan office 11. object library ke project

Imports System.Data
Imports System.Data.OleDb
Imports System.IO

deklaresi-----------------------

Inherits System.Windows.Forms.Form
Dim mykonek As New acces.datacon

Menampilkan yang akan di export-------------------

#Region "tampil"
Sub tampil()
Try
objdatatable.Clear()
Dim strsql As String
strsql = "Select * from [export] order by [urut]"
objcom = New OleDbCommand(strsql)
objcom.Connection = mykonek.open
objdataadapter = New OleDbDataAdapter(objcom)
objdataadapter.Fill(objdatset, "export")
objdatatable = objdatset.Tables("export")
DataGrid1.DataSource = objdatatable
mykonek.close()
Catch ex As Exception
MsgBox("kenapa ya tidak Konek" & ex.Message & vbCrLf)
End Try
End Sub
#End Region

Export ke excel------------------------------------

#Region "export"
Sub export()
Dim objcom2 As New OleDbCommand("select * from [export] order by [urut]")
objcom2.Connection = mykonek.open
objdatreader = objcom2.ExecuteReader
objdatreader.Read()
sfd.ShowDialog()
Dim nama As String = sfd.FileName
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim tNode As TreeNode
Dim x, Add, z As Integer
Try
xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlSheet.Cells(1, 1) = CStr(objdatreader.GetName(0).ToString)
xlSheet.Cells(1, 2) = CStr(objdatreader.GetName(1).ToString)
xlSheet.Cells(1, 3) = CStr(objdatreader.GetName(2).ToString)
xlSheet.Cells(1, 4) = CStr(objdatreader.GetName(3).ToString)
xlSheet.Cells(1, 5) = CStr(objdatreader.GetName(5).ToString)
xlSheet.Cells(1, 6) = CStr(objdatreader.GetName(6).ToString)
xlSheet.Cells(1, 7) = CStr(objdatreader.GetName(7).ToString)
xlSheet.Cells(1, 8) = CStr(objdatreader.GetName(8).ToString)
xlSheet.Cells(1, 9) = CStr(objdatreader.GetName(10).ToString)
xlSheet.Cells(1, 10) = CStr(objdatreader.GetName(11).ToString)
xlSheet.Cells(1, 11) = CStr(objdatreader.GetName(12).ToString)
xlSheet.Cells(1, 12) = CStr(objdatreader.GetName(13).ToString)
xlSheet.Cells(1, 13) = CStr(objdatreader.GetName(14).ToString)
xlSheet.Cells(1, 14) = CStr(objdatreader.GetName(15).ToString)
'xlSheet.Cells(1, 15) = CStr(objdatreader.GetName(15).ToString)
'xlSheet.Cells(1, 16) = CStr(objdatreader.GetName(15).ToString)
mykonek.close()
'conversi()
objdatatable.Clear()
tampil()
For x = 0 To objdatatable.Rows.Count - 1
xlSheet.Cells(x + 2, 1) = CStr(objdatatable.Rows(x).Item(0))
xlSheet.Cells(x + 2, 2) = CStr(objdatatable.Rows(x).Item(1))
xlSheet.Cells(x + 2, 3) = CStr(objdatatable.Rows(x).Item(2))
xlSheet.Cells(x + 2, 4) = CStr(objdatatable.Rows(x).Item(3))
xlSheet.Cells(x + 2, 5) = CStr(objdatatable.Rows(x).Item(5))
xlSheet.Cells(x + 2, 6) = CStr(objdatatable.Rows(x).Item(6))
xlSheet.Cells(x + 2, 7) = CStr(objdatatable.Rows(x).Item(7))
xlSheet.Cells(x + 2, 8) = CStr(objdatatable.Rows(x).Item(8))
xlSheet.Cells(x + 2, 9) = CStr(objdatatable.Rows(x).Item(10))
xlSheet.Cells(x + 2, 10) = CStr(objdatatable.Rows(x).Item(11))
xlSheet.Cells(x + 2, 11) = CStr(objdatatable.Rows(x).Item(12))
xlSheet.Cells(x + 2, 12) = CStr(objdatatable.Rows(x).Item(13))
xlSheet.Cells(x + 2, 13) = CStr(objdatatable.Rows(x).Item(14))
xlSheet.Cells(x + 2, 14) = CStr(objdatatable.Rows(x).Item(15))
'xlSheet.Cells(x + 2, 15) = CStr(objdatatable.Rows(x).Item(14))
'xlSheet.Cells(x + 2, 16) = CStr(objdatatable.Rows(x).Item(15))
Next x
xlSheet.Application.Visible = True
xlSheet.SaveAs(nama)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region

Button export diklik--------------------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
export()
End Sub

Button view diklik---------------------------------

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
tampil()
End Sub

Button close-----------------------------------

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.Close()
End Sub