// Add AttendeeHour : Get
// wid = workshop Id
public ActionResult Add(int wid = 1)
{
var model = new AttendeeHourEditModel();
model.WorkshopId = wid;
model.Attendees = _attendeeService.GetAttendees(1, 100);
// Get workshop we are adding attendee hours to
model.Workshop = _workshopService.GetDetails(wid);
return(View(model));
}
// Default action : Get
// Shows workshop details and list of attendees with hours
// with form to add more attendees and hours
// id is workshop id
public ActionResult Index(int id = 0, int p = 1, int ps = 50)
{
StorePaging(p, ps);
// Id is for workshop and must be > 0
if (id <= 0)
{
return(RedirectToAction("Index", "Workshops", new { p = PageNumber, ps = PageSize }));
}
// Get workshop with project, employee and provider code
var model = _workshopService.GetDetails(id);
// Get attendee hours for workshop
model.WorkshopAttendeeHours = _workshopService.GetWorkshopAttendeeHours(id);
return(View(model));
}
/// <summary>
/// Creates an Excel spreadsheet with the name of the workshop
/// and all attendees with PD hours listed.
/// </summary>
/// <param name="workshopId"></param>
/// <returns></returns>
public byte[] WorkshopAttendeeHours(int workshopId)
{
// Get workshop data and attendee hours from db
var workshop = _workshopService.GetDetails(workshopId);
var workshopAttendeeHours = _workshopService.GetWorkshopAttendeeHours(workshopId);
using (var ms = new MemoryStream())
{
using (var package = new ExcelPackage(ms))
{
var worksheet = package.Workbook.Worksheets.Add("Sheet 1");
// Print workshop name, date and session id at top
// Merge cells A-D on first row so workshop name is centered across data
var workshopNameRange = "A1:E1";
var workshopDateRange = "A2:E2";
var workshopIdRange = "A3:E3";
worksheet.Cells[workshopNameRange].Merge = true;
worksheet.Cells[workshopNameRange].Style.HorizontalAlignment
= OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[workshopNameRange].Style.Font.Bold = true;
worksheet.Cells[workshopNameRange].Style.Font.Size = 18;
worksheet.Cells[workshopNameRange].Value = workshop.WorkshopName;
// Workshop Date
worksheet.Cells[workshopDateRange].Merge = true;
worksheet.Cells[workshopDateRange].Style.HorizontalAlignment
= OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[workshopDateRange].Style.Font.Bold = true;
worksheet.Cells[workshopDateRange].Style.Font.Size = 16;
worksheet.Cells[workshopDateRange].Value = workshop.TrainingDate.ToShortDateString();
// Workshop Session Id
worksheet.Cells[workshopIdRange].Merge = true;
worksheet.Cells[workshopIdRange].Style.HorizontalAlignment
= OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
worksheet.Cells[workshopIdRange].Style.Font.Italic = true;
worksheet.Cells[workshopIdRange].Value
= $"{workshop.SessionIdentifier}-{workshop.ProviderCode.Code}";
// Column Headings
worksheet.Cells["A4"].Value = "Name";
worksheet.Cells["A4"].Style.Font.Bold = true;
worksheet.Cells["B4"].Value = "ID";
worksheet.Cells["B4"].Style.Font.Bold = true;
worksheet.Cells["C4"].Value = "Hours";
worksheet.Cells["C4"].Style.Font.Bold = true;
worksheet.Cells["D4"].Value = "Title";
worksheet.Cells["D4"].Style.Font.Bold = true;
worksheet.Cells["E4"].Value = "Agency";
worksheet.Cells["E4"].Style.Font.Bold = true;
// Print attendee name, ID, PD hours, title and agency
// in columns A, B, C, D and E
var row = 5;
var col = 1;
foreach (var attendee in workshopAttendeeHours)
{
// Reset column back to 1 (A)
col = 1;
// Attendee name
worksheet.Cells[row, col].Value = attendee.FullName;
// ID
col++;
worksheet.Cells[row, col].Value = attendee.CertId;
// PD Hours
col++;
worksheet.Cells[row, col].Value = attendee.PDHours;
// Job title
col++;
worksheet.Cells[row, col].Value = attendee.JobTitle;
// Agency
col++;
worksheet.Cells[row, col].Value = attendee.AgencyName;
// Update counter which is row number
row++;
}
// Style PD Hours column for two decimal places
worksheet.Column(3).Style.Numberformat.Format = "0.00";
worksheet.Column(3).Style.HorizontalAlignment
= OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
// Set column widths
// name
worksheet.Column(1).Width = 20;
// id
worksheet.Column(2).Width = 10;
// pd hours
worksheet.Column(3).Width = 7;
// job title
worksheet.Column(4).Width = 20;
// agency
worksheet.Column(5).Width = 30;
return(package.GetAsByteArray());
}
}
}
