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;
}
}
public C1XLBook CreateSample()
{
// create C1XLBook
C1XLBook wb = new C1XLBook();
// three methods add images to Excel file
XLPictureShape picture;
// create images
Metafile metafile = (Metafile)Metafile.FromStream(GetManifestResource("meta.emf"));
Image spbImage = Image.FromStream(GetManifestResource("spb.jpg"));
Image canadaImage = Image.FromStream(GetManifestResource("canada.bmp"));
Image googleImage = Image.FromStream(GetManifestResource("google.bmp"));
Image babyImage = Image.FromStream(GetManifestResource("baby.png"));
/////////////////////////////////////////////////////////
// List "Images" -- three methods add images
/////////////////////////////////////////////////////////
XLSheet sheet = wb.Sheets[0];
sheet.Name = "Images";
sheet.PrintSettings.Header = "&LCanada:&G&D&CHeader Center&R&P";
sheet.PrintSettings.Footer = "&C&Z";
// first method
picture = new XLPictureShape(googleImage, 0, 0, 2200, 5000);
picture.DashedLineStyle = XLShapeDashedLineStyleEnum.Solid;
picture.LineStyle = XLShapeLineStyleEnum.Simple;
picture.LineColor = Color.BlueViolet;
picture.Rotation = 90.0f;
picture.LineWidth = 10;
sheet[1, 7].Value = picture;
sheet[1, 1].Value = metafile;
// second method
picture = new XLPictureShape(spbImage, 100, 3000, 8000, 6000);
//picture = new XLPictureShape(canadaImage);
picture.Brightness = .55f;
picture.Contrast = .5f;
sheet.Shapes.Add(picture);
picture = new XLPictureShape(canadaImage);
picture.LineColor = Color.Aqua;
picture.LineWidth = 100;
sheet.PrintSettings.HeaderPictureLeft = picture;
// third method
picture = new XLPictureShape(babyImage);
picture.Rotation = 30.0f;
picture.LineColor = Color.Aqua;
picture.LineWidth = 100;
sheet[15, 2].Value = picture;
// header or footer image
//picture = new XLPictureShape(spbImage);
//picture.Rotation = 45.0f; // for header or footer ignored
//picture.LineColor = Color.Blue;
//picture.LineWidth = 50;
//picture.Brightness = 0.4f;
//sheet.PrintSettings.HeaderPictureCenter = picture;
/////////////////////////////////////////////////////////
// List "Types" -- support image types (bmp, png, jpg, emf)
/////////////////////////////////////////////////////////
sheet = wb.Sheets.Add("Types");
sheet[1, 0].Value = "Bmp:";
sheet[1, 1].Value = googleImage;
sheet[8, 0].Value = "Png:";
sheet[8, 1].Value = babyImage;
sheet[25, 0].Value = "Jpeg:";
sheet[25, 1].Value = spbImage;
sheet[34, 0].Value = "Emf:";
sheet[34, 1].Value = metafile;
/////////////////////////////////////////////////////////
// List "Borders" -- various picture borders
/////////////////////////////////////////////////////////
sheet = wb.Sheets.Add("Borders");
int row = 1, col = 0;
foreach (XLShapeLineStyleEnum style in Enum.GetValues(typeof(XLShapeLineStyleEnum)))
{
col = 1;
sheet.Rows[row].Height = 3700;
foreach (XLShapeDashedLineStyleEnum dashedStyle in Enum.GetValues(typeof(XLShapeDashedLineStyleEnum)))
{
sheet.Columns[col].Width = 2300;
picture = new XLPictureShape(babyImage);
picture.LineWidth = 100;
picture.LineColor = Color.FromArgb(100, 200, Math.Min(col * 12, 255));
picture.DashedLineStyle = dashedStyle;
picture.LineStyle = style;
sheet[row, col].Value = picture;
sheet[row + 1, col].Value = "style: " + style.ToString();
sheet[row + 2, col].Value = "dashed: " + dashedStyle.ToString();
col += 2;
}
row += 4;
}
/////////////////////////////////////////////////////////
// List "Alignment" -- position image using ContentAlignment
/////////////////////////////////////////////////////////
sheet = wb.Sheets.Add("Alignment");
row = 1;
sheet.Columns[1].Width = sheet.Columns[4].Width = 6000;
sheet.Columns[7].Width = sheet.Columns[10].Width = 2000;
foreach (ContentAlignment alignment in Enum.GetValues(typeof(ContentAlignment)))
{
sheet.Rows[row].Height = 2400;
Size cellSize = new Size(sheet.Columns[1].Width, sheet.Rows[row].Height);
picture = new XLPictureShape(googleImage, cellSize, alignment, ImageScaling.Clip);
sheet[row, 1].Value = picture;
sheet[row, 2].Value = "clip: " + alignment.ToString();
picture = new XLPictureShape(googleImage, cellSize, alignment, ImageScaling.Scale);
sheet[row, 4].Value = picture;
sheet[row, 5].Value = "scale: " + alignment.ToString();
row += 4;
}
/////////////////////////////////////////////////////////
// List "Properties" -- various picture properties
/////////////////////////////////////////////////////////
sheet = wb.Sheets.Add("Properties");
// associating hyperlink with the shape
sheet.Rows[1].Height = 2000;
sheet.Columns[1].Width = 3600;
picture = new XLPictureShape(spbImage);
picture.Hyperlink = "http://www.spb.ru/";
sheet[1, 1].Value = picture;
sheet[2, 1].Value = "hyperlink (click on the picture)";
// others view type
col = 1;
sheet.Rows[4].Height = 2000;
foreach (XLPictureViewType viewType in Enum.GetValues(typeof(XLPictureViewType)))
{
sheet.Columns[col].Width = 3600;
picture = new XLPictureShape(spbImage);
picture.ViewType = viewType;
sheet[4, col].Value = picture;
sheet[5, col].Value = "view type: " + viewType.ToString();;
col += 2;
}
// brightness & contrast
col = 1;
sheet.Rows[7].Height = sheet.Rows[10].Height = 2000;
for (int i = 0; i <= 100; i += 10)
{
sheet.Columns[col].Width = 3600;
picture = new XLPictureShape(spbImage);
picture.Brightness = (float)i / 100;
sheet[7, col].Value = picture;
sheet[8, col].Value = string.Format("brightness: {0}", picture.Brightness);
picture = new XLPictureShape(spbImage);
picture.Contrast = (float)i / 100;
sheet[10, col].Value = picture;
sheet[11, col].Value = string.Format("contrast: {0}", picture.Contrast);
col += 2;
}
// done
return(wb);
}
