|
Member 14954903 wrote: What is the issue here? Impossible to tell. It is possible that between the moment your code reads the clipboard and the next statement, the contents of Textbox1 changes. You need to add some debug code to try and find out exactly what data exists in each place.
|
|
|
|
|
|
Hi there, please help me how to speed up, i'm new in VB.NET programming, we have code to calculate salary and generate excel file with custom format (field, fonts, etc), the process is takes long time (up to 3 hours for 2000 record).
The code asf :
Private Sub tsbExcel_Click(sender As Object, e As EventArgs) Handles tsbExcel.Click
If MessageBox.Show("Continue Proses...", "Rekap Detail All", MessageBoxButtons.OKCancel, MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) = Windows.Forms.DialogResult.OK Then
GenPayrollAll()
End If
End Sub
Private Sub GenPayrollAll()
'Get variabel
Dim vabsDateBgn As Date = dtabsDateBgn.Value
Dim vabsDateEnd As Date = dtabsDateEnd.Value
'Validation
If chkPeriod(vabsDateBgn) = False Then
MessageBox.Show("No record available", "Attention", MessageBoxButtons.OK, MessageBoxIcon.Information)
Return
End If
DayCount()
SendKeys.Send("{TAB}")
lblProgress.Text = "Collecting data..."
Application.DoEvents() ' Keep App Responsive
'Get header data
SQLstr = "SELECT t_Absent_h.absID,t_Absent_h.absDateBgn,t_Absent_h.absDateEnd,m_Customers.cstName,m_CustomersLocation.cloContLocation,t_Absent_h.IdEmp,m_Employee.FName+' '+m_Employee.LName AS NmEmp, m_othFunctAllowance.ofaName, " & _
"t_Absent_h.absGajiPokok,t_Absent_h.absFunctionalAllow,t_Absent_h.absSecurityAllow,t_Absent_h.absTunjKehadiran,t_Absent_h.absMealAllow,t_Absent_h.absOthersAllow,t_Absent_h.absLmbBiasa,t_Absent_h.absLmbNasional,t_Absent_h.absLmbUser, " & _
"t_Absent_h.absPotJamsostek,t_Absent_h.absPotJamsostekKes,t_Absent_h.absPotSeragam,t_Absent_h.absPotPelatihan,t_Absent_h.absPotInvDiklat,t_Absent_h.absPotAbsen,t_Absent_h.absOthersReduction, " & _
"t_Absent_h.absPromosi,t_Absent_h.absDemosi,t_Absent_h.absTotal,t_Absent_h.abs21_PPh,t_Absent_h.absTotal+t_Absent_h.abs21_pph AS absGajiKotor,t_Absent_h.absKeterangan,m_Employee.empAccBank,m_Employee.empAccNo,m_Employee.empDPermanent,m_Employee.empDResign " & _
"FROM t_Absent_h " & _
"INNER JOIN m_Employee ON m_Employee.IdEmp=t_Absent_h.IdEmp " & _
"INNER JOIN m_Customers ON m_Customers.cstID=t_Absent_h.cstID " & _
"INNER JOIN m_CustomersLocation ON m_CustomersLocation.cloID=t_Absent_h.cloID " & _
"LEFT JOIN m_othFunctAllowance ON m_othFunctAllowance.ofaID=t_Absent_h.ofaID " & _
"WHERE CONVERT(VARCHAR(10),t_Absent_h.absDateBgn,120)=@absDateBgn " & _
"ORDER BY cstName ASC,cloContLocation ASC,IdEmp ASC"
'Set command
DBcmd = New SqlCommand(SQLstr, DBcon)
DBcmd.CommandType = CommandType.Text
'Set parameter
DBcmd.Parameters.Add("@absDateBgn", SqlDbType.Date).Value = Format(vabsDateBgn, "yyyy-MM-dd")
'Set DataAdapter
DAtbl = New SqlDataAdapter(DBcmd)
DAtbl.SelectCommand = DBcmd
'Execute DataAdapter
If (DBcon.State <> ConnectionState.Closed) Then : DBcon.Close() : End If
DBcon.Open()
If DStbl.Tables.Contains("tmpPayrollSlip") Then : DStbl.Tables.Remove("tmpPayrollSlip") : End If
DAtbl.Fill(DStbl, "tmpPayrollSlip")
DBcmd.Dispose()
DBcon.Close()
'Add column to dataset
Dim dcH As New DataColumn("sumH", System.Type.GetType("System.Int32"))
Dim dcO As New DataColumn("sumO", System.Type.GetType("System.Int32"))
Dim dcC As New DataColumn("sumC", System.Type.GetType("System.Int32"))
Dim dcA As New DataColumn("sumA", System.Type.GetType("System.Int32"))
Dim dcDS As New DataColumn("sumDS", System.Type.GetType("System.Int32"))
Dim dcTS As New DataColumn("sumTS", System.Type.GetType("System.Int32"))
Dim dcI0 As New DataColumn("sumI0", System.Type.GetType("System.Int32"))
Dim dcI1 As New DataColumn("sumI1", System.Type.GetType("System.Int32"))
Dim dcLB As New DataColumn("sumLB", System.Type.GetType("System.Int32"))
Dim dcLN As New DataColumn("sumLN", System.Type.GetType("System.Int32"))
Dim dcLU As New DataColumn("sumLU", System.Type.GetType("System.Int32"))
Dim dcX As New DataColumn("sumX", System.Type.GetType("System.Int32"))
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcH)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcO)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcC)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcA)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcDS)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcTS)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcI0)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcI1)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcLB)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcLN)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcLU)
DStbl.Tables("tmpPayrollSlip").Columns.Add(dcX)
DStbl.AcceptChanges()
'Finding summary detail absent
Dim countMax As Integer = DStbl.Tables("tmpPayrollSlip").Rows.Count - 1
If countMax < 0 Then
MessageBox.Show("No record available", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Return
End If
ProgressBar1.Maximum = countMax + 1
Dim vabsID As Integer = 0
If (DBcon.State <> ConnectionState.Closed) Then : DBcon.Close() : End If
DBcon.Open()
For i = 0 To countMax
vabsID = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absID")
SQLstr = "SELECT absAbsent,COUNT(absAbsent) AS absAbsentCount,SUM(absOvt) AS absOvtSum,SUM(absAdd) AS absAddSum FROM t_Absent_d WHERE absID=@absID GROUP BY absAbsent"
'Set command
DBcmd = New SqlCommand(SQLstr, DBcon)
DBcmd.CommandType = CommandType.Text
'Set parameter
DBcmd.Parameters.Add("@absID", SqlDbType.Int).Value = vabsID
'Execute reader
DRtbl = DBcmd.ExecuteReader
If DRtbl.HasRows = True Then
Dim vabsAbsent As String = ""
Dim vabsAbsentCount As Integer = 0
Dim vabsOvtSum As Integer = 0
Dim vabsAddSum As Integer = 0
While DRtbl.Read()
vabsAbsent = VB.Trim(DRtbl("absAbsent"))
vabsAbsentCount = VB.Trim(DRtbl("absAbsentCount"))
vabsOvtSum = VB.Trim(DRtbl("absOvtSum"))
vabsAddSum = VB.Trim(DRtbl("absAddSum"))
Select Case vabsAbsent
Case Is = "H"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumH") = vabsAbsentCount
Case Is = "O"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumO") = vabsAbsentCount
Case Is = "C"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumC") = vabsAbsentCount
Case Is = "A"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumA") = vabsAbsentCount
Case Is = "DS"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumDS") = vabsAbsentCount
Case Is = "TS"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumTS") = vabsAbsentCount
Case Is = "I0"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumI0") = vabsAbsentCount
Case Is = "I1"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumI1") = vabsAbsentCount
Case Is = "LB"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLB") = vabsOvtSum + vabsAddSum
Case Is = "LN"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLN") = vabsOvtSum + vabsAddSum
Case Is = "LU"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLU") = vabsOvtSum + vabsAddSum
Case Is = "X"
DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumX") = vabsOvtSum + vabsAddSum
End Select
End While
End If
DRtbl.Close()
DBcmd.Dispose()
vabsID = 0
ProgressBar1.Value = (i + 1)
lblProgress.Text = (i + 1).ToString("#,##0") + " of " + (countMax + 1).ToString("#,##0") + " Write summary to excel"
Next
DBcon.Close()
Delay(0)
lblProgress.Text = "..."
ProgressBar1.Value = 0
'=======================================================
'Export to excel file
'=======================================================
'Create dimension for excel applications
Dim oXL As New Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oRange As Excel.Range
'Start Excel and get Application object.
oXL = CreateObject("Excel.Application")
oXL.Visible = False
oXL.SheetsInNewWorkbook = 1
'Get a new workbook and create FirstSheet
Dim SName As String = "PayrollRecap"
oWB = oXL.Workbooks.Add
oWB.Sheets("Sheet1").Name = SName
'Select active sheet
Dim iRowH As Integer = 4
Dim iColH As Integer = 2
oWB.Sheets(SName).Select()
oWB.Sheets(SName).Cells(iRowH, iColH - 1).Value = "SITE dan LOKASI"
oWB.Sheets(SName).Cells(iRowH, iColH - 0).Value = "No"
oWB.Sheets(SName).Cells(iRowH, iColH + 1).Value = "NIP"
oWB.Sheets(SName).Cells(iRowH, iColH + 2).Value = "NAMA"
oWB.Sheets(SName).Cells(iRowH, iColH + 3).Value = "Jabatan"
oWB.Sheets(SName).Cells(iRowH, iColH + 4).Value = "Gaji Pokok"
oWB.Sheets(SName).Cells(iRowH, iColH + 5).Value = "Jabatan"
oWB.Sheets(SName).Cells(iRowH, iColH + 6).Value = "Security"
oWB.Sheets(SName).Cells(iRowH, iColH + 7).Value = "Kehadiran"
oWB.Sheets(SName).Cells(iRowH, iColH + 8).Value = "U.Makan"
oWB.Sheets(SName).Cells(iRowH, iColH + 9).Value = "Lain2"
oWB.Sheets(SName).Cells(iRowH, iColH + 10).Value = "Biasa"
oWB.Sheets(SName).Cells(iRowH, iColH + 11).Value = "Nasional"
oWB.Sheets(SName).Cells(iRowH, iColH + 12).Value = "P/User"
oWB.Sheets(SName).Cells(iRowH, iColH + 13).Value = "BPJS-TK"
oWB.Sheets(SName).Cells(iRowH, iColH + 14).Value = "BPJS-KES"
oWB.Sheets(SName).Cells(iRowH, iColH + 15).Value = "Seragam"
oWB.Sheets(SName).Cells(iRowH, iColH + 16).Value = "Pelatihan"
oWB.Sheets(SName).Cells(iRowH, iColH + 17).Value = "Investasi Diklat"
oWB.Sheets(SName).Cells(iRowH, iColH + 18).Value = "Absen"
oWB.Sheets(SName).Cells(iRowH, iColH + 19).Value = "Lain2"
oWB.Sheets(SName).Cells(iRowH, iColH + 20).Value = "Penyesuaian (+)"
oWB.Sheets(SName).Cells(iRowH, iColH + 21).Value = "Penyesuaian (-)"
oWB.Sheets(SName).Cells(iRowH, iColH + 22).Value = "Gaji Sblm PPh"
oWB.Sheets(SName).Cells(iRowH, iColH + 23).Value = "PPh 21"
oWB.Sheets(SName).Cells(iRowH, iColH + 24).Value = "Gaji Stlh PPh"
oWB.Sheets(SName).Cells(iRowH, iColH + 25).Value = "Total Gaji"
oWB.Sheets(SName).Cells(iRowH, iColH + 26).Value = "H"
oWB.Sheets(SName).Cells(iRowH, iColH + 27).Value = "O"
oWB.Sheets(SName).Cells(iRowH, iColH + 28).Value = "C"
oWB.Sheets(SName).Cells(iRowH, iColH + 29).Value = "A"
oWB.Sheets(SName).Cells(iRowH, iColH + 30).Value = "DS"
oWB.Sheets(SName).Cells(iRowH, iColH + 31).Value = "TS"
oWB.Sheets(SName).Cells(iRowH, iColH + 32).Value = "I0"
oWB.Sheets(SName).Cells(iRowH, iColH + 33).Value = "I1"
oWB.Sheets(SName).Cells(iRowH, iColH + 34).Value = "LB"
oWB.Sheets(SName).Cells(iRowH, iColH + 35).Value = "LN"
oWB.Sheets(SName).Cells(iRowH, iColH + 36).Value = "LU"
oWB.Sheets(SName).Cells(iRowH, iColH + 37).Value = "X"
oWB.Sheets(SName).Cells(iRowH, iColH + 38).Value = "KETERANGAN"
oWB.Sheets(SName).Cells(iRowH, iColH + 39).Value = "Nama Bank"
oWB.Sheets(SName).Cells(iRowH, iColH + 40).Value = "No.Rekening"
oWB.Sheets(SName).Cells(iRowH, iColH + 41).Value = "Tgl.Masuk"
oWB.Sheets(SName).Cells(iRowH, iColH + 42).Value = "Tgl.Keluar"
countMax = DStbl.Tables("tmpPayrollSlip").Rows.Count - 1
ProgressBar1.Maximum = countMax + 1
Dim iRowD As Integer = 5
Dim iColD As Integer = 2
Dim vNoRec As Integer = 0
Dim vcstName As String = DStbl.Tables("tmpPayrollSlip").Rows(0).Item("cstName")
Dim vcloContLocation As String = DStbl.Tables("tmpPayrollSlip").Rows(0).Item("cloContLocation")
'Variable for Perhitungan summary
Dim vGajiPokok As Decimal = 0 : Dim xGajiPokok As Decimal = 0
Dim vFunctionalAllow As Decimal = 0 : Dim xFunctionalAllow As Decimal = 0
Dim vSecurityAllow As Decimal = 0 : Dim xSecurityAllow As Decimal = 0
Dim vTunjKehadiran As Decimal = 0 : Dim xTunjKehadiran As Decimal = 0
Dim vMealAllow As Decimal = 0 : Dim xMealAllow As Decimal = 0
Dim vOthersAllow As Decimal = 0 : Dim xOthersAllow As Decimal = 0
Dim vLmbBiasa As Decimal = 0 : Dim xLmbBiasa As Decimal = 0
Dim vLmbNasional As Decimal = 0 : Dim xLmbNasional As Decimal = 0
Dim vLmbUser As Decimal = 0 : Dim xLmbUser As Decimal = 0
Dim vPotJamsostek As Decimal = 0 : Dim xPotJamsostek As Decimal = 0
Dim vPotJamsostekKes As Decimal = 0 : Dim xPotJamsostekKes As Decimal = 0
Dim vPotSeragam As Decimal = 0 : Dim xPotSeragam As Decimal = 0
Dim vPotPelatihan As Decimal = 0 : Dim xPotPelatihan As Decimal = 0
Dim vPotInvDiklat As Decimal = 0 : Dim xPotInvDiklat As Decimal = 0
Dim vPotAbsen As Decimal = 0 : Dim xPotAbsen As Decimal = 0
Dim vOthersReduction As Decimal = 0 : Dim xOthersReduction As Decimal = 0
Dim vPromosi As Decimal = 0 : Dim xPromosi As Decimal = 0
Dim vDemosi As Decimal = 0 : Dim xDemosi As Decimal = 0
Dim vabs21_pph As Decimal = 0 : Dim xabs21_pph As Decimal = 0
Dim vabsGajiKotor As Decimal = 0 : Dim xabsGajiKotor As Decimal = 0
Dim vTotal As Decimal = 0 : Dim xTotal As Decimal = 0
Dim vTotalGaji As Decimal = 0 : Dim xTotalGaji As Decimal = 0
For i = 0 To countMax
'Nomor Urut
If DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cstName") & DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cloContLocation") <> vcstName & vcloContLocation Then
'Write sub total summary
oWB.Sheets(SName).Cells(iRowD, iColD + 3).Value = "SUB TOTAL : "
oWB.Sheets(SName).Cells(iRowD, iColD + 4).VALUE = vGajiPokok
oWB.Sheets(SName).Cells(iRowD, iColD + 5).VALUE = vFunctionalAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 6).VALUE = vSecurityAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 7).VALUE = vTunjKehadiran
oWB.Sheets(SName).Cells(iRowD, iColD + 8).VALUE = vMealAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 9).VALUE = vOthersAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 10).VALUE = vLmbBiasa
oWB.Sheets(SName).Cells(iRowD, iColD + 11).VALUE = vLmbNasional
oWB.Sheets(SName).Cells(iRowD, iColD + 12).VALUE = vLmbUser
oWB.Sheets(SName).Cells(iRowD, iColD + 13).VALUE = vPotJamsostek
oWB.Sheets(SName).Cells(iRowD, iColD + 14).VALUE = vPotJamsostekKes
oWB.Sheets(SName).Cells(iRowD, iColD + 15).VALUE = vPotSeragam
oWB.Sheets(SName).Cells(iRowD, iColD + 16).VALUE = vPotPelatihan
oWB.Sheets(SName).Cells(iRowD, iColD + 17).VALUE = vPotInvDiklat
oWB.Sheets(SName).Cells(iRowD, iColD + 18).VALUE = vPotAbsen
oWB.Sheets(SName).Cells(iRowD, iColD + 19).VALUE = vOthersReduction
oWB.Sheets(SName).Cells(iRowD, iColD + 20).VALUE = vPromosi
oWB.Sheets(SName).Cells(iRowD, iColD + 21).VALUE = vDemosi
oWB.Sheets(SName).Cells(iRowD, iColD + 22).VALUE = vabsGajiKotor
oWB.Sheets(SName).Cells(iRowD, iColD + 23).VALUE = vabs21_pph
oWB.Sheets(SName).Cells(iRowD, iColD + 24).VALUE = vTotal
oWB.Sheets(SName).Cells(iRowD, iColD + 25).VALUE = vTotalGaji
'Format excel for summary
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).NumberFormat = "#,##0"
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).Font.Bold = True
'Reset sub total
vGajiPokok = 0 : vFunctionalAllow = 0 : vSecurityAllow = 0 : vTunjKehadiran = 0
vMealAllow = 0 : vOthersAllow = 0 : vLmbBiasa = 0 : vLmbNasional = 0 : vLmbUser = 0
vPotJamsostek = 0 : vPotJamsostekKes = 0 : vPotSeragam = 0 : vPotPelatihan = 0 : vPotInvDiklat = 0 : vPotAbsen = 0 : vOthersReduction = 0
vPromosi = 0 : vDemosi = 0 : vTotal = 0 : vTotalGaji = 0 : vabsGajiKotor = 0 : vabs21_pph = 0
'Add next row
iRowD = iRowD + 2
'Write customer name
vcstName = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cstName")
vcloContLocation = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cloContLocation")
vNoRec = 0
End If
vNoRec = vNoRec + 1
'For summary sub total
vGajiPokok = vGajiPokok + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiPokok")
vFunctionalAllow = vFunctionalAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absFunctionalAllow")
vSecurityAllow = vSecurityAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absSecurityAllow")
vTunjKehadiran = vTunjKehadiran + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTunjKehadiran")
vMealAllow = vMealAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absMealAllow")
vOthersAllow = vOthersAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersAllow")
vLmbBiasa = vLmbBiasa + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbBiasa")
vLmbNasional = vLmbNasional + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbNasional")
vLmbUser = vLmbUser + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbUser")
vPotJamsostek = vPotJamsostek + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostek")
vPotJamsostekKes = vPotJamsostekKes + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostekKes")
vPotSeragam = vPotSeragam + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotSeragam")
vPotPelatihan = vPotPelatihan + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotPelatihan")
vPotInvDiklat = vPotInvDiklat + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotInvDiklat")
vPotAbsen = vPotAbsen + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotAbsen")
vOthersReduction = vOthersReduction + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersReduction")
vPromosi = vPromosi + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPromosi")
vDemosi = vDemosi + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absDemosi")
vabs21_pph = vabs21_pph + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("abs21_PPh")
vabsGajiKotor = vabsGajiKotor + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiKotor")
vTotal = vTotal + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
'For summary total
xGajiPokok = xGajiPokok + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiPokok")
xFunctionalAllow = xFunctionalAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absFunctionalAllow")
xSecurityAllow = xSecurityAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absSecurityAllow")
xTunjKehadiran = xTunjKehadiran + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTunjKehadiran")
xMealAllow = xMealAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absMealAllow")
xOthersAllow = xOthersAllow + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersAllow")
xLmbBiasa = xLmbBiasa + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbBiasa")
xLmbNasional = xLmbNasional + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbNasional")
xLmbUser = xLmbUser + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbUser")
xPotJamsostek = xPotJamsostek + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostek")
xPotJamsostekKes = xPotJamsostekKes + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostekKes")
xPotSeragam = xPotSeragam + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotSeragam")
xPotPelatihan = xPotPelatihan + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotPelatihan")
xPotInvDiklat = xPotInvDiklat + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotInvDiklat")
xPotAbsen = xPotAbsen + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotAbsen")
xOthersReduction = xOthersReduction + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersReduction")
xPromosi = xPromosi + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPromosi")
xDemosi = xDemosi + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absDemosi")
xabs21_pph = xabs21_pph + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("abs21_pph")
xabsGajiKotor = xabsGajiKotor + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiKotor")
xTotal = xTotal + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
'Write to excel cell
oWB.Sheets(SName).Cells(iRowD, iColD - 1).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cstName") & " - " & DStbl.Tables("tmpPayrollSlip").Rows(i).Item("cloCOntLocation")
oWB.Sheets(SName).Cells(iRowD, iColD - 0).Value = vNoRec
oWB.Sheets(SName).Cells(iRowD, iColD + 1).Value = "'" + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("IdEmp")
oWB.Sheets(SName).Cells(iRowD, iColD + 2).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("NmEmp")
oWB.Sheets(SName).Cells(iRowD, iColD + 3).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("ofaName")
oWB.Sheets(SName).Cells(iRowD, iColD + 4).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiPokok")
oWB.Sheets(SName).Cells(iRowD, iColD + 5).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absFunctionalAllow")
oWB.Sheets(SName).Cells(iRowD, iColD + 6).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absSecurityAllow")
oWB.Sheets(SName).Cells(iRowD, iColD + 7).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTunjKehadiran")
oWB.Sheets(SName).Cells(iRowD, iColD + 8).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absMealAllow")
oWB.Sheets(SName).Cells(iRowD, iColD + 9).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersAllow")
oWB.Sheets(SName).Cells(iRowD, iColD + 10).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbBiasa")
oWB.Sheets(SName).Cells(iRowD, iColD + 11).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbNasional")
oWB.Sheets(SName).Cells(iRowD, iColD + 12).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absLmbUser")
oWB.Sheets(SName).Cells(iRowD, iColD + 13).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostek")
oWB.Sheets(SName).Cells(iRowD, iColD + 14).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotJamsostekKes")
oWB.Sheets(SName).Cells(iRowD, iColD + 15).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotSeragam")
oWB.Sheets(SName).Cells(iRowD, iColD + 16).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotPelatihan")
oWB.Sheets(SName).Cells(iRowD, iColD + 17).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotInvDiklat")
oWB.Sheets(SName).Cells(iRowD, iColD + 18).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPotAbsen")
oWB.Sheets(SName).Cells(iRowD, iColD + 19).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absOthersReduction")
oWB.Sheets(SName).Cells(iRowD, iColD + 20).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absPromosi")
oWB.Sheets(SName).Cells(iRowD, iColD + 21).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absDemosi")
oWB.Sheets(SName).Cells(iRowD, iColD + 22).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absGajiKotor")
oWB.Sheets(SName).Cells(iRowD, iColD + 23).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("abs21_pph")
oWB.Sheets(SName).Cells(iRowD, iColD + 24).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
'Total gaji (where absTotal not minus)
If DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal") > 0 Then
vTotalGaji = vTotalGaji + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
xTotalGaji = xTotalGaji + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
oWB.Sheets(SName).Cells(iRowD, iColD + 25).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absTotal")
Else
vTotalGaji = vTotalGaji + 0
xTotalGaji = xTotalGaji + 0
oWB.Sheets(SName).Cells(iRowD, iColD + 25).Value = 0
End If
oWB.Sheets(SName).Cells(iRowD, iColD + 26).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumH")
oWB.Sheets(SName).Cells(iRowD, iColD + 27).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumO")
oWB.Sheets(SName).Cells(iRowD, iColD + 28).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumC")
oWB.Sheets(SName).Cells(iRowD, iColD + 29).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumA")
oWB.Sheets(SName).Cells(iRowD, iColD + 30).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumDS")
oWB.Sheets(SName).Cells(iRowD, iColD + 31).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumTS")
oWB.Sheets(SName).Cells(iRowD, iColD + 32).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumI0")
oWB.Sheets(SName).Cells(iRowD, iColD + 33).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumI1")
oWB.Sheets(SName).Cells(iRowD, iColD + 34).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLB")
oWB.Sheets(SName).Cells(iRowD, iColD + 35).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLN")
oWB.Sheets(SName).Cells(iRowD, iColD + 36).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumLU")
oWB.Sheets(SName).Cells(iRowD, iColD + 37).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("sumX")
oWB.Sheets(SName).Cells(iRowD, iColD + 38).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("absKeterangan")
oWB.Sheets(SName).Cells(iRowD, iColD + 39).Value = DStbl.Tables("tmpPayrollSlip").Rows(i).Item("empAccBank")
oWB.Sheets(SName).Cells(iRowD, iColD + 40).Value = "'" + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("empAccNo")
oWB.Sheets(SName).Cells(iRowD, iColD + 41).Value = "'" + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("empDPermanent")
oWB.Sheets(SName).Cells(iRowD, iColD + 42).Value = "'" + DStbl.Tables("tmpPayrollSlip").Rows(i).Item("empDResign")
iRowD = iRowD + 1
ProgressBar1.Value = (i + 1)
lblProgress.Text = (i + 1).ToString("#,##0") + " of " + (countMax + 1).ToString("#,##0") + " Write detail to excel"
Next
'Write sub total summary for last customer name
oWB.Sheets(SName).Cells(iRowD, iColD + 3).Value = "SUB TOTAL : "
oWB.Sheets(SName).Cells(iRowD, iColD + 4).VALUE = vGajiPokok
oWB.Sheets(SName).Cells(iRowD, iColD + 5).VALUE = vFunctionalAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 6).VALUE = vSecurityAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 7).VALUE = vTunjKehadiran
oWB.Sheets(SName).Cells(iRowD, iColD + 8).VALUE = vMealAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 9).VALUE = vOthersAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 10).VALUE = vLmbBiasa
oWB.Sheets(SName).Cells(iRowD, iColD + 11).VALUE = vLmbNasional
oWB.Sheets(SName).Cells(iRowD, iColD + 12).VALUE = vLmbUser
oWB.Sheets(SName).Cells(iRowD, iColD + 13).VALUE = vPotJamsostek
oWB.Sheets(SName).Cells(iRowD, iColD + 14).VALUE = vPotJamsostekKes
oWB.Sheets(SName).Cells(iRowD, iColD + 15).VALUE = vPotSeragam
oWB.Sheets(SName).Cells(iRowD, iColD + 16).VALUE = vPotPelatihan
oWB.Sheets(SName).Cells(iRowD, iColD + 17).VALUE = vPotInvDiklat
oWB.Sheets(SName).Cells(iRowD, iColD + 18).VALUE = vPotAbsen
oWB.Sheets(SName).Cells(iRowD, iColD + 19).VALUE = vOthersReduction
oWB.Sheets(SName).Cells(iRowD, iColD + 20).VALUE = vPromosi
oWB.Sheets(SName).Cells(iRowD, iColD + 21).VALUE = vDemosi
oWB.Sheets(SName).Cells(iRowD, iColD + 22).VALUE = vabsGajiKotor
oWB.Sheets(SName).Cells(iRowD, iColD + 23).VALUE = vabs21_pph
oWB.Sheets(SName).Cells(iRowD, iColD + 24).VALUE = vTotal
oWB.Sheets(SName).Cells(iRowD, iColD + 25).VALUE = vTotalGaji
'Format excel for summary
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).NumberFormat = "#,##0"
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).Font.Bold = True
Delay(0)
lblProgress.Text = "..."
ProgressBar1.Value = 0
'Write total summary
iRowD = iRowD + 2
oWB.Sheets(SName).Cells(iRowD, iColD + 3).Value = "TOTAL : "
oWB.Sheets(SName).Cells(iRowD, iColD + 4).Value = xGajiPokok
oWB.Sheets(SName).Cells(iRowD, iColD + 5).Value = xFunctionalAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 6).Value = xSecurityAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 7).Value = xTunjKehadiran
oWB.Sheets(SName).Cells(iRowD, iColD + 8).Value = xMealAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 9).Value = xOthersAllow
oWB.Sheets(SName).Cells(iRowD, iColD + 10).Value = xLmbBiasa
oWB.Sheets(SName).Cells(iRowD, iColD + 11).Value = xLmbNasional
oWB.Sheets(SName).Cells(iRowD, iColD + 12).Value = xLmbUser
oWB.Sheets(SName).Cells(iRowD, iColD + 13).Value = xPotJamsostek
oWB.Sheets(SName).Cells(iRowD, iColD + 14).Value = xPotJamsostekKes
oWB.Sheets(SName).Cells(iRowD, iColD + 15).Value = xPotSeragam
oWB.Sheets(SName).Cells(iRowD, iColD + 16).Value = xPotPelatihan
oWB.Sheets(SName).Cells(iRowD, iColD + 17).Value = xPotInvDiklat
oWB.Sheets(SName).Cells(iRowD, iColD + 18).Value = xPotAbsen
oWB.Sheets(SName).Cells(iRowD, iColD + 19).Value = xOthersReduction
oWB.Sheets(SName).Cells(iRowD, iColD + 20).Value = xPromosi
oWB.Sheets(SName).Cells(iRowD, iColD + 21).Value = xDemosi
oWB.Sheets(SName).Cells(iRowD, iColD + 22).Value = xabsGajiKotor
oWB.Sheets(SName).Cells(iRowD, iColD + 23).Value = xabs21_pph
oWB.Sheets(SName).Cells(iRowD, iColD + 24).Value = xTotal
oWB.Sheets(SName).Cells(iRowD, iColD + 25).Value = xTotalGaji
'Format excel
oWB.Sheets(SName).Range("A1:A2").Font.Size = 13
oWB.Sheets(SName).Range("A1", "AR4").Font.Bold = True
oWB.Sheets(SName).Range("F5", "AI" + CStr(iRowD - 1)).NumberFormat = "#,##0"
oWB.Sheets(SName).Range("F" + CStr(iRowH), "AI" + CStr(iRowD - 1)).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
'Format excel for summary
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).NumberFormat = "#,##0"
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Range("E" + CStr(iRowD), "AA" + CStr(iRowD)).Font.Bold = True
'Autofit column when loop ending
oWB.Sheets(SName).Columns.AutoFit()
oWB.Sheets(SName).Cells(1, 1).Value = "PERHITUNGAN GAJI SECURITY"
oWB.Sheets(SName).Cells(2, 1).Value = "Periode : " + Format(dtabsDateBgn.Value, "yyyy-MM-dd") + " s/d " + Format(dtabsDateEnd.Value, "yyyy-MM-dd")
oWB.Sheets(SName).Range("A3", "A4").MergeCells = True 'Site and Location
oWB.Sheets(SName).Cells(3, 1).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(3, 1).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("B3", "B4").MergeCells = True 'No
oWB.Sheets(SName).Cells(3, 2).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(3, 2).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("C3", "C4").MergeCells = True 'NIP
oWB.Sheets(SName).Cells(3, 3).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(3, 3).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("D3", "D4").MergeCells = True 'NAMA
oWB.Sheets(SName).Cells(3, 4).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(3, 4).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("E3", "E4").MergeCells = True 'JABATAN
oWB.Sheets(SName).Cells(3, 5).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(3, 5).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("F3", "F4").MergeCells = True 'GAJI POKOK
oWB.Sheets(SName).Cells(3, 6).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Cells(3, 6).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Cells(3, 7).Value = "TUNJANGAN"
oWB.Sheets(SName).Cells(3, 7).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Range("G3", "K3").MergeCells = True
oWB.Sheets(SName).Cells(3, 12).Value = "LEMBUR"
oWB.Sheets(SName).Cells(3, 12).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Range("L3", "N3").MergeCells = True
oWB.Sheets(SName).Cells(3, 17).Value = "POTONGAN"
oWB.Sheets(SName).Cells(3, 17).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Range("O3", "U3").MergeCells = True
oWB.Sheets(SName).Cells(3, 29).Value = "KEHADIRAN"
oWB.Sheets(SName).Cells(3, 29).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Range("AB3", "AM3").MergeCells = True
oWB.Sheets(SName).Range("V3", "V4").MergeCells = True 'PROMOSI
oWB.Sheets(SName).Cells(3, 22).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Cells(3, 22).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("W3", "W4").MergeCells = True ' DEMOSI
oWB.Sheets(SName).Cells(3, 23).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Cells(3, 23).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("X3", "X4").MergeCells = True ' Gaji Kotor
oWB.Sheets(SName).Cells(3, 24).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Cells(3, 24).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("Y3", "Y4").MergeCells = True ' PPH 21
oWB.Sheets(SName).Cells(3, 24).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Cells(3, 24).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("Z3", "Z4").MergeCells = True ' TOTAL
oWB.Sheets(SName).Cells(3, 25).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Cells(3, 25).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("AA3", "AA4").MergeCells = True ' TOTAL GAJI
oWB.Sheets(SName).Cells(3, 26).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
oWB.Sheets(SName).Cells(3, 26).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("AN3", "AN4").MergeCells = True ' Keterangan
oWB.Sheets(SName).Cells(3, 37).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
oWB.Sheets(SName).Cells(3, 37).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("AO3", "AO4").MergeCells = True ' Nama Bank
oWB.Sheets(SName).Cells(3, 38).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
oWB.Sheets(SName).Cells(3, 38).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("AP3", "AP4").MergeCells = True ' No.Rekening
oWB.Sheets(SName).Cells(3, 39).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
oWB.Sheets(SName).Cells(3, 39).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("AQ3", "AQ4").MergeCells = True ' Tgl. Masuk
oWB.Sheets(SName).Cells(3, 40).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
oWB.Sheets(SName).Cells(3, 40).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oWB.Sheets(SName).Range("AR3", "AR4").MergeCells = True ' Tgl. Keluar
oWB.Sheets(SName).Cells(3, 41).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft
oWB.Sheets(SName).Cells(3, 41).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
'Line formated
oRange = oWB.Sheets(SName).Range("A3", "AR" + CStr(iRowD))
'Border
With oRange.Borders(Excel.XlBordersIndex.xlEdgeTop)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlMedium
.ColorIndex = 1
End With
With oRange.Borders(Excel.XlBordersIndex.xlEdgeBottom)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
.ColorIndex = 1
End With
With oRange.Borders(Excel.XlBordersIndex.xlEdgeLeft)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
.ColorIndex = 1
End With
With oRange.Borders(Excel.XlBordersIndex.xlEdgeRight)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
.ColorIndex = 1
End With
'Inside
With oRange.Borders(Excel.XlBordersIndex.xlInsideVertical)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
.ColorIndex = 1
End With
With oRange.Borders(Excel.XlBordersIndex.xlInsideHorizontal)
.LineStyle = Excel.XlLineStyle.xlContinuous
.Weight = Excel.XlBorderWeight.xlThin
.ColorIndex = 1
End With
'Footer tertanda
oWB.Sheets(SName).Cells(iRowD + 3, iColD + 1).Value = "Tangerang, " + Format(Now, "dd MMM yyyy")
oWB.Sheets(SName).Cells(iRowD + 4, iColD + 1).Value = "Dibuat oleh,"
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 1).Value = "Meylitha"
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 1).Font.Underline = True
oWB.Sheets(SName).Cells(iRowD + 8, iColD + 1).Value = "Payroll"
oWB.Sheets(SName).Cells(iRowD + 10, iColD + 1).Value = "Tembusan Kasir"
oWB.Sheets(SName).Cells(iRowD + 4, iColD + 4).Value = "Diperiksa,"
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 4).Value = "Ernina"
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 4).Font.Underline = True
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 4).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(iRowD + 8, iColD + 4).Value = "HR Manager"
oWB.Sheets(SName).Cells(iRowD + 8, iColD + 4).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(iRowD + 4, iColD + 9).Value = "Diperiksa,"
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 9).Value = "Sastra"
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 9).Font.Underline = True
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 9).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(iRowD + 8, iColD + 9).Value = "Wakil Direktur"
oWB.Sheets(SName).Cells(iRowD + 8, iColD + 9).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(iRowD + 4, iColD + 13).Value = "Disetujui,"
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 13).Value = "Thomas Torana"
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 13).Font.Underline = True
oWB.Sheets(SName).Cells(iRowD + 7, iColD + 13).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
oWB.Sheets(SName).Cells(iRowD + 8, iColD + 13).Value = "Direktur"
oWB.Sheets(SName).Cells(iRowD + 8, iColD + 13).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
'Password for excel
oWB.Sheets(SName).Protect("gbn@123", AllowSorting:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True)
'Make sure Excel is visible and give the user control of Excel's lifetime.
oXL.Visible = True
oXL.UserControl = True
'Release object {oWB.Close():oXL.Quit()}
oSheet = Nothing : releaseObject(oSheet)
releaseObject(oWB)
releaseObject(oXL)
End Sub
Private Sub Delay(ByVal DelayInSeconds As Integer)
Dim TS As TimeSpan
Dim TargetTime As DateTime = DateTime.Now.AddSeconds(DelayInSeconds)
Do
TS = TargetTime.Subtract(DateTime.Now)
Application.DoEvents() ' Keep App Responsive
System.Threading.Thread.Sleep(10) ' Reduce CPU Usage
Loop While TS.TotalSeconds > 0
End Sub
Private Sub DelayMSC(ByVal DelayInMilliseconds As Integer)
Dim timeOut As DateTime = Now.AddMilliseconds(DelayInMilliseconds)
Do
'Keep the app from freezing and allow Windows to continue processing.
Application.DoEvents()
Loop Until Now > timeOut 'Keep looping until the elasped time of milliseconds.
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Catch ex As Exception
Finally
obj = Nothing
GC.Collect()
End Try
End Sub
|
|
|
|
|
You need to profile your code to find out where the bottleneck is before you can start fixing it. Just dumping 669 lines of unexplained code in a message and expecting us to profile it for you isn't going to work - especially since we don't have access to your database. And since you can't be bothered to remove irrelevant code, such as the unused DelayMSC function, you clearly don't care about helping us to help you.
For a start, look at your queries. If your outer query returns 2000 rows, you will be executing 2001 queries to load the data, since you execute a separate query for every row in the "header data" query. There will almost certainly be a way to combine the queries so that you only need to execute one.
You have multiple calls to the Delay function, which will delay your code by more than 10ms each time you call it.
You're calling Application.DoEvents , which is always a code-smell. It indicates that you're doing work on the UI thread which should actually be performed on a background thread using a BackgroundWorker[^] or similar.
Also, Office Interop can be quite slow. You might have better luck using the Open XML SDK[^] to generate the file instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I'm using "avicap32.dll" for my program which found on this website. However the webcam preview only works on older laptop built in webcam but display black/blank on newer model laptop.
Anyone has the solution to this? Been cracking my head for few months trying to solve it.
Attach below with my code:
1 Imports System.IO
2 Imports System.Threading
3 Imports System.Diagnostics
4 Imports System.Runtime.InteropServices
5
6 Public Class frmTest
7 Private Structure BITMAPINFOHEADER
8 Dim biSize As Integer
9 Dim biWidth As Integer
10 Dim biHeight As Integer
11 Dim biPlanes As Short
12 Dim biBitCount As Short
13 Dim biCompression As Integer
14 Dim biSizeImage As Integer
15 Dim biXPelsPerMeter As Integer
16 Dim biYPelsPerMeter As Integer
17 Dim biClrUsed As Integer
18 Dim biClrImportant As Integer
19 End Structure
20
21 Private Structure BITMAPINFO
22 Dim bmiHeader As BITMAPINFOHEADER
23 Dim bmiColors() As Integer
24 End Structure
25
26 Const WM_CAP As Short = &H400S
27 Const WM_CAP_DRIVER_CONNECT As Integer = WM_CAP + 10
28 Const WM_CAP_DRIVER_DISCONNECT As Integer = WM_CAP + 11
29 Const WM_CAP_EDIT_COPY As Integer = WM_CAP + 30
30 Const WM_CAP_SET_VIDEOFORMAT = WM_CAP + 45
31 Const WM_CAP_SET_PREVIEW As Integer = WM_CAP + 50
32 Const WM_CAP_SET_PREVIEWRATE As Integer = WM_CAP + 52
33 Const WM_CAP_SET_SCALE As Integer = WM_CAP + 53
34
35 Const WS_CHILD As Integer = &H40000000
36 Const WS_VISIBLE As Integer = &H10000000
37 Const SWP_NOMOVE As Short = &H2S
38 Const SWP_NOZORDER As Short = &H4S
39 Const HWND_BOTTOM As Short = 1
40
41 Declare Function SetWindowPos Lib "user32" Alias "SetWindowPos" (ByVal hwnd As Integer, _
42 ByVal hWndInsertAfter As Integer, ByVal x As Integer, ByVal y As Integer, _
43 ByVal cx As Integer, ByVal cy As Integer, ByVal wFlags As Integer) As Integer
44
45 Declare Function capCreateCaptureWindowA Lib "avicap32.dll" _
46 (ByVal lpszWindowName As String, ByVal dwStyle As Integer, _
47 ByVal x As Integer, ByVal y As Integer, ByVal nWidth As Integer, _
48 ByVal nHeight As Short, ByVal hWndParent As Integer, _
49 ByVal nID As Integer) As Integer
50
51 Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
52 (ByVal hwnd As Integer, ByVal wMsg As Integer, ByVal wParam As Integer, _
53 <MarshalAs(UnmanagedType.AsAny)> ByVal lParam As Object) As Integer
54
55 Declare Function DestroyWindow Lib "user32" (ByVal hndw As Integer) As Boolean
56
57 Private iDevice As Integer = 0
58 Private hHwnd As Integer
59
60 Private intSecurity As ENSECURITY
61 Private m_strImage As String
62 Private m_strFinger As String
63 Private m_imageRawData() As Byte = Nothing
64 Private m_strReader As String
65
66
67
68
69 Public WriteOnly Property SetSecurity() As Integer
70 Set(ByVal value As Integer)
71 intSecurity = value
72 If intSecurity = ENSECURITY.EN_READ Then
73
74 End If
75 End Set
76 End Property
77
78
79
80 Private Sub OpenPreviewWindow()
81 Dim iHeight As Integer = pbPhoto.Height
82 Dim iWidth As Integer = pbPhoto.Width
83
84
85
86
87 hHwnd = capCreateCaptureWindowA(iDevice.ToString, WS_VISIBLE Or WS_CHILD, 0, 0, 1280, _
88 1024, pbPhoto.Handle.ToInt32, 0)
89
90
91
92
93 If SendMessage(hHwnd, WM_CAP_DRIVER_CONNECT, iDevice, 0) Then
94
95
96
97 SendMessage(hHwnd, WM_CAP_SET_SCALE, True, 0)
98
99
100
101
102 SendMessage(hHwnd, WM_CAP_SET_PREVIEWRATE, 66, 0)
103
104
105
106
107 SendMessage(hHwnd, WM_CAP_SET_PREVIEW, True, 0)
108
109
110
111
112 SetWindowPos(hHwnd, HWND_BOTTOM, 0, 0, pbPhoto.Width, pbPhoto.Height, _
113 SWP_NOMOVE Or SWP_NOZORDER)
114
115
116
117 Else
118
119
120
121 DestroyWindow(hHwnd)
122
123 End If
124 End Sub
125
126 Private Sub fncStart()
127 Try
128 File.Delete(Application.StartupPath & "\" & "Web.jpg")
129 If hHwnd = 0 Then
130 hHwnd = capCreateCaptureWindowA(iDevice, WS_VISIBLE Or WS_CHILD, 0, 0, pbPhoto.Width, pbPhoto.Height, pbPhoto.Handle.ToInt32, 0)
131 End If
132
133
134 If SendMessage(hHwnd, WM_CAP_DRIVER_CONNECT, iDevice, 0) Then
135
136 Dim myBitMapInfo As BITMAPINFO
137 With myBitMapInfo.bmiHeader
138 .biSize = Len(myBitMapInfo.bmiHeader)
139 .biWidth = 1024
140 .biHeight = 768
141 .biPlanes = 1
142 .biBitCount = 24
143 End With
144
145 SendMessage(hHwnd, WM_CAP_SET_VIDEOFORMAT, Len(myBitMapInfo), myBitMapInfo)
146
147 SendMessage(hHwnd, WM_CAP_SET_SCALE, True, 0)
148
149 SendMessage(hHwnd, WM_CAP_SET_PREVIEWRATE, 66, 0)
150
151 SendMessage(hHwnd, WM_CAP_SET_PREVIEW, True, 0)
152
153 SetWindowPos(hHwnd, HWND_BOTTOM, 0, 0, pbPhoto.Width, pbPhoto.Height, SWP_NOMOVE Or SWP_NOZORDER)
154
155 Else
156
157 DestroyWindow(hHwnd)
158 End If
159 Catch ex As Exception
160
161 End Try
162
163 End Sub
164
165 Private Sub ClosePreviewWindow()
166
167
168
169 SendMessage(hHwnd, WM_CAP_DRIVER_DISCONNECT, iDevice, 0)
170
171
172
173
174
175 DestroyWindow(hHwnd)
176 End Sub
177
178 Private Sub fncCapture()
179 Dim data As IDataObject
180 Dim bmap As Bitmap
181 Dim strImageFileName As String
182 Dim fsImage As FileStream = Nothing
183
184
185
186 SendMessage(hHwnd, WM_CAP_EDIT_COPY, 0, 0)
187
188
189
190
191 data = Clipboard.GetDataObject()
192 If data.GetDataPresent(GetType(System.Drawing.Bitmap)) Then
193 strImageFileName = Application.StartupPath & "\" & "Web.jpg"
194 bmap = CType(data.GetData(GetType(System.Drawing.Bitmap)), Bitmap)
195 pbPhoto.Image = bmap
196
197 ClosePreviewWindow()
198
199 Trace.Assert(Not (bmap Is Nothing))
200
201 bmap.Save(strImageFileName, Imaging.ImageFormat.Jpeg)
202
203 pbPhoto.ImageLocation = strImageFileName
204 fsImage = New FileStream(strImageFileName, FileMode.Open, FileAccess.Read)
205 m_imageRawData = New Byte(fsImage.Length) {}
206 fsImage.Read(m_imageRawData, 0, fsImage.Length)
207 fsImage.Close()
208
209 End If
210 hHwnd = Nothing
211 End Sub
212
213
214
215 Private Sub frmTest_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
216 Dim retval As Int32
217 Dim readers(-1)() As Char
218 Try
219
220
221
222 retval = GetReaders(readers)
223
224
225
226
227
228
229
230
231
232
233 m_strReader = CStr(readers(0))
234 If txtName.CanFocus Then txtName.Focus()
235
236
237 Catch ex As Exception
238 cmdMyKad.Visible = False
239
240 End Try
241 End Sub
242
243
244
245
246 Private Sub pbPhoto_Resize(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles pbPhoto.Resize
247 fncStart()
248 End Sub
modified 17-Aug-21 3:45am.
|
|
|
|
|
You need to provide more details, rather than just dumping 250 lines of code here.
|
|
|
|
|
You seem to be swallowing some exceptions. Put some logging in there to find out if all the code runs as you expect it.
Also, the cam should probably not be in use by another application at the same time.
Bastard Programmer from Hell
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.
|
|
|
|
|
I have a small clock/calendar Applet that lives on my desktop, since (for at least some purposes) I like to have an analog clock. I recently decided to clean up the Panel that displays the clock to make the square background to the round clockface transparent, so that the clock face would just 'float' over the desktop. I discovered that this was not simply a matter of making the relevant area in the source image transparent, since the transparency 'disappears' when the image is loaded into the Panel. However, the following load code almost works when the panel is displayed:
Private Function ConvertImageToRGBFormat(img As Image) As Image
Dim temp As New Bitmap(img.Width, img.Height, System.Drawing.Imaging.PixelFormat.Format32bppRgb)
Dim g As Graphics = Graphics.FromImage(temp)
g.DrawImage(img, New Rectangle(0, 0, img.Width, img.Height), 0, 0, img.Width, img.Height, GraphicsUnit.Pixel)
g.Dispose()
temp.MakeTransparent(temp.GetPixel(1, 1))
Return temp
End Function
I say that it 'almost' works because the square background of the displayed image is, indeed, transparent, except for a very thin green 'halo' round the clock. I typically use bright green as the guide color for image areas that I am going to make transparent, so I initially thought that the feathering at the edges of the clock had caused this, and changed the color for the transparent area to a dark gray (similar to the background color on my desktop), so that there was no green anywhere in the image (or in its color palette). Notwithstanding this change, the green halo persists. This is how the Panel appears on the desktop, and this is the source image.
Does anyone have any idea what is going on?
|
|
|
|
|
Yeah, it's aliasing. The "green" halo isn't green. At least, it's not the green of your transparency green.
In order to get rid of the green, you have to get rid of the antialiasing while drawing the clock image. On your Graphics object, set the SmoothingMode = None.
Your clock image will end up with "jaggies" all around it, but you won't get the halo any more.
|
|
|
|
|
Thanks for your response, which makes sense. I have, however, now tried setting the SmoothingMode to None in the Panel's overridden OnPaint Event without changing the (mis)behavior. I assume that the aliasing is occurring earlier, probably when the image (a .png file included and passed as a Resource item) is read in and converted to its internal Bitmap format, and I don't see how I can influence that step.
|
|
|
|
|
It could also be in the image itself if it was created with antialiasing turned on.
|
|
|
|
|
Yes. Looking at the image at a very high zoom, I do now see that it is antialiased. However, the 'smoothing' is all grayscale, which would not be a problem if it carried over to the final displayed image. It is the green false color introduced by the Panel's rendering of it that is the major issue. I will see if I can 'un-antialias' the original image, but that may introduce different, but equally unsightly, problems.
|
|
|
|
|
Since writing my last message, I 'un-antialiased' the source image and tried again. Even though there are only three colors of pixels in the new source image (pure black, pure white, and the very dark gray used for the area to be rendered as transparent), the panel appears exactly the same when displayed on the desktop - with the thin green 'halo' round the clock. The new source image can be found here. It really does look as if the false color aliasing is happening when the image is created/initialized.
modified 6-Aug-21 16:06pm.
|
|
|
|
|
I did something similar, though not transparent, a couple decades ago.
Instead of using an image like that, I just drew the entire clock face when needed.
|
|
|
|
|
Since the clock face is invariant, it seemed to me to make sense to use a static image. My other experiments, described in the other 'branch' of this thread, would also suggest that your approach would not solve the haloing problem with transparency.
|
|
|
|
|
I think no matter what color you pick for the chroma key, you're going to have the same problem. It comes down to the key color you pick isn't the resulting key color when the black of the clock face and the key color are blended together. I don't think there's going to be any solution to the problem while using a chroma key.
|
|
|
|
|
I am not sure what you mean by 'chroma key' in this context. The placeholder 'color' I am using for the transparent area is a very dark gray - the entire palette of the image is grayscale. If you blend black, white, and a pure grey in any proportions, you shouldn't get bright green!
|
|
|
|
|
The "chroma key" is the color chosen to be replaced by "transparent", or "green screen".
|
|
|
|
|
That's what I thought you meant, hence my comment that blending white, black, and gray shouldn't give you green!
|
|
|
|
|
PixelFormat.Format32bppRgb
With black and white you can get by with 8 bits.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Yes. I was reusing old code which allowed color images. I wasn't the original author, so I didn't change anything that I didn't think absolutely had to be changed, and I wasn't sure whether any of the other reused code implicitly assumed the specified format. Indeed, experimentation since you raised the issue showed that using other formats (including indexed and grayscale ones) does cause other problems.
|
|
|
|
|
In general, Windows (10) recommends against bit maps because they don't scale well. Use (font) icons and vector graphics. Segoe UI MDL2 assets can be overlayed, reversed and rotated. The .NET shape classess provide circles, polygons, etc.
In your case, I would try overlaying the outter problem area with an ellipse / circle (black stroke, transparent fill) for a quick fix.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
Well, that was very interesting! Doing as you suggested does not solve the problem. If you use a sensible sized pen (fairly small), nothing visible happens, and the 'halo' looks just as before, though there is a suggestion of it disappearing at the points where the drawn circle overlaps the edges of the original image (the top, bottom, and two sides). If you use a thicker pen, it is more obvious that the 'halo' is gone at the overlapped edges, but it is just pushed out to the edge of the drawn circle over the rest of the rendered image - it still always appears where a 'transparent' pixel was adjacent to one of another color, whether that pixel was part of the original bitmap or subsequently drawn on the Graphic. Presumably, therefore, it is an artefact of the [Bitmap].MakeTransparent() Function, but I have no idea why it has not (as far as I can tell) been described before
Parenthetically, I now see that that function forces the Bitmap on which it is invoked to 32bppRgb mode, since that format has the alpha channel which it needs, so any attempt to use a different format would be pointless.
|
|
|
|
|
I think your problem is part of Windows Forms.
I do a lot of graphics in UWP and WPF and never have any issues I can't overcome.
e.g. WPF and snap to pixels:
Quote: You can set this property to true on your root element to enable pixel snap rendering throughout the UI. For devices operating at greater than 96 dots per inch (dpi), pixel snap rendering can minimize anti-aliasing visual artifacts in the vicinity of single-unit solid lines.
It was only in wine that he laid down no limit for himself, but he did not allow himself to be confused by it.
― Confucian Analects: Rules of Confucius about his food
|
|
|
|
|
I want to pass PointF as optional Parameter. But I am finding it difficult to assign default value for PointF.
I can't assign Nothing. If I do it becomes (0,0) which can be working value hence can't be used as a unique default value.
I am not able to assign any other value. I tried (Optional P1 As PointF = New PointF(-100, -100) but it does not work.
Public MyFunc(Optional P1 As PointF = ????)
Please help.
|
|
|
|
|