/// <summary> /// Displays the confirmation. /// </summary> /// <param name="personId">The person identifier.</param> private void DisplayConfirmation(int personId) { PersonService personService = new PersonService(new RockContext()); Person person = personService.Get(personId); if (person != null) { Rock.Model.UserLogin user = CreateUser(person, false); string url = LinkedPageUrl("ConfirmationPage"); if (string.IsNullOrWhiteSpace(url)) { url = ResolveRockUrl("~/ConfirmAccount"); } var mergeObjects = Rock.Lava.LavaHelper.GetCommonMergeFields(this.RockPage, this.CurrentPerson); mergeObjects.Add("ConfirmAccountUrl", RootPath + url.TrimStart(new char[] { '/' })); mergeObjects.Add("Person", person); mergeObjects.Add("User", user); var recipients = new List <RecipientData>(); recipients.Add(new RecipientData(person.Email, mergeObjects)); Email.Send(GetAttributeValue("ConfirmAccountTemplate").AsGuid(), recipients, ResolveRockUrl("~/"), ResolveRockUrl("~~/"), false); ShowPanel(4); } else { ShowErrorMessage("Invalid Person"); } }
private void DisplayConfirmation(int personId) { PersonService personService = new PersonService(); Person person = personService.Get(personId); if (person != null) { Rock.Model.UserLogin user = CreateUser(person, false); var mergeObjects = new Dictionary <string, object>(); mergeObjects.Add("ConfirmAccountUrl", RootPath + "ConfirmAccount"); var personDictionary = person.ToDictionary(); mergeObjects.Add("Person", personDictionary); mergeObjects.Add("User", user.ToDictionary()); var recipients = new Dictionary <string, Dictionary <string, object> >(); recipients.Add(person.Email, mergeObjects); Email email = new Email(Rock.SystemGuid.EmailTemplate.SECURITY_CONFIRM_ACCOUNT); email.Send(recipients); ShowPanel(4); } else { ShowErrorMessage("Invalid Person"); } }
/// <summary> /// Handles the Load event of the Page control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="EventArgs" /> instance containing the event data.</param> protected void Page_Load(object sender, EventArgs e) { // get error level int errorLevel = 0; if (Request["error"] != null) { errorLevel = Int32.Parse(Request["error"].ToString()); } if (errorLevel == 1) { // check to see if the user is an admin, if so allow them to view the error details Rock.Model.UserLogin user = Rock.Model.UserService.GetCurrentUser(); GroupService service = new GroupService(); Group adminGroup = service.GetByGuid(Rock.SystemGuid.Group.GROUP_ADMINISTRATORS); if (user != null && adminGroup.Members.Where(m => m.PersonId == user.PersonId).Count() > 0) { // is an admin lErrorInfo.Text = "<h4>Exception Log:</h4>"; // get exception from Session if (Session["Exception"] != null) { ProcessException((Exception)Session["Exception"], " "); } } } // clear session object Session.Remove("Exception"); }
/// <summary> /// Displays the confirmation. /// </summary> /// <param name="personId">The person identifier.</param> private void DisplayConfirmation(int personId) { PersonService personService = new PersonService(new RockContext()); Person person = personService.Get(personId); if (person != null) { Rock.Model.UserLogin user = CreateUser(person, false); string url = LinkedPageUrl(AttributeKey.ConfirmationPage); if (string.IsNullOrWhiteSpace(url)) { url = ResolveRockUrl("~/ConfirmAccount"); } var mergeObjects = Rock.Lava.LavaHelper.GetCommonMergeFields(this.RockPage, this.CurrentPerson); mergeObjects.Add("ConfirmAccountUrl", RootPath + url.TrimStart(new char[] { '/' })); mergeObjects.Add("Person", person); mergeObjects.Add("User", user); var emailMessage = new RockEmailMessage(GetAttributeValue(AttributeKey.ConfirmAccountTemplate).AsGuid()); emailMessage.AddRecipient(new RockEmailMessageRecipient(person, mergeObjects)); emailMessage.AppRoot = ResolveRockUrl("~/"); emailMessage.ThemeRoot = ResolveRockUrl("~~/"); emailMessage.CreateCommunicationRecord = GetAttributeValue(AttributeKey.CreateCommunicationRecord).AsBoolean(); emailMessage.Send(); ShowPanel(4); } else { ShowErrorMessage("Invalid Person"); } }
private void DisplayConfirmation(int personId) { PersonService personService = new PersonService(); Person person = personService.Get(personId); if (person != null) { Rock.Model.UserLogin user = CreateUser(person, false); string url = LinkedPageUrl("ConfirmationPage"); if (string.IsNullOrWhiteSpace(url)) { url = ResolveRockUrl("~/ConfirmAccount"); } var mergeObjects = new Dictionary <string, object>(); mergeObjects.Add("ConfirmAccountUrl", RootPath + url.TrimStart(new char[] { '/' })); var personDictionary = person.ToDictionary(); mergeObjects.Add("Person", personDictionary); mergeObjects.Add("User", user.ToDictionary()); var recipients = new Dictionary <string, Dictionary <string, object> >(); recipients.Add(person.Email, mergeObjects); Email email = new Email(GetAttributeValue("ConfirmAccountTemplate")); email.Send(recipients); ShowPanel(4); } else { ShowErrorMessage("Invalid Person"); } }
/// <summary> /// Displays the success. /// </summary> /// <param name="user">The user.</param> private void DisplaySuccess(Rock.Model.UserLogin user) { Authorization.SignOut(); Authorization.SetAuthCookie(tbUserName.Text, false, false); if (user != null && user.PersonId.HasValue) { PersonService personService = new PersonService(new RockContext()); Person person = personService.Get(user.PersonId.Value); if (person != null) { try { string url = LinkedPageUrl(AttributeKey.ConfirmationPage); if (string.IsNullOrWhiteSpace(url)) { url = ResolveRockUrl("~/ConfirmAccount"); } var mergeObjects = Rock.Lava.LavaHelper.GetCommonMergeFields(this.RockPage, this.CurrentPerson); mergeObjects.Add("ConfirmAccountUrl", RootPath + url.TrimStart(new char[] { '/' })); mergeObjects.Add("Person", person); mergeObjects.Add("User", user); var emailMessage = new RockEmailMessage(GetAttributeValue(AttributeKey.AccountCreatedTemplate).AsGuid()); emailMessage.AddRecipient(new RockEmailMessageRecipient(person, mergeObjects)); emailMessage.AppRoot = ResolveRockUrl("~/"); emailMessage.ThemeRoot = ResolveRockUrl("~~/"); emailMessage.CreateCommunicationRecord = false; emailMessage.Send(); } catch (SystemException ex) { ExceptionLogService.LogException(ex, Context, RockPage.PageId, RockPage.Site.Id, CurrentPersonAlias); } string returnUrl = Request.QueryString["returnurl"]; btnContinue.Visible = !string.IsNullOrWhiteSpace(returnUrl); lSuccessCaption.Text = GetAttributeValue(AttributeKey.SuccessCaption); if (lSuccessCaption.Text.Contains("{0}")) { lSuccessCaption.Text = string.Format(lSuccessCaption.Text, person.FirstName); } ShowPanel(5); } else { ShowErrorMessage("Invalid Person"); } } else { ShowErrorMessage("Invalid User"); } }
/// <summary> /// Displays the success. /// </summary> /// <param name="user">The user.</param> private void DisplaySuccess(Rock.Model.UserLogin user) { FormsAuthentication.SignOut(); Rock.Security.Authorization.SetAuthCookie(tbUserName.Text, false, false); if (user != null && user.PersonId.HasValue) { PersonService personService = new PersonService(new RockContext()); Person person = personService.Get(user.PersonId.Value); if (person != null) { try { string url = LinkedPageUrl("ConfirmationPage"); if (string.IsNullOrWhiteSpace(url)) { url = ResolveRockUrl("~/ConfirmAccount"); } var mergeObjects = new Dictionary <string, object>(); mergeObjects.Add("ConfirmAccountUrl", RootPath + url.TrimStart(new char[] { '/' })); var personDictionary = person.ToLiquid() as Dictionary <string, object>; mergeObjects.Add("Person", personDictionary); mergeObjects.Add("User", user); var recipients = new Dictionary <string, Dictionary <string, object> >(); recipients.Add(person.Email, mergeObjects); Email.Send(GetAttributeValue("AccountCreatedTemplate").AsGuid(), recipients, ResolveRockUrl("~/"), ResolveRockUrl("~~/")); } catch (SystemException ex) { ExceptionLogService.LogException(ex, Context, RockPage.PageId, RockPage.Site.Id, CurrentPersonAlias); } string returnUrl = Request.QueryString["returnurl"]; btnContinue.Visible = !string.IsNullOrWhiteSpace(returnUrl); lSuccessCaption.Text = GetAttributeValue("SuccessCaption"); if (lSuccessCaption.Text.Contains("{0}")) { lSuccessCaption.Text = string.Format(lSuccessCaption.Text, person.FirstName); } ShowPanel(5); } else { ShowErrorMessage("Invalid Person"); } } else { ShowErrorMessage("Invalid User"); } }
private void DisplaySuccess(Rock.Model.UserLogin user) { FormsAuthentication.SignOut(); Rock.Security.Authorization.SetAuthCookie(tbUserName.Text, false, false); if (user != null && user.PersonId.HasValue) { PersonService personService = new PersonService(); Person person = personService.Get(user.PersonId.Value); if (person != null) { string url = LinkedPageUrl("ConfirmationPage"); if (string.IsNullOrWhiteSpace(url)) { url = ResolveRockUrl("~/ConfirmAccount"); } var mergeObjects = new Dictionary <string, object>(); mergeObjects.Add("ConfirmAccountUrl", RootPath + url.TrimStart(new char[] { '/' })); var personDictionary = person.ToDictionary(); mergeObjects.Add("Person", personDictionary); mergeObjects.Add("User", user.ToDictionary()); var recipients = new Dictionary <string, Dictionary <string, object> >(); recipients.Add(person.Email, mergeObjects); Email email = new Email(GetAttributeValue("AccountCreatedTemplate")); email.Send(recipients); lSuccessCaption.Text = GetAttributeValue("SuccessCaption"); if (lSuccessCaption.Text.Contains("{0}")) { lSuccessCaption.Text = string.Format(lSuccessCaption.Text, person.FirstName); } ShowPanel(5); } else { ShowErrorMessage("Invalid Person"); } } else { ShowErrorMessage("Invalid User"); } }
private void DisplaySuccess(Rock.Model.UserLogin user) { FormsAuthentication.SignOut(); Rock.Security.Authorization.SetAuthCookie(tbUserName.Text, false, false); if (user != null && user.PersonId.HasValue) { PersonService personService = new PersonService(); Person person = personService.Get(user.PersonId.Value); if (person != null) { var mergeObjects = new Dictionary <string, object>(); mergeObjects.Add("ConfirmAccountUrl", RootPath + "ConfirmAccount"); var personDictionary = new PersonDto(person).ToDictionary(); personDictionary.Add("FirstName", person.FirstName); mergeObjects.Add("Person", personDictionary); mergeObjects.Add("User", new UserLoginDto(user).ToDictionary()); var recipients = new Dictionary <string, Dictionary <string, object> >(); recipients.Add(person.Email, mergeObjects); Email email = new Email(Rock.SystemGuid.EmailTemplate.SECURITY_ACCOUNT_CREATED); email.Send(recipients); lSuccessCaption.Text = AttributeValue("SuccessCaption"); if (lSuccessCaption.Text.Contains("{0}")) { lSuccessCaption.Text = string.Format(lSuccessCaption.Text, person.FirstName); } ShowPanel(5); } else { ShowErrorMessage("Invalid Person"); } } else { ShowErrorMessage("Invalid User"); } }
protected void btnUserInfoNext_Click(object sender, EventArgs e) { Password = tbPassword.Text; PasswordConfirm = tbPasswordConfirm.Text; if (Page.IsValid) { Rock.Model.UserService userService = new Rock.Model.UserService(); Rock.Model.UserLogin user = userService.GetByUserName(tbUserName.Text); if (user == null) { DisplayDuplicates(Direction.Forward); } else { ShowErrorMessage("Username already exists"); } } }
private void GenerateExcel(object sender, EventArgs e) { using (RockContext rockContext = new RockContext()) { var newQry = GetQuery(rockContext); var hospitals = newQry.Select(q => q.Hospital).DistinctBy(h => h).ToList(); // create default settings string filename = gReport.ExportFilename; string workSheetName = "List"; string title = "Hospital Report"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", this.Page.Request.Url.OriginalString); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(workSheetName); worksheet.PrinterSettings.LeftMargin = .5m; worksheet.PrinterSettings.RightMargin = .5m; worksheet.PrinterSettings.TopMargin = .5m; worksheet.PrinterSettings.BottomMargin = .5m; //Print Title // format and set title worksheet.Cells[1, 1].Value = title; using (ExcelRange r = worksheet.Cells[1, 1, 1, 7]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 28, FontStyle.Regular)); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } worksheet.Cells[2, 1].Value = Rock.RockDateTime.Today.ToString("MMMM d, yyyy"); using (ExcelRange r = worksheet.Cells[2, 1, 2, 7]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 20, FontStyle.Regular)); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } int rowCounter = 3; foreach (var hospital in hospitals) { //Hospital header var hospitalInfo = newQry .Where(q => q.Hospital == hospital) .FirstOrDefault(); worksheet.Cells[rowCounter, 1].Value = hospital; worksheet.Cells[rowCounter, 6].Value = hospitalInfo.HospitalPhone; using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 5]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 20, FontStyle.Regular)); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; r.Style.Font.Color.SetColor(Color.White); r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 41, 55)); } using (ExcelRange r = worksheet.Cells[rowCounter, 6, rowCounter, 10]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 20, FontStyle.Regular)); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; r.Style.Font.Color.SetColor(Color.White); r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 41, 55)); } rowCounter++; //Put the address on second line worksheet.Cells[rowCounter, 1].Value = hospitalInfo.HospitalAddress; using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 7]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 18, FontStyle.Regular)); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; r.Style.Font.Color.SetColor(Color.White); r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 41, 55)); } rowCounter++; //Person header worksheet.Cells[rowCounter, 1].Value = "Name"; worksheet.Cells[rowCounter, 3].Value = "Age"; worksheet.Cells[rowCounter, 4].Value = "M/F"; worksheet.Cells[rowCounter, 5].Value = "Membership"; worksheet.Cells[rowCounter, 6].Value = "Admit Date"; worksheet.Cells[rowCounter, 7].Value = "Room"; using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 7]) { r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(200, 200, 200)); } rowCounter++; //Patient info var patients = newQry.Where(q => q.Hospital == hospital); foreach (var patient in patients) { SetExcelValue(worksheet.Cells[rowCounter, 1], patient.PersonToVisit.FullName); using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 2]) { r.Merge = true; } SetExcelValue(worksheet.Cells[rowCounter, 3], patient.PersonToVisit.FormatAge()); SetExcelValue(worksheet.Cells[rowCounter, 4], patient.PersonToVisit.Gender); SetExcelValue(worksheet.Cells[rowCounter, 5], patient.PersonToVisit.ConnectionStatusValue); SetExcelValue(worksheet.Cells[rowCounter, 6], patient.AdmitDate.HasValue?patient.AdmitDate.Value.Date.ToShortDateString():""); SetExcelValue(worksheet.Cells[rowCounter, 7], patient.Room); rowCounter++; //Second line SetExcelValue(worksheet.Cells[rowCounter, 1], "Relationships:"); using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 1]) { r.Style.Font.Bold = true; } SetExcelValue(worksheet.Cells[rowCounter, 2], GetPersonRelationships(patient.PersonToVisit)); using (ExcelRange r = worksheet.Cells[rowCounter, 2, rowCounter, 7]) { r.Merge = true; } rowCounter++; //begin third row SetExcelValue(worksheet.Cells[rowCounter, 1], "Notifier: " + patient.NotifiedBy); using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 2]) { r.Merge = true; } SetExcelValue(worksheet.Cells[rowCounter, 3], patient.Description); using (ExcelRange r = worksheet.Cells[rowCounter, 3, rowCounter, 6]) { r.Merge = true; } SetExcelValue(worksheet.Cells[rowCounter, 7], "Visits: " + patient.Visits.ToString()); //ToString to make formatting better rowCounter++; //Fourth row SetExcelValue(worksheet.Cells[rowCounter, 1], "Last Visit:"); using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 1]) { r.Merge = true; r.Style.Font.Bold = true; } SetExcelValue(worksheet.Cells[rowCounter, 2], (patient.LastVisitor != "N/A" ? patient.LastVisitor + " " : "") + (patient.LastVisitDate != "N/A" ? " on " + patient.LastVisitDate + ": " : "") + patient.LastVisitNotes); using (ExcelRange r = worksheet.Cells[rowCounter, 2, rowCounter, 7]) { r.Merge = true; } using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 7]) { r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } rowCounter++; } } // autofit columns for all cells worksheet.Cells.AutoFitColumns(0); for (var i = 1; i < 8; i++) { worksheet.Column(i).Width = 18; } byte[] byteArray; using (MemoryStream ms = new MemoryStream()) { excel.SaveAs(ms); byteArray = ms.ToArray(); } // send the spreadsheet to the browser this.Page.EnableViewState = false; this.Page.Response.Clear(); this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Page.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename); this.Page.Response.Charset = string.Empty; this.Page.Response.BinaryWrite(byteArray); this.Page.Response.Flush(); this.Page.Response.End(); } }
/// <summary> /// Handles the ExcelExportClick event of the Actions control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="EventArgs" /> instance containing the event data.</param> protected void ExcelExportClick(object sender, EventArgs e) { SavePreference(); RockContext rockContext = new RockContext(); var groupIds = cblGroups.SelectedValuesAsInt; List <Group> groups = new GroupService(rockContext).GetByIds(groupIds).ToList(); // create default settings string filename = "Breakout Group Handout"; string title = "Hospital Report"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", this.Page.Request.Url.OriginalString); int count = 0; foreach (var group in groups) { count++; ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(count.ToString() + ": " + group.Name); worksheet.PrinterSettings.LeftMargin = .5m; worksheet.PrinterSettings.RightMargin = .5m; worksheet.PrinterSettings.TopMargin = .5m; worksheet.PrinterSettings.BottomMargin = .5m; //Print Title // format and set title worksheet.Cells[1, 1].Value = group.Name; using (ExcelRange r = worksheet.Cells[1, 1, 1, 2]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 20, FontStyle.Regular)); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } worksheet.Cells[2, 1].Value = Rock.RockDateTime.Today.ToString("MMMM d, yyyy"); using (ExcelRange r = worksheet.Cells[2, 1, 2, 2]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 18, FontStyle.Regular)); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } var rowCounter = 3; var groupMembers = group.Members.Where(gm => gm.GroupMemberStatus == GroupMemberStatus.Active); foreach (var member in groupMembers.OrderBy(gm => gm.Person.NickName).ToList()) { var person = member.Person; person.LoadAttributes(); //Name using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 2]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 16, FontStyle.Regular)); // set border r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } SetExcelValue(worksheet.Cells[rowCounter, 1], person.FullName); rowCounter++; //Age + Birthday SetExcelValue( worksheet.Cells[rowCounter, 1], string.Format( "Age: {0} ({1})", person.Age, (person.BirthDate ?? new DateTime()).ToString("MMMM d, yyyy"))); //Street var homelocation = person.GetHomeLocation(); if (homelocation != null) { SetExcelValue(worksheet.Cells[rowCounter, 2], homelocation.Street1 + " " + homelocation.Street2); } else { SetExcelValue(worksheet.Cells[rowCounter, 2], "[No location on record]"); } rowCounter++; //Membership Information var membershipInfo = person.ConnectionStatusValue.Value; var baptismDateString = person.GetAttributeValue("Arena-34-404"); if (!string.IsNullOrEmpty(baptismDateString)) { var baptismDateNullable = baptismDateString.AsDateTime(); if (baptismDateNullable != null) { var baptismDate = baptismDateNullable ?? new DateTime(); membershipInfo += " Baptism Date: " + baptismDate.ToString("MMMM d, yyyy"); } } SetExcelValue(worksheet.Cells[rowCounter, 1], membershipInfo); //City State if (homelocation != null) { SetExcelValue(worksheet.Cells[rowCounter, 2], string.Format("{0}, {1} {2}", homelocation.City, homelocation.State, new string( homelocation.PostalCode.Take(5).ToArray()))); } rowCounter++; var parents = person .GetFamilyMembers() .Where(m => m.GroupRoleId == 3) .Select(m => m.Person) .ToList(); foreach (var parent in parents) { using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 1]) { r.Style.Font.SetFromFont(new Font("Calibri", 11, FontStyle.Bold)); } SetExcelValue(worksheet.Cells[rowCounter, 1], parent.FullName); SetExcelValue(worksheet.Cells[rowCounter, 2], parent.Email); rowCounter++; using (ExcelRange r = worksheet.Cells[rowCounter, 1, rowCounter, 2]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 10, FontStyle.Regular)); } var phoneNumbers = new List <string>(); foreach (var number in parent.PhoneNumbers) { phoneNumbers.Add(string.Format("{0}: {1}", number.NumberTypeValue.Value, number.NumberFormatted)); } SetExcelValue(worksheet.Cells[rowCounter, 1], string.Join(", ", phoneNumbers)); rowCounter++; } } // autofit columns for all cells worksheet.Cells.AutoFitColumns(0); for (var i = 1; i < 9; i++) { worksheet.Column(i).Width = 47; } } byte[] byteArray; using (MemoryStream ms = new MemoryStream()) { excel.SaveAs(ms); byteArray = ms.ToArray(); } // send the spreadsheet to the browser this.Page.EnableViewState = false; this.Page.Response.Clear(); this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Page.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + ".xlsx"); this.Page.Response.Charset = string.Empty; this.Page.Response.BinaryWrite(byteArray); this.Page.Response.Flush(); this.Page.Response.End(); }
protected void btnExport_Click(object sender, EventArgs e) { if (Session["IndividualData"] != null) { var individualSource = (IEnumerable <dynamic>)Session["IndividualData"]; // create default settings string workSheetName = "Export"; string title = "RockExport"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", HttpContext.Current.Request.Url.OriginalString); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(workSheetName); var headerRows = 1; int rowCounter = headerRows; int columnCounter = 1; worksheet.Cells[rowCounter, columnCounter].Value = "NickName"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.GeneralFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "LastName"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.GeneralFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "IndividualGoal"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.CurrencyFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "TotalRaised"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.CurrencyFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "PercentageRaised"; worksheet.Column(columnCounter).Style.Numberformat.Format = "0%"; // print data if (individualSource.Any()) { foreach (var individual in individualSource) { rowCounter++; var columnIndex = 1; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], individual.NickName); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, individual.NickName); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], individual.LastName); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, individual.LastName); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], decimal.Parse(individual.IndividualFundraisingGoal)); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, decimal.Parse(individual.IndividualFundraisingGoal)); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], decimal.Parse(individual.ContributionTotal)); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, decimal.Parse(individual.ContributionTotal)); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], decimal.Parse(individual.Percentage) / 100); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, decimal.Parse(individual.Percentage)); } } else { rowCounter++; var columnIndex = 1; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], string.Empty); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, string.Empty); } var range = worksheet.Cells[headerRows, 1, rowCounter, columnCounter]; var table = worksheet.Tables.Add(range, title); table.ShowHeader = true; table.ShowFilter = true; table.TableStyle = OfficeOpenXml.Table.TableStyles.None; // Format header range using (ExcelRange r = worksheet.Cells[headerRows, 1, headerRows, columnCounter]) { r.Style.Font.Bold = true; r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } // do AutoFitColumns on no more than the first 10000 rows (10000 can take 4-5 seconds, but could take several minutes if there are 100000+ rows ) int autoFitRows = Math.Min(rowCounter, 10000); var autoFitRange = worksheet.Cells[headerRows, 1, autoFitRows, columnCounter]; autoFitRange.AutoFitColumns(); // set some footer text worksheet.HeaderFooter.OddHeader.CenteredText = title; worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); var filename = string.Format("FinancialProgress_{0}.xlsx", Regex.Replace(individualSource.FirstOrDefault().GroupName, "[^A-Za-z0-9_\\- ]", string.Empty, RegexOptions.CultureInvariant)); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename); Response.Charset = string.Empty; Response.BinaryWrite(excel.ToByteArray()); Response.Flush(); Response.End(); int? groupId = this.PageParameter("GroupId").AsIntegerOrNull(); int? groupMemberId = this.PageParameter("GroupMemberId").AsIntegerOrNull(); Guid?groupGuid = GetAttributeValue("ShowSpecificGroup").AsGuidOrNull(); if (groupId.HasValue || groupMemberId.HasValue || groupGuid.HasValue) { ShowView(groupId, groupMemberId, groupGuid); } } }
/// <summary> /// Logs the error. /// </summary> /// <param name="ex">The ex.</param> /// <param name="parentException">The parent exception.</param> /// <param name="status">The status.</param> /// <param name="context">The context.</param> private void LogError(Exception ex, int parentException, string status, System.Web.HttpContext context) { try { // get the current user Rock.Model.UserLogin user = Rock.Model.UserService.GetCurrentUser(); // save the exception info to the db ExceptionLogService service = new ExceptionLogService(); ExceptionLog exceptionLog = new ExceptionLog();; exceptionLog.ParentId = parentException; exceptionLog.ExceptionDate = DateTime.Now; if (ex.InnerException != null) { exceptionLog.HasInnerException = true; } exceptionLog.Description = ex.Message; exceptionLog.StackTrace = ex.StackTrace; exceptionLog.Source = ex.Source; exceptionLog.StatusCode = status; if (context.Items["Rock:SiteId"] != null) { exceptionLog.SiteId = Int32.Parse(context.Items["Rock:SiteId"].ToString()); } if (context.Items["Rock:PageId"] != null) { exceptionLog.PageId = Int32.Parse(context.Items["Rock:PageId"].ToString()); } exceptionLog.ExceptionType = ex.GetType().Name; exceptionLog.PageUrl = context.Request.RawUrl; exceptionLog.QueryString = context.Request.QueryString.ToString(); // write cookies StringBuilder cookies = new StringBuilder(); cookies.Append("<table class=\"cookies\">"); foreach (string cookie in context.Request.Cookies) { cookies.Append("<tr><td><b>" + cookie + "</b></td><td>" + context.Request.Cookies[cookie].Value + "</td></tr>"); } cookies.Append("</table>"); exceptionLog.Cookies = cookies.ToString(); // write form items StringBuilder formItems = new StringBuilder(); cookies.Append("<table class=\"formItems\">"); foreach (string formItem in context.Request.Form) { cookies.Append("<tr><td><b>" + formItem + "</b></td><td>" + context.Request.Form[formItem].ToString() + "</td></tr>"); } cookies.Append("</table>"); exceptionLog.Form = formItems.ToString(); // write server vars StringBuilder serverVars = new StringBuilder(); cookies.Append("<table class=\"server-variables\">"); foreach (string serverVar in context.Request.ServerVariables) { serverVars.Append("<tr><td><b>" + serverVar + "</b></td><td>" + context.Request.ServerVariables[serverVar].ToString() + "</td></tr>"); } cookies.Append("</table>"); exceptionLog.ServerVariables = serverVars.ToString(); if (user != null) { exceptionLog.CreatedByPersonId = user.PersonId; } service.Add(exceptionLog, null); service.Save(exceptionLog, null); // log inner exceptions if (ex.InnerException != null) { LogError(ex.InnerException, exceptionLog.Id, status, context); } } catch (Exception) { // if you get an exception while logging an exception I guess you're hosed... try { EventLog.WriteEntry("Rock", string.Format("Exception in Global.LogError(): {0}", ex.Message), EventLogEntryType.Error); } catch { } } }
protected void btnGenerate_Click(object sender, EventArgs e) { RockContext rockContext = new RockContext(); var parentGroupGuid = GetAttributeValue("BreakoutParentGroup").AsGuid(); var gQry = new GroupService(rockContext).Queryable().Where(g => g.IsActive && !g.IsArchived && g.ParentGroup.Guid == parentGroupGuid); var gmQry = gQry.SelectMany(g => g.Members.Where(gm => gm.GroupMemberStatus == GroupMemberStatus.Active)); // Get all members of every breakout group var membersQry = gmQry.Select(gm => new { GroupId = gm.GroupId, PersonId = gm.PersonId }); //get all the breakout groups where we have members var breakoutGroups = gQry.ToList() .Where(g => g.Members.Any(gm => gm.GroupMemberStatus == GroupMemberStatus.Active)) .OrderBy(g => g.Schedule.Id) .ThenBy(g => g.Name) .ToList(); var schedules = new ScheduleService(rockContext) .GetByIds(GetAttributeValue("ScheduleIDs") .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) .Select(int.Parse) .ToList() ).ToList(); var scheduleIds = GetAttributeValue("ScheduleIDs") .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) .Select(int.Parse) .ToList(); var attendanceQry = new AttendanceService(rockContext).Queryable(); attendanceQry = attendanceQry.Where(a => a.DidAttend == true && a.Occurrence.SundayDate != null && scheduleIds.Contains(a.Occurrence.ScheduleId ?? 0)); if (!string.IsNullOrWhiteSpace(rddlCheckinGroup.SelectedValue)) { int groupId = rddlCheckinGroup.SelectedValue.AsInteger(); attendanceQry = attendanceQry.Where(a => a.Occurrence.GroupId == groupId); } var upper = drpRange.UpperValue; var lower = drpRange.LowerValue; if (upper != null) { upper = upper.Value.AddDays(1); attendanceQry = attendanceQry.Where(a => a.StartDateTime <= upper); } if (lower != null) { attendanceQry = attendanceQry.Where(a => a.StartDateTime >= lower); } //Begin creation of EXCEL file string filename = "RockExport.xls"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = "Breakout Group Attendance Summary"; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", this.Page.Request.Url.OriginalString); List <ExcelWorksheet> worksheets = new List <ExcelWorksheet>(); worksheets.Add(excel.Workbook.Worksheets.Add("Service Averages")); worksheets.Add(excel.Workbook.Worksheets.Add("Attendance Summary")); foreach (var worksheet in worksheets) { worksheet.PrinterSettings.LeftMargin = .5m; worksheet.PrinterSettings.RightMargin = .5m; worksheet.PrinterSettings.TopMargin = .5m; worksheet.PrinterSettings.BottomMargin = .5m; } //// write data to worksheet there are three supported data sources //// DataTables, DataViews and ILists int rowCounter0 = 4; int rowCounter1 = 4; int columnCounter0 = 0; int columnCounter1 = 0; List <string> columns0 = new List <string>() { "Service", "No Breakout Group" }; List <string> columns1 = new List <string>() { "Week", "Service", "No Breakout Group" }; foreach (var group in breakoutGroups) { group.LoadAttributes(); var scheduleName = "Unknown"; if (group.Schedule != null && group.Schedule.GetICalEvent() != null && group.Schedule.GetICalEvent().DtStart != null && group.Schedule.GetICalEvent().DtStart.Value != null && group.Schedule.GetICalEvent().DtStart.Value.TimeOfDay != null) { scheduleName = group.Schedule.GetICalEvent().DtStart.Value.TimeOfDay.ToTimeString(); } var name = string.Format("{0} {1}", scheduleName, group.GetAttributeValue("Letter")); columns0.Add(name); columns1.Add(name); } foreach (String column in columns0) { columnCounter0++; worksheets[0].Cells[3, columnCounter0].Value = column.SplitCase(); } foreach (String column in columns1) { columnCounter1++; worksheets[1].Cells[3, columnCounter1].Value = column.SplitCase(); } var sundays = attendanceQry .Select(a => a.Occurrence.SundayDate) .Distinct() .OrderBy(a => a) .ToList(); if (sundays.Count == 0) { nbError.Text = "No attendance records were found for the selected group and Sunday dates."; nbError.Visible = true; return; } var attendance = attendanceQry .Select(a => new { PersonId = a.PersonAlias.PersonId, ScheduleId = a.Occurrence.ScheduleId, SundayDate = a.Occurrence.SundayDate }) .ToList().Distinct(); var allMemberIds = membersQry.Select(m => m.PersonId).ToList(); //Worksheet 0 / Service Averages foreach (var schedule in schedules) { SetExcelValue(worksheets[0].Cells[rowCounter0, 1], schedule.Name); var nonMembers = attendance.Where(a => a.ScheduleId == schedule.Id && !allMemberIds.Contains(a.PersonId) ).Count() / sundays.Count(); SetExcelValue(worksheets[0].Cells[rowCounter0, 2], nonMembers); var i = 0; foreach (var group in breakoutGroups) { var breakoutGroupMemberPersonIds = membersQry.Where(m => m.GroupId == group.Id).Select(m => m.PersonId).ToList(); var groupCount = attendance.Where(a => a.ScheduleId == schedule.Id && breakoutGroupMemberPersonIds.Contains(a.PersonId) ).Count() / sundays.Count(); SetExcelValue(worksheets[0].Cells[rowCounter0, 3 + i], groupCount); i++; } rowCounter0++; } //Worsheet 1 / Attendance Summary foreach (var sunday in sundays) { SetExcelValue(worksheets[1].Cells[rowCounter1, 1], sunday.ToString("MM/dd/yyyy")); var nonMemberTotal = 0; var totals = new List <int>(); foreach (var group in breakoutGroups) { totals.Add(0); } foreach (var schedule in schedules) { SetExcelValue(worksheets[1].Cells[rowCounter1, 2], schedule.Name); var nonMembers = attendance.Where(a => a.SundayDate == sunday && a.ScheduleId == schedule.Id && !allMemberIds.Contains(a.PersonId) ).Count(); nonMemberTotal += nonMembers; SetExcelValue(worksheets[1].Cells[rowCounter1, 3], nonMembers); var i = 0; foreach (var group in breakoutGroups) { var breakoutGroupMemberPersonIds = membersQry.Where(m => m.GroupId == group.Id).Select(m => m.PersonId).ToList(); var groupCount = attendance.Where(a => a.SundayDate == sunday && a.ScheduleId == schedule.Id && breakoutGroupMemberPersonIds.Contains(a.PersonId) ).Count(); SetExcelValue(worksheets[1].Cells[rowCounter1, 4 + i], groupCount); totals[i] += groupCount; i++; } rowCounter1++; } SetExcelValue(worksheets[1].Cells[rowCounter1, 2], "Total"); worksheets[1].Cells[rowCounter1, 3].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; worksheets[1].Cells[rowCounter1, 3].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); worksheets[1].Cells[rowCounter1, 3].Style.Font.Color.SetColor(Color.Black); SetExcelValue(worksheets[1].Cells[rowCounter1, 3], nonMemberTotal); worksheets[1].Cells[rowCounter1, 4].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; worksheets[1].Cells[rowCounter1, 4].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); worksheets[1].Cells[rowCounter1, 4].Style.Font.Color.SetColor(Color.Black); for (var index = 0; index < totals.Count(); index++) { SetExcelValue(worksheets[1].Cells[rowCounter1, 4 + index], totals[index]); worksheets[1].Cells[rowCounter1, 5 + index].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; worksheets[1].Cells[rowCounter1, 5 + index].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); worksheets[1].Cells[rowCounter1, 5 + index].Style.Font.Color.SetColor(Color.Black); } rowCounter1++; } //Finish EXCEL var range = worksheets[0].Cells[3, 1, rowCounter0, columnCounter0]; var table = worksheets[0].Tables.Add(range, "table1"); // ensure each column in the table has a unique name var columnNames = worksheets[0].Cells[3, 1, 3, columnCounter0].Select(a => new { OrigColumnName = a.Text, Cell = a }).ToList(); columnNames.Reverse(); foreach (var col in columnNames) { int duplicateSuffix = 0; string uniqueName = col.OrigColumnName; // increment the suffix by 1 until there is only one column with that name while (columnNames.Where(a => a.Cell.Text == uniqueName).Count() > 1) { duplicateSuffix++; uniqueName = col.OrigColumnName + duplicateSuffix.ToString(); col.Cell.Value = uniqueName; } } table.ShowFilter = true; table.TableStyle = OfficeOpenXml.Table.TableStyles.None; worksheets[0].Cells[1, 1].Value = "Service Averages"; worksheets[1].Cells[1, 1].Value = "Attendance Summary"; foreach (var worksheet in worksheets) { // format header range using (ExcelRange r = worksheet.Cells[3, 1, 3, columnCounter1]) { r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); r.Style.Font.Color.SetColor(Color.Black); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } // format and set title using (ExcelRange r = worksheet.Cells[1, 1, 1, columnCounter1]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 22, FontStyle.Regular)); r.Style.Font.Color.SetColor(Color.White); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 41, 55)); // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } worksheet.View.FreezePanes(3, 1); worksheet.Cells.AutoFitColumns(0); } // autofit columns for all cells // set some footer text byte[] byteArray; using (MemoryStream ms = new MemoryStream()) { excel.SaveAs(ms); byteArray = ms.ToArray(); } // send the spreadsheet to the browser this.Page.EnableViewState = false; this.Page.Response.Clear(); this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Page.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename); this.Page.Response.Charset = string.Empty; this.Page.Response.BinaryWrite(byteArray); this.Page.Response.Flush(); this.Page.Response.End(); }
public ExcelPackage GLExcelExport(List <GLExcelLine> items) { var exportColumns = GetExportColumns(items); // create default settings string workSheetName = "Export"; string title = "RockExport"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", HttpContext.Current.Request.Url.OriginalString); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(workSheetName); var headerRows = 1; int rowCounter = headerRows; int columnCounter = 1; worksheet.Cells[rowCounter, columnCounter].Value = "Amount"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.FormattedNumberFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "JournalNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "JournalDescription"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.GeneralFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "Date"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.DateFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "Period"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "JournalCode"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.GeneralFormat; if (exportColumns.CompanyNumber) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "CompanyNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.RegionNumber) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "RegionNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.SuperFundNumber) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "SuperFundNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.FundNumber) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "FundNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.LocationNumber) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "LocationNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.CostCenterNumber) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "CostCenterNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.DepartmentNumber) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "DepartmentNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.AccountNumber) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "AccountNumber"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.AccountSub) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "AccountSub"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.Project) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "Project"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.UnformattedNumberFormat; } if (exportColumns.Note) { columnCounter++; worksheet.Cells[rowCounter, columnCounter].Value = "Note"; worksheet.Column(columnCounter).Style.Numberformat.Format = ExcelHelper.GeneralFormat; } // print data if (items.Any()) { foreach (var item in items) { rowCounter++; var columnIndex = 1; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.Amount); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.Amount); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.JournalNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.JournalNumber); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.JournalDescription); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.JournalDescription); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.Date); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.Date); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.Period); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.Period); columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.JournalCode); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.JournalCode); if (exportColumns.CompanyNumber) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.CompanyNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.CompanyNumber); } if (exportColumns.RegionNumber) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.RegionNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.RegionNumber); } if (exportColumns.SuperFundNumber) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.SuperFundNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.SuperFundNumber); } if (exportColumns.FundNumber) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.FundNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.FundNumber); } if (exportColumns.LocationNumber) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.LocationNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.LocationNumber); } if (exportColumns.CostCenterNumber) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.CostCenterNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.CostCenterNumber); } if (exportColumns.DepartmentNumber) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.DepartmentNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.DepartmentNumber); } if (exportColumns.AccountNumber) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.AccountNumber); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.AccountNumber); } if (exportColumns.AccountSub) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.AccountSub); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.AccountSub); } if (exportColumns.Project) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.Project); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.Project); } if (exportColumns.Note) { columnIndex++; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], item.Note); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, item.Note); } } } else { rowCounter++; var columnIndex = 1; ExcelHelper.SetExcelValue(worksheet.Cells[rowCounter, columnIndex], string.Empty); ExcelHelper.FinalizeColumnFormat(worksheet, columnIndex, string.Empty); } var range = worksheet.Cells[headerRows, 1, rowCounter, columnCounter]; var table = worksheet.Tables.Add(range, title); // ensure each column in the table has a unique name var columnNames = worksheet.Cells[headerRows, 1, headerRows, columnCounter].Select(a => new { OrigColumnName = a.Text, Cell = a }).ToList(); columnNames.Reverse(); foreach (var col in columnNames) { int duplicateSuffix = 0; string uniqueName = col.OrigColumnName; // increment the suffix by 1 until there is only one column with that name while (columnNames.Where(a => a.Cell.Text == uniqueName).Count() > 1) { duplicateSuffix++; uniqueName = col.OrigColumnName + duplicateSuffix.ToString(); col.Cell.Value = uniqueName; } } table.ShowHeader = true; table.ShowFilter = true; table.TableStyle = OfficeOpenXml.Table.TableStyles.None; // Format header range using (ExcelRange r = worksheet.Cells[headerRows, 1, headerRows, columnCounter]) { r.Style.Font.Bold = true; r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } // do AutoFitColumns on no more than the first 10000 rows (10000 can take 4-5 seconds, but could take several minutes if there are 100000+ rows ) int autoFitRows = Math.Min(rowCounter, 10000); var autoFitRange = worksheet.Cells[headerRows, 1, autoFitRows, columnCounter]; autoFitRange.AutoFitColumns(); // set some footer text worksheet.HeaderFooter.OddHeader.CenteredText = title; worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); return(excel); }
/// <summary> /// Logs the error to database /// </summary> /// <param name="ex">The ex.</param> /// <param name="parentException">The parent exception.</param> /// <param name="status">The status.</param> /// <param name="context">The context.</param> private void LogError(Exception ex, int parentException, string status, System.Web.HttpContext context) { try { // get the current user Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); // save the exception info to the db ExceptionLogService service = new ExceptionLogService(); ExceptionLog exceptionLog = new ExceptionLog();; exceptionLog.ParentId = parentException; exceptionLog.ExceptionDateTime = DateTime.Now; if (ex.InnerException != null) { exceptionLog.HasInnerException = true; } exceptionLog.Description = ex.Message; exceptionLog.StackTrace = ex.StackTrace; exceptionLog.Source = ex.Source; exceptionLog.StatusCode = status; if (context.Items["Rock:SiteId"] != null) { exceptionLog.SiteId = Int32.Parse(context.Items["Rock:SiteId"].ToString()); } if (context.Items["Rock:PageId"] != null) { exceptionLog.PageId = Int32.Parse(context.Items["Rock:PageId"].ToString()); } exceptionLog.ExceptionType = ex.GetType().Name; exceptionLog.PageUrl = context.Request.RawUrl; exceptionLog.QueryString = context.Request.QueryString.ToString(); // write cookies StringBuilder cookies = new StringBuilder(); cookies.Append("<table class=\"cookies\">"); foreach (string cookie in context.Request.Cookies) { cookies.Append("<tr><td><b>" + cookie + "</b></td><td>" + context.Request.Cookies[cookie].Value + "</td></tr>"); } cookies.Append("</table>"); exceptionLog.Cookies = cookies.ToString(); // write form items StringBuilder formItems = new StringBuilder(); cookies.Append("<table class=\"formItems\">"); foreach (string formItem in context.Request.Form) { cookies.Append("<tr><td><b>" + formItem + "</b></td><td>" + context.Request.Form[formItem].ToString() + "</td></tr>"); } cookies.Append("</table>"); exceptionLog.Form = formItems.ToString(); // write server vars StringBuilder serverVars = new StringBuilder(); serverVars.Append("<table class=\"server-variables\">"); foreach (string serverVar in context.Request.ServerVariables) { serverVars.Append("<tr><td><b>" + serverVar + "</b></td><td>" + context.Request.ServerVariables[serverVar].ToString() + "</td></tr>"); } serverVars.Append("</table>"); exceptionLog.ServerVariables = serverVars.ToString(); if (userLogin != null) { exceptionLog.CreatedByPersonId = userLogin.PersonId; } service.Add(exceptionLog, null); service.Save(exceptionLog, null); // log inner exceptions if (ex.InnerException != null) { LogError(ex.InnerException, exceptionLog.Id, status, context); } } catch (Exception) { // If logging the exception fails, write the exception to a file try { string directory = AppDomain.CurrentDomain.BaseDirectory; directory = Path.Combine(directory, "Logs"); // check that directory exists if (!Directory.Exists(directory)) { Directory.CreateDirectory(directory); } // create full path to the fie string filePath = Path.Combine(directory, "RockExceptions.csv"); // write to the file System.IO.File.AppendAllText(filePath, string.Format("{0},{1},\"{2}\"\r\n", DateTime.Now.ToString(), EventLogEntryType.Error, ex.Message)); } catch { // failed to write to database and also failed to write to log file, so there is nowhere to log this error } } }
private void GenerateExcel(object sender, EventArgs e) { var medicalItems = GetMedicalItems(); string filename = gGrid.ExportFilename; string workSheetName = "List"; string title = "Medication Information"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", this.Page.Request.Url.OriginalString); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(workSheetName); worksheet.PrinterSettings.LeftMargin = .5m; worksheet.PrinterSettings.RightMargin = .5m; worksheet.PrinterSettings.TopMargin = .5m; worksheet.PrinterSettings.BottomMargin = .5m; //// write data to worksheet there are three supported data sources //// DataTables, DataViews and ILists int rowCounter = 4; int columnCounter = 0; List <string> columns = new List <string>() { "Name", "Medication", "Instructions", "Schedule" }; var filterAttribute = ""; var hasFilter = false; if (!string.IsNullOrWhiteSpace(ddlAttribute.Label)) { hasFilter = true; columns.Add(ddlAttribute.Label); filterAttribute = GetAttributeValue("GroupMemberAttributeFilter"); } // print headings foreach (String column in columns) { columnCounter++; worksheet.Cells[3, columnCounter].Value = column.SplitCase(); } foreach (var item in medicalItems) { SetExcelValue(worksheet.Cells[rowCounter, 1], item.Person); SetExcelValue(worksheet.Cells[rowCounter, 2], item.Medication); SetExcelValue(worksheet.Cells[rowCounter, 3], item.Instructions); SetExcelValue(worksheet.Cells[rowCounter, 4], item.Schedule); if (hasFilter) { item.GroupMember.LoadAttributes(); if (item.GroupMember != null) { SetExcelValue(worksheet.Cells[rowCounter, 5], item.GroupMember.GetAttributeValue(filterAttribute)); } } rowCounter++; } var range = worksheet.Cells[3, 1, rowCounter, columnCounter]; var table = worksheet.Tables.Add(range, "table1"); // ensure each column in the table has a unique name var columnNames = worksheet.Cells[3, 1, 3, columnCounter].Select(a => new { OrigColumnName = a.Text, Cell = a }).ToList(); columnNames.Reverse(); foreach (var col in columnNames) { int duplicateSuffix = 0; string uniqueName = col.OrigColumnName; // increment the suffix by 1 until there is only one column with that name while (columnNames.Where(a => a.Cell.Text == uniqueName).Count() > 1) { duplicateSuffix++; uniqueName = col.OrigColumnName + duplicateSuffix.ToString(); col.Cell.Value = uniqueName; } } table.ShowFilter = true; table.TableStyle = OfficeOpenXml.Table.TableStyles.None; // format header range using (ExcelRange r = worksheet.Cells[3, 1, 3, columnCounter]) { r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); r.Style.Font.Color.SetColor(Color.Black); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } // format and set title worksheet.Cells[1, 1].Value = title; using (ExcelRange r = worksheet.Cells[1, 1, 2, columnCounter]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 22, FontStyle.Regular)); r.Style.Font.Color.SetColor(Color.White); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 41, 55)); // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } // freeze panes worksheet.View.FreezePanes(3, 1); // autofit columns for all cells worksheet.Cells.AutoFitColumns(1000); byte[] byteArray; using (MemoryStream ms = new MemoryStream()) { excel.SaveAs(ms); byteArray = ms.ToArray(); } // send the spreadsheet to the browser this.Page.EnableViewState = false; this.Page.Response.Clear(); this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Page.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename); this.Page.Response.Charset = string.Empty; this.Page.Response.BinaryWrite(byteArray); this.Page.Response.Flush(); this.Page.Response.End(); }
/// <summary> /// Handles the ExcelExportClick event of the Actions control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="EventArgs" /> instance containing the event data.</param> protected void Actions_ExcelExportClick(object sender, EventArgs e) { // create default settings string filename = gReport.ExportFilename; string workSheetName = "List"; string title = "Communion List - " + Rock.RockDateTime.Today.ToString("MMMM d, yyyy"); ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", this.Page.Request.Url.OriginalString); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(workSheetName); worksheet.PrinterSettings.LeftMargin = .5m; worksheet.PrinterSettings.RightMargin = .5m; worksheet.PrinterSettings.TopMargin = .5m; worksheet.PrinterSettings.BottomMargin = .5m; //// write data to worksheet there are three supported data sources //// DataTables, DataViews and ILists int rowCounter = 4; int columnCounter = 0; // print headings foreach (String column in new List <String>() { "Zip", "Name", "Campus", "Address", "Phone", "Notes" }) { columnCounter++; worksheet.Cells[3, columnCounter].Value = column.SplitCase(); } PhoneNumberService phoneNumberService = new PhoneNumberService(new RockContext()); Guid homePhone = Rock.SystemGuid.DefinedValue.PERSON_PHONE_TYPE_HOME.AsGuid(); // print data foreach (CommunionData row in getQuery <CommunionData>()) { SetExcelValue(worksheet.Cells[rowCounter, 1], row.PostalCode.Length > 5 ? row.PostalCode.Substring(0, 5) : row.PostalCode); SetExcelValue(worksheet.Cells[rowCounter, 2], row.Person.FullName); SetExcelValue(worksheet.Cells[rowCounter, 3], row.Campus); SetExcelValue(worksheet.Cells[rowCounter, 4], (row.Location != "Home" ? row.Location + "\r\n" : "") + row.Address + (!string.IsNullOrEmpty(row.Room) ? "\r\nRoom: " + row.Room : "") + (!string.IsNullOrWhiteSpace(row.FacilityNumber) ? "\r\n" + row.FacilityNumber : "")); SetExcelValue(worksheet.Cells[rowCounter, 5], phoneNumberService.GetByPersonId(row.Person.Id).Where(p => p.NumberTypeValue.Guid == homePhone).Select(p => p.NumberFormatted).FirstOrDefault()); SetExcelValue(worksheet.Cells[rowCounter, 6], row.Description); worksheet.Cells[rowCounter, 6].Style.WrapText = true; rowCounter++; } var range = worksheet.Cells[3, 1, rowCounter, columnCounter]; // use conditionalFormatting to create the alternate row style var conditionalFormatting = range.ConditionalFormatting.AddExpression(); conditionalFormatting.Formula = "MOD(ROW()+1,2)=0"; conditionalFormatting.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; conditionalFormatting.Style.Fill.BackgroundColor.Color = Color.FromArgb(240, 240, 240); var table = worksheet.Tables.Add(range, "table1"); // ensure each column in the table has a unique name var columnNames = worksheet.Cells[3, 1, 3, columnCounter].Select(a => new { OrigColumnName = a.Text, Cell = a }).ToList(); columnNames.Reverse(); foreach (var col in columnNames) { int duplicateSuffix = 0; string uniqueName = col.OrigColumnName; // increment the suffix by 1 until there is only one column with that name while (columnNames.Where(a => a.Cell.Text == uniqueName).Count() > 1) { duplicateSuffix++; uniqueName = col.OrigColumnName + duplicateSuffix.ToString(); col.Cell.Value = uniqueName; } } table.ShowFilter = true; table.TableStyle = OfficeOpenXml.Table.TableStyles.None; // format header range using (ExcelRange r = worksheet.Cells[3, 1, 3, columnCounter]) { r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); r.Style.Font.Color.SetColor(Color.Black); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } // format and set title worksheet.Cells[1, 1].Value = title; using (ExcelRange r = worksheet.Cells[1, 1, 1, columnCounter]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 22, FontStyle.Regular)); r.Style.Font.Color.SetColor(Color.White); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 41, 55)); // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } using (ExcelRange r = worksheet.Cells[3, 1, rowCounter, columnCounter]) { r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } // TODO: add image to worksheet // freeze panes worksheet.View.FreezePanes(3, 1); // autofit columns for all cells worksheet.Cells.AutoFitColumns(0); // Set all the column widths worksheet.Column(2).Width = 20; worksheet.Column(4).Width = 30; worksheet.Column(6).Width = 45; // add alternating highlights // set some footer text worksheet.HeaderFooter.OddHeader.CenteredText = title; worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); byte[] byteArray; using (MemoryStream ms = new MemoryStream()) { excel.SaveAs(ms); byteArray = ms.ToArray(); } // send the spreadsheet to the browser this.Page.EnableViewState = false; this.Page.Response.Clear(); this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Page.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename); this.Page.Response.Charset = string.Empty; this.Page.Response.BinaryWrite(byteArray); this.Page.Response.Flush(); this.Page.Response.End(); }
private void BindGrid() { var scheduleIds = GetAttributeValue("ScheduleIDs"); rockContext = new RockContext(); schedules = new ScheduleService(rockContext) .GetByIds(GetAttributeValue("ScheduleIDs") .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) .Select(int.Parse) .ToList() ).ToList(); var parentGroupGuidString = GetAttributeValue("ParentBreakoutGroup"); if (string.IsNullOrWhiteSpace(parentGroupGuidString)) { return; } var parentGroupGuid = parentGroupGuidString.AsGuid(); var groupService = new GroupService(rockContext); var parentGroup = groupService.Get(parentGroupGuid); var breakoutGroupMembers = groupService.Queryable() .Where(g => g.IsActive && !g.IsArchived) .SelectMany(g => g.Groups.Where(g2 => g2.IsActive && g2.ParentGroupId == parentGroup.Id)) .SelectMany(g => g.Members.Where(gm => gm.GroupMemberStatus == GroupMemberStatus.Active)) .Select(m => m.Person) .SelectMany(p => p.Aliases) .Select(a => a.Id); var selectedScheduleIds = schedules.Select(s => s.Id).ToList(); var attendanceService = new AttendanceService(rockContext); var attendanceQry = attendanceService.Queryable() .Where(a => !breakoutGroupMembers.Contains(a.PersonAliasId ?? 0) && a.DidAttend == true && a.Occurrence.ScheduleId != null && selectedScheduleIds.Contains(a.Occurrence.ScheduleId ?? 0)); if (gpGrade.SelectedGradeValue != null) { var graduationYear = Person.GraduationYearFromGradeOffset(gpGrade.SelectedGradeValue.Value.AsInteger()); attendanceQry = attendanceQry.Where(a => a.PersonAlias.Person.GraduationYear == graduationYear); } var lower = drRange.LowerValue; if (lower != null) { attendanceQry = attendanceQry.Where(a => a.StartDateTime >= lower); } var upper = drRange.UpperValue; if (upper != null) { upper = upper.Value.AddDays(1); attendanceQry = attendanceQry.Where(a => a.StartDateTime <= upper); } var attendance = attendanceQry.Select(a => new { Person = a.PersonAlias.Person, Attendance = a } ).ToList(); var children = attendance .DistinctBy(a => a.Person.Id) .Select(a => a.Person) .ToList(); // create default settings string filename = "WithoutBreakoutGroupAttendance.xlsx"; string workSheetName = "List"; string title = "Without Breakout Group Attendance"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", this.Page.Request.Url.OriginalString); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(workSheetName); worksheet.PrinterSettings.LeftMargin = .5m; worksheet.PrinterSettings.RightMargin = .5m; worksheet.PrinterSettings.TopMargin = .5m; worksheet.PrinterSettings.BottomMargin = .5m; //// write data to worksheet there are three supported data sources //// DataTables, DataViews and ILists int rowCounter = 4; int columnCounter = 0; List <string> columns = new List <string>() { "Name", "Gender", "Birthdate", "Grade", "First Time", "Last Time", "Total" }; foreach (var schedule in schedules) { columns.Add(schedule.Name); } // print headings foreach (String column in columns) { columnCounter++; worksheet.Cells[3, columnCounter].Value = column.SplitCase(); } List <int> scheduleTotals = new List <int>(); var schedulePreference = GetUserPreference(BlockCache.Guid.ToString() + "Schedule") .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) .ToList(); foreach (var schedulePref in schedulePreference) { scheduleTotals.Add(0); } //print data foreach (Person person in children) { var currentAttendances = attendance .Where(a => a.Person.Id == person.Id) .OrderBy(a => a.Attendance.StartDateTime).ToList(); SetExcelValue(worksheet.Cells[rowCounter, 1], person.FullName); SetExcelValue(worksheet.Cells[rowCounter, 2], person.Gender.ToString()); SetExcelValue(worksheet.Cells[rowCounter, 3], (person.BirthDate ?? new DateTime()).ToString("MM/dd/yyyy")); SetExcelValue(worksheet.Cells[rowCounter, 4], person.GradeFormatted); var firstAttendance = currentAttendances.FirstOrDefault().Attendance.StartDateTime.ToString("MM/dd/yyyy"); SetExcelValue(worksheet.Cells[rowCounter, 5], firstAttendance); var lastAttendance = currentAttendances.LastOrDefault().Attendance.StartDateTime.ToString("MM/dd/yyyy"); SetExcelValue(worksheet.Cells[rowCounter, 6], lastAttendance); SetExcelValue(worksheet.Cells[rowCounter, 7], currentAttendances.Count); var i = 0; foreach (var schedule in schedules) { var count = currentAttendances.Where(a => a.Attendance.Occurrence.ScheduleId == schedule.Id).Count(); SetExcelValue(worksheet.Cells[rowCounter, 8 + i], count); i++; } worksheet.Cells[rowCounter, 7].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[rowCounter, 7].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); rowCounter++; } var range = worksheet.Cells[3, 1, rowCounter, columnCounter]; var table = worksheet.Tables.Add(range, "table1"); // ensure each column in the table has a unique name var columnNames = worksheet.Cells[3, 1, 3, columnCounter].Select(a => new { OrigColumnName = a.Text, Cell = a }).ToList(); columnNames.Reverse(); foreach (var col in columnNames) { int duplicateSuffix = 0; string uniqueName = col.OrigColumnName; // increment the suffix by 1 until there is only one column with that name while (columnNames.Where(a => a.Cell.Text == uniqueName).Count() > 1) { duplicateSuffix++; uniqueName = col.OrigColumnName + duplicateSuffix.ToString(); col.Cell.Value = uniqueName; } } table.ShowFilter = true; table.TableStyle = OfficeOpenXml.Table.TableStyles.None; // format header range using (ExcelRange r = worksheet.Cells[3, 1, 3, columnCounter]) { r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); r.Style.Font.Color.SetColor(Color.Black); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } // format and set title worksheet.Cells[1, 1].Value = title; using (ExcelRange r = worksheet.Cells[1, 1, 1, columnCounter]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 22, FontStyle.Regular)); r.Style.Font.Color.SetColor(Color.White); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 41, 55)); // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } // TODO: add image to worksheet // freeze panes worksheet.View.FreezePanes(3, 1); // autofit columns for all cells worksheet.Cells.AutoFitColumns(0); // Set all the column widths worksheet.Column(2).Width = 20; worksheet.Column(4).Width = 30; worksheet.Column(6).Width = 45; // add alternating highlights // set some footer text worksheet.HeaderFooter.OddHeader.CenteredText = title; worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); byte[] byteArray; using (MemoryStream ms = new MemoryStream()) { excel.SaveAs(ms); byteArray = ms.ToArray(); } // send the spreadsheet to the browser this.Page.EnableViewState = false; this.Page.Response.Clear(); this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Page.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename); this.Page.Response.Charset = string.Empty; this.Page.Response.BinaryWrite(byteArray); this.Page.Response.Flush(); this.Page.Response.End(); }
/// <summary> /// Returns the <see cref="Rock.Model.UserLogin"/> of the user who is currently logged in, and updates their last activity date if userIsOnline=true /// </summary> /// <param name="userIsOnline">A <see cref="System.Boolean"/> value that returns the logged in user if <c>true</c>; otherwise can return the impersonated user</param> /// <returns>The current <see cref="Rock.Model.UserLogin"/></returns> public static UserLogin GetCurrentUser(bool userIsOnline) { var rockContext = new RockContext(); string userName = UserLogin.GetCurrentUserName(); if (userName != string.Empty) { if (userName.StartsWith("rckipid=")) { Rock.Model.PersonTokenService personTokenService = new Model.PersonTokenService(rockContext); Rock.Model.PersonToken personToken = personTokenService.GetByImpersonationToken(userName.Substring(8)); if (personToken?.PersonAlias?.Person != null) { return(personToken.PersonAlias.Person.GetImpersonatedUser()); } } else { var userLoginService = new UserLoginService(rockContext); UserLogin user = userLoginService.GetByUserName(userName); if (user != null && userIsOnline) { // Save last activity date var transaction = new Rock.Transactions.UserLastActivityTransaction(); transaction.UserId = user.Id; transaction.LastActivityDate = RockDateTime.Now; if ((user.IsConfirmed ?? true) && !(user.IsLockedOut ?? false)) { if (HttpContext.Current != null && HttpContext.Current.Session != null) { HttpContext.Current.Session["RockUserId"] = user.Id; } // see if there is already a LastActivitytransaction queued for this user, and just update its LastActivityDate instead of adding another to the queue var userLastActivity = Rock.Transactions.RockQueue.TransactionQueue.ToArray().OfType <Rock.Transactions.UserLastActivityTransaction>() .Where(a => a.UserId == transaction.UserId).FirstOrDefault(); if (userLastActivity != null) { userLastActivity.LastActivityDate = transaction.LastActivityDate; } else { Rock.Transactions.RockQueue.TransactionQueue.Enqueue(transaction); } } else { transaction.IsOnLine = false; Rock.Transactions.RockQueue.TransactionQueue.Enqueue(transaction); Authorization.SignOut(); return(null); } } return(user); } } return(null); }
/// <summary> /// Creates the specified rock context. /// </summary> /// <param name="rockContext">The rock context.</param> /// <param name="person">The person.</param> /// <param name="serviceType">Type of the service.</param> /// <param name="entityTypeId">The entity type identifier.</param> /// <param name="username">The username.</param> /// <param name="password">The password.</param> /// <param name="isConfirmed">if set to <c>true</c> [is confirmed].</param> /// <param name="isRequirePasswordChange">if set to <c>true</c> [is require password change].</param> /// <returns></returns> /// <exception cref="System.ArgumentOutOfRangeException">username;Username already exists</exception> /// <exception cref="System.ArgumentException"> /// entityTypeId /// or /// Invalid EntityTypeId, entity does not exist;entityTypeId /// or /// Invalid Person, person does not exist;person /// </exception> public static UserLogin Create(RockContext rockContext, Rock.Model.Person person, AuthenticationServiceType serviceType, int entityTypeId, string username, string password, bool isConfirmed, bool isRequirePasswordChange) { if (person != null) { var userLoginService = new UserLoginService(rockContext); var entityType = EntityTypeCache.Get(entityTypeId); if (entityType != null) { UserLogin user = userLoginService.GetByUserName(username); if (user != null) { throw new ArgumentOutOfRangeException("username", "Username already exists"); } DateTime createDate = RockDateTime.Now; user = new UserLogin(); user.Guid = Guid.NewGuid(); user.EntityTypeId = entityTypeId; user.UserName = username; user.IsConfirmed = isConfirmed; user.LastPasswordChangedDateTime = createDate; user.PersonId = person.Id; user.IsPasswordChangeRequired = isRequirePasswordChange; if (serviceType == AuthenticationServiceType.Internal) { var authenticationComponent = AuthenticationContainer.GetComponent(entityType.Name); if (authenticationComponent == null || !authenticationComponent.IsActive) { throw new ArgumentException(string.Format("'{0}' service does not exist, or is not active", entityType.FriendlyName), "entityTypeId"); } user.Password = authenticationComponent.EncodePassword(user, password); } userLoginService.Add(user); rockContext.SaveChanges(); var historyCategory = CategoryCache.Get(Rock.SystemGuid.Category.HISTORY_PERSON_ACTIVITY.AsGuid(), rockContext); if (historyCategory != null) { var changes = new History.HistoryChangeList(); History.EvaluateChange(changes, "User Login", string.Empty, username); HistoryService.SaveChanges(rockContext, typeof(Person), historyCategory.Guid, person.Id, changes); } return(user); } else { throw new ArgumentException("Invalid EntityTypeId, entity does not exist", "entityTypeId"); } } else { throw new ArgumentException("Invalid Person, person does not exist", "person"); } }
/// <summary> /// Handles the ExcelExportClick event of the Actions control. /// </summary> /// <param name="sender">The source of the event.</param> /// <param name="e">The <see cref="EventArgs" /> instance containing the event data.</param> protected void Actions_ExcelExportClick(object sender, EventArgs e) { // create default settings string filename = gBreakoutGroups.ExportFilename; string workSheetName = "List"; string title = "Breakout Group Attendance"; ExcelPackage excel = new ExcelPackage(); excel.Workbook.Properties.Title = title; // add author info Rock.Model.UserLogin userLogin = Rock.Model.UserLoginService.GetCurrentUser(); if (userLogin != null) { excel.Workbook.Properties.Author = userLogin.Person.FullName; } else { excel.Workbook.Properties.Author = "Rock"; } // add the page that created this excel.Workbook.Properties.SetCustomPropertyValue("Source", this.Page.Request.Url.OriginalString); ExcelWorksheet worksheet = excel.Workbook.Worksheets.Add(workSheetName); worksheet.PrinterSettings.LeftMargin = .5m; worksheet.PrinterSettings.RightMargin = .5m; worksheet.PrinterSettings.TopMargin = .5m; worksheet.PrinterSettings.BottomMargin = .5m; //// write data to worksheet there are three supported data sources //// DataTables, DataViews and ILists int rowCounter = 4; int columnCounter = 0; List <string> columns = new List <string>() { "Breakout", "Name", "Gender", "Birthdate", "Grade", "First Time", "Last Time", "Total" }; foreach (var schedule in schedules) { columns.Add(schedule.Name); } // print headings foreach (String column in columns) { columnCounter++; worksheet.Cells[3, columnCounter].Value = column.SplitCase(); } int lastBreakoutGroupId = 0; int lastBreakoutGroupTotal = 0; List <int> scheduleTotals = new List <int>(); var schedulePreference = GetUserPreference(BlockCache.Guid.ToString() + "Schedule") .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries) .ToList(); foreach (var schedulePref in schedulePreference) { scheduleTotals.Add(0); } // print data foreach (BreakoutGroupMember bgm in GetBreakoutGroupMembers()) { if (lastBreakoutGroupId == 0) { lastBreakoutGroupId = bgm.group.Id; } if (lastBreakoutGroupId != bgm.group.Id) { SetExcelValue(worksheet.Cells[rowCounter, 1], worksheet.Cells[rowCounter - 1, 1].Text); lastBreakoutGroupId = bgm.group.Id; SetExcelValue(worksheet.Cells[rowCounter, 7], "Breakout Total"); worksheet.Cells[rowCounter, 7].Style.Font.Bold = true; SetExcelValue(worksheet.Cells[rowCounter, 8], lastBreakoutGroupTotal); worksheet.Cells[rowCounter, 8].Style.Font.Bold = true; for (int j = 0; j < scheduleTotals.Count; j++) { SetExcelValue(worksheet.Cells[rowCounter, 9 + j], scheduleTotals[j]); worksheet.Cells[rowCounter, 9 + j].Style.Font.Bold = true; scheduleTotals[j] = 0; } lastBreakoutGroupTotal = 0; rowCounter++; } SetExcelValue(worksheet.Cells[rowCounter, 1], bgm.Breakout); SetExcelValue(worksheet.Cells[rowCounter, 2], bgm.Name); SetExcelValue(worksheet.Cells[rowCounter, 3], bgm.Gender); SetExcelValue(worksheet.Cells[rowCounter, 4], (bgm.person.BirthDate ?? new DateTime()).ToString("MM/dd/yyyy")); SetExcelValue(worksheet.Cells[rowCounter, 5], bgm.person.GradeFormatted); if (firstAttendanceDate.ContainsKey(bgm.person.Id)) { SetExcelValue(worksheet.Cells[rowCounter, 6], firstAttendanceDate[bgm.person.Id].ToString("MM/dd/yyyy")); } if (lastAttendanceDate.ContainsKey(bgm.person.Id)) { SetExcelValue(worksheet.Cells[rowCounter, 7], lastAttendanceDate[bgm.person.Id].ToString("MM/dd/yyyy")); } var i = 0; var total = 0; if (schedulePreference.Any()) { foreach (var schedule in schedules) { if (!schedulePreference.Contains(schedule.Id.ToString())) { continue; } var attendance = 0; if (scheduleValueDicts[i].ContainsKey(bgm.person.Id)) { attendance = scheduleValueDicts[i][bgm.person.Id]; } //update row total total += attendance; //update attendance total scheduleTotals[i] += attendance; SetExcelValue(worksheet.Cells[rowCounter, 9 + i], attendance); i++; } } SetExcelValue(worksheet.Cells[rowCounter, 8], total); worksheet.Cells[rowCounter, 8].Style.Fill.PatternType = ExcelFillStyle.Solid; worksheet.Cells[rowCounter, 8].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); lastBreakoutGroupTotal += total; rowCounter++; } var range = worksheet.Cells[3, 1, rowCounter, columnCounter]; var table = worksheet.Tables.Add(range, "table1"); // ensure each column in the table has a unique name var columnNames = worksheet.Cells[3, 1, 3, columnCounter].Select(a => new { OrigColumnName = a.Text, Cell = a }).ToList(); columnNames.Reverse(); foreach (var col in columnNames) { int duplicateSuffix = 0; string uniqueName = col.OrigColumnName; // increment the suffix by 1 until there is only one column with that name while (columnNames.Where(a => a.Cell.Text == uniqueName).Count() > 1) { duplicateSuffix++; uniqueName = col.OrigColumnName + duplicateSuffix.ToString(); col.Cell.Value = uniqueName; } } table.ShowFilter = true; table.TableStyle = OfficeOpenXml.Table.TableStyles.None; // format header range using (ExcelRange r = worksheet.Cells[3, 1, 3, columnCounter]) { r.Style.Font.Bold = true; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(223, 223, 223)); r.Style.Font.Color.SetColor(Color.Black); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; } // format and set title worksheet.Cells[1, 1].Value = title; using (ExcelRange r = worksheet.Cells[1, 1, 1, columnCounter]) { r.Merge = true; r.Style.Font.SetFromFont(new Font("Calibri", 22, FontStyle.Regular)); r.Style.Font.Color.SetColor(Color.White); r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(34, 41, 55)); // set border r.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; r.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; } // TODO: add image to worksheet // freeze panes worksheet.View.FreezePanes(3, 1); // autofit columns for all cells worksheet.Cells.AutoFitColumns(0); // Set all the column widths worksheet.Column(2).Width = 20; worksheet.Column(4).Width = 30; worksheet.Column(6).Width = 45; // add alternating highlights // set some footer text worksheet.HeaderFooter.OddHeader.CenteredText = title; worksheet.HeaderFooter.OddFooter.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); byte[] byteArray; using (MemoryStream ms = new MemoryStream()) { excel.SaveAs(ms); byteArray = ms.ToArray(); } // send the spreadsheet to the browser this.Page.EnableViewState = false; this.Page.Response.Clear(); this.Page.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; this.Page.Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename); this.Page.Response.Charset = string.Empty; this.Page.Response.BinaryWrite(byteArray); this.Page.Response.Flush(); this.Page.Response.End(); }