private static int BasicDataToExcel(Excel.Worksheet ws, EventModel model, DataGridView invitedDGV, DataGridView acceptedDGV)
{
ws.Select();
ws.Name = "Invites";
ws.Cells[1, 1] = "Event Name :";
ws.Cells[1, 2] = model.EventName;
ws.Cells[2, 1] = model.EventDescription;
ws.get_Range("A2", "M2").Merge();
ws.get_Range("A2").WrapText = true;
ws.get_Range("A2").EntireRow.RowHeight = Math.Ceiling((double)model.EventDescription.Length / 150) * 22;
ws.get_Range("A2:M2").VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
ws.get_Range("A2:M2").HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
ws.Cells[3, 1] = "Event Date :";
ws.Cells[3, 2] = model.EventDate;
ws.Cells[4, 1] = "Start Time :";
ws.Cells[4, 2] = model.StartTime.ToString();
ws.Cells[5, 1] = "End Time :";
ws.Cells[5, 2] = model.EndTime.ToString();
ws.Cells[6, 1] = "Venue :";
ws.Cells[6, 2] = model.EventLocation.Venue.VenueName;
ws.Cells[7, 1] = "Address :";
ws.Cells[8, 2] = model.EventLocation.VenueAddress.AddressLine1;
int i = 1;
ws.Cells[8 + i, 2] = AddressLogic.ReturnAddressLineIfNotNull(model.EventLocation.VenueAddress.AddressLine2, ref i);
ws.Cells[8 + i, 2] = AddressLogic.ReturnAddressLineIfNotNull(model.EventLocation.VenueAddress.AddressLine3, ref i);
ws.Cells[8 + i, 2] = AddressLogic.ReturnAddressLineIfNotNull(model.EventLocation.VenueAddress.AddressLine4, ref i);
ws.Cells[8 + i, 2] = AddressLogic.ReturnAddressLineIfNotNull(model.EventLocation.VenueAddress.Area, ref i);
ws.Cells[8 + i, 2] = model.EventLocation.VenueAddress.PostCode;
ws.Cells[8 + i, 2].NumberFormat = "@";
int colHeaderRow = 8 + i + 3;
//Add table headers going cell by cell.
ws.Cells[colHeaderRow, 1] = "Title";
ws.Cells[colHeaderRow, 1].Font.Bold = true;
ws.Cells[colHeaderRow, 2] = "First Name";
ws.Cells[colHeaderRow, 2].Font.Bold = true;
ws.Cells[colHeaderRow, 3] = "Last Name";
ws.Cells[colHeaderRow, 3].Font.Bold = true;
ws.Cells[colHeaderRow, 4] = "Business Name";
ws.Cells[colHeaderRow, 4].Font.Bold = true;
ws.Cells[colHeaderRow, 5] = "Email Address";
ws.Cells[colHeaderRow, 5].Font.Bold = true;
ws.Cells[colHeaderRow, 6] = "Address";
ws.Cells[colHeaderRow, 6].Font.Bold = true;
ws.Cells[colHeaderRow, 7] = "Cell Phone";
ws.Cells[colHeaderRow, 7].Font.Bold = true;
ws.Cells[colHeaderRow, 8] = "Work Phone";
ws.Cells[colHeaderRow, 8].Font.Bold = true;
ws.Cells[colHeaderRow, 9] = "No. Invites";
ws.Cells[colHeaderRow, 9].Font.Bold = true;
ws.Cells[colHeaderRow, 10] = "Invite Confirmed?";
ws.Cells[colHeaderRow, 10].Font.Bold = true;
//Now load Custom QuestionS
bool CustomQs = (GlobalConfig.Connection.GetCustomQuestionCount(model.Id) > 0);
if (CustomQs)
{
int Qcol = 1;
List <string> CustomQuestions = GlobalConfig.Connection.GetCustomQuestions(model.Id);
foreach (string customQuestion in CustomQuestions)
{
ws.Cells[colHeaderRow, 10 + Qcol] = customQuestion;
ws.Cells[colHeaderRow, 10 + Qcol].Font.Bold = true;
Qcol++;
}
}
//These should be text to make sure any leading 0s are kept
ws.get_Range("G:H").NumberFormat = "@";
int TotalInvites = 0;
int j = colHeaderRow;
foreach (DataGridViewRow item in acceptedDGV.Rows)
{
InviteModel invite = (InviteModel)item.Tag;
j++;
ws.Cells[j, 1] = invite.Client.Title;
ws.Cells[j, 2] = invite.Client.FirstName;
ws.Cells[j, 3] = invite.Client.LastName;
ws.Cells[j, 4] = invite.Client.BusinessName;
ws.Cells[j, 5] = invite.Client.EmailAddress;
if (invite.Client.HomeAddress != null)
{
ws.Cells[j, 6] = invite.Client.HomeAddress.FullMultiLineAddress;
}
ws.Cells[j, 7] = invite.Client.CellPhone;
ws.Cells[j, 8] = invite.Client.WorkPhone;
ws.Cells[j, 9] = invite.PlacesReserved;
TotalInvites += invite.PlacesReserved;
ws.Cells[j, 10] = true;
if (CustomQs)
{
DataTable dt = GlobalConfig.Connection.GetClientsInvitationAnswers(invite.ClientId, invite.EventId);
foreach (DataRow row in dt.Rows)
{
if (row[0].ToString().Length > 0)
{
ws.Cells[j, 10 + Convert.ToInt32(row["ColumnNumber"].ToString()) - 2] = row["Response"].ToString();
}
}
}
}
foreach (DataGridViewRow item in invitedDGV.Rows)
{
InviteModel invite = (InviteModel)item.Tag;
j++;
ws.Cells[j, 1] = invite.Client.Title;
ws.Cells[j, 2] = invite.Client.FirstName;
ws.Cells[j, 3] = invite.Client.LastName;
ws.Cells[j, 4] = invite.Client.BusinessName;
ws.Cells[j, 5] = invite.Client.EmailAddress;
if (invite.Client.HomeAddress != null)
{
ws.Cells[j, 6] = invite.Client.HomeAddress.FullMultiLineAddress;
}
ws.Cells[j, 7] = invite.Client.CellPhone;
ws.Cells[j, 8] = invite.Client.WorkPhone;
ws.Cells[j, 9] = invite.PlacesReserved;
TotalInvites += invite.PlacesReserved;
ws.Cells[j, 10] = false;
}
ws.Cells[j + 1, 8] = "Total Invites";
ws.Cells[j + 1, 9] = "=sum(I" + (8 + i + 4) + ":I" + j + ")";
ws.Cells[j + 1, 9].Font.Bold = true;
ws.get_Range("I:I").Columns.NumberFormat = "0";
ws.Cells[j + 1, 10] = "(Max = " + model.MaxNumberOfGuests + ")";
ws.Cells[j + 2, 8] = "Confirmed Places";
ws.Cells[j + 2, 9] = "=sumif(J" + (8 + i + 4) + ": J" + j + ", TRUE, I" + (8 + i + 4) + ": I" + j + ")";
ws.Cells[j + 2, 9].Font.Bold = true;
int inviteSumRow = j + 2;
ws.get_Range("A:Z").Columns.AutoFit();
ws.Cells[7, 2] = model.EventLocation.VenueAddress.FullMultiLineAddress;
ws.Cells[7, 2].WrapText = false;
return(inviteSumRow);
}
