private void CreateBill(Employee employee, string targetFolderPath)
{
using (var db = new EmployeesDBContext())
{
var toExport = db.Employees.SingleOrDefault(em => em.ID == employee.ID);
if (toExport != null)
{
EmployeeSalaryBill salaryBill = new EmployeeSalaryBill(DateTime.Now, toExport);
if (targetFolderPath.Substring(targetFolderPath.Length - 1) == "\\")
{
targetFolderPath += "Bill_" + toExport.Name + "_" + salaryBill.Date.ToString().Replace("/", "_").Replace(":", "_") + ".xlsx";
}
else
{
targetFolderPath += "\\Bill_" + toExport.Name + "_" + salaryBill.Date.ToString().Replace("/", "_").Replace(":", "_") + ".xlsx";
}
SalaryBillToExcel.WriteSalaryBillToExcel(salaryBill, targetFolderPath);
db.EmployeeSalaryBills.Add(salaryBill);
db.SaveChanges();
}
}
}
public static void WriteSalaryBillToExcel(EmployeeSalaryBill salaryBill, String path)
{
try
{
Application Excel = new Application
{
Visible = false,
DisplayAlerts = false
};
Workbook WorkBook = Excel.Workbooks.Add(Type.Missing);
Worksheet WorkSheet = (Worksheet)WorkBook.ActiveSheet;
WorkSheet.Name = salaryBill.Employee.Name;
WorkSheet.Range[WorkSheet.Cells[1, 1], WorkSheet.Cells[1, 8]].Merge();
WorkSheet.Cells[1, 1] = "Фиш за работна заплата";
WorkSheet.Cells[1, 1].Font.Size = 15;
WorkSheet.Range[WorkSheet.Cells[3, 1], WorkSheet.Cells[5, 2]].BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin);
WorkSheet.Cells[3, 1] = "Фирма: ";
WorkSheet.Cells[3, 1] = salaryBill.Employee.CompanyName;
WorkSheet.Cells[4, 1] = "ЕГН: ";
WorkSheet.Cells[4, 2] = salaryBill.Employee.EGN;
WorkSheet.Cells[5, 1] = "Име: ";
WorkSheet.Cells[5, 2] = salaryBill.Employee.Name;
WorkSheet.Cells[3, 7] = "Дата: ";
WorkSheet.Cells[3, 8] = salaryBill.Date;
WorkSheet.Range[WorkSheet.Cells[7, 1], WorkSheet.Cells[8, 2]].BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin);
WorkSheet.Cells[7, 1] = "Брутно тр. възнаграждение: ";
WorkSheet.Cells[7, 2] = salaryBill.Employee.Salary;
WorkSheet.Cells[8, 1] = "Данъчна основа: ";
WorkSheet.Cells[8, 2] = salaryBill.DanychnaOsnova;
WorkSheet.Range[WorkSheet.Cells[10, 1], WorkSheet.Cells[16, 3]].BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin);
WorkSheet.Range[WorkSheet.Cells[10, 1], WorkSheet.Cells[10, 3]].Borders.LineStyle = XlLineStyle.xlContinuous;
WorkSheet.Cells[10, 1] = "Удръжки";
WorkSheet.Cells[10, 2] = "Мярка %";
WorkSheet.Cells[10, 3] = "Сума";
WorkSheet.Cells[11, 1] = "ДДФЛ";
WorkSheet.Cells[11, 2] = Constants.DDFL_PERCENTAGE;
WorkSheet.Cells[11, 3] = salaryBill.DDFL;
WorkSheet.Cells[12, 1] = "ДЗПО в УПФ";
WorkSheet.Cells[12, 2] = Constants.DZPO_PERCENTAGE;
WorkSheet.Cells[12, 3] = salaryBill.DZPO;
WorkSheet.Cells[13, 1] = "ЗО";
WorkSheet.Cells[13, 2] = Constants.ZO_PERCENTAGE;
WorkSheet.Cells[13, 3] = salaryBill.ZO;
WorkSheet.Cells[14, 1] = "ДОО Пенсии";
WorkSheet.Cells[14, 2] = Constants.DOO_PENSII_PERCENTAGE;
WorkSheet.Cells[14, 3] = salaryBill.DOO_PENSII;
WorkSheet.Cells[15, 1] = "ДОО ОЗМ";
WorkSheet.Cells[15, 2] = Constants.DOO_ZOM_PERCENTAGE;
WorkSheet.Cells[15, 3] = salaryBill.DOO_ZOM;
WorkSheet.Cells[16, 1] = "ДОО Безработица";
WorkSheet.Cells[16, 2] = Constants.DOO_BEZRABOTICA_PERCENTAGE;
WorkSheet.Cells[16, 3] = salaryBill.DOO_BEZRABOTICA;
WorkSheet.Range[WorkSheet.Cells[17, 1], WorkSheet.Cells[18, 3]].BorderAround(XlLineStyle.xlContinuous, XlBorderWeight.xlThin);
WorkSheet.Range[WorkSheet.Cells[17, 1], WorkSheet.Cells[17, 2]].Merge();
WorkSheet.Cells[17, 1] = "Всико удръжки: ";
WorkSheet.Cells[17, 3] = (salaryBill.Employee.Salary - salaryBill.NetSalary).ToString();
WorkSheet.Range[WorkSheet.Cells[18, 1], WorkSheet.Cells[18, 2]].Merge();
WorkSheet.Cells[18, 1] = "Сума за получаване: ";
WorkSheet.Cells[18, 3] = salaryBill.NetSalary.ToString();
WorkSheet.Columns.AutoFit();
WorkSheet.Rows.AutoFit();
WorkBook.SaveAs(path);
Excel.Quit();
}
catch (Exception ex)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, ex.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, ex.Source);
System.Windows.MessageBox.Show(errorMessage, "Error");
}
finally
{
Excel = null;
}
}