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