public Object GetData(IDataReader reader) { // This is where we define the mapping between the object properties and the // data columns. The convention that should be used is that the object property // names are exactly the same as the column names. However if there is some // compelling reason for the names to be different, the mapping can be defined here. // We assume the reader has data and is already on the row that contains the data // we need. We don't need to call read. As a general rule, assume that every field must // be null checked. If a field is null then the nullvalue for that field has already // been set by the DTO constructor, we don't have to change it. if (!_isInitialized) { InitializeMapper(reader); } // Now we can load the data into the DTO object from the DB reader AreaAsset dto = new AreaAsset(); if (!reader.IsDBNull(_ordinal_AreaID)) { dto.AreaID = Convert.ToInt32(reader[_ordinal_AreaID]); } if (!reader.IsDBNull(_ordinal_AreaName)) { dto.AreaName = Convert.ToString(reader[_ordinal_AreaName]); } if (!reader.IsDBNull(_ordinal_Description)) { dto.AreaDescription = Convert.ToString(reader[_ordinal_Description]); } return(dto); }
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()); }