private void BuildCriteriaSheet(string worksheetName, XLWorkbook workbook, SelectedCriteria 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 = CompetencyTrackingReport; 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 SelectedCriteria GetCriteriaControlValues() { var criteriaController = Master.CurrentCriteria(); SelectedCriteria selectedCriteria = new SelectedCriteria(); //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.Value).FirstOrDefault(); //Teacher selectedCriteria.SelectedTeacher = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("TeacherName").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(); //StandardLevel selectedCriteria.StandardLevel = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("StandardLevel").Select(x => x.Value).FirstOrDefault(); //Demographics selectedCriteria.SelectedDemographics = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.ACDropDownList.ValueObject>("Demographic").Select(x => x.Value).FirstOrDefault(); //Group; selectedCriteria.SelectedGroup = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("Group").Select(x => x.Value).FirstOrDefault(); //Worksheet List string selectedListId = "0"; if (criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("ListSelection").Select(x => x.Value).FirstOrDefault() != null) { selectedListId = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("ListSelection").Select(x => x.Value).FirstOrDefault(); } else { selectedListId = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("WorksheetSelection").Select(x => x.Value).FirstOrDefault(); } selectedCriteria.SelectedWorksheet = selectedListId; //StandardList selectedCriteria.StandardList = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("StandardList").Select(x => x.Value).FirstOrDefault(); selectedCriteria.StandardSet = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.StandardSet).FirstOrDefault(); selectedCriteria.StandardId = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.StandardId).FirstOrDefault(); //if (criteriaController.CriteriaNodes[2].Values[0].Value != null && criteriaController.CriteriaNodes[2].Values[0].Value.ContainsKey("Grades")) //{ selectedCriteria.Grade = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.Grades).FirstOrDefault();//criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("Grades").Select(x => x.Grades).FirstOrDefault(); //} //if (criteriaController.CriteriaNodes[2].Values[0].Value != null && criteriaController.CriteriaNodes[2].Values[0].Value.ContainsKey("Subjects")) //{ selectedCriteria.Subject = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.Subjects).FirstOrDefault(); //criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("Subjects").Select(x => x.Subjects).FirstOrDefault(); //} //if (criteriaController.CriteriaNodes[2].Values[0].Value != null && criteriaController.CriteriaNodes[2].Values[0].Value.ContainsKey("Courses")) //{ selectedCriteria.Course = criteriaController.ParseCriteria<E3Criteria.AutoCompleteCriteriaControls.GetGradeSubjectCourseStandard.ValueObject>("StandardSet").Select(x => x.Courses).FirstOrDefault(); //} //TODO UNCOMMENT AND VERIFY BELOW CODE ONCE STANDARD LIST IS WORKING //selectedCriteria.StandardList = criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("ListSelection").Select(x => x.Value).FirstOrDefault(); selectedlist = Convert.ToInt32(criteriaController.ParseCriteria<E3Criteria.DropDownList.ValueObject>("StandardList").Select(x => x.Value).FirstOrDefault()); if (selectedlist == 9)//for competency list option only { Base.DataAccess.dtGeneric_Int _standardids = new Base.DataAccess.dtGeneric_Int(); _standardids.Add(Convert.ToInt32(selectedCriteria.SelectedWorksheet)); DataTable dsstandard = CompetencyWorkSheet.GetCurrStabdardsById_Kentico(_standardids, true); var stdListFromCompetencyList = ""; foreach (DataRow dr in dsstandard.Rows) { stdListFromCompetencyList += "_" + dr[0].ToString(); } if (!string.IsNullOrEmpty(stdListFromCompetencyList)) { selectedCriteria.StandardId = stdListFromCompetencyList.Substring(1); } selectedCriteria.SelectedWorksheet = null; } /* Created Date Range */ foreach (var val in criteriaController.ParseCriteria<DateRange.ValueObject>("DateRange")) { if (val.Type == "Start") { selectedCriteria.StartDate = val.Date; } else { selectedCriteria.EndDate = val.Date; } } return selectedCriteria; }
private void FormatColumnsForExcel(SelectedCriteria selectedCriteria) { string selectedViewBy = selectedCriteria.SelectedViewBy; string standardLevel = selectedCriteria.StandardLevel; DataTable localDataTable = GridDataTable.Copy(); localDataTable.Columns[selectedViewBy + "Name"].ColumnName = selectedViewBy + " (" + _distinctRowsCount + ")"; if (!string.IsNullOrEmpty(standardLevel)) { localDataTable.Columns["StandardName"].ColumnName = standardLevel + " (" + _distinctObjectivesCount + ")"; } else { localDataTable.Columns["StandardName"].ColumnName = "Competencies (" + _distinctObjectivesCount + ")"; } //localDataTable.Columns["DateA"].ColumnName = "No Attempt"; //localDataTable.Columns["DateB"].ColumnName = "Beginning\r\n/Novice"; //localDataTable.Columns["DateC"].ColumnName = "Developing\r\n/Emerging"; //localDataTable.Columns["DateD"].ColumnName = "Accomplished\r\n/Proficient"; //localDataTable.Columns["DateE"].ColumnName = "Exemplary\r\n/Master"; ReportData theReportData = new ReportData { ReportDataTable = localDataTable, ReportSelectedCriteria = selectedCriteria }; SessionObject.CompetencyTracking_ReportData = theReportData; }