Export Form

Imports WORD = Microsoft.Office.Interop.Word

Imports Excel = Microsoft.Office.Interop.Excel

Imports System.Data

Imports System.Data.SqlClient

Public Class Form_Lihat

#Region "deklarasi"

Dim FontBold As Boolean

Dim FontSize As Integer

Dim FontName As String

Dim i, a1, a2, a3, a4, a5 As Integer

Dim NamaLeader(100000) As String

Dim posisi(10000) As String

Dim nama(10000) As String

Dim nik(10000) As String

Dim DataNama0(100000) As String

Dim DataNik0(100000) As String

Dim DataPosisi0(100000) As String

Dim DataUpNik0(100000) As String

Dim DataNama1(100000) As String

Dim DataNik1(100000) As String

Dim DataPosisi1(100000) As String

Dim DataUpNik1(100000) As String

Dim DataNama2(100000) As String

Dim DataNik2(100000) As String

Dim DataPosisi2(100000) As String

Dim DataUpNik2(100000) As String

Dim DataNama3(100000) As String

Dim DataNik3(100000) As String

Dim DataPosisi3(100000) As String

Dim DataUpNik3(100000) As String

Dim DataNama4(100000) As String

Dim DataNik4(100000) As String

Dim DataPosisi4(100000) As String

Dim DataUpNik4(100000) As String

Dim DataNama5(100000) As String

Dim DataNik5(100000) As String

Dim DataPosisi5(100000) As String

Dim DataUpNik5(100000) As String

Dim DataIdMaster(100000) As String

Dim DataLevel(100000) As Integer

Dim JumlahData As Integer

Dim JumlahData0 As Integer

Dim JumlahData1 As Integer

Dim JumlahData2 As Integer

Dim JumlahData3 As Integer

Dim JumlahData4 As Integer

Dim JumlahData5 As Integer

#End Region

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

ViewComboDepartment()

ViewComboSiteCard()

ViewComboPosisi()

ViewComboLokasi()

ViewComboGrade()

' ViewComboLeader()

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Rtb1.Clear()

Dim nama, nik, posisi As String

nama = Trim(Mid(cmbLeader.Text, 1, 50))

nik = Trim(Mid(cmbLeader.Text, 51, 20))

posisi = Trim(Mid(cmbLeader.Text, 71, 20))

ViewTextBox("NAMA : ", nama, "", "")

ViewTextBox("NIK : ", nik, "", "")

ViewTextBox("POSISI : ", posisi, "", "")

' ViewTextBox("-------------------------------------------------------------------", "", "", "")

ViewTextBox("", "", "", "")

ViewTextBox("NO", "NAMA", "NIK", "POSISI")

' ViewTextBox("-------------------------------------------------------------------", "", "", "")

JumlahData = 0

ViewBawahan0(nik)

' ViewTextBox("-------------------------------------------------------------------", "", "", "")

'JumlahData = JumlahData + 1

JumlahPerPosisi()

End Sub

Function space(ByVal x As String, ByVal n As Integer) As String

While Len(x) <>

x = x + " "

End While

Return x

End Function

Sub ViewComboDepartment()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT department FROM TB_Karyawan GROUP BY department"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("department").ToString <> ""

With objDataTable.Rows(i)

' cmbDepartment.Items.Add(.Item("department").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboSiteCard()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT siteCard FROM TB_Karyawan GROUP BY siteCard"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("siteCard").ToString <> ""

With objDataTable.Rows(i)

cmbSite.Items.Add(.Item("siteCard").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboPosisi()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT nama FROM TB_Position ORDER BY kode"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

cmbPosisi.Items.Add(.Item("nama").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboGrade()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT grade FROM TB_Karyawan GROUP BY grade ORDER BY grade"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("grade").ToString <> ""

With objDataTable.Rows(i)

cmbGrade.Items.Add(.Item("grade").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboLokasi()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT lokasi FROM TB_Karyawan GROUP BY lokasi ORDER BY lokasi"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("lokasi").ToString <> ""

With objDataTable.Rows(i)

cmbLokasi.Items.Add(.Item("lokasi").ToString)

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewComboLeader()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Try

strSQL = "SELECT nama, nik, posisi FROM TB_Karyawan "

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

Dim i As Integer

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

cmbLeader.Items.Add(space((.Item("nama").ToString), 50) + space((.Item("nik").ToString), 20) + (.Item("posisi").ToString))

i = i + 1

End With

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub viewJumlahData(ByVal x As String)

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT * FROM TB_Karyawan where upNik ='" + x + "'"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

i = i + 1

End With

JumlahData = i - 1

End While

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

JumlahData = i - 1

End Sub

Sub ViewBawahan0(ByVal x As String)

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT * FROM TB_Karyawan where upNik ='" + x + "'"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNama0(i + 1) = space((.Item("nama").ToString), 30)

DataNik0(i + 1) = space((.Item("nik").ToString), 10)

DataPosisi0(i + 1) = space((.Item("posisi").ToString), 20)

' DataUpNik1(i + 1) = space((.Item("siteCard").ToString), 10)

JumlahData = JumlahData + 1

posisi(JumlahData) = space((.Item("posisi").ToString), 20)

nama(JumlahData) = space((.Item("nama").ToString), 30)

nik(JumlahData) = space((.Item("nik").ToString), 10)

ViewTextBox(CStr(JumlahData), DataNama0(i + 1), DataNik0(i + 1), DataPosisi0(i + 1))

ViewBawahan1(DataNik0(i + 1))

i = i + 1

End With

End While

JumlahData0 = i - 1

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewBawahan1(ByVal x As String)

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT * FROM TB_Karyawan where upNik ='" + x + "'"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNama1(i + 1) = space((.Item("nama").ToString), 30)

DataNik1(i + 1) = space((.Item("nik").ToString), 10)

DataPosisi1(i + 1) = space((.Item("posisi").ToString), 20)

' DataUpNik1(i + 1) = space((.Item("siteCard").ToString), 10)

JumlahData = JumlahData + 1

posisi(JumlahData) = space((.Item("posisi").ToString), 20)

nama(JumlahData) = space((.Item("nama").ToString), 30)

nik(JumlahData) = space((.Item("nik").ToString), 10)

ViewTextBox(CStr(JumlahData), DataNama1(i + 1), DataNik1(i + 1), DataPosisi1(i + 1))

ViewBawahan2(DataNik1(i + 1))

i = i + 1

End With

End While

JumlahData1 = i - 1

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewBawahan2(ByVal x As String)

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT * FROM TB_Karyawan where upNik ='" + x + "'"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNama2(i + 1) = space((.Item("nama").ToString), 30)

DataNik2(i + 1) = space((.Item("nik").ToString), 10)

DataPosisi2(i + 1) = space((.Item("posisi").ToString), 20)

'DataUpNik2(i + 1) = space((.Item("siteCard").ToString), 10)

JumlahData = JumlahData + 1

nama(JumlahData) = space((.Item("nama").ToString), 30)

nik(JumlahData) = space((.Item("nik").ToString), 10)

posisi(JumlahData) = space((.Item("posisi").ToString), 20)

ViewTextBox(CStr(JumlahData), DataNama2(i + 1), DataNik2(i + 1), DataPosisi2(i + 1))

ViewBawahan3(DataNik2(i + 1))

i = i + 1

End With

End While

JumlahData2 = i - 1

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewBawahan3(ByVal x As String)

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT * FROM TB_Karyawan where upNik ='" + x + "'"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNama3(i + 1) = space((.Item("nama").ToString), 30)

DataNik3(i + 1) = space((.Item("nik").ToString), 10)

DataPosisi3(i + 1) = space((.Item("posisi").ToString), 20)

JumlahData = JumlahData + 1

nama(JumlahData) = space((.Item("nama").ToString), 30)

nik(JumlahData) = space((.Item("nik").ToString), 10)

posisi(JumlahData) = space((.Item("posisi").ToString), 20)

ViewTextBox(CStr(JumlahData), DataNama3(i + 1), DataNik3(i + 1), DataPosisi3(i + 1))

ViewBawahan4(DataNik3(i + 1))

i = i + 1

End With

End While

JumlahData3 = i - 1

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewBawahan4(ByVal x As String)

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT * FROM TB_Karyawan where upNik ='" + x + "'"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNama4(i + 1) = space((.Item("nama").ToString), 30)

DataNik4(i + 1) = space((.Item("nik").ToString), 10)

DataPosisi4(i + 1) = space((.Item("posisi").ToString), 20)

JumlahData = JumlahData + 1

nama(JumlahData) = space((.Item("nama").ToString), 30)

nik(JumlahData) = space((.Item("nik").ToString), 10)

posisi(JumlahData) = space((.Item("posisi").ToString), 20)

ViewTextBox(CStr(JumlahData), DataNama4(i + 1), DataNik4(i + 1), DataPosisi4(i + 1))

ViewBawahan5(DataNik4(i + 1))

i = i + 1

End With

End While

JumlahData4 = i - 1

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewBawahan5(ByVal x As String)

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT * FROM TB_Karyawan where upNik ='" + x + "'"

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

DataNama5(i + 1) = space((.Item("nama").ToString), 30)

DataNik5(i + 1) = space((.Item("nik").ToString), 10)

DataPosisi5(i + 1) = space((.Item("posisi").ToString), 20)

JumlahData = JumlahData + 1

nama(JumlahData) = space((.Item("nama").ToString), 30)

nik(JumlahData) = space((.Item("nik").ToString), 10)

posisi(JumlahData) = space((.Item("posisi").ToString), 20)

ViewTextBox(CStr(JumlahData), DataNama5(i + 1), DataNik5(i + 1), DataPosisi5(i + 1))

i = i + 1

End With

End While

JumlahData5 = i - 1

Catch When Err.Number <> 0

' MsgBox("Tidak dapat membuat hubungan ke database2" & vbCrLf & Err.Description)

MyConnection.close()

End Try

End Sub

Sub ViewFilter()

Dim MyConnection As New DataAcces.DatabaseConnection

Dim objCommand As SqlCommand

Dim strSQL As String

Dim objDataSet As New DataSet

Dim objDataTable As New DataTable

Dim i As Integer

Try

strSQL = "SELECT * FROM TB_Karyawan WHERE grade like '%" + cmbGrade.Text + "%' AND posisi like '%" + cmbPosisi.Text + "%' AND lokasi like '%" + cmbLokasi.Text + "%' AND siteCard like '%" + cmbSite.Text + "%' ORDER by nama"

' rtb1.Text = (strSQL)

objCommand = New SqlCommand

objCommand.Connection = MyConnection.open

objCommand.CommandType = CommandType.Text

objCommand.CommandText = strSQL

Dim objDataAdapter1 As SqlClient.SqlDataAdapter

objDataAdapter1 = New SqlDataAdapter(objCommand)

objDataAdapter1.Fill(objDataSet, "MDT_Data")

MyConnection.close()

objDataTable = objDataSet.Tables("MDT_Data")

i = 0

cmbLeader.Items.Clear()

While objDataTable.Rows(i).Item("nama").ToString <> ""

With objDataTable.Rows(i)

cmbLeader.Items.Add(space((.Item("nama").ToString), 50) + space((.Item("nik").ToString), 20) + (.Item("posisi").ToString))

i = i + 1

End With

End While

Catch When Err.Number <> 0

MyConnection.close()

End Try

JumlahData = i

End Sub

Function garis()

Return "-------------------------------------------------------------------------------------"

End Function

Sub ViewTextBox(ByVal no As String, ByVal nama As String, ByVal nik As String, ByVal posisi As String)

Rtb1.Text = Rtb1.Text + space(no, 10)

Rtb1.Text = Rtb1.Text + space(nama, 30)

Rtb1.Text = Rtb1.Text + space(nik, 10)

Rtb1.Text = Rtb1.Text + space(posisi, 20) + Chr(13)

' Rtb1.Text = Rtb1.Text + Chr(13) + Chr(13) + garis() + Chr(13) + "Main Power Summary : " + CStr(JumlahData - 1)

End Sub

Sub font_style(ByVal level As Integer)

Select Case level

Case 10

FontBold = False

FontSize = 8

Case 9

FontBold = True

FontSize = 8

Case 8

FontBold = False

FontSize = 9

Case 7

FontBold = True

FontSize = 9

Case 6

FontBold = False

FontSize = 10

Case 5

FontBold = True

FontSize = 10

Case 4

FontBold = False

FontSize = 11

Case 3

FontBold = True

FontSize = 11

Case 2

FontBold = False

FontSize = 12

Case 1

FontBold = True

FontSize = 12

End Select

End Sub

Function SpaceLine(ByVal level As Integer) As String

Dim t As String

t = ""

Select Case level

Case 10

t = " "

Case 9

t = " "

Case 8

t = " "

Case 7

t = " "

Case 6

t = " "

Case 5

t = " "

Case 4

t = " "

Case 3

t = " "

Case 2

t = " "

Case 1

t = ""

End Select

Return t

End Function

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

ViewFilter()

' WordDocument()

End Sub

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

Rtb1.Clear()

Dim nama, nik, posisi As String

nama = Trim(Mid(cmbLeader.Text, 1, 50))

nik = Trim(Mid(cmbLeader.Text, 51, 20))

posisi = Trim(Mid(cmbLeader.Text, 71, 20))

ViewTextBox("NAMA : ", nama, "", "")

ViewTextBox("NIK : ", nik, "", "")

ViewTextBox("POSISI : ", posisi, "", "")

' ViewTextBox("-------------------------------------------------------------------", "", "", "")

ViewTextBox("", "", "", "")

ViewTextBox("NO", "NAMA", "NIK", "POSISI")

' ViewTextBox("-------------------------------------------------------------------", "", "", "")

JumlahData = 0

ViewBawahan0(nik)

' ViewTextBox("-------------------------------------------------------------------", "", "", "")

'JumlahData = JumlahData + 1

JumlahPerPosisi()

ExcelExport()

End Sub

Sub JumlahPerPosisi()

a1 = 0

a2 = 0

a3 = 0

a4 = 0

a5 = 0

For i = 1 To JumlahData

If Trim(posisi(i)) = "SOCM / OCM / AVP" Then

a1 = a1 + 1

End If

If Trim(posisi(i)) = "FM" Then

a2 = a2 + 1

End If

If Trim(posisi(i)) = "SERVICE MANAGER" Then

a3 = a3 + 1

End If

If Trim(posisi(i)) = "SPV" Then

a4 = a4 + 1

End If

If Trim(posisi(i)) = "TEAM LEADER" Then

a5 = a5 + 1

End If

Next

Rtb1.Text = Rtb1.Text + Chr(13)

If a1 <> 0 Then

Rtb1.Text = Rtb1.Text + " JUMLAH SOCM / OCM / AVP = " + space(CStr(a1), 20) + Chr(13)

End If

If a2 <> 0 Then

Rtb1.Text = Rtb1.Text + " JUMLAH FM = " + space(CStr(a2), 20) + Chr(13)

End If

If a3 <> 0 Then

Rtb1.Text = Rtb1.Text + " JUMLAH SERVICE MANAGER = " + space(CStr(a3), 20) + Chr(13)

End If

If a4 <> 0 Then

Rtb1.Text = Rtb1.Text + " JUMLAH SPV = " + space(CStr(a4), 20) + Chr(13)

End If

If a5 <> 0 Then

Rtb1.Text = Rtb1.Text + " JUMLAH TEAM LEADER = " + space(CStr(a5), 20) + Chr(13)

End If

Rtb1.Text = Rtb1.Text + " JUMLAH TOTAL = " + space(CStr(JumlahData), 20) + Chr(13)

End Sub

Sub ExcelExport()

Dim objExcel As Excel.Application

objExcel = New Excel.Application

Dim a As Integer

Dim objSheet As New Excel.Worksheet

' Dim objRange As Excel.Range

' Dim intRow, intCol As Integer

objExcel.Visible = True

'Add a worksheet and then add some content to it.

objSheet = objExcel.Workbooks.Add.Worksheets.Add

Dim i, n As Integer

n = 6

With objSheet

.Range("A1:I" + CStr(n)).Select()

With objExcel.Selection.Font

.Name = "arial"

.FontStyle = "Bold"

.Size = 10

End With

Dim namaX, nikX, posisiX As String

namaX = Trim(Mid(cmbLeader.Text, 1, 50))

nikX = Trim(Mid(cmbLeader.Text, 51, 20))

posisiX = Trim(Mid(cmbLeader.Text, 71, 20))

.Cells(1, 1).Value = "NAMA"

.Cells(2, 1).Value = "NIK"

.Cells(3, 1).Value = "POSISI"

.Cells(1, 2).Value = namaX

.Cells(2, 2).Value = nikX

.Cells(3, 2).Value = posisiX

.Cells(5, 1).Value = "NO"

.Cells(5, 2).Value = "NAMA"

.Cells(5, 3).Value = "NIK"

.Cells(5, 4).Value = "POSISI"

For i = 1 + n To JumlahData + n

.Cells(i, 1).Value = CStr(i - n)

.Cells(i, 2).Value = nama(i - n)

.Cells(i, 3).Value = nik(i - n)

.Cells(i, 4).Value = posisi(i - n)

Next

a = n + 2

If a1 <> 0 Then

.Cells(JumlahData + a, 2).Value = "JUMLAH SOCM / OCM / AVP"

.Cells(JumlahData + a, 3).Value = +space(CStr(a1), 20)

a = a + 1

End If

If a2 <> 0 Then

.Cells(JumlahData + a, 2).Value = "JUMLAH FM"

.Cells(JumlahData + a, 3).Value = +space(CStr(a2), 20)

a = a + 1

End If

If a3 <> 0 Then

.Cells(JumlahData + a, 2).Value = "JUMLAH SERVICE MANAGER"

.Cells(JumlahData + a, 3).Value = +space(CStr(a3), 20)

a = a + 1

End If

If a4 <> 0 Then

.Cells(JumlahData + a, 2).Value = "JUMLAH SPV"

.Cells(JumlahData + a, 3).Value = +space(CStr(a4), 20)

a = a + 1

End If

If a5 <> 0 Then

.Cells(JumlahData + a, 2).Value = "JUMLAH TEAM LEADER"

.Cells(JumlahData + a, 3).Value = +space(CStr(a5), 20)

a = a + 1

End If

.Cells(JumlahData + a, 2).Value = "JUMLAH TOTAL"

.Cells(JumlahData + a, 3).Value = +space(CStr(JumlahData), 20)

.Range("A" + CStr(n) + ":I" + CStr(JumlahData + n)).Select()

With objExcel.Selection.Font

.Name = "arial"

.FontStyle = "general"

.Size = 10

End With

.Range("A" + CStr(JumlahData + 1 + n) + ":I" + CStr(JumlahData + a + 1)).Select()

With objExcel.Selection.Font

.Name = "arial"

.FontStyle = "bold"

.Size = 10

End With

End With

'Set the alignment.

objSheet.Range("A1:I" + CStr(JumlahData + a + 1)).Select()

objExcel.Selection.Columns.AutoFit()

objSheet.Range("c1:D" + CStr(JumlahData + a + 1)).Select()

With objExcel.Selection

.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

End With

objExcel = Nothing

End Sub

Private Sub cmbSite_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbSite.SelectedIndexChanged

ViewFilter()

End Sub

Private Sub cmbDepartment_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)

ViewFilter()

End Sub

Private Sub cmbGrade_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbGrade.SelectedIndexChanged

ViewFilter()

End Sub

Private Sub cmbPosisi_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbPosisi.SelectedIndexChanged

ViewFilter()

End Sub

Private Sub cmbLokasi_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbLokasi.SelectedIndexChanged

ViewFilter()

End Sub

End Class