public DateCell(ColumnId id, DateTime dateTime, uint index)
     : this(id.ToString(), dateTime, index)
 {
 }
        public static void Create(DateTime date)
        {
            var Months = new List<string> { "Januari", "Februari", "Maart", "April",
                                                                            "Mei", "Juni", "Juli", "Augustus",
                                                                            "September", "Oktober", "November", "December" };
            var Days = new List<string> { "Zondag", "Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag" };
            var weekdays = GetDays(date).ToList();

            int month = date.Month;
            int year = date.Year;

            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Aanwezigheid");

            ws.Style.Font.FontName = "Calibri";
            ws.Style.Font.FontSize = 10;

            var color = XLColor.FromArgb(0, 51, 102);
            var cell = ws.Cell(1, "D");

            cell.Value = "Aanwezigheidregister";
            cell.Style.Font.FontSize = 14;
            cell.Style.Font.FontColor = color;
            cell.Style.Font.Bold = true;

            cell = ws.Cell(1, "M");
            cell.Value = Months[month - 1];
            cell.Style.Font.FontSize = 14;
            cell.Style.Font.FontColor = color;
            cell.Style.Font.Bold = true;

            cell = ws.Cell(1, "P");
            cell.Value = string.Format("'{0}", year);
            cell.Style.Font.FontSize = 14;
            cell.Style.Font.FontColor = color;
            cell.Style.Font.Bold = true;

            ColumnId colid = new ColumnId('D');
            uint columns = 0;
            int dayCol = 4;

            foreach (var day in weekdays)
            {
                var daycell = ws.Range(2, dayCol, 2, dayCol + 2).Merge();
                daycell.Value = string.Format("'{0} {1}", Days[(int)(day.Date.DayOfWeek)], day.Date.ToShortDateString());

                daycell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                daycell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                Enbox(daycell.Style);

                cell = ws.Cell(3, colid.ToString());
                cell.Value = "uur";
                Enbox(cell.Style);
                colid.Increment();

                cell = ws.Cell(3, colid.ToString());
                cell.Value = "wie";
                Enbox(cell.Style);
                colid.Increment();

                cell = ws.Cell(3, colid.ToString());
                cell.Value = "paraaf";
                Enbox(cell.Style);
                colid.Increment();

                dayCol += 3;
                columns++;
            }

            int rowIndex = 4;
            uint childIndex = 1;

            var startDate = weekdays.Min(d => d.Date);
            var endDate = weekdays.Max(d => d.Date);

            using (var db = new PetoeterDb(PetoeterDb.FileName))
            {
                var presenceData = db.Presences.Find(p => p.Date >= startDate.Date && p.Date <= endDate.Date).ToList();

                if (presenceData != null && presenceData.Count != 0)
                {
                    var presences = from p in presenceData
                                                    orderby p.Child.BirthDay
                                                    group p by p.Child.Id into presencegroup
                                                    //orderby presencegroup.Key.BirthDay
                                                    select presencegroup;
                    var count = presences.Count();
                    var rowSize = count > 30 ? 24.75 : 27.0;

                    foreach (var presence in presences)
                    {
                        var nr = ws.Range(rowIndex, 1, rowIndex + 1, 1).Merge();
                        nr.Value = string.Format("{0}.", childIndex);
                        nr.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        nr.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        Enbox(nr.Style);

                        var cname = ws.Range(rowIndex, 2, rowIndex + 1, 2).Merge();
                        cname.Value = presence.First().Child.GetFullName();
                        //Ch.GetFullName();		//	string.Format("{0} {1}", child.LastName, child.FirstName);
                        cname.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        cname.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        cname.Style.Alignment.WrapText = true;
                        Enbox(cname.Style);

                        cell = ws.Cell(rowIndex, "C");
                        cell.Value = "Aankomst";
                        Enbox(cell.Style);
                        ws.Row(rowIndex).Height = rowSize;

                        cell = ws.Cell(rowIndex + 1, "C");
                        cell.Value = "Vertrek";
                        Enbox(cell.Style);
                        ws.Row(rowIndex + 1).Height = rowSize;

                        foreach (var day in from p in presence orderby p.Date select p)
                        {
                            int dateindex = (day.Date - startDate).Days;

                            if (day.BroughtBy != null)
                            {
                                colid = new ColumnId('D');
                                colid.Increment(dateindex * 3);

                                cell = ws.Cell(rowIndex, colid.ToString());
                                cell.SetValue<string>(day.BroughtAt.ToString("HH:MM"));

                                colid.Increment(1);
                                cell = ws.Cell(rowIndex, colid.ToString());
                                cell.Value = day.BroughtBy.GetFullName();
                            }

                            if (day.TakenBy != null)
                            {
                                colid = new ColumnId('D');
                                colid.Increment(dateindex * 3);

                                cell = ws.Cell(rowIndex + 1, colid.ToString());
                                cell.SetValue<string>(day.TakenAt.ToString("HH:MM"));

                                colid.Increment(1);
                                cell = ws.Cell(rowIndex + 1, colid.ToString());
                                cell.Value = day.TakenBy.GetFullName();
                            }
                        }

                        childIndex++;
                        rowIndex += 2;
                    }
                }
                else
                {
                    //var query = from c in db.Children.FindAll()
                    //						orderby c.BirthDay descending
                    //						select c;

                    var children = from c in db.Children.FindAll()
                                                 where c.Schedule.Any(d => weekdays.Any(wd => wd.Date == d.Day))
                                                 orderby c.BirthDay ascending
                                                 select c;

                    var rowSize = children.Count() > 30 ? 24.75 : 27.0;

                    foreach (var child in children)
                    {
                        var nr = ws.Range(rowIndex, 1, rowIndex + 1, 1).Merge();
                        nr.Value = string.Format("{0}.", childIndex);
                        nr.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        nr.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        Enbox(nr.Style);

                        var cname = ws.Range(rowIndex, 2, rowIndex + 1, 2).Merge();
                        cname.Value = string.Format("{0} {1}", child.LastName, child.FirstName);
                        cname.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                        cname.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
                        cname.Style.Alignment.WrapText = true;
                        Enbox(cname.Style);

                        cell = ws.Cell(rowIndex, "C");
                        cell.Value = "Aankomst";
                        Enbox(cell.Style);
                        ws.Row(rowIndex).Height = rowSize;

                        cell = ws.Cell(rowIndex + 1, "C");
                        cell.Value = "Vertrek";
                        Enbox(cell.Style);
                        ws.Row(rowIndex + 1).Height = rowSize;

                        colid = new ColumnId('D');
                        for (int index = 0; index < 15; index++)
                        {
                            cell = ws.Cell(rowIndex, colid.ToString());
                            Enbox(cell.Style);
                            cell = ws.Cell(rowIndex + 1, colid.ToString());
                            Enbox(cell.Style);

                            colid.Increment();
                        }

                        childIndex++;
                        rowIndex += 2;
                    }
                }
            }

            //var model = ServiceProvider.Instance.GetService<Petoeter>();
            //var thisWeek = DatePeriod.MakePeriod(month, year);

            var mergedcell = ws.Range(2, 1, 3, 3).Merge();
            mergedcell.Value = "Naam";
            mergedcell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            mergedcell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
            mergedcell.Style.Border.LeftBorder = XLBorderStyleValues.Thin;
            mergedcell.Style.Border.RightBorder = XLBorderStyleValues.Thin;
            mergedcell.Style.Border.TopBorder = XLBorderStyleValues.Thin;
            mergedcell.Style.Border.BottomBorder = XLBorderStyleValues.Thin;

            //	styling
            ws.Column(1).Width = 2.0;
            ws.Column(2).Width = 13.0;
            ws.Column(3).Width = 6.43;

            ws.Column(3).Style.Font.FontSize = 8;

            for (int day = 0; day < 5; day++)
            {
                ws.Column(4 + (day * 3)).Width = 5.43;
                ws.Column((int)(4 + (day * 3) + 1)).Width = 10.14;
                ws.Column((int)(4 + (day * 3) + 2)).Width = 5.43;
            }

            ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
            ws.PageSetup.PaperSize = XLPaperSize.A4Paper;
            ws.PageSetup.VerticalDpi = 600;
            ws.PageSetup.HorizontalDpi = 600;

            ws.PageSetup.Margins.Bottom = 0;
            ws.PageSetup.Margins.Top = 0;
            ws.PageSetup.Margins.Left = 0.25;
            ws.PageSetup.Margins.Right = 0.25;

            ws.PageSetup.SetColumnsToRepeatAtLeft(1, 3);
            ws.PageSetup.SetRowsToRepeatAtTop(1, 3);

            var file = GetTempFilePathWithExtension("xlsx");
            wb.SaveAs(file);

            System.Diagnostics.Process.Start(file);
        }