Form_Upload

Imports EXCEL = Microsoft.Office.Interop.Excel

Imports System.Data

Imports System.Data.SqlClient

Public
Class Form_Upload

Inherits System.Windows.Forms.Form

#Region
"deklarasi"

Dim vekNik(100000) As
String

Dim vekNama(100000) As
String

Dim vekGrade(100000) As
String

Dim vekSiteCard(100000) As
String

Dim vekPosisi(100000) As
String

Dim vekLokasi(100000) As
String

Dim vekDepartment(100000) As
String

Dim vekUpNik(100000) As
String

Dim vekGagal(100000) As
String

Dim JumlahGagal As
Integer

Dim i1 As
Integer = 0

Dim i2 As
Integer = 0

Dim i3 As
Integer = 0

Dim j As
Integer = 0

Dim k As
Integer = 0

Dim l As
Integer = 0

Dim MyConnection As DataAcces.DatabaseConnection

Dim JumlahDataExcel As
Integer

#End
Region

Private
Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click

OpenFileDialog1.ShowDialog()

If OpenFileDialog1.FileName <> ""
Then

txbFile.Text = OpenFileDialog1.FileName

End
If

End
Sub

Sub ViewJumlahDataExcell()

Dim connectionStringTemplate As
String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"""

If txbFile.Text = ""
Then

MsgBox("File Name can not empty !", MsgBoxStyle.Information, "Error Message")

Exit
Sub

End
If

Dim connectionString As
String = String.Format(connectionStringTemplate, txbFile.Text)

While TextBox1.Text = ""

MsgBox("Sheet Name can not empty !", MsgBoxStyle.Information, "Error Message")

Exit
Sub

End
While

Dim sqlSelect As
String = "SELECT count(*) as Jumlah FROM [" & TextBox1.Text & "$];"

' Load the Excel worksheet into a DataTable

Dim workbook As DataSet = New DataSet()

Dim excelAdapter As System.Data.Common.DataAdapter = New System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)

Try

excelAdapter.Fill(workbook)

Dim worksheet As DataTable = workbook.Tables(0)

JumlahDataExcel = CInt(worksheet.Rows(0).Item("Jumlah"))

Catch

MsgBox("Error Load File!", MsgBoxStyle.Critical, "Error Message")

End
Try

End
Sub

Sub ViewDataExcel(ByVal n As
Integer)

Dim objExcel As EXCEL.Application

objExcel = New EXCEL.Application

Dim objSheet As
New EXCEL.Worksheet

Dim objData As EXCEL.Range

Dim IdxRow As
Integer

objExcel.Visible = True

Try

objSheet = objExcel.Workbooks.Open(txbFile.Text).Worksheets.Item(1)

objExcel.Range("A2:h" & n).Select()

objData = objExcel.Selection

For IdxRow = 1 To n

vekNik(IdxRow) = objData(IdxRow, 1).Value

vekNama(IdxRow) = objData(IdxRow, 2).Value

vekGrade(IdxRow) = objData(IdxRow, 3).Value

vekSiteCard(IdxRow) = objData(IdxRow, 4).Value

vekPosisi(IdxRow) = objData(IdxRow, 5).Value

vekLokasi(IdxRow) = objData(IdxRow, 6).Value

vekDepartment(IdxRow) = objData(IdxRow, 7).Value

vekUpNik(IdxRow) = objData(IdxRow, 8).Value

Next

objExcel.Workbooks.Close()

objExcel.Quit()

objExcel = Nothing

Catch ex As Exception

MsgBox("Error Read File!" & vbCrLf & Err.Description, MsgBoxStyle.Critical, "Error Message")

End
Try

End
Sub

Sub ViewInsertTB_Karyawan(ByVal n As
Integer)

pb1.Minimum = 1

pb1.Maximum = n

Dim MyConnection_Karyawan As
New DataAcces.Access_TB_Karyawan

Dim i, j, k As
Integer

For i = 1 To n

pb1.Value = i

Try

' cari(Mid(ListBox1.Items(i), 51, 7))

''''''''''''''''''''''''''''''''''''''''nik'''''''''''''''''''''''''''''''''''nama''''''''''''''''''''''''''''''''''grade'''''''''''''''''''''''''''''''''''siteCard'''''''''''''''''''''''''''''''posisi'''''''''''''''''''''''''''''''''lokasi''''''''''''''''''''''''''''''''idMaster'''''''''department'''''''''''''''''''''''''''''upNik''''''''''''''''''''''''''''''''

MyConnection_Karyawan.Karyawan_Insert(vekNik(i), vekNama(i), vekGrade(i), vekSiteCard(i), vekPosisi(i), vekLokasi(i), vekDepartment(i), vekUpNik(i), Format(Now, "dd/MM/yyyy, hh:mm:ss"))

vekGagal(i) = "sukses"

k = k + 1

Me.Text = "total record = " & CStr(k) & " ; " & CStr(k * 100 / JumlahDataExcel) & "%"

Catch
When Err.Number <> 0

MsgBox("Tidak dapat melakukan proses simpan" & vbCrLf & Err.Description)

j = j + 1

vekGagal(i) = "gagal"

End
Try

JumlahGagal = j

'OBJDataTable.Clear()

Next

MsgBox("Succes Upload = " + CStr(k))

MsgBox("Failed Upload = " + CStr(j))

End
Sub

Sub ViewInsertgagal(ByVal n As
Integer)

Dim MyConnection2 As
New DataAcces.DatabaseConnection

pb1.Minimum = 1

pb1.Maximum = JumlahGagal

Dim MyConnection_Karyawan As
New DataAcces.Access_TB_Karyawan

Dim i, j, k As
Integer

j = 0

k = 0

For i = 1 To n

If vekGagal(i) = "gagal"
Then

Try

' cari(Mid(ListBox1.Items(i), 51, 7))

''''''''''''''''''''''''''''''''''''''''nik'''''''''''''''''''''''''''''''''''nama''''''''''''''''''''''''''''''''''grade'''''''''''''''''''''''''''''''''''siteCard'''''''''''''''''''''''''''''''posisi'''''''''''''''''''''''''''''''''lokasi'''''''''''''''''''''''''''''''''''''''''department'''''''''''''''''''''''''''''upNik''''''''''''''''''''''''''''''''

MyConnection_Karyawan.Karyawan_Insert(vekNik(i), vekNama(i), vekGrade(i), vekSiteCard(i), vekPosisi(i), vekLokasi(i), vekDepartment(i), vekUpNik(i), Format(Now, "dd/MM/yyyy, hh:mm:ss"))

vekGagal(i) = "sukses"

k = k + 1

pb1.Value = k

Me.Text = "record = " & CStr(k) & " ; " & CStr(k * 100 / JumlahGagal) & "%"

Catch
When Err.Number <> 0

MsgBox("Tidak dapat melakukan proses simpan" & vbCrLf & Err.Description)

j = j + 1

vekGagal(i) = "gagal"

End
Try

JumlahGagal = j

'OBJDataTable.Clear()

End
If

Next

MsgBox("Succes Upload = " + CStr(k))

MsgBox("Failed Upload = " + CStr(j))

End
Sub

Private
Sub btnAddUser_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddUser.Click

ViewJumlahDataExcell()

ViewDataExcel(JumlahDataExcel)

'ViewInsertTB_Karyawan(JumlahDataExcel)

TimerGo()

End
Sub

Sub TimerGo()

Timer1.Start()

Timer2.Start()

End
Sub

Private
Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick

Dim MyConnection1 As
New DataAcces.DatabaseConnection

pb1.Minimum = 1

pb1.Maximum = JumlahDataExcel + 1

' idx = CInt(DateTime.Now.ToString("ss")) / CInt(DateTime.Now.ToString("ss"))

i2 = 1 + i2

If i3 Mod 500 = 0 Then

Timer1.Stop()

i3 = i3

MyConnection1.close()

End
If

If i3 = JumlahDataExcel Then

Timer1.Stop()

Timer2.Stop()

MsgBox("Succes Upload = " + CStr(k))

MsgBox("Failed Upload = " + CStr(j))

JumlahGagal = j

While JumlahGagal > 0

Dim mYes_No As
String = MsgBox("continue proccess ?", vbYesNo)

If mYes_No = vbYes Then

ViewInsertgagal(JumlahDataExcel)

Else

Exit
While

End
If

End
While

End
If

If i2 Mod 50 = 0 Then

Timer1.Stop()

i3 = i3

MyConnection1.close()

Else

i3 = i3 + 1

' pb1.Value = i3

Try

Dim MyConnection_Karyawan As
New DataAcces.Access_TB_Karyawan

' cari(Mid(ListBox1.Items(i), 51, 7))

''''''''''''''''''''''''''''''''''''''''nik'''''''''''''''''''''''''''''''''''nama''''''''''''''''''''''''''''''''''grade'''''''''''''''''''''''''''''''''''siteCard'''''''''''''''''''''''''''''''posisi'''''''''''''''''''''''''''''''''lokasi'''''''''''''''''''''''''''''''''''''''''department'''''''''''''''''''''''''''''upNik''''''''''''''''''''''''''''''''

MyConnection_Karyawan.Karyawan_Insert(vekNik(i3), vekNama(i3), vekGrade(i3), vekSiteCard(i3), vekPosisi(i3), vekLokasi(i3), vekDepartment(i3), vekUpNik(i3), Format(Now, "dd/MM/yyyy, hh:mm:ss"))

vekGagal(i3) = "sukses"

k = k + 1

pb1.Value = k

Me.Text = "record = " & CStr(k) & " ; " & CStr(k * 100 / JumlahDataExcel) & "%"

MyConnection1.close()

Catch
When Err.Number <> 0

MsgBox("Tidak dapat melakukan proses simpan" & vbCrLf & Err.Description)

j = j + 1

vekGagal(i3) = "gagal"

MyConnection1.close()

End
Try

'OBJDataTable.Clear()

End
If

End
Sub

Private
Sub Form_Upload_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
MyBase.Load

Timer1.Stop()

Timer2.Stop()

TextBox1.Text = "Sheet1"

End
Sub

Private
Sub Timer2_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer2.Tick

i1 = i1 + 1

l = l + 1

If i1 Mod 51 = 0 Then

Timer1.Start()

i2 = 0

i1 = 0

End
If

If l Mod 510 = 0 Then

Timer1.Start()

End
If

End
Sub

End
Class