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()); }
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);// DateTime.Now;//Datetime.Now;//DateTime.Now;//Datetime.Now; // Now gather and analyze data for the report SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements(); requiredDataElements.NeedsSensorData = true; requiredDataElements.NeedsPaymentData = false; requiredDataElements.NeedsOverstayData = false; requiredDataElements.NeedsEnforcementActionData = false; this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, _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 // Create the worksheet ws = pck.Workbook.Worksheets.Add("Summary"); // 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++; colIdx = 1; int hyperlinkstartRowIdx = rowIdx; if (_ReportParams.IncludeMeterSummary == true) { RenderWorksheetHyperlink(ws, "Meter Occupancy", "Meter Occupancy summary"); } if (_ReportParams.IncludeSpaceSummary == true) { RenderWorksheetHyperlink(ws, "Space Occupancy", "Space Occupancy summary"); } if (_ReportParams.IncludeAreaSummary == true) { RenderWorksheetHyperlink(ws, "Area Occupancy", "Area Occupancy summary"); } if (_ReportParams.IncludeDailySummary == true) { RenderWorksheetHyperlink(ws, "Daily Occupancy", "Daily Occupancy summary"); } if (_ReportParams.IncludeMonthlySummary == true) { RenderWorksheetHyperlink(ws, "Monthly Occupancy", "Monthly Occupancy summary"); } if (_ReportParams.IncludeDetailRecords == true) { RenderWorksheetHyperlink(ws, "Details", "Occupancy details"); } rowIdx++; rowIdx++; colIdx = 1; using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, numColumnsMergedForHeader]) { rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White); } // Now start the report data summary header RenderOverallReportSummary(ws); // --- END OF OVERALL SUMMARY WORKSHEET --- // Should we include a worksheet with Meter aggregates? if (_ReportParams.IncludeMeterSummary == true) { RenderMeterSummaryWorksheet(pck, "Meter Occupancy"); } // Should we include a worksheet with Space aggregates? if (_ReportParams.IncludeSpaceSummary == true) { RenderSpaceSummaryWorksheet(pck, "Space Occupancy"); } // Should we include a worksheet with Area aggregates? if (_ReportParams.IncludeAreaSummary == true) { RenderAreaSummaryWorksheet(pck, "Area Occupancy"); } // Should we include a worksheet with Daily aggregates? if (_ReportParams.IncludeDailySummary == true) { RenderDailySummaryWorksheet(pck, "Daily Occupancy"); } // Should we include a worksheet with Monthly aggregates? if (_ReportParams.IncludeDailySummary == true) { RenderMonthlySummaryWorksheet(pck, "Monthly Occupancy"); } // Should we include a Details worksheet? if (_ReportParams.IncludeDetailRecords == true) { // Create the worksheet ws = pck.Workbook.Worksheets.Add("Details"); int detailColumnCount = 8; // Render the header row rowIdx = 1; // Excel uses 1-based indexes 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 Start"); ws.SetValue(rowIdx, 6, "Event End"); ws.SetValue(rowIdx, 7, "Occupied?"); ws.SetValue(rowIdx, 8, "Duration"); // Format the header row using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, detailColumnCount]) { 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); } // Increment the row index, which will now be the 1st row of our data rowIdx++; #region Populate data for each record foreach (SpaceAsset spaceAsset in this._ReportEngine.ReportDataModel.SpacesIncludedInReport) { List <SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent> spaceRecs = this._ReportEngine.ReportDataModel.FindRecsForBayAndMeter(spaceAsset.SpaceID, spaceAsset.MeterID); foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent nextEvent in spaceRecs) { // Don't detail this item if its a "dummy" event if (nextEvent.IsDummySensorEvent == true) { continue; } AreaAsset areaAsset = _ReportEngine.GetAreaAsset(spaceAsset.AreaID_PreferLibertyBeforeInternal); // Output row values for data ws.SetValue(rowIdx, 1, spaceAsset.SpaceID); ws.SetValue(rowIdx, 2, spaceAsset.MeterID); if (areaAsset != null) { ws.SetValue(rowIdx, 3, areaAsset.AreaID); ws.SetValue(rowIdx, 4, areaAsset.AreaName); } ws.SetValue(rowIdx, 5, nextEvent.SensorEvent_Start); ws.SetValue(rowIdx, 6, nextEvent.SensorEvent_End); if (nextEvent.SensorEvent_IsOccupied == true) { ws.SetValue(rowIdx, 7, "Y"); } else { ws.SetValue(rowIdx, 7, "N"); } ws.SetValue(rowIdx, 8, FormatTimeSpanAsHoursMinutesAndSeconds(nextEvent.SensorEvent_Duration)); // Increment the row index, which will now be the next row of our data rowIdx++; // Is there a child "repeat" event also? if (nextEvent.RepeatSensorEvents != null) { foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repeatEvent in nextEvent.RepeatSensorEvents) { ws.SetValue(rowIdx, 1, spaceAsset.SpaceID); ws.SetValue(rowIdx, 2, spaceAsset.MeterID); if (areaAsset != null) { ws.SetValue(rowIdx, 3, areaAsset.AreaID); ws.SetValue(rowIdx, 4, areaAsset.AreaName); } ws.SetValue(rowIdx, 5, repeatEvent.SensorEvent_Start); ws.SetValue(rowIdx, 6, repeatEvent.SensorEvent_End); if (repeatEvent.SensorEvent_IsOccupied == true) { ws.SetValue(rowIdx, 7, "Y"); } else { ws.SetValue(rowIdx, 7, "N"); } ws.SetValue(rowIdx, 8, FormatTimeSpanAsHoursMinutesAndSeconds(repeatEvent.SensorEvent_Duration)); // Increment the row index, which will now be the next row of our data rowIdx++; } } } } #endregion // We will add autofilters to our headers so user can sort the columns easier using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, rowIdx, detailColumnCount]) { rng.AutoFilter = true; } // Apply formatting to the columns as appropriate (Starting row is 2 (first row of data), and ending row is the current rowIdx value) ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 2, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 3, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 5, 2, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 6, 2, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, 7, 2, rowIdx, "@", ExcelHorizontalAlignment.Right); // String value, right-aligned ApplyNumberStyleToColumn(ws, 8, 2, rowIdx, "@", ExcelHorizontalAlignment.Right); // String value, right-aligned // And now lets size the columns for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++) { using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx]) { col.AutoFitColumns(); } } } // 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()); }
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 = true; 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("Summary"); // 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++; colIdx = 1; int hyperlinkstartRowIdx = rowIdx; if (_ReportParams.IncludeMeterSummary == true) { RenderWorksheetHyperlink(ws, "Meter Enforcement", "Meter Enforcement summary"); } if (_ReportParams.IncludeSpaceSummary == true) { RenderWorksheetHyperlink(ws, "Space Enforcement", "Space Enforcement summary"); } if (_ReportParams.IncludeAreaSummary == true) { RenderWorksheetHyperlink(ws, "Area Enforcement", "Area Enforcement summary"); } if (_ReportParams.IncludeDailySummary == true) { RenderWorksheetHyperlink(ws, "Daily Enforcement", "Daily Enforcement summary"); } if (_ReportParams.IncludeMonthlySummary == true) { RenderWorksheetHyperlink(ws, "Monthly Enforcement", "Monthly Enforcement summary"); } if (_ReportParams.IncludeDetailRecords == true) { RenderWorksheetHyperlink(ws, "Details", "Enforcement details"); } rowIdx++; rowIdx++; colIdx = 1; using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 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.White); } // Now start the report data summary header RenderOverallReportSummary(ws); // --- END OF OVERALL SUMMARY WORKSHEET --- // Should we include a worksheet with Meter aggregates? if (_ReportParams.IncludeMeterSummary == true) { RenderMeterSummaryWorksheet(pck, "Meter Enforcement"); } // Should we include a worksheet with Space aggregates? if (_ReportParams.IncludeSpaceSummary == true) { RenderSpaceSummaryWorksheet(pck, "Space Enforcement"); } // Should we include a worksheet with Area aggregates? if (_ReportParams.IncludeAreaSummary == true) { RenderAreaSummaryWorksheet(pck, "Area Enforcement"); } // Should we include a worksheet with Daily aggregates? if (_ReportParams.IncludeDailySummary == true) { RenderDailySummaryWorksheet(pck, "Daily Enforcement"); } // Should we include a worksheet with Monthly aggregates? if (_ReportParams.IncludeDailySummary == true) { RenderMonthlySummaryWorksheet(pck, "Monthly Enforcement"); } // Should we include a Details worksheet? if (_ReportParams.IncludeDetailRecords == true) { // Create the worksheet ws = pck.Workbook.Worksheets.Add("Details"); int detailColumnCount = 18; // Render the header row rowIdx = 1; // Excel uses 1-based indexes ws.SetValue(rowIdx, 1, "Space #"); ws.SetValue(rowIdx, 2, "Meter #"); ws.SetValue(rowIdx, 3, "Area #"); ws.SetValue(rowIdx, 4, "Area"); ws.SetValue(rowIdx, 5, "Arrival"); ws.SetValue(rowIdx, 6, "Departure"); ws.SetValue(rowIdx, 7, "Start of" + Environment.NewLine + "Overstay Violation"); ApplyWrapTextStyleToCell(ws, rowIdx, 7); ws.SetValue(rowIdx, 8, "Overstay Violation" + Environment.NewLine + "Duration"); ApplyWrapTextStyleToCell(ws, rowIdx, 8); ws.SetValue(rowIdx, 9, "Overstay Violation" + Environment.NewLine + "Action Taken"); ApplyWrapTextStyleToCell(ws, rowIdx, 9); ws.SetValue(rowIdx, 10, "Overstay Violation" + Environment.NewLine + "Action Taken Timestamp"); ApplyWrapTextStyleToCell(ws, rowIdx, 10); ws.SetValue(rowIdx, 11, "Overstay Rule"); ws.SetValue(rowIdx, 12, "Payment Timestamp"); ws.SetValue(rowIdx, 13, "Payment Expiration"); ws.SetValue(rowIdx, 14, "Payment Zeroed-out" + Environment.NewLine + "Timestamp"); ApplyWrapTextStyleToCell(ws, rowIdx, 14); ws.SetValue(rowIdx, 15, "Start of" + Environment.NewLine + "Payment Violation"); ApplyWrapTextStyleToCell(ws, rowIdx, 15); ws.SetValue(rowIdx, 16, "Payment Violation" + Environment.NewLine + "Duration"); ApplyWrapTextStyleToCell(ws, rowIdx, 16); ws.SetValue(rowIdx, 17, "Payment Violation" + Environment.NewLine + "Action Taken"); ApplyWrapTextStyleToCell(ws, rowIdx, 17); ws.SetValue(rowIdx, 18, "Payment Violation" + Environment.NewLine + "Action Taken Timestamp"); ApplyWrapTextStyleToCell(ws, rowIdx, 18); Dictionary <int, List <string> > ColumnLinesForRow = new Dictionary <int, List <string> >(); // Format the header row using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, detailColumnCount]) { 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); } // Increment the row index, which will now be the 1st row of our data rowIdx++; #region Populate data for each record foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repEvent in this._ReportEngine.ReportDataModel.ReportableEvents) { // Ignore unoccupied sections or dummy sensor events if (repEvent.SensorEvent_IsOccupied == false) { continue; } if (repEvent.IsDummySensorEvent == true) { continue; } // The details only need to list records that are involved in either payment or overstay violations (unenforceable sensor and payment events can be ignored) if ((repEvent.PaymentVios.Count == 0) && (repEvent.Overstays.Count == 0)) { continue; } // Start with fresh collections for each column's text lines for current row for (int nextCol = 1; nextCol <= detailColumnCount; nextCol++) { ColumnLinesForRow[nextCol] = new List <string>(); } AreaAsset areaAsset = _ReportEngine.GetAreaAsset(repEvent.BayInfo.AreaID_PreferLibertyBeforeInternal); // Output row values for data ColumnLinesForRow[1].Add(repEvent.BayInfo.SpaceID.ToString()); ColumnLinesForRow[2].Add(repEvent.BayInfo.MeterID.ToString()); if (areaAsset != null) { ColumnLinesForRow[3].Add(areaAsset.AreaID.ToString()); ColumnLinesForRow[4].Add(areaAsset.AreaName); } ColumnLinesForRow[5].Add(repEvent.SensorEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt")); ColumnLinesForRow[6].Add(repEvent.SensorEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt")); // Add sensor ins/outs for each "repeat" sensor event foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repeatEvent in repEvent.RepeatSensorEvents) { ColumnLinesForRow[5].Add(repEvent.SensorEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt")); ColumnLinesForRow[6].Add(repEvent.SensorEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt")); } foreach (SensorAndPaymentReportEngine.OverstayVioEvent overstay in repEvent.Overstays) { ColumnLinesForRow[7].Add(overstay.StartOfOverstayViolation.ToString("yyyy-MM-dd hh:mm:ss tt")); ColumnLinesForRow[8].Add(FormatTimeSpanAsHoursMinutesAndSeconds(overstay.DurationOfTimeBeyondStayLimits)); if (!string.IsNullOrEmpty(overstay.EnforcementActionTaken)) { ColumnLinesForRow[9].Add(overstay.EnforcementActionTaken); } else { ColumnLinesForRow[9].Add(""); } if (overstay.EnforcementActionTakenTimeStamp > DateTime.MinValue) { ColumnLinesForRow[10].Add(overstay.EnforcementActionTakenTimeStamp.ToString("yyyy-MM-dd hh:mm:ss tt")); } else { ColumnLinesForRow[10].Add(""); } if (overstay.OverstayBasedOnRuleDetail != null) { StringBuilder sb = new StringBuilder(); sb.Append(Enum.ToObject(typeof(DayOfWeek), overstay.OverstayBasedOnRuleDetail.DayOfWeek).ToString() + " "); sb.Append(overstay.OverstayBasedOnRuleDetail.StartTime.ToString("hh:mm:ss tt") + " - " + overstay.OverstayBasedOnRuleDetail.EndTime.ToString("hh:mm:ss tt") + ", "); sb.Append(overstay.OverstayBasedOnRuleDetail.Type + ", Max Stay: " + overstay.OverstayBasedOnRuleDetail.MaxStayMinutes.ToString()); ColumnLinesForRow[11].Add(sb.ToString()); } else { ColumnLinesForRow[11].Add(""); } } foreach (SensorAndPaymentReportEngine.PaymentEvent payEvent in repEvent.PaymentEvents) { if (payEvent.PaymentEvent_IsPaid == false) { continue; } ColumnLinesForRow[12].Add(payEvent.PaymentEvent_Start.ToString("yyyy-MM-dd hh:mm:ss tt")); if (payEvent.WasStoppedShortViaZeroOutTrans == true) { ColumnLinesForRow[13].Add(payEvent.OriginalPaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt")); ColumnLinesForRow[14].Add(payEvent.PaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt")); } else { ColumnLinesForRow[13].Add(payEvent.PaymentEvent_End.ToString("yyyy-MM-dd hh:mm:ss tt")); ColumnLinesForRow[14].Add(""); } } foreach (SensorAndPaymentReportEngine.PaymentVioEvent payVio in repEvent.PaymentVios) { ColumnLinesForRow[15].Add(payVio.StartOfPayViolation.ToString("yyyy-MM-dd hh:mm:ss tt")); ColumnLinesForRow[16].Add(FormatTimeSpanAsHoursMinutesAndSeconds(payVio.DurationOfTimeInViolation)); if (!string.IsNullOrEmpty(payVio.EnforcementActionTaken)) { ColumnLinesForRow[17].Add(payVio.EnforcementActionTaken); } else { ColumnLinesForRow[17].Add(""); } if (payVio.EnforcementActionTakenTimeStamp > DateTime.MinValue) { ColumnLinesForRow[18].Add(payVio.EnforcementActionTakenTimeStamp.ToString("yyyy-MM-dd hh:mm:ss tt")); } else { ColumnLinesForRow[18].Add(""); } } int linesForRow = 1; for (int nextCol = 1; nextCol <= detailColumnCount; nextCol++) { int columnRowLines = 0; StringBuilder sb = new StringBuilder(); bool firstLine = true; foreach (string nextLine in ColumnLinesForRow[nextCol]) { columnRowLines++; if (firstLine == false) { sb.AppendLine(); } sb.Append(nextLine); firstLine = false; } ws.SetValue(rowIdx, nextCol, sb.ToString()); if (columnRowLines > linesForRow) { linesForRow = columnRowLines; } if (columnRowLines > 1) { using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, nextCol]) { ws.Cells[rowIdx, nextCol].Style.WrapText = true; } } } // Do we need to resize the row? if (linesForRow > 1) { ws.Row(rowIdx).Height = (ws.DefaultRowHeight * linesForRow); using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, 1, rowIdx, detailColumnCount]) { rowrange.Style.VerticalAlignment = ExcelVerticalAlignment.Top; } } // Increment the row index, which will now be the next row of our data rowIdx++; } #endregion // We will add autofilters to our headers so user can sort the columns easier using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, rowIdx, detailColumnCount]) { rng.AutoFilter = true; } // Apply formatting to the columns as appropriate (Starting row is 2 (first row of data), and ending row is the current rowIdx value) // Column 1 & 2 are numeric integer ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 2, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 3, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 4, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 5, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 6, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 7, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 8, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 9, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 10, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 11, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 12, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 13, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 14, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 15, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 16, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 17, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); ApplyNumberStyleToColumn(ws, 18, 2, rowIdx, "@", ExcelHorizontalAlignment.Left); // And now lets size the columns for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++) { using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx]) { col.AutoFitColumns(); } } // And now finally we must manually size the columns that have wrap text (autofit doesn't work nicely when we have wrap text) ws.Column(1 + 6).Width = 24; ws.Column(1 + 7).Width = 24; ws.Column(1 + 8).Width = 24; ws.Column(1 + 9).Width = 27; ws.Column(1 + 13).Width = 24; ws.Column(1 + 14).Width = 24; ws.Column(1 + 15).Width = 24; ws.Column(1 + 16).Width = 24; ws.Column(1 + 17).Width = 27; } // 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()); }