public static Byte[] GenerateXLS(DashboardCollection datasource)
{
/* Call OpenOfficeXML need nuget package for epplus
This can now be customize for each type of excel export, also we can make a abstract calls for data dumps
My want abastrct it out os multiple worksheets can be created.
*/
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Dashboards");
// Build Excel title
BuildTitle(ws);
//By Teachers
BuildTeachersTable(ws, datasource.Teachers);
//By Homerooms
BuildHomeroomsTable(ws, datasource.Homerooms);
//By Violations
BuildViolationsTable(ws, datasource.OffenseTypes);
return pck.GetAsByteArray();
}
}
public NarrativeReport(IEnumerable<Area> areas, IEnumerable<Fund> funds)
{
Funds = funds;
ExcelPackage package = new ExcelPackage();
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Funding Request Report");
#region Table Labels
Row++;
sheet = WriteTableLabels(sheet);
#endregion
#region Area Data
foreach (Area area in areas)
{
sheet = WriteAreaData(sheet, area);
}
#endregion
sheet = PerformFinalFormatting(sheet);
this.BinaryData = package.GetAsByteArray();
this.FileType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
this.FileName = "FoundationPortal_" + System.DateTime.Now.ToShortDateString() + ".xlsx";
}
private void CreateTranslationsFile(List<IndicatorUpdate> indicators, string filename)
{
DataTable table = new DataTable();
table.Columns.Add("Key");
table.Columns.Add("English");
table.Columns.Add("French");
table.Columns.Add("Portuguese");
table.Columns.Add("Bahasa");
foreach (var ind in indicators)
{
var dr = table.NewRow();
dr["Key"] = ind.Key;
dr["English"] = ind.English;
dr["French"] = ind.French;
dr["Portuguese"] = ind.Portuguese;
dr["Bahasa"] = ind.Bahasa;
table.Rows.Add(dr);
}
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Sheet1");
ws.Cells["A1"].LoadFromDataTable(table, true);
File.WriteAllBytes(filename, pck.GetAsByteArray());
}
}
/// <summary>
/// Creates an Excel spreadsheet with a single worksheet for the supplied data.
/// </summary>
/// <param name="data">Each row of the spreadsheet will contain one item from the data collection.</param>
/// <returns>An Excel spreadsheet as a byte array.</returns>
public static byte[] Create(IEnumerable<object> data)
{
var package = new ExcelPackage();
Spreadsheet.AddData(ref package, data);
return package.GetAsByteArray();
}
public byte[] CreateNewStatusesReport(ObservableCollection<AccountsMainSet> accountsList)
{
byte[] resultPackage;
using (ExcelPackage p = new ExcelPackage())
{
p.Workbook.Worksheets.Add("Report");
var ws = p.Workbook.Worksheets[1];
ws.Cells[1, 1].Value = @"Номер п\п";
ws.Cells[1, 2].Value = "Объект";
ws.Cells[1, 3].Value = "Поставщик";
ws.Cells[1, 4].Value = "Номер счета";
ws.Cells[1, 5].Value = "Дата счета";
ws.Cells[1, 6].Value = "Сумма счета";
ws.Cells[1, 1, 1, 6].Style.Font.Bold = true;
var i = 1;
foreach(var account in accountsList)
{
ws.Cells[i + 1, 1].Value = i;
ws.Cells[i + 1, 2].Value = (account.AccountsStoreDetailsSets.Count > 1) ? "По списку" : ((account.AccountsStoreDetailsSets.Count == 0) ? "Не задан" : _storeService.GetStoreName(account.AccountsStoreDetailsSets.FirstOrDefault().AccountStore));
ws.Cells[i + 1, 3].Value = account.AccountCompany;
ws.Cells[i + 1, 4].Value = account.AccountNumber;
ws.Cells[i + 1, 5].Value = account.AccountDate.ToShortDateString();
ws.Cells[i + 1, 6].Value = account.AccountAmount;
i++;
}
for (int k = 1; k <= 6; k++)
{
ws.Column(k).AutoFit(k);
}
resultPackage = p.GetAsByteArray();
return resultPackage;
}
}
protected void btnExport_Click(object sender, EventArgs e)
{
ExcelPackage package = new ExcelPackage();
EppTools tools = new EppTools();
//
string fileName = "WeeklyReport";
string year = ddlYear.SelectedValue;
int classid = Convert.ToInt32(ddlClass.SelectedItem.Value);
DataTable table = Common.GetDailyReport(year, classid);
//
tools.GenerateDailyReport(table, ref package, fileName);
//
var ws = package.Workbook.Worksheets[fileName];
//
// HEADER TITLE FORMATING
ws.Cells[2, 1].Value = "DAILY REPORT ";
ws.Cells[3, 1].Value = "Year and Date ";
ws.Cells[4, 1].Value = "Agent: " + GlobalAccess.Username;
ws.Cells[2, 1, 4, 1].Style.Font.Bold = true;
ws.Cells[2, 1, 4, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
// SAVING EXCEL FILE
Response.Clear();
Response.BinaryWrite(package.GetAsByteArray());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=\"" + "Daily_Report.xlsx\"");
Response.Flush();
Response.End();
package = null;
ws = null;
}
public void export()
{
SqlConnection conn = new SqlConnection();
string DatabaseEntities = ConfigurationManager.ConnectionStrings["Database"].ConnectionString;
conn.ConnectionString = DatabaseEntities;
conn.Open();
DataTable DtNew = new DataTable();
SqlDataAdapter adp = new SqlDataAdapter("Select * from Student", conn);
adp.Fill(DtNew);
if (DtNew.Rows.Count > 0)
{
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
var filename = new FileInfo("excel");
using (var excel = new OfficeOpenXml.ExcelPackage(filename))
{
var sheetcreate = excel.Workbook.Worksheets.Add("Data");
for (int i = 0; i < DtNew.Columns.Count; i++)
{
sheetcreate.Cells[1, i + 1].Value = DtNew.Columns[i].ColumnName.ToString();
}
for (int i = 0; i < DtNew.Rows.Count; i++)
{
for (int j = 0; j < DtNew.Columns.Count; j++)
{
sheetcreate.Cells[i + 2, j + 1].Value = DtNew.Rows[i][j].ToString();
}
}
Session["DownloadExcel_FileManager"] = excel.GetAsByteArray();
}
}
conn.Close();
}
public static byte[] GetBytes(this ExcelWorksheet worksheet)
{
using(ExcelPackage package = new ExcelPackage())
{
package.Workbook.Worksheets.Add(worksheet.Name, worksheet);
return package.GetAsByteArray();
}
}
public static byte[] ToExcelBytes(this IDataReader rd, string filename = null, bool useTable = false)
{
var dt = new DataTable();
dt.Load(rd);
var ep = new ExcelPackage();
ep.AddSheet(dt, filename, useTable);
return ep.GetAsByteArray();
}
/// <summary>
/// Creates an Excel spreadsheet with a single worksheet for the supplied data.
/// </summary>
/// <param name="data">Each row of the spreadsheet will contain one item from the data collection.</param>
/// <returns>An Excel spreadsheet as a byte array.</returns>
public static byte[] Create(IEnumerable<object> data)
{
var package = new ExcelPackage();
AddWorksheet(package, data);
AddSpreadsheetLinks(package, new[] { data });
return package.GetAsByteArray();
}
public static Byte[] GenerateXLS(List<Student> datasource)
{
/* Call OpenOfficeXML need nuget package for epplus
This can now be customize for each type of excel export, also we can make a abstract calls for data dumps
My want abastrct it out os multiple worksheets can be created.
*/
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Students");
//Set Header titles
ws.Cells[1, 1].Value = "Student Id";
ws.Cells[1, 2].Value = "First Name";
ws.Cells[1, 3].Value = "Last Name";
ws.Cells[1, 4].Value = "School Year";
ws.Cells[1, 5].Value = "Home Room";
ws.Cells[1, 6].Value = "Grade";
ws.Cells[1, 7].Value = "Created By";
ws.Cells[1, 8].Value = "Create Date";
ws.Cells[1, 9].Value = "Last Updated By";
ws.Cells[1, 10].Value = "Last Update Date";
ws.Cells[1, 11].Value = "Deleted";
//Get Data
for (int i = 0; i < datasource.Count(); i++)
{
ws.Cells[i + 2, 1].Value = datasource.ElementAt(i).student_id;
ws.Cells[i + 2, 2].Value = datasource.ElementAt(i).first_name;
ws.Cells[i + 2, 3].Value = datasource.ElementAt(i).last_name;
ws.Cells[i + 2, 4].Value = datasource.ElementAt(i).school_year_id;
ws.Cells[i + 2, 5].Value = datasource.ElementAt(i).homeroom_id;
ws.Cells[i + 2, 6].Value = datasource.ElementAt(i).grade_id;
ws.Cells[i + 2, 7].Value = datasource.ElementAt(i).create_contact_id;
ws.Cells[i + 2, 8].Value = datasource.ElementAt(i).create_dt;
ws.Cells[i + 2, 9].Value = datasource.ElementAt(i).last_update_contact_id;
ws.Cells[i + 2, 10].Value = datasource.ElementAt(i).last_update_dt;
ws.Cells[i + 2, 11].Value = datasource.ElementAt(i).is_deleted;
}
//Set Header style
using (ExcelRange rng = ws.Cells[1,1,1,11])
{
rng.AutoFilter = true;
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(Color.Blue);
rng.Style.Font.Color.SetColor(Color.White);
}
return pck.GetAsByteArray();
}
}
public static Response AsExcel(this IResponseFormatter response, IEnumerable<TimeRegistration> items)
{
using (var package = new ExcelPackage())
{
WriteSheet(package, items);
var stream = new MemoryStream(package.GetAsByteArray());
return response.FromStream(() => stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
.WithHeader("content-disposition", string.Format("attachment;filename=Timeregistrations_all.xlsx"));
}
}
private void SetHttpResponse(HttpResponseBase httpResponse, string fileNameWithoutExtension, ExcelPackage package)
{
httpResponse.ClearContent();
httpResponse.Buffer = true;
//Write it back to the client
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", fileNameWithoutExtension));
httpResponse.BinaryWrite(package.GetAsByteArray());
httpResponse.Flush();
httpResponse.End();
}
/// <summary>
/// Creates an Excel spreadsheet with worksheets for each collection of objects.
/// </summary>
/// <param name="data">A collection of data collections. Each outer collection will be used as a worksheet, while the inner collections will be used as data rows.</param>
/// <returns>An Excel spreadsheet as a byte array.</returns>
public static byte[] Create(IEnumerable<IEnumerable<object>> data)
{
var package = new ExcelPackage();
foreach (var collection in data)
{
AddWorksheet(package, collection);
}
AddSpreadsheetLinks(package, data);
return package.GetAsByteArray();
}
public void ExportDataSetToExcelWithPlus(DataSet ds)
{
using (ExcelPackage pck = new ExcelPackage())
{
HttpResponse Response = HttpContext.Current.Response;
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("ItemList");
string[] cellNames = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
DataTable tbl = ds.Tables[0];
int cellRange = tbl.Columns.Count;
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromDataTable(tbl, true);
//Format the header for column 1-3
using (ExcelRange rng = ws.Cells[cellNames[0] + "1:" + cellNames[cellRange - 1] + "1"])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(Color.White);
}
////Example how to Format Column 1 as numeric
//using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
//{
// col.Style.Numberformat.Format = "#,##0.00";
// col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
//}
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=ItemDataList.xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
try {
Response.End();
}catch (Exception ex)
{
if (!(ex is System.Threading.ThreadAbortException))
{
//Log other errors here
}
}
}
}
public FileResult ExportOrdersToExcel(OrderExportQuery exportQuery)
{
using (var excelFile = new ExcelPackage())
{
AddOrders(excelFile, exportQuery);
AddOrderLines(excelFile, exportQuery);
byte[] data = excelFile.GetAsByteArray();
return new FileContentResult(data, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
FileDownloadName = "mrcms-ecommerce-order-export.xlsx"
};
}
}
/// <summary>
/// Generates the report.
/// </summary>
private static void GenerateReport()
{
var startTime = DateTime.Now.Second;
using (ExcelPackage p = new ExcelPackage())
{
//set the workbook properties and add a default sheet in it
SetWorkbookProperties(p);
//Create a sheet
ExcelWorksheet ws = CreateSheet(p,"Sample Sheet");
DataTable dt = CreateDataTable(); //My Function which generates DataTable
List<TestData> testData = BuildList();
ws.InsertRow(1, testData.Count);
ws.InsertColumn(1, 7);
//Merging cells and create a center heading for out table
ws.Cells[1, 1].Value = "Sample DataTable Export";
ws.Cells[1, 1, 1, 6].Merge = true;
ws.Cells[1, 1, 1, 6].Style.Font.Bold = true;
ws.Cells[1, 1, 1, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
int rowIndex = 2;
CreateHeader(ws, ref rowIndex);
CreateData(ws, ref rowIndex, testData);
CreateFooter(ws, ref rowIndex);
AddComment(ws, 5, 10, "Zeeshan Umar's Comments", "Zeeshan Umar");
ws.Cells.AutoFitColumns();
//string path = Path.Combine(Path.GetDirectoryName(Path.GetDirectoryName(Application.StartupPath)), "Zeeshan Umar.jpg");
//AddImage(ws, 10, 0, path);
//AddCustomShape(ws, 10, 7, eShapeStyle.Ellipse, "Text inside Ellipse.");
//Generate A File with Random name
Byte[] bin = p.GetAsByteArray();
string file = Guid.NewGuid().ToString() + ".xlsx";
File.WriteAllBytes(@"C:\temp\" + file, bin);
var endTime = DateTime.Now.Second;
MessageBox.Show("All done it took " + (endTime - startTime) + " seconds for 25000 rows");
}
}
public byte[] GetFacilityListDocument(IEnumerable<Ombudsman.Models.Facility> all)
{
using (var pck = new ExcelPackage())
{
var pcblist = all
.Where(f => f.FacilityTypeId == IdPCBH)
.Select(x => new { x.Name, x.OmbudsmanName, x.Address1, x.Address2, x.City, x.ZipCode, x.Phone, x.NumberOfBeds, x.IsActive, x.IsMedicaid, x.IsContinuum });
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("PersonalCareBoardingHome");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells["A1"].LoadFromCollection(pcblist, true);
ws.Cells["A1"].Value = "Facility";
ws.Cells["B1"].Value = "Ombudsman";
ws.Cells.AutoFitColumns();
//ws.Column(10).Hidden = true;
//ws.Column(11).Hidden = true;
var rtflist = all
.Where(f => f.FacilityTypeId == IdRTF)
.Select(x => new { x.Name, x.OmbudsmanName, x.Address1, x.Address2, x.City, x.ZipCode, x.Phone, x.NumberOfBeds, x.IsActive, x.IsMedicaid, x.IsContinuum });
ExcelWorksheet ws1 = pck.Workbook.Worksheets.Add("ResidentialTreatmentFacility");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws1.Cells["A1"].LoadFromCollection(rtflist, true);
ws1.Cells["A1"].Value = "Facility";
ws1.Cells["B1"].Value = "Ombudsman";
ws1.Cells.AutoFitColumns();
var nhlist = all
.Where(f => f.FacilityTypeId == IdNH)
.Select(x => new { x.Name, x.OmbudsmanName, x.Address1, x.Address2, x.City, x.ZipCode, x.Phone, x.NumberOfBeds, x.IsActive, x.IsMedicaid, x.IsContinuum });
ExcelWorksheet ws2 = pck.Workbook.Worksheets.Add("NursingHome");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws2.Cells["A1"].LoadFromCollection(nhlist, true);
ws2.Cells["A1"].Value = "Facility";
ws2.Cells["B1"].Value = "Ombudsman";
ws2.Cells.AutoFitColumns();
return pck.GetAsByteArray();
}
}
public FundingRequestReport(IEnumerable<Area> areas, IEnumerable<Fund> funds)
{
this.Funds = funds;
var otherUsesOfFundsArea = areas.SingleOrDefault(a => a.Number == "O");
if (otherUsesOfFundsArea != null)
{
this.OtherUsesOfFundsAreaId = otherUsesOfFundsArea.Id;
}
ExcelPackage package = new ExcelPackage();
ExcelWorksheet sheet = package.Workbook.Worksheets.Add("Funding Request Report");
#region Table Labels
Row++;
sheet = WriteTableLabels(sheet);
#endregion
#region Area Data
foreach (Area area in areas.Where(a => a.Id != this.OtherUsesOfFundsAreaId))
{
sheet = WriteAreaData(sheet, area);
}
#endregion
#region University Data
sheet = WriteUniversityData(sheet);
#endregion
#region Other Uses of Funds Data
foreach (Area area in areas.Where(r => r.Id == this.OtherUsesOfFundsAreaId))
{
sheet = WriteAreaData(sheet, area);
}
#endregion
sheet = PerformFinalFormatting(sheet);
this.BinaryData = package.GetAsByteArray();
this.FileType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
this.FileName = "FoundationPortal_" + System.DateTime.Now.ToShortDateString() + ".xlsx";
}
public byte[] ExportProductsToExcel()
{
using (var excelFile = new ExcelPackage())
{
CreateInfo(excelFile);
var wsItems = excelFile.Workbook.Worksheets.Add("Items");
AddHeader(wsItems);
var productVariants = _productVariantService.GetAll().Where(variant => variant.Product != null).OrderBy(x => x.Product.Id).ToList();
for (var i = 0; i < productVariants.Count; i++)
{
AddVariant(i, wsItems, productVariants);
}
AutofitColumns(wsItems);
return excelFile.GetAsByteArray();
}
}
/// <summary>
/// Generates the report.
/// </summary>
private static void GenerateReport()
{
using (ExcelPackage p = new ExcelPackage())
{
//set the workbook properties and add a default sheet in it
SetWorkbookProperties(p);
//Create a sheet
ExcelWorksheet ws = CreateSheet(p,"Sample Sheet");
DataTable dt = CreateDataTable(); //My Function which generates DataTable
//Merging cells and create a center heading for out table
ws.Cells[1, 1].Value = "Sample DataTable Export";
ws.Cells[1, 1, 1, dt.Columns.Count].Merge = true;
ws.Cells[1, 1, 1, dt.Columns.Count].Style.Font.Bold = true;
ws.Cells[1, 1, 1, dt.Columns.Count].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
int rowIndex = 2;
CreateHeader(ws, ref rowIndex, dt);
CreateData(ws, ref rowIndex, dt);
CreateFooter(ws, ref rowIndex, dt);
AddComment(ws, 5, 10, "Zeeshan Umar's Comments", "Zeeshan Umar");
string path = Path.Combine(Path.GetDirectoryName(Path.GetDirectoryName(Application.StartupPath)), "Zeeshan Umar.jpg");
AddImage(ws, 10, 0, path);
AddCustomShape(ws, 10, 7, eShapeStyle.Ellipse, "Text inside Ellipse.");
//Generate A File with Random name
Byte[] bin = p.GetAsByteArray();
string file = Guid.NewGuid().ToString() + ".xlsx";
File.WriteAllBytes(file, bin);
//These lines will open it in Excel
ProcessStartInfo pi = new ProcessStartInfo(file);
Process.Start(pi);
}
}
public ActionResult Download()
{
var codeDict = _codeService.Query().ToDictionary(x => x.Id.ToString(), x => x.Label);
using (var excel = new ExcelPackage())
{
var worksheet = excel.Workbook.Worksheets.Add("Agencies");
worksheet.Cells[1, 1].Value = "Agencies";
var exclude = new[] {"Codes", "UserId", "User", "IsNew"};
var props = typeof(Agency).GetProperties()
.Where(x => !exclude.Contains(x.Name))
.ToList();
//Header
var row = worksheet.Dimension.End.Row + 1;
for (var i = 0; i < props.Count; i++)
{
worksheet.Cells[row, i + 1].Value = props[i].Name;
}
//Rows
foreach (var agency in _agencyService.Query().Include(x => x.Codes).ToList())
{
row = worksheet.Dimension.End.Row + 1;
for (var i = 0; i < props.Count; i++)
{
worksheet.Cells[row, i + 1].Value = GetValue(props[i], agency, codeDict);
}
}
return new FileContentResult(excel.GetAsByteArray(), MediaTypeNames.Application.Octet)
{
FileDownloadName = "Agencies.xlsx",
};
}
}
public async Task<HttpResponseMessage> Post()
{
var query = await Request.Content.ReadAsStringAsync();
query = System.Web.HttpUtility.UrlDecode(query);
query = query.Substring("query=".Length, query.Length - "query=".Length);
var models = this.MockDynamicQuery();
var response = Request.CreateResponse();
using (ExcelPackage pck = new ExcelPackage())
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Projects");
ws.Cells["A1"].Value = "First Name";
ws.Cells["B1"].Value = "Last Name";
ws.Column(1).Width = 10;
ws.Column(2).Width = 10;
ws.Column(1).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(2).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Column(3).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells["A2"].LoadFromCollection(models);
var bytes = pck.GetAsByteArray();
response.Content = new ByteArrayContent(bytes);
response.Content.Headers.Add("content-disposition", "attachment; filename=excel_name" + DateTime.Now.Ticks + ".xlsx");
response.Content.Headers.Add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
return response;
}
private void GenerateExcelFromImage(string imagePath)
{
using (var p = new ExcelPackage())
{
var totalProgress = 0f;
var currentProgress = 0;
var progress = 0f;
p.Workbook.Properties.Author = "Patryk Daru";
p.Workbook.Properties.Title = "ExcelToImage Converted";
p.Workbook.Properties.Company = "DaruHQ";
p.Workbook.Properties.Comments = FileName;
var ws = p.Workbook.Worksheets.Add(FileName);
var image = new Bitmap(imagePath);
totalProgress = image.Width * image.Height;
_backgroundWorker.ReportProgress(currentProgress, "Generating xlsx...");
for (var x = 1; x < image.Height; x++)
{
for (var y = 1; y < image.Width; y++)
{
ws.Column(y).Width = 0.3;
ws.Cells[x, y].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[x, y].Style.Fill.BackgroundColor.SetColor(image.GetPixel(y, x));
progress++;
currentProgress = (int)(progress / totalProgress * 100);
_backgroundWorker.ReportProgress(currentProgress);
}
ws.Row(x).Height = 2;
}
_backgroundWorker.ReportProgress(currentProgress, "Saving xlsx...");
var bin = p.GetAsByteArray();
File.WriteAllBytes(string.Format("{0}.xlsx", FileName), bin);
}
}
public Byte[] exportStudents(List<Student> studentsToExport)
{
FileInfo newFile = new FileInfo("Students");
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("UESL Students");
//bio
worksheet.Cells[1, 1].Value = "Last Name";
worksheet.Cells[1, 2].Value = "First Name";
worksheet.Cells[1, 3].Value = "SID";
worksheet.Cells[1, 4].Value = "SEVIS";
worksheet.Cells[1, 5].Value = "I-20 Expiration";
worksheet.Cells[1, 6].Value = "Mission Student ID";
worksheet.Cells[1, 7].Value = "Mission ID Expiration";
worksheet.Cells[1, 8].Value = "Gender";
worksheet.Cells[1, 9].Value = "DOB";
worksheet.Cells[1, 10].Value = "Citizenship";
worksheet.Cells[1, 11].Value = "School/Agent";
worksheet.Cells[1, 12].Value = "Agent Email";
worksheet.Cells[1, 13].Value = "Transfer?";
worksheet.Cells[1, 14].Value = "Condtional Admission?";
worksheet.Cells[1, 15].Value = "status?";
worksheet.Cells[1, 16].Value = "Placement";
worksheet.Cells[1, 17].Value = "Placement Quarter";
//worksheet.Cells[1, 18].Value = "Catalog Number (placement)";
//contact
worksheet.Cells[1, 18].Value = "Telephone";
worksheet.Cells[1, 19].Value = "Email";
worksheet.Cells[1, 20].Value = "CWU Email";
worksheet.Cells[1, 21].Value = "CWU Housing?";
worksheet.Cells[1, 22].Value = "CWU Adress";
worksheet.Cells[1, 23].Value = "Home Adress";
worksheet.Cells[1, 24].Value = "Emergency Contact";
worksheet.Cells[1, 25].Value = "Emergency Contact Relationship";
worksheet.Cells[1, 26].Value = "Emergency Contact Email";
worksheet.Cells[1, 27].Value = "Emergency Contact Phone";
int rowCounter = 2;
String a, b, c, d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,aa = "";
foreach (Student stud in studentsToExport)
{
a = "A" + rowCounter.ToString();
b = "B" + rowCounter.ToString();
c = "C" + rowCounter.ToString();
d = "D" + rowCounter.ToString();
e = "E" + rowCounter.ToString();
f = "F" + rowCounter.ToString();
g = "G" + rowCounter.ToString();
h = "H" + rowCounter.ToString();
i = "I" + rowCounter.ToString();
j = "J" + rowCounter.ToString();
k = "K" + rowCounter.ToString();
l = "L" + rowCounter.ToString();
m = "M" + rowCounter.ToString();
n = "N" + rowCounter.ToString();
o = "O" + rowCounter.ToString();
p = "P" + rowCounter.ToString();
q = "Q" + rowCounter.ToString();
r = "R" + rowCounter.ToString();
s = "S" + rowCounter.ToString();
t = "T" + rowCounter.ToString();
u = "U" + rowCounter.ToString();
v = "V" + rowCounter.ToString();
w = "W" + rowCounter.ToString();
x = "X" + rowCounter.ToString();
y = "Y" + rowCounter.ToString();
z = "Z" + rowCounter.ToString();
aa = "AA" + rowCounter.ToString();
worksheet.Cells[a.ToString()].Value = stud.LastName;
worksheet.Cells[b.ToString()].Value = stud.FirstName;
worksheet.Cells[c.ToString()].Value = stud.StudentID.ToString();
worksheet.Cells[d.ToString()].Value = stud.SEVIS;
//need to check if i20expiration is null first otherwise you will get error!
string donly = "";
if (stud.I_20_Expiration != null)
{
var dt = (DateTime)stud.I_20_Expiration;
donly = dt.Date.ToShortDateString();
}
worksheet.Cells[e.ToString()].Value = donly;
worksheet.Cells[f.ToString()].Value = stud.Mission_Student_ID;
string donly2 = "";
if (stud.Mission_ID_Expiration != null)
{
var dt2 = (DateTime)stud.Mission_ID_Expiration;
donly = dt2.Date.ToShortDateString();
}
worksheet.Cells[g.ToString()].Value = donly2;
worksheet.Cells[h.ToString()].Value = stud.gender;
string donly3 = "";
if (stud.DOB != null)
{
var dt3 = (DateTime)stud.DOB;
donly3 = dt3.Date.ToShortDateString();
}
worksheet.Cells[i.ToString()].Value = donly3;
worksheet.Cells[j.ToString()].Value = stud.Citezenship;
worksheet.Cells[k.ToString()].Value = stud.School_Agent;
worksheet.Cells[l.ToString()].Value = stud.Agent_Email;
worksheet.Cells[m.ToString()].Value = stud.Transfer_;
worksheet.Cells[n.ToString()].Value = stud.Conditional_Admission;
worksheet.Cells[o.ToString()].Value = stud.Status;
worksheet.Cells[p.ToString()].Value = stud.Placement;
worksheet.Cells[q.ToString()].Value = stud.QuarterOfPlacement;
// worksheet.Cells[r.ToString()].Value = stud.CourseCatalogNumber;
worksheet.Cells[r.ToString()].Value = stud.Telephone;
worksheet.Cells[s.ToString()].Value = stud.Email;
worksheet.Cells[t.ToString()].Value = stud.CWU_Email;
worksheet.Cells[u.ToString()].Value = stud.CWU_Housing;
worksheet.Cells[v.ToString()].Value = stud.CWU_Address;
worksheet.Cells[w.ToString()].Value = stud.Home_Address;
worksheet.Cells[x.ToString()].Value = stud.Emergency_Contact;
worksheet.Cells[y.ToString()].Value = stud.Emergency_Contact_Relationship;
worksheet.Cells[z.ToString()].Value = stud.Emergency_Contact_Email;
worksheet.Cells[aa.ToString()].Value = stud.Emergency_Contact_Phone;
rowCounter++;
}
//Ok now format the values;
using (var range = worksheet.Cells[1, 1, 1, 29])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
//There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful.
//For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or
//you want to use the result of a formula in your program.
worksheet.Calculate();
// worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
worksheet.DefaultRowHeight = 25;
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" UESL Students";
// set some document properties
package.Workbook.Properties.Title = "Students";
package.Workbook.Properties.Author = "UESL";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
// return dat package as a byte array
return package.GetAsByteArray();
}//using
}//export students end
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());
}
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());
}
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result)
{
timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;
// Start diagnostics timer
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);// DateTime.Now;//Datetime.Now;//DateTime.Now;//Datetime.Now;
// Now gather and analyze data for the report
SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements();
requiredDataElements.NeedsSensorData = true;
requiredDataElements.NeedsPaymentData = false;
requiredDataElements.NeedsOverstayData = false;
requiredDataElements.NeedsEnforcementActionData = false;
this._ReportEngine = new SensorAndPaymentReportEngine(this._CustomerConfig, _ReportParams);
this._ReportEngine.GatherReportData(listOfMeterIDs, requiredDataElements, result);
OfficeOpenXml.ExcelWorksheet ws = null;
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
// Let's create a report coversheet and overall summary page, with hyperlinks to the other worksheets
// Create the worksheet
ws = pck.Workbook.Worksheets.Add("Summary");
// Render the standard report title lines
rowIdx = 1; // Excel uses 1-based indexes
colIdx = 1;
RenderCommonReportTitle(ws, this._ReportName);
// Render common report header for enforcement activity restriction filter, but only if its not for all activity
if (this._ReportParams.ActionTakenRestrictionFilter != SensorAndPaymentReportEngine.ReportableEnforcementActivity.AllActivity)
{
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_ActionTakenRestrictions(ws);
}
// Render common report header for regulated hour restriction filter
rowIdx++;
colIdx = 1;
RenderCommonReportFilterHeader_RegulatedHourRestrictions(ws);
using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(207, 221, 237)); //Set color to lighter blue FromArgb(184, 204, 228)
}
rowIdx++;
colIdx = 1;
int hyperlinkstartRowIdx = rowIdx;
if (_ReportParams.IncludeMeterSummary == true)
{
RenderWorksheetHyperlink(ws, "Meter Occupancy", "Meter Occupancy summary");
}
if (_ReportParams.IncludeSpaceSummary == true)
{
RenderWorksheetHyperlink(ws, "Space Occupancy", "Space Occupancy summary");
}
if (_ReportParams.IncludeAreaSummary == true)
{
RenderWorksheetHyperlink(ws, "Area Occupancy", "Area Occupancy summary");
}
if (_ReportParams.IncludeDailySummary == true)
{
RenderWorksheetHyperlink(ws, "Daily Occupancy", "Daily Occupancy summary");
}
if (_ReportParams.IncludeMonthlySummary == true)
{
RenderWorksheetHyperlink(ws, "Monthly Occupancy", "Monthly Occupancy summary");
}
if (_ReportParams.IncludeDetailRecords == true)
{
RenderWorksheetHyperlink(ws, "Details", "Occupancy details");
}
rowIdx++;
rowIdx++;
colIdx = 1;
using (OfficeOpenXml.ExcelRange rng = ws.Cells[hyperlinkstartRowIdx, 1, rowIdx, numColumnsMergedForHeader])
{
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.White);
}
// Now start the report data summary header
RenderOverallReportSummary(ws);
// --- END OF OVERALL SUMMARY WORKSHEET ---
// Should we include a worksheet with Meter aggregates?
if (_ReportParams.IncludeMeterSummary == true)
{
RenderMeterSummaryWorksheet(pck, "Meter Occupancy");
}
// Should we include a worksheet with Space aggregates?
if (_ReportParams.IncludeSpaceSummary == true)
{
RenderSpaceSummaryWorksheet(pck, "Space Occupancy");
}
// Should we include a worksheet with Area aggregates?
if (_ReportParams.IncludeAreaSummary == true)
{
RenderAreaSummaryWorksheet(pck, "Area Occupancy");
}
// Should we include a worksheet with Daily aggregates?
if (_ReportParams.IncludeDailySummary == true)
{
RenderDailySummaryWorksheet(pck, "Daily Occupancy");
}
// Should we include a worksheet with Monthly aggregates?
if (_ReportParams.IncludeDailySummary == true)
{
RenderMonthlySummaryWorksheet(pck, "Monthly Occupancy");
}
// Should we include a Details worksheet?
if (_ReportParams.IncludeDetailRecords == true)
{
// Create the worksheet
ws = pck.Workbook.Worksheets.Add("Details");
int detailColumnCount = 8;
// Render the header row
rowIdx = 1; // Excel uses 1-based indexes
ws.SetValue(rowIdx, 1, "Space #");
ws.SetValue(rowIdx, 2, "Meter #");
ws.SetValue(rowIdx, 3, "Area #");
ws.SetValue(rowIdx, 4, "Area");
ws.SetValue(rowIdx, 5, "Event Start");
ws.SetValue(rowIdx, 6, "Event End");
ws.SetValue(rowIdx, 7, "Occupied?");
ws.SetValue(rowIdx, 8, "Duration");
// Format the header row
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, detailColumnCount])
{
rng.Style.Font.Bold = true;
rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; //Set Pattern for the background to Solid
rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189)); //Set color to dark blue
rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
}
// Increment the row index, which will now be the 1st row of our data
rowIdx++;
#region Populate data for each record
foreach (SpaceAsset spaceAsset in this._ReportEngine.ReportDataModel.SpacesIncludedInReport)
{
List <SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent> spaceRecs = this._ReportEngine.ReportDataModel.FindRecsForBayAndMeter(spaceAsset.SpaceID, spaceAsset.MeterID);
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent nextEvent in spaceRecs)
{
// Don't detail this item if its a "dummy" event
if (nextEvent.IsDummySensorEvent == true)
{
continue;
}
AreaAsset areaAsset = _ReportEngine.GetAreaAsset(spaceAsset.AreaID_PreferLibertyBeforeInternal);
// Output row values for data
ws.SetValue(rowIdx, 1, spaceAsset.SpaceID);
ws.SetValue(rowIdx, 2, spaceAsset.MeterID);
if (areaAsset != null)
{
ws.SetValue(rowIdx, 3, areaAsset.AreaID);
ws.SetValue(rowIdx, 4, areaAsset.AreaName);
}
ws.SetValue(rowIdx, 5, nextEvent.SensorEvent_Start);
ws.SetValue(rowIdx, 6, nextEvent.SensorEvent_End);
if (nextEvent.SensorEvent_IsOccupied == true)
{
ws.SetValue(rowIdx, 7, "Y");
}
else
{
ws.SetValue(rowIdx, 7, "N");
}
ws.SetValue(rowIdx, 8, FormatTimeSpanAsHoursMinutesAndSeconds(nextEvent.SensorEvent_Duration));
// Increment the row index, which will now be the next row of our data
rowIdx++;
// Is there a child "repeat" event also?
if (nextEvent.RepeatSensorEvents != null)
{
foreach (SensorAndPaymentReportEngine.CommonSensorAndPaymentEvent repeatEvent in nextEvent.RepeatSensorEvents)
{
ws.SetValue(rowIdx, 1, spaceAsset.SpaceID);
ws.SetValue(rowIdx, 2, spaceAsset.MeterID);
if (areaAsset != null)
{
ws.SetValue(rowIdx, 3, areaAsset.AreaID);
ws.SetValue(rowIdx, 4, areaAsset.AreaName);
}
ws.SetValue(rowIdx, 5, repeatEvent.SensorEvent_Start);
ws.SetValue(rowIdx, 6, repeatEvent.SensorEvent_End);
if (repeatEvent.SensorEvent_IsOccupied == true)
{
ws.SetValue(rowIdx, 7, "Y");
}
else
{
ws.SetValue(rowIdx, 7, "N");
}
ws.SetValue(rowIdx, 8, FormatTimeSpanAsHoursMinutesAndSeconds(repeatEvent.SensorEvent_Duration));
// Increment the row index, which will now be the next row of our data
rowIdx++;
}
}
}
}
#endregion
// We will add autofilters to our headers so user can sort the columns easier
using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, rowIdx, detailColumnCount])
{
rng.AutoFilter = true;
}
// Apply formatting to the columns as appropriate (Starting row is 2 (first row of data), and ending row is the current rowIdx value)
ApplyNumberStyleToColumn(ws, 1, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 2, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 3, 2, rowIdx, "########0", ExcelHorizontalAlignment.Left);
ApplyNumberStyleToColumn(ws, 5, 2, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 6, 2, rowIdx, "yyyy-mm-dd hh:mm:ss tt", ExcelHorizontalAlignment.Right);
ApplyNumberStyleToColumn(ws, 7, 2, rowIdx, "@", ExcelHorizontalAlignment.Right); // String value, right-aligned
ApplyNumberStyleToColumn(ws, 8, 2, rowIdx, "@", ExcelHorizontalAlignment.Right); // String value, right-aligned
// And now lets size the columns
for (int autoSizeColIdx = 1; autoSizeColIdx <= detailColumnCount; autoSizeColIdx++)
{
using (OfficeOpenXml.ExcelRange col = ws.Cells[1, autoSizeColIdx, rowIdx, autoSizeColIdx])
{
col.AutoFitColumns();
}
}
}
// All cells in spreadsheet are populated now, so render (save the file) to a memory stream
byte[] bytes = pck.GetAsByteArray();
ms.Write(bytes, 0, bytes.Length);
}
// Stop diagnostics timer
sw.Stop();
System.Diagnostics.Debug.WriteLine(this._ReportName + " generation took: " + sw.Elapsed.ToString());
}
public void GetReportAsExcelSpreadsheet(List <int> listOfMeterIDs, MemoryStream ms, CustomerLogic result)
{
timeIsolation.IsolationType = SensorAndPaymentReportEngine.TimeIsolations.None;
// Start diagnostics timer
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
sw.Start();
DateTime NowAtDestination = Convert.ToDateTime(this._CustomerConfig.DestinationTimeZoneDisplayName);
// Now gather and analyze data for the report
SensorAndPaymentReportEngine.RequiredDataElements requiredDataElements = new SensorAndPaymentReportEngine.RequiredDataElements();
requiredDataElements.NeedsSensorData = true;
requiredDataElements.NeedsPaymentData = 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());
}
}//coursesexport
public Byte[] exportEnrollments(List<Enrollment> enrollmentsToExport)
{
FileInfo newFile = new FileInfo("Enrollments");
using (ExcelPackage package = new ExcelPackage(newFile))
{
// add a new worksheet to the empty workbook
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Enrollments List");
//bio
worksheet.Cells[1, 1].Value = "Last Name";
worksheet.Cells[1, 2].Value = "First Name";
worksheet.Cells[1, 3].Value = "Student ID";
worksheet.Cells[1, 4].Value = "Class ID";
worksheet.Cells[1, 5].Value = "Class Name";
worksheet.Cells[1, 6].Value = "Catlalog #";
worksheet.Cells[1, 7].Value = "Location";
worksheet.Cells[1, 8].Value = "Meet Time";
worksheet.Cells[1, 9].Value = "Quarter";
worksheet.Cells[1, 10].Value = "Instructor";
worksheet.Cells[1, 11].Value = "Grade";
int rowCounter = 2;
String a, b, c, d, e, f, g ,h,i,j,k= "";
foreach (Enrollment en in enrollmentsToExport)
{
a = "A" + rowCounter.ToString();
b = "B" + rowCounter.ToString();
c = "C" + rowCounter.ToString();
d = "D" + rowCounter.ToString();
e = "E" + rowCounter.ToString();
f = "F" + rowCounter.ToString();
g = "G" + rowCounter.ToString();
h = "H" + rowCounter.ToString();
i = "I" + rowCounter.ToString();
j = "J" + rowCounter.ToString();
k = "K" + rowCounter.ToString();
worksheet.Cells[a.ToString()].Value = en.Student.LastName;
worksheet.Cells[b.ToString()].Value = en.Student.FirstName;
worksheet.Cells[c.ToString()].Value = en.Student.StudentID;
worksheet.Cells[d.ToString()].Value = en.Course.classID;
worksheet.Cells[e.ToString()].Value = en.Course.ClassName;
worksheet.Cells[f.ToString()].Value = en.Course.CatalogNumber;
worksheet.Cells[g.ToString()].Value = en.Course.Location;
worksheet.Cells[h.ToString()].Value = en.Course.MeetTime;
worksheet.Cells[i.ToString()].Value = en.Course.quarter;
worksheet.Cells[j.ToString()].Value = en.Course.Instructor;
worksheet.Cells[k.ToString()].Value = en.grade;
rowCounter++;
}
//Ok now format the values;
using (var range = worksheet.Cells[1, 1, 1, 11])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
range.Style.Font.Color.SetColor(Color.White);
}
//There is actually no need to calculate, Excel will do it for you, but in some cases it might be useful.
//For example if you link to this workbook from another workbook or you will open the workbook in a program that hasn't a calculation engine or
//you want to use the result of a formula in your program.
worksheet.Calculate();
// worksheet.Cells.AutoFitColumns(0); //Autofit columns for all cells
worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();
worksheet.DefaultRowHeight = 25;
// lets set the header text
worksheet.HeaderFooter.OddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Course List";
// set some document properties
package.Workbook.Properties.Title = "Enrollments";
package.Workbook.Properties.Author = "UESL";
// set some custom property values
package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
// return dat package as a byte array
return package.GetAsByteArray();
}//using
}//end enrollments export
public async Task <byte[]> GenerateExportStaffAsync()
{
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("StaffCode");
dt.Columns.Add("FirstName");
dt.Columns.Add("LastName");
dt.Columns.Add("MiddleName");
dt.Columns.Add("Gender");
dt.Columns.Add("JobTitle");
dt.Columns.Add("Office/Department");
dt.Columns.Add("PhoneNumber");
dt.Columns.Add("Email");
dt.Columns.Add("DateOfBirth");
dt.Columns.Add("Address");
dt.Columns.Add("Country");
dt.Columns.Add("State");
dt.Columns.Add("StaffLimit");
var structures = await(from a in _dataContext.cor_staff
join b in _dataContext.Users on a.StaffId equals b.StaffId
where a.Deleted == false
orderby a.FirstName
select new GODPAPIs.Contracts.Response.Admin.StaffObj
{
StaffId = a.StaffId,
StaffCode = a.StaffCode,
FirstName = a.FirstName,
LastName = a.LastName,
MiddleName = a.MiddleName,
JobTitle = a.JobTitle,
JobTitleName = _dataContext.cor_jobtitles.FirstOrDefault(x => x.JobTitleId == a.JobTitle).Name,
StaffOfficeName = _dataContext.cor_companystructure.FirstOrDefault(x => x.CompanyStructureId == a.StaffOfficeId).Name,
PhoneNumber = a.PhoneNumber,
Email = a.Email,
Address = a.Address,
DateOfBirth = a.DateOfBirth,
Gender = a.Gender,
StateId = a.StateId,
StateName = a.cor_state.StateName,
CountryId = a.CountryId,
CountryName = a.cor_country.CountryName,
Active = a.Active,
UserName = b.UserName,
UserId = b.Id,
UserStatus = b.Active
}).ToListAsync();
foreach (var kk in structures)
{
var row = dt.NewRow();
row["StaffCode"] = kk.StaffCode;
row["FirstName"] = kk.FirstName;
row["LastName"] = kk.LastName;
row["MiddleName"] = kk.MiddleName;
row["Gender"] = kk.Gender == "1" ? "Male" : "Female";
row["JobTitle"] = kk.JobTitleName;
row["Office/Department"] = kk.StaffOfficeName;
row["PhoneNumber"] = kk.PhoneNumber;
row["Email"] = kk.Email;
row["DateOfBirth"] = kk.DateOfBirth;
row["Address"] = kk.Address;
row["Country"] = kk.CountryName;
row["State"] = kk.StateName;
row["StaffLimit"] = kk.StaffLimit;
dt.Rows.Add(row);
}
Byte[] fileBytes = null;
if (structures != null)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage())
{
OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add("StaffList");
ws.DefaultColWidth = 20;
ws.Cells["A1"].LoadFromDataTable(dt, true, OfficeOpenXml.Table.TableStyles.None);
fileBytes = pck.GetAsByteArray();
}
}
return(fileBytes);
}
public string FillSheet(TimeSheetExcel ts, string templatepath, string timesheetpath)
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(templatepath))
{
int RowNum = 8;
string fileName = "";
pck.Load(stream);
List <ExcelWorksheet> wsList = new List <ExcelWorksheet>();
var ws1 = pck.Workbook.Worksheets.First();
wsList.Add(ws1);
int sheetindex = 0;
var ws = wsList[sheetindex];
wsList[sheetindex].Cells["C2:N2"].Value = ts.EmployeeName;
wsList[sheetindex].Cells["C5:F5"].Value = ts.Year;
wsList[sheetindex].Cells["L5:S5"].Value = ts.FromDay;
wsList[sheetindex].Cells["Z5:AF5"].Value = ts.ToDay;
if (ts.LiveInEmployee)
{
wsList[sheetindex].Cells["S28"].Value = "X";
}
else
{
wsList[sheetindex].Cells["W28"].Value = "X";
}
double cnt = (double)ts.TimeRecordsLst.Count / (double)15;
int count = 0;
if ((cnt % 1) > 0)
{
count = (int)cnt;
count++;
}
else
{
count = (int)cnt;
}
for (int i = 1; i < count; i++)
{
wsList.Add(pck.Workbook.Worksheets.Add("Sheet (" + i.ToString() + ")", ws));
}
double tot = 0;
foreach (TimeRecordExcel tr in ts.TimeRecordsLst)
{
wsList[sheetindex].Cells["A" + RowNum.ToString()].Value = tr.MonthNumber.ToString("00");
wsList[sheetindex].Cells["B" + RowNum.ToString()].Value = tr.Day.ToString("00");
wsList[sheetindex].Cells["C" + RowNum.ToString()].Value = tr.ServiceCode;
wsList[sheetindex].Cells["D" + RowNum.ToString() + ":F" + RowNum.ToString()].Value = tr.EnterPlan.ToUpper();
wsList[sheetindex].Cells["G" + RowNum.ToString()].Value = tr.Backup.ToString().ToUpper();
wsList[sheetindex].Cells["H" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn1.Hours.ToString("00"))[0];
wsList[sheetindex].Cells["I" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn1.Hours.ToString("00"))[1];
wsList[sheetindex].Cells["J" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn1.Mins.ToString("00"))[0];
wsList[sheetindex].Cells["K" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn1.Mins.ToString("00"))[1];
wsList[sheetindex].Cells["L" + RowNum.ToString() + ":M" + RowNum.ToString()].Value = tr.TimeIn1.AmOrPm.ToUpper();
wsList[sheetindex].Cells["N" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut1.Hours.ToString("00"))[0];
wsList[sheetindex].Cells["O" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut1.Hours.ToString("00"))[1];
wsList[sheetindex].Cells["P" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut1.Mins.ToString("00"))[0];
wsList[sheetindex].Cells["Q" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut1.Mins.ToString("00"))[1];
wsList[sheetindex].Cells["R" + RowNum.ToString() + ":S" + RowNum.ToString()].Value = tr.TimeOut1.AmOrPm.ToUpper();
var calculatedHours = CalculateTotalWorkingHours(tr.TimeIn1.Hours, tr.TimeIn1.Mins, tr.TimeIn1.AmOrPm
, tr.TimeOut1.Hours, tr.TimeOut1.Mins, tr.TimeOut1.AmOrPm);
if (calculatedHours.TotalHours < 0)
{
tr.TotalWorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
tr.TotalWorkedHours += calculatedHours;
}
if (tr.TimeIn2.Hours != 0)
{
wsList[sheetindex].Cells["T" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn2.Hours.ToString("00"))[0];
wsList[sheetindex].Cells["U" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn2.Hours.ToString("00"))[1];
wsList[sheetindex].Cells["V" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn2.Mins.ToString("00"))[0];
wsList[sheetindex].Cells["W" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeIn2.Mins.ToString("00"))[1];
wsList[sheetindex].Cells["X" + RowNum.ToString() + ":Y" + RowNum.ToString()].Value = tr.TimeIn2.AmOrPm.ToUpper();
wsList[sheetindex].Cells["Z" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut2.Hours.ToString("00"))[0];
wsList[sheetindex].Cells["AA" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut2.Hours.ToString("00"))[1];
wsList[sheetindex].Cells["AB" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut2.Mins.ToString("00"))[0];
wsList[sheetindex].Cells["AC" + RowNum.ToString()].Value = StringtoStringArray(tr.TimeOut2.Mins.ToString("00"))[1];
wsList[sheetindex].Cells["AD" + RowNum.ToString() + ":AE" + RowNum.ToString()].Value = tr.TimeOut2.AmOrPm.ToUpper();
calculatedHours = CalculateTotalWorkingHours(tr.TimeIn2.Hours, tr.TimeIn2.Mins, tr.TimeIn2.AmOrPm
, tr.TimeOut2.Hours, tr.TimeOut2.Mins, tr.TimeOut2.AmOrPm);
if (calculatedHours.TotalHours < 0)
{
tr.TotalWorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
tr.TotalWorkedHours += calculatedHours;
}
if (tr.ServiceCode.Equals("032"))
{
if (calculatedHours.TotalHours <= 0)
{
ts.Total032WorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
ts.Total032WorkedHours += calculatedHours;
}
}
else if (tr.ServiceCode.Equals("011"))
{
if (calculatedHours.TotalHours <= 0)
{
ts.Total011WorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
ts.Total011WorkedHours += calculatedHours;
}
}
}
calculatedHours = CalculateTotalWorkingHours(tr.TimeIn1.Hours, tr.TimeIn1.Mins, tr.TimeIn1.AmOrPm
, tr.TimeOut1.Hours, tr.TimeOut1.Mins, tr.TimeOut1.AmOrPm);
// Calculate total working hours per each Service Code
if (tr.ServiceCode.Equals("032"))
{
if (calculatedHours.TotalHours < 0)
{
ts.Total032WorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
ts.Total032WorkedHours += calculatedHours;
}
}
else if (tr.ServiceCode.Equals("011"))
{
if (calculatedHours.TotalHours < 0)
{
ts.Total011WorkedHours += TimeSpan.FromHours(24) + calculatedHours;
}
else
{
ts.Total011WorkedHours += calculatedHours;
}
}
wsList[sheetindex].Cells["AF" + RowNum.ToString()].Value = Math.Abs(tr.TotalWorkedHours.TotalHours);
tot += tr.TotalWorkedHours.TotalHours;
RowNum++;
if (RowNum > 22)
{
RowNum = 8;
// save before go to the next sheet the total working hours per each Service Code
if (ts.Total032WorkedHours.TotalHours != 0)
{
wsList[sheetindex].Cells["F24:G24"].Value = "032";
wsList[sheetindex].Cells["H24:J24"].Value = Math.Abs(ts.Total032WorkedHours.TotalHours);
}
if (ts.Total011WorkedHours.TotalHours != 0)
{
wsList[sheetindex].Cells["F26:G26"].Value = "011";
wsList[sheetindex].Cells["H26:J26"].Value = Math.Abs(ts.Total011WorkedHours.TotalHours);
}
wsList[sheetindex].Cells["AF26"].Value = tot;
wsList[sheetindex].Cells["AA26:AD26"].Value = tot;
tot = 0;
ts.Total011WorkedHours = new TimeSpan();
ts.Total032WorkedHours = new TimeSpan();
sheetindex++;
// ws = wsList[sheetindex];
}
}
if (RowNum <= 22)
{
// save in the last sheet the total working hours per each Service Code
if (ts.Total032WorkedHours.TotalHours != 0)
{
wsList[sheetindex].Cells["F24:G24"].Value = "032";
wsList[sheetindex].Cells["H24:J24"].Value = Math.Abs(ts.Total032WorkedHours.TotalHours);
}
if (ts.Total011WorkedHours.TotalHours != 0)
{
wsList[sheetindex].Cells["F26:G26"].Value = "011";
wsList[sheetindex].Cells["H26:J26"].Value = Math.Abs(ts.Total011WorkedHours.TotalHours);
}
wsList[sheetindex].Cells["AF26"].Value = tot;
wsList[sheetindex].Cells["AA26:AD26"].Value = tot;
tot = 0;
ts.Total011WorkedHours = new TimeSpan();
ts.Total032WorkedHours = new TimeSpan();
}
string dirPath = Path.Combine(timesheetpath + @"/" + ts.EmployeeName + "_" + ts.FromDay.Replace("/", "-") + "_" + ts.ToDay.Replace("/", "-"));
if (!Directory.Exists(dirPath))
{
Directory.CreateDirectory(dirPath);
}
fileName = dirPath + @"/" + ts.EmployeeName + "_" + ts.FromDay.Replace("/", "-") + "_" + ts.ToDay.Replace("/", "-") + ".xlsx";
// oXL.DisplayAlerts = false;
byte[] data = pck.GetAsByteArray();
File.WriteAllBytes(fileName, data);
//oWB.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
// false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//oWB.Close();
}
}
// oXL.ActiveWorkbook.Sheets[1].Activate();
// oXL.UserControl = false;
return("");
}
public void ExportResultsToExcel(int TournamentID,
string IncludeName = null,
string IncludeFIO = null,
string IncludeBirthDay = null,
string IncludePhoneNumber = null,
string IncludeEmail = null,
string IncludeCountry = null,
string IncludeCity = null,
string IncludeInstitution = null,
string IncludeCategory = null,
string IncludeGradeLevel = null,
string IncludeProblemsResults = null,
string IncludeTotalScoreIOI = null,
string IncludeTotalAcceptedACM = null,
string IncludeTotalPenaltiesACM = null
)
{
logger.Info("User " + WebSecurity.CurrentUserId +
" \"" + User.Identity.Name + "\" download results for tournament " + TournamentID);
var tournament = repository
.Tournaments
.FirstOrDefault(t => t.TournamentID == TournamentID);
if (tournament == null)
{
logger.Warn("Tournament with id = " + TournamentID + " not found");
throw new HttpException(404, "Tournament not found");
}
IEnumerable<ParticipantTournamentResult> userTournamentResults = null;
if (tournament.Format == TournamentFormats.IOI)
{
IOIResults(TournamentID, out userTournamentResults);
}
else if (tournament.Format == TournamentFormats.ACM)
{
ACMResults(TournamentID, out userTournamentResults);
}
using (ExcelPackage pck = new ExcelPackage())
{
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add(tournament.Name);
int row = 1, col = 1;
ws.Cells[row, col].Value = "Место";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
col++;
if (IncludeName != null)
{
ws.Cells[row, col].Value = "Участник";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 20;
col++;
}
if (IncludeEmail != null)
{
ws.Cells[row, col].Value = "Email";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 25;
col++;
}
if (IncludeFIO != null)
{
ws.Cells[row, col].Value = "ФИО";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 30;
col++;
}
if (IncludeBirthDay != null)
{
ws.Cells[row, col].Value = "Дата рождения";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 15;
col++;
}
if (IncludePhoneNumber != null)
{
ws.Cells[row, col].Value = "Номер телефона";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 20;
col++;
}
if (IncludeCountry != null)
{
ws.Cells[row, col].Value = "Страна";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 20;
col++;
}
if (IncludeCity != null)
{
ws.Cells[row, col].Value = "Город";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 20;
col++;
}
if (IncludeInstitution != null)
{
ws.Cells[row, col].Value = "Образовательное учреждение (организация)";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 40;
col++;
}
if (IncludeCategory != null)
{
ws.Cells[row, col].Value = "Категория";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 20;
col++;
}
if (IncludeGradeLevel != null)
{
ws.Cells[row, col].Value = "Год обучения";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 15;
col++;
}
if (IncludeProblemsResults != null)
{
for (int i = 1; i < tournament.Problems.Count + 1; i++)
{
ws.Cells[row, col].Value = i.ToAlpha();
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
col++;
}
}
if (tournament.Format == TournamentFormats.IOI)
{
if (IncludeTotalScoreIOI != null)
{
ws.Cells[row, col].Value = "Всего";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
col++;
}
}
else if (tournament.Format == TournamentFormats.ACM)
{
if (IncludeTotalAcceptedACM != null)
{
ws.Cells[row, col].Value = "Всего зачтено";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 15;
col++;
}
if (IncludeTotalPenaltiesACM != null)
{
ws.Cells[row, col].Value = "Всего штрафных";
ws.Cells[row, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Column(col).Width = 15;
col++;
}
}
//Format the header for columns
using (ExcelRange rng = ws.Cells[row, 1, row, col])
{
rng.Style.Font.Bold = true;
rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
//rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
//rng.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(79, 129, 189)); //Set color to dark blue
//rng.Style.Font.Color.SetColor(Color.White);
}
foreach (var utr in userTournamentResults)
{
row++;
int usersCount = utr.Users.Count() - 1;
col = 1;
// Place
if (usersCount > 0)
ws.Cells[row, col, row + usersCount, col].Merge = true;
ws.Cells[row, col, row + usersCount, col].Value = utr.Place;
ws.Cells[row, col, row + usersCount, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row, col, row + usersCount, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[row, col, row + usersCount, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
col++;
// Name
if (IncludeName != null)
{
if (usersCount > 0)
ws.Cells[row, col, row + usersCount, col].Merge = true;
ws.Cells[row, col, row + usersCount, col].Value = utr.Name;
ws.Cells[row, col, row + usersCount, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row, col, row + usersCount, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[row, col, row + usersCount, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
col++;
}
// Email
if (IncludeEmail != null)
{
int i = 0;
foreach (var u in utr.Users)
{
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row + i++, col].Value = u.Email;
}
col++;
}
// FIO
if (IncludeFIO != null)
{
int i = 0;
foreach (var u in utr.Users)
{
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row + i++, col].Value = (u.SecondName + " " + u.FirstName + " " + u.ThirdName).Trim();
}
col++;
}
// BirthDay
if (IncludeBirthDay != null)
{
int i = 0;
foreach (var u in utr.Users)
{
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row + i++, col].Value = u.BirthDay != null ? ((DateTime)u.BirthDay).ToString("dd.MM.yyyy") : String.Empty;
}
col++;
}
// Phone number
if (IncludeBirthDay != null)
{
int i = 0;
foreach (var u in utr.Users)
{
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row + i++, col].Value = u.PhoneNumber;
}
col++;
}
// Country
if (IncludeCountry != null)
{
int i = 0;
foreach (var u in utr.Users)
{
// Country entity don't loaded from u.Country, becouse it's IEnumarable collection
var country = repository.Country.FirstOrDefault(c => c.CountryID == u.CountryID);
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row + i++, col].Value = country != null ? country.Name : String.Empty;
}
col++;
}
// City
if (IncludeCity != null)
{
int i = 0;
foreach (var u in utr.Users)
{
var city = repository.City.FirstOrDefault(c => c.CityID == u.CityID);
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row + i++, col].Value = city != null ? (city.Name + " " + city.Area + " " + city.Region) : String.Empty;
}
col++;
}
// Institution
if (IncludeInstitution != null)
{
int i = 0;
foreach (var u in utr.Users)
{
var institution = repository.Institutions.FirstOrDefault(ins => ins.InstitutionID == u.InstitutionID);
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row + i++, col].Value = institution != null ? institution.Name : String.Empty;
}
col++;
}
// Category
if (IncludeCategory != null)
{
int i = 0;
foreach (var u in utr.Users)
{
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
string category = "";
switch (u.Category)
{
case UserCategories.None:
category = "Не выбрано";
break;
case UserCategories.School:
category = "Школьник";
break;
case UserCategories.Student:
category = "Студент";
break;
case UserCategories.Teacher:
category = "Преподаватель";
break;
case UserCategories.Other:
category = "Другое";
break;
}
ws.Cells[row + i++, col].Value = category;
}
col++;
}
// Grade level
if (IncludeGradeLevel != null)
{
int i = 0;
foreach (var u in utr.Users)
{
ws.Cells[row + i, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row + i++, col].Value = u.GradeLevel;
}
col++;
}
// Problems results
if (IncludeProblemsResults != null)
{
foreach (var pr in utr.ProblemsResults)
{
string color = "transparent";
string text = null;
if (tournament.Format == TournamentFormats.IOI)
text = pr.Penalties > 0 ? pr.Score.ToString() : String.Empty;
else if (tournament.Format == TournamentFormats.ACM)
text = pr.Accept ? "+" : "-" + pr.Penalties;
if (pr.Accept == true)
{
color = "#9BE69B";
}
else if (pr.Penalties > 0)
{
if (pr.Score > 0)
{
color = "#FFD798";
}
else
{
color = "#FF9090";
}
}
else
{
text = "";
}
if (usersCount > 0)
ws.Cells[row, col, row + usersCount, col].Merge = true;
ws.Cells[row, col, row + usersCount, col].Value = text;
ws.Cells[row, col, row + usersCount, col].Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
ws.Cells[row, col, row + usersCount, col].Style.Fill.BackgroundColor.SetColor(ColorTranslator.FromHtml(color)); //Set color to dark blue
ws.Cells[row, col, row + usersCount, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row, col, row + usersCount, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[row, col, row + usersCount, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
col++;
}
}
if (tournament.Format == TournamentFormats.IOI)
{
// Total score
if (IncludeTotalScoreIOI != null)
{
if (usersCount > 0)
ws.Cells[row, col, row + usersCount, col].Merge = true;
ws.Cells[row, col, row + usersCount, col].Value = utr.TotalScore;
ws.Cells[row, col, row + usersCount, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row, col, row + usersCount, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[row, col, row + usersCount, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
col++;
}
}
else if (tournament.Format == TournamentFormats.ACM)
{
// Total accepted
if (IncludeTotalAcceptedACM != null)
{
if (usersCount > 0)
ws.Cells[row, col, row + usersCount, col].Merge = true;
ws.Cells[row, col, row + usersCount, col].Value = utr.TotalAccepted;
ws.Cells[row, col, row + usersCount, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row, col, row + usersCount, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[row, col, row + usersCount, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
col++;
}
// Total penalties
if (IncludeTotalPenaltiesACM != null)
{
if (usersCount > 0)
ws.Cells[row, col, row + usersCount, col].Merge = true;
ws.Cells[row, col, row + usersCount, col].Value = utr.TotalPenalties;
ws.Cells[row, col, row + usersCount, col].Style.Border.BorderAround(ExcelBorderStyle.Thin);
ws.Cells[row, col, row + usersCount, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
ws.Cells[row, col, row + usersCount, col].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
col++;
}
}
row += usersCount;
}
////Example how to Format Column 1 as numeric
//using (ExcelRange col = ws.Cells[2, 1, 2 + tbl.Rows.Count, 1])
//{
// col.Style.Numberformat.Format = "#,##0.00";
// col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
//}
//Write it back to the client
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=" + tournament.Name + ".xlsx");
Response.BinaryWrite(pck.GetAsByteArray());
}
return;
}
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();
}
public void ExcelCustomer(NodeUser user, NodePrice price)
{
// On va générer une facture excel à partir des données de l'utilisateur (sa commande)
// et des données référence des prix (NodePrice)
ConfigManager cm = ConfigManager.getSingleton();
if (File.Exists(cm.getExcelTemplateFile()))
{
using (ExcelPackage p = new ExcelPackage(new FileInfo(cm.getExcelTemplateFile()), true))
{
//Set up the headers
ExcelWorksheet ws = p.Workbook.Worksheets[1];
int nbphoto = user.NbPhoto();
idFacture = nbIDFacture();
// Identification de la facture
ws.Cells["B1"].Value = String.Format("FACTURE N° {0}", idFacture.ToString().PadLeft(6, '0'));
ws.Cells["B2"].Value = DateTime.Today.ToString("yyyy-MM-dd");
// Identification du client
ws.Cells["C6"].Value = user.chamberNumber.ToString();
ws.Cells["C4"].Value = user.name;
ws.Cells["C5"].Value = user.firstname;
ws.Cells["C7"].Value = user.LeavingDate;
#region Ici, on va générer tous les tableaux de données qui seront utilisés pour la suite de la construction du fichier excel
List<OrderedItem> listForfaitsDivers = new List<OrderedItem>();
for (int i = 0; i < price.NbForfaits(); i++)
{
XmlNode n = price.getForfait(i);
OrderedItem item = new OrderedItem();
item.Name = XMLTools.GetAttributeStringValue(n, "name");
listForfaitsDivers.Add(item);
}
Int32 TotalPhotoCD = 0;
Int32 TotalPhotoTirage = 0;
List<OrderedItem> listFormatPhotos = new List<OrderedItem>();
List<OrderedItem> listMerchandising = new List<OrderedItem>();
List<OrderedItem> listImageOnBook = new List<OrderedItem>();
for (int i = 0; i < price.NbProduct(); i++)
{
XmlNode n = price.getProduct(i);
String formatPhoto = XMLTools.GetAttributeStringValue(n, "formatphoto");
if (formatPhoto.ToLower() == "true")
{
OrderedItem item = new OrderedItem();
String currentFormatName = XMLTools.GetAttributeStringValue(n, "name");
item.Name = GetFormatDisplayName(currentFormatName);
item.UnitPrice = XMLTools.GetAttributePriceValue(n, "price");
// parcourir la commande utilisateur pour déterminer les quantités !!
// pour chaque photo commandée par l'utilisateur
for (int j = 0; j < user.NbPhoto(); j++)
{
XmlNode img = user.GetPhoto(j);
// on regarde si le format actuel a été commandé et si oui, en combien d'exemplaires
String strOrderedQuantity = XMLTools.GetAttributeStringValue(img, currentFormatName);
// Le format a été commandé, on va récupérer le chiffre et l'ajouter à la quantité de photos commandées
if (!String.IsNullOrEmpty(strOrderedQuantity))
{
Int32 qty = 0;
Int32.TryParse(strOrderedQuantity, out qty);
item.Quantity += qty;
TotalPhotoTirage += qty;
}
}
listFormatPhotos.Add(item);
}
else
{
OrderedItem item = new OrderedItem();
String currentFormatName = XMLTools.GetAttributeStringValue(n, "name");
item.Name = XMLTools.GetAttributeStringValue(n, "description");
if (item.Name == String.Empty)
item.Name = currentFormatName;
item.UnitPrice = XMLTools.GetAttributePriceValue(n, "price");
// parcourir la commande utilisateur pour déterminer les quantités !!
// pour chaque photo commandée par l'utilisateur
for (int j = 0; j < user.NbPhoto(); j++)
{
XmlNode img = user.GetPhoto(j);
// on regarde si le format actuel a été commandé et si oui, en combien d'exemplaires
String strOrderedQuantity = XMLTools.GetAttributeStringValue(img, currentFormatName);
// Le format a été commandé, on va récupérer le chiffre et l'ajouter à la quantité de photos commandées
if (!String.IsNullOrEmpty(strOrderedQuantity))
{
Int32 qty = 0;
Int32.TryParse(strOrderedQuantity, out qty);
item.Quantity += qty;
}
}
listMerchandising.Add(item);
}
}
// avant de finir, on reparcours encore la commande utilisateur pour calculer le nombre d'images commandées sur CD (minimum 10 normalement, mais ça c'est censé etre checké en amont)
for (int j = 0; j < user.NbPhoto(); j++)
{
// On en profite également pour regarder si la photo a été commandée sur CD
// Dans ce cas, on incrément le compteur. cela nous permettra de calculer le prix de revient du CD
if (user.isPhotoOnCD(j))
{
TotalPhotoCD++;
}
}
// avant de finir, on reparcours encore la commande utilisateur pour calculer le nombre d'images commandées sur CD (minimum 10 normalement, mais ça c'est censé etre checké en amont)
for (int j = 0; j < user.NbPhoto(); j++)
{
XmlNode img = user.GetPhoto(j);
// On en profite également pour regarder si la photo a été commandée sur CD
// Dans ce cas, on incrément le compteur. cela nous permettra de calculer le prix de revient du CD
}
// Maintenant on détermine les remises auxquelles l'utilisateur a le droit
String strPromoList = String.Empty;
Int32 pourcentageRemise = 0;
for (int i = 0; i < price.NbPromotion(); i++)
{
XmlNode n = price.getPromotion(i);
if (!String.IsNullOrEmpty(strPromoList))
strPromoList += " / ";
Int32 nbPhotos = XMLTools.GetAttributeIntValue(n, "nbphoto");
Int32 montantReduction = XMLTools.GetAttributeIntValue(n, "promotion");
strPromoList += String.Format("{0} P={1}%", nbPhotos.ToString(), montantReduction.ToString());
if (TotalPhotoTirage >= nbPhotos && pourcentageRemise < montantReduction)
{
pourcentageRemise = montantReduction;
}
}
#endregion
// On va itérer sur chaque format de photo disponible
int row = STARTING_ROW;
createCategoryRows(ref ws, row, listFormatPhotos);
row += listFormatPhotos.Count - 1;
createSubTotalRow(ref ws, ++row);
createRemiseRow(ref ws, ++row, 0.0, pourcentageRemise); // remise à calculer
createTotalRow(ref ws, ++row);
//createPromotionListRow(ref ws, ++row, strPromoList);
// Formules photos
List<OrderedItem> listFormulesPhotos = new List<OrderedItem>();
if (user.withoutprinting )
{
if (user.NbPhoto() == 12)
{
listFormulesPhotos.Add(new OrderedItem() { Name = "Forfait 50 Images sur CD", Quantity = 1, UnitPrice = price.getforfait50CD() });
}
else
{
listFormulesPhotos.Add(new OrderedItem() { Name = "Forfait 100 Images sur CD", Quantity = 1, UnitPrice = price.getforfait100CD() });
}
}
else
{
listFormulesPhotos.Add(new OrderedItem() { Name = "Fichiers numériques sur CD", Quantity = TotalPhotoCD, UnitPrice = price.PrixFichierNumerique() });
}
createCategoryRows(ref ws, ++row, listFormulesPhotos);
row += listFormulesPhotos.Count - 1;
createInterCategoryRow(ref ws, ++row);
// merchandising
createCategoryRows(ref ws, ++row, listMerchandising);
row += listMerchandising.Count - 1;
createInterCategoryRow(ref ws, ++row);
// Nombre de page sur le livre.
// 20 pages a 300 euros soit 12000 Mur
// 500 mur la page supplémentaire , fonctionnant par 2 pages
if (user.orderBook == true)
{
int nbPageOnBook = user.getNbPageOnBook;
int nbPageSupplementaire = nbPageOnBook -20;
row += createPhotoOnBook(ref ws, ++row, nbPageSupplementaire, price.getPrixFortaitBookPrestige(), price.getprixPageBookMore());
createInterCategoryRow(ref ws, ++row);
}
// forfaits divers
createCategoryRows(ref ws, ++row, listForfaitsDivers, false);
row += listForfaitsDivers.Count - 1;
// FINALLY
// LE GRAND TOTAL
createMainTotalQuantiteRow(ref ws, ++row, listFormatPhotos);
createMainTotalTtcRow(ref ws, ++row, listFormatPhotos);
createMainTVARow(ref ws, ++row);
Byte[] bin = p.GetAsByteArray();
string file = String.Format("{0}\\{1}-{2}.{3}.{4}-{5}.xlsx", user.getDirectory(), idFacture.ToString().PadLeft(5, '0'), user.name, user.firstname, user.chamberNumber, DateTime.Now.ToString("yyyyMMdd"));
try
{
File.WriteAllBytes(file, bin);
ShellExecute se = new ShellExecute();
//se.Verb = ShellExecute.PrintFile;
se.Path = file;
se.Execute();
}
catch (Exception e)
{
MessageBox.Show("Error Exporting Excel " + e.Message);
}
}
}
else
{
MessageBox.Show("Unable to open template file located at : \"" + cm.getExcelTemplateFile() + "\". Please check the file and try again.");
}
}
/// <summary>
/// Sample 3
/// Uses a cached template
/// </summary>
private void Sample3()
{
if (Application["Sample3Template"] == null) //Check if the template is loaded
{
//Here we create the template.
//As an alternative the template could be loaded from disk or from a resource.
ExcelPackage pckTemplate = new ExcelPackage();
var wsTemplate = pckTemplate.Workbook.Worksheets.Add("Sample3");
wsTemplate.Cells["A1"].Value = "Sample 3";
wsTemplate.Cells["A1"].Style.Font.Bold = true;
var shape = wsTemplate.Drawings.AddShape("Shape1", eShapeStyle.Rect);
shape.SetPosition(50, 200);
shape.SetSize(200, 100);
shape.Text = "Sample 3 uses a template that is stored in the application cashe.";
pckTemplate.Save();
Application["Sample3Template"] = pckTemplate.Stream;
}
//Open the new package with the template stream.
//The template stream is copied to the new stream in the constructor
ExcelPackage pck = new ExcelPackage(new MemoryStream(), Application["Sample3Template"] as Stream);
var ws = pck.Workbook.Worksheets[1];
int row = new Random().Next(10) + 10; //Pick a random row to print the text
ws.Cells[row,1].Value = "We make a small change here, after the template has been loaded...";
ws.Cells[row, 1, row, 5].Style.Fill.PatternType = ExcelFillStyle.Solid;
ws.Cells[row, 1, row, 5].Style.Fill.BackgroundColor.SetColor(Color.LightGoldenrodYellow);
Response.BinaryWrite(pck.GetAsByteArray());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Sample1.xlsx");
}
/// <summary>
/// Sample 2
/// Demonstrates the GetAsByteArray method
/// </summary>
private void Sample2()
{
ExcelPackage pck = new ExcelPackage();
var ws = pck.Workbook.Worksheets.Add("Sample2");
ws.Cells["A1"].Value = "Sample 2";
ws.Cells["A1"].Style.Font.Bold = true;
var shape = ws.Drawings.AddShape("Shape1", eShapeStyle.Rect);
shape.SetPosition(50, 200);
shape.SetSize(200, 100);
shape.Text = "Sample 2 outputs the sheet using the Response.BinaryWrite method";
Response.BinaryWrite(pck.GetAsByteArray());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment; filename=Sample1.xlsx");
}
public byte[] Create()
{
//using (var package = new ExcelPackage(new FileInfo(@"c:\temp\ttc-excels\testy-" + DateTime.Now.ToString("yyyy-M-d HH.mm.ss") + ".xlsx")))
using (var package = new ExcelPackage())
{
var allPlayerSheet = package.Workbook.Worksheets.Add(ExcelExportResources.SheetAllPlayers);
ExcelHelper.SetHeader(
allPlayerSheet,
ExcelExportResources.PlayerName,
ExcelExportResources.PlayerAddress,
ExcelExportResources.PlayerCity,
ExcelExportResources.PlayerPhone,
ExcelExportResources.PlayerEmail
);
SetPlayersSheet(allPlayerSheet);
allPlayerSheet.Cells.AutoFitColumns();
allPlayerSheet.Column(5).Width = 50;
var vttlSheet = package.Workbook.Worksheets.Add(ExcelExportResources.SheetVttl);
SetVttlSheet(vttlSheet);
var sportaSheet = package.Workbook.Worksheets.Add(ExcelExportResources.SheetSporta);
SetSportaSheet(sportaSheet);
//package.Save(); // save to file
return package.GetAsByteArray();
}
}