//
// GET: /AttendanceTracker/
public ActionResult AttendanceTracker(string button)
{
_columnNames = new string[] { "Employee Name", "Supervisor", "Present Count", "Leave Count", "Absent Count", "Unplanned %", "Planned %" };
if (button == null)
{
return(View());
}
AttendanceDetailsGenerator attendanceDetailsGenerator = new AttendanceDetailsGenerator();
_employeeAttendanceList = attendanceDetailsGenerator.GetEmployeeAttendanceDetails(2019, 01);
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
#region Workbook Initialize
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.EnableIncrementalFormula = true;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
DateTime dateTime = new DateTime(2019, 01, 1);
string monthName = dateTime.ToString("MMM", CultureInfo.InvariantCulture);
worksheet.Name = monthName + "-" + 2019;
worksheet.Range["A1:AL1"].CellStyle.Font.Bold = true;
CreateHeaderRow(worksheet);//Format header row
FillAttendanceDetails(worksheet);
ApplyConditionFormatting(worksheet);
worksheet.UsedRange.AutofitColumns();
worksheet.Range["A2:B31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Range["C2:G31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
#endregion
try
{
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "AttendanceTracker.xlsx"));
}
catch (Exception)
{
}
// Close the workbook
workbook.Close();
excelEngine.Dispose();
return(View());
}
//
// GET: /AttendanceTracker/
public ActionResult AttendanceTracker(string button)
{
_columnNames = new string[] { "Employee Name", "Supervisor", "Present Count", "Leave Count", "Absent Count", "Unplanned %", "Planned %" };
if (button == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
AttendanceDetailsGenerator attendanceDetailsGenerator = new AttendanceDetailsGenerator();
_employeeAttendanceList = attendanceDetailsGenerator.GetEmployeeAttendanceDetails(2019, 01);
#region Workbook Initialize
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.EnableIncrementalFormula = true;
application.DefaultVersion = ExcelVersion.Excel2016;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
DateTime dateTime = DateTime.Now;
string monthName = dateTime.ToString("MMM", CultureInfo.InvariantCulture);
worksheet.Name = monthName + "-" + dateTime.Year;
CreateHeaderRow(worksheet);//Format header row
FillAttendanceDetails(worksheet);
ApplyConditionFormatting(worksheet);
#region Apply Styles
worksheet.Range["A1:AL1"].RowHeight = 24;
worksheet.Range["A2:AL31"].RowHeight = 20;
worksheet.Range["A1:B1"].ColumnWidth = 20;
worksheet.Range["C1:G1"].ColumnWidth = 16;
worksheet.Range["H1:AL31"].ColumnWidth = 4;
worksheet.Range["A1:AL31"].CellStyle.Font.Bold = true;
worksheet.Range["A1:AL31"].CellStyle.Font.Size = 12;
worksheet.Range["A2:AL31"].CellStyle.Font.RGBColor = Color.FromArgb(64, 64, 64);
worksheet.Range["A1:AL31"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
worksheet.Range["A1:AL1"].CellStyle.Font.Color = ExcelKnownColors.White;
worksheet.Range["A1:AL1"].CellStyle.Color = Color.FromArgb(58, 56, 56);
worksheet.Range["A1:B31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignLeft;
worksheet.Range["C2:G31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range["H1:AL31"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
worksheet.Range["A2:B31"].CellStyle.IndentLevel = 1;
worksheet.Range["A1:G1"].CellStyle.IndentLevel = 1;
worksheet.Range["A1:AL1"].BorderAround(ExcelLineStyle.Medium, Color.LightGray);
worksheet.Range["A1:AL1"].BorderInside(ExcelLineStyle.Medium, Color.LightGray);
worksheet.Range["A2:G31"].BorderAround(ExcelLineStyle.Medium, Color.LightGray);
worksheet.Range["A2:G31"].BorderInside(ExcelLineStyle.Medium, Color.LightGray);
worksheet.Range["H2:AL31"].BorderInside(ExcelLineStyle.Medium, ExcelKnownColors.White);
#endregion
#endregion
try
{
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "AttendanceTracker.xlsx"));
}
catch (Exception)
{
}
// Close the workbook
workbook.Close();
excelEngine.Dispose();
return(View());
}
