General declarasi
Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Deklarasi
#Region "declar"
Dim mykoneksi As New accesdata.dataconnection
Dim myinsert As New accesdata.dataacces
Dim mytampil As New accesdata.tampil
Dim rc, x, I, bln, rowexce As Integer
Dim r As DataRow
Dim objdataset As New DataSet
Dim objdatatable As New DataTable
Dim objdatatable1 As New DataTable
Dim sql As String
#End Region
Konversi bulan
#Region "conversi"
Sub conversi()
If cbbulan.Text = "January" Then
bln = 1
ElseIf cbbulan.Text = "Pebruary" Then
bln = 2
ElseIf cbbulan.Text = "Maret" Then
bln = 3
ElseIf cbbulan.Text = "April" Then
bln = 4
ElseIf cbbulan.Text = "Mei" Then
bln = 5
ElseIf cbbulan.Text = "Juni" Then
bln = 6
ElseIf cbbulan.Text = "Juli" Then
bln = 7
ElseIf cbbulan.Text = "Agustus" Then
bln = 8
ElseIf cbbulan.Text = "September" Then
bln = 9
ElseIf cbbulan.Text = "Oktober" Then
bln = 10
ElseIf cbbulan.Text = "Nopember" Then
bln = 11
ElseIf cbbulan.Text = "Desember" Then
bln = 12
End If
End Sub
#End Region
Tampil
#Region "tampil"
Sub tampil()
objdatatable.Clear()
Try
objcommand = New OleDbCommand("select * from [q_export] where bln = " & bln & " and th='" & ndtahun.Text & "'")
objcommand.Connection = mykoneksi.open
objdataadapter = New OleDbDataAdapter(objcommand)
objdataadapter.Fill(objdataset, "scrapt")
objdatatable = objdataset.Tables("scrapt")
objdatareaer = objcommand.ExecuteReader
objdatareaer.Read()
Label5.Text = objdatatable.Rows.Count
dgexport.DataSource = objdatatable
mykoneksi.close()
Catch ex As Exception
MsgBox("konek ke database gagal" & ex.Message)
mykoneksi.close()
End Try
End Sub
#End Region
#Region "konek excel"
Sub konekexcel()
Dim namafile As String = Mid(Trim(txtnama.Text), InStrRev(Trim(txtnama.Text), "\", +1), Len(Trim(txtnama.Text)))
Dim objcon As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & txtnama.Text & "'")
Dim objcommand1 As New OleDbCommand("select * from [namafile]")
objcommand1.Connection = objcon
objcon.Open()
objdataadapter1 = New OleDbDataAdapter(objcommand1)
objdataadapter1.Fill(objdataset1, "excell")
objdatatable1 = objdataset1.Tables("excell")
End Sub
#End Region
Export To Text / CSV
#Region "expor"
Sub exksportxt()
Try
conversi()
objdatatable.Clear()
objcommand = New OleDbCommand("select * from [q_export] where bln = " & bln & " and th='" & ndtahun.Text & "'")
objcommand.Connection = mykoneksi.open
objdataadapter = New OleDbDataAdapter(objcommand)
objdataadapter.Fill(objdataset, "scrapt")
objdatatable = objdataset.Tables("scrapt")
objdatareaer = objcommand.ExecuteReader
objdatareaer.Read()
Dim fs As FileStream = New FileStream(Trim(txtnama.Text), FileMode.Create, FileAccess.Write)
Dim sw As StreamWriter = New StreamWriter(fs)
'Dim tgl As Date = CDate(objdatareaer.Item(4))
Dim filn0 As String = objdatareaer.GetName(0)
Dim filn1 As String = objdatareaer.GetName(1)
Dim filn2 As String = objdatareaer.GetName(2)
Dim filn3 As String = objdatareaer.GetName(3)
Dim filn4 As String = objdatareaer.GetName(4)
Dim filn5 As String = objdatareaer.GetName(5)
Dim filn6 As String = objdatareaer.GetName(6)
Dim filn7 As String = objdatareaer.GetName(7)
Dim filn8 As String = objdatareaer.GetName(8)
Dim filn9 As String = objdatareaer.GetName(9)
Dim filn10 As String = objdatareaer.GetName(10)
Dim filn11 As String = objdatareaer.GetName(11)
Dim filn12 As String = objdatareaer.GetName(12)
Dim filn13 As String = objdatareaer.GetName(13)
Dim filn14 As String = objdatareaer.GetName(14)
sw.WriteLine(filn0 & "," & filn1 & "," & filn2 & "," & filn3 & "," & filn4 & "," & filn5 & "," & filn6 & "," & filn7 & "," & filn8 & "," & filn9 & "," & filn10 & "," & filn11 & "," & filn12 & "," & filn13 & "," & filn14)
For I = 0 To objdatatable.Rows.Count - 1
Dim tgl As Date = CDate(objdatareaer.Item(4))
Dim fil0 As String = objdatareaer.Item(0)
Dim fil1 As String = objdatareaer.Item(1)
Dim fil2 As String = objdatareaer.Item(2)
Dim fil3 As String = objdatareaer.Item(3)
Dim fil4 As String = tgl.ToShortDateString
Dim fil5 As String = objdatareaer.Item(5)
Dim fil6 As String = objdatareaer.Item(6)
Dim fil7 As String = objdatareaer.Item(7)
Dim fil8 As String = objdatareaer.Item(8)
Dim fil9 As String = objdatareaer.Item(9)
Dim fil10 As String = objdatareaer.Item(10)
Dim fil11 As String = objdatareaer.Item(11)
Dim fil12 As String = objdatareaer.Item(12)
Dim fil13 As String = objdatareaer.Item(13)
Dim fil14 As String = objdatareaer.Item(14)
sw.WriteLine(fil0 & "," & fil1 & "," & fil2 & "," & fil3 & "," & fil4 & "," & fil5 & "," & fil6 & "," & fil7 & "," & fil8 & "," & fil9 & "," & fil10 & "," & fil11 & "," & fil12 & "," & fil13 & "," & fil14)
jum1.Text = Convert.ToString(I)
jum2.Text = Convert.ToString(objdatatable.Rows.Count - 1)
Next I
MsgBox("Export data scrapt sukses", MsgBoxStyle.Information, "Informasi")
mykoneksi.close()
Catch ex As Exception
MsgBox("Export file gagal,ulangi lagi" & vbCrLf & ex.Message)
mykoneksi.close()
End Try
End Sub
#End Region
Exksport Ke Excel
#Region "exkspor excel"
Sub exsportexcel1()
conversi()
Dim objcom2 As New OleDbCommand("select * from [q_export] where bln = " & bln & " and th='" & ndtahun.Text & "'")
objcom2.Connection = mykoneksi.open
objdatareaer = objcom2.ExecuteReader
objdatareaer.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(objdatareaer.GetName(0).ToString)
xlSheet.Cells(1, 2) = CStr(objdatareaer.GetName(1).ToString)
xlSheet.Cells(1, 3) = CStr(objdatareaer.GetName(2).ToString)
xlSheet.Cells(1, 4) = CStr(objdatareaer.GetName(3).ToString)
xlSheet.Cells(1, 5) = CStr(objdatareaer.GetName(4).ToString)
xlSheet.Cells(1, 6) = CStr(objdatareaer.GetName(5).ToString)
xlSheet.Cells(1, 7) = CStr(objdatareaer.GetName(6).ToString)
xlSheet.Cells(1, 8) = CStr(objdatareaer.GetName(7).ToString)
xlSheet.Cells(1, 9) = CStr(objdatareaer.GetName(8).ToString)
xlSheet.Cells(1, 10) = CStr(objdatareaer.GetName(9).ToString)
xlSheet.Cells(1, 11) = CStr(objdatareaer.GetName(10).ToString)
xlSheet.Cells(1, 12) = CStr(objdatareaer.GetName(11).ToString)
xlSheet.Cells(1, 13) = CStr(objdatareaer.GetName(12).ToString)
xlSheet.Cells(1, 14) = CStr(objdatareaer.GetName(13).ToString)
xlSheet.Cells(1, 15) = CStr(objdatareaer.GetName(14).ToString)
mykoneksi.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(4))
xlSheet.Cells(x + 2, 6) = CStr(objdatatable.Rows(x).Item(5))
xlSheet.Cells(x + 2, 7) = CStr(objdatatable.Rows(x).Item(6))
xlSheet.Cells(x + 2, 8) = CStr(objdatatable.Rows(x).Item(7))
xlSheet.Cells(x + 2, 9) = CStr(objdatatable.Rows(x).Item(8))
xlSheet.Cells(x + 2, 10) = CStr(objdatatable.Rows(x).Item(9))
xlSheet.Cells(x + 2, 11) = CStr(objdatatable.Rows(x).Item(10))
xlSheet.Cells(x + 2, 12) = CStr(objdatatable.Rows(x).Item(11))
xlSheet.Cells(x + 2, 13) = CStr(objdatatable.Rows(x).Item(12))
xlSheet.Cells(x + 2, 14) = CStr(objdatatable.Rows(x).Item(13))
xlSheet.Cells(x + 2, 15) = CStr(objdatatable.Rows(x).Item(14))
Next x
xlSheet.Application.Visible = True
xlSheet.SaveAs(nama)
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
#End Region
Button Eksport
#Region "button export"
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If cbext.SelectedIndex = 1 Then
exsportexcel1()
ElseIf cbext.SelectedIndex = 0 Then
exksportxt()
End If
End Sub
#End Region
Button Go
#Region "button go"
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btngo.Click
conversi()
tampil()
jum2.Text = Convert.ToString(objdatatable.Rows.Count - 1)
End Sub
#End Region
Load Program
Private Sub exportscrapt_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
cbbulan.SelectedIndex = 0
End Sub
0 komentar:
Posting Komentar