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