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