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