/// <summary> /// Create the Excel-File with all devices /// </summary> public FileInfo CreateSingleCarSheet(Statistic statistic) { bool isPlanning = false; var filename = "Auswertung_" + statistic.Car.Description + statistic.Car.Radio + "_" + DateTime.Now.ToString("d") + ".xlsx"; if (string.IsNullOrWhiteSpace(statistic.Creator)) { statistic.Creator = "Luca Marti"; } var response = new FileInfo(filename); using (var package = new ExcelPackage(response)) { var worksheet = package.Workbook.Worksheets.Add("Fahrzeugstatistik"); SetTemplate(worksheet, isPlanning); var allEntities = _planningService.GetPlannedPlannings(statistic.StartDate, statistic.EndDate).Where(x => x.Car.Id == statistic.Car.Id).OrderByDescending(x => x.EndTime); var countPlannings = allEntities.Count(); //Add the header informations //First row // COL / ROW worksheet.Cells[1, 1].Style.Font.Size = 18; worksheet.Cells[1, 1].Style.Font.Bold = true; worksheet.Cells[1, 1].Value = "Auswertung - " + statistic.Car.Description + " " + statistic.Car.Radio; worksheet.Cells[2, 1].Style.Font.Size = 14; worksheet.Cells[2, 1].Value = statistic.StartDate.ToString("dd.MM.yyyy") + " - " + statistic.EndDate.ToString("dd.MM.yyyy"); worksheet.Cells[5, 1].Value = "Erstelldatum:"; worksheet.Cells[5, 2].Value = DateTime.Now.ToString("dd.MM.yyyy H:mm"); worksheet.Cells[6, 1].Value = "Ersteller:"; worksheet.Cells[6, 2].Value = statistic.Creator; //Second row worksheet.Cells[8, 1].Style.Font.Bold = true; worksheet.Cells[8, 1].Value = "Anzahl Einträge:"; worksheet.Cells[8, 2].Style.Font.Bold = true; worksheet.Cells[8, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; worksheet.Cells[8, 2].Value = countPlannings; var col = 9; foreach (var state in _stateService.GetAll()) { worksheet.Cells[col, 1].Value = state.Name + ":"; worksheet.Cells[col, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; worksheet.Cells[col, 2].Value = _planningService.GetCountPlannedPlanningsByState(allEntities, state.Name); col++; } col = col + 2; var tableHeader = col; //Add the content-headers worksheet.Cells[tableHeader, 1].Value = "Startdatum"; worksheet.Cells[tableHeader, 2].Value = "Enddatum"; worksheet.Cells[tableHeader, 3].Value = "Status"; foreach (var planning in allEntities) { col++; worksheet.Cells[col, 1].Value = planning.StartTime.ToString("dd.MM.yyyy"); worksheet.Cells[col, 2].Value = planning.EndTime.ToString("dd.MM.yyyy"); worksheet.Cells[col, 3].Value = planning.State.Name; } //Format the Header using (var range = worksheet.Cells[4, 1, 4, 3]) { range.Style.Border.Top.Style = ExcelBorderStyle.Medium; } var borderEnd = tableHeader - 2; using (var range = worksheet.Cells[borderEnd, 1, borderEnd, 3]) { range.Style.Border.Bottom.Style = ExcelBorderStyle.Medium; } // Format the List-Header using (var range = worksheet.Cells[tableHeader, 1, tableHeader, 3]) { range.Style.Font.Bold = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(_red); range.Style.Font.Color.SetColor(Color.White); range.AutoFilter = true; } //Set property values package.Workbook.Properties.Subject = "Fahrzeugstatistik"; package.Workbook.Properties.Title = "Fahrzeugstatistik"; //Set extended property values package.Workbook.Properties.Company = "Verwaltungspolizei Stadtpolizei Uster"; //this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //this.Response.AddHeader( // "content-disposition", // string.Format("attachment; filename={0}", filename)); //this.Response.BinaryWrite(package.GetAsByteArray()); } return(response); }