private static void SetResultCell(ExcelRange excelCell, Color color)
{
excelCell.Style.Fill.PatternType = ExcelFillStyle.Solid;
excelCell.Style.Fill.BackgroundColor.SetColor(color);
excelCell.Style.Font.Size = 12;
excelCell.Value = double.Parse(excelCell.Value.ToString().Replace(",", "."), CultureInfo.InvariantCulture);
}
private ExcelCell ResolveCell(EPPlus.ExcelRange native)
{
if (!_cells.ContainsKey(native))
{
_cells.Add(native, new EPPlusExcelCell(native));
}
return(_cells[native]);
}
protected void ApplyNumberStyleToColumn(ExcelWorksheet ws, int colIdx, int rowStartIdx, int rowEndIdx, string numberFormat, ExcelHorizontalAlignment horzAlign)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[rowStartIdx, colIdx, rowEndIdx, colIdx])
{
col.Style.Numberformat.Format = numberFormat;
col.Style.HorizontalAlignment = horzAlign;
}
}
public WorksheetXlsx(ExcelPackage package, int sheet_number)
{
if (package.Workbook.Worksheets.Count < sheet_number && sheet_number > 0)
{
throw new Exception("Sheet number is not valid");
}
cells = package.Workbook.Worksheets[sheet_number].Cells;
}
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);
}
}
protected IList<JamLineupModel> ProcessLineupRows(ExcelRange homeLineupStart, ExcelRange awayLineupStart, bool isFirstHalf)
{
List<JamLineupModel> list = new List<JamLineupModel>();
int currentRow = 1;
while (currentRow < 39)
{
string jamNumber = homeLineupStart.SubRange(currentRow, 1).Value == null ? null : homeLineupStart.SubRange(currentRow, 1).Value.ToString();
string nextJamNumber = null;
if (!string.IsNullOrEmpty(jamNumber))
{
// add jam
int number = Convert.ToInt32(jamNumber);
JamLineupModel jamLineup = new JamLineupModel();
jamLineup.IsFirstHalf = isFirstHalf;
jamLineup.JamNumber = number;
nextJamNumber = homeLineupStart.SubRange(currentRow + 1, 1).Value == null ? null : homeLineupStart.SubRange(currentRow + 1, 1).Value.ToString();
bool isSP = nextJamNumber == null ? false : nextJamNumber.Trim().ToLowerInvariant() == "sp";
jamLineup.HomeLineup = new List<PlayerLineupModel>();
bool hasPivot = homeLineupStart.SubRange(currentRow, 2).Value == null;
_dupePlayerCheck = new HashSet<string>();
// TODO: do error checking here
jamLineup.HomeLineup.Add(CreateJamPlayer(homeLineupStart.SubRange(currentRow, 3), true, false, isSP));
jamLineup.HomeLineup.Add(CreateJamPlayer(homeLineupStart.SubRange(currentRow, 7), false, hasPivot, isSP));
jamLineup.HomeLineup.Add(CreateJamPlayer(homeLineupStart.SubRange(currentRow, 11), false, false, isSP));
jamLineup.HomeLineup.Add(CreateJamPlayer(homeLineupStart.SubRange(currentRow, 15), false, false, isSP));
jamLineup.HomeLineup.Add(CreateJamPlayer(homeLineupStart.SubRange(currentRow, 19), false, false, isSP));
nextJamNumber = awayLineupStart.SubRange(currentRow + 1, 1).Value == null ? null : awayLineupStart.SubRange(currentRow + 1, 1).Value.ToString();
isSP = nextJamNumber == null ? false : nextJamNumber.Trim().ToLowerInvariant() == "sp";
jamLineup.AwayLineup = new List<PlayerLineupModel>();
hasPivot = awayLineupStart.SubRange(currentRow, 2).Value == null;
_dupePlayerCheck = new HashSet<string>();
// TODO: do error checking here
jamLineup.AwayLineup.Add(CreateJamPlayer(awayLineupStart.SubRange(currentRow, 3), true, false, isSP));
jamLineup.AwayLineup.Add(CreateJamPlayer(awayLineupStart.SubRange(currentRow, 7), false, hasPivot, isSP));
jamLineup.AwayLineup.Add(CreateJamPlayer(awayLineupStart.SubRange(currentRow, 11), false, false, isSP));
jamLineup.AwayLineup.Add(CreateJamPlayer(awayLineupStart.SubRange(currentRow, 15), false, false, isSP));
jamLineup.AwayLineup.Add(CreateJamPlayer(awayLineupStart.SubRange(currentRow, 19), false, false, isSP));
list.Add(jamLineup);
}
currentRow++;
while (nextJamNumber != null && (nextJamNumber.ToLower().Trim() == "sp" || nextJamNumber.ToLower().Trim() == "sp*"))
{
currentRow++;
var nextJam = homeLineupStart.SubRange(currentRow, 1).Value;
nextJamNumber = nextJam == null ? null : nextJam.ToString();
}
}
return list;
}
public static DateTime? ReadDateEmpty(ExcelRange cell)
{
if (cell.Value == null) return null;
long serialDate = 0;
if(long.TryParse(cell.Value.ToString(),out serialDate))
{
return DateTime.FromOADate(serialDate);
}
DateTime d1 = DateTime.MinValue;
if (DateTime.TryParse(cell.Value.ToString(), out d1)) return d1;
return null;
}
/// <summary>
/// </summary>
/// <param name="cell"></param>
/// <returns>如果空则返回0，如果非空且不是数字则返回0</returns>
public static decimal ReadDecimal(ExcelRange cell)
{
decimal d = 0;
try
{
if (cell.Value != null) return Convert.ToDecimal((double)(cell.Value)); ;
}
catch (Exception exp)
{
Debug.WriteLine("错误" + cell.Address + " " + exp.Message);
}
return d;
}
/// <summary>
/// Given a range, assumed to be a single cell,
/// return the contents as a string.
///
/// Return empty string if range is null or if
/// range's value is null.
///
/// </summary>
/// <param name="c"></param>
/// <returns></returns>
public static string OoXmlGetCellContents(OfficeOpenXml.ExcelRange c)
{
string retString;
if (c == null || c.Value == null)
{
retString = "";
}
else
{
retString = c.Value.ToString();
}
return(retString);
}
public static decimal? ReadDecimalEmpty(ExcelRange cell)
{
if (cell.GetType() == typeof(double))
{
return Convert.ToDecimal((double)(cell.Value));
}
else
{
decimal d = 0;
if(decimal.TryParse(cell.Value.ToString(),out d))
{
return d;
}
}
return null;
}
/// <summary>
/// Creates a black box around a certain range of cells.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="worksheet">The worksheet.</param>
private void BlackBoxRange(ExcelRange range, ExcelWorksheet worksheet)
{
var start = range.Start;
var end = range.End;
for (int i = start.Row; i <= end.Row; i++)
{
worksheet.Cells[i, start.Column].Style.Border.Left.Style = worksheet.Cells[i, end.Column].Style.Border.Right.Style = ExcelBorderStyle.Thin;
worksheet.Cells[i, start.Column].Style.Border.Left.Color.SetColor(Color.Black);
worksheet.Cells[i, end.Column].Style.Border.Right.Color.SetColor(Color.Black);
}
for (int i = start.Column; i <= end.Column; i++)
{
worksheet.Cells[start.Row, i].Style.Border.Top.Style = worksheet.Cells[end.Row, i].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
worksheet.Cells[start.Row, i].Style.Border.Top.Color.SetColor(Color.Black);
worksheet.Cells[end.Row, i].Style.Border.Bottom.Color.SetColor(Color.Black);
}
}
public static WorkCenter FromExcelRange(ExcelRange excelRange, int row)
{
var id = excelRange[row, 1].Text.Trim();
if (string.IsNullOrEmpty(id))
{
return null;
}
var prodLineCount = 0;
Int32.TryParse(excelRange[row, 2].Text.Trim(), out prodLineCount);
if (prodLineCount < 1)
{
return null;
}
var minDivQuantity = 0;
Int32.TryParse(excelRange[row, 3].Text.Trim(), out minDivQuantity);
if (minDivQuantity < 1)
{
return null;
}
var isActive = excelRange[row, 4].Text.Trim().ToUpperInvariant();
if (!IS_ACTIVE_REGEX.IsMatch(isActive))
{
return null;
}
return new WorkCenter()
{
Id = id,
ProdLineCount = prodLineCount,
MinDivQuantity = minDivQuantity
};
}
private static void CreateBoard(ExcelRange rng)
{
rng.Style.Fill.Gradient.Color1.SetColor(Color.FromArgb(0x80, 0x80, 0XFF));
rng.Style.Fill.Gradient.Color2.SetColor(Color.FromArgb(0x20, 0x20, 0XFF));
rng.Style.Fill.Gradient.Type = ExcelFillGradientType.None;
for (int col = 0; col <= rng.End.Column - rng.Start.Column; col++)
{
for (int row = 0; row <= rng.End.Row - rng.Start.Row; row++)
{
if (col % 4 == 0)
{
rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 45;
}
if (col % 4 == 1)
{
rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 70;
}
if (col % 4 == 2)
{
rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 110;
}
else
{
rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 135;
}
}
}
rng.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
rng.Style.Border.Top.Color.SetColor(Color.Gray);
rng.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
rng.Style.Border.Right.Color.SetColor(Color.Gray);
rng.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
rng.Style.Border.Left.Color.SetColor(Color.Gray);
rng.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
rng.Style.Border.Bottom.Color.SetColor(Color.Gray);
rng.Offset(0, 0, 1, rng.End.Column - rng.Start.Column+1).Style.Border.Top.Color.SetColor(Color.Black);
rng.Offset(0, 0, 1, rng.End.Column - rng.Start.Column + 1).Style.Border.Top.Style=ExcelBorderStyle.Medium;
int rows=rng.End.Row - rng.Start.Row;
rng.Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
}
private static void CreateBoard(ExcelRange rng)
{
//Create a gradiant background with one dark and one light blue color
rng.Style.Fill.Gradient.Color1.SetColor(Color.FromArgb(0x80, 0x80, 0XFF));
rng.Style.Fill.Gradient.Color2.SetColor(Color.FromArgb(0x20, 0x20, 0XFF));
rng.Style.Fill.Gradient.Type = ExcelFillGradientType.None;
for (int col = 0; col <= rng.End.Column - rng.Start.Column; col++)
{
for (int row = 0; row <= rng.End.Row - rng.Start.Row; row++)
{
if (col % 4 == 0)
{
rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 45;
}
if (col % 4 == 1)
{
rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 70;
}
if (col % 4 == 2)
{
rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 110;
}
else
{
rng.Offset(row, col, 1, 1).Style.Fill.Gradient.Degree = 135;
}
}
}
//Set the inner cell border to thin, light gray
rng.Style.Border.Top.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Top.Color.SetColor(Color.Gray);
rng.Style.Border.Right.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Right.Color.SetColor(Color.Gray);
rng.Style.Border.Left.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Left.Color.SetColor(Color.Gray);
rng.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
rng.Style.Border.Bottom.Color.SetColor(Color.Gray);
//Solid black border around the board.
rng.Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
}
private static void AddChart(ExcelRange rng,string name, string prefix)
{
var chrt = (ExcelPieChart)rng.Worksheet.Drawings.AddChart(name, eChartType.Pie);
chrt.SetPosition(rng.Start.Row-1, 0, rng.Start.Column-1, 0);
chrt.To.Row = rng.Start.Row+9;
chrt.To.Column = rng.Start.Column + 9;
chrt.Style = eChartStyle.Style18;
chrt.DataLabel.ShowPercent = true;
var serie = chrt.Series.Add(rng.Offset(2, 2, 1, 2), rng.Offset(1, 2, 1, 2));
serie.Header = "Hits";
chrt.Title.Text = "Hit ratio";
var n1 = rng.Worksheet.Names.Add(prefix + "Misses", rng.Offset(2, 2));
n1.Value = 0;
var n2 = rng.Worksheet.Names.Add(prefix + "Hits", rng.Offset(2, 3));
n2.Value = 0;
rng.Offset(1, 2).Value = "Misses";
rng.Offset(1, 3).Value = "Hits";
}
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());
}
protected override void RenderCommonHeader(ExcelWorksheet ws, int startRowIdx, int startColIdx, ref int colIdx_HourlyCategory)
{
int overallSummaryHeaderRowIdx = startRowIdx;
int renderRowIdx = startRowIdx;
int renderColIdx = startColIdx;
int colIdx_1stCommonColumn = startColIdx;
renderColIdx = colIdx_1stCommonColumn;
ws.SetValue(renderRowIdx, renderColIdx, "Occupied Duration"); // Column 1
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Max Possible" + Environment.NewLine + "Occupied Duration"); // Column 2
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Occupied %"); // Column 3
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Vehicle" + Environment.NewLine + "Arrivals"); // Column 4
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 12;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Vehicle" + Environment.NewLine + "Departures"); // Column 5
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 12;
// Format current portion of the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[renderRowIdx, colIdx_1stCommonColumn, renderRowIdx, renderColIdx])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
if (this._ReportParams.IncludeHourlyStatistics == true)
{
renderColIdx++;
colIdx_HourlyCategory = renderColIdx; // Retain this column index as the known column index for hourly category
ws.SetValue(renderRowIdx, renderColIdx, "Hourly Category"); // Column 6
ws.Column(renderColIdx).Width = 24;
// Format hourly category portion of the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[renderRowIdx, renderColIdx, renderRowIdx, renderColIdx])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.OliveDrab);
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
rng.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
renderColIdx++; // Column 7 is start of hourly items (Midnight hour)
DateTime tempHourlyTime = DateTime.Today;
DateTime tempHourlyTime2 = DateTime.Today.AddHours(1);
for (int hourlyIdx = 0; hourlyIdx < 24; hourlyIdx++)
{
ws.SetValue(renderRowIdx, renderColIdx + hourlyIdx, tempHourlyTime.ToString("h ") + "-" + tempHourlyTime2.ToString(" h tt").ToLower());
tempHourlyTime = tempHourlyTime.AddHours(1);
tempHourlyTime2 = tempHourlyTime2.AddHours(1);
ws.Column(renderColIdx + hourlyIdx).Width = 14;
}
// Format hourly portion of the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[renderRowIdx, renderColIdx, renderRowIdx, renderColIdx + 23])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkSlateBlue);
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
rng.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
}
/// <summary>
/// Writes data to the currently selected sheet starting at the
/// currently selected cell.
/// </summary>
/// <param name="data">The data as an enumeration of rows wher each row is an enumeration of values.</param>
/// <param name="translateFormulas">Whether to consider string values starting with "=" as A1 formula's and string values starting with "/=" as R1C1 formulas.</param>
/// <param name="headerRows">Number of rows to style as headers. Does only work for new documents.</param>
/// <param name="headerColumns">Number of columns to style as headers. Does only work for new documents.</param>
public void Write(IEnumerable<IEnumerable<Object>> data, bool translateFormulas = false, int headerRows = 0, int headerColumns = 0)
{
var offsetrow = this.Selection.Start.Row;
var offsetcol = this.Selection.Start.Column;
var row = 0;
foreach (var datarow in data)
{
var col = 0;
foreach (var value in datarow)
{
var cell = this.CurrentSheet.Cells[offsetrow + row, offsetcol + col];
if (value is String)
{
var svalue = (string)value;
if (translateFormulas && svalue.StartsWith("/="))
{
cell.FormulaR1C1 = svalue.Substring(2);
}
else if (translateFormulas && svalue.StartsWith("="))
{
cell.Formula = svalue.Substring(1);
}
else
{
cell.Value = svalue;
}
}
else if (value is DateTime)
{
var dvalue = (DateTime)value;
cell.Value = dvalue;
if (IsNewDocument)
{
if (dvalue.Date == dvalue)
cell.Style.Numberformat.Format = "yyyy/MM/dd";
else
cell.Style.Numberformat.Format = "yyyy/MM/dd HH:mm:ss";
}
}
else if (value is Uri)
{
var uvalue = (Uri)value;
cell.Value = uvalue.ToString();
cell.Hyperlink = uvalue;
}
else if (value == null)
{
cell.Value = null;
}
else if (value is DBNull)
{
cell.Value = null;
}
else
{
cell.Value = value;
}
if (IsNewDocument && ( row < headerRows || col < headerColumns))
{
cell.StyleName = "Header";
}
col++;
}
row++;
}
this.Selection = this.CurrentSheet.Cells[offsetrow + row, offsetcol];
}
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 static string ReadString(ExcelRange cell)
{
if (cell.Value != null) return cell.Value.ToString().Trim();
return "";
}
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());
}
private void setCellDisabled(ref ExcelRange cell)
{
cell.Style.Fill.PatternType = ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
}
private static void HeaderStyles(ExcelWorksheet ws, ExcelRange cell)
{
cell.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
cell.Style.Font.Bold = true;
cell.Style.Locked = true;
ws.View.FreezePanes(2, 1);
ws.Row(1).Height = ExcelConstants.DefaultRowHeight;
}
/// <summary>
/// Formats the export value.
/// </summary>
/// <param name="range">The range.</param>
/// <param name="exportValue">The export value.</param>
public static void SetExcelValue( ExcelRange range, object exportValue )
{
if ( exportValue != null &&
( exportValue is decimal || exportValue is decimal? ||
exportValue is int || exportValue is int? ||
exportValue is short || exportValue is short? ||
exportValue is long || exportValue is long? ||
exportValue is double || exportValue is double? ||
exportValue is float || exportValue is float? ||
exportValue is DateTime || exportValue is DateTime? ) )
{
range.Value = exportValue;
}
else
{
string value = exportValue != null ? exportValue.ToString().ConvertBrToCrLf().Replace( " ", " " ).TrimEnd( '\r', '\n' ) : string.Empty;
range.Value = value;
if ( value.Contains( Environment.NewLine ) )
{
range.Style.WrapText = true;
}
}
}
public List <ExcelData> getAllExcelData([FromUri] string filename)
{
string conStr = "", Extension = ".xlsx";
// uploadSharePointFileFunction();
switch (Extension)
{
case ".xls": //Excel 97-03
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
.ConnectionString;
break;
case ".xlsx": //Excel 07
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
break;
}
conStr = String.Format(conStr, Path.Combine(HttpContext.Current.Server.MapPath("~/CSV/CurrentFile"), filename));
OleDbConnection connExcel = new OleDbConnection(conStr);
string Roadmapsheet = "Official Roadmap$";
string Roadmapcolor = "Color$";
string Roadmapresource = "ProgramOverview$";
ExcelRoadMapdata excelRoadMapdata;
ExcelColordata excelColordata;
ExcelResourcedata excelResourcedata;
ExcelData excelData;
List <ExcelRoadMapdata> lstExcelRoadMapdata = new List <ExcelRoadMapdata>();
List <ExcelColordata> lstExcelColordata = new List <ExcelColordata>();
List <ExcelResourcedata> lstExcelResourcedata = new List <ExcelResourcedata>();
List <ExcelData> lstExcelData = new List <ExcelData>();
try
{
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
System.Data.DataTable dt = new System.Data.DataTable();
System.Data.DataTable dtRoadmap = new System.Data.DataTable();
System.Data.DataTable dtColor = new System.Data.DataTable();
System.Data.DataTable dtResource = new System.Data.DataTable();
cmdExcel.Connection = connExcel;
//Get the name of First Sheet
//Read Data from First Sheet
connExcel.Open();
// cmdExcel.CommandText = "SELECT * From [" + Roadmapsheet + "A11:F197" + "] ORDER BY 5";
cmdExcel.CommandText = "SELECT * From [" + Roadmapsheet + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dtRoadmap);
cmdExcel.CommandText = "SELECT * From [" + Roadmapcolor + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dtColor);
cmdExcel.CommandText = "SELECT * From [" + Roadmapresource + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dtResource);
connExcel.Close();
var directory = Path.Combine(HttpContext.Current.Server.MapPath("~/CSV/CurrentFile"));
DirectoryInfo d = new DirectoryInfo(directory);//Assuming Test is your Folder
FileInfo[] file = d.GetFiles("Global_IT_Roadmap.xlsx");
FileInfo excelfile = file[0];
ExcelPackage xlPackage = new ExcelPackage(excelfile, false);
ExcelWorksheet objSht = xlPackage.Workbook.Worksheets["Color"];
int maxRow = dtColor.Rows.Count + 1;
int maxCol = 6;
OfficeOpenXml.ExcelRange range = objSht.Cells[1, 1, maxRow, maxCol];
for (int i = 2; i <= maxRow; i++)
{
string color = range[i, 2].Style.Fill.BackgroundColor.Rgb;
excelColordata = new ExcelColordata();
excelColordata.program_name = Convert.ToString(range[i, 1].Value).Trim();
excelColordata.project_color = range[i, 2].Style.Fill.BackgroundColor.Rgb;
excelColordata.region_name = Convert.ToString(range[i, 3].Value).Trim();
excelColordata.region_color = range[i, 4].Style.Fill.BackgroundColor.Rgb;
excelColordata.resource_name = Convert.ToString(range[i, 5].Value).Trim();
excelColordata.resource_color = range[i, 6].Style.Fill.BackgroundColor.Rgb;
lstExcelColordata.Add(excelColordata);
}
foreach (DataRow dr in dtResource.Rows)
{
excelResourcedata = new ExcelResourcedata();
excelResourcedata.program_name = Convert.ToString(dr[0]).Trim();
excelResourcedata.resource_name = Convert.ToString(dr[3]).Trim();
lstExcelResourcedata.Add(excelResourcedata);
if (Convert.ToString(dr[6]) != "")
{
excelResourcedata = new ExcelResourcedata();
excelResourcedata.program_name = Convert.ToString(dr[0]).Trim();
excelResourcedata.resource_name = Convert.ToString(dr[6]).Trim();
lstExcelResourcedata.Add(excelResourcedata);
}
}
for (int i = 0; i < 9; i++)
{
DataRow row = dtRoadmap.Rows[0];
dtRoadmap.Rows.Remove(row);
}
foreach (DataRow dr in dtRoadmap.Rows)
{
if (dr[0].ToString() == "")
{
break;
}
else
{
var resourcelist = lstExcelResourcedata.Where(x => x.program_name == Convert.ToString(dr[0]).Trim())
.ToList();
for (int i = 0; i < resourcelist.Count; i++)
{
excelRoadMapdata = new ExcelRoadMapdata();
excelRoadMapdata.program_name = Convert.ToString(dr[0]).Trim();
excelRoadMapdata.region_name = Convert.ToString(dr[2]).Trim();;
excelRoadMapdata.country_name = Convert.ToString(dr[3]).Trim();
excelRoadMapdata.start_date = (Convert.ToDateTime(Convert.ToString(dr[4]).Trim().Replace('/', '-'))).ToString("dd-MM-yyyy");
excelRoadMapdata.end_date = (Convert.ToDateTime(Convert.ToString(dr[5]).Trim().Replace('/', '-'))).ToString("dd-MM-yyyy"); //excelRoadMapdata.start_date = Convert.ToString(dr[4]).Trim().Replace('/', '-');
// excelRoadMapdata.start_date = Convert.ToString(dr[4]).Trim().Replace('/', '-');
// excelRoadMapdata.end_date = Convert.ToString(dr[5]).Trim().Replace('/', '-');
excelRoadMapdata.resource_name = Convert.ToString(resourcelist[i].resource_name.Trim());;
lstExcelRoadMapdata.Add(excelRoadMapdata);
}
}
}
excelData = new ExcelData();
lstExcelRoadMapdata = lstExcelRoadMapdata.OrderBy(o => Convert.ToDateTime(o.start_date)).ToList();
excelData.excelRoadMapdata = lstExcelRoadMapdata;
excelData.excelColordata = lstExcelColordata;
lstExcelData.Add(excelData);
//excelData = new ExcelData();
// lstExcelData.Add(excelData);
}
catch (Exception ex)
{
excelData = new ExcelData();
excelData.excelColordata = lstExcelColordata;
excelRoadMapdata = new ExcelRoadMapdata();
excelRoadMapdata.program_name = ex.ToString();
lstExcelRoadMapdata.Add(excelRoadMapdata);
excelData.excelRoadMapdata = lstExcelRoadMapdata;
lstExcelData.Add(excelData);
connExcel.Close();
}
return(lstExcelData);
}
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="cell">The native representation of this cell.</param>
public EPPlusExcelCell(EPPlus.ExcelRange cell)
{
_cell = cell;
}
protected override void RenderCommonHeader(ExcelWorksheet ws, int startRowIdx, int startColIdx, ref int colIdx_HourlyCategory)
{
int overallSummaryHeaderRowIdx = startRowIdx;
int renderRowIdx = startRowIdx;
int renderColIdx = startColIdx;
int colIdx_1stCommonColumn = startColIdx;
renderColIdx = colIdx_1stCommonColumn;
ws.SetValue(renderRowIdx, renderColIdx, "Overstay" + Environment.NewLine + "Violations"); // Column 1
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Payment" + Environment.NewLine + "Violations"); // Column 2
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total" + Environment.NewLine + "Violations"); // Column 3
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total" + Environment.NewLine + "Actioned"); // Column 4
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total" + Environment.NewLine + "Enforced"); // Column 5
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total" + Environment.NewLine + "Cautioned"); // Column 6
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total" + Environment.NewLine + "Not Enforced"); // Column 7
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total" + Environment.NewLine + "Faulty"); // Column 8
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total Actioned" + Environment.NewLine + "< 15 Minutes"); // Column 9
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total Actioned" + Environment.NewLine + "15 - 40 Minutes"); // Column 10
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total Actioned" + Environment.NewLine + "> 40 Minutes"); // Column 11
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
renderColIdx++;
ws.SetValue(renderRowIdx, renderColIdx, "Total Missed" + Environment.NewLine + "(Unactioned)"); // Column 12
ApplyWrapTextStyleToCell(ws, renderRowIdx, renderColIdx);
ws.Column(renderColIdx).Width = 20;
// Format current portion of the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[renderRowIdx, colIdx_1stCommonColumn, renderRowIdx, renderColIdx])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
if (this._ReportParams.IncludeHourlyStatistics == true)
{
renderColIdx++;
colIdx_HourlyCategory = renderColIdx; // Retain this column index as the known column index for hourly category
ws.SetValue(renderRowIdx, renderColIdx, "Hourly Category"); // Column 13
ws.Column(renderColIdx).Width = 35;
// Format hourly category portion of the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[renderRowIdx, renderColIdx, renderRowIdx, renderColIdx])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.OliveDrab);
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
rng.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
renderColIdx++; // Column 15 is start of hourly items (Midnight hour)
DateTime tempHourlyTime = DateTime.Today;
DateTime tempHourlyTime2 = DateTime.Today.AddHours(1);
for (int hourlyIdx = 0; hourlyIdx < 24; hourlyIdx++)
{
ws.SetValue(renderRowIdx, renderColIdx + hourlyIdx, tempHourlyTime.ToString("h ") + "-" + tempHourlyTime2.ToString(" h tt").ToLower());
tempHourlyTime = tempHourlyTime.AddHours(1);
tempHourlyTime2 = tempHourlyTime2.AddHours(1);
ws.Column(renderColIdx + hourlyIdx).Width = 14;
}
// Format hourly portion of the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[renderRowIdx, renderColIdx, renderRowIdx, renderColIdx + 23])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DarkSlateBlue);
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
rng.Style.VerticalAlignment = ExcelVerticalAlignment.Top;
}
}
}
private void setCellPriceFormat(ref ExcelRange cell)
{
cell.Style.Numberformat.Format = "# ##0.00 ";
}
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());
}
private void setCellPriceTop(ref ExcelRange cell)
{
setCellPriceMiddle(ref cell);
cell.Style.Border.Top.Style = ExcelBorderStyle.Thin;
}
public void GetLatencyDataXLS(int PaymentCustomerId, List <int> listOfMeterIDs, DateTime StartTime, DateTime EndTime, MemoryStream ms)
{
SensingDatabaseSource Sensing = new SensingDatabaseSource(_CustomerConfig);
List <HistoricalSensingRecord> sensingData = Sensing.GetHistoricalVehicleSensingDataForMeters_StronglyTyped(_CustomerConfig.CustomerId, listOfMeterIDs, StartTime, EndTime, true);
DataSet dsSensingData = new DataSet();
DataTable dtVSData = new DataTable();
dtVSData.TableName = "All";
dtVSData.Columns.Add("MeterID", typeof(System.Int32));
dtVSData.Columns.Add("BayID", typeof(System.Int32));
dtVSData.Columns.Add("EventDateTime", typeof(System.DateTime));
dtVSData.Columns.Add("RecCreationDate", typeof(System.DateTime));
dtVSData.Columns.Add("LatencyAsSeconds", typeof(System.Int32));
dtVSData.Columns.Add("LatencyAsHMS", typeof(System.String));
dtVSData.Columns.Add("Occupied", typeof(System.Int32));
dsSensingData.Tables.Add(dtVSData);
Dictionary <string, VSLatency> sheetLatencies = new Dictionary <string, VSLatency>();
foreach (HistoricalSensingRecord nextVSData in sensingData)
{
DateTime RecCreationDate_ClientTimeZone = nextVSData.RecCreationDateTime;
// DEBUG: Not really sure if the RecCreationDateTime is stored in client or server timezone...
/*RecCreationDate_ClientTimeZone = UtilityClasses.TimeZoneInfo.ConvertTimeZoneToTimeZone(RecCreationDate_ClientTimeZone, _CustomerConfig.ServerTimeZone, _CustomerConfig.CustomerTimeZone);*/
TimeSpan latency = RecCreationDate_ClientTimeZone - nextVSData.DateTime;
VSLatency currSheetLatency = null;
// Get or create latency container for All spaces
if (sheetLatencies.ContainsKey("All"))
{
currSheetLatency = sheetLatencies["All"];
}
else
{
currSheetLatency = new VSLatency();
sheetLatencies.Add("All", currSheetLatency);
}
// Add the latency to the list
currSheetLatency.LatenciesInSeconds.Add(Convert.ToInt32(Math.Abs(latency.TotalSeconds)));
// Add info to the "All" table
DataRow dr = dtVSData.NewRow();
dtVSData.Rows.Add(dr);
dr["MeterID"] = nextVSData.MeterMappingId;
dr["BayID"] = nextVSData.BayId;
dr["EventDateTime"] = nextVSData.DateTime;
dr["RecCreationDate"] = RecCreationDate_ClientTimeZone;
dr["LatencyAsSeconds"] = Convert.ToInt32(Math.Abs(latency.TotalSeconds));
dr["LatencyAsHMS"] = Math.Abs(latency.Hours).ToString().PadLeft(2, '0') +
":" + Math.Abs(latency.Minutes).ToString().PadLeft(2, '0') +
":" + Math.Abs(latency.Seconds).ToString().PadLeft(2, '0') +
"." + Math.Abs(latency.Milliseconds).ToString();
dr["Occupied"] = Convert.ToInt32(nextVSData.IsOccupied);
// Then add info to a space-specific table
DataTable dtSpaceTable = null;
if (dsSensingData.Tables.IndexOf("Space" + nextVSData.BayId.ToString()) != -1)
{
dtSpaceTable = dsSensingData.Tables["Space" + nextVSData.BayId.ToString()];
}
else
{
dtSpaceTable = new DataTable();
dtSpaceTable.TableName = "Space" + nextVSData.BayId.ToString();
dtSpaceTable.Columns.Add("MeterID", typeof(System.Int32));
dtSpaceTable.Columns.Add("BayID", typeof(System.Int32));
dtSpaceTable.Columns.Add("EventDateTime", typeof(System.DateTime));
dtSpaceTable.Columns.Add("RecCreationDate", typeof(System.DateTime));
dtSpaceTable.Columns.Add("LatencyAsSeconds", typeof(System.Int32));
dtSpaceTable.Columns.Add("LatencyAsHMS", typeof(System.String));
dtSpaceTable.Columns.Add("Occupied", typeof(System.Int32));
dsSensingData.Tables.Add(dtSpaceTable);
}
// Get or create latency container for current space
if (sheetLatencies.ContainsKey("Space" + nextVSData.BayId.ToString()))
{
currSheetLatency = sheetLatencies["Space" + nextVSData.BayId.ToString()];
}
else
{
currSheetLatency = new VSLatency();
sheetLatencies.Add("Space" + nextVSData.BayId.ToString(), currSheetLatency);
}
// Add the latency to the list
currSheetLatency.LatenciesInSeconds.Add(Convert.ToInt32(Math.Abs(latency.TotalSeconds)));
dr = dtSpaceTable.NewRow();
dtSpaceTable.Rows.Add(dr);
dr["MeterID"] = nextVSData.MeterMappingId;
dr["BayID"] = nextVSData.BayId;
dr["EventDateTime"] = nextVSData.DateTime;
dr["RecCreationDate"] = RecCreationDate_ClientTimeZone;
dr["LatencyAsSeconds"] = Convert.ToInt32(Math.Abs(latency.TotalSeconds));
dr["LatencyAsHMS"] = Math.Abs(latency.Hours).ToString().PadLeft(2, '0') +
":" + Math.Abs(latency.Minutes).ToString().PadLeft(2, '0') +
":" + Math.Abs(latency.Seconds).ToString().PadLeft(2, '0') +
"." + Math.Abs(latency.Milliseconds).ToString();
dr["Occupied"] = Convert.ToInt32(nextVSData.IsOccupied);
}
dsSensingData.AcceptChanges();
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
foreach (DataTable nextTable in dsSensingData.Tables)
{
//Create the worksheet
OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add(nextTable.TableName);
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(nextTable, true);
//Format the header for column 1-3
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, nextTable.Columns.Count /*"A1:C1"*/])
{
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);
}
//Example how to Format Column 1 as numeric
using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 1, 2 + nextTable.Rows.Count, 1])
{
col.Style.Numberformat.Format = "########0";
col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 2, 2 + nextTable.Rows.Count, 2])
{
col.Style.Numberformat.Format = "########0";
col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
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;
}
ws.Column(4).Width = 20;
using (OfficeOpenXml.ExcelRange col = ws.Cells[2, 4, 2 + nextTable.Rows.Count, 4])
{
col.Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss tt";
col.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
}
ws.Column(5).Width = 20;
ws.Column(6).Width = 28;
// Now lets add aggregate data at the end of the sheet
if (sheetLatencies.ContainsKey(nextTable.TableName))
{
VSLatency currSheetLatenies = sheetLatencies[nextTable.TableName];
if (currSheetLatenies != null)
{
int colCount = nextTable.Columns.Count;
int rowCount = nextTable.Rows.Count;
// Average Latency
// Set cell value, then merge cells on same row
string cellValue = "Average Latency: " + currSheetLatenies.GetAverageLatency().ToString() + " seconds";
ws.Cells[rowCount + 3, 1].Value = cellValue;
ws.Cells[rowCount + 3, 1, rowCount + 3, colCount].Merge = true; //Merge columns start and end range
ws.Cells[rowCount + 3, 1, rowCount + 3, colCount].Style.Font.Bold = true; //Font should be bold
ws.Cells[rowCount + 3, 1, rowCount + 3, colCount].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; // Aligmnet is center
// Minimum Latency
// Set cell value, then merge cells on same row
cellValue = "Minimum Latency: " + currSheetLatenies.GetMinLatency().ToString() + " seconds";
ws.Cells[rowCount + 4, 1].Value = cellValue;
ws.Cells[rowCount + 4, 1, rowCount + 4, colCount].Merge = true; //Merge columns start and end range
ws.Cells[rowCount + 4, 1, rowCount + 4, colCount].Style.Font.Bold = true; //Font should be bold
ws.Cells[rowCount + 4, 1, rowCount + 4, colCount].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; // Aligmnet is center
// Maximum Latency
// Set cell value, then merge cells on same row
cellValue = "Maximum Latency: " + currSheetLatenies.GetMaxLatency().ToString() + " seconds";
ws.Cells[rowCount + 5, 1].Value = cellValue;
ws.Cells[rowCount + 5, 1, rowCount + 5, colCount].Merge = true; //Merge columns start and end range
ws.Cells[rowCount + 5, 1, rowCount + 5, colCount].Style.Font.Bold = true; //Font should be bold
ws.Cells[rowCount + 5, 1, rowCount + 5, colCount].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; // Aligmnet is center
}
}
}
byte[] bytes = pck.GetAsByteArray();
ms.Write(bytes, 0, bytes.Length);
}
dsSensingData.Dispose();
}
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);
}
}
/// <summary>
/// Sets styles for filling cells
/// </summary>
private void SetColorAndFillPattern(ExcelRange range, int red, int green, int blue)
{
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(red, green, blue));
}
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());
}
private static void SetFontColorCell(ExcelRange cell, Color color)
{
cell.Style.Font.Color.SetColor(color);
}
private static void SetBorderCell(ExcelRange cell)
{
var border = cell.Style.Border;
border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
}
private static void AgregarSiExiste(ExcelRange cell, XmlNode node)
{
if (node == null)
{
return;
}
decimal value = 0;
if (decimal.TryParse(node.Value, out value))
{
cell.Value = value;
}
else
{
cell.Value = node.Value;
}
}
private static void CreateHeaderStyle(ExcelRange cell)
{
cell.Style.Font.Bold = true;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
cell.Style.Font.Size = 18;
cell.Style.Font.Name = "Arial Black";
}
private void setCellBoxed(ref ExcelRange cell)
{
setCellPriceTop(ref cell);
setCellPriceBottom(ref cell);
}
private static void CreateHeaderStyle2(ExcelRange cell)
{
cell.Style.Font.Bold = true;
cell.Style.Font.Size = 12;
cell.Style.Font.Name = "Arial Black";
}
private void setCellPriceBottom(ref ExcelRange cell)
{
setCellPriceMiddle(ref cell);
cell.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
/// <summary>
/// Default constructor.
/// </summary>
/// <param name="cell">The native representation of this cell.</param>
public EPPlusExcelCell(EPPlus.ExcelRange cell)
{
_cell = cell;
}
private void setCellPriceMiddle(ref ExcelRange cell)
{
cell.Style.Border.Right.Style = ExcelBorderStyle.Thin;
cell.Style.Border.Left.Style = ExcelBorderStyle.Thin;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.General;
}
/// <summary>
/// Selects the given sheet and cell.
/// If no sheet with the given name is found, one is created.
/// </summary>
public void Select(string sheetName, string cellAddress)
{
this.CurrentSheet = this.Package.Workbook.Worksheets[sheetName];
if (this.CurrentSheet == null)
{
this.CurrentSheet = this.Package.Workbook.Worksheets.Add(sheetName);
}
this.Selection = this.CurrentSheet.Cells[cellAddress];
}
private void setCellStandard(ref ExcelRange cell)
{
cell.Style.Font.Bold = false;
cell.Style.Border.Bottom.Style = ExcelBorderStyle.None;
cell.Style.Border.Top.Style = ExcelBorderStyle.None;
cell.Style.Border.Left.Style = ExcelBorderStyle.None;
cell.Style.Border.Right.Style = ExcelBorderStyle.None;
cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.General;
cell.Style.Fill.PatternType = ExcelFillStyle.None;
}
public Dictionary <string, Exception> ExcelToSQL(string fullPath) // bool isHeader
{
#region Reference Url
// https://github.com/JanKallman/EPPlus
// http://ccsig.blogspot.com/2017/10/c-epplus-excel-xlsx.html
// http://smile0103.blogspot.com/2014/02/excel_330.html
// https://stackoverflow.com/questions/16828222/checking-the-background-color-of-excel-file-using-epplus-library
#endregion
bool isHeader = false;
List <List <string> > ExcelData = new List <List <string> >();
#region Read Excel
//Dictionary<string, string> ExcelData = new Dictionary<string, string>();
using (FileStream fs = new FileStream(fullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
using (ExcelPackage excelPkg = new ExcelPackage(fs))
{
ExcelWorksheet sheet = excelPkg.Workbook.Worksheets[1]; //取得Sheet1
int startRowIndex = sheet.Dimension.Start.Row; //起始列
int endRowIndex = sheet.Dimension.End.Row; //結束列
int startColumn = sheet.Dimension.Start.Column; //開始欄
int endColumn = 10; //sheet.Dimension.End.Column;//結束欄
if (isHeader) //有包含標題
{
startRowIndex += 1;
}
for (int currentRow = startRowIndex; currentRow <= endRowIndex; currentRow++)
{
//抓出當前的資料範圍
OfficeOpenXml.ExcelRange range = sheet.Cells[currentRow, startColumn, currentRow, endColumn];
//全部儲存格是完全空白時則跳過
if (range.Any(c => !string.IsNullOrEmpty(c.Text)) == false)
{
continue;//略過此列
}
else
{
List <string> tmp_row = new List <string>();
string FirstCellColor = range.FirstOrDefault().Style.Fill.BackgroundColor.Rgb;
tmp_row.Add(FirstCellColor);
for (int currentColumn = startColumn; currentColumn <= endColumn; currentColumn++)
{
tmp_row.Add(sheet.Cells[currentRow, currentColumn].Text.ToUpper());
}
ExcelData.Add(tmp_row);
if (sheet.Cells[currentRow, 1].Text == "資料表名稱")
{
currentRow += 2;
}
}
}
}
}
#endregion
#region ToMsql
#region 功能 - 對應色碼(FF + R,G,B)
// Title - FFFFCC99
// Create - FF00B050
// Edit - FF00B0F0
// Delete - FFFF0000
// EndLine - FFFFFF00
#endregion
#region ExcelData 索引定義
//異動色碼 0
//異動色塊 1
//項目名稱 2
//欄位名稱 3
//欄位型態 4
//PK 5
//FK 6
//NULL 7
//唯一 8
//必填 9
//備註說明 10
#endregion
ConnectDB <dynamic> MssqlDB = new ConnectDB <dynamic>();
string Check_Host = Globals.HOST;
//MSSQLRepository<dynamic> MssqlDB1 = new MSSQLRepository<dynamic>();
string Sql = "";
string table_name = "";
string Tcomm_Sql = "";
string Primary_key = "";
string PK_Sql = "";
string Mode = "create";
string color = "";
string edit_data = "";
bool table_status = false;
int Table_Num = 0;
int table_no = 0;
int TmpNum = 0;
int index = 0;
List <dynamic> OLD_Table = new List <dynamic>();
Dictionary <string, Exception> Error = new Dictionary <string, Exception>();
for (int i = 0; i < ExcelData.Count; i++)
{
if (ExcelData[i][1] == "資料表名稱")
{
Sql = "";
table_name = "";
Tcomm_Sql = "";
Primary_key = "";
PK_Sql = "";
table_name = ExcelData[i][2];
Table_Num += 1;
index += 1;
try { table_status = int.Parse(MssqlDB.GetAlls <string>("SELECT COUNT(*) FROM " + table_name, Check_Host).FirstOrDefault()) >= 0; }
catch { table_status = false; }
if (table_status == false)
{
Mode = "create";
Sql = "CREATE TABLE " + ExcelData[i][2] + "(";
if (ExcelData[i][3] != "")
{
Tcomm_Sql = string.Format("exec sp_addextendedproperty 'MS_Description', '{0}', 'user', 'dbo', 'table', '{1}'", ExcelData[i][3], table_name);
}
}
else
{
Mode = "update";
OLD_Table = MssqlDB.GetAlls <dynamic>(string.Format(" SELECT COLUMN_NAME, " +
" (UPPER(DATA_TYPE) + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN '' ELSE '(' + CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) + ')' END) DATA_TYPE, " +
" (CASE WHEN IS_NULLABLE = 'NO' THEN '' ELSE 'V' END) IS_NULLABLE " + // ,COLUMN_DEFAULT
" FROM abc.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'", table_name), Check_Host).ToList();
// 刪除原有PK
PK_Sql += int.Parse(MssqlDB.GetAlls <string>("SELECT COUNT(*) FROM abc.INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE WHERE TABLE_NAME = '" + table_name + "'", Check_Host).FirstOrDefault()) >= 1 ?
string.Format(" ALTER TABLE {0} DROP {0}_PK ;\n", table_name) : "";
}
continue;
}
Primary_key += ExcelData[i][5] != "" ? ExcelData[i][3] + "," : "";
if (ExcelData[i][0] == "FFFFFF00" && ExcelData[i][1] != "資料表名稱")
{
// Create Table
Sql = Sql.Substring(0, 1) == "C" ? Sql.Substring(0, Sql.Length - 1) + ")":Sql;
// Create Primary key
if (Primary_key != "")
{
PK_Sql += string.Format("ALTER TABLE [dbo].[{0}] ADD CONSTRAINT [{0}_PK] PRIMARY KEY (", table_name) + Primary_key.Substring(0, Primary_key.Length - 1) + ")";
}
// Column comment
try
{
richTextBox1.Text += "--" + Mode.ToUpper() + " Table-" + table_name + "\n";
if (Globals.Cr_Ta_mode)
{
richTextBox1.Text += Sql + "\n";
richTextBox1.Text += "--新增Primary_key:\n";
richTextBox1.Text += PK_Sql + "\n";
richTextBox1.Text += "--新增Table註解:\n";
richTextBox1.Text += Tcomm_Sql + "\n";
}
richTextBox1.Text += string.Concat("----------------------", Table_Num.ToString(), "-", table_name, " 讀取成功!!", "---------------------\n");
//MessageBox.Show(Table_Num.ToString()+ "-" + table_name + "讀取成功!!");
using (SqlConnection conn = new SqlConnection($"Data Source={Globals.HOST};Initial Catalog=abc;Persist Security Info=True;User ID=abc;Password=abc;Connection Timeout=0"))
{
conn.Open();
SqlTransaction tran = conn.BeginTransaction();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandTimeout = 60;
cmd.CommandText = Sql + "\n" + PK_Sql + "\n" + Tcomm_Sql;
cmd.Transaction = tran;
int result = cmd.ExecuteNonQuery();
if (result == 0)
{
tran.Rollback(); //交易取消
}
else
{
tran.Commit(); //執行交易
}
conn.Close();
}
}
catch (Exception e) { MessageBox.Show(e.ToString() + "檔案格式內格式有誤!!"); }
index += 1;
}
else
{
if (Mode == "create")
{
// Auto Increment
string Auto_Increment = ExcelData[i][3] == "NO123" ? " IDENTITY(1,1) ":"";
Sql += ExcelData[i][3] + " " + ExcelData[i][4] + " " + Auto_Increment + (ExcelData[i][7] == "" ? "NOT" : "") + " NULL,";
index += 1;
}
else
{
#region 功能 - 對應色碼(FF + R,G,B)
// Title - FFFFCC99
// Create - FF00B050
// Edit - FF00B0F0
// Delete - FFFF0000
// EndLine - FFFFFF00
#endregion
color = ExcelData[i][0];
edit_data = "";
TmpNum = Table_Num > 1 && Table_Num != table_no ? index : (Table_Num > 1 ? TmpNum : 1);
table_no = Table_Num;
index += 1;
switch (color)
{
// nochange
case null:
continue;
case "":
continue;
// new create
case "FF00B050":
Sql += string.Format(" ALTER TABLE {0} ADD {1} ;\n", table_name, ExcelData[i][3] + " " + ExcelData[i][4] + " " + (ExcelData[i][7] == "" ? "NOT" : "") + " NULL");
continue;
// edit
case "FF00B0F0":
// compare the column information
// Column name
edit_data = OLD_Table[i - TmpNum].COLUMN_NAME != ExcelData[i][3] ? ExcelData[i][3] : "";
Sql += edit_data != "" ? string.Format(" exec sp_rename '{0}.{1}', {2};\n", table_name, OLD_Table[i - TmpNum].COLUMN_NAME, edit_data) : "";
// Data type
edit_data = OLD_Table[i - TmpNum].DATA_TYPE != ExcelData[i][4] ? ExcelData[i][4] : "";
Sql += edit_data != "" ? string.Format(" ALTER TABLE {0} ALTER COLUMN {1} {2};\n", table_name, ExcelData[i][3], edit_data) : "";
// Nullable
edit_data = OLD_Table[i - TmpNum].IS_NULLABLE != ExcelData[i][7] ? (ExcelData[i][7] == "V" ? "NULL" : "NOT NULL") : "";
Sql += edit_data != "" ? string.Format(" ALTER TABLE {0} ALTER COLUMN {1} {2} {3};\n", table_name, ExcelData[i][3], ExcelData[i][4], edit_data) : "";
continue;
// delete
case "FFFF0000":
PK_Sql += string.Format(" ALTER TABLE {0} DROP COLUMN {1} ;\n", table_name, ExcelData[i][3]);
continue;
// exception
default:
var Ex = new Exception("Excel row:" + i + "column:" + 1 + "、色碼錯誤");
Error.Add(table_name, Ex);
return(Error);
}
}
}
}
#endregion
return(Error);
}
