public void PaySlip_NSW_MYOB1_Excel_SalaryNaN() { ExcelPackage excelExport = new ExcelPackage(); var workSheetOutput = excelExport.Workbook.Worksheets.Add("PaySlips"); workSheetOutput.Cells[1, 1].Value = InputExcelColumn.FirstName; workSheetOutput.Cells[1, 2].Value = InputExcelColumn.LastName; workSheetOutput.Cells[1, 3].Value = InputExcelColumn.AnnualSalary; workSheetOutput.Cells[1, 4].Value = InputExcelColumn.SuperRate; workSheetOutput.Cells[1, 5].Value = InputExcelColumn.PayPeriod; workSheetOutput.Cells[2, 1].Value = "David"; workSheetOutput.Cells[2, 2].Value = "Rudd"; workSheetOutput.Cells[2, 3].Value = "Sixty Thousand"; workSheetOutput.Cells[2, 4].Value = 9; workSheetOutput.Cells[2, 5].Value = "01 March - 31 March"; var output = PaySlipWorker.GeneratePaySlipsExcel(excelExport, States.NSW); ExcelWorksheet workSheet = output.Workbook.Worksheets.First(); var maxColumnCount = workSheet.Dimension.End.Column; var row = workSheet.Cells[2, 1, 2, maxColumnCount]; var paySlipOutput = new EngineOutput() { Name = Convert.ToString(((object[, ])row.Value)[0, 0]), GrossIncome = Convert.ToDecimal(((object[, ])row.Value)[0, 1]), IncomeTax = Convert.ToDecimal(((object[, ])row.Value)[0, 2]), NetIncome = Convert.ToDecimal(((object[, ])row.Value)[0, 3]), Super = Convert.ToDecimal(((object[, ])row.Value)[0, 4]), PayPeriod = Convert.ToString(((object[, ])row.Value)[0, 5]) }; }
/// <summary> /// This event handles the generation of payslips /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnGenerate_Click(object sender, EventArgs e) { try { ErroMsg.Text = ""; string filecontent = Convert.ToBase64String(uploadFile.FileBytes); if (Path.GetExtension(uploadFile.FileName).Equals(".xlsx")) { var excel = new ExcelPackage(uploadFile.FileContent); var paySlips = PaySlipWorker.GeneratePaySlipsExcel(excel, ddlState.SelectedValue); string excelName = "PaySlips"; using (var memoryStream = new MemoryStream()) { Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment; filename=" + excelName + ".xlsx"); paySlips.SaveAs(memoryStream); memoryStream.WriteTo(Response.OutputStream); Response.Flush(); } } else { ErroMsg.Text = "Please select valid excel file."; } } catch (AggregateException ex) { foreach (var exp in ex.InnerExceptions) { CommonLogger.LogError(exp.InnerException != null ? exp.InnerException : exp); ErroMsg.Text = exp.InnerException != null ? exp.InnerException.Message : exp.Message; } CommonLogger.LogError(ex); ErroMsg.Text = ErroMsg.Text + "\n" + ex.Message; } catch (Exception ex) { CommonLogger.LogError(ex); ErroMsg.Text = ex.Message; } finally { uploadFile.Dispose(); if (Response.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") { Response.End(); } } }
public void PaySlip_NSW_MYOB2_Excel() { ExcelPackage excelExport = new ExcelPackage(); var workSheetOutput = excelExport.Workbook.Worksheets.Add("PaySlips"); workSheetOutput.Cells[1, 1].Value = InputExcelColumn.FirstName; workSheetOutput.Cells[1, 2].Value = InputExcelColumn.LastName; workSheetOutput.Cells[1, 3].Value = InputExcelColumn.AnnualSalary; workSheetOutput.Cells[1, 4].Value = InputExcelColumn.SuperRate; workSheetOutput.Cells[1, 5].Value = InputExcelColumn.PayPeriod; workSheetOutput.Cells[2, 1].Value = "Ryan"; workSheetOutput.Cells[2, 2].Value = "Chen"; workSheetOutput.Cells[2, 3].Value = 120000; workSheetOutput.Cells[2, 4].Value = 10; workSheetOutput.Cells[2, 5].Value = "01 March - 31 March"; var output = PaySlipWorker.GeneratePaySlipsExcel(excelExport, States.NSW); ExcelWorksheet workSheet = output.Workbook.Worksheets.First(); var maxColumnCount = workSheet.Dimension.End.Column; var row = workSheet.Cells[2, 1, 2, maxColumnCount]; var paySlipOutput = new EngineOutput() { Name = Convert.ToString(((object[, ])row.Value)[0, 0]), GrossIncome = Convert.ToDecimal(((object[, ])row.Value)[0, 1]), IncomeTax = Convert.ToDecimal(((object[, ])row.Value)[0, 2]), NetIncome = Convert.ToDecimal(((object[, ])row.Value)[0, 3]), Super = Convert.ToDecimal(((object[, ])row.Value)[0, 4]), PayPeriod = Convert.ToString(((object[, ])row.Value)[0, 5]) }; Assert.IsTrue(paySlipOutput.Name.Equals("Ryan Chen") && paySlipOutput.GrossIncome == 10000 && paySlipOutput.IncomeTax == 2669 && paySlipOutput.NetIncome == 7331 && paySlipOutput.Super == 1000); }