private void FormatColumnsForExcel(SelectedCriteriaCrd selectedCriteria)
{
string selectedViewBy = selectedCriteria.SelectedViewBy;
DataTable localDataTable = GridDataTable.Copy();
switch (selectedViewBy)
{
case "District":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns.Remove("LevelName");
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "School":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "School";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Teacher":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Teacher";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Class":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Class";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Group":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Group";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Student":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Student";
localDataTable.Columns.Remove("CredentialID");
// Need to modify for yes/no value.
//localDataTable.Columns["EarnedDate"].DataType = typeof(String);
DataColumn EarnedCol = localDataTable.Columns.Add("Earned", typeof(String));
foreach (DataRow dr in localDataTable.Rows)
{
string date = dr["EarnedDate"].ToString().Trim();
dr["Earned"] = String.IsNullOrEmpty(date) ? "No" : "Yes";
}
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns.Remove("StudentCount");
break;
case "Demographics":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Demographics";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Alignment":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Alignment";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
case "Year":
localDataTable.Columns.Remove("LevelID");
localDataTable.Columns["LevelName"].ColumnName = "Year";
localDataTable.Columns.Remove("CredentialID");
localDataTable.Columns.Remove("EarnedDate");
localDataTable.Columns["CredentialName"].ColumnName = "Credential Name";
localDataTable.Columns["StudentCount"].ColumnName = "Student Count";
break;
}
ReportDataCredential theReportData = new ReportDataCredential
{
ReportDataTable = localDataTable,
ReportSelectedCriteria = selectedCriteria
};
SessionObject.CredentialTracking_ReportData = theReportData;
}
private void BuildCriteriaSheet(string worksheetName, XLWorkbook workbook, SelectedCriteriaCrd selectedCriteria)
{
//You can find info on manipulating the Excel workbook here - https://closedxml.codeplex.com/documentation
var ws = workbook.Worksheets.Add(worksheetName);
ws.Range("B2:B4").Style.Font.Bold = true;
ws.Cell(2, 2).Value = CredentialTrackingReport;
ws.Cell("B2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //Center the cell
ws.Range("B2:C2").Row(1).Merge(); //Merge across cells
ws.Range("B2:C2").Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
ws.Cell(3, 2).Value = "Standard List";
ws.Cell(4, 2).Value = "Criteria";
ws.Cell("B4").Style.Alignment.Vertical = XLAlignmentVerticalValues.Center; //Center the cell
ws.Range("B4:B7").Column(1).Merge(); ; //Merge across columns
if (string.IsNullOrEmpty(selectedCriteria.SelectedWorksheet))
{
ws.Cell(3, 3).Value = selectedCriteria.StandardList;
}
else
{
ws.Cell(3, 3).Value = selectedCriteria.StandardList + ": \"" + selectedCriteria.SelectedWorksheet + "\"";
}
//View By, Demographics, Standard Level, and Data Range
ws.Cell(4, 3).Value = "View By = " + selectedCriteria.SelectedViewBy;
ws.Cell(5, 3).Value = "Demographics = " + selectedCriteria.SelectedDemographics.ToArray();
ws.Cell(6, 3).Value = "Standard Level = " + selectedCriteria.StandardLevel;
ws.Cell(7, 3).Value = "Date Range = (start:" + selectedCriteria.StartDate + " - end: " + selectedCriteria.EndDate + ")";
ws.Range("D2:D7").Style.Font.Bold = true;
var theDistrict = Base.Classes.District.GetDistrictByID(SessionObject.LoggedInUser.District);
ws.Cell(2, 4).Value = theDistrict.DistrictName + " (" + theDistrict.ClientID + ")";
ws.Cell("D2").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; //Center the cell
ws.Range("D2:G2").Row(1).Merge(); //Merge across cells
ws.Range("D2:G2").Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
ws.Cell(3, 4).Value = "School";
ws.Cell(4, 4).Value = "Teacher";
ws.Cell(5, 4).Value = "Class";
ws.Cell(6, 4).Value = "Group";
ws.Cell(7, 4).Value = "Student";
ws.Cell(3, 5).Value = selectedCriteria.SelectedSchoolName;
ws.Cell(4, 5).Value = selectedCriteria.SelectedTeacher;
ws.Cell(5, 5).Value = selectedCriteria.SelectedClass;
ws.Cell(6, 5).Value = selectedCriteria.SelectedGroup;
ws.Cell(7, 5).Value = selectedCriteria.SelectedStudent;
ws.Range("F3:F6").Style.Font.Bold = true;
ws.Cell(3, 6).Value = "# Schools";
ws.Cell(4, 6).Value = "# Teachers";
ws.Cell(5, 6).Value = "# Classes";
ws.Cell(6, 6).Value = "# Groups";
ws.Cell(7, 6).Value = "# Students";
ws.Cell(3, 7).Value = "?";
ws.Cell(4, 7).Value = "?";
ws.Cell(5, 7).Value = "?";
ws.Cell(6, 7).Value = "?";
ws.Cell(7, 7).Value = "?";
ws.Columns().AdjustToContents();
}
private SelectedCriteriaCrd GetCriteriaControlValues()
{
var criteriaController = Master.CurrentCriteria();
SelectedCriteriaCrd selectedCriteria = new SelectedCriteriaCrd();
//ViewBy
selectedCriteria.SelectedViewBy = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("ViewBy").Select(x => x.Text).FirstOrDefault();
//School
selectedCriteria.SelectedSchoolId = DataIntegrity.ConvertToInt(criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("School").Select(x => x.Value).FirstOrDefault());
selectedCriteria.SelectedSchoolName = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("School").Select(x => x.Text).FirstOrDefault();
//Teacher
selectedCriteria.SelectedTeacher = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Teacher").Select(x => x.Value).FirstOrDefault();
//Class
selectedCriteria.SelectedClass = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Class").Select(x => x.Value).FirstOrDefault();
//Student
selectedCriteria.SelectedStudent = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Student").Select(x => x.Value).FirstOrDefault();
//Demographics
selectedCriteria.SelectedDemographics = criteriaController.ParseCriteria<Demographics.ValueObject>("Demographics");
//Group;
selectedCriteria.SelectedGroup = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Group").Select(x => x.Value).FirstOrDefault();
//Alignment
selectedCriteria.SelectedAlignment = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Alignment").Select(x => x.Value).FirstOrDefault();
//Year
selectedCriteria.SelectedYear = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Year").Select(x => x.Value).FirstOrDefault();
return selectedCriteria;
}