private RegulatedHoursDetail GetRegulationRuleAtEventTime(SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent currentReportableEvent)
{
RegulatedHoursDetail result = null;
// Try to obtain the regulated hours applicable to this meter
RegulatedHoursGroup regulatedHours = this._ReportEngine.GetBestGroupForMeter(this._CustomerConfig.CustomerId, currentReportableEvent.BayInfo.AreaID_PreferLibertyBeforeInternal, currentReportableEvent.BayInfo.MeterID);
// If no regulated hour defintions came back, then we are unable to calculate any overstay violation, so just exit
if ((regulatedHours == null) || (regulatedHours.Details == null) || (regulatedHours.Details.Count == 0))
{
return(result);
}
DateTime ruleStart = DateTime.MinValue;
DateTime ruleEnd = DateTime.MinValue;
TimeSlot OccupiedSegment = new TimeSlot(currentReportableEvent.SensorEvent_Start, currentReportableEvent.SensorEvent_End);
// We need to check if this single occupancy event is an overstay violation for multiple rules, or even for more than one day, etc.
while (OccupiedSegment.Start < currentReportableEvent.SensorEvent_End)
{
// Determine the day of week that is involved
int dayOfWeek = (int)OccupiedSegment.Start.DayOfWeek;
// Loop through the daily rules and see which ones overlap with our occupied period
foreach (RegulatedHoursDetail detail in regulatedHours.Details)
{
// Skip this one if its for a different day of the week
if (detail.DayOfWeek != dayOfWeek)
{
continue;
}
// Determine if the occupied timeslot overlaps with the rule's timeslot
ruleStart = new DateTime(OccupiedSegment.Start.Year, OccupiedSegment.Start.Month, OccupiedSegment.Start.Day, detail.StartTime.Hour, detail.StartTime.Minute, 0);
ruleEnd = new DateTime(OccupiedSegment.Start.Year, OccupiedSegment.Start.Month, OccupiedSegment.Start.Day, detail.EndTime.Hour, detail.EndTime.Minute, 59);
TimeSlot RuleSegment = new TimeSlot(ruleStart, ruleEnd);
// We only care about this overlapping rule if the MaxStayMinutes is greater than zero (zero or less means there is no MaxStay that is enforced),
// or if it's explicitly set as a "No Parking" regulation
if ((RuleSegment.OverlapsWith(OccupiedSegment) == true) &&
((detail.MaxStayMinutes > 0) || (string.Compare(detail.Type, "No Parking", true) == 0))
)
{
result = detail;
return(result);
}
}
// Nothing more to do, so break out of loop!
break;
}
return(result);
}
protected void FindCurrentOverstayRule(SpaceStatusModel model)
{
// We need to try to find the regulation rule that is currently in effect at the customer's current timezone
// Resolve the associated area for the meter
int areaID = ResolveAreaIDForMeterID(model.MeterID);
RegulatedHoursGroupRepository.Repository = new RegulatedHoursGroupRepository();
// Try to obtain the regulated hours applicable to this meter
RegulatedHoursGroup regulatedHours = RegulatedHoursGroupRepository.Repository.GetBestGroupForMeter(this._CustomerConfig.CustomerId, areaID, model.MeterID);
// If no regulated hour defintions came back, then we are unable to calculate any overstay violation, so just exit
if ((regulatedHours == null) || (regulatedHours.Details == null) || (regulatedHours.Details.Count == 0))
{
return;
}
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);
// Determine the day of week that is involved
int dayOfWeek = (int)NowAtDestination.DayOfWeek;
// Loop through the daily rules and see which ones overlap with our occupied period
foreach (RegulatedHoursDetail detail in regulatedHours.Details)
{
// Skip this one if its for a different day of the week
if (detail.DayOfWeek != dayOfWeek)
{
continue;
}
TimeSlot OccupiedSegment = new TimeSlot(NowAtDestination);
// Determine if the occupied timeslot overlaps with the rule's timeslot
DateTime ruleStart = new DateTime(NowAtDestination.Year, NowAtDestination.Month, NowAtDestination.Day, detail.StartTime.Hour, detail.StartTime.Minute, 0);
DateTime ruleEnd = new DateTime(NowAtDestination.Year, NowAtDestination.Month, NowAtDestination.Day, detail.EndTime.Hour, detail.EndTime.Minute, 59);
TimeSlot RuleSegment = new TimeSlot(ruleStart, ruleEnd);
if (RuleSegment.OverlapsWith(OccupiedSegment) == true)
{
model.ActiveRegulationPeriod = detail;
break;
}
}
}
public static string GetFormEditDefaultsForDay(RegulatedHoursGroup group, DayOfWeek day, CustomerConfig customerCfg)
{
StringBuilder sb = new StringBuilder();
string delim = string.Empty;
sb.AppendLine("$(\"#itemDetails_" + day.ToString().Substring(0, 3) + "\").EnableMultiField({");
sb.AppendLine(" data:[");
foreach (RegulatedHoursDetail nextDetail in group.Details)
{
// Skip if its not for the day we are interested in
if (nextDetail.DayOfWeek != (int)day)
{
continue;
}
if (string.IsNullOrEmpty(delim))
{
delim = ",";
}
else
{
sb.AppendLine(delim);
}
sb.AppendLine(" {");
sb.AppendLine(" " + "detail_Type : " + "\"" + nextDetail.Type.ToString() + "\",");
sb.AppendLine(" " + "detail_StartTime : " + "\"" + nextDetail.StartTime.ToString("hh:mm:ss tt") + "\",");
sb.AppendLine(" " + "detail_EndTime : " + "\"" + nextDetail.EndTime.ToString("hh:mm:ss tt") + "\",");
sb.AppendLine(" " + "detail_DayOfWeek : " + "\"" + Convert.ToString((int)nextDetail.DayOfWeek) + "\",");
sb.AppendLine(" " + "detail_MaxStayMinutes : " + "\"" + nextDetail.MaxStayMinutes.ToString() + "\",");
sb.AppendLine(" " + "detail_RegulatedHoursDetailID : " + "\"" + nextDetail.RegulatedHoursDetailID.ToString() + "\"");
sb.Append(" }");
}
sb.AppendLine();
sb.AppendLine(" ],");
sb.AppendLine(" addEventCallback : function(newElem, clonnedFrom){commonCloneFormFieldsEvent(newElem, clonnedFrom);} });");
return(sb.ToString());
}
protected void AnalyzeSpaceStatusModelForOverstayViolation(SpaceStatusModel model)
{
// There is nothing to do if this event is not for occupied status
if ((model.BayOccupancyState == OccupancyState.Empty) || (model.BayOccupancyState == OccupancyState.NotAvailable) ||
(model.BayOccupancyState == OccupancyState.OutOfDate) || (model.BayOccupancyState == OccupancyState.Unknown))
{
return;
}
// Find the space asset associated with this data model. If the space is "inactive" (based on the "IsActive" column of "HousingMaster" table in database),
// then we will not consider the space to be in a violating state, because the sensor is effectively marked as bad/untrusted
SpaceAsset spcAsset = GetSpaceAsset(model.MeterID, model.BayID);
if (spcAsset != null)
{
// Nothing more to do if the space isn't active
if (spcAsset.IsActive == false)
{
return;
}
}
// Resolve the associated area for the meter
int areaID = ResolveAreaIDForMeterID(model.MeterID);
// Try to obtain the regulated hours applicable to this meter
RegulatedHoursGroup regulatedHours = RegulatedHoursGroupRepository.Repository.GetBestGroupForMeter(this._CustomerConfig.CustomerId, areaID, model.MeterID);
// If no regulated hour defintions came back, then we are unable to calculate any overstay violation, so just exit
if ((regulatedHours == null) || (regulatedHours.Details == null) || (regulatedHours.Details.Count == 0))
{
return;
}
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);
DateTime ruleStart = DateTime.MinValue;
DateTime ruleEnd = DateTime.MinValue;
TimeSlot OccupiedSegment = new TimeSlot(model.BayVehicleSensingTimestamp, NowAtDestination);
// We need to check if this single occupancy event is an overstay violation for multiple rules, or even for more than one day, etc.
while (OccupiedSegment.Start < NowAtDestination)
{
// Determine the day of week that is involved
int dayOfWeek = (int)OccupiedSegment.Start.DayOfWeek;
// Loop through the daily rules and see which ones overlap with our occupied period
foreach (RegulatedHoursDetail detail in regulatedHours.Details)
{
// Skip this one if its for a different day of the week
if (detail.DayOfWeek != dayOfWeek)
{
continue;
}
// Determine if the occupied timeslot overlaps with the rule's timeslot
ruleStart = new DateTime(OccupiedSegment.Start.Year, OccupiedSegment.Start.Month, OccupiedSegment.Start.Day, detail.StartTime.Hour, detail.StartTime.Minute, 0);
ruleEnd = new DateTime(OccupiedSegment.Start.Year, OccupiedSegment.Start.Month, OccupiedSegment.Start.Day, detail.EndTime.Hour, detail.EndTime.Minute, 59);
TimeSlot RuleSegment = new TimeSlot(ruleStart, ruleEnd);
// We only care about this overlapping rule if the MaxStayMinutes is greater than zero (zero or less means there is no MaxStay that is enforced),
// or if it's explicitly set as a "No Parking" regulation
if ((RuleSegment.OverlapsWith(OccupiedSegment) == true) &&
((detail.MaxStayMinutes > 0) || (string.Compare(detail.Type, "No Parking", true) == 0))
)
{
// Normally we will use the verbatim value of the max stay minutes, but if its a "No Parking", we will always take that to mean 0 minutes is the actual max
int timeLimitMinutes = detail.MaxStayMinutes;
if (string.Compare(detail.Type, "No Parking", true) == 0)
{
timeLimitMinutes = 0;
}
// Get the intersection of the overlaps so we know how long the vehicle has been occupied during this rule
TimeSlot OccupiedIntersection = RuleSegment.GetIntersection(OccupiedSegment);
// Determine if the vehicle has been occupied during this rule segment in excess of the MaxStayMinutes
if (OccupiedIntersection != null)
{
if (OccupiedIntersection.Duration.TotalMinutes >= timeLimitMinutes)
{
// We will check to see if this violated regulated period matches the current regulated period.
// But since it could be occupied for a long time, we must also check the current date, in addition to day of week and time of day!
bool currDetailMatchesCurrentRegulatedPeriod = false;
DateTime TodayAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName); //UtilityClasses.TimeZoneInfo.ConvertTimeZoneToTimeZone(DateTime.Now, this._CustomerConfig.ServerTimeZone, this._CustomerConfig.CustomerTimeZone).Date;
if (OccupiedSegment.Start >= TodayAtDestination)
{
if (model.ActiveRegulationPeriod != null)
{
RegulatedHoursDetailLogicalComparer comparer = new RegulatedHoursDetailLogicalComparer();
currDetailMatchesCurrentRegulatedPeriod = (comparer.Compare(model.ActiveRegulationPeriod, detail) == 0);
}
}
// Create a new Overstay Vio Info object and add to the overall list of violations
OverstayViolationInfo overstay = new OverstayViolationInfo();
overstay.IsForCurrentRegulationPeriod = currDetailMatchesCurrentRegulatedPeriod;
overstay.Regulation_Date = new DateTime(OccupiedSegment.Start.Year, OccupiedSegment.Start.Month, OccupiedSegment.Start.Day);
overstay.Regulation_DayOfWeek = detail.DayOfWeek;
overstay.Regulation_EndTime = detail.EndTime;
overstay.Regulation_MaxStayMinutes = detail.MaxStayMinutes; // Instead of using our calculated time limit, we will record the configured max stay minutes here, because it will be displayed
overstay.Regulation_StartTime = detail.StartTime;
overstay.Regulation_Type = detail.Type;
overstay.DateTime_StartOfOverstayViolation = new DateTime(OccupiedIntersection.Start.Ticks).AddMinutes(timeLimitMinutes);
overstay.DurationOfTimeBeyondStayLimits = new TimeSpan(OccupiedIntersection.Duration.Ticks).Add(new TimeSpan(0, (-1) * timeLimitMinutes, 0));
// Add this overstay info to the model's list
model.AllOverstayViolations.Add(overstay);
// If its also for the current regulation period, retain a reference to it
if (overstay.IsForCurrentRegulationPeriod == true)
{
model.CurrentOverstayViolation = overstay;
}
// Mark as "Overstay Violation" status if we have a current overstay violation. Otherwise mark
// as "Discretionary" because there is an overstay violation, but not for the current enforcement period
if (model.CurrentOverstayViolation != null)
{
model.BayEnforcementState = EnforcementState.OverstayViolation;
}
else
{
model.BayEnforcementState = EnforcementState.Discretionary;
}
}
}
}
}
// Rules for current day of week have been processed. So now we will advance to beginning of next day and see if there are more violations that we will use
// to add accumulated time in violation state...
OccupiedSegment = new TimeSlot(new DateTime(OccupiedSegment.Start.Year, OccupiedSegment.Start.Month, OccupiedSegment.Start.Day).AddDays(1),
NowAtDestination);
}
}
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());
}
