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);
}
