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; }
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); }