Index: lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java =================================================================== diff -u -rf13b8a48b9a1cfaee56a654ba751a9736616b726 -rf387f22d1d29e552262b9b538a3b46c1b766a3c7 --- lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java (.../ExcelUtil.java) (revision f13b8a48b9a1cfaee56a654ba751a9736616b726) +++ lams_common/src/java/org/lamsfoundation/lams/util/excel/ExcelUtil.java (.../ExcelUtil.java) (revision f387f22d1d29e552262b9b538a3b46c1b766a3c7) @@ -20,14 +20,11 @@ * **************************************************************** */ - package org.lamsfoundation.lams.util.excel; import java.io.IOException; import java.io.OutputStream; import java.util.Date; -import java.util.LinkedHashMap; -import java.util.LinkedList; import java.util.List; import java.util.TimeZone; @@ -66,7 +63,7 @@ private static short dateFormat; private static short timeFormat; private static short percentageFormat; - + private static CellStyle defaultStyle; private static CellStyle boldStyle; @@ -86,8 +83,11 @@ public final static String DEFAULT_FONT_NAME = "Calibri-Regular"; + private final static int MAX_CELL_TEXT_LENGTH = 32767; + /** - * Create .xlsx file out of provided data and then write out it to an OutputStream. It will be saved with the .xlsx extension. + * Create .xlsx file out of provided data and then write out it to an OutputStream. It will be saved with the .xlsx + * extension. * * @param out * output stream to which the file written; usually taken from HTTP response @@ -105,11 +105,11 @@ boolean displaySheetTitle) throws IOException { ExcelUtil.createExcel(out, sheets, dateHeader, displaySheetTitle, true); } - + /** - * Creates Excel file based on the provided data and writes it out to an OutputStream. - * - * + * Creates Excel file based on the provided data and writes it out to an OutputStream. + * + * * Warning: The styling is untested with this option and may fail. If you want full styling look at createExcel() * * @param out @@ -122,32 +122,32 @@ * {@link #EXPORT_TO_SPREADSHEET_TITLE_DATE_FORMAT} * @param displaySheetTitle * whether to display title (printed in the first (0,0) cell) - * + * * @param produceXlsxFile * whether excel file should be of .xlsx or .xls format. Use .xls only if you want to read the file back * in again afterwards. * @throws IOException */ public static void createExcel(OutputStream out, List sheets, String dateHeader, boolean displaySheetTitle, boolean produceXlsxFile) throws IOException { - //set user time zone, which is required for outputting cells of time format + //set user time zone, which is required for outputting cells of time format HttpSession ss = SessionManager.getSession(); UserDTO user = (UserDTO) ss.getAttribute(AttributeNames.USER); TimeZone userTimeZone = user.getTimeZone(); LocaleUtil.setUserTimeZone(userTimeZone); - + //in case .xlsx is requested use SXSSFWorkbook.class (which keeps 100 rows in memory, exceeding rows will be flushed to disk) - Workbook workbook = produceXlsxFile ? new SXSSFWorkbook(100): new HSSFWorkbook(); + Workbook workbook = produceXlsxFile ? new SXSSFWorkbook(100) : new HSSFWorkbook(); ExcelUtil.initStyles(workbook); - + for (ExcelSheet sheet : sheets) { ExcelUtil.createSheet(workbook, sheet, dateHeader, displaySheetTitle); } workbook.write(out); out.close(); } - + private static void initStyles(Workbook workbook) { Font defaultFont = workbook.createFont(); defaultFont.setFontName(DEFAULT_FONT_NAME); @@ -187,8 +187,8 @@ // create data formats floatFormat = workbook.createDataFormat().getFormat("0.00"); numberFormat = workbook.createDataFormat().getFormat("0"); - dateFormat = (short)14;// built-in 0xe format - "m/d/yy" - timeFormat = (short)19;// built-in 0x13 format - "h:mm:ss AM/PM" + dateFormat = (short) 14;// built-in 0xe format - "m/d/yy" + timeFormat = (short) 19;// built-in 0x13 format - "h:mm:ss AM/PM" percentageFormat = workbook.createDataFormat().getFormat(FORMAT_PERCENTAGE); //create border style @@ -218,8 +218,8 @@ borderStyleBottomThinBoldFont.setFont(boldFont); } - private static void createSheet(Workbook workbook, ExcelSheet excelSheet, String dateHeader, boolean displaySheetTitle) - throws IOException { + private static void createSheet(Workbook workbook, ExcelSheet excelSheet, String dateHeader, + boolean displaySheetTitle) 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()); @@ -230,7 +230,7 @@ Sheet sheet = workbook.createSheet(sheetName); //make sure columns are tracked prior to auto-sizing them if (workbook instanceof SXSSFWorkbook) { - ((SXSSFSheet)sheet).trackAllColumnsForAutoSizing(); + ((SXSSFSheet) sheet).trackAllColumnsForAutoSizing(); } // Print title if requested @@ -274,7 +274,7 @@ private static void createRow(ExcelRow excelRow, int rowIndex, Sheet sheet) { Row row = sheet.createRow(rowIndex); - + int columnIndex = 0; for (ExcelCell excelCell : excelRow.getCells()) { if (excelCell == null) { @@ -305,7 +305,7 @@ cell.setCellValue(((Integer) excelCellValue).doubleValue()); } else { - cell.setCellValue(excelCellValue.toString()); + cell.setCellValue(ExcelUtil.ensureCorrectCellLength(excelCellValue.toString())); } } @@ -409,4 +409,8 @@ } } + public static String ensureCorrectCellLength(String cellText) { + return cellText.length() > MAX_CELL_TEXT_LENGTH ? cellText.substring(0, MAX_CELL_TEXT_LENGTH) : cellText; + } + } Index: lams_tool_forum/src/java/org/lamsfoundation/lams/tool/forum/web/controller/MonitoringController.java =================================================================== diff -u -r0e7d403e91b0916fd3842d8d3098b1c466d28ece -rf387f22d1d29e552262b9b538a3b46c1b766a3c7 --- lams_tool_forum/src/java/org/lamsfoundation/lams/tool/forum/web/controller/MonitoringController.java (.../MonitoringController.java) (revision 0e7d403e91b0916fd3842d8d3098b1c466d28ece) +++ lams_tool_forum/src/java/org/lamsfoundation/lams/tool/forum/web/controller/MonitoringController.java (.../MonitoringController.java) (revision f387f22d1d29e552262b9b538a3b46c1b766a3c7) @@ -69,6 +69,7 @@ import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.NumberUtil; import org.lamsfoundation.lams.util.WebUtil; +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; @@ -380,7 +381,8 @@ cell = row.createCell(idx++); if (dto.getMessage() != null && dto.getMessage().getReport() != null) { - cell.setCellValue(dto.getMessage().getReport().getComment()); + cell.setCellValue( + ExcelUtil.ensureCorrectCellLength(dto.getMessage().getReport().getComment())); } else { cell.setCellValue(""); } Index: lams_tool_sbmt/src/java/org/lamsfoundation/lams/tool/sbmt/web/controller/MonitoringController.java =================================================================== diff -u -r0e7d403e91b0916fd3842d8d3098b1c466d28ece -rf387f22d1d29e552262b9b538a3b46c1b766a3c7 --- lams_tool_sbmt/src/java/org/lamsfoundation/lams/tool/sbmt/web/controller/MonitoringController.java (.../MonitoringController.java) (revision 0e7d403e91b0916fd3842d8d3098b1c466d28ece) +++ lams_tool_sbmt/src/java/org/lamsfoundation/lams/tool/sbmt/web/controller/MonitoringController.java (.../MonitoringController.java) (revision f387f22d1d29e552262b9b538a3b46c1b766a3c7) @@ -59,6 +59,7 @@ import org.lamsfoundation.lams.util.DateUtil; import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.WebUtil; +import org.lamsfoundation.lams.util.excel.ExcelUtil; import org.lamsfoundation.lams.web.session.SessionManager; import org.lamsfoundation.lams.web.util.AttributeNames; import org.springframework.beans.factory.annotation.Autowired; @@ -341,7 +342,7 @@ cell.setCellValue(marks != null ? marks : ""); cell = row.createCell(count++); - cell.setCellValue(dto.getComments()); + cell.setCellValue(ExcelUtil.ensureCorrectCellLength(dto.getComments())); } } } Index: lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/web/controller/MonitoringController.java =================================================================== diff -u -rf23f6693e999a16730433009b95264fa8af9e1a8 -rf387f22d1d29e552262b9b538a3b46c1b766a3c7 --- lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/web/controller/MonitoringController.java (.../MonitoringController.java) (revision f23f6693e999a16730433009b95264fa8af9e1a8) +++ lams_tool_spreadsheet/src/java/org/lamsfoundation/lams/tool/spreadsheet/web/controller/MonitoringController.java (.../MonitoringController.java) (revision f387f22d1d29e552262b9b538a3b46c1b766a3c7) @@ -49,6 +49,7 @@ import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.NumberUtil; import org.lamsfoundation.lams.util.WebUtil; +import org.lamsfoundation.lams.util.excel.ExcelUtil; import org.lamsfoundation.lams.web.util.AttributeNames; import org.lamsfoundation.lams.web.util.SessionMap; import org.springframework.beans.factory.annotation.Autowired; @@ -280,7 +281,7 @@ } cell = row.createCell(count++); - cell.setCellValue(mark.getComments()); + cell.setCellValue(ExcelUtil.ensureCorrectCellLength(mark.getComments())); } } Index: lams_tool_survey/src/java/org/lamsfoundation/lams/tool/survey/web/controller/MonitoringController.java =================================================================== diff -u -r0e7d403e91b0916fd3842d8d3098b1c466d28ece -rf387f22d1d29e552262b9b538a3b46c1b766a3c7 --- lams_tool_survey/src/java/org/lamsfoundation/lams/tool/survey/web/controller/MonitoringController.java (.../MonitoringController.java) (revision 0e7d403e91b0916fd3842d8d3098b1c466d28ece) +++ lams_tool_survey/src/java/org/lamsfoundation/lams/tool/survey/web/controller/MonitoringController.java (.../MonitoringController.java) (revision f387f22d1d29e552262b9b538a3b46c1b766a3c7) @@ -59,6 +59,7 @@ import org.lamsfoundation.lams.util.JsonUtil; import org.lamsfoundation.lams.util.MessageService; import org.lamsfoundation.lams.util.WebUtil; +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; @@ -331,7 +332,8 @@ // survey instruction row = sheet.createRow(idx++); cell = row.createCell(0); - cell.setCellValue(SurveyWebUtils.removeHTMLTags(survey.getInstructions())); + cell.setCellValue( + ExcelUtil.ensureCorrectCellLength(SurveyWebUtils.removeHTMLTags(survey.getInstructions()))); // display 2 empty row row = sheet.createRow(idx++); @@ -383,7 +385,8 @@ cell = row.createCell(0); cell.setCellValue(SurveyConstants.OPTION_SHORT_HEADER + optionIdx); cell = row.createCell(1); - cell.setCellValue(SurveyWebUtils.removeHTMLTags(option.getDescription())); + cell.setCellValue( + ExcelUtil.ensureCorrectCellLength(SurveyWebUtils.removeHTMLTags(option.getDescription()))); } if (question.isAppendText() || question.getType() == SurveyConstants.QUESTION_TYPE_TEXT_ENTRY) { optionIdx++; @@ -456,7 +459,8 @@ if (question.isAppendText() || question.getType() == SurveyConstants.QUESTION_TYPE_TEXT_ENTRY) { cell = row.createCell(++cellIdx); if (answer.getAnswer() != null) { - cell.setCellValue(SurveyWebUtils.removeHTMLTags(answer.getAnswer().getAnswerText())); + cell.setCellValue(ExcelUtil.ensureCorrectCellLength( + SurveyWebUtils.removeHTMLTags(answer.getAnswer().getAnswerText()))); } }