Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelCell.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelCell.java (.../ExcelCell.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelCell.java (.../ExcelCell.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -46,7 +46,7 @@ public final static int CELL_FORMAT_PERCENTAGE = 3; private Object cellValue; - private int cellFormat = ExcelCell.CELL_FORMAT_DEFAULT;//default format is 0 + private int dataFormat = ExcelCell.CELL_FORMAT_DEFAULT;//default format is 0 private Boolean isBold = false; private IndexedColors color; private int borderStyle = 0; @@ -90,12 +90,12 @@ this.cellValue = cellValue; } - public int getCellFormat() { - return cellFormat; + public int getDataFormat() { + return dataFormat; } - public void setCellFormat(int cellFormat) { - this.cellFormat = cellFormat; + public void setDataFormat(int cellFormat) { + this.dataFormat = cellFormat; } public Boolean isBold() { Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java =================================================================== diff -u -r082bf8f436f01910fa9188202cf1d54b7208ee73 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java (.../ExcelRow.java) (revision 082bf8f436f01910fa9188202cf1d54b7208ee73) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelRow.java (.../ExcelRow.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -13,6 +13,16 @@ public class ExcelRow { private List cells = new ArrayList<>(); + /** + * Return cell value at the specified position in cells list. + * + * @param index + * @return + */ + public Object getCell(int index) { + return cells.get(index).getCellValue(); + } + public ExcelCell addCell(Object cellValue) { ExcelCell cell = new ExcelCell(cellValue); cells.add(cell); @@ -21,7 +31,7 @@ public ExcelCell addPercentageCell(Object cellValue) { ExcelCell cell = new ExcelCell(cellValue); - cell.setCellFormat(ExcelCell.CELL_FORMAT_PERCENTAGE); + cell.setDataFormat(ExcelCell.CELL_FORMAT_PERCENTAGE); cells.add(cell); return cell; } @@ -47,6 +57,19 @@ cells.add(cell); } + /** + * Add one empty cell + */ + public void addEmptyCell() { + ExcelCell cell = new ExcelCell(""); + cells.add(cell); + } + + /** + * Add specified number of empty cell. + * + * @param numberEmptyCells + */ public void addEmptyCells(int numberEmptyCells) { for (int i = 0; i < numberEmptyCells; i++) { ExcelCell cell = new ExcelCell(""); Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelSheet.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelSheet.java (.../ExcelSheet.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelSheet.java (.../ExcelSheet.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -18,6 +18,29 @@ this.sheetName = sheetName; } + /** + * Add empty row. + */ + public void addEmptyRow() { + rows.add(new ExcelRow()); + } + + /** + * Return row at the specified position in rows list. + * + * @param index + * @return + */ + public ExcelRow getRow(int index) { + return rows.get(index); + } + + public ExcelRow initRow() { + ExcelRow row = new ExcelRow(); + rows.add(row); + return row; + } + public void addRow(ExcelRow row) { rows.add(row); } Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java =================================================================== diff -u -r082bf8f436f01910fa9188202cf1d54b7208ee73 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java (.../ExcelUtil.java) (revision 082bf8f436f01910fa9188202cf1d54b7208ee73) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java (.../ExcelUtil.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -304,7 +304,7 @@ //cast excelCell's value if (excelCellValue != null) { - if (excelCell.getCellFormat() == ExcelCell.CELL_FORMAT_TIME && excelCellValue instanceof Date) { + if (excelCell.getDataFormat() == ExcelCell.CELL_FORMAT_TIME && excelCellValue instanceof Date) { cell.setCellValue((Date) excelCellValue); } else if (excelCellValue instanceof Date) { @@ -395,13 +395,13 @@ && (excelCellValue instanceof java.lang.Float || excelCellValue instanceof java.lang.Double)) { CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, floatFormat); - } else if (excelCell.getCellFormat() == ExcelCell.CELL_FORMAT_DATE) { + } else if (excelCell.getDataFormat() == ExcelCell.CELL_FORMAT_DATE) { CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, dateFormat); - } else if (excelCell.getCellFormat() == ExcelCell.CELL_FORMAT_TIME) { + } else if (excelCell.getDataFormat() == ExcelCell.CELL_FORMAT_TIME) { CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, timeFormat); } - if (excelCell.getCellFormat() == ExcelCell.CELL_FORMAT_PERCENTAGE) { + if (excelCell.getDataFormat() == ExcelCell.CELL_FORMAT_PERCENTAGE) { CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, percentageFormat); } Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/util/GradebookUtil.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/util/GradebookUtil.java (.../GradebookUtil.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/util/GradebookUtil.java (.../GradebookUtil.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -200,7 +200,7 @@ } ExcelCell userMarkCell = new ExcelCell(convertedMark, isBold, borderStyle); - userMarkCell.setCellFormat(ExcelCell.CELL_FORMAT_PERCENTAGE); + userMarkCell.setDataFormat(ExcelCell.CELL_FORMAT_PERCENTAGE); return userMarkCell; } Index: lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/controller/GradebookMonitoringController.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/controller/GradebookMonitoringController.java (.../GradebookMonitoringController.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_gradebook/src/java/org/lamsfoundation/lams/gradebook/web/controller/GradebookMonitoringController.java (.../GradebookMonitoringController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -301,7 +301,6 @@ response.addCookie(fileDownloadTokenCookie); ExcelUtil.createExcel(out, dataToExport, gradebookService.getMessage("gradebook.export.dateheader"), true); - } /** @@ -339,7 +338,6 @@ // Code to generate file and write file contents to response ServletOutputStream out = response.getOutputStream(); ExcelUtil.createExcel(out, dataToExport, gradebookService.getMessage("gradebook.export.dateheader"), true); - } /** @@ -381,7 +379,6 @@ // Code to generate file and write file contents to response ServletOutputStream out = response.getOutputStream(); ExcelUtil.createExcel(out, dataToExport, null, false); - } /** Index: lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/KumaliveController.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/KumaliveController.java (.../KumaliveController.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_learning/src/java/org/lamsfoundation/lams/learning/kumalive/KumaliveController.java (.../KumaliveController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -29,9 +29,11 @@ import org.lamsfoundation.lams.web.session.SessionManager; import org.lamsfoundation.lams.web.util.AttributeNames; import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.http.HttpStatus; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; +import org.springframework.web.bind.annotation.ResponseStatus; import org.springframework.web.context.WebApplicationContext; import org.springframework.web.context.support.WebApplicationContextUtils; @@ -233,7 +235,7 @@ } @RequestMapping("/exportKumalives") - @ResponseBody + @ResponseStatus(HttpStatus.OK) public void exportKumalives(HttpServletRequest request, HttpServletResponse response) throws IOException { UserDTO userDTO = getUserDTO(); Integer currentUserId = userDTO.getUserID(); Index: lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/web/EmailNotificationsController.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/web/EmailNotificationsController.java (.../EmailNotificationsController.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_monitoring/src/java/org/lamsfoundation/lams/monitoring/web/EmailNotificationsController.java (.../EmailNotificationsController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -81,10 +81,12 @@ import org.quartz.TriggerKey; import org.quartz.impl.matchers.GroupMatcher; import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.http.HttpStatus; import org.springframework.stereotype.Controller; import org.springframework.util.Assert; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; +import org.springframework.web.bind.annotation.ResponseStatus; import com.fasterxml.jackson.databind.node.ArrayNode; import com.fasterxml.jackson.databind.node.JsonNodeFactory; @@ -427,7 +429,8 @@ * Exports the given archived email notification to excel. */ @RequestMapping("exportArchivedNotification") - public String exportArchivedNotification(HttpServletRequest request, HttpServletResponse response) + @ResponseStatus(HttpStatus.OK) + public void exportArchivedNotification(HttpServletRequest request, HttpServletResponse response) throws IOException { Long emailNotificationUid = WebUtil.readLongParam(request, "emailNotificationUid"); @@ -442,13 +445,13 @@ if (!securityService.isLessonMonitor(lessonId, getCurrentUser().getUserID(), "export archived lesson email notification", false)) { response.sendError(HttpServletResponse.SC_FORBIDDEN, "The user is not a monitor in the lesson"); - return null; + return; } } else { if (!securityService.isGroupMonitor(organisationId, getCurrentUser().getUserID(), "export archived course email notification", false)) { response.sendError(HttpServletResponse.SC_FORBIDDEN, "The user is not a monitor in the organisation"); - return null; + return; } } @@ -463,8 +466,6 @@ ExcelUtil.createExcel(response.getOutputStream(), dataToExport, monitoringService.getMessageService().getMessage("export.dateheader"), false); - return null; - } /** Index: lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/service/AssessmentServiceImpl.java =================================================================== diff -u -rfcaa7d0693410ac912b7f7c75ab995803b8dbbab -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/service/AssessmentServiceImpl.java (.../AssessmentServiceImpl.java) (revision fcaa7d0693410ac912b7f7c75ab995803b8dbbab) +++ lams_tool_assessment/src/java/org/lamsfoundation/lams/tool/assessment/service/AssessmentServiceImpl.java (.../AssessmentServiceImpl.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -1433,7 +1433,6 @@ public List exportSummary(Assessment assessment, List sessionDtos, boolean showUserNames) { List sheets = new LinkedList(); - final ExcelRow EMPTY_ROW = new ExcelRow(); // -------------- First tab: Summary ---------------------------------------------------- if (showUserNames) { @@ -1444,11 +1443,10 @@ for (SessionDTO sessionDTO : sessionDtos) { Long sessionId = sessionDTO.getSessionId(); - summarySheet.addRow(EMPTY_ROW); + summarySheet.addEmptyRow(); - ExcelRow sessionTitleRow = new ExcelRow(); + ExcelRow sessionTitleRow = summarySheet.initRow(); sessionTitleRow.addCell(sessionDTO.getSessionName(), true); - summarySheet.addRow(sessionTitleRow); List userDtos = new ArrayList<>(); // in case of UseSelectLeaderToolOuput - display only one user @@ -1500,58 +1498,51 @@ } // Mark Summary Min, Max + Grouped Percentages - summarySheet.addRow(EMPTY_ROW); - ExcelRow minMaxRow = new ExcelRow(); + summarySheet.addEmptyRow(); + ExcelRow minMaxRow = summarySheet.initRow(); minMaxRow.addCell(getMessage("label.number.learners"), true); minMaxRow.addCell(totalNumEntries); - summarySheet.addRow(minMaxRow); - minMaxRow = new ExcelRow(); + + minMaxRow = summarySheet.initRow(); minMaxRow.addCell(getMessage("label.lowest.mark"), true); minMaxRow.addCell((double) minGrade); - summarySheet.addRow(minMaxRow); - minMaxRow = new ExcelRow(); + + minMaxRow = summarySheet.initRow(); minMaxRow.addCell(getMessage("label.highest.mark"), true); minMaxRow.addCell((double) maxGrade); - summarySheet.addRow(minMaxRow); - - summarySheet.addRow(EMPTY_ROW); - ExcelRow binSummaryRow = new ExcelRow(); + summarySheet.addEmptyRow(); + + ExcelRow binSummaryRow = summarySheet.initRow(); binSummaryRow.addCell(getMessage("label.authoring.basic.list.header.mark"), true, ExcelCell.BORDER_STYLE_BOTTOM_THIN); binSummaryRow.addCell(getMessage("label.number.learners"), true, ExcelCell.BORDER_STYLE_BOTTOM_THIN); binSummaryRow.addCell(getMessage("label.percentage"), true, ExcelCell.BORDER_STYLE_BOTTOM_THIN); - summarySheet.addRow(binSummaryRow); float totalNumEntriesAsFloat = totalNumEntries; for (Map.Entry entry : markSummary.entrySet()) { - binSummaryRow = new ExcelRow(); + binSummaryRow = summarySheet.initRow(); binSummaryRow.addCell(entry.getKey()); binSummaryRow.addCell(entry.getValue()); - binSummaryRow.addCell(Math.round(entry.getValue() / totalNumEntriesAsFloat * 100), false); - summarySheet.addRow(binSummaryRow); + binSummaryRow.addCell(Math.round(entry.getValue() / totalNumEntriesAsFloat * 100)); } - summarySheet.addRow(EMPTY_ROW); - summarySheet.addRow(EMPTY_ROW); - - ArrayList summaryTabLearnerList = new ArrayList<>(); + summarySheet.addEmptyRow(); + summarySheet.addEmptyRow(); - ExcelRow summaryTitleRow = new ExcelRow(); + ExcelRow summaryTitleRow = summarySheet.initRow(); summaryTitleRow.addCell(getMessage("label.export.user.id"), true, ExcelCell.BORDER_STYLE_BOTTOM_THIN); summaryTitleRow.addCell(getMessage("label.monitoring.summary.user.name"), true, ExcelCell.BORDER_STYLE_BOTTOM_THIN); summaryTitleRow.addCell(getMessage("label.monitoring.summary.total"), true, ExcelCell.BORDER_STYLE_BOTTOM_THIN); - summarySheet.addRow(summaryTitleRow); for (AssessmentUserDTO userDto : userDtos) { - ExcelRow userResultRow = new ExcelRow(); + ExcelRow userResultRow = summarySheet.initRow(); userResultRow.addCell(userDto.getLogin()); userResultRow.addCell(userDto.getFirstName() + " " + userDto.getLastName()); userResultRow.addCell(userDto.getGrade()); - summarySheet.addRow(userResultRow); } - summarySheet.addRow(EMPTY_ROW); + summarySheet.addEmptyRow(); } } } @@ -1562,10 +1553,9 @@ sheets.add(questionSummarySheet); // Create the question summary - ExcelRow summaryTitleRow = new ExcelRow(); + ExcelRow summaryTitleRow = questionSummarySheet.initRow(); summaryTitleRow.addCell(getMessage("label.export.question.summary"), true); - questionSummarySheet.addRow(summaryTitleRow); - questionSummarySheet.addRow(EMPTY_ROW); + questionSummarySheet.addEmptyRow(); Map questionSummaries = getQuestionSummaryForExport(assessment); @@ -1602,10 +1592,9 @@ int questionNumber = 1; for (AssessmentQuestion question : questions) { - ExcelRow questionTitle = new ExcelRow(); + ExcelRow questionTitle = questionSummarySheet.initRow(); questionTitle.addCell( getMessage("label.monitoring.question.summary.question") + " " + questionNumber++, true); - questionSummarySheet.addRow(questionTitle); // set up the summary table data for the top of the question area. boolean doSummaryTable = question.getType() == AssessmentConstants.QUESTION_TYPE_MULTIPLE_CHOICE @@ -1670,8 +1659,7 @@ for (List resultList : allResultsForQuestion) { for (AssessmentQuestionResult questionResult : resultList) { ExcelRow userResultRow = new ExcelRow(); - userResultRow.addCell(questionResult.getAssessmentQuestion().getTitle(), - false); + userResultRow.addCell(questionResult.getAssessmentQuestion().getTitle()); userResultRow.addCell( getQuestionTypeLanguageLabel(questionResult.getAssessmentQuestion().getType())); userResultRow.addCell( @@ -1688,9 +1676,9 @@ //date and time ExcelCell dateCell = userResultRow.addCell(questionResult.getFinishDate()); - dateCell.setCellFormat(ExcelCell.CELL_FORMAT_DATE); + dateCell.setDataFormat(ExcelCell.CELL_FORMAT_DATE); ExcelCell timeCell = userResultRow.addCell(questionResult.getFinishDate()); - timeCell.setCellFormat(ExcelCell.CELL_FORMAT_TIME); + timeCell.setDataFormat(ExcelCell.CELL_FORMAT_TIME); //answer if (question.getType() == AssessmentConstants.QUESTION_TYPE_MARK_HEDGING) { @@ -1740,25 +1728,21 @@ if (doSummaryTable) { questionSummarySheet .addRow(outputSummaryTable(question, summaryOfAnswers, summaryNACount, trueKey, falseKey)); - questionSummarySheet.addRow(EMPTY_ROW); + questionSummarySheet.addEmptyRow(); } questionSummarySheet.getRows().addAll(questionSummaryTabTemp); // Calculating the averages - ExcelRow averageRow = new ExcelRow(); - averageRow.addEmptyCells(7); - if (showUserNames) { - averageRow.addEmptyCells(1); - } + ExcelRow averageRow = questionSummarySheet.initRow(); + averageRow.addEmptyCells(showUserNames ? 8 : 7); averageRow.addCell(getMessage("label.export.average"), true); if (timeTakenTotal > 0) { averageRow.addCell(Long.valueOf(timeTakenTotal / timeTakenCount)); } Float averageMark = markTotal > 0 ? Float.valueOf(markTotal / markCount) : 0.0F; averageRow.addCell(averageMark); - questionSummarySheet.addRow(averageRow); - questionSummarySheet.addRow(EMPTY_ROW); + questionSummarySheet.addEmptyRow(); } } @@ -1769,11 +1753,10 @@ sheets.add(userSummarySheet); // Create the question summary - ExcelRow userSummaryTitle = new ExcelRow(); + ExcelRow userSummaryTitle = userSummarySheet.initRow(); userSummaryTitle.addCell(getMessage("label.export.user.summary"), true); - userSummarySheet.addRow(userSummaryTitle); - ExcelRow summaryRowTitle = new ExcelRow(); + ExcelRow summaryRowTitle = userSummarySheet.initRow(); summaryRowTitle.addCell(getMessage("label.monitoring.question.summary.question"), true, ExcelCell.BORDER_STYLE_BOTTOM_THIN); summaryRowTitle.addCell(getMessage("label.authoring.basic.list.header.type"), true, @@ -1784,7 +1767,7 @@ ExcelCell.BORDER_STYLE_BOTTOM_THIN); summaryRowTitle.addCell(getMessage("label.monitoring.question.summary.average.mark"), true, ExcelCell.BORDER_STYLE_BOTTOM_THIN); - userSummarySheet.addRow(summaryRowTitle); + Float totalGradesPossible = 0F; Float totalAverage = 0F; if (assessment.getQuestionReferences() != null) { @@ -1821,25 +1804,22 @@ int maxGrade = questionReference.getDefaultGrade(); totalGradesPossible += maxGrade; - ExcelRow questCellRow = new ExcelRow(); + ExcelRow questCellRow = userSummarySheet.initRow(); questCellRow.addCell(title); questCellRow.addCell(questionType); questCellRow.addCell(penaltyFactor); questCellRow.addCell(maxGrade); questCellRow.addCell(averageMark); - - userSummarySheet.addRow(questCellRow); } if (totalGradesPossible.floatValue() > 0) { - ExcelRow totalCellRow = new ExcelRow(); + ExcelRow totalCellRow = userSummarySheet.initRow(); totalCellRow.addEmptyCells(2); totalCellRow.addCell(getMessage("label.monitoring.summary.total"), true); totalCellRow.addCell(totalGradesPossible); totalCellRow.addCell(totalAverage); - userSummarySheet.addRow(totalCellRow); } - userSummarySheet.addRow(EMPTY_ROW); + userSummarySheet.addEmptyRow(); } if (sessionDtos != null) { @@ -1851,23 +1831,17 @@ } for (SessionDTO sessionDTO : sessionDtos) { + userSummarySheet.addEmptyRow(); - userSummarySheet.addRow(EMPTY_ROW); - - ExcelRow sessionTitle = new ExcelRow(); + ExcelRow sessionTitle = userSummarySheet.initRow(); sessionTitle.addCell(sessionDTO.getSessionName(), true); - userSummarySheet.addRow(sessionTitle); AssessmentSession assessmentSession = getSessionBySessionId(sessionDTO.getSessionId()); - Set assessmentUsers = assessmentSession.getAssessmentUsers(); - if (assessmentUsers != null) { - for (AssessmentUser assessmentUser : assessmentUsers) { - if (showUserNames) { - ExcelRow userTitleRow = new ExcelRow(); + ExcelRow userTitleRow = userSummarySheet.initRow(); userTitleRow.addCell(getMessage("label.export.user.id"), true); userTitleRow.addCell(getMessage("label.monitoring.user.summary.user.name"), true); @@ -1876,24 +1850,23 @@ true); userTitleRow.addCell(getMessage("label.authoring.basic.option.answer"), true); userTitleRow.addCell(getMessage("label.export.mark"), true); - userSummarySheet.addRow(userTitleRow); + } else { - ExcelRow userTitleRow = new ExcelRow(); + ExcelRow userTitleRow = userSummarySheet.initRow(); userTitleRow.addCell(getMessage("label.export.user.id"), true); userTitleRow.addCell(getMessage("label.export.date.attempted"), true); userTitleRow.addCell(getMessage("label.monitoring.question.summary.question"), true); userTitleRow.addCell(getMessage("label.authoring.basic.option.answer"), true); userTitleRow.addCell(getMessage("label.export.mark"), true); - userSummarySheet.addRow(userTitleRow); } AssessmentResult assessmentResult = userUidToResultMap.get(assessmentUser.getUid()); if (assessmentResult != null) { Set questionResults = assessmentResult.getQuestionResults(); if (questionResults != null) { for (AssessmentQuestionResult questionResult : questionResults) { - ExcelRow userResultRow = new ExcelRow(); + ExcelRow userResultRow = userSummarySheet.initRow(); if (showUserNames) { userResultRow.addCell(assessmentUser.getLoginName()); userResultRow.addCell(assessmentUser.getFullName()); @@ -1905,17 +1878,14 @@ userResultRow.addCell( AssessmentEscapeUtils.printResponsesForExcelExport(questionResult)); userResultRow.addCell(questionResult.getMark()); - userSummarySheet.addRow(userResultRow); } } - ExcelRow userTotalRow = new ExcelRow(); + ExcelRow userTotalRow = userSummarySheet.initRow(); userTotalRow.addEmptyCells(showUserNames ? 4 : 3); userTotalRow.addCell(getMessage("label.monitoring.summary.total"), true); userTotalRow.addCell(assessmentResult.getGrade()); - - userSummarySheet.addRow(userTotalRow); - userSummarySheet.addRow(EMPTY_ROW); + userSummarySheet.addEmptyRow(); } } } @@ -2013,11 +1983,6 @@ return summaryNACount; } -// private String valueAsPercentage(Integer value, int total) { -// Double percentage = (double) value / total * 100; -// return NumberUtil.formatLocalisedNumber(percentage, (Locale) null, 2); -// } - private ExcelRow outputSummaryTable(AssessmentQuestion question, Map summaryOfAnswers, Integer summaryNACount, Long trueKey, Long falseKey) { ExcelRow summaryTableRow = new ExcelRow(); Index: lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/service/McService.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/service/McService.java (.../McService.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_tool_lamc/src/java/org/lamsfoundation/lams/tool/mc/service/McService.java (.../McService.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -51,6 +51,7 @@ import org.apache.poi.ss.usermodel.FillPatternType; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; +import org.apache.poi.ss.util.CellUtil; import org.lamsfoundation.lams.confidencelevel.ConfidenceLevelDTO; import org.lamsfoundation.lams.contentrepository.client.IToolContentHandler; import org.lamsfoundation.lams.learningdesign.service.ExportToolContentException; @@ -917,7 +918,6 @@ @Override public byte[] prepareSessionDataSpreadsheet(McContent mcContent) throws IOException { - Set questions = mcContent.getMcQueContents(); int maxOptionsInQuestion = 0; for (McQueContent question : questions) { @@ -942,6 +942,8 @@ whiteFont.setColor(IndexedColors.WHITE.getIndex()); whiteFont.setFontName(ExcelUtil.DEFAULT_FONT_NAME); greenColor.setFont(whiteFont); + + short percentageFormat = wb.createDataFormat().getFormat("0%"); // ======================================================= Report by question IRA page // ======================================= @@ -976,15 +978,17 @@ for (McOptsContent option : question.getMcOptionsContents()) { int optionAttemptCount = mcUsrAttemptDAO.getAttemptsCountPerOption(option.getUid()); cell = row.createCell(count++); - int percentage = (optionAttemptCount * 100) / totalNumberOfUsers; - cell.setCellValue(percentage + "%"); + int percentage = optionAttemptCount / totalNumberOfUsers; + cell.setCellValue(percentage); + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, percentageFormat); totalPercentage += percentage; if (option.isCorrectOption()) { cell.setCellStyle(greenColor); } } cell = row.createCell(maxOptionsInQuestion + 1); - cell.setCellValue((100 - totalPercentage) + "%"); + cell.setCellValue((1 - totalPercentage)); + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, percentageFormat); } rowCount++; @@ -1076,10 +1080,11 @@ cell = row.createCell(count++); cell.setCellValue(new Long(userMark.getTotalMark())); - int totalPercents = (numberOfCorrectlyAnsweredByUser * 100) / questions.size(); + int totalPercents = numberOfCorrectlyAnsweredByUser / questions.size(); totalPercentList.add(totalPercents); cell = row.createCell(count++); - cell.setCellValue(totalPercents + "%"); + cell.setCellValue(totalPercents); + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, percentageFormat); } rowCount++; @@ -1092,7 +1097,8 @@ cell.setCellValue(messageService.getMessage("label.ave")); for (int numberOfCorrectAnswers : numberOfCorrectAnswersPerQuestion) { cell = row.createCell(count++); - cell.setCellValue(((numberOfCorrectAnswers * 100) / totalPercentList.size()) + "%"); + cell.setCellValue(numberOfCorrectAnswers / totalPercentList.size()); + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, percentageFormat); } // class mean @@ -1108,7 +1114,8 @@ if (totalPercents.length != 0) { int classMean = sum / totalPercents.length; cell = row.createCell(questions.size() + 3); - cell.setCellValue(classMean + "%"); + cell.setCellValue(classMean); + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, percentageFormat); } // median @@ -1124,7 +1131,8 @@ median = (int) ((totalPercents[middle - 1] + totalPercents[middle]) / 2.0); } cell = row.createCell(questions.size() + 3); - cell.setCellValue(median + "%"); + cell.setCellValue(median); + CellUtil.setCellStyleProperty(cell, CellUtil.DATA_FORMAT, percentageFormat); } row = sheet.createRow(rowCount++); @@ -1218,7 +1226,6 @@ @Override public void copyToolContent(Long fromContentId, Long toContentId) { - if (fromContentId == null) { logger.warn("fromContentId is null."); long defaultContentId = getToolDefaultContentIdBySignature(McAppConstants.TOOL_SIGNATURE); Index: lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/web/controller/MonitoringController.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/web/controller/MonitoringController.java (.../MonitoringController.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/web/controller/MonitoringController.java (.../MonitoringController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -54,10 +54,12 @@ import org.lamsfoundation.lams.web.util.SessionMap; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; +import org.springframework.http.HttpStatus; import org.springframework.stereotype.Controller; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; +import org.springframework.web.bind.annotation.ResponseStatus; import org.springframework.web.util.HtmlUtils; import com.fasterxml.jackson.databind.JsonNode; @@ -557,16 +559,16 @@ * @throws IOException */ @RequestMapping("/exportTeamReport") - @ResponseBody - public String exportTeamReport(HttpServletRequest request, + @ResponseStatus(HttpStatus.OK) + public void exportTeamReport(HttpServletRequest request, HttpServletResponse response) throws ServletException { Long toolContentId = WebUtil.readLongParam(request, PeerreviewConstants.ATTR_TOOL_CONTENT_ID); Peerreview peerreview = service.getPeerreviewByContentId(toolContentId); if (peerreview == null) { log.warn("Did not find Peer Review with toolContentId: " + toolContentId + " export content"); - return null; + return; } String fileName = peerreview.getTitle().replaceAll(" ", "_") + ".xlsx"; @@ -596,8 +598,6 @@ log.error("exportTeamReportExcelSpreadsheet i/o error occured: " + e.getMessage(), e); throw new ServletException(e); } - - return null; } @RequestMapping("/manageUsers") Index: lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/service/IScratchieService.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/service/IScratchieService.java (.../IScratchieService.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/service/IScratchieService.java (.../IScratchieService.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -46,6 +46,7 @@ import org.lamsfoundation.lams.tool.scratchie.model.ScratchieUser; import org.lamsfoundation.lams.tool.service.ICommonToolService; import org.lamsfoundation.lams.util.excel.ExcelCell; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import org.quartz.SchedulerException; /** @@ -307,7 +308,7 @@ * @param scratchie * @return */ - LinkedHashMap exportExcel(Long contentId); + List exportExcel(Long contentId); /** * Create refection entry into notebook tool. Index: lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/service/ScratchieServiceImpl.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/service/ScratchieServiceImpl.java (.../ScratchieServiceImpl.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/service/ScratchieServiceImpl.java (.../ScratchieServiceImpl.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -100,6 +100,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 com.fasterxml.jackson.databind.node.ArrayNode; import com.fasterxml.jackson.databind.node.ObjectNode; @@ -111,8 +113,6 @@ implements IScratchieService, ToolContentManager, ToolSessionManager, ToolRestManager { private static Logger log = Logger.getLogger(ScratchieServiceImpl.class.getName()); - private static final ExcelCell[] EMPTY_ROW = new ExcelCell[4]; - private ScratchieDAO scratchieDao; private ScratchieItemDAO scratchieItemDao; @@ -1052,47 +1052,46 @@ } @Override - public LinkedHashMap exportExcel(Long contentId) { + public List exportExcel(Long contentId) { Scratchie scratchie = scratchieDao.getByContentId(contentId); Collection items = new TreeSet<>(new ScratchieItemComparator()); items.addAll(scratchie.getScratchieItems()); int numberOfItems = items.size(); - LinkedHashMap dataToExport = new LinkedHashMap<>(); + List sheets = new LinkedList(); // ======================================================= For Immediate Analysis page // ======================================= + ExcelSheet immediateAnalysisSheet = new ExcelSheet(getMessage("label.for.immediate.analysis")); + sheets.add(immediateAnalysisSheet); - List rowList = new LinkedList<>(); + ExcelRow row = immediateAnalysisSheet.initRow(); + row.addCell(getMessage("label.quick.analysis"), true); + + row = immediateAnalysisSheet.initRow(); + row.addEmptyCell(); + row.addCell(getMessage("label.in.table.below.we.show")); + immediateAnalysisSheet.addEmptyRow(); - ExcelCell[] row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.quick.analysis"), true); - rowList.add(row); - row = new ExcelCell[2]; - row[1] = new ExcelCell(getMessage("label.in.table.below.we.show"), false); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + row = immediateAnalysisSheet.initRow(); + row.addEmptyCells(2); + row.addCell(getMessage("label.questions")); - row = new ExcelCell[3]; - row[2] = new ExcelCell(getMessage("label.questions"), false); - rowList.add(row); - - row = new ExcelCell[numberOfItems + 4]; - int columnCount = 1; - row[columnCount++] = new ExcelCell(getMessage("label.teams"), true); + row = immediateAnalysisSheet.initRow(); + row.addEmptyCell(); + row.addCell(getMessage("label.teams"), true); for (int itemCount = 0; itemCount < numberOfItems; itemCount++) { - row[columnCount++] = new ExcelCell("Q" + (itemCount + 1), true); + row.addCell("Q" + (itemCount + 1), true); } - row[columnCount++] = new ExcelCell(getMessage("label.total"), true); - row[columnCount++] = new ExcelCell(getMessage("label.total") + " %", true); - rowList.add(row); + row.addCell(getMessage("label.total"), true); + row.addCell(getMessage("label.total") + " %", true); List summaryByTeam = getSummaryByTeam(scratchie, items); for (GroupSummary summary : summaryByTeam) { - row = new ExcelCell[numberOfItems + 4]; - columnCount = 1; - row[columnCount++] = new ExcelCell(summary.getSessionName(), true); + row = immediateAnalysisSheet.initRow(); + row.addEmptyCell(); + row.addCell(summary.getSessionName(), true); int numberOfFirstChoiceEvents = 0; for (ScratchieItemDTO itemDto : summary.getItemDtos()) { @@ -1111,43 +1110,36 @@ isFirstChoice = getMessage("label.incorrect"); color = IndexedColors.RED; } - row[columnCount++] = new ExcelCell(isFirstChoice, color); + row.addCell(isFirstChoice, color); } - row[columnCount++] = new ExcelCell(new Integer(numberOfFirstChoiceEvents), false); - int percentage = (numberOfItems == 0) ? 0 : (100 * numberOfFirstChoiceEvents) / numberOfItems; - row[columnCount++] = new ExcelCell(percentage + "%", false); - rowList.add(row); + row.addCell(new Integer(numberOfFirstChoiceEvents)); + double percentage = (numberOfItems == 0) ? 0 : (double) numberOfFirstChoiceEvents / numberOfItems; + row.addPercentageCell(percentage); } - ExcelCell[][] firstPageData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("label.for.immediate.analysis"), firstPageData); - // ======================================================= For Report by Team TRA page // ======================================= + ExcelSheet reportByTeamSheet = new ExcelSheet(getMessage("label.report.by.team.tra")); + sheets.add(reportByTeamSheet); - rowList = new LinkedList<>(); + row = reportByTeamSheet.initRow(); + row.addCell(getMessage("label.quick.analysis"), true); + + row = reportByTeamSheet.initRow(); + row.addEmptyCell(); + row.addCell(getMessage("label.table.below.shows.which.answer.teams.selected.first.try")); + reportByTeamSheet.addEmptyRow(); - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.quick.analysis"), true); - rowList.add(row); - row = new ExcelCell[2]; - row[1] = new ExcelCell(getMessage("label.table.below.shows.which.answer.teams.selected.first.try"), false); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); - - row = new ExcelCell[numberOfItems + 3]; - columnCount = 1; + row = reportByTeamSheet.initRow(); + row.addEmptyCell(); for (int itemCount = 0; itemCount < numberOfItems; itemCount++) { - row[columnCount++] = new ExcelCell(getMessage("label.authoring.basic.instruction") + " " + (itemCount + 1), - false); + row.addCell(getMessage("label.authoring.basic.instruction") + " " + (itemCount + 1)); } - row[columnCount++] = new ExcelCell(getMessage("label.total"), false); - row[columnCount++] = new ExcelCell(getMessage("label.total") + " %", false); - rowList.add(row); + row.addCell(getMessage("label.total")); + row.addCell(getMessage("label.total") + " %"); - row = new ExcelCell[numberOfItems + 1]; - columnCount = 0; - row[columnCount++] = new ExcelCell(getMessage("label.correct.answer"), false); + row = reportByTeamSheet.initRow(); + row.addCell(getMessage("label.correct.answer")); for (ScratchieItem item : items) { // find out the correct answer's sequential letter - A,B,C... @@ -1160,21 +1152,18 @@ } answerCount++; } - row[columnCount++] = new ExcelCell(correctAnswerLetter, false); + row.addCell(correctAnswerLetter); } - rowList.add(row); - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("monitoring.label.group"), false); - rowList.add(row); + row = reportByTeamSheet.initRow(); + row.addCell(getMessage("monitoring.label.group")); int groupCount = 1; - int[] percentages = new int[summaryByTeam.size()]; + double[] percentages = new double[summaryByTeam.size()]; for (GroupSummary summary : summaryByTeam) { - row = new ExcelCell[numberOfItems + 3]; - columnCount = 0; - row[columnCount++] = new ExcelCell(summary.getSessionName(), false); + row = reportByTeamSheet.initRow(); + row.addCell(summary.getSessionName()); int numberOfFirstChoiceEvents = 0; for (ScratchieItemDTO itemDto : summary.getItemDtos()) { @@ -1184,12 +1173,12 @@ color = IndexedColors.GREEN; numberOfFirstChoiceEvents++; } - row[columnCount++] = new ExcelCell(itemDto.getAnswersSequence(), color); + row.addCell(itemDto.getAnswersSequence(), color); } - row[columnCount++] = new ExcelCell(new Integer(numberOfFirstChoiceEvents), false); - int percentage = (numberOfItems == 0) ? 0 : (100 * numberOfFirstChoiceEvents) / numberOfItems; - row[columnCount++] = new ExcelCell(percentage + "%", false); - rowList.add(row); + row.addCell(new Integer(numberOfFirstChoiceEvents)); + double percentage = (numberOfItems == 0) ? 0 : (double) numberOfFirstChoiceEvents / numberOfItems; + row.addPercentageCell(percentage); + percentages[groupCount - 1] = percentage; groupCount++; } @@ -1202,76 +1191,65 @@ sum += percentages[i]; } int percentagesLength = percentages.length == 0 ? 1 : percentages.length; - int avgMean = sum / percentagesLength; - row = new ExcelCell[numberOfItems + 3]; - row[0] = new ExcelCell(getMessage("label.avg.mean"), false); - row[numberOfItems + 2] = new ExcelCell(avgMean + "%", false); - rowList.add(row); + double avgMean = (double) sum / percentagesLength; + row = reportByTeamSheet.initRow(); + row.addCell(getMessage("label.avg.mean")); + row.addEmptyCells(numberOfItems + 1); + row.addPercentageCell(avgMean); // median - int median; + double median; int middle = percentages.length / 2; if ((percentages.length % 2) == 1) { median = percentages[middle]; } else { - median = (int) ((percentages[middle - 1] + percentages[middle]) / 2.0); + median = (double) ((percentages[middle - 1] + percentages[middle]) / 2.0); } - row = new ExcelCell[numberOfItems + 3]; - row[0] = new ExcelCell(getMessage("label.median"), false); - row[numberOfItems + 2] = new ExcelCell(median, false); - rowList.add(row); + row = reportByTeamSheet.initRow(); + row.addCell(getMessage("label.median")); + row.addEmptyCells(numberOfItems + 1); + row.addCell(median); - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.legend"), false); - rowList.add(row); + row = reportByTeamSheet.initRow(); + row.addCell(getMessage("label.legend")); - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.denotes.correct.answer"), IndexedColors.GREEN); - rowList.add(row); + row = reportByTeamSheet.initRow(); + row.addCell(getMessage("label.denotes.correct.answer"), IndexedColors.GREEN); - ExcelCell[][] secondPageData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("label.report.by.team.tra"), secondPageData); - // ======================================================= Research and Analysis page // ======================================= + ExcelSheet researchAndAnalysisSheet = new ExcelSheet(getMessage("label.research.analysis")); + sheets.add(researchAndAnalysisSheet); - // all rows - rowList = new LinkedList<>(); - // Caption - row = new ExcelCell[2]; - row[0] = new ExcelCell(getMessage("label.scratchie.report"), true); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.scratchie.report"), true); + researchAndAnalysisSheet.addEmptyRow(); + researchAndAnalysisSheet.addEmptyRow(); // Overall Summary by Team -------------------------------------------------- - row = new ExcelCell[2]; - row[0] = new ExcelCell(getMessage("label.overall.summary.by.team"), true); - rowList.add(row); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.overall.summary.by.team"), true); - row = new ExcelCell[(numberOfItems * 3) + 1]; - columnCount = 1; + row = researchAndAnalysisSheet.initRow(); + row.addEmptyCell(); for (int itemCount = 0; itemCount < numberOfItems; itemCount++) { - row[columnCount] = new ExcelCell(getMessage("label.for.question", new Object[] { itemCount + 1 }), false); - columnCount += 3; + row.addCell(getMessage("label.for.question", new Object[] { itemCount + 1 })); + row.addEmptyCells(2); } - rowList.add(row); - row = new ExcelCell[(numberOfItems * 3) + 1]; - columnCount = 1; + row = researchAndAnalysisSheet.initRow(); + row.addEmptyCell(); for (int itemCount = 0; itemCount < numberOfItems; itemCount++) { - row[columnCount++] = new ExcelCell(getMessage("label.first.choice"), IndexedColors.BLUE); - row[columnCount++] = new ExcelCell(getMessage("label.attempts"), IndexedColors.BLUE); - row[columnCount++] = new ExcelCell(getMessage("label.mark"), IndexedColors.BLUE); + row.addCell(getMessage("label.first.choice"), IndexedColors.BLUE); + row.addCell(getMessage("label.attempts"), IndexedColors.BLUE); + row.addCell(getMessage("label.mark"), IndexedColors.BLUE); } - rowList.add(row); for (GroupSummary summary : summaryByTeam) { - row = new ExcelCell[(numberOfItems * 3) + 1]; - columnCount = 0; + row = researchAndAnalysisSheet.initRow(); - row[columnCount++] = new ExcelCell(summary.getSessionName(), false); + row.addCell(summary.getSessionName()); for (ScratchieItemDTO itemDto : summary.getItemDtos()) { int attempts = itemDto.getUserAttempts(); @@ -1288,146 +1266,133 @@ isFirstChoice = getMessage("label.incorrect"); color = IndexedColors.RED; } - row[columnCount++] = new ExcelCell(isFirstChoice, color); - row[columnCount++] = new ExcelCell(new Long(attempts), color); + row.addCell(isFirstChoice, color); + row.addCell(new Long(attempts), color); Long mark = (itemDto.getUserMark() == -1) ? null : new Long(itemDto.getUserMark()); - row[columnCount++] = new ExcelCell(mark, false); + row.addCell(mark); } - rowList.add(row); } - rowList.add(ScratchieServiceImpl.EMPTY_ROW); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + researchAndAnalysisSheet.addEmptyRow(); + researchAndAnalysisSheet.addEmptyRow(); + researchAndAnalysisSheet.addEmptyRow(); // Overall Summary By Individual Student in each Team---------------------------------------- - row = new ExcelCell[2]; - row[0] = new ExcelCell(getMessage("label.overall.summary.by.individual.student"), true); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.overall.summary.by.individual.student"), true); + researchAndAnalysisSheet.addEmptyRow(); - row = new ExcelCell[4]; - row[1] = new ExcelCell(getMessage("label.attempts"), false); - row[2] = new ExcelCell(getMessage("label.mark"), false); - row[3] = new ExcelCell(getMessage("label.group"), false); - rowList.add(row); + row = researchAndAnalysisSheet.initRow(); + row.addEmptyCell(); + row.addCell(getMessage("label.attempts")); + row.addCell(getMessage("label.mark")); + row.addCell(getMessage("label.group")); List summaryList = getMonitoringSummary(contentId, false); for (GroupSummary summary : summaryList) { for (ScratchieUser user : summary.getUsers()) { - row = new ExcelCell[4]; - row[0] = new ExcelCell(user.getFirstName() + " " + user.getLastName(), false); - row[1] = new ExcelCell(new Long(summary.getTotalAttempts()), false); + row = researchAndAnalysisSheet.initRow(); + row.addCell(user.getFirstName() + " " + user.getLastName()); + row.addCell(new Long(summary.getTotalAttempts())); Long mark = (summary.getTotalAttempts() == 0) ? null : new Long(summary.getMark()); - row[2] = new ExcelCell(mark, false); - row[3] = new ExcelCell(summary.getSessionName(), false); - rowList.add(row); + row.addCell(mark); + row.addCell(summary.getSessionName()); } } - rowList.add(ScratchieServiceImpl.EMPTY_ROW); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + researchAndAnalysisSheet.addEmptyRow(); + researchAndAnalysisSheet.addEmptyRow(); // Question Reports----------------------------------------------------------------- - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.question.reports"), true); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.question.reports"), true); + researchAndAnalysisSheet.addEmptyRow(); SimpleDateFormat fullDateFormat = new SimpleDateFormat("dd/MM/yy HH:mm:ss"); for (ScratchieItem item : items) { List itemSummary = getQuestionSummary(contentId, item.getUid()); - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.question.semicolon", new Object[] { item.getTitle() }), true); - rowList.add(row); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.question.semicolon", new Object[] { item.getTitle() }), true); - row = new ExcelCell[1]; - row[0] = new ExcelCell(removeHtmlMarkup(item.getDescription()), true); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + row = researchAndAnalysisSheet.initRow(); + row.addCell(removeHtmlMarkup(item.getDescription()), true); + researchAndAnalysisSheet.addEmptyRow(); + researchAndAnalysisSheet.addEmptyRow(); // show all team summary in case there is more than 1 group if (summaryList.size() > 1) { - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.all.teams.summary"), true); - rowList.add(row); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.all.teams.summary"), true); GroupSummary allTeamSummary = itemSummary.get(0); Collection answers = allTeamSummary.getAnswers(); - row = new ExcelCell[1 + answers.size()]; + row = researchAndAnalysisSheet.initRow(); + row.addEmptyCell(); for (int i = 0; i < answers.size(); i++) { - row[i + 1] = new ExcelCell((long) i + 1, IndexedColors.YELLOW); + row.addCell((long) i + 1, IndexedColors.YELLOW); } - rowList.add(row); for (ScratchieAnswer answer : answers) { - row = new ExcelCell[1 + answers.size()]; + row = researchAndAnalysisSheet.initRow(); String answerTitle = removeHtmlMarkup(answer.getDescription()); IndexedColors color = null; if (answer.isCorrect()) { answerTitle += "(" + getMessage("label.monitoring.item.summary.correct") + ")"; color = IndexedColors.GREEN; } - columnCount = 0; - row[columnCount++] = new ExcelCell(answerTitle, color); + row.addCell(answerTitle, color); for (int numberAttempts : answer.getAttempts()) { - row[columnCount++] = new ExcelCell(new Long(numberAttempts), false); + row.addCell(new Long(numberAttempts)); } - rowList.add(row); } - rowList.add(ScratchieServiceImpl.EMPTY_ROW); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + researchAndAnalysisSheet.addEmptyRow(); + researchAndAnalysisSheet.addEmptyRow(); } - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.breakdown.by.team"), true); - rowList.add(row); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.breakdown.by.team"), true); + for (GroupSummary groupSummary : itemSummary) { if (groupSummary.getSessionId().equals(0L)) { continue; } Collection answers = groupSummary.getAnswers(); - row = new ExcelCell[1]; - row[0] = new ExcelCell(groupSummary.getSessionName(), true); - rowList.add(row); + row = researchAndAnalysisSheet.initRow(); + row.addCell(groupSummary.getSessionName(), true); - row = new ExcelCell[1 + answers.size()]; + row = researchAndAnalysisSheet.initRow(); + row.addEmptyCell(); for (int i = 0; i < answers.size(); i++) { - row[i + 1] = new ExcelCell(new Long(i + 1), false); + row.addCell(new Long(i + 1)); } - rowList.add(row); for (ScratchieAnswer answer : answers) { - row = new ExcelCell[1 + answers.size()]; + row = researchAndAnalysisSheet.initRow(); String answerTitle = removeHtmlMarkup(answer.getDescription()); if (answer.isCorrect()) { answerTitle += "(" + getMessage("label.monitoring.item.summary.correct") + ")"; } - columnCount = 0; - row[columnCount++] = new ExcelCell(answerTitle, false); + row.addCell(answerTitle); for (int numberAttempts : answer.getAttempts()) { - row[columnCount++] = new ExcelCell(new Long(numberAttempts), false); + row.addCell(new Long(numberAttempts)); } - rowList.add(row); } } - rowList.add(ScratchieServiceImpl.EMPTY_ROW); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + researchAndAnalysisSheet.addEmptyRow(); + researchAndAnalysisSheet.addEmptyRow(); } // Breakdown By Student with Timing---------------------------------------------------- - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.breakdown.by.student.with.timing"), true); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.breakdown.by.student.with.timing"), true); + researchAndAnalysisSheet.addEmptyRow(); List sessionList = scratchieSessionDao.getByContentId(scratchie.getContentId()); for (ScratchieSession session : sessionList) { @@ -1437,51 +1402,43 @@ if (groupLeader != null) { - row = new ExcelCell[5]; - row[0] = new ExcelCell(groupLeader.getFirstName() + " " + groupLeader.getLastName(), true); - row[1] = new ExcelCell(getMessage("label.attempts") + ":", false); + row = researchAndAnalysisSheet.initRow(); + row.addCell(groupLeader.getFirstName() + " " + groupLeader.getLastName(), true); + row.addCell(getMessage("label.attempts") + ":"); Long attempts = (long) scratchieAnswerVisitDao.getLogCountTotal(sessionId); - row[2] = new ExcelCell(attempts, false); - row[3] = new ExcelCell(getMessage("label.mark") + ":", false); - row[4] = new ExcelCell(new Long(session.getMark()), false); - rowList.add(row); + row.addCell(attempts); + row.addCell(getMessage("label.mark") + ":"); + row.addCell(new Long(session.getMark())); - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.team.leader") + session.getSessionName(), false); - rowList.add(row); + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.team.leader") + session.getSessionName()); for (ScratchieItem item : items) { - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.question.semicolon", new Object[] { item.getTitle() }), + row = researchAndAnalysisSheet.initRow(); + row.addCell(getMessage("label.question.semicolon", new Object[] { item.getTitle() }), false); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); int i = 1; List logs = scratchieAnswerVisitDao.getLogsBySessionAndItem(sessionId, item.getUid()); for (ScratchieAnswerVisitLog log : logs) { - row = new ExcelCell[4]; - row[0] = new ExcelCell(new Long(i++), false); + row = researchAndAnalysisSheet.initRow(); + row.addCell(new Long(i++)); String answerDescr = removeHtmlMarkup(log.getScratchieAnswer().getDescription()); - row[1] = new ExcelCell(answerDescr, false); - row[3] = new ExcelCell(fullDateFormat.format(log.getAccessDate()), false); - rowList.add(row); + row.addCell(answerDescr); + row.addCell(fullDateFormat.format(log.getAccessDate())); } - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + researchAndAnalysisSheet.addEmptyRow(); } } } - ExcelCell[][] thirdPageData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("label.research.analysis"), thirdPageData); - // ======================================================= For_XLS_export(SPSS analysis) page // ======================================= + ExcelSheet spssAnalysisSheet = new ExcelSheet(getMessage("label.spss.analysis")); + sheets.add(spssAnalysisSheet); - rowList = new LinkedList<>(); - // Table header------------------------------------ int maxAnswers = 0; @@ -1491,25 +1448,23 @@ } } - row = new ExcelCell[10 + (maxAnswers * 2)]; - columnCount = 0; - row[columnCount++] = new ExcelCell(getMessage("label.student.name"), true); - row[columnCount++] = new ExcelCell(getMessage("label.student.username"), true); - row[columnCount++] = new ExcelCell(getMessage("label.team"), true); - row[columnCount++] = new ExcelCell(getMessage("label.question.number"), true); - row[columnCount++] = new ExcelCell(getMessage("label.question"), true); - row[columnCount++] = new ExcelCell(getMessage("label.correct.answer"), true); - row[columnCount++] = new ExcelCell(getMessage("label.first.choice.accuracy"), true); - row[columnCount++] = new ExcelCell(getMessage("label.number.of.attempts"), true); - row[columnCount++] = new ExcelCell(getMessage("label.mark.awarded"), true); + row = spssAnalysisSheet.initRow(); + row.addCell(getMessage("label.student.name"), true); + row.addCell(getMessage("label.student.username"), true); + row.addCell(getMessage("label.team"), true); + row.addCell(getMessage("label.question.number"), true); + row.addCell(getMessage("label.question"), true); + row.addCell(getMessage("label.correct.answer"), true); + row.addCell(getMessage("label.first.choice.accuracy"), true); + row.addCell(getMessage("label.number.of.attempts"), true); + row.addCell(getMessage("label.mark.awarded"), true); for (int i = 0; i < maxAnswers; i++) { - row[columnCount++] = new ExcelCell(getMessage("label." + (i + 1) + ".answer.selected"), true); + row.addCell(getMessage("label." + (i + 1) + ".answer.selected"), true); } - row[columnCount++] = new ExcelCell(getMessage("label.date"), true); + row.addCell(getMessage("label.date"), true); for (int i = 0; i < maxAnswers; i++) { - row[columnCount++] = new ExcelCell(getMessage("label.time.of.selection." + (i + 1)), true); + row.addCell(getMessage("label.time.of.selection." + (i + 1)), true); } - rowList.add(row); // Table content------------------------------------ @@ -1518,22 +1473,20 @@ List users = scratchieUserDao.getBySessionID(sessionId); for (ScratchieUser user : users) { - int questionCount = 1; for (ScratchieItemDTO itemDto : summary.getItemDtos()) { - row = new ExcelCell[10 + (maxAnswers * 2)]; - columnCount = 0; + row = spssAnalysisSheet.initRow(); // learner name - row[columnCount++] = new ExcelCell(user.getFirstName() + " " + user.getLastName(), false); + row.addCell(user.getFirstName() + " " + user.getLastName()); // username - row[columnCount++] = new ExcelCell(user.getLoginName(), false); + row.addCell(user.getLoginName()); // group name - row[columnCount++] = new ExcelCell(summary.getSessionName(), false); + row.addCell(summary.getSessionName()); // question number - row[columnCount++] = new ExcelCell(new Long(questionCount++), false); + row.addCell(new Long(questionCount++)); // question title - row[columnCount++] = new ExcelCell(itemDto.getTitle(), false); + row.addCell(itemDto.getTitle()); // correct answer String correctAnswer = ""; @@ -1543,7 +1496,7 @@ correctAnswer = removeHtmlMarkup(answer.getDescription()); } } - row[columnCount++] = new ExcelCell(correctAnswer, false); + row.addCell(correctAnswer); // isFirstChoice int attempts = itemDto.getUserAttempts(); @@ -1555,12 +1508,12 @@ } else { isFirstChoice = getMessage("label.incorrect"); } - row[columnCount++] = new ExcelCell(isFirstChoice, false); + row.addCell(isFirstChoice); // attempts - row[columnCount++] = new ExcelCell(new Long(attempts), false); + row.addCell(new Long(attempts)); // mark Object mark = (itemDto.getUserMark() == -1) ? "" : new Long(itemDto.getUserMark()); - row[columnCount++] = new ExcelCell(mark, false); + row.addCell(mark); // Answers selected List logs = scratchieAnswerVisitDao.getLogsBySessionAndItem(sessionId, @@ -1571,13 +1524,13 @@ for (ScratchieAnswerVisitLog log : logs) { String answer = removeHtmlMarkup(log.getScratchieAnswer().getDescription()); - row[columnCount++] = new ExcelCell(answer, false); + row.addCell(answer); } for (int i = logs.size(); i < itemDto.getAnswers().size(); i++) { - row[columnCount++] = new ExcelCell(getMessage("label.none"), false); + row.addCell(getMessage("label.none")); } for (int i = answers.size(); i < maxAnswers; i++) { - row[columnCount++] = new ExcelCell("", false); + row.addCell(""); } // Date @@ -1587,70 +1540,56 @@ Date accessDate = logs.iterator().next().getAccessDate(); dateStr = dateFormat.format(accessDate); } - row[columnCount++] = new ExcelCell(dateStr, false); + row.addCell(dateStr); // time of selection SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm:ss"); for (ScratchieAnswerVisitLog log : logs) { Date accessDate = log.getAccessDate(); String timeStr = timeFormat.format(accessDate); - row[columnCount++] = new ExcelCell(timeStr, false); + row.addCell(timeStr); } for (int i = logs.size(); i < maxAnswers; i++) { - row[columnCount++] = new ExcelCell("", false); + row.addCell(""); } - - rowList.add(row); } - } - } - ExcelCell[][] fourthPageData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("label.spss.analysis"), fourthPageData); - // ======================================================= Burning questions page // ======================================= - if (scratchie.isBurningQuestionsEnabled()) { - rowList = new LinkedList<>(); + ExcelSheet burningQuestionsSheet = new ExcelSheet(getMessage("label.burning.questions")); + sheets.add(burningQuestionsSheet); - row = new ExcelCell[1]; - row[0] = new ExcelCell(getMessage("label.burning.questions"), true); - rowList.add(row); - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + row = burningQuestionsSheet.initRow(); + row.addCell(getMessage("label.burning.questions"), true); + burningQuestionsSheet.addEmptyRow(); - row = new ExcelCell[3]; - row[0] = new ExcelCell(getMessage("label.monitoring.summary.user.name"), IndexedColors.BLUE); - row[1] = new ExcelCell(getMessage("label.burning.questions"), IndexedColors.BLUE); - row[2] = new ExcelCell(getMessage("label.count"), IndexedColors.BLUE); - rowList.add(row); + row = burningQuestionsSheet.initRow(); + row.addCell(getMessage("label.monitoring.summary.user.name"), IndexedColors.BLUE); + row.addCell(getMessage("label.burning.questions"), IndexedColors.BLUE); + row.addCell(getMessage("label.count"), IndexedColors.BLUE); List burningQuestionItemDtos = getBurningQuestionDtos(scratchie, null, true); for (BurningQuestionItemDTO burningQuestionItemDto : burningQuestionItemDtos) { ScratchieItem item = burningQuestionItemDto.getScratchieItem(); - row = new ExcelCell[1]; - row[0] = new ExcelCell(item.getTitle(), false); - rowList.add(row); + row = burningQuestionsSheet.initRow(); + row.addCell(item.getTitle()); List burningQuestionDtos = burningQuestionItemDto.getBurningQuestionDtos(); for (BurningQuestionDTO burningQuestionDto : burningQuestionDtos) { String burningQuestion = burningQuestionDto.getBurningQuestion().getQuestion(); - row = new ExcelCell[3]; - row[0] = new ExcelCell(burningQuestionDto.getSessionName(), false); - row[1] = new ExcelCell(burningQuestion, false); - row[2] = new ExcelCell(burningQuestionDto.getLikeCount(), false); - rowList.add(row); + row = burningQuestionsSheet.initRow(); + row.addCell(burningQuestionDto.getSessionName()); + row.addCell(burningQuestion); + row.addCell(burningQuestionDto.getLikeCount()); } - rowList.add(ScratchieServiceImpl.EMPTY_ROW); + burningQuestionsSheet.addEmptyRow(); } - - ExcelCell[][] fifthPageData = rowList.toArray(new ExcelCell[][] {}); - dataToExport.put(getMessage("label.burning.questions"), fifthPageData); } - return dataToExport; + return sheets; } @Override @@ -2260,7 +2199,7 @@ scratchie.setBurningQuestionsEnabled(JsonUtil.optBoolean(toolContentJSON, "burningQuestionsEnabled", true)); scratchie.setTimeLimit(JsonUtil.optInt(toolContentJSON, "timeLimit", 0)); - scratchie.setExtraPoint(JsonUtil.optBoolean(toolContentJSON, "extraPoint", false)); + scratchie.setExtraPoint(JsonUtil.optBoolean(toolContentJSON, "extraPoint")); scratchie.setReflectOnActivity( JsonUtil.optBoolean(toolContentJSON, RestTags.REFLECT_ON_ACTIVITY, Boolean.FALSE)); scratchie.setReflectInstructions(JsonUtil.optString(toolContentJSON, RestTags.REFLECT_INSTRUCTIONS)); Index: lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/web/controller/MonitoringController.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/web/controller/MonitoringController.java (.../MonitoringController.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/web/controller/MonitoringController.java (.../MonitoringController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -56,13 +56,16 @@ 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; import org.lamsfoundation.lams.web.util.SessionMap; import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.http.HttpStatus; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; +import org.springframework.web.bind.annotation.ResponseStatus; import com.fasterxml.jackson.databind.node.JsonNodeFactory; import com.fasterxml.jackson.databind.node.ObjectNode; @@ -216,14 +219,15 @@ * @throws IOException */ @RequestMapping("/exportExcel") - private String exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { + @ResponseStatus(HttpStatus.OK) + private void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { String sessionMapID = request.getParameter(ScratchieConstants.ATTR_SESSION_MAP_ID); SessionMap sessionMap = (SessionMap) request.getSession() .getAttribute(sessionMapID); Scratchie scratchie = (Scratchie) sessionMap.get(ScratchieConstants.ATTR_SCRATCHIE); - LinkedHashMap dataToExport = scratchieService.exportExcel(scratchie.getContentId()); + List sheets = scratchieService.exportExcel(scratchie.getContentId()); String fileName = "scratchie_export.xlsx"; fileName = FileUtil.encodeFilenameForDownload(request, fileName); @@ -239,9 +243,7 @@ // Code to generate file and write file contents to response ServletOutputStream out = response.getOutputStream(); - ExcelUtil.createExcel(out, dataToExport, null, false); - - return null; + ExcelUtil.createExcel(out, sheets, null, false); } /** Index: lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/web/controller/TblMonitorController.java =================================================================== diff -u -r757ceb570a6d7b9ac11df60ef4de581848c79ba0 -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/web/controller/TblMonitorController.java (.../TblMonitorController.java) (revision 757ceb570a6d7b9ac11df60ef4de581848c79ba0) +++ lams_tool_scratchie/src/java/org/lamsfoundation/lams/tool/scratchie/web/controller/TblMonitorController.java (.../TblMonitorController.java) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -28,7 +28,6 @@ import java.util.Collection; import java.util.Collections; import java.util.Comparator; -import java.util.LinkedHashMap; import java.util.LinkedHashSet; import java.util.LinkedList; import java.util.List; @@ -56,13 +55,16 @@ import org.lamsfoundation.lams.util.AlphanumComparator; 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.ExcelRow; +import org.lamsfoundation.lams.util.excel.ExcelSheet; import org.lamsfoundation.lams.util.excel.ExcelUtil; import org.lamsfoundation.lams.web.util.AttributeNames; import org.springframework.beans.factory.annotation.Autowired; +import org.springframework.http.HttpStatus; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.ResponseBody; +import org.springframework.web.bind.annotation.ResponseStatus; import com.fasterxml.jackson.databind.node.JsonNodeFactory; import com.fasterxml.jackson.databind.node.ObjectNode; @@ -76,11 +78,10 @@ private IScratchieService scratchieService; /** - * Shows tra page + * Shows TRA page */ @RequestMapping("/tra") public String tra(HttpServletRequest request) throws IOException, ServletException { - long toolContentId = WebUtil.readLongParam(request, AttributeNames.PARAM_TOOL_CONTENT_ID); Scratchie scratchie = scratchieService.getScratchieByContentId(toolContentId); @@ -92,22 +93,18 @@ request.setAttribute("items", items); if (attemptedLearnersNumber != 0) { - // find first page in excel file - LinkedHashMap excelDoc = scratchieService.exportExcel(toolContentId); - ExcelCell[][] firstPageData = null; - for (String key : excelDoc.keySet()) { - firstPageData = excelDoc.get(key); - break; - } + // find the first page in excel file + List sheets = scratchieService.exportExcel(toolContentId); + ExcelSheet firstPageData = sheets.get(0); int groupsSize = scratchieService.countSessionsByContentId(toolContentId); ArrayList groupRows = new ArrayList<>(); for (int groupCount = 0; groupCount < groupsSize; groupCount++) { - ExcelCell[] groupRow = firstPageData[5 + groupCount]; + ExcelRow groupRow = firstPageData.getRow(5 + groupCount); String[] groupRow2 = new String[2]; - groupRow2[0] = (String) groupRow[1].getCellValue(); - groupRow2[1] = ((String) groupRow[groupRow.length - 1].getCellValue()).replaceAll("%", ""); + groupRow2[0] = (String) groupRow.getCell(1); + groupRow2[1] = String.valueOf((Double) groupRow.getCell(groupRow.getCells().size() - 1) * 100); groupRows.add(groupRow2); } request.setAttribute("groupRows", groupRows); @@ -121,7 +118,6 @@ */ @RequestMapping("/traStudentChoices") public String traStudentChoices(HttpServletRequest request) throws IOException, ServletException { - long toolContentId = WebUtil.readLongParam(request, AttributeNames.PARAM_TOOL_CONTENT_ID); Scratchie scratchie = scratchieService.getScratchieByContentId(toolContentId); @@ -130,43 +126,33 @@ request.setAttribute("items", items); //find second page in excel file - LinkedHashMap excelDoc = scratchieService.exportExcel(toolContentId); - ExcelCell[][] secondPageData = null; - for (ExcelCell[][] excelPage : excelDoc.values()) { - //check last row string starts with "*" (i.e. the string "*- Denotes the correct answer") - if (excelPage.length > 0) { - ExcelCell lastRow = excelPage[excelPage.length - 1][0]; - if (lastRow != null && ((String) lastRow.getCellValue()).startsWith("*")) { - secondPageData = excelPage; - break; - } - } - } + List sheets = scratchieService.exportExcel(toolContentId); + ExcelSheet secondPageData = sheets.get(1); //correct answers - ExcelCell[] correctAnswersRow = secondPageData[4]; + ExcelRow correctAnswersRow = secondPageData.getRow(4); request.setAttribute("correctAnswers", correctAnswersRow); //prepare data for displaying user answers table int groupsSize = scratchieService.countSessionsByContentId(toolContentId); ArrayList sessionDtos = new ArrayList<>(); for (int groupCount = 0; groupCount < groupsSize; groupCount++) { - ExcelCell[] groupRow = secondPageData[6 + groupCount]; + ExcelRow groupRow = secondPageData.getRows().get(6 + groupCount); GroupSummary groupSummary = new GroupSummary(); - String sessionName = groupRow[0].getCellValue().toString(); + String sessionName = groupRow.getCell(0).toString(); groupSummary.setSessionName(sessionName); Collection itemDtos = new ArrayList<>(); for (int i = 1; i <= items.size(); i++) { ScratchieItemDTO itemDto = new ScratchieItemDTO(); - String answersSequence = groupRow[i].getCellValue().toString(); + String answersSequence = groupRow.getCell(i).toString(); String[] answerLetters = answersSequence.split(", "); Set answers = new LinkedHashSet<>(); for (int j = 0; j < answerLetters.length; j++) { String answerLetter = answerLetters[j]; - String correctAnswerLetter = correctAnswersRow[i].getCellValue().toString(); + String correctAnswerLetter = correctAnswersRow.getCell(i).toString(); ScratchieAnswer answer = new ScratchieAnswer(); answer.setDescription(answerLetter); @@ -181,10 +167,10 @@ groupSummary.setItemDtos(itemDtos); if (!itemDtos.isEmpty()) { - int total = (Integer) groupRow[itemDtos.size() + 1].getCellValue(); + int total = (Integer) groupRow.getCell(itemDtos.size() + 1); groupSummary.setMark(total); - String totalPercentage = groupRow[itemDtos.size() + 2].getCellValue().toString(); + String totalPercentage = groupRow.getCell(itemDtos.size() + 2).toString(); groupSummary.setTotalPercentage(totalPercentage); } @@ -204,10 +190,11 @@ * @throws IOException */ @RequestMapping("/exportExcel") - public String exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { + @ResponseStatus(HttpStatus.OK) + public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { Long toolContentId = WebUtil.readLongParam(request, AttributeNames.PARAM_TOOL_CONTENT_ID); - LinkedHashMap dataToExport = scratchieService.exportExcel(toolContentId); + List sheets = scratchieService.exportExcel(toolContentId); String fileName = "scratchie_export.xlsx"; fileName = FileUtil.encodeFilenameForDownload(request, fileName); @@ -217,9 +204,7 @@ // Code to generate file and write file contents to response ServletOutputStream out = response.getOutputStream(); - ExcelUtil.createExcel(out, dataToExport, null, false); - - return null; + ExcelUtil.createExcel(out, sheets, null, false); } /** Index: lams_tool_scratchie/web/pages/tblmonitoring/traStudentChoices.jsp =================================================================== diff -u -rba15b06823380b36eb2a71a4f95a4bef851d311a -rcbf95a868252401757c61327b3d9a383119ff9b5 --- lams_tool_scratchie/web/pages/tblmonitoring/traStudentChoices.jsp (.../traStudentChoices.jsp) (revision ba15b06823380b36eb2a71a4f95a4bef851d311a) +++ lams_tool_scratchie/web/pages/tblmonitoring/traStudentChoices.jsp (.../traStudentChoices.jsp) (revision cbf95a868252401757c61327b3d9a383119ff9b5) @@ -105,10 +105,12 @@ - - - ${correctAnswers[i].cellValue} - + + + + ${correctAnswerCell.cellValue} + +