Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java =================================================================== diff -u -rcbf95a868252401757c61327b3d9a383119ff9b5 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java (.../ExcelRow.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java (.../ExcelRow.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -30,6 +30,10 @@ } public ExcelCell addPercentageCell(Object cellValue) { + return addPercentageCell(cellValue, false, 0); + } + + public ExcelCell addPercentageCell(Object cellValue, Boolean isBold, int borderStyle) { ExcelCell cell = new ExcelCell(cellValue); cell.setDataFormat(ExcelCell.CELL_FORMAT_PERCENTAGE); cells.add(cell); @@ -52,9 +56,10 @@ cells.add(cell); } - public void addCell(Object cellValue, Boolean isBold, int borderStyle) { + public ExcelCell addCell(Object cellValue, Boolean isBold, int borderStyle) { ExcelCell cell = new ExcelCell(cellValue, isBold, borderStyle); cells.add(cell); + return cell; } /** @@ -71,6 +76,10 @@ * @param numberEmptyCells */ public void addEmptyCells(int numberEmptyCells) { + if (numberEmptyCells < 1) { + return; + } + for (int i = 0; i < numberEmptyCells; i++) { ExcelCell cell = new ExcelCell(""); cells.add(cell); Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/GradebookService.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/GradebookService.java (.../GradebookService.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/GradebookService.java (.../GradebookService.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -99,6 +99,8 @@ import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.WebUtil; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelRow; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import org.lamsfoundation.lams.web.session.SessionManager; import org.lamsfoundation.lams.web.util.AttributeNames; import org.springframework.web.context.WebApplicationContext; @@ -118,8 +120,6 @@ public class GradebookService implements IGradebookFullService { private static Logger logger = Logger.getLogger(GradebookService.class); - private static final ExcelCell[] EMPTY_ROW = new ExcelCell[4]; - private static final String TOOL_SIGNATURE_ASSESSMENT = "laasse10"; public static final String TOOL_SIGNATURE_SCRATCHIE = "lascrt11"; public static final String TOOL_SIGNATURE_MCQ = "lamc11"; @@ -141,7 +141,7 @@ @Override public List getGBActivityRowsForLearner(Long lessonId, Integer userId, TimeZone userTimezone) { - GradebookService.logger.debug("Getting gradebook user data for lesson: " + lessonId + ". For user: " + userId); + logger.debug("Getting gradebook user data for lesson: " + lessonId + ". For user: " + userId); Lesson lesson = lessonService.getLesson(lessonId); User learner = (User) userService.findById(User.class, userId); @@ -235,7 +235,7 @@ @Override public List getGBActivityArchiveRowsForLearner(Long activityId, Integer userId, TimeZone userTimezone) { - GradebookService.logger + logger .debug("Getting archive gradebook user data for activity: " + activityId + ". For user: " + userId); Activity activity = getActivityById(activityId); @@ -279,7 +279,7 @@ @Override public List getGBLessonComplete(Long lessonId, Integer userId) { - GradebookService.logger + logger .debug("Getting lesson complete gradebook user data for lesson: " + lessonId + ". For user: " + userId); Lesson lesson = lessonService.getLesson(lessonId); @@ -319,7 +319,7 @@ @Override public List getGBActivityRowsForLesson(Long lessonId, TimeZone userTimezone, boolean escapeTitles) { - GradebookService.logger.debug("Getting gradebook data for lesson: " + lessonId); + logger.debug("Getting gradebook data for lesson: " + lessonId); Lesson lesson = lessonService.getLesson(lessonId); List gradebookActivityDTOs = new ArrayList<>(); @@ -729,7 +729,7 @@ } } catch (ToolException e) { - GradebookService.logger.debug( + logger.debug( "Runtime exception when attempted to get outputs for activity: " + toolActivity.getActivityId(), e); } } @@ -821,7 +821,7 @@ } } catch (ToolException e) { - GradebookService.logger.debug( + logger.debug( "Runtime exception when attempted to get outputs for activity: " + toolActivity.getActivityId(), e); } } @@ -1040,7 +1040,7 @@ } } else { - GradebookService.logger.error("Request for gradebook grid with a null organisation"); + logger.error("Request for gradebook grid with a null organisation"); } return lessonRows; @@ -1129,94 +1129,91 @@ @Override @SuppressWarnings("unchecked") - public LinkedHashMap exportLessonGradebook(Lesson lesson) { - + public List exportLessonGradebook(Lesson lesson) { boolean isWeighted = toolService.isWeightedMarks(lesson.getLearningDesign()); - LinkedHashMap dataToExport = new LinkedHashMap<>(); + List sheets = new LinkedList(); // -------------------- process summary excel page -------------------------------- + + ExcelSheet summarySheet = new ExcelSheet(getMessage("gradebook.export.lesson.summary")); + sheets.add(summarySheet); - // The entire data list - List rowList = new LinkedList<>(); - // Adding the lesson average data to the summary Double lessonAverageMarkValue = getAverageMarkForLesson(lesson.getLessonId()); - ExcelCell[] lessonAverageMark = new ExcelCell[2]; - lessonAverageMark[0] = new ExcelCell(getMessage("gradebook.export.average.lesson.mark"), true); - ExcelCell markCell = isWeighted ? GradebookUtil.createPercentageCell(lessonAverageMarkValue, true) - : new ExcelCell(lessonAverageMarkValue, false); - lessonAverageMark[1] = markCell; + ExcelRow lessonAverageMark = summarySheet.initRow(); + lessonAverageMark.addCell(getMessage("gradebook.export.average.lesson.mark"), true); + if (isWeighted) { + lessonAverageMark.addPercentageCell(lessonAverageMarkValue / 100.0); + } else { + lessonAverageMark.addCell(lessonAverageMarkValue); + } - ExcelCell[] lessonMedianTimeTaken = new ExcelCell[2]; - lessonMedianTimeTaken[0] = new ExcelCell(getMessage("gradebook.export.average.lesson.time.taken"), true); - lessonMedianTimeTaken[1] = new ExcelCell(gradebookDAO.getMedianTimeTakenLesson(lesson.getLessonId()) / 1000, + ExcelRow lessonMedianTimeTaken = summarySheet.initRow(); + lessonMedianTimeTaken.addCell(getMessage("gradebook.export.average.lesson.time.taken"), true); + lessonMedianTimeTaken.addCell(gradebookDAO.getMedianTimeTakenLesson(lesson.getLessonId()) / 1000, false); - rowList.add(lessonMedianTimeTaken); - rowList.add(GradebookService.EMPTY_ROW); + summarySheet.addEmptyRow(); // Adding the activity average data to the summary List activityRows = getGBActivityRowsForLesson(lesson.getLessonId(), null, false); - ExcelCell[] activityAverageTitle = new ExcelCell[1]; - activityAverageTitle[0] = new ExcelCell(getMessage("gradebook.export.activities"), true); - rowList.add(activityAverageTitle); + ExcelRow activityAverageTitle = summarySheet.initRow(); + activityAverageTitle.addCell(getMessage("gradebook.export.activities"), true); // Setting up the activity summary table - ExcelCell[] activityAverageRow = new ExcelCell[6]; - activityAverageRow[0] = new ExcelCell(getMessage("gradebook.export.activity"), true); - activityAverageRow[1] = new ExcelCell(getMessage("gradebook.columntitle.competences"), true); - activityAverageRow[2] = new ExcelCell(getMessage("gradebook.export.average.time.taken.seconds"), true); - activityAverageRow[3] = new ExcelCell(getMessage("gradebook.columntitle.averageMark"), true); - activityAverageRow[4] = new ExcelCell(getMessage("gradebook.export.min.time.taken.seconds"), true); - activityAverageRow[5] = new ExcelCell(getMessage("gradebook.export.max.time.taken.seconds"), true); - rowList.add(activityAverageRow); + ExcelRow activityAverageRow = summarySheet.initRow(); + activityAverageRow.addCell(getMessage("gradebook.export.activity"), true); + activityAverageRow.addCell(getMessage("gradebook.columntitle.competences"), true); + activityAverageRow.addCell(getMessage("gradebook.export.average.time.taken.seconds"), true); + activityAverageRow.addCell(getMessage("gradebook.columntitle.averageMark"), true); + activityAverageRow.addCell(getMessage("gradebook.export.min.time.taken.seconds"), true); + activityAverageRow.addCell(getMessage("gradebook.export.max.time.taken.seconds"), true); Iterator it = activityRows.iterator(); while (it.hasNext()) { GBActivityGridRowDTO activityRow = (GBActivityGridRowDTO) it.next(); // Add the activity average data - ExcelCell[] activityDataRow = new ExcelCell[6]; - activityDataRow[0] = new ExcelCell(activityRow.getRowName(), false); // this is the problem entry - activityDataRow[1] = new ExcelCell(activityRow.getCompetences(), false); - activityDataRow[2] = new ExcelCell(activityRow.getMedianTimeTakenSeconds(), false); - activityDataRow[3] = new ExcelCell(activityRow.getAverageMark(), false); - activityDataRow[4] = new ExcelCell(activityRow.getMinTimeTakenSeconds(), false); - activityDataRow[5] = new ExcelCell(activityRow.getMaxTimeTakenSeconds(), false); - rowList.add(activityDataRow); + ExcelRow activityDataRow = summarySheet.initRow(); + activityDataRow.addCell(activityRow.getRowName()); // this is the problem entry + activityDataRow.addCell(activityRow.getCompetences()); + activityDataRow.addCell(activityRow.getMedianTimeTakenSeconds()); + activityDataRow.addCell(activityRow.getAverageMark()); + activityDataRow.addCell(activityRow.getMinTimeTakenSeconds()); + activityDataRow.addCell(activityRow.getMaxTimeTakenSeconds()); } - rowList.add(GradebookService.EMPTY_ROW); + summarySheet.addEmptyRow(); // Adding the user lesson marks to the summary - ExcelCell[] userMarksTitle = new ExcelCell[1]; - userMarksTitle[0] = new ExcelCell(getMessage("gradebook.export.total.marks.for.lesson"), true); - rowList.add(userMarksTitle); + ExcelRow userMarksTitle = summarySheet.initRow(); + userMarksTitle.addCell(getMessage("gradebook.export.total.marks.for.lesson"), true); // Fetching the user data ArrayList userRows = getGBUserRowsForLesson(lesson, null); // Setting up the user marks table - ExcelCell[] userTitleRow = new ExcelCell[6]; - userTitleRow[0] = new ExcelCell(getMessage("gradebook.export.last.name"), true); - userTitleRow[1] = new ExcelCell(getMessage("gradebook.export.first.name"), true); - userTitleRow[2] = new ExcelCell(getMessage("gradebook.export.login"), true); - userTitleRow[3] = new ExcelCell(getMessage("gradebook.exportcourse.progress"), true); - userTitleRow[4] = new ExcelCell(getMessage("gradebook.export.time.taken.seconds"), true); - userTitleRow[5] = new ExcelCell(getMessage("gradebook.export.total.mark"), true); - rowList.add(userTitleRow); + ExcelRow userTitleRow = summarySheet.initRow(); + userTitleRow.addCell(getMessage("gradebook.export.last.name"), true); + userTitleRow.addCell(getMessage("gradebook.export.first.name"), true); + userTitleRow.addCell(getMessage("gradebook.export.login"), true); + userTitleRow.addCell(getMessage("gradebook.exportcourse.progress"), true); + userTitleRow.addCell(getMessage("gradebook.export.time.taken.seconds"), true); + userTitleRow.addCell(getMessage("gradebook.export.total.mark"), true); for (GBUserGridRowDTO userRow : userRows) { // Adding the user data for the lesson - ExcelCell[] userDataRow = new ExcelCell[6]; - userDataRow[0] = new ExcelCell(userRow.getLastName(), false); - userDataRow[1] = new ExcelCell(userRow.getFirstName(), false); - userDataRow[2] = new ExcelCell(userRow.getLogin(), false); - userDataRow[3] = new ExcelCell(getProgressMessage(userRow), false); - userDataRow[4] = new ExcelCell(userRow.getTimeTakenSeconds(), false); - userDataRow[5] = isWeighted ? GradebookUtil.createPercentageCell(userRow.getMark(), true) - : new ExcelCell(userRow.getMark(), false); - rowList.add(userDataRow); + ExcelRow userDataRow = summarySheet.initRow(); + userDataRow.addCell(userRow.getLastName()); + userDataRow.addCell(userRow.getFirstName()); + userDataRow.addCell(userRow.getLogin()); + userDataRow.addCell(getProgressMessage(userRow)); + userDataRow.addCell(userRow.getTimeTakenSeconds()); + if (isWeighted) { + userDataRow.addPercentageCell(userRow.getMark() / 100.0); + } else { + userDataRow.addCell(userRow.getMark()); + } } - rowList.add(GradebookService.EMPTY_ROW); + summarySheet.addEmptyRow(); // -- Summary for activity marks (simplified) --- @@ -1233,33 +1230,30 @@ } //add header - ExcelCell[] headerRow = new ExcelCell[1]; - headerRow[0] = new ExcelCell(getMessage("gradebook.summary.activity.marks"), true); - rowList.add(headerRow); - headerRow = new ExcelCell[3 + filteredActivityToUserDTOMap.keySet().size()]; - int count = 3; + ExcelRow headerRow = summarySheet.initRow(); + headerRow.addCell(getMessage("gradebook.summary.activity.marks"), true); + + headerRow = summarySheet.initRow(); + headerRow.addEmptyCells(3); for (Activity activity : filteredActivityToUserDTOMap.keySet()) { - headerRow[count++] = new ExcelCell(activity.getTitle(), true); // this one works + headerRow.addCell(activity.getTitle(), true); // this one works } - rowList.add(headerRow); - headerRow = new ExcelCell[4 + filteredActivityToUserDTOMap.keySet().size()]; - count = 0; - headerRow[count++] = new ExcelCell(getMessage("gradebook.export.last.name"), true); - headerRow[count++] = new ExcelCell(getMessage("gradebook.export.first.name"), true); - headerRow[count++] = new ExcelCell(getMessage("gradebook.export.login"), true); + + headerRow = summarySheet.initRow(); + headerRow.addCell(getMessage("gradebook.export.last.name"), true); + headerRow.addCell(getMessage("gradebook.export.first.name"), true); + headerRow.addCell(getMessage("gradebook.export.login"), true); for (Activity activity : filteredActivityToUserDTOMap.keySet()) { - headerRow[count++] = new ExcelCell(getMessage("gradebook.columntitle.mark"), true); + headerRow.addCell(getMessage("gradebook.columntitle.mark"), true); } - headerRow[count] = new ExcelCell(getMessage("gradebook.export.total.mark"), true); - rowList.add(headerRow); + headerRow.addCell(getMessage("gradebook.export.total.mark"), true); //iterating through all users in a lesson for (GBUserGridRowDTO userRow : userRows) { - ExcelCell[] userDataRow = new ExcelCell[4 + filteredActivityToUserDTOMap.keySet().size()]; - count = 0; - userDataRow[count++] = new ExcelCell(userRow.getLastName(), false); - userDataRow[count++] = new ExcelCell(userRow.getFirstName(), false); - userDataRow[count++] = new ExcelCell(userRow.getLogin(), false); + ExcelRow userDataRow = summarySheet.initRow(); + userDataRow.addCell(userRow.getLastName()); + userDataRow.addCell(userRow.getFirstName()); + userDataRow.addCell(userRow.getLogin()); for (Activity activity : filteredActivityToUserDTOMap.keySet()) { @@ -1272,35 +1266,32 @@ break; } } - userDataRow[count++] = new ExcelCell(userActivityMark, false); + userDataRow.addCell(userActivityMark); } - userDataRow[count] = isWeighted ? GradebookUtil.createPercentageCell(userRow.getMark(), true) - : new ExcelCell(userRow.getMark(), false); - rowList.add(userDataRow); + if (isWeighted) { + userDataRow.addPercentageCell(userRow.getMark() / 100.0); + } else { + userDataRow.addCell(userRow.getMark()); + } } - ExcelCell[][] summaryData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("gradebook.export.lesson.summary"), summaryData); - // -------------------- process activity excel page -------------------------------- - List rowList1 = new LinkedList<>(); + ExcelSheet activitySheet = new ExcelSheet(getMessage("gradebook.gridtitle.activitygrid")); + sheets.add(activitySheet); for (Activity activity : activityToUserDTOMap.keySet()) { - ExcelCell[] activityTitleRow = new ExcelCell[7]; - activityTitleRow[0] = new ExcelCell(activity.getTitle(), true); - rowList1.add(activityTitleRow); + ExcelRow activityTitleRow = activitySheet.initRow(); + activityTitleRow.addCell(activity.getTitle(), true); - count = 0; - ExcelCell[] titleRow = new ExcelCell[7]; - titleRow[count++] = new ExcelCell(getMessage("gradebook.export.last.name"), true); - titleRow[count++] = new ExcelCell(getMessage("gradebook.export.first.name"), true); - titleRow[count++] = new ExcelCell(getMessage("gradebook.export.login"), true); - titleRow[count++] = new ExcelCell(getMessage("gradebook.columntitle.startDate"), true); - titleRow[count++] = new ExcelCell(getMessage("gradebook.columntitle.completeDate"), true); - titleRow[count++] = new ExcelCell(getMessage("gradebook.export.time.taken.seconds"), true); - titleRow[count++] = new ExcelCell(getMessage("gradebook.columntitle.mark"), true); - rowList1.add(titleRow); + ExcelRow titleRow = activitySheet.initRow(); + titleRow.addCell(getMessage("gradebook.export.last.name"), true); + titleRow.addCell(getMessage("gradebook.export.first.name"), true); + titleRow.addCell(getMessage("gradebook.export.login"), true); + titleRow.addCell(getMessage("gradebook.columntitle.startDate"), true); + titleRow.addCell(getMessage("gradebook.columntitle.completeDate"), true); + titleRow.addCell(getMessage("gradebook.export.time.taken.seconds"), true); + titleRow.addCell(getMessage("gradebook.columntitle.mark"), true); // Get the rest of the data List userDtos = activityToUserDTOMap.get(activity); @@ -1311,45 +1302,40 @@ String finishDate = (userDto.getFinishDate() == null) ? "" : FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(userDto.getFinishDate()); - count = 0; - ExcelCell[] userDataRow = new ExcelCell[7]; - userDataRow[count++] = new ExcelCell(userDto.getLastName(), false); - userDataRow[count++] = new ExcelCell(userDto.getFirstName(), false); - userDataRow[count++] = new ExcelCell(userDto.getLogin(), false); - userDataRow[count++] = new ExcelCell(startDate, false); - userDataRow[count++] = new ExcelCell(finishDate, false); - userDataRow[count++] = new ExcelCell(userDto.getTimeTakenSeconds(), false); - userDataRow[count++] = new ExcelCell(userDto.getMark(), false); - rowList1.add(userDataRow); + ExcelRow userDataRow = activitySheet.initRow(); + userDataRow.addCell(userDto.getLastName()); + userDataRow.addCell(userDto.getFirstName()); + userDataRow.addCell(userDto.getLogin()); + userDataRow.addCell(startDate); + userDataRow.addCell(finishDate); + userDataRow.addCell(userDto.getTimeTakenSeconds()); + userDataRow.addCell(userDto.getMark()); } - rowList1.add(GradebookService.EMPTY_ROW); + activitySheet.addEmptyRow(); } - ExcelCell[][] activityData = rowList1.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("gradebook.gridtitle.activitygrid"), activityData); - // -------------------- process Learner View page -------------------------------- + ExcelSheet learnerViewSheet = new ExcelSheet(getMessage("gradebook.export.learner.view")); + sheets.add(learnerViewSheet); + Set learners = new TreeSet(new LastNameAlphabeticComparator()); if (lesson.getAllLearners() != null) { learners.addAll(lesson.getAllLearners()); } - - rowList = new LinkedList<>(); + for (User learner : learners) { - userTitleRow = new ExcelCell[4]; - userTitleRow[0] = new ExcelCell(learner.getFullName() + " (" + learner.getLogin() + ")", true); - rowList.add(userTitleRow); + userTitleRow = learnerViewSheet.initRow(); + userTitleRow.addCell(learner.getFullName() + " (" + learner.getLogin() + ")", true); - ExcelCell[] titleRow = new ExcelCell[5]; - titleRow[0] = new ExcelCell(getMessage("gradebook.export.activity"), true); - titleRow[1] = new ExcelCell(getMessage("gradebook.columntitle.startDate"), true); - titleRow[2] = new ExcelCell(getMessage("gradebook.columntitle.completeDate"), true); - titleRow[3] = new ExcelCell(getMessage("gradebook.export.time.taken.seconds"), true); - titleRow[4] = new ExcelCell(getMessage("gradebook.columntitle.mark"), true); - rowList.add(titleRow); + ExcelRow titleRow = learnerViewSheet.initRow(); + titleRow.addCell(getMessage("gradebook.export.activity"), true); + titleRow.addCell(getMessage("gradebook.columntitle.startDate"), true); + titleRow.addCell(getMessage("gradebook.columntitle.completeDate"), true); + titleRow.addCell(getMessage("gradebook.export.time.taken.seconds"), true); + titleRow.addCell(getMessage("gradebook.columntitle.mark"), true); Map activityIdToName = new HashMap<>(); @@ -1386,37 +1372,34 @@ String finishDate = (userDto.getFinishDate() == null) ? "" : FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(userDto.getFinishDate()); - ExcelCell[] activityDataRow = new ExcelCell[5]; - activityDataRow[0] = new ExcelCell(activityRowName, false); - activityDataRow[1] = new ExcelCell(startDate, false); - activityDataRow[2] = new ExcelCell(finishDate, false); - activityDataRow[3] = new ExcelCell(userDto.getTimeTakenSeconds(), false); - activityDataRow[4] = new ExcelCell(userDto.getMark(), false); - rowList.add(activityDataRow); + ExcelRow activityDataRow = learnerViewSheet.initRow(); + activityDataRow.addCell(activityRowName); + activityDataRow.addCell(startDate); + activityDataRow.addCell(finishDate); + activityDataRow.addCell(userDto.getTimeTakenSeconds()); + activityDataRow.addCell(userDto.getMark()); } } // check if learner has restarted the lesson and has archived marks boolean hasArchivedMarks = gradebookDAO.hasArchivedMarks(lesson.getLessonId(), learner.getUserId()); if (hasArchivedMarks) { // "Previous attempts" row - ExcelCell[] attemptsRow = new ExcelCell[1]; - attemptsRow[0] = new ExcelCell(getMessage("gradebook.columntitle.attempts"), true); - rowList.add(attemptsRow); + ExcelRow attemptsRow = learnerViewSheet.initRow(); + attemptsRow.addCell(getMessage("gradebook.columntitle.attempts"), true); List lessonArchives = gradebookDAO .getArchivedLessonMarks(lesson.getLessonId(), learner.getUserId()); int attemptOrder = lessonArchives.size(); // go through each lesson attempt for (GradebookUserLessonArchive lessonArchive : lessonArchives) { // lesson attempt header - ExcelCell[] attemptRow = new ExcelCell[4]; - attemptRow[0] = new ExcelCell(getMessage("gradebook.columntitle.attempt"), true); - attemptRow[1] = new ExcelCell(attemptOrder, true); - attemptRow[1].setAlignment(ExcelCell.ALIGN_LEFT); - attemptRow[2] = new ExcelCell(getMessage("gradebook.columntitle.lesson.mark"), true); - attemptRow[3] = new ExcelCell(lessonArchive.getMark(), false); - rowList.add(attemptRow); + ExcelRow attemptRow = learnerViewSheet.initRow(); + attemptRow.addCell(getMessage("gradebook.columntitle.attempt"), true); + ExcelCell cell = attemptRow.addCell(attemptOrder, true); + cell.setAlignment(ExcelCell.ALIGN_LEFT); + attemptRow.addCell(getMessage("gradebook.columntitle.lesson.mark"), true); + attemptRow.addCell(lessonArchive.getMark()); Date archiveDate = lessonArchive.getArchiveDate(); LearnerProgressArchive learnerProgress = learnerProgressDAO @@ -1435,83 +1418,71 @@ } } - ExcelCell[] activityDataRow = new ExcelCell[5]; - activityDataRow[0] = new ExcelCell(activityIdToName.get(activity.getActivityId()), false); + ExcelRow activityDataRow = learnerViewSheet.initRow(); + activityDataRow.addCell(activityIdToName.get(activity.getActivityId())); Date startDate = getActivityStartDate(learnerProgress, activity, null); - activityDataRow[1] = new ExcelCell(startDate == null ? "" - : FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(startDate), false); + activityDataRow.addCell(startDate == null ? "" + : FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(startDate)); Date finishDate = getActivityFinishDate(learnerProgress, activity, null); - activityDataRow[2] = new ExcelCell(finishDate == null ? "" - : FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(finishDate), false); + activityDataRow.addCell(finishDate == null ? "" + : FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(finishDate)); Long duration = getActivityDuration(learnerProgress, activity); - activityDataRow[3] = new ExcelCell(duration == null ? "" : duration / 1000, false); - activityDataRow[4] = new ExcelCell(activityArchive == null ? "" : activityArchive.getMark(), + activityDataRow.addCell(duration == null ? "" : duration / 1000); + activityDataRow.addCell(activityArchive == null ? "" : activityArchive.getMark(), false); - - rowList.add(activityDataRow); } attemptOrder--; } } - - rowList.add(GradebookService.EMPTY_ROW); + learnerViewSheet.addEmptyRow(); } - ExcelCell[][] userData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("gradebook.export.learner.view"), userData); - - return dataToExport; + return sheets; } @Override @SuppressWarnings("unchecked") - public LinkedHashMap exportCourseGradebook(Integer userId, Integer organisationId) { - LinkedHashMap dataToExport = new LinkedHashMap<>(); + public List exportCourseGradebook(Integer userId, Integer organisationId) { + List sheets = new LinkedList(); // The entire data list - List rowList = new LinkedList<>(); + ExcelSheet sheet = new ExcelSheet(getMessage("gradebook.exportcourse.course.summary")); + sheets.add(sheet); Set lessons = new TreeSet<>(new LessonComparator()); lessons.addAll(lessonService.getLessonsByGroupAndUser(userId, organisationId)); Map isWeightedLessonMap = new HashMap<>(); if ((lessons != null) && (lessons.size() > 0)) { - - int numberOfCellsInARow = 3 + (lessons.size() * 6); - // Adding the user lesson marks to the summary---------------------- - ExcelCell[] lessonsNames = new ExcelCell[numberOfCellsInARow]; - int i = 0; - lessonsNames[i++] = new ExcelCell("", false); - lessonsNames[i++] = new ExcelCell("", false); - lessonsNames[i++] = new ExcelCell("", false); + ExcelRow lessonsNames = sheet.initRow(); + lessonsNames.addCell(""); + lessonsNames.addCell(""); + lessonsNames.addCell(""); for (Lesson lesson : lessons) { - lessonsNames[i++] = new ExcelCell(messageService.getMessage("gradebook.exportcourse.lesson", + lessonsNames.addCell(messageService.getMessage("gradebook.exportcourse.lesson", new Object[] { lesson.getLessonName() }), true); - lessonsNames[i++] = new ExcelCell("", false); - lessonsNames[i++] = new ExcelCell("", false); - lessonsNames[i++] = new ExcelCell("", false); - lessonsNames[i++] = new ExcelCell("", false); - lessonsNames[i++] = new ExcelCell("", false); + lessonsNames.addCell(""); + lessonsNames.addCell(""); + lessonsNames.addCell(""); + lessonsNames.addCell(""); + lessonsNames.addCell(""); } - rowList.add(lessonsNames); // Setting up the user marks table - ExcelCell[] headerRow = new ExcelCell[numberOfCellsInARow]; - i = 0; - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.last.name"), true); - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.first.name"), true); - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.login"), true); + ExcelRow headerRow = sheet.initRow(); + headerRow.addCell(getMessage("gradebook.export.last.name"), true); + headerRow.addCell(getMessage("gradebook.export.first.name"), true); + headerRow.addCell(getMessage("gradebook.export.login"), true); for (Lesson lesson : lessons) { - headerRow[i++] = new ExcelCell(getMessage("gradebook.exportcourse.progress"), true); - headerRow[i++] = new ExcelCell(getMessage("gradebook.columntitle.startDate"), false); - headerRow[i++] = new ExcelCell(getMessage("gradebook.columntitle.completeDate"), false); - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.time.taken.seconds"), true); - headerRow[i++] = new ExcelCell(getMessage("gradebook.exportcourse.lessonFeedback"), true); - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.total.mark"), true); + headerRow.addCell(getMessage("gradebook.exportcourse.progress"), true); + headerRow.addCell(getMessage("gradebook.columntitle.startDate")); + headerRow.addCell(getMessage("gradebook.columntitle.completeDate")); + headerRow.addCell(getMessage("gradebook.export.time.taken.seconds"), true); + headerRow.addCell(getMessage("gradebook.exportcourse.lessonFeedback"), true); + headerRow.addCell(getMessage("gradebook.export.total.mark"), true); } - rowList.add(headerRow); // collect users from all lessons & check if lesson uses weightings LinkedHashSet allLearners = new LinkedHashSet<>(); @@ -1537,11 +1508,10 @@ sortedLearners.addAll(allLearners); for (User learner : sortedLearners) { - i = 0; - ExcelCell[] userDataRow = new ExcelCell[numberOfCellsInARow]; - userDataRow[i++] = new ExcelCell(learner.getLastName(), false); - userDataRow[i++] = new ExcelCell(learner.getFirstName(), false); - userDataRow[i++] = new ExcelCell(learner.getLogin(), false); + ExcelRow userDataRow = sheet.initRow(); + userDataRow.addCell(learner.getLastName()); + userDataRow.addCell(learner.getFirstName()); + userDataRow.addCell(learner.getLogin()); for (Lesson lesson : lessons) { GBUserGridRowDTO userDto = new GBUserGridRowDTO(learner); @@ -1607,35 +1577,31 @@ } //all of GBUserGridRowDTOs will be displayed on 1 line on course export. - userDataRow[i++] = new ExcelCell(getProgressMessage(userDto), false); - userDataRow[i++] = new ExcelCell(startDate, false); - userDataRow[i++] = new ExcelCell(finishDate, false); - userDataRow[i++] = new ExcelCell(timeTakenSeconds, false); - userDataRow[i++] = new ExcelCell(feedback, false); - userDataRow[i++] = isWeightedLessonMap.get(lesson.getLessonId()) - ? GradebookUtil.createPercentageCell(mark, true) - : new ExcelCell(mark, false); + userDataRow.addCell(getProgressMessage(userDto)); + userDataRow.addCell(startDate); + userDataRow.addCell(finishDate); + userDataRow.addCell(timeTakenSeconds); + userDataRow.addCell(feedback); + if (isWeightedLessonMap.get(lesson.getLessonId())) { + userDataRow.addPercentageCell(mark / 100.0); + } else { + userDataRow.addCell(mark); + } } - - rowList.add(userDataRow); } } - - ExcelCell[][] summaryData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("gradebook.exportcourse.course.summary"), summaryData); - return dataToExport; + return sheets; } @Override - public LinkedHashMap exportSelectedLessonsGradebook(Integer userId, Integer organisationId, + public List exportSelectedLessonsGradebook(Integer userId, Integer organisationId, String[] lessonIds, boolean simplified) { - LinkedHashMap dataToExport = new LinkedHashMap<>(); + List sheets = new LinkedList(); + ExcelSheet sheet = new ExcelSheet(getMessage("gradebook.exportcourse.course.summary")); + sheets.add(sheet); Organisation organisation = (Organisation) userService.findById(Organisation.class, organisationId); - // The entire data list - List rowList = new LinkedList<>(); - User user = (User) userService.findById(User.class, userId); Set selectedLessons = new TreeSet<>(new LessonComparator()); Map isWeightedLessonMap = new HashMap<>(); @@ -1671,7 +1637,6 @@ } if (!selectedLessons.isEmpty()) { - // Fetching the user data List lessonIdLongs = new LinkedList<>(); for (String lessonId : lessonIds) { @@ -1696,52 +1661,90 @@ String weightedMessage = messageService.getMessage("label.activity.marks.weighted"); // Lesson names row---------------------- - ExcelCell[] lessonsNames = new ExcelCell[numberCellsPerRow]; + ExcelRow lessonsNames = sheet.initRow(); if (simplified) { - int i = 3; + lessonsNames.addEmptyCells(3); for (Lesson lesson : selectedLessons) { - lessonsNames[i++] = new ExcelCell(lesson.getLessonName(), true) + lessonsNames.addCell(lesson.getLessonName(), true) .setAlignment(ExcelCell.ALIGN_CENTER); } - lessonsNames[i++] = new ExcelCell("", ExcelCell.BORDER_STYLE_LEFT_THICK); - lessonsNames[i++] = new ExcelCell(getMessage("label.overall.totals"), true) + lessonsNames.addCell("", ExcelCell.BORDER_STYLE_LEFT_THICK); + lessonsNames.addCell(getMessage("label.overall.totals"), true) .setAlignment(ExcelCell.ALIGN_CENTER); - lessonsNames[i++] = new ExcelCell("", ExcelCell.BORDER_STYLE_RIGHT_THICK); + lessonsNames.addCell("", ExcelCell.BORDER_STYLE_RIGHT_THICK); } else { - int i = 4; + lessonsNames.addEmptyCells(4); for (Lesson lesson : selectedLessons) { - List lessonActivities = lessonActivitiesMap.get(lesson.getLessonId()); - int numberActivities = lessonActivities.size(); String lessonName = isWeightedLessonMap.get(lesson.getLessonId()) ? new StringBuilder(lesson.getLessonName()).append(" ").append(weightedMessage).toString() : lesson.getLessonName(); - lessonsNames[i + numberActivities] = new ExcelCell(lessonName, true); - i += 9 + (numberActivities * 2); + + List lessonActivities = lessonActivitiesMap.get(lesson.getLessonId()); + int numberActivities = lessonActivities.size(); + lessonsNames.addEmptyCells(numberActivities); + lessonsNames.addCell(lessonName, true); + lessonsNames.addEmptyCells(9 + (numberActivities * 2)); } - i -= 2; - lessonsNames[i++] = new ExcelCell("", ExcelCell.BORDER_STYLE_LEFT_THIN); - lessonsNames[i++] = new ExcelCell(getMessage("label.overall.totals"), true); - lessonsNames[i++] = new ExcelCell("", ExcelCell.BORDER_STYLE_RIGHT_THICK); +// i -= 2; + lessonsNames.addCell("", ExcelCell.BORDER_STYLE_LEFT_THIN); + lessonsNames.addCell(getMessage("label.overall.totals"), true); + lessonsNames.addCell("", ExcelCell.BORDER_STYLE_RIGHT_THICK); } - rowList.add(lessonsNames); // Headers row---------------------- + ExcelRow headerRow = sheet.initRow(); if (simplified) { - rowList.add(createSelectedLessonsHeaderSimplified(selectedLessons, numberCellsPerRow)); + // Simplified shows the learner's name once at the far left of the spreadsheet. + headerRow.addCell(getMessage("gradebook.export.last.name")); + headerRow.addCell(getMessage("gradebook.export.first.name")); + headerRow.addCell(getMessage("gradebook.export.login")); + + for (Lesson lesson : selectedLessons) { + headerRow.addCell(getMessage("label.total.actuals"), false, ExcelCell.BORDER_STYLE_LEFT_THIN) + .setAlignment(ExcelCell.ALIGN_CENTER); + } + + headerRow.addCell(getMessage("label.actuals"), true, ExcelCell.BORDER_STYLE_LEFT_THICK) + .setAlignment(ExcelCell.ALIGN_CENTER); + headerRow.addCell(getMessage("label.max")).setAlignment(ExcelCell.ALIGN_CENTER); + headerRow.addCell("%", false, ExcelCell.BORDER_STYLE_RIGHT_THICK) + .setAlignment(ExcelCell.ALIGN_CENTER); } else { - rowList.add(createSelectedLessonsHeaderFull(selectedLessons, lessonActivitiesMap, numberCellsPerRow)); + //create Selected Lessons Header Full + for (Lesson lesson : selectedLessons) { + headerRow.addCell(getMessage("gradebook.export.last.name")); + headerRow.addCell(getMessage("gradebook.export.first.name")); + headerRow.addCell(getMessage("gradebook.export.login")); + headerRow.addCell(getMessage("label.group")); + headerRow.addCell(getMessage("gradebook.columntitle.startDate")); + headerRow.addCell(getMessage("gradebook.columntitle.completeDate")); + + List activities = lessonActivitiesMap.get(lesson.getLessonId()); + for (Activity activity : activities) { + headerRow.addCell(activity.getTitle(), true); + headerRow.addCell(getMessage("label.max.possible")); + } + + headerRow.addCell(getMessage("label.total.actuals"), true, ExcelCell.BORDER_STYLE_LEFT_THIN); + headerRow.addCell(getMessage("label.max.mark")); + headerRow.addCell("%", ExcelCell.BORDER_STYLE_RIGHT_THICK); + } + + headerRow.addEmptyCells(2); + headerRow.addCell(getMessage("label.actuals"), true, ExcelCell.BORDER_STYLE_LEFT_THIN); + headerRow.addCell(getMessage("label.max"), true); + headerRow.addCell("%", true, ExcelCell.BORDER_STYLE_RIGHT_THICK); } // Actual data rows---------------------- for (User learner : allLearners) { Double overallTotal = 0d; Double overallMaxMark = 0d; - ExcelCell[] userRow = new ExcelCell[numberCellsPerRow]; - int i = 0; + ExcelRow userRow = sheet.initRow(); if (simplified) { - i = addUsernameCells(learner, userRow, i); + addUsernameCells(learner, userRow); } for (Lesson lesson : selectedLessons) { @@ -1752,14 +1755,14 @@ Boolean weighted = isWeightedLessonMap.get(lesson.getLessonId()); if (!simplified) { - i = addUsernameCells(learner, userRow, i); + addUsernameCells(learner, userRow); // check if learner is participating in this lesson if (!lesson.getAllLearners().contains(learner)) { - i += 3 + (activities.size() * 2); - userRow[i++] = new ExcelCell("", ExcelCell.BORDER_STYLE_LEFT_THIN); - userRow[i++] = new ExcelCell("", false); - userRow[i++] = new ExcelCell("", ExcelCell.BORDER_STYLE_RIGHT_THICK); + userRow.addEmptyCells(3 + (activities.size() * 2)); + userRow.addCell("", ExcelCell.BORDER_STYLE_LEFT_THIN); + userRow.addCell(""); + userRow.addCell("", ExcelCell.BORDER_STYLE_RIGHT_THICK); continue; } @@ -1771,7 +1774,7 @@ break; } } - userRow[i++] = new ExcelCell(groupName, false); + userRow.addCell(groupName); //start and complete dates LearnerProgress learnerProgress = null; @@ -1784,11 +1787,11 @@ String startDate = (learnerProgress == null || learnerProgress.getStartDate() == null) ? "" : FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT .format(learnerProgress.getStartDate()); - userRow[i++] = new ExcelCell(startDate, false); + userRow.addCell(startDate); String finishDate = (learnerProgress == null || learnerProgress.getFinishDate() == null) ? "" : FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT .format(learnerProgress.getFinishDate()); - userRow[i++] = new ExcelCell(finishDate, false); + userRow.addCell(finishDate); } for (ToolActivity activity : activities) { @@ -1813,19 +1816,19 @@ mark = doWeightedMarkCalc(mark, activity, weight, rawActivityTotalMarks); } if (!simplified) { - userRow[i++] = new ExcelCell(mark, false); + userRow.addCell(mark); } } else { if (!simplified) { - userRow[i++] = new ExcelCell("", false); + userRow.addCell(""); } } if (!simplified) { if (weightedActivityTotalMarks > 0) { - userRow[i++] = new ExcelCell(weightedActivityTotalMarks, false); + userRow.addCell(weightedActivityTotalMarks); } else { - userRow[i++] = new ExcelCell("", false); + userRow.addCell(""); } } @@ -1837,107 +1840,44 @@ if (simplified) { if (weighted) { - userRow[i++] = GradebookUtil.createPercentageCell(lessonTotal, true, false, - ExcelCell.BORDER_STYLE_LEFT_THIN); + userRow.addPercentageCell(lessonTotal / 100.0, false, ExcelCell.BORDER_STYLE_LEFT_THIN); } else { - userRow[i++] = new ExcelCell(lessonTotal, ExcelCell.BORDER_STYLE_LEFT_THIN); + userRow.addCell(lessonTotal, ExcelCell.BORDER_STYLE_LEFT_THIN); } } else { - userRow[i++] = new ExcelCell(lessonTotal, ExcelCell.BORDER_STYLE_LEFT_THIN); - userRow[i++] = new ExcelCell(lessonMaxMark, false); + userRow.addCell(lessonTotal, ExcelCell.BORDER_STYLE_LEFT_THIN); + userRow.addCell(lessonMaxMark); Double percentage = (lessonMaxMark != 0) ? lessonTotal / lessonMaxMark : 0d; - userRow[i++] = GradebookUtil.createPercentageCell(percentage, false, false, - ExcelCell.BORDER_STYLE_RIGHT_THICK); + userRow.addPercentageCell(percentage, false, ExcelCell.BORDER_STYLE_RIGHT_THICK); } } Double percentage = (overallMaxMark != 0) ? overallTotal / overallMaxMark : 0d; if (simplified) { - userRow[i++] = new ExcelCell(overallTotal, ExcelCell.BORDER_STYLE_LEFT_THICK); - userRow[i++] = new ExcelCell(overallMaxMark, false); - userRow[i++] = GradebookUtil.createPercentageCell(percentage, false, false, - ExcelCell.BORDER_STYLE_RIGHT_THICK); + userRow.addCell(overallTotal, ExcelCell.BORDER_STYLE_LEFT_THICK); + userRow.addCell(overallMaxMark); + userRow.addPercentageCell(percentage, false, ExcelCell.BORDER_STYLE_RIGHT_THICK); } else { - i += 2; - userRow[i++] = new ExcelCell(overallTotal, ExcelCell.BORDER_STYLE_LEFT_THIN); - userRow[i++] = new ExcelCell(overallMaxMark, false); - userRow[i++] = GradebookUtil.createPercentageCell(percentage, false, true, - ExcelCell.BORDER_STYLE_RIGHT_THICK); + userRow.addEmptyCells(2); + userRow.addCell(overallTotal, ExcelCell.BORDER_STYLE_LEFT_THIN); + userRow.addCell(overallMaxMark); + userRow.addPercentageCell(percentage, true, ExcelCell.BORDER_STYLE_RIGHT_THICK); } - - rowList.add(userRow); } } - - ExcelCell[][] summaryData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("gradebook.exportcourse.course.summary"), summaryData); - return dataToExport; + return sheets; } - private int addUsernameCells(User learner, ExcelCell[] userRow, int i) { + private void addUsernameCells(User learner, ExcelRow userRow) { //first, last names and login String lastName = (learner.getLastName() == null) ? "" : learner.getLastName().toUpperCase(); - userRow[i++] = new ExcelCell(lastName, false); + userRow.addCell(lastName); String firstName = (learner.getFirstName() == null) ? "" : learner.getFirstName().toUpperCase(); - userRow[i++] = new ExcelCell(firstName, false); - userRow[i++] = new ExcelCell(learner.getLogin(), false); - return i; + userRow.addCell(firstName); + userRow.addCell(learner.getLogin()); } - private ExcelCell[] createSelectedLessonsHeaderFull(Set selectedLessons, - Map> lessonActivitiesMap, int numberCellsPerRow) { - int i; - ExcelCell[] headerRow = new ExcelCell[numberCellsPerRow]; - i = 0; - - for (Lesson lesson : selectedLessons) { - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.last.name"), false); - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.first.name"), false); - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.login"), false); - headerRow[i++] = new ExcelCell(getMessage("label.group"), false); - headerRow[i++] = new ExcelCell(getMessage("gradebook.columntitle.startDate"), false); - headerRow[i++] = new ExcelCell(getMessage("gradebook.columntitle.completeDate"), false); - - List activities = lessonActivitiesMap.get(lesson.getLessonId()); - for (Activity activity : activities) { - headerRow[i++] = new ExcelCell(activity.getTitle(), true); - headerRow[i++] = new ExcelCell(getMessage("label.max.possible"), false); - } - - headerRow[i++] = new ExcelCell(getMessage("label.total.actuals"), true, ExcelCell.BORDER_STYLE_LEFT_THIN); - headerRow[i++] = new ExcelCell(getMessage("label.max.mark"), false); - headerRow[i++] = new ExcelCell("%", ExcelCell.BORDER_STYLE_RIGHT_THICK); - } - i += 2; - headerRow[i++] = new ExcelCell(getMessage("label.actuals"), true, ExcelCell.BORDER_STYLE_LEFT_THIN); - headerRow[i++] = new ExcelCell(getMessage("label.max"), true); - headerRow[i++] = new ExcelCell("%", true, ExcelCell.BORDER_STYLE_RIGHT_THICK); - return headerRow; - } - - private ExcelCell[] createSelectedLessonsHeaderSimplified(Set selectedLessons, int numberCellsPerRow) { - int i = 0; - ExcelCell[] headerRow = new ExcelCell[numberCellsPerRow]; - - // Simplified shows the learner's name once at the far left of the spreadsheet. - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.last.name"), false); - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.first.name"), false); - headerRow[i++] = new ExcelCell(getMessage("gradebook.export.login"), false); - - for (Lesson lesson : selectedLessons) { - headerRow[i++] = new ExcelCell(getMessage("label.total.actuals"), false, ExcelCell.BORDER_STYLE_LEFT_THIN) - .setAlignment(ExcelCell.ALIGN_CENTER); - } - - headerRow[i++] = new ExcelCell(getMessage("label.actuals"), true, ExcelCell.BORDER_STYLE_LEFT_THICK) - .setAlignment(ExcelCell.ALIGN_CENTER); - headerRow[i++] = new ExcelCell(getMessage("label.max"), false).setAlignment(ExcelCell.ALIGN_CENTER); - headerRow[i++] = new ExcelCell("%", false, ExcelCell.BORDER_STYLE_RIGHT_THICK) - .setAlignment(ExcelCell.ALIGN_CENTER); - return headerRow; - } - @Override public void updateGradebookUserActivityMark(Double mark, String feedback, Integer userID, Long toolSessionID, Boolean markedInGradebook) { @@ -2328,10 +2268,10 @@ if (startDate != null) { if (timeZone == null) { - GradebookService.logger.warn("No user time zone provided, leaving server default"); + logger.warn("No user time zone provided, leaving server default"); } else { - if (GradebookService.logger.isTraceEnabled()) { - GradebookService.logger.trace("Adjusting time according to zone \"" + timeZone + "\""); + if (logger.isTraceEnabled()) { + logger.trace("Adjusting time according to zone \"" + timeZone + "\""); } startDate = DateUtil.convertToTimeZoneFromDefault(timeZone, startDate); } @@ -2368,10 +2308,10 @@ if (finishDate != null) { if (timeZone == null) { - GradebookService.logger.warn("No user time zone provided, leaving server default"); + logger.warn("No user time zone provided, leaving server default"); } else { - if (GradebookService.logger.isTraceEnabled()) { - GradebookService.logger.trace("Adjusting time according to zone \"" + timeZone + "\""); + if (logger.isTraceEnabled()) { + logger.trace("Adjusting time according to zone \"" + timeZone + "\""); } finishDate = DateUtil.convertToTimeZoneFromDefault(timeZone, finishDate); } Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/IGradebookFullService.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/IGradebookFullService.java (.../IGradebookFullService.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/service/IGradebookFullService.java (.../IGradebookFullService.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -39,6 +39,7 @@ import org.lamsfoundation.lams.usermanagement.Organisation; import org.lamsfoundation.lams.usermanagement.User; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelSheet; public interface IGradebookFullService extends IGradebookService { @@ -276,7 +277,7 @@ * @param lesson * @return */ - LinkedHashMap exportLessonGradebook(Lesson lesson); + List exportLessonGradebook(Lesson lesson); /** * Get the summary data for course in order to create excel export @@ -285,7 +286,7 @@ * @param organisationId * @return */ - LinkedHashMap exportCourseGradebook(Integer userId, Integer organisationId); + List exportCourseGradebook(Integer userId, Integer organisationId); /** * Get the summary data for selected lessons in order to create excel export @@ -294,8 +295,8 @@ * @param organisationId * @return */ - LinkedHashMap exportSelectedLessonsGradebook(Integer userId, Integer organisationId, - String[] lessonIds, boolean simplified); + List exportSelectedLessonsGradebook(Integer userId, Integer organisationId, String[] lessonIds, + boolean simplified); /** * Get the raw overall marks for a lesson for charting purposes Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/util/GradebookUtil.java =================================================================== diff -u -rcbf95a868252401757c61327b3d9a383119ff9b5 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/util/GradebookUtil.java (.../GradebookUtil.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/util/GradebookUtil.java (.../GradebookUtil.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -183,27 +183,6 @@ } - public static ExcelCell createPercentageCell(Double mark, boolean markConversionNeeded) { - return GradebookUtil.createPercentageCell(mark, markConversionNeeded, false, 0); - } - -// public static ExcelCell createPercentageCell(Double mark, boolean markConversionNeeded, Boolean isBold) { -// return createPercentageCell(mark, markConversionNeeded, isBold, 0); -// } - - // if markConversionNeeded is true then mark is divided by 100. Otherwise assumes already a percentage. - public static ExcelCell createPercentageCell(Double mark, boolean markConversionNeeded, Boolean isBold, - int borderStyle) { - Double convertedMark = null; - if (mark != null) { - convertedMark = markConversionNeeded ? mark / 100.0 : mark; - } - - ExcelCell userMarkCell = new ExcelCell(convertedMark, isBold, borderStyle); - userMarkCell.setDataFormat(ExcelCell.CELL_FORMAT_PERCENTAGE); - return userMarkCell; - } - private static Document getDocument() throws ParserConfigurationException { DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance(); DocumentBuilder builder = factory.newDocumentBuilder(); Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/controller/GradebookMonitoringController.java =================================================================== diff -u -rcbf95a868252401757c61327b3d9a383119ff9b5 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/controller/GradebookMonitoringController.java (.../GradebookMonitoringController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/controller/GradebookMonitoringController.java (.../GradebookMonitoringController.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -53,6 +53,7 @@ import org.lamsfoundation.lams.util.JsonUtil; import org.lamsfoundation.lams.util.WebUtil; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import org.lamsfoundation.lams.util.excel.ExcelUtil; import org.lamsfoundation.lams.web.session.SessionManager; import org.lamsfoundation.lams.web.util.AttributeNames; @@ -292,15 +293,15 @@ log.debug("Exporting to a spreadsheet gradebook lesson: " + lessonID); ServletOutputStream out = response.getOutputStream(); - LinkedHashMap dataToExport = gradebookService.exportLessonGradebook(lesson); + List sheets = gradebookService.exportLessonGradebook(lesson); // set cookie that will tell JS script that export has been finished String downloadTokenValue = WebUtil.readStrParam(request, "downloadTokenValue"); Cookie fileDownloadTokenCookie = new Cookie("fileDownloadToken", downloadTokenValue); fileDownloadTokenCookie.setPath("/"); response.addCookie(fileDownloadTokenCookie); - ExcelUtil.createExcel(out, dataToExport, gradebookService.getMessage("gradebook.export.dateheader"), true); + ExcelUtil.createExcel(out, sheets, gradebookService.getMessage("gradebook.export.dateheader"), true); } /** @@ -320,8 +321,7 @@ if (log.isDebugEnabled()) { log.debug("Exporting to a spreadsheet course: " + organisationID); } - LinkedHashMap dataToExport = gradebookService.exportCourseGradebook(user.getUserID(), - organisationID); + List sheets = gradebookService.exportCourseGradebook(user.getUserID(), organisationID); String fileName = organisation.getName().replaceAll(" ", "_") + ".xlsx"; fileName = FileUtil.encodeFilenameForDownload(request, fileName); @@ -337,7 +337,7 @@ // Code to generate file and write file contents to response ServletOutputStream out = response.getOutputStream(); - ExcelUtil.createExcel(out, dataToExport, gradebookService.getMessage("gradebook.export.dateheader"), true); + ExcelUtil.createExcel(out, sheets, gradebookService.getMessage("gradebook.export.dateheader"), true); } /** @@ -361,8 +361,8 @@ log.debug("Exporting to a spreadsheet lessons " + Arrays.toString(lessonIds) + " from course: " + organisationID); } - LinkedHashMap dataToExport = gradebookService - .exportSelectedLessonsGradebook(user.getUserID(), organisationID, lessonIds, simplified); + List sheets = gradebookService.exportSelectedLessonsGradebook(user.getUserID(), organisationID, + lessonIds, simplified); String fileName = organisation.getName().replaceAll(" ", "_") + ".xlsx"; fileName = FileUtil.encodeFilenameForDownload(request, fileName); @@ -378,7 +378,7 @@ // Code to generate file and write file contents to response ServletOutputStream out = response.getOutputStream(); - ExcelUtil.createExcel(out, dataToExport, null, false); + ExcelUtil.createExcel(out, sheets, null, false); } /** Index: lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/KumaliveController.java =================================================================== diff -u -rcbf95a868252401757c61327b3d9a383119ff9b5 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/KumaliveController.java (.../KumaliveController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) +++ lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/KumaliveController.java (.../KumaliveController.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -25,6 +25,7 @@ import org.lamsfoundation.lams.util.JsonUtil; import org.lamsfoundation.lams.util.WebUtil; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import org.lamsfoundation.lams.util.excel.ExcelUtil; import org.lamsfoundation.lams.web.session.SessionManager; import org.lamsfoundation.lams.web.util.AttributeNames; @@ -266,11 +267,11 @@ response.sendError(HttpServletResponse.SC_FORBIDDEN, warning); } - LinkedHashMap dataToExport = null; + List sheets = null; if (kumaliveIds == null) { - dataToExport = kumaliveService.exportKumalives(organisationId); + sheets = kumaliveService.exportKumalives(organisationId); } else { - dataToExport = kumaliveService.exportKumalives(kumaliveIds); + sheets = kumaliveService.exportKumalives(kumaliveIds); } String fileName = "kumalive_report.xlsx"; fileName = FileUtil.encodeFilenameForDownload(request, fileName); @@ -284,7 +285,7 @@ fileDownloadTokenCookie.setPath("/"); response.addCookie(fileDownloadTokenCookie); - ExcelUtil.createExcel(response.getOutputStream(), dataToExport, "Exported on:", true); + ExcelUtil.createExcel(response.getOutputStream(), sheets, "Exported on:", true); } @RequestMapping("/saveRubrics") Index: lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/service/IKumaliveService.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/service/IKumaliveService.java (.../IKumaliveService.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/service/IKumaliveService.java (.../IKumaliveService.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -31,6 +31,7 @@ import org.lamsfoundation.lams.learning.kumalive.model.KumalivePoll; import org.lamsfoundation.lams.learning.kumalive.model.KumaliveRubric; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import com.fasterxml.jackson.databind.node.ArrayNode; import com.fasterxml.jackson.databind.node.ObjectNode; @@ -57,9 +58,9 @@ ObjectNode getReportUserData(Long kumaliveId, Integer userId); - LinkedHashMap exportKumalives(List kumaliveIds); + List exportKumalives(List kumaliveIds); - LinkedHashMap exportKumalives(Integer organisationId); + List exportKumalives(Integer organisationId); KumalivePoll getPollByKumaliveId(Long kumaliveId); Index: lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/service/KumaliveService.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/service/KumaliveService.java (.../KumaliveService.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/service/KumaliveService.java (.../KumaliveService.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -26,6 +26,7 @@ import java.math.RoundingMode; import java.text.DecimalFormat; import java.text.DecimalFormatSymbols; +import java.util.ArrayList; import java.util.Comparator; import java.util.Date; import java.util.HashMap; @@ -57,6 +58,8 @@ import org.lamsfoundation.lams.util.FileUtil; import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelRow; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import com.fasterxml.jackson.databind.node.ArrayNode; import com.fasterxml.jackson.databind.node.JsonNodeFactory; @@ -67,7 +70,6 @@ private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("#.##", new DecimalFormatSymbols(Locale.ENGLISH)); - private static final ExcelCell[] EMPTY_ROW = new ExcelCell[1]; private static final Comparator USER_COMPARATOR = new LastNameAlphabeticComparator(); private IKumaliveDAO kumaliveDAO; @@ -330,7 +332,7 @@ * Exports to Excel all Kumalives in the given organisation. */ @Override - public LinkedHashMap exportKumalives(Integer organisationId) { + public List exportKumalives(Integer organisationId) { List kumalives = kumaliveDAO.findKumalives(organisationId, "", true); return export(kumalives); } @@ -339,45 +341,42 @@ * Exports to Excel Kumalives with given IDs. */ @Override - public LinkedHashMap exportKumalives(List kumaliveIds) { + public List exportKumalives(List kumaliveIds) { List kumalives = kumaliveDAO.findKumalives(kumaliveIds); return export(kumalives); } /** * Exports to Excel given Kumalives. */ - private LinkedHashMap export(List kumalives) { + private List export(List kumalives) { Map>> learnerSummaries = new TreeMap<>(USER_COMPARATOR); + List sheets = new ArrayList<>(); - Organisation organisation = kumalives.get(0).getOrganisation(); - LinkedHashMap dataToExport = new LinkedHashMap(); + ExcelSheet kumalivesSheet = buildReportKumalivesSheet(kumalives, learnerSummaries); + sheets.add(kumalivesSheet); - ExcelCell[][] kumalivesSheet = buildReportKumalivesSheet(kumalives, learnerSummaries); - dataToExport.put(messageService.getMessage("label.kumalive.report.sheet.header", - new Object[] { organisation.getName() }), kumalivesSheet); - - ExcelCell[][] learnersSheet = buildReportLearnersSheet(kumalives, learnerSummaries); - dataToExport.put(messageService.getMessage("label.kumalive.report.sheet.header.learners"), learnersSheet); - return dataToExport; + ExcelSheet learnersSheet = buildReportLearnersSheet(kumalives, learnerSummaries); + sheets.add(learnersSheet); + return sheets; } /** * Builds Kumalives summary sheet for the report */ - private ExcelCell[][] buildReportKumalivesSheet(List kumalives, + private ExcelSheet buildReportKumalivesSheet(List kumalives, Map>> learnerSummaries) { - List rows = new LinkedList(); + Organisation organisation = kumalives.get(0).getOrganisation(); + ExcelSheet sheet = new ExcelSheet(messageService.getMessage("label.kumalive.report.sheet.header", + new Object[] { organisation.getName() })); // iterate over Kumalives and add them to the report for (Kumalive kumalive : kumalives) { - ExcelCell[] kumaliveHeaderRow = new ExcelCell[1]; - kumaliveHeaderRow[0] = new ExcelCell(messageService.getMessage("label.kumalive.report.name"), true); - rows.add(kumaliveHeaderRow); + ExcelRow kumaliveHeaderRow = sheet.initRow(); + kumaliveHeaderRow.addCell(messageService.getMessage("label.kumalive.report.name"), true); - ExcelCell[] kumaliveNameRow = new ExcelCell[1]; - kumaliveNameRow[0] = new ExcelCell(kumalive.getName(), false); - rows.add(kumaliveNameRow); + ExcelRow kumaliveNameRow = sheet.initRow(); + kumaliveNameRow.addCell(kumalive.getName(), false); // mapping user (sorted by name) -> batch (i.e. question ID) -> rubric -> score TreeMap>> scores = kumaliveDAO @@ -389,64 +388,60 @@ if (scores.size() == 0) { // no learners answered to question, carry on - ExcelCell[] noMarksRow = new ExcelCell[1]; - noMarksRow[0] = new ExcelCell(messageService.getMessage("label.kumalive.report.mark.none"), true); - rows.add(noMarksRow); - rows.add(EMPTY_ROW); + ExcelRow noMarksRow = sheet.initRow(); + noMarksRow.addCell(messageService.getMessage("label.kumalive.report.mark.none"), true); + sheet.addEmptyRow(); continue; } // headers for learners - ExcelCell[] marksRow = new ExcelCell[1]; - marksRow[0] = new ExcelCell(messageService.getMessage("label.kumalive.report.mark"), true); - rows.add(marksRow); + ExcelRow marksRow = sheet.initRow(); + marksRow.addCell(messageService.getMessage("label.kumalive.report.mark"), true); - ExcelCell[] userHeaderRow = new ExcelCell[5 + kumalive.getRubrics().size()]; - userHeaderRow[0] = new ExcelCell(messageService.getMessage("label.kumalive.report.last.name"), true); - userHeaderRow[1] = new ExcelCell(messageService.getMessage("label.kumalive.report.first.name"), true); - userHeaderRow[2] = new ExcelCell(messageService.getMessage("label.kumalive.report.login"), true); - userHeaderRow[3] = new ExcelCell(messageService.getMessage("label.kumalive.report.attempt"), true); + ExcelRow userHeaderRow = sheet.initRow(); + userHeaderRow.addCell(messageService.getMessage("label.kumalive.report.last.name"), true); + userHeaderRow.addCell(messageService.getMessage("label.kumalive.report.first.name"), true); + userHeaderRow.addCell(messageService.getMessage("label.kumalive.report.login"), true); + userHeaderRow.addCell(messageService.getMessage("label.kumalive.report.attempt"), true); // iterate over rubrics and make them columns int userRowLength = 4; for (KumaliveRubric rubric : kumalive.getRubrics()) { - userHeaderRow[userRowLength++] = new ExcelCell(rubric.getName(), true); + userHeaderRow.addCell(rubric.getName(), true); + userRowLength++; } - userHeaderRow[userRowLength] = new ExcelCell(messageService.getMessage("label.kumalive.report.time"), true); - rows.add(userHeaderRow); + userHeaderRow.addCell(messageService.getMessage("label.kumalive.report.time"), true); for (Entry>> learnerEntry : scores.entrySet()) { // learner details and average mark User learner = learnerEntry.getKey(); - ExcelCell[] userRow = new ExcelCell[userRowLength + 1]; - userRow[0] = new ExcelCell(learner.getFirstName(), false); - userRow[1] = new ExcelCell(learner.getLastName(), false); - userRow[2] = new ExcelCell(learner.getLogin(), false); - userRow[3] = new ExcelCell(messageService.getMessage("label.kumalive.report.average"), false); - rows.add(userRow); + ExcelRow userRow = sheet.initRow(); + userRow.addCell(learner.getFirstName(), false); + userRow.addCell(learner.getLastName(), false); + userRow.addCell(learner.getLogin(), false); + userRow.addCell(messageService.getMessage("label.kumalive.report.average"), false); // build rows for each attempt (answer to a question, batch) Short[][] resultsByRubric = new Short[learnerEntry.getValue().size()][userRowLength - 4]; int attempt = 0; Long[] rubricIds = new Long[kumalive.getRubrics().size()]; for (Entry> batchEntry : scores.get(learner).entrySet()) { - ExcelCell[] attemptRow = new ExcelCell[userRowLength + 1]; - attemptRow[3] = new ExcelCell( - messageService.getMessage("label.kumalive.report.attempt") + " " + (attempt + 1), false); + ExcelRow attemptRow = sheet.initRow(); + attemptRow.addEmptyCells(3); + attemptRow.addCell(messageService.getMessage("label.kumalive.report.attempt") + " " + (attempt + 1), + false); int rubricIndex = 0; Map results = batchEntry.getValue(); for (KumaliveRubric rubric : kumalive.getRubrics()) { rubricIds[rubricIndex] = rubric.getRubricId(); Short result = results.get(rubric.getRubricId()); - attemptRow[rubricIndex + 4] = new ExcelCell(result == null ? null : result.intValue(), false); + attemptRow.addCell(result == null ? null : result.intValue(), false); // store mark to calculate average later resultsByRubric[attempt][rubricIndex] = result; rubricIndex++; } attempt++; Date attemptDate = new Date(batchEntry.getKey() * 1000); - attemptRow[userRowLength] = new ExcelCell( - FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(attemptDate), false); - rows.add(attemptRow); + attemptRow.addCell(FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(attemptDate), false); } // calculate average per each rubric and update the first learner row for (int rubricIndex = 0; rubricIndex < userRowLength - 4; rubricIndex++) { @@ -462,7 +457,7 @@ } if (count > 0) { double average = Double.valueOf(DECIMAL_FORMAT.format(score / count)); - userRow[rubricIndex + 4] = new ExcelCell(average, false); + userRow.addCell(average, false); // populate data for learners sheet Map> learnerSummary = learnerSummaries.get(learner); if (learnerSummary == null) { @@ -478,70 +473,63 @@ } } } - rows.add(EMPTY_ROW); + sheet.addEmptyRow(); } - return rows.toArray(new ExcelCell[][] {}); + return sheet; } /** * Builds Kumalives summary sheet for the report */ - private ExcelCell[][] buildReportLearnersSheet(List kumalives, + private ExcelSheet buildReportLearnersSheet(List kumalives, Map>> learnerSummaries) { - List rows = new LinkedList(); - Map kumaliveNamePosition = new HashMap(); - List userHeaderRow = new LinkedList(); - userHeaderRow.add(new ExcelCell(messageService.getMessage("label.kumalive.report.last.name"), true)); - userHeaderRow.add(new ExcelCell(messageService.getMessage("label.kumalive.report.first.name"), true)); - userHeaderRow.add(new ExcelCell(messageService.getMessage("label.kumalive.report.login"), true)); - // count cells for kumalives and their rubrics - int userRowLength = 3; + ExcelSheet sheet = new ExcelSheet(messageService.getMessage("label.kumalive.report.sheet.header.learners")); + + // now that we know how long is the whole user row, we can build kumalive names row and add it first + ExcelRow kumaliveNameRow = sheet.initRow(); + kumaliveNameRow.addEmptyCells(3); for (Kumalive kumalive : kumalives) { - kumaliveNamePosition.put(kumalive.getName(), userRowLength); + kumaliveNameRow.addCell(kumalive.getName(), true, 1); + kumaliveNameRow.addEmptyCells(kumalive.getRubrics().size() - 1); + } + + ExcelRow userHeaderRow = sheet.initRow(); + userHeaderRow.addCell(messageService.getMessage("label.kumalive.report.last.name"), true); + userHeaderRow.addCell(messageService.getMessage("label.kumalive.report.first.name"), true); + userHeaderRow.addCell(messageService.getMessage("label.kumalive.report.login"), true); + for (Kumalive kumalive : kumalives) { // use border only on first cell in kumalive - boolean border = true; + boolean isFirstRubric = true; for (KumaliveRubric rubric : kumalive.getRubrics()) { - userHeaderRow.add(new ExcelCell(rubric.getName(), true, border ? 1 : 0)); - border = false; - userRowLength++; + userHeaderRow.addCell(rubric.getName(), true, isFirstRubric ? 1 : 0); + isFirstRubric = false; } } - // now that we know how long is the whole user row, we can build kumalive names row and add it first - ExcelCell[] kumaliveNameRow = new ExcelCell[userRowLength]; - for (Entry kumaliveNameEntry : kumaliveNamePosition.entrySet()) { - kumaliveNameRow[kumaliveNameEntry.getValue()] = new ExcelCell(kumaliveNameEntry.getKey(), true, 1); - } - rows.add(kumaliveNameRow); - rows.add(userHeaderRow.toArray(EMPTY_ROW)); - for (Entry>> learnerSummary : learnerSummaries.entrySet()) { - ExcelCell[] userRow = new ExcelCell[userRowLength]; + ExcelRow userRow = sheet.initRow(); User learner = learnerSummary.getKey(); - userRow[0] = new ExcelCell(learner.getFirstName(), false); - userRow[1] = new ExcelCell(learner.getLastName(), false); - userRow[2] = new ExcelCell(learner.getLogin(), false); + userRow.addCell(learner.getFirstName(), false); + userRow.addCell(learner.getLastName(), false); + userRow.addCell(learner.getLogin(), false); + for (Kumalive kumalive : kumalives) { Map learnerKumaliveSummary = learnerSummary.getValue().get(kumalive.getName()); - if (learnerKumaliveSummary == null) { - continue; - } - int position = kumaliveNamePosition.get(kumalive.getName()); boolean border = true; for (KumaliveRubric rubric : kumalive.getRubrics()) { - Double average = learnerKumaliveSummary.get(rubric.getRubricId()); - if (average != null) { - userRow[position] = new ExcelCell(average, false, border ? 1 : 0); - } + Double average = learnerKumaliveSummary == null ? null : learnerKumaliveSummary.get(rubric.getRubricId()); border = false; - position++; + if (average != null) { + userRow.addCell(average, false, border ? 1 : 0); + } else { + userRow.addEmptyCell(); + } } } - rows.add(userRow); } - return rows.toArray(new ExcelCell[][] {}); + return sheet; } @Override Index: lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/service/IMonitoringFullService.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/service/IMonitoringFullService.java (.../IMonitoringFullService.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/service/IMonitoringFullService.java (.../IMonitoringFullService.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -20,6 +20,7 @@ import org.lamsfoundation.lams.usermanagement.exception.UserAccessDeniedException; import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelSheet; /** * Contains methods intended for internal usage by lams_monitoring. @@ -325,7 +326,7 @@ /** * Exports the given email notification to Excel sheet */ - LinkedHashMap exportArchivedEmailNotification(Long emailNotificationUid); + List exportArchivedEmailNotification(Long emailNotificationUid); /** * Set a groups name Index: lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/service/MonitoringService.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/service/MonitoringService.java (.../MonitoringService.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/service/MonitoringService.java (.../MonitoringService.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -115,6 +115,8 @@ import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.NumberUtil; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelRow; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import org.lamsfoundation.lams.web.session.SessionManager; import org.lamsfoundation.lams.web.util.AttributeNames; import org.quartz.JobBuilder; @@ -206,8 +208,6 @@ private static final String FORCE_COMPLETE_STOP_MESSAGE_STOPPED_UNEXPECTEDLY = "force.complete.stop.message.stopped.unexpectedly"; - private static final ExcelCell[] EMPTY_ROW = new ExcelCell[1]; - // --------------------------------------------------------------------- // Inversion of Control Methods - Method injection // --------------------------------------------------------------------- @@ -1922,49 +1922,41 @@ } @Override - public LinkedHashMap exportArchivedEmailNotification(Long emailNotificationUid) { + public List exportArchivedEmailNotification(Long emailNotificationUid) { EmailNotificationArchive notification = (EmailNotificationArchive) baseDAO.find(EmailNotificationArchive.class, emailNotificationUid); - LinkedHashMap sheets = new LinkedHashMap<>(); - List rows = new LinkedList<>(); - ExcelCell[] row = new ExcelCell[3]; - row[0] = new ExcelCell(messageService.getMessage("email.notifications.archived.messages.list.sent.date"), true); - row[1] = new ExcelCell( - messageService.getMessage("email.notifications.scheduled.messages.list.notify.sudents.that"), true); - row[2] = new ExcelCell(messageService.getMessage("email.notifications.scheduled.messages.list.email.body"), - true); - rows.add(row); + List sheets = new LinkedList(); + ExcelSheet sheet = new ExcelSheet(messageService.getMessage("email.notifications.archived.export.sheet.name")); + sheets.add(sheet); - row = new ExcelCell[3]; - row[0] = new ExcelCell(FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(notification.getSentOn()), - false); - row[1] = new ExcelCell( + ExcelRow row = sheet.initRow(); + row.addCell(messageService.getMessage("email.notifications.archived.messages.list.sent.date"), true); + row.addCell(messageService.getMessage("email.notifications.scheduled.messages.list.notify.sudents.that"), true); + row.addCell(messageService.getMessage("email.notifications.scheduled.messages.list.email.body"), true); + + row = sheet.initRow(); + row.addCell(FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(notification.getSentOn()), false); + row.addCell( messageService.getMessage("email.notifications.user.search.property." + notification.getSearchType()), false); - row[2] = new ExcelCell(notification.getBody(), false); - rows.add(row); - rows.add(EMPTY_ROW); + row.addCell(notification.getBody(), false); + sheet.addEmptyRow(); - row = new ExcelCell[2]; - row[0] = new ExcelCell(messageService.getMessage("email.notifications.archived.messages.list.sent.count"), - true); - row[1] = new ExcelCell(notification.getRecipients().size() + " " + row = sheet.initRow(); + row.addCell(messageService.getMessage("email.notifications.archived.messages.list.sent.count"), true); + row.addCell(notification.getRecipients().size() + " " + messageService.getMessage("email.notifications.archived.messages.list.learners"), false); - rows.add(row); // get all recipient objects, sorted by name List recipients = getArchivedEmailNotificationRecipients(emailNotificationUid, null, null); for (User recipient : recipients) { - row = new ExcelCell[1]; + row = sheet.initRow(); String recipientName = new StringBuilder(recipient.getLastName()).append(", ") .append(recipient.getFirstName()).append(" [").append(recipient.getLogin()).append("]").toString(); - row[0] = new ExcelCell(recipientName, false); - rows.add(row); + row.addCell(recipientName, false); } - sheets.put(messageService.getMessage("email.notifications.archived.export.sheet.name"), - rows.toArray(new ExcelCell[][] {})); return sheets; } Index: lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/web/EmailNotificationsController.java =================================================================== diff -u -rcbf95a868252401757c61327b3d9a383119ff9b5 -rd471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495 --- lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/web/EmailNotificationsController.java (.../EmailNotificationsController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) +++ lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/web/EmailNotificationsController.java (.../EmailNotificationsController.java) (revision d471fb4d4ad60b6568b9f3cb4ec9cd82c6fbe495) @@ -67,6 +67,7 @@ import org.lamsfoundation.lams.util.FileUtil; import org.lamsfoundation.lams.util.WebUtil; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import org.lamsfoundation.lams.util.excel.ExcelUtil; import org.lamsfoundation.lams.web.session.SessionManager; import org.lamsfoundation.lams.web.util.AttributeNames; @@ -455,16 +456,15 @@ } } - LinkedHashMap dataToExport = monitoringService - .exportArchivedEmailNotification(emailNotificationUid); + List sheets = monitoringService.exportArchivedEmailNotification(emailNotificationUid); String fileName = "email_notification_" + FileUtil.EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT.format(notification.getSentOn()) + ".xlsx"; fileName = FileUtil.encodeFilenameForDownload(request, fileName); response.setContentType("application/x-download"); response.setHeader("Content-Disposition", "attachment;filename=" + fileName); - ExcelUtil.createExcel(response.getOutputStream(), dataToExport, + ExcelUtil.createExcel(response.getOutputStream(), sheets, monitoringService.getMessageService().getMessage("export.dateheader"), false); }