Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java =================================================================== diff -u -r5446ab8ac25d45a9d06b3ec8a8724a05546daa26 -r74717924f7dac5b18892bdcf36fc14d90ea2ee61 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java (.../ExcelUtil.java) (revision 5446ab8ac25d45a9d06b3ec8a8724a05546daa26) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java (.../ExcelUtil.java) (revision 74717924f7dac5b18892bdcf36fc14d90ea2ee61) @@ -96,11 +96,23 @@ * whether to display title (printed in the first (0,0) cell) * @throws IOException */ + public static void createExcel(OutputStream out, List sheets, String dateHeader, boolean displaySheetTitle) throws IOException { - ExcelUtil.createExcel(out, sheets, dateHeader, displaySheetTitle, true); + ExcelUtil.createExcel(out, sheets, dateHeader, displaySheetTitle, true, null); } + // versions of this method with fixedColumnWidth + public static void createExcel(OutputStream out, List sheets, String dateHeader, + boolean displaySheetTitle, Integer fixedColumnWidth) throws IOException { + ExcelUtil.createExcel(out, sheets, dateHeader, displaySheetTitle, true, fixedColumnWidth); + } + + public static void createExcel(OutputStream out, List sheets, String dateHeader, + boolean displaySheetTitle, boolean produceXlsxFile) throws IOException { + ExcelUtil.createExcel(out, sheets, dateHeader, displaySheetTitle, true, null); + } + /** * Creates Excel file based on the provided data and writes it out to an OutputStream. * @@ -124,7 +136,7 @@ * @throws IOException */ public static void createExcel(OutputStream out, List sheets, String dateHeader, - boolean displaySheetTitle, boolean produceXlsxFile) throws IOException { + boolean displaySheetTitle, boolean produceXlsxFile, Integer fixedColumnWidth) throws IOException { //set user time zone, which is required for outputting cells of time format HttpSession ss = SessionManager.getSession(); UserDTO user = (UserDTO) ss.getAttribute(AttributeNames.USER); @@ -136,7 +148,7 @@ ExcelUtil.initStyles(workbook); for (ExcelSheet sheet : sheets) { - ExcelUtil.createSheet(workbook, sheet, dateHeader, displaySheetTitle); + ExcelUtil.createSheet(workbook, sheet, dateHeader, displaySheetTitle, fixedColumnWidth); } workbook.write(out); @@ -186,7 +198,7 @@ } private static void createSheet(Workbook workbook, ExcelSheet excelSheet, String dateHeader, - boolean displaySheetTitle) throws IOException { + boolean displaySheetTitle, Integer fixedColumnWidth) throws IOException { // Modify sheet name if required. It should contain only allowed letters and sheets are not allowed with // the same names (case insensitive) String sheetName = WorkbookUtil.createSafeSheetName(excelSheet.getSheetName()); @@ -195,7 +207,7 @@ } Sheet sheet = workbook.createSheet(sheetName); - Map columnWidths = new HashMap<>(); + Map columnWidths = fixedColumnWidth == null ? null : new HashMap<>(); // Print title if requested boolean isTitleToBePrinted = displaySheetTitle && StringUtils.isNotBlank(excelSheet.getSheetName()); @@ -238,7 +250,10 @@ for (int columnIndex : columnWidths.keySet()) { // one unit is 1/256 of character width, plus some characters for padding // maximum is 255 characters - sheet.setColumnWidth(columnIndex, Math.min(255, columnWidths.get(columnIndex) + 4) * 256); + // or just use the declared fixed column width + int columnWidth = fixedColumnWidth == null ? Math.min(255, columnWidths.get(columnIndex) + 4) * 256 + : fixedColumnWidth; + sheet.setColumnWidth(columnIndex, columnWidth); } } @@ -275,7 +290,7 @@ } // prevent malicious formula injection sourceCellStyle.setQuotePrefixed(true); - + Cell cell = CellUtil.createCell(row, columnIndex, null, sourceCellStyle); Object excelCellValue = excelCell.getCellValue(); @@ -374,11 +389,13 @@ } } - // Store maximum number of characters in each column. - // XLXS format processing is done chunk by chunk and it is append only, so this information needs to be stored on the fly. - Integer existingColumnWidth = columnWidths.get(columnIndex); - if (existingColumnWidth == null || existingColumnWidth < cellValueLength) { - columnWidths.put(columnIndex, cellValueLength); + if (columnWidths != null) { + // Store maximum number of characters in each column. + // XLXS format processing is done chunk by chunk and it is append only, so this information needs to be stored on the fly. + Integer existingColumnWidth = columnWidths.get(columnIndex); + if (existingColumnWidth == null || existingColumnWidth < cellValueLength) { + columnWidths.put(columnIndex, cellValueLength); + } } columnIndex++; Index: lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/util/SpreadsheetBuilder.java =================================================================== diff -u -r050db5467533e8e30b07389b2c44ab3d86275ce7 -r74717924f7dac5b18892bdcf36fc14d90ea2ee61 --- lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/util/SpreadsheetBuilder.java (.../SpreadsheetBuilder.java) (revision 050db5467533e8e30b07389b2c44ab3d86275ce7) +++ lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/util/SpreadsheetBuilder.java (.../SpreadsheetBuilder.java) (revision 74717924f7dac5b18892bdcf36fc14d90ea2ee61) @@ -69,7 +69,6 @@ return sheets; } - @SuppressWarnings("unchecked") private void generateTeamSheet(PeerreviewSession session) { ExcelSheet sessionSheet = new ExcelSheet(session.getSessionName()); sheets.add(sessionSheet); @@ -141,7 +140,7 @@ List ratingDtos = service.getRatingCriteriaDtos(session.getPeerreview().getContentId(), session.getSessionId(), userNames.keySet(), true, -1L); for (ItemRatingDTO ratingDto : ratingDtos) { - Double userMarkSum = 0D; + double userMarkSum = 0D; double[] userRowData = new double[countNonCommentCriteria]; for (ItemRatingCriteriaDTO itemRatingCriteriaDTO : ratingDto.getCriteriaDtos()) { if (itemRatingCriteriaDTO.getAverageRatingAsNumber() != null @@ -171,7 +170,7 @@ // calculate the group averages ExcelRow avgRow = new ExcelRow(); avgRow.addCell(service.getLocalisedMessage("label.average", null), true); - Double averageMarkSum = 0D; + double averageMarkSum = 0D; for (int i = 0; i < criteriaMarkSum.length - 1; i++) { if (criteriaMarkCount[i] > 0) { Double d = criteriaMarkSum[i] / criteriaMarkCount[i]; Index: lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/web/controller/MonitoringController.java =================================================================== diff -u -r53b1f5c6dcfb9e0d74e56c9647da69f07b889a55 -r74717924f7dac5b18892bdcf36fc14d90ea2ee61 --- lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/web/controller/MonitoringController.java (.../MonitoringController.java) (revision 53b1f5c6dcfb9e0d74e56c9647da69f07b889a55) +++ lams_tool_preview/src/java/org/lamsfoundation/lams/tool/peerreview/web/controller/MonitoringController.java (.../MonitoringController.java) (revision 74717924f7dac5b18892bdcf36fc14d90ea2ee61) @@ -84,6 +84,9 @@ private static final String EMAIL_PREVIEW_PATH = "pages/monitoring/emailpreview"; private static final String MANAGE_USERS_PATH = "/pages/monitoring/manageUsers"; + // this seems to reflect width of 12.5 the best + private static final int SPREADSHEET_EXPORT_FIXED_COLUMN_WIDTH = Double.valueOf(13.22 * 256).intValue(); + @Autowired @Qualifier("peerreviewService") private IPeerreviewService service; @@ -598,7 +601,7 @@ // set cookie that will tell JS script that export has been finished WebUtil.setFileDownloadTokenCookie(request, response); - ExcelUtil.createExcel(out, sheets, "Exported on:", true); + ExcelUtil.createExcel(out, sheets, "Exported on:", true, SPREADSHEET_EXPORT_FIXED_COLUMN_WIDTH); } catch (IOException e) { log.error("exportTeamReportExcelSpreadsheet i/o error occured: " + e.getMessage(), e);