How to create advanced excel spreadsheets without using Microsoft Office dlls?

In this blog article I will discuss how to create advanced excel spreadsheets without using Microsoft Office dlls(microsoft interop excel). I am using here EPPlus .net library to create Excel sheets. Let's start how to create.


Step 1: Download EPPlus Library from  http://epplus.codeplex.com/ & integrate its refrence with Project.

Step 2:  Excel Generation Code for calculate attendance. 

    #region--Generate Attendace HTML--
        public string CreateAttendanceExcel()
        {
            DateTime StartDate =DateTime.Now.AddDays(-31);
            DateTime EndDate = DateTime.Now;
            List<UserEntity> UserList =GetUserData(); //Get User Data
            List<AttendanceEntity> AttendanceList = GetAttendanceData(); //Get Attendance Data
            string FileName =  "Attendance-Report" + DateTime.Now.ToString("dd-MM-yyyy--hh-mm-ss") + ".xlsx";
            TimeSpan TimeDiffrence = new TimeSpan(0, 0, 0); ;
            if (ConfigurationManager.AppSettings["TimezoneOffset"] != null)
            {
                int miniouts = int.Parse(ConfigurationManager.AppSettings["TimezoneOffset"].ToString());
                TimeDiffrence = new TimeSpan(0, miniouts, 0);

            }
            string outputDir = AppDomain.CurrentDomain.BaseDirectory + "OutPutFiles\\";
            if (!Directory.Exists(outputDir))
            {
                Directory.CreateDirectory(outputDir);
            }
            string filepath = outputDir + FileName;
            // Create the file using the FileInfo object
            var file = new FileInfo(filepath);

            // Create the package and make sure you wrap it in a using statement
            using (var package = new ExcelPackage(file))
            {
                int TotalColumns = (EndDate - StartDate).Days + 3;
                ExcelWorksheet ws = package.Workbook.Worksheets.Add("Attendance - " + DateTime.Now.ToShortDateString());
                ws.Cells.Style.Font.Size = 11; //Default font size for whole sheet               

                int RowNum = 1;
                int ColNum = 1;
                //Merging cells and create a center heading for out table
                ws.Cells[RowNum, ColNum].Value = "Attendance Report(" + StartDate.ToString("dd'-'MMM'-'yyyy") + " to " + EndDate.ToString("dd'-'MMM'-'yyyy") + ")"; // Heading Name
                ws.Cells[RowNum, ColNum, RowNum, TotalColumns].Merge = true; //Merge columns start and end range               
                ws.Cells[RowNum, ColNum, RowNum, TotalColumns].Style.Font.Size = 30; //Font should be bold                 
                ws.Cells[RowNum, ColNum, RowNum, TotalColumns].Style.Font.Bold = true; //Font should be bold                  
                ws.Cells[RowNum, ColNum, RowNum, TotalColumns].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
                RowNum++;

                //Set Up Headers

                ws.Cells[RowNum, ColNum].Value = "Name"; // Heading Name
                ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Merge = true; //Merge columns start and end range
                ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Style.Fill.PatternType = ExcelFillStyle.Solid;
                ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Style.Fill.BackgroundColor.SetColor(Color.Black);
                ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Style.Font.Bold = true; //Font should be bold    
                ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Style.Font.Color.SetColor(Color.White);
                ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

                ws.Cells[RowNum, ColNum + 1].Value = "Duration"; // Heading Name
                ws.Cells[RowNum, ColNum + 1, RowNum + 1, ColNum + 1].Merge = true; //Merge columns start and end range
                ws.Cells[RowNum, ColNum + 1, RowNum + 1, ColNum + 1].Style.Fill.PatternType = ExcelFillStyle.Solid;
                ws.Cells[RowNum, ColNum + 1, RowNum + 1, ColNum + 1].Style.Fill.BackgroundColor.SetColor(Color.Black);
                ws.Cells[RowNum, ColNum + 1, RowNum + 1, ColNum + 1].Style.Font.Bold = true; //Font should be bold    
                ws.Cells[RowNum, ColNum + 1, RowNum + 1, ColNum + 1].Style.Font.Color.SetColor(Color.White);
                ws.Cells[RowNum, ColNum + 1, RowNum + 1, ColNum + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

                int j = 1;
                for (DateTime i = StartDate; i <= EndDate; i = i.AddDays(1))
                {
                    j++;
                    ws.Cells[RowNum, ColNum + j].Value = i.Day; // Heading Name
                    ws.Cells[RowNum, ColNum + j].Style.Fill.PatternType = ExcelFillStyle.Solid;
                    ws.Cells[RowNum, ColNum + j].Style.Fill.BackgroundColor.SetColor(Color.Black);
                    ws.Cells[RowNum, ColNum + j].Style.Font.Bold = true; //Font should be bold    
                    ws.Cells[RowNum, ColNum + j].Style.Font.Color.SetColor(Color.White);
                    ws.Cells[RowNum, ColNum + j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

                    ws.Cells[RowNum + 1, ColNum + j].Value = i.ToString("ddd"); // Heading Name
                    ws.Cells[RowNum + 1, ColNum + j].Style.Fill.PatternType = ExcelFillStyle.Solid;
                    ws.Cells[RowNum + 1, ColNum + j].Style.Fill.BackgroundColor.SetColor(Color.Black);
                    ws.Cells[RowNum + 1, ColNum + j].Style.Font.Bold = true; //Font should be bold    
                    ws.Cells[RowNum + 1, ColNum + j].Style.Font.Color.SetColor(Color.White);
                    ws.Cells[RowNum + 1, ColNum + j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center                   
                }
                //End Set Up Headers
                RowNum = RowNum + 2;

                foreach (var user in UserList)
                {

                    ws.Cells[RowNum, ColNum].Value = user.Name;// Heading Name
                    ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Merge = true; //Merge columns start and end range
                    ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Style.Font.Bold = true; //Font should be bold   
                    ws.Cells[RowNum, ColNum, RowNum + 1, ColNum].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

                    ws.Cells[RowNum, ColNum + 1].Value = "PunchIn";// Heading Name                   
                    ws.Cells[RowNum, ColNum + 1].Style.Font.Bold = true; //Font should be bold   
                    ws.Cells[RowNum, ColNum + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center

                    ws.Cells[RowNum + 1, ColNum + 1].Value = "PunchOut";// Heading Name                   
                    ws.Cells[RowNum + 1, ColNum + 1].Style.Font.Bold = true; //Font should be bold   
                    ws.Cells[RowNum + 1, ColNum + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
                    j = 1;
                    for (DateTime i = StartDate; i <= EndDate; i = i.AddDays(1))
                    {
                        j++;
                        string PunchIn = "";
                        try
                        {
                            PunchIn = ((DateTime)AttendanceList.Where(a => a.UserId == user.UserId && a.PunchIn >= CommonHelper.Instance.GetStartTimeOfDay(i) && a.PunchOut <= CommonHelper.Instance.GetEndTimeOfDay(i)).FirstOrDefault().PunchIn).Subtract(TimeDiffrence).ToShortTimeString();
                            ws.Cells[RowNum, ColNum + j].Value = PunchIn; // Heading Name                            
                            ws.Cells[RowNum, ColNum + j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
                        }
                        catch
                        {
                            if (((int)i.DayOfWeek) == 6 || ((int)i.DayOfWeek) == 0)
                            {
                                PunchIn = "x";
                                ws.Cells[RowNum, ColNum + j].Value = PunchIn; // Heading Name
                                ws.Cells[RowNum, ColNum + j].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                ws.Cells[RowNum, ColNum + j].Style.Fill.BackgroundColor.SetColor(Color.Green);
                                ws.Cells[RowNum, ColNum + j].Style.Font.Bold = true; //Font should be bold    
                                ws.Cells[RowNum, ColNum + j].Style.Font.Color.SetColor(Color.Black);
                                ws.Cells[RowNum, ColNum + j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
                            }
                            else
                            {
                                PunchIn = "NA";
                                ws.Cells[RowNum, ColNum + j].Value = PunchIn; // Heading Name
                                ws.Cells[RowNum, ColNum + j].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                ws.Cells[RowNum, ColNum + j].Style.Fill.BackgroundColor.SetColor(Color.Red);
                                ws.Cells[RowNum, ColNum + j].Style.Font.Bold = true; //Font should be bold    
                                ws.Cells[RowNum, ColNum + j].Style.Font.Color.SetColor(Color.Black);
                                ws.Cells[RowNum, ColNum + j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
                            }
                        }
                    }

                    j = 1;
                    for (DateTime i = StartDate; i <= EndDate; i = i.AddDays(1))
                    {
                        j++;
                        string PunchOut = "";
                        try
                        {
                            PunchOut = ((DateTime)AttendanceList.Where(a => a.UserId == user.UserId && a.PunchIn >= CommonHelper.Instance.GetStartTimeOfDay(i) && a.PunchOut <= CommonHelper.Instance.GetEndTimeOfDay(i)).FirstOrDefault().PunchOut).Subtract(TimeDiffrence).ToShortTimeString();
                            ws.Cells[RowNum + 1, ColNum + j].Value = PunchOut; // Heading Name                            
                            ws.Cells[RowNum + 1, ColNum + j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
                        }
                        catch
                        {
                            if (((int)i.DayOfWeek) == 6 || ((int)i.DayOfWeek) == 0)
                            {
                                PunchOut = "x";
                                ws.Cells[RowNum + 1, ColNum + j].Value = PunchOut; // Heading Name
                                ws.Cells[RowNum + 1, ColNum + j].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                ws.Cells[RowNum + 1, ColNum + j].Style.Fill.BackgroundColor.SetColor(Color.Green);
                                ws.Cells[RowNum + 1, ColNum + j].Style.Font.Bold = true; //Font should be bold    
                                ws.Cells[RowNum + 1, ColNum + j].Style.Font.Color.SetColor(Color.Black);
                                ws.Cells[RowNum + 1, ColNum + j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
                            }
                            else
                            {
                                PunchOut = "NA";
                                ws.Cells[RowNum + 1, ColNum + j].Value = PunchOut; // Heading Name
                                ws.Cells[RowNum + 1, ColNum + j].Style.Fill.PatternType = ExcelFillStyle.Solid;
                                ws.Cells[RowNum + 1, ColNum + j].Style.Fill.BackgroundColor.SetColor(Color.Red);
                                ws.Cells[RowNum + 1, ColNum + j].Style.Font.Bold = true; //Font should be bold    
                                ws.Cells[RowNum + 1, ColNum + j].Style.Font.Color.SetColor(Color.Black);
                                ws.Cells[RowNum + 1, ColNum + j].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; // Aligmnet is center
                            }
                        }
                    }

                    RowNum = RowNum + 2;
                }
                ws.Column(1).Width = 40;
                ws.Column(2).Width = 15;
                // save our new workbook and we are done!
                package.Save();
            }
            return filepath;
        }
        #endregion

Please review above code to understand excel file generation. Please check below screen shot of generated excel after calling this method.