private RegulatedHoursDetail GetRegulationRuleAtEventTime(SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent currentReportableEvent) { RegulatedHoursDetail result = null; // Try to obtain the regulated hours applicable to this meter RegulatedHoursGroup regulatedHours = this._ReportEngine.GetBestGroupForMeter(this._CustomerConfig.CustomerId, currentReportableEvent.BayInfo.AreaID_PreferLibertyBeforeInternal, currentReportableEvent.BayInfo.MeterID); // If no regulated hour defintions came back, then we are unable to calculate any overstay violation, so just exit if ((regulatedHours == null) || (regulatedHours.Details == null) || (regulatedHours.Details.Count == 0)) { return(result); } DateTime ruleStart = DateTime.MinValue; DateTime ruleEnd = DateTime.MinValue; TimeSlot OccupiedSegment = new TimeSlot(currentReportableEvent.SensorEvent_Start, currentReportableEvent.SensorEvent_End); // We need to check if this single occupancy event is an overstay violation for multiple rules, or even for more than one day, etc. while (OccupiedSegment.Start < currentReportableEvent.SensorEvent_End) { // Determine the day of week that is involved int dayOfWeek = (int)OccupiedSegment.Start.DayOfWeek; // Loop through the daily rules and see which ones overlap with our occupied period foreach (RegulatedHoursDetail detail in regulatedHours.Details) { // Skip this one if its for a different day of the week if (detail.DayOfWeek != dayOfWeek) { continue; } // Determine if the occupied timeslot overlaps with the rule's timeslot ruleStart = new DateTime(OccupiedSegment.Start.Year, OccupiedSegment.Start.Month, OccupiedSegment.Start.Day, detail.StartTime.Hour, detail.StartTime.Minute, 0); ruleEnd = new DateTime(OccupiedSegment.Start.Year, OccupiedSegment.Start.Month, OccupiedSegment.Start.Day, detail.EndTime.Hour, detail.EndTime.Minute, 59); TimeSlot RuleSegment = new TimeSlot(ruleStart, ruleEnd); // We only care about this overlapping rule if the MaxStayMinutes is greater than zero (zero or less means there is no MaxStay that is enforced), // or if it's explicitly set as a "No Parking" regulation if ((RuleSegment.OverlapsWith(OccupiedSegment) == true) && ((detail.MaxStayMinutes > 0) || (string.Compare(detail.Type, "No Parking", true) == 0)) ) { result = detail; return(result); } } // Nothing more to do, so break out of loop! break; } return(result); }
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result) { timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None; // Start diagnostics timer System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName); // Now gather and analyze data for the report SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements(); requiredDataElements.NeedsSensorData = true; requiredDataElements.NeedsPaymentData = false; requiredDataElements.NeedsOverstayData = true; requiredDataElements.NeedsEnforcementActionData = true; this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, this._ReportParams); this._ReportEngine.GatherReportData(listOfMeterIDs, requiredDataElements, result); OfficeOpenXml.ExcelWorksheet ws = null; using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage()) { // Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets ws = pck.Workbook.Worksheets.Add("Details"); // Render the standard report title lines rowIdx = 1; // Excel uses 1-based indexes colIdx = 1; RenderCommonReportTitle(ws, this._ReportName); // Render common report header for enforcement activity restriction filter, but only if its not for all activity if (this._ReportParams.ActionTakenRestrictionFilter != SensorAndPaymentReportEngine.ReportableEnforcementActivity.AllActivity) { rowIdx++; colIdx = 1; RenderCommonReportFilterHeader_ActionTakenRestrictions(ws); } // Render common report header for regulated hour restriction filter rowIdx++; colIdx = 1; RenderCommonReportFilterHeader_RegulatedHourRestrictions(ws); using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, numColumnsMergedForHeader]) { rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228) } rowIdx++; rowIdx++; colIdx = 1; int detailsStartRow = rowIdx; int detailColumnCount = 29; // Render the header row ws.SetValue(rowIdx, 1, "Space #"); ws.SetValue(rowIdx, 2, "Meter #"); ws.SetValue(rowIdx, 3, "Area #"); ws.SetValue(rowIdx, 4, "Area"); ws.SetValue(rowIdx, 5, "Event Timestamp"); ws.SetValue(rowIdx, 6, "Record Timestamp"); ws.SetValue(rowIdx, 7, "Latency"); ws.SetValue(rowIdx, 8, "Occupied"); ws.SetValue(rowIdx, 9, "Vacant Duration"); ws.SetValue(rowIdx, 10, "Time Arrived"); ws.SetValue(rowIdx, 11, "Time Departed"); ws.SetValue(rowIdx, 12, "Parked Duration"); ws.SetValue(rowIdx, 13, "Max Stay Regulation"); ws.SetValue(rowIdx, 14, "Overstay Violation"); ws.SetValue(rowIdx, 15, "Overstay Duration"); ws.SetValue(rowIdx, 16, "Overstay (0-15min)"); ws.SetValue(rowIdx, 17, "Overstay (15-30min)"); ws.SetValue(rowIdx, 18, "Overstay (30-60min)"); ws.SetValue(rowIdx, 19, "Overstay (>60min)"); ws.SetValue(rowIdx, 20, "Violation Actioned"); ws.SetValue(rowIdx, 21, "Violation Issued"); ws.SetValue(rowIdx, 22, "Violation Warning"); ws.SetValue(rowIdx, 23, "Violation Not Issued"); ws.SetValue(rowIdx, 24, "Violation Fault"); ws.SetValue(rowIdx, 25, "Violation Missed"); ws.SetValue(rowIdx, 26, "Capture Rate (0-15min)"); ws.SetValue(rowIdx, 27, "Capture Rate (15-30min)"); ws.SetValue(rowIdx, 28, "Capture Rate (30-60min)"); ws.SetValue(rowIdx, 29, "Capture Rate (>60min)"); // Format the header row using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 1, detailsStartRow, 4]) { rng.Style.Font.Bold = true; rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); //Set color to dark blue rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 5, detailsStartRow, 8]) { rng.Style.Font.Bold = true; rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(36, 64, 98)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 9, detailsStartRow, 13]) { rng.Style.Font.Bold = true; rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(0, 176, 80)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow, 14, detailsStartRow, 29]) { rng.Style.Font.Bold = true; rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(150, 54, 52)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } // Increment the row index, which will now be the 1st row of our data rowIdx++; detailsStartRow = rowIdx; timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None; GroupStats baseTotalStats = this._ReportEngine.GetOverallStats(timeIsolation); ParkingAndOverstayGroupStats totalStats = new ParkingAndOverstayGroupStats(baseTotalStats); #region Populate data for each record foreach (AreaAsset areaAsset in this._ReportEngine.ReportDataModel.AreasIncludedInReport) { GroupStats baseAreaStats = this._ReportEngine.GetAreaStats(areaAsset.AreaID, timeIsolation); ParkingAndOverstayGroupStats areaStats = new ParkingAndOverstayGroupStats(baseAreaStats); foreach (SpaceAsset spaceAsset in this._ReportEngine.ReportDataModel.SpacesIncludedInReport) { // Skip record if its not applicable to the current area we are processing if (spaceAsset.AreaID_PreferLibertyBeforeInternal != areaAsset.AreaID) { continue; } List <SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent> spaceRecs = this._ReportEngine.ReportDataModel.FindRecsForBayAndMeter(spaceAsset.SpaceID, spaceAsset.MeterID); TimeSpan previousVacantDuration = new TimeSpan(0); SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent previousOccupiedEvent = null; foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repEvents in spaceRecs) { ws.SetValue(rowIdx, 1, repEvents.BayInfo.SpaceID); ws.SetValue(rowIdx, 2, repEvents.BayInfo.MeterID); if (areaAsset != null) { ws.SetValue(rowIdx, 3, areaAsset.AreaID); ws.SetValue(rowIdx, 4, areaAsset.AreaName); } ws.SetValue(rowIdx, 5, repEvents.SensorEvent_Start); ws.SetValue(rowIdx, 6, repEvents.SensorEvent_RecCreationDateTime); ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(repEvents.SensorEvent_Latency)); if (repEvents.SensorEvent_IsOccupied == true) { previousOccupiedEvent = repEvents; ws.SetValue(rowIdx, 8, 1); // 1 for numeric version of "True" if (previousVacantDuration.Ticks > 0) { ws.SetValue(rowIdx, 9, FormatTimeSpanAsHoursMinutesAndSeconds(previousVacantDuration)); } ws.SetValue(rowIdx, 10, repEvents.SensorEvent_Start); } else { ws.SetValue(rowIdx, 8, 0); // 1 for numeric version of "False" ws.SetValue(rowIdx, 11, repEvents.SensorEvent_Start); if (previousOccupiedEvent != null) { bool firstOverstay = true; if (previousOccupiedEvent.Overstays.Count == 0) { ws.SetValue(rowIdx, 11, previousOccupiedEvent.SensorEvent_Start); ws.SetValue(rowIdx, 12, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Duration)); RegulatedHoursDetail ruleForEvent = GetRegulationRuleAtEventTime(repEvents); if (ruleForEvent != null) { StringBuilder sb = new StringBuilder(); sb.Append(ruleForEvent.MaxStayMinutes.ToString()); ws.SetValue(rowIdx, 13, sb.ToString()); } ws.SetValue(rowIdx, 14, "N"); } foreach (SensorAndPaymentReportEngine.OverstayVioEvent overstay in previousOccupiedEvent.Overstays) { if (firstOverstay == false) { // Need to start new row and repeat the header info! rowIdx++; ws.SetValue(rowIdx, 1, previousOccupiedEvent.BayInfo.SpaceID); ws.SetValue(rowIdx, 2, previousOccupiedEvent.BayInfo.MeterID); if (areaAsset != null) { ws.SetValue(rowIdx, 3, areaAsset.AreaID); ws.SetValue(rowIdx, 4, areaAsset.AreaName); } ws.SetValue(rowIdx, 5, previousOccupiedEvent.SensorEvent_Start); ws.SetValue(rowIdx, 6, previousOccupiedEvent.SensorEvent_RecCreationDateTime); ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Latency)); ws.SetValue(rowIdx, 8, 0); // 1 for numeric version of "False" ws.SetValue(rowIdx, 11, previousOccupiedEvent.SensorEvent_Start); } ws.SetValue(rowIdx, 12, FormatTimeSpanAsHoursMinutesAndSeconds(previousOccupiedEvent.SensorEvent_Duration)); if (overstay.OverstayBasedOnRuleDetail != null) { StringBuilder sb = new StringBuilder(); sb.Append(overstay.OverstayBasedOnRuleDetail.MaxStayMinutes.ToString()); ws.SetValue(rowIdx, 13, sb.ToString()); } ws.SetValue(rowIdx, 14, "Y"); ws.SetValue(rowIdx, 15, FormatTimeSpanAsHoursMinutesAndSeconds(overstay.DurationOfTimeBeyondStayLimits)); if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 15) { ws.SetValue(rowIdx, 16, "Y"); areaStats.TotalOverstaysDuration0To15Mins++; totalStats.TotalOverstaysDuration0To15Mins++; } else if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 30) { ws.SetValue(rowIdx, 17, "Y"); areaStats.TotalOverstaysDuration15To30Mins++; totalStats.TotalOverstaysDuration15To30Mins++; } else if (overstay.DurationOfTimeBeyondStayLimits.TotalMinutes < 60) { ws.SetValue(rowIdx, 18, "Y"); areaStats.TotalOverstaysDuration30To60Mins++; totalStats.TotalOverstaysDuration30To60Mins++; } else { ws.SetValue(rowIdx, 19, "Y"); areaStats.TotalOverstaysDurationOver60Mins++; totalStats.TotalOverstaysDurationOver60Mins++; } if (!string.IsNullOrEmpty(overstay.EnforcementActionTaken)) { ws.SetValue(rowIdx, 20, "Y"); } else { ws.SetValue(rowIdx, 20, "N"); } if (string.Compare(overstay.EnforcementActionTaken, "Enforced", true) == 0) { ws.SetValue(rowIdx, 21, "Y"); } else { ws.SetValue(rowIdx, 21, "N"); } if (string.Compare(overstay.EnforcementActionTaken, "Cautioned", true) == 0) { ws.SetValue(rowIdx, 22, "Y"); } else { ws.SetValue(rowIdx, 22, "N"); } if (string.Compare(overstay.EnforcementActionTaken, "NotEnforced", true) == 0) { ws.SetValue(rowIdx, 23, "Y"); } else { ws.SetValue(rowIdx, 23, "N"); } if (string.Compare(overstay.EnforcementActionTaken, "Fault", true) == 0) { ws.SetValue(rowIdx, 24, "Y"); } else { ws.SetValue(rowIdx, 24, "N"); } if (string.IsNullOrEmpty(overstay.EnforcementActionTaken)) { ws.SetValue(rowIdx, 25, "Y"); ws.SetValue(rowIdx, 26, "N"); ws.SetValue(rowIdx, 27, "N"); ws.SetValue(rowIdx, 28, "N"); ws.SetValue(rowIdx, 29, "N"); } else { ws.SetValue(rowIdx, 25, "N"); ws.SetValue(rowIdx, 26, "N"); ws.SetValue(rowIdx, 27, "N"); ws.SetValue(rowIdx, 28, "N"); ws.SetValue(rowIdx, 29, "N"); TimeSpan captureRate = (overstay.EnforcementActionTakenTimeStamp - overstay.StartOfOverstayViolation); if (captureRate.TotalMinutes < 15) { ws.SetValue(rowIdx, 26, "Y"); areaStats.TotalOverstaysActioned0To15Mins++; totalStats.TotalOverstaysActioned0To15Mins++; } else if (captureRate.TotalMinutes < 30) { ws.SetValue(rowIdx, 27, "Y"); areaStats.TotalOverstaysActioned15To30Mins++; totalStats.TotalOverstaysActioned15To30Mins++; } else if (captureRate.TotalMinutes < 60) { ws.SetValue(rowIdx, 28, "Y"); areaStats.TotalOverstaysActioned30To60Mins++; totalStats.TotalOverstaysActioned30To60Mins++; } else { ws.SetValue(rowIdx, 29, "Y"); areaStats.TotalOverstaysActionedOver60Mins++; totalStats.TotalOverstaysActionedOver60Mins++; } } // Set flag so we know we're no longer dealing with the first overstay of this occupied event firstOverstay = false; } } } if (repEvents.SensorEvent_IsOccupied == false) { previousVacantDuration = new TimeSpan(repEvents.SensorEvent_Duration.Ticks); } // Increment the row index, which will now be the next row of our data rowIdx++; } } // Finish the area aggregations areaStats.AggregateSelf(); colIdx = 1; ws.SetValue(rowIdx, colIdx, "SUBTOTAL AREA"); MergeCellRange(ws, rowIdx, colIdx, rowIdx, 4); using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, colIdx, rowIdx, 29]) { rng.Style.Font.Bold = true; } ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(areaStats.AverageLatency)); ws.SetValue(rowIdx, 8, areaStats.ingress); ws.SetValue(rowIdx, 9, areaStats.PercentVacantDuration.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 12, areaStats.PercentageOccupancy.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 14, areaStats.PercentOverstayedCount.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 15, areaStats.PercentageOverstayedDuration.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 16, areaStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 17, areaStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 18, areaStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 19, areaStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 20, areaStats.PercentOverstaysActioned.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 21, areaStats.PercentOverstaysIssued.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 22, areaStats.PercentOverstaysCautioned.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 23, areaStats.PercentOverstaysNotIssued.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 24, areaStats.PercentOverstaysFault.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 25, areaStats.PercentOverstaysMissed.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 26, areaStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 27, areaStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 28, areaStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 29, areaStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%"); rowIdx++; rowIdx++; } // Finish the total aggregations totalStats.AggregateSelf(); colIdx = 1; ws.SetValue(rowIdx, colIdx, "TOTAL"); MergeCellRange(ws, rowIdx, colIdx, rowIdx, 4); using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, colIdx, rowIdx, 29]) { rng.Style.Font.Bold = true; } ws.SetValue(rowIdx, 7, FormatTimeSpanAsHoursMinutesAndSeconds(totalStats.AverageLatency)); ws.SetValue(rowIdx, 8, totalStats.ingress); ws.SetValue(rowIdx, 9, totalStats.PercentVacantDuration.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 12, totalStats.PercentageOccupancy.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 14, totalStats.PercentOverstayedCount.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 15, totalStats.PercentageOverstayedDuration.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 16, totalStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 17, totalStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 18, totalStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 19, totalStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 20, totalStats.PercentOverstaysActioned.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 21, totalStats.PercentOverstaysIssued.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 22, totalStats.PercentOverstaysCautioned.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 23, totalStats.PercentOverstaysNotIssued.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 24, totalStats.PercentOverstaysFault.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 25, totalStats.PercentOverstaysMissed.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 26, totalStats.PercentOverstayedDuration_0To15Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 27, totalStats.PercentOverstayedDuration_15To30Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 28, totalStats.PercentOverstayedDuration_30To60Min.ToString("##0.00") + "%"); ws.SetValue(rowIdx, 29, totalStats.PercentOverstayedDuration_Over60Min.ToString("##0.00") + "%"); rowIdx++; rowIdx++; #endregion // AutoFilters aren't suitable for this report /* * // We will add autofilters to our headers so user can sort the columns easier * using (OfficeOpenXml.ExcelRange rng = ws.Cells[detailsStartRow - 1, 1, rowIdx, detailColumnCount]) * { * rng.AutoFilter = true; * } */ ApplyNumberStyleToColumn(ws, 1, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 2, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 3, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 4, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 5, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 6, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 7, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 8, detailsStartRow, rowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 9, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 10, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 11, detailsStartRow, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 12, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 13, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 14, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 15, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 16, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 17, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 18, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 19, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 20, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 21, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 22, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 23, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 24, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 25, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 26, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 27, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 28, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 29, detailsStartRow, rowIdx, "@", ExcelHorizontalAlignment.Right); // And now lets size the columns for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++) { using (OfficeOpenXml.ExcelRange col = ws.Cells[detailsStartRow - 1, autoSizeColIdx, rowIdx, autoSizeColIdx]) { col.AutoFitColumns(); } } // And finally we will freeze the header rows for nicer scrolling ws.View.FreezePanes(7, 1); // All cells in spreadsheet are populated now, so render (save the file) to a memory stream byte[] bytes = pck.GetAsByteArray(); ms.Write(bytes, 0, bytes.Length); } // Stop diagnostics timer sw.Stop(); System.Diagnostics.Debug.WriteLine(this._ReportName + " generation took: " + sw.Elapsed.ToString()); }