//
        // 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());
        }
Exemplo n.º 2
0
        //
        // 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());
        }