protected override void RenderCommonData(ExcelWorksheet ws, int startRowIdx, int startColIdx, ref int colIdx_HourlyCategory, GroupStats statsObj) { int colIdx_1stCommonColumn = startColIdx; int renderColIdx = colIdx_1stCommonColumn; int renderRowIdx = startRowIdx; ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.TotalOccupancyTime)); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.MaximumPotentialOccupancyTime)); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.PercentageOccupancy); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.ingress); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.egress); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 2, renderRowIdx, renderRowIdx, "###0.00", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 3, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 4, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); // And now lets autosize the columns for (int autoSizeColIdx = colIdx_1stCommonColumn; autoSizeColIdx <= renderColIdx; autoSizeColIdx++) { using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, renderRowIdx, autoSizeColIdx]) { col.AutoFitColumns(); col.Style.VerticalAlignment = ExcelVerticalAlignment.Top; } } // 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(colIdx_1stCommonColumn + 1).Width = 20; ws.Column(colIdx_1stCommonColumn + 3).Width = 12; ws.Column(colIdx_1stCommonColumn + 4).Width = 12; if (this._ReportParams.IncludeHourlyStatistics == true) { // Now we will construct the hourly category column, followed by hour detail columns ws.SetValue(renderRowIdx + 0, colIdx_HourlyCategory, "Occupied Duration"); ws.SetValue(renderRowIdx + 1, colIdx_HourlyCategory, "Max Possible Duration"); ws.SetValue(renderRowIdx + 2, colIdx_HourlyCategory, "% Occupied"); ws.SetValue(renderRowIdx + 3, colIdx_HourlyCategory, "Arrivals"); ws.SetValue(renderRowIdx + 4, colIdx_HourlyCategory, "Departures"); using (OfficeOpenXml.ExcelRange col = ws.Cells[renderRowIdx, colIdx_HourlyCategory, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory]) { col.Style.Font.Bold = true; } MergeCellRange(ws, renderRowIdx + 1, 1, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory - 1); } }
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 = 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()); }
protected override void RenderCommonData(ExcelWorksheet ws, int startRowIdx, int startColIdx, ref int colIdx_HourlyCategory, GroupStats statsObj) { int colIdx_1stCommonColumn = startColIdx; int renderColIdx = colIdx_1stCommonColumn; int renderRowIdx = startRowIdx; ws.SetValue(renderRowIdx, renderColIdx, statsObj.OverstayCount); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.PayVioCount); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.OverstayCount + statsObj.PayVioCount); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysActioned + statsObj.TotalPayViosActioned); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysEnforced + statsObj.TotalPayViosEnforced); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysCautioned + statsObj.TotalPayViosCautioned); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysNotEnforced + statsObj.TotalPayViosNotEnforced); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalOverstaysFaulty + statsObj.TotalPayViosFaulty); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalViosActioned0To15Mins); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalViosActioned15To40Mins); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalViosActionedOver40Mins); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, (statsObj.OverstayCount + statsObj.PayVioCount) - (statsObj.TotalOverstaysActioned + statsObj.TotalPayViosActioned)); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 0, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 1, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 2, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 3, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 4, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 5, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 6, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 7, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 8, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 9, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 10, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 11, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); // And now lets autosize the columns for (int autoSizeColIdx = colIdx_1stCommonColumn; autoSizeColIdx <= renderColIdx; autoSizeColIdx++) { using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, renderRowIdx, autoSizeColIdx]) { col.AutoFitColumns(); col.Style.VerticalAlignment = ExcelVerticalAlignment.Top; } } // 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(colIdx_1stCommonColumn + 0).Width = 20; ws.Column(colIdx_1stCommonColumn + 1).Width = 20; ws.Column(colIdx_1stCommonColumn + 2).Width = 20; ws.Column(colIdx_1stCommonColumn + 3).Width = 20; ws.Column(colIdx_1stCommonColumn + 4).Width = 20; ws.Column(colIdx_1stCommonColumn + 5).Width = 20; ws.Column(colIdx_1stCommonColumn + 6).Width = 20; ws.Column(colIdx_1stCommonColumn + 8).Width = 20; ws.Column(colIdx_1stCommonColumn + 9).Width = 20; ws.Column(colIdx_1stCommonColumn + 10).Width = 20; ws.Column(colIdx_1stCommonColumn + 11).Width = 20; if (this._ReportParams.IncludeHourlyStatistics == true) { // Now we will construct the hourly category column, followed by hour detail columns ws.SetValue(renderRowIdx + 0, colIdx_HourlyCategory, "Overstay Violations"); ws.SetValue(renderRowIdx + 1, colIdx_HourlyCategory, "Payment Violations"); ws.SetValue(renderRowIdx + 2, colIdx_HourlyCategory, "Total Violations"); ws.SetValue(renderRowIdx + 3, colIdx_HourlyCategory, "Total Actioned"); ws.SetValue(renderRowIdx + 4, colIdx_HourlyCategory, "Total Enforced"); ws.SetValue(renderRowIdx + 5, colIdx_HourlyCategory, "Total Cautioned"); ws.SetValue(renderRowIdx + 6, colIdx_HourlyCategory, "Total Not Enforced"); ws.SetValue(renderRowIdx + 7, colIdx_HourlyCategory, "Total Faulty"); ws.SetValue(renderRowIdx + 8, colIdx_HourlyCategory, "Actioned < 15 mins"); ws.SetValue(renderRowIdx + 9, colIdx_HourlyCategory, "Actioned 15 - 40 mins"); ws.SetValue(renderRowIdx + 10, colIdx_HourlyCategory, "Actioned > 40 mins"); ws.SetValue(renderRowIdx + 11, colIdx_HourlyCategory, "Total Missed"); using (OfficeOpenXml.ExcelRange col = ws.Cells[renderRowIdx, colIdx_HourlyCategory, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory]) { col.Style.Font.Bold = true; } MergeCellRange(ws, renderRowIdx + 1, 1, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory - 1); } }
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()); }
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, ActivityRestrictions activityRestriction, string scopedAreaName, string scopedMeter) { // Start diagnostics timer System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch(); sw.Start(); DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName); this._ActivityRestriction = activityRestriction; this.GatherReportData(listOfMeterIDs); OfficeOpenXml.ExcelWorksheet ws = null; int rowIdx = -1; 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 header row rowIdx = 1; // Excel uses 1-based indexes ws.Cells[rowIdx, 1].Value = "Asset Listings Report"; using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 1, rowIdx, 10]) { rng.Merge = true; //Merge columns start and end range rng.Style.Font.Bold = true; rng.Style.Font.Italic = true; rng.Style.Font.Size = 22; rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } rowIdx++; ws.Cells[rowIdx, 1].IsRichText = true; OfficeOpenXml.Style.ExcelRichTextCollection rtfCollection = ws.Cells[rowIdx, 1].RichText; AddRichTextNameAndValue(rtfCollection, "Client: ", this._CustomerConfig.CustomerName); AddRichTextNameAndValue(rtfCollection, ", Generated: ", NowAtDestination.ToShortDateString() + " " + NowAtDestination.ToShortTimeString()); ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true; rowIdx++; rtfCollection = ws.Cells[rowIdx, 1].RichText; AddRichTextNameAndValue(rtfCollection, "Included Activity: ", "Asset Listing"); ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true; if (!string.IsNullOrEmpty(scopedAreaName)) { rowIdx++; rtfCollection = ws.Cells[rowIdx, 1].RichText; AddRichTextNameAndValue(rtfCollection, "Report limited to area: ", scopedAreaName); ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true; } if (!string.IsNullOrEmpty(scopedMeter)) { rowIdx++; rtfCollection = ws.Cells[rowIdx, 1].RichText; AddRichTextNameAndValue(rtfCollection, "Report limited to meter: ", scopedMeter); ws.Cells[rowIdx, 1, rowIdx, 10].Merge = true; } rowIdx++; using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, 10]) { rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Font.Color.SetColor(System.Drawing.Color.White); rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228) } rowIdx++; int hyperlinkstartRowIdx = rowIdx; rowIdx++; rowIdx++; using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, 13]) { rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White); } // Render the header row rowIdx = 7; // Excel uses 1-based indexes // have to start at column 2, does not work when start column is 1. Will come back when more time is avail using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 2, rowIdx, 6]) { rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rng.Merge = true; //Merge columns start and end range rng.Style.Font.Bold = true; } ws.Cells[rowIdx, 2].Value = "Site Details"; using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 1, rowIdx, 6]) { rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(23, 55, 93)); rng.Style.Font.Color.SetColor(System.Drawing.Color.White); } using (OfficeOpenXml.ExcelRange rng = ws.Cells[rowIdx, 7, rowIdx, 13]) { rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rng.Merge = true; //Merge columns start and end range rng.Style.Font.Bold = true; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); } ws.Cells[rowIdx, 7].Value = "Regulations"; rowIdx++; ws.Cells[rowIdx, 1].Value = "Meter ID"; ws.Cells[rowIdx, 2].Value = "Space ID"; ws.Cells[rowIdx, 3].Value = "Area #"; ws.Cells[rowIdx, 4].Value = "Site Details Area"; ws.Cells[rowIdx, 5].Value = "Co-Ordinates Lat"; ws.Cells[rowIdx, 6].Value = "Co-Ordinates Long"; ws.Cells[rowIdx, 7].Value = "Regulations - Sun"; ws.Cells[rowIdx, 8].Value = "Regulations - Mon"; ws.Cells[rowIdx, 9].Value = "Regulations - Tues"; ws.Cells[rowIdx, 10].Value = "Regulations - Wed"; ws.Cells[rowIdx, 11].Value = "Regulations - Thurs"; ws.Cells[rowIdx, 12].Value = "Regulations - Fri"; ws.Cells[rowIdx, 13].Value = "Regulations - Sat"; // Format the header row using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, 6]) { 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++; foreach (AssetListing_Space meterStat in this._ReportDataModel.SpaceDetailsList) { #region Unused code, but useful examples // Example of how we could automatically render a dataset to worksheet /* * // Load the datatable into the sheet, starting from cell A1. Print the column names on row 1 * ws.Cells["A1"].LoadFromDataTable(nextTable, true); */ // Example of how we could automatically render a strongly-typed collection of objects to worksheet /* * List<MeterStatisticObj> statObjects = new List<MeterStatisticObj>(); * statObjects.Add(meterStatCollection.MeterStats_Summary); * ws.Cells["A1"].LoadFromCollection(statObjects, true); */ // Example of formatting a column for Date/Time /* * ws.Column(3).Width = 20; * using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 3, 2 + nextTable.Rows.Count, 3]) * { * col.Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss tt"; * col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right; * } */ // Example of using RichText in a cell for advanced formatting possibilites /* * ws.Cells[rowIdx, 1].IsRichText = true; * ws.Cells[rowIdx, 1].Style.WrapText = true; // Need this if we want multi-line * OfficeOpenXml.Style.ExcelRichTextCollection rtfCollection = ws.Cells[rowIdx, 1].RichText; * OfficeOpenXml.Style.ExcelRichText ert = rtfCollection.Add(areaStat.AreaName + "\r\n"); * * ert = rtfCollection.Add(" (ID=" + areaStat.AreaID.ToString() + ")"); * ert.Bold = false; * ert.Italic = true; * ert.Size = 8; */ #endregion // Output row values for data ws.Cells[rowIdx, 1].Value = meterStat.MeterID; ws.Cells[rowIdx, 2].Value = meterStat.SpaceID; ws.Cells[rowIdx, 3].Value = meterStat.AreaID; ws.Cells[rowIdx, 4].Value = meterStat.Location; ws.Cells[rowIdx, 5].Value = meterStat.Latitude; ws.Cells[rowIdx, 6].Value = meterStat.Longitude; RegulatedHoursGroupRepository.Repository = new RegulatedHoursGroupRepository(); RegulatedHoursGroup regulatedHours = RegulatedHoursGroupRepository.Repository.GetBestGroupForMeter(this._CustomerConfig.CustomerId, meterStat.AreaID, meterStat.MeterID); // If no regulated hour defintions came back, then we will default to assumption that regulated period is 24-hours a day if ((regulatedHours == null) || (regulatedHours.Details == null) || (regulatedHours.Details.Count == 0)) { rowIdx++; continue; } // Loop through the daily rules and see if the timestamp falls within a Regulated or No Parking timeslot for the appropriate day foreach (RegulatedHoursDetail detail in regulatedHours.Details) { string regulationTxt = detail.StartTime.ToString("hh:mm:ss tt") + " - " + detail.EndTime.ToString("hh:mm:ss tt") + ", " + detail.MaxStayMinutes.ToString() + " mins"; if (string.Compare(detail.Type, "Unregulated", true) == 0) { regulationTxt = "(Unregulated) " + detail.StartTime.ToString("hh:mm:ss tt") + " - " + detail.EndTime.ToString("hh:mm:ss tt") + ", " + detail.MaxStayMinutes.ToString() + " mins"; } else if (string.Compare(detail.Type, "No Parking", true) == 0) { regulationTxt = "(No Parking) " + detail.StartTime.ToString("hh:mm:ss tt") + " - " + detail.EndTime.ToString("hh:mm:ss tt"); } else if (detail.MaxStayMinutes < 1) { regulationTxt = "(No Limit) " + detail.StartTime.ToString("hh:mm:ss tt") + " - " + detail.EndTime.ToString("hh:mm:ss tt"); } // Determine which column of the spreadsheet is used for this day of the week int columnIdxForDayOfWeek = 7 + detail.DayOfWeek; // If the cell is empty, just add the regulation text. If something is already there, append the regulation text // (There might be more than one regulated period for the same day) if ((ws.Cells[rowIdx, columnIdxForDayOfWeek].Value == null) || ((ws.Cells[rowIdx, columnIdxForDayOfWeek].Value as string) == null)) { ws.Cells[rowIdx, columnIdxForDayOfWeek].Value = regulationTxt; } else { ws.Cells[rowIdx, columnIdxForDayOfWeek].Value = (ws.Cells[rowIdx, columnIdxForDayOfWeek].Value as string) + Environment.NewLine + regulationTxt; // And increment the row height also ws.Row(rowIdx).Height = ws.Row(rowIdx).Height + ws.DefaultRowHeight; ws.Cells[rowIdx, columnIdxForDayOfWeek].Style.WrapText = true; using (OfficeOpenXml.ExcelRange rowrange = ws.Cells[rowIdx, 1, rowIdx, 14]) { rowrange.Style.VerticalAlignment = ExcelVerticalAlignment.Top; } } } // Increment the row index, which will now be the next row of our data rowIdx++; } // We will add autofilters to our headers so user can sort the columns easier using (OfficeOpenXml.ExcelRange rng = ws.Cells[8, 1, 8, 13]) { rng.AutoFilter = true; } // Column 1 is numeric integer (Meter ID) ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left); // And now lets size the columns for (int autoSizeColIdx = 1; autoSizeColIdx <= 13; 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("Occupancy Report Generation took: " + sw.Elapsed.ToString()); }
protected override void RenderCommonData(ExcelWorksheet ws, int startRowIdx, int startColIdx, ref int colIdx_HourlyCategory, GroupStats statsObj) { int colIdx_1stCommonColumn = startColIdx; int renderColIdx = colIdx_1stCommonColumn; int renderRowIdx = startRowIdx; ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.TotalOccupancyTime)); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.TotalOccupancyPaidTime)); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.MaximumPotentialOccupancyTime)); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.PercentageOccupiedPaid); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.PercentageOccupiedNotPaid); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.ingress); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.egress); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.PaymentCount); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalZeroedOutEvents); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, FormatTimeSpanAsHoursMinutesAndSeconds(statsObj.TotalZeroedOutDuration)); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosActioned); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.PayVioCount); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosEnforced); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosCautioned); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosNotEnforced); renderColIdx++; ws.SetValue(renderRowIdx, renderColIdx, statsObj.TotalPayViosFaulty); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 3, renderRowIdx, renderRowIdx, "###0.00", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 4, renderRowIdx, renderRowIdx, "###0.00", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 5, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 6, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 7, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 8, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 10, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 11, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 12, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 13, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 14, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); ApplyNumberStyleToColumn(ws, colIdx_1stCommonColumn + 15, renderRowIdx, renderRowIdx, "########0", ExcelHorizontalAlignment.Right); // And now lets autosize the columns for (int autoSizeColIdx = colIdx_1stCommonColumn; autoSizeColIdx <= renderColIdx; autoSizeColIdx++) { using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, renderRowIdx, autoSizeColIdx]) { col.AutoFitColumns(); col.Style.VerticalAlignment = ExcelVerticalAlignment.Top; } } // 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(colIdx_1stCommonColumn + 0).Width = 15; ws.Column(colIdx_1stCommonColumn + 1).Width = 20; ws.Column(colIdx_1stCommonColumn + 2).Width = 20; ws.Column(colIdx_1stCommonColumn + 3).Width = 20; ws.Column(colIdx_1stCommonColumn + 4).Width = 24; ws.Column(colIdx_1stCommonColumn + 5).Width = 12; ws.Column(colIdx_1stCommonColumn + 6).Width = 12; ws.Column(colIdx_1stCommonColumn + 8).Width = 15; ws.Column(colIdx_1stCommonColumn + 9).Width = 15; ws.Column(colIdx_1stCommonColumn + 10).Width = 12; ws.Column(colIdx_1stCommonColumn + 11).Width = 12; ws.Column(colIdx_1stCommonColumn + 12).Width = 12; ws.Column(colIdx_1stCommonColumn + 13).Width = 12; ws.Column(colIdx_1stCommonColumn + 14).Width = 12; ws.Column(colIdx_1stCommonColumn + 15).Width = 12; ws.Column(colIdx_1stCommonColumn + 16).Width = 40; if (this._ReportParams.IncludeHourlyStatistics == true) { // Now we will construct the hourly category column, followed by hour detail columns ws.SetValue(renderRowIdx + 0, colIdx_HourlyCategory, "Occupied Duration"); ws.SetValue(renderRowIdx + 1, colIdx_HourlyCategory, "Occupied & Paid Duration"); ws.SetValue(renderRowIdx + 2, colIdx_HourlyCategory, "Max Possible Duration"); ws.SetValue(renderRowIdx + 3, colIdx_HourlyCategory, "Occupied & Paid % (Compliance)"); ws.SetValue(renderRowIdx + 4, colIdx_HourlyCategory, "Occupied & Not Paid % (Non-Compliance"); ws.SetValue(renderRowIdx + 5, colIdx_HourlyCategory, "Arrivals"); ws.SetValue(renderRowIdx + 6, colIdx_HourlyCategory, "Departures"); ws.SetValue(renderRowIdx + 7, colIdx_HourlyCategory, "Payment Count"); ws.SetValue(renderRowIdx + 8, colIdx_HourlyCategory, "Zeroed Out Events"); ws.SetValue(renderRowIdx + 9, colIdx_HourlyCategory, "Total Zeroed Out Time"); ws.SetValue(renderRowIdx + 10, colIdx_HourlyCategory, "Violations Actioned"); ws.SetValue(renderRowIdx + 11, colIdx_HourlyCategory, "Total Violations"); ws.SetValue(renderRowIdx + 12, colIdx_HourlyCategory, "Total Enforced"); ws.SetValue(renderRowIdx + 13, colIdx_HourlyCategory, "Total Cautioned"); ws.SetValue(renderRowIdx + 14, colIdx_HourlyCategory, "Total Not Enforced"); ws.SetValue(renderRowIdx + 15, colIdx_HourlyCategory, "Total Faulty"); using (OfficeOpenXml.ExcelRange col = ws.Cells[renderRowIdx, colIdx_HourlyCategory, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory]) { col.Style.Font.Bold = true; } MergeCellRange(ws, renderRowIdx + 1, 1, renderRowIdx + (numberOfHourlyCategories - 1), colIdx_HourlyCategory - 1); } }