protected void AddRichTextNameAndValue(OfficeOpenXml.Style.ExcelRichTextCollection rtfCollection, string name, string value)
{
OfficeOpenXml.Style.ExcelRichText ert = rtfCollection.Add(name);
ert.Bold = false;
ert.Color = System.Drawing.Color.Black;
ert = rtfCollection.Add(value);
ert.Bold = true;
ert.Color = System.Drawing.Color.Blue;
}
internal ExcelRichText(XmlNamespaceManager ns, XmlNode topNode, ExcelRichTextCollection collection) :
base(ns, topNode)
{
SchemaNodeOrder = new string[] { "rPr", "t", "b", "i", "strike", "u", "vertAlign", "sz", "color", "rFont", "family", "scheme", "charset" };
_collection = collection;
}
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());
}
