private void btnPrint_Click(object sender, EventArgs e) { try { Cursor.Current = Cursors.WaitCursor; xl.Load(@".\Reports\Payroll_Summary.xlsx"); var sh = xl.Sheets["Payroll"]; var header = sh.PrintSettings.Header; header = header.Replace("%Date%", DateTime.Now.ToString("dd-MMM-yy hh:mm tt")); header = header.Replace("%Title%", ItemData.Remarks); //header = header.Replace("%Title%", ItemData.DateCovered.ToString("dd MMMM yyyy")); sh.PrintSettings.Header = header; //sh.PrintSettings.AutoScale = true; //sh.PrintSettings.FitPagesAcross = 1; panelStatus.Left = (this.Width / 2) - (panelStatus.Width / 2); panelStatus.Visible = true; pb.Maximum = ItemData.Employees.Items.Count(); var row = 1; foreach (var item in ItemData.Employees.Items) { row++; var pName = new PersonName(); pName.Map(item); lblStatus.Text = "Generating " + pName.Fullname + "..."; lblStatus.Refresh(); sh[row, 0].Value = row - 1; sh[row, 1].Value = item.EmpNum; sh[row, 2].Value = pName.Fullname; sh[row, 5].Value = item.CurrentPosition; sh[row, 8].Value = item.SG; sh[row, 9].Value = item.Step; sh[row, 10].Value = item.GrossBasicSalary; sh[row, 11].Value = item.TaxShortDesc; sh[row, 12].Value = item.TaxExemption; // CREATE PAYSLIP var shPayslip = xl.Sheets["Sheet1"].Clone(); shPayslip.Name = "Payslip #" + (row - 1).ToString(); xl.Sheets.Add(shPayslip); //Profile Picture var profilePic = new XLPictureShape(InputControls.GetImage(item.CameraCounter), 50, 1300, 900, 900); profilePic.LineStyle = XLShapeLineStyleEnum.Simple; shPayslip.Shapes.Add(profilePic); shPayslip[4, 0].Value = ItemData.Remarks; shPayslip[6, 2].Value = pName.Fullname; shPayslip[7, 2].Value = "Position : " + item.CurrentPosition; shPayslip[8, 2].Value = $"Salary Grade : {item.SG}-{item.Step}"; shPayslip[9, 2].Value = $"Tax Code : {item.TaxShortDesc}"; shPayslip[11, 4].Value = item.BasicSalary; shPayslip[12, 4].Value = item.GrossBasicSalary; //0111 Medicare(PhilHealth) //0222 PAG - IBIG Premium //0036 BIR Withholding Tax //0001 SSS var sssDeduction = (decimal)0.00; var birDeduction = (decimal)0.00; var philHealthDeduction = (decimal)0.00; var pagibigDeduction = (decimal)0.00; var otherDeduction = (decimal)0.00; var totalDeduction = (decimal)0.00; var payslipRow = 16; foreach (var deduction in item.Deductions.Items) { totalDeduction += deduction.Amount; switch (deduction.Code) { case "0036": birDeduction = deduction.Amount; break; case "0001": sssDeduction = deduction.Amount; break; case "0111": philHealthDeduction = deduction.Amount; break; case "0222": pagibigDeduction = deduction.Amount; break; default: otherDeduction += deduction.Amount; break; } //Do Payslip Deductions //var newRow = shPayslip.Rows[16].Clone(); //shPayslip.Rows.Insert(16, newRow); shPayslip[payslipRow, 1].Value = deduction.Code; shPayslip[payslipRow, 2].Value = deduction.Description; shPayslip[payslipRow, 4].Value = deduction.Amount; payslipRow++; } sh[row, 13].Value = birDeduction; // item.Deductions.Items.FirstOrDefault(_ => _.Code == "0036")?.Amount; // Bir sh[row, 14].Value = sssDeduction; // item.Deductions.Items.FirstOrDefault(_ => _.Code == "0001")?.Amount; // SSS sh[row, 15].Value = philHealthDeduction; // item.Deductions.Items.FirstOrDefault(_ => _.Code == "0111")?.Amount; // PhilHealth sh[row, 16].Value = pagibigDeduction; // item.Deductions.Items.FirstOrDefault(_ => _.Code == "0222")?.Amount; // Pagibig sh[row, 17].Value = otherDeduction; sh[row, 18].Formula = string.Format("=SUM(N{0}:Q{0})", row + 1); sh[row, 19].Value = item.BasicSalary; shPayslip[13, 4].Value = totalDeduction; shPayslip.Locked = true; pb.Value = row; } sh.PrintSettings.FitPagesAcross = 1; sh.Locked = true; xl.Sheets.Remove(xl.Sheets["Sheet1"]); var targetFolder = Path.Combine(Path.GetTempPath(), "amwp"); var file = DateTime.Now.ToString("yymmddhhmmss"); var filename = file + ".xlsx"; Directory.CreateDirectory(targetFolder); xl.Save(Path.Combine(targetFolder, filename)); panelStatus.Visible = false; //System.Threading.Thread.Sleep(1000); Process.Start(Path.Combine(targetFolder, filename)); } catch (Exception ex) { MessageDialog.ShowError(ex, this); panelStatus.Visible = false; } }