List of usage examples for org.apache.poi.ss.usermodel Row setHeightInPoints
void setHeightInPoints(float height);
From source file:org.dashbuilder.dataset.backend.DataSetExportServicesImpl.java
License:Apache License
@Override public org.uberfire.backend.vfs.Path exportDataSetExcel(DataSet dataSet) { try {/* w ww .ja v a 2 s . c o m*/ // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters if (dataSet == null) throw new IllegalArgumentException("Null dataSet specified!"); int columnCount = dataSet.getColumns().size(); int rowCount = dataSet.getRowCount() + 1; //Include header row; int row = 0; SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk Map<String, CellStyle> styles = createStyles(wb); Sheet sh = wb.createSheet("Sheet 1"); // General setup sh.setDisplayGridlines(true); sh.setPrintGridlines(false); sh.setFitToPage(true); sh.setHorizontallyCenter(true); PrintSetup printSetup = sh.getPrintSetup(); printSetup.setLandscape(true); // Create header Row header = sh.createRow(row++); header.setHeightInPoints(20f); for (int i = 0; i < columnCount; i++) { Cell cell = header.createCell(i); cell.setCellStyle(styles.get("header")); cell.setCellValue(dataSet.getColumnByIndex(i).getId()); } // Create data rows for (; row < rowCount; row++) { Row _row = sh.createRow(row); for (int cellnum = 0; cellnum < columnCount; cellnum++) { Cell cell = _row.createCell(cellnum); Object value = dataSet.getValueAt(row - 1, cellnum); if (value instanceof Short || value instanceof Long || value instanceof Integer || value instanceof BigInteger) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("integer_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("decimal_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("date_cell")); cell.setCellValue((Date) value); } else if (value instanceof Interval) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(((Interval) value).getName()); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(value.toString()); } } } // Adjust column size for (int i = 0; i < columnCount; i++) { sh.autoSizeColumn(i); } String tempXlsFile = uuidGenerator.newUuid() + ".xlsx"; Path tempXlsPath = gitStorage.createTempFile(tempXlsFile); OutputStream os = Files.newOutputStream(tempXlsPath); wb.write(os); os.flush(); os.close(); // Dispose of temporary files backing this workbook on disk if (!wb.dispose()) { log.warn("Could not dispose of temporary file associated to data export!"); } return Paths.convert(tempXlsPath); } catch (Exception e) { throw exceptionManager.handleException(e); } }
From source file:org.dashbuilder.dataset.service.DataSetExportServicesImpl.java
License:Apache License
SXSSFWorkbook dataSetToWorkbook(DataSet dataSet) {
// TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters
if (dataSet == null) {
throw new IllegalArgumentException("Null dataSet specified!");
}//from w w w . j av a2s . co m
int columnCount = dataSet.getColumns().size();
int rowCount = dataSet.getRowCount() + 1; //Include header row;
int row = 0;
SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
Map<String, CellStyle> styles = createStyles(wb);
SXSSFSheet sh = wb.createSheet("Sheet 1");
// General setup
sh.setDisplayGridlines(true);
sh.setPrintGridlines(false);
sh.setFitToPage(true);
sh.setHorizontallyCenter(true);
sh.trackAllColumnsForAutoSizing();
PrintSetup printSetup = sh.getPrintSetup();
printSetup.setLandscape(true);
// Create header
Row header = sh.createRow(row++);
header.setHeightInPoints(20f);
for (int i = 0; i < columnCount; i++) {
Cell cell = header.createCell(i);
cell.setCellStyle(styles.get("header"));
cell.setCellValue(dataSet.getColumnByIndex(i).getId());
}
// Create data rows
for (; row < rowCount; row++) {
Row _row = sh.createRow(row);
for (int cellnum = 0; cellnum < columnCount; cellnum++) {
Cell cell = _row.createCell(cellnum);
Object value = dataSet.getValueAt(row - 1, cellnum);
if (value instanceof Short || value instanceof Long || value instanceof Integer
|| value instanceof BigInteger) {
cell.setCellType(CellType.NUMERIC);
cell.setCellStyle(styles.get("integer_number_cell"));
cell.setCellValue(((Number) value).doubleValue());
} else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) {
cell.setCellType(CellType.NUMERIC);
cell.setCellStyle(styles.get("decimal_number_cell"));
cell.setCellValue(((Number) value).doubleValue());
} else if (value instanceof Date) {
cell.setCellType(CellType.STRING);
cell.setCellStyle(styles.get("date_cell"));
cell.setCellValue((Date) value);
} else if (value instanceof Interval) {
cell.setCellType(CellType.STRING);
cell.setCellStyle(styles.get(TEXT_CELL));
cell.setCellValue(((Interval) value).getName());
} else {
cell.setCellType(CellType.STRING);
cell.setCellStyle(styles.get(TEXT_CELL));
String val = value == null ? "" : value.toString();
cell.setCellValue(val);
}
}
}
// Adjust column size
for (int i = 0; i < columnCount; i++) {
sh.autoSizeColumn(i);
}
return wb;
}
From source file:org.h819.commons.file.excel.poi.examples.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*from w w w .java 2 s .c o m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); // header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { // the 10th cell contains sum over week days, e.g. // SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } // row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); // set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } // finally set column widths, the width is measured in units of 1/256th // of a character width sheet.setColumnWidth(0, 30 * 256); // 30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); // 6 characters wide } sheet.setColumnWidth(10, 10 * 256); // 10 characters wide // Write the output to a file String file = "timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:org.hellojavaer.poi.excel.utils.ExcelUtils.java
License:Apache License
@SuppressWarnings("unchecked") private static void write(boolean useTemplate, Workbook workbook, OutputStream outputStream, ExcelWriteSheetProcessor<?>... sheetProcessors) { for (@SuppressWarnings("rawtypes") ExcelWriteSheetProcessor sheetProcessor : sheetProcessors) { @SuppressWarnings("rawtypes") ExcelWriteContext context = new ExcelWriteContext(); try {/* w w w . j av a 2s.c om*/ if (sheetProcessor == null) { continue; } String sheetName = sheetProcessor.getSheetName(); Integer sheetIndex = sheetProcessor.getSheetIndex(); Sheet sheet = null; if (sheetProcessor.getTemplateStartRowIndex() == null && sheetProcessor.getTemplateEndRowIndex() == null) { sheetProcessor.setTemplateRows(sheetProcessor.getStartRowIndex(), sheetProcessor.getStartRowIndex()); } // sheetName priority, if (useTemplate) { if (sheetName != null) { try { sheet = workbook.getSheet(sheetName); } catch (IllegalArgumentException e) { // ignore } if (sheet != null && sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException( "sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else if (sheetIndex != null) { try { sheet = workbook.getSheetAt(sheetIndex); } catch (IllegalArgumentException e) { // ignore } } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } if (sheet == null) { ExcelWriteException e = new ExcelWriteException( "Sheet Not Found Exception. for sheet name:" + sheetName); e.setCode(ExcelWriteException.CODE_OF_SHEET_NOT_EXSIT); throw e; } } else { if (sheetName != null) { sheet = workbook.getSheet(sheetName); if (sheet != null) { if (sheetIndex != null && !sheetIndex.equals(workbook.getSheetIndex(sheet))) { throw new IllegalArgumentException("sheetName[" + sheetName + "] and sheetIndex[" + sheetIndex + "] not match."); } } else { sheet = workbook.createSheet(sheetName); if (sheetIndex != null) { workbook.setSheetOrder(sheetName, sheetIndex); } } } else if (sheetIndex != null) { sheet = workbook.createSheet(); workbook.setSheetOrder(sheet.getSheetName(), sheetIndex); } else { throw new IllegalArgumentException("sheetName or sheetIndex can't be null"); } } if (sheetIndex == null) { sheetIndex = workbook.getSheetIndex(sheet); } if (sheetName == null) { sheetName = sheet.getSheetName(); } // proc sheet context.setCurSheet(sheet); context.setCurSheetIndex(sheetIndex); context.setCurSheetName(sheet.getSheetName()); context.setCurRow(null); context.setCurRowIndex(null); context.setCurCell(null); context.setCurColIndex(null); // beforeProcess sheetProcessor.beforeProcess(context); // write head writeHead(useTemplate, sheet, sheetProcessor); // sheet ExcelProcessControllerImpl controller = new ExcelProcessControllerImpl(); int writeRowIndex = sheetProcessor.getStartRowIndex(); boolean isBreak = false; Map<Integer, InnerRow> cacheForTemplateRow = new HashMap<Integer, InnerRow>(); List<?> dataList = sheetProcessor.getDataList(); // if (dataList != null && !dataList.isEmpty()) { for (Object rowData : dataList) { // proc row Row row = sheet.getRow(writeRowIndex); if (row == null) { row = sheet.createRow(writeRowIndex); } InnerRow templateRow = getTemplateRow(cacheForTemplateRow, sheet, sheetProcessor, writeRowIndex); if (templateRow != null) { row.setHeight(templateRow.getHeight()); row.setHeightInPoints(templateRow.getHeightInPoints()); row.setRowStyle(templateRow.getRowStyle()); row.setZeroHeight(templateRow.isZeroHeight()); } context.setCurRow(row); context.setCurRowIndex(writeRowIndex); context.setCurColIndex(null); context.setCurCell(null); // try { controller.reset(); if (sheetProcessor.getRowProcessor() != null) { sheetProcessor.getRowProcessor().process(controller, context, rowData, row); } if (!controller.isDoSkip()) { writeRow(context, templateRow, row, rowData, sheetProcessor); writeRowIndex++; } if (controller.isDoBreak()) { isBreak = true; break; } } catch (RuntimeException e) { if (e instanceof ExcelWriteException) { ExcelWriteException ewe = (ExcelWriteException) e; // ef.setColIndex(null); user may want to set this value, ewe.setRowIndex(writeRowIndex); throw ewe; } else { ExcelWriteException ewe = new ExcelWriteException(e); ewe.setColIndex(null); ewe.setCode(ExcelWriteException.CODE_OF_PROCESS_EXCEPTION); ewe.setRowIndex(writeRowIndex); throw ewe; } } } if (isBreak) { break; } } if (sheetProcessor.getTemplateStartRowIndex() != null && sheetProcessor.getTemplateEndRowIndex() != null) { writeDataValidations(sheet, sheetProcessor); writeStyleAfterFinish(useTemplate, sheet, sheetProcessor); } } catch (RuntimeException e) { sheetProcessor.onException(context, e); } finally { sheetProcessor.afterProcess(context); } } try { workbook.write(outputStream); } catch (IOException e) { throw new RuntimeException(e); } }
From source file:org.jboss.dashboard.displayer.table.ExportTool.java
License:Apache License
public InputStream exportExcel(Table table) { // TODO?: Excel 2010 limits: 1,048,576 rows by 16,384 columns; row width 255 characters if (table == null) throw new IllegalArgumentException("Null table specified!"); int columnCount = table.getColumnCount(); int rowCount = table.getRowCount() + 1; //Include header row int row = 0;//from www . j av a 2 s. co m SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk Map<String, CellStyle> styles = createStyles(wb); Sheet sh = wb.createSheet("Sheet 1"); // General setup sh.setDisplayGridlines(true); sh.setPrintGridlines(false); sh.setFitToPage(true); sh.setHorizontallyCenter(true); PrintSetup printSetup = sh.getPrintSetup(); printSetup.setLandscape(true); // Create header Row header = sh.createRow(row++); header.setHeightInPoints(20f); for (int i = 0; i < columnCount; i++) { Cell cell = header.createCell(i); cell.setCellStyle(styles.get("header")); cell.setCellValue(table.getColumnName(i)); } // Create data rows for (; row < rowCount; row++) { Row _row = sh.createRow(row); for (int cellnum = 0; cellnum < columnCount; cellnum++) { Cell cell = _row.createCell(cellnum); Object value = table.getValueAt(row - 1, cellnum); if (value instanceof Short || value instanceof Long || value instanceof Integer || value instanceof BigInteger) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("integer_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Float || value instanceof Double || value instanceof BigDecimal) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(styles.get("decimal_number_cell")); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("date_cell")); cell.setCellValue((Date) value); } else if (value instanceof Interval) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(((Interval) value).getDescription(LocaleManager.currentLocale())); } else if (value == null) { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(""); } else { cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellStyle(styles.get("text_cell")); cell.setCellValue(value.toString()); } } } // Adjust column size for (int i = 0; i < columnCount; i++) { sh.autoSizeColumn(i); } ByteArrayInputStream bis = null; try { ByteArrayOutputStream bos = new ByteArrayOutputStream(); wb.write(bos); bis = new ByteArrayInputStream(bos.toByteArray()); bos.close(); } catch (IOException e) { log.error("Data export error: ", e); } // Dispose of temporary files backing this workbook on disk if (!wb.dispose()) log.warn("Could not dispose of temporary file associated to data export!"); return bis; }
From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelPrinter.java
License:Open Source License
public void print(final LogicalPageKey logicalPageKey, final LogicalPageBox logicalPage, final TableContentProducer contentProducer, final boolean incremental) { if (workbook == null) { workbook = createWorkbook();//from w w w. j a va2 s.c om initializeStyleProducers(workbook); this.textExtractor = new ExcelTextExtractor(getMetaData(), getFontColorProducer(), workbook.getCreationHelper(), getCellStyleProducer().getFontFactory()); } final int startRow = contentProducer.getFinishedRows(); final int finishRow = contentProducer.getFilledRows(); if (incremental && startRow == finishRow) { return; } if (sheet == null) { // make sure a new patriarch is created if needed. sheet = openSheet(contentProducer.getSheetName()); final SheetPropertySource excelTableContentProducer = (SheetPropertySource) contentProducer; configureSheetProperties(sheet, excelTableContentProducer); // Start a new page. configureSheetPaperSize(sheet, logicalPage.getPageGrid().getPage(0, 0)); configureSheetColumnWidths(sheet, contentProducer.getSheetLayout(), contentProducer.getColumnCount()); } // and finally the content .. final SheetLayout sheetLayout = contentProducer.getSheetLayout(); final int colCount = sheetLayout.getColumnCount(); for (int row = startRow; row < finishRow; row++) { final Row hssfRow = getRowAt(row); final double lastRowHeight = StrictGeomUtility.toExternalValue(sheetLayout.getRowHeight(row)); hssfRow.setHeightInPoints((float) (lastRowHeight)); for (int col = 0; col < colCount; col++) { final CellMarker.SectionType sectionType = contentProducer.getSectionType(row, col); final RenderBox content = contentProducer.getContent(row, col); if (content == null) { final RenderBox backgroundBox = contentProducer.getBackground(row, col); final CellBackground background; if (backgroundBox != null) { background = cellBackgroundProducer.getBackgroundForBox(logicalPage, sheetLayout, col, row, 1, 1, true, sectionType, backgroundBox); } else { background = cellBackgroundProducer.getBackgroundAt(logicalPage, sheetLayout, col, row, true, sectionType); } if (background == null) { if (row == 0 && col == 0) { // create a single cell, so that we dont run into nullpointer inside POI.. getCellAt(col, row); } // An empty cell .. ignore continue; } // A empty cell with a defined background .. final Cell cell = getCellAt(col, row); final CellStyle style = getCellStyleProducer().createCellStyle(null, null, background); if (style != null) { cell.setCellStyle(style); } continue; } if (content.isCommited() == false) { throw new InvalidReportStateException("Uncommited content encountered"); } final long contentOffset = contentProducer.getContentOffset(row, col); final TableRectangle rectangle = sheetLayout.getTableBounds(content.getX(), content.getY() + contentOffset, content.getWidth(), content.getHeight(), null); if (rectangle.isOrigin(col, row) == false) { // A spanned cell .. continue; } final CellBackground fastBackground = cellBackgroundProducer.getBackgroundForBox(logicalPage, sheetLayout, rectangle.getX1(), rectangle.getY1(), rectangle.getColumnSpan(), rectangle.getRowSpan(), false, sectionType, content); // export the cell and all content .. final Cell cell = getCellAt(col, row); final CellStyle style = getCellStyleProducer().createCellStyle(content.getInstanceId(), content.getStyleSheet(), fastBackground); if (style != null) { cell.setCellStyle(style); } if (applyCellValue(content, cell, sheetLayout, rectangle, contentOffset)) { mergeCellRegion(rectangle, row, col, sheetLayout, logicalPage, content, contentProducer); } content.setFinishedTable(true); } } if (incremental == false) { // cleanup .. sheet = null; } }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a data excel worksheet for attendee's informaiton *///from ww w.j a va 2s .c o m private Workbook createAttendeeDataWorksheet(List<SignupMeetingWrapper> wrappers) { String eventTitle = rb.getString("sheet_name_Attendee_schedules", "Attendees' Schedules"); Sheet sheet = wb.createSheet(eventTitle); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); /* Define column numbers and width here */ int numberOfColumn = 13; sheet.setColumnWidth(0, 25 * 256);// event title sheet.setColumnWidth(1, 20 * 256);// attendee display name sheet.setColumnWidth(2, 20 * 256);// attendee user id sheet.setColumnWidth(3, 25 * 256);// attendee user email sheet.setColumnWidth(4, 25 * 256);// site name sheet.setColumnWidth(5, 20 * 256);// appointment start time sheet.setColumnWidth(6, 16 * 256);// duration sheet.setColumnWidth(7, 22 * 256);// #num of attendees sheet.setColumnWidth(8, 25 * 256);// #user comment sheet.setColumnWidth(9, 20 * 256);// event owner sheet.setColumnWidth(10, 20 * 256);// event location sheet.setColumnWidth(11, 20 * 256);// event category sheet.setColumnWidth(12, 20 * 256);// event start time sheet.setColumnWidth(13, 20 * 256);// duration if (wrappers == null) return wb; int rowNum = 0; Cell cell = null; Row titleRow = sheet.createRow(rowNum++); titleRow.setHeightInPoints(rowHigh); for (int i = 0; i <= numberOfColumn; i++) { titleRow.createCell(i).setCellStyle(styles.get("item_leftBold")); } int cellNum = 0; titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_meeting_name", "Event Name")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_user_name", "Attendee Name")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_user_id", "Attendee User Id")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_user_email", "Email")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_site_name", "Site Title")); titleRow.getCell(cellNum++) .setCellValue(rb.getString("wksheet_appointment_start_time", "Appointment Time")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_appointment_duration", "Duration (min)")); titleRow.getCell(cellNum++) .setCellValue(rb.getString("wksheet_num_of_attendees", "#Num Attendees in Slot")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_user_comment", "User Comment")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_organizer", "Organizer")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_location", "Location")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_category", "Category")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_meeting_start_time", "Event Start Time")); titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_meeting_duration", "Event Duration (min)")); for (SignupMeetingWrapper wrp : wrappers) { List<SignupTimeslot> tsItems = wrp.getMeeting().getSignupTimeSlots(); if (tsItems != null) { for (SignupTimeslot tsItem : tsItems) { /*strange thing happen for hibernate, tsItem can be null for mySql 4.x*/ List<SignupAttendee> attendees = tsItem == null ? null : getValidAttendees(tsItem.getAttendees()); if (attendees != null) { // JIRA Signup-204: do we need to do the sorting here for data sheet? //it may affect downstream data decoding by other system. for (SignupAttendee s : attendees) { s.setDisplayName(sakaiFacade.getUserDisplayLastFirstName(s.getAttendeeUserId())); } //Sorting by last-first name JIRA Signup-204 Collections.sort(attendees); for (SignupAttendee att : attendees) { Row row = sheet.createRow(rowNum++); for (int i = 0; i <= numberOfColumn; i++) { row.createCell(i).setCellStyle(styles.get("item_left")); } User attendee = sakaiFacade.getUser(att.getAttendeeUserId()); /* reset */ cellNum = 0; /* meeting title */ cell = row.getCell(cellNum++); cell.setCellValue(wrp.getMeeting().getTitle()); /* attendee name */ cell = row.getCell(cellNum++); //cell.setCellValue(attendee ==null? "--" :attendee.getDisplayName()); cell.setCellValue(attendee == null ? "--" : att.getDisplayName()); cell = row.getCell(cellNum++); cell.setCellValue(attendee == null ? "--" : attendee.getDisplayId()); cell = row.getCell(cellNum++); cell.setCellValue(attendee == null ? "--" : attendee.getEmail()); cell = row.getCell(cellNum++); cell.setCellValue(getSiteTitle(att.getSignupSiteId())); cell = row.getCell(cellNum++); cell.setCellValue(sakaiFacade.getTimeService().newTime(tsItem.getStartTime().getTime()) .toStringLocalFull()); cell = row.getCell(cellNum++); cell.setCellValue(getDurationLength(tsItem.getEndTime(), tsItem.getStartTime()));// minutes cell = row.getCell(cellNum++); cell.setCellValue(getValidAttendees(tsItem.getAttendees()).size()); cell = row.getCell(cellNum++); cell.setCellValue(att.getComments()); cell = row.getCell(cellNum++); cell.setCellValue(sakaiFacade.getUserDisplayName(wrp.getMeeting().getCreatorUserId())); cell = row.getCell(cellNum++); cell.setCellValue(wrp.getMeeting().getLocation()); cell = row.getCell(cellNum++); cell.setCellValue(wrp.getMeeting().getCategory()); cell = row.getCell(cellNum++); cell.setCellValue(sakaiFacade.getTimeService() .newTime(wrp.getMeeting().getStartTime().getTime()).toStringLocalFull()); cell = row.getCell(cellNum++); cell.setCellValue(getDurationLength(wrp.getMeeting().getEndTime(), wrp.getMeeting().getStartTime())); } } } } } return wb; }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a short version excel worksheet *///from w w w. j a v a 2 s .c o m private Workbook createShortVersonWorksheet(List<SignupMeetingWrapper> wrappers) { String eventTitle = rb.getString("event_overview", "Events Overview"); Sheet sheet = wb.createSheet(eventTitle); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 20 * 256); sheet.setColumnWidth(1, 15 * 256); sheet.setColumnWidth(2, 16 * 256); sheet.setColumnWidth(3, 15 * 256); sheet.setColumnWidth(4, 25 * 256); sheet.setColumnWidth(5, 19 * 256); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 0; i <= 6; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(0); titleCell.setCellValue(eventTitle); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$F$1")); // Cureent viewer row Row row = sheet.createRow(2); row.setHeightInPoints(rowHigh); Cell cell = row.createCell(0); cell.setCellValue(rb.getString("event_viewer", "Viewer:")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(1); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getCurrentUserName()); // site title row row = sheet.createRow(3); row.setHeightInPoints(rowHigh); cell = row.createCell(0); cell.setCellValue(rb.getString("event_site_title", "Site Title:")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(1); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getCurrentSiteTitle()); // Table titles th row row = sheet.createRow(5); row.setHeightInPoints(rowHigh); for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tabColNames")); } cell = row.getCell(0); cell.setCellValue(tabTitles_shortVersion[0]); cell = row.getCell(1); cell.setCellValue(tabTitles_shortVersion[1]); cell = row.getCell(2); cell.setCellValue(tabTitles_shortVersion[2]); cell = row.getCell(3); cell.setCellValue(tabTitles_shortVersion[3]); cell = row.getCell(4); cell.setCellValue(tabTitles_shortVersion[4]); cell = row.getCell(5); cell.setCellValue(tabTitles_shortVersion[5]); cell = row.getCell(6); cell.setCellValue(tabTitles_shortVersion[6]); /* table row data */ int rowNum = 6; int seqNum = 1; for (SignupMeetingWrapper wrp : wrappers) { if (wrp.isToDownload()) { row = sheet.createRow(rowNum); int rowHighNum = 1; rowNum++; for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tabItem_fields")); } // event ttile cell = row.getCell(0); cell.setCellStyle(styles.get("item_left_wrap")); cell.setCellValue(wrp.getMeeting().getTitle()); Hyperlink sheetLink = createHelper.createHyperlink(Hyperlink.LINK_DOCUMENT); String validSheetName = CreateValidWorksheetName(wrp.getMeeting().getTitle(), seqNum, true); String hlinkAddr = "'" + validSheetName + "'" + "!A1"; sheetLink.setAddress(hlinkAddr); cell.setHyperlink(sheetLink); cell.setCellStyle(styles.get("hyperLink")); seqNum++; // event owner cell = row.getCell(1); cell.setCellValue(wrp.getCreator()); // event location cell = row.getCell(2); cell.setCellValue(wrp.getMeeting().getLocation()); // event category cell = row.getCell(3); cell.setCellValue(wrp.getMeeting().getCategory()); // event Date cell = row.getCell(4); cell.setCellValue(getShortWeekDayName(wrp.getStartTime()) + ", " + getTime(wrp.getStartTime()).toStringLocalShortDate()); // event time period cell = row.getCell(5); cell.setCellValue(getMeetingPeriodShortVersion(wrp)); // event status cell = row.getCell(6); cell.setCellValue( ExcelPlainTextFormat.convertFormattedHtmlTextToExcelPlaintext(wrp.getAvailableStatus())); } } // end of table line row = sheet.createRow(rowNum); for (int i = 0; i <= 6; i++) { row.createCell(i).setCellStyle(styles.get("tab_endline")); } return wb; }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a full version excel worksheet *///from w w w . j av a 2 s .c o m private void createWorksheet(SignupMeetingWrapper wrapper, int serialNum, boolean hasSerialNum) { String validSheetName = CreateValidWorksheetName(wrapper.getMeeting().getTitle(), serialNum, hasSerialNum); Sheet sheet = wb.createSheet(validSheetName); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 17 * 256); sheet.setColumnWidth(3, 15 * 256); sheet.setColumnWidth(4, 22 * 256); sheet.setColumnWidth(5, 22 * 256); sheet.setColumnWidth(6, 22 * 256); // title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue(wrapper.getMeeting().getTitle()); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); // timezone row Row timezoneRow = sheet.createRow(1); timezoneRow.setHeightInPoints(16); for (int i = 1; i <= 7; i++) { timezoneRow.createCell(i).setCellStyle(styles.get("tabItem_fields")); } Cell timezoneCell = timezoneRow.getCell(2); timezoneCell.setCellValue("(" + rb.getString("event_timezone") + " " + sakaiFacade.getTimeService().getLocalTimeZone().getID() + ")"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$2:$H$2")); // owner row Row row = sheet.createRow(2); row.setHeightInPoints(rowHigh); Cell cell = row.createCell(2); cell.setCellValue(rb.getString("event_owner")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(wrapper.getCreator()); // meeting Date row row = sheet.createRow(3); row.setHeightInPoints(rowHigh); cell = row.createCell(2); cell.setCellValue(rb.getString("event_date")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getTime(wrapper.getStartTime()).toStringLocalDate()); // Time Period row row = sheet.createRow(4); row.setHeightInPoints(rowHigh); cell = row.createCell(2); cell.setCellValue(rb.getString("event_time_period")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getMeetingPeriod(wrapper.getMeeting())); // Sign-up Begins row row = sheet.createRow(5); row.setHeightInPoints(rowHigh); cell = row.createCell(2); cell.setCellValue(rb.getString("event_signup_start")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getTime(wrapper.getMeeting().getSignupBegins()).toStringLocalDate() + ", " + getTime(wrapper.getMeeting().getSignupBegins()).toStringLocalTime()); // Sign-up Ends row row = sheet.createRow(6); row.setHeightInPoints(rowHigh); cell = row.createCell(2); cell.setCellValue(rb.getString("event_signup_deadline")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.createCell(3); cell.setCellStyle(styles.get("item_left")); cell.setCellValue(getTime(wrapper.getMeeting().getSignupDeadline()).toStringLocalDate() + ", " + getTime(wrapper.getMeeting().getSignupDeadline()).toStringLocalTime()); // Available To row row = sheet.createRow(7); for (int i = 1; i <= 5; i++) { row.createCell(i); } cell = row.getCell(2); cell.setCellValue(rb.getString("event_publish_to")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.getCell(3); cell.setCellStyle(styles.get("item_left_wrap")); String availSitesGroups = getAvailableSitesGroups(wrapper.getMeeting()); cell.setCellValue(availSitesGroups); int rownum = getNumRows(availSitesGroups); row.setHeightInPoints(rowHigh * rownum); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$8:$F$8")); // Description row row = sheet.createRow(8); for (int i = 1; i <= 7; i++) { row.createCell(i);// setCellStyle(styles.get("description")); } // cell = row.createCell(2); cell = row.getCell(2); cell.setCellValue(rb.getString("event_description")); cell.setCellStyle(styles.get("item_leftBold")); cell = row.getCell(3); cell.setCellStyle(styles.get("item_left_wrap_top")); String description = wrapper.getMeeting().getDescription(); if (description != null && description.length() > 0) { description = ExcelPlainTextFormat.convertFormattedHtmlTextToExcelPlaintext(description); row.setHeightInPoints(rowHigh * getDescRowNum(description)); } cell.setCellValue(description); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$9:$H$9")); /* add attachment links */ int cur_rowNum = 9; row = sheet.createRow(cur_rowNum); for (int i = 1; i <= 5; i++) { row.createCell(i); } row.setHeightInPoints(rowHigh); cell = row.getCell(2); cell.setCellValue(rb.getString("attachments")); cell.setCellStyle(styles.get("item_leftBold")); List<SignupAttachment> attachs = wrapper.getEventMainAttachments(); if (attachs != null && !attachs.isEmpty()) { for (int i = 0; i < attachs.size(); i++) { SignupAttachment attach = attachs.get(i); if (i > 0) {// start with second attachment cur_rowNum++; row = sheet.createRow(cur_rowNum);// create next // attachment row row.setHeightInPoints(rowHigh); for (int j = 1; j <= 5; j++) { row.createCell(j); } } cell = row.getCell(3); cell.setCellStyle(styles.get("hyperLink")); cell.setCellValue(attach.getFilename()); cell.setHyperlink(setAttachmentURLLinks(attach)); } } else { cell = row.getCell(3); cell.setCellStyle(styles.get("item_left_wrap")); cell.setCellValue(rb.getString("event_no_attachment")); } /* Case: for announcement event */ if (ANNOUNCEMENT.equals(wrapper.getMeeting().getMeetingType())) { row = sheet.createRow(cur_rowNum + 3); row.setHeightInPoints(rowHigh); cell = row.createCell(3); cell.setCellValue(rb.getString("event_is_open_session", "This is an open session meeting. No sign-up is necessary.")); cell.setCellStyle(styles.get("item_leftBold")); return; } /* Case: for group and individual events */ // Table titles row cur_rowNum = cur_rowNum + 2; row = sheet.createRow(cur_rowNum); row.setHeightInPoints(rowHigh); for (int i = 2; i <= 7; i++) { row.createCell(i).setCellStyle(styles.get("tabColNames")); } cell = row.getCell(2); currentTabTitles = isOrganizer(wrapper.getMeeting()) ? tabTitles_Organizor : tabTitles_Participant; cell.setCellValue(currentTabTitles[0]); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (cur_rowNum + 1) + ":$D$" + (cur_rowNum + 1))); cell = row.getCell(4); cell.setCellValue(currentTabTitles[1]); cell = row.getCell(5); cell.setCellValue(currentTabTitles[2]); cell = row.getCell(6); cell.setCellValue(currentTabTitles[3]); cell = row.getCell(7); cell.setCellValue(currentTabTitles[4]); // Table schedule Info int rowNum = cur_rowNum + 1; List<SignupTimeslot> tsItems = wrapper.getMeeting().getSignupTimeSlots(); if (tsItems != null) { for (SignupTimeslot tsItem : tsItems) { /*strange thing happen for hibernate, it can be null for mySql 4.x*/ if (tsItem == null) { continue; } row = sheet.createRow(rowNum); int rowHighNum = 1; rowNum++; for (int i = 1; i <= 7; i++) { row.createCell(i).setCellStyle(styles.get("tabItem_fields")); } // timeslot period cell = row.getCell(2); cell.setCellValue(getTimeSlotPeriod(tsItem, wrapper.getMeeting().isMeetingCrossDays())); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + rowNum + ":$D$" + rowNum));// "$C$11:$D$11" // Max # of participants cell = row.getCell(4); if (tsItem.isUnlimitedAttendee()) cell.setCellValue(rb.getString("event_unlimited")); else if (isOrganizer(wrapper.getMeeting())) { cell.setCellValue(tsItem.getMaxNoOfAttendees()); } else { int availableSpots = getValidAttendees(tsItem.getAttendees()) != null ? tsItem.getMaxNoOfAttendees() - getValidAttendees(tsItem.getAttendees()).size() : tsItem.getMaxNoOfAttendees(); availableSpots = availableSpots < 1 ? 0 : availableSpots; String value = String.valueOf(availableSpots); if (tsItem.isLocked()) value = rb.getString("event_is_locked"); else if (tsItem.isCanceled()) value = rb.getString("event_is_canceled"); cell.setCellValue(value); } List<SignupAttendee> attendees = getValidAttendees(tsItem.getAttendees()); // attendee names cell = row.getCell(5); String aNames = rb.getString("event_show_no_attendee_info"); if (isDisplayNames(wrapper.getMeeting())) { if (attendees != null && attendees.size() > rowHighNum) { rowHighNum = attendees.size(); } aNames = getNames(attendees, true); } if (tsItem.isCanceled() && isOrganizer(wrapper.getMeeting())) { aNames = rb.getString("event_is_canceled"); } cell.setCellValue(aNames); cell.setCellStyle(styles.get("attendee_layout")); // attendee userids // without completely reformatting the way the table is constructed, this gives the userids in a separate column cell = row.getCell(6); String aIds = rb.getString("event_show_no_attendee_info"); if (isDisplayNames(wrapper.getMeeting())) { if (attendees != null && attendees.size() > rowHighNum) { rowHighNum = attendees.size(); } aIds = getIds(attendees); } if (tsItem.isCanceled() && isOrganizer(wrapper.getMeeting())) { aIds = rb.getString("event_is_canceled"); } cell.setCellValue(aIds); cell.setCellStyle(styles.get("attendee_layout")); // waiters cell = row.getCell(7); String fieldValue = ""; if (isOrganizer(wrapper.getMeeting())) { List<SignupAttendee> waiters = tsItem.getWaitingList(); if (waiters != null && waiters.size() > rowHighNum) { rowHighNum = waiters.size(); } fieldValue = getNames(waiters, false); } else { fieldValue = getYourStatus(tsItem); } cell.setCellValue(fieldValue); cell.setCellStyle(styles.get("attendee_layout")); // set row high row.setHeightInPoints(rowHigh * rowHighNum); } } // end of table line row = sheet.createRow(rowNum); for (int i = 2; i <= 7; i++) { row.createCell(i).setCellStyle(styles.get("tab_endline")); } /* process attendee's comments */ rowNum = rowNum + 2; // Comment Title row Row commentsRow = sheet.createRow(rowNum); commentsRow.setHeightInPoints(25); for (int i = 1; i <= 7; i++) { commentsRow.createCell(i).setCellStyle(styles.get("commentTitle")); } Cell commentsCell = commentsRow.getCell(2); commentsCell.setCellValue(rb.getString("event_comments_title", "Participant's Comments")); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (rowNum + 1) + ":$H$" + (rowNum + 1))); // separate line rowNum++; row = sheet.createRow(rowNum); for (int i = 2; i <= 4; i++) { row.createCell(i).setCellStyle(styles.get("tab_endline")); } rowNum++; ; boolean hasComment = false; if (tsItems != null) { for (SignupTimeslot ts : tsItems) { /*strange thing happen for hibernate, it can be null for mySql 4.x*/ List<SignupAttendee> attendees = ts != null ? getValidAttendees(ts.getAttendees()) : null; if (attendees != null) { for (SignupAttendee att : attendees) { if (isOrganizer(wrapper.getMeeting()) || isViewerSelf(att)) { String comment = att.getComments(); if (comment != null && comment.trim().length() > 0) { row = sheet.createRow(rowNum++); for (int i = 1; i <= 7; i++) { row.createCell(i); } cell = row.getCell(2); cell.setCellValue(sakaiFacade.getUserDisplayName(att.getAttendeeUserId()) + ":"); cell.setCellStyle(styles.get("item_leftBold")); cell = row.getCell(3); cell.setCellStyle(styles.get("item_left_wrap_top")); comment = ExcelPlainTextFormat.convertFormattedHtmlTextToExcelPlaintext(comment); row.setHeightInPoints(rowHigh * getDescRowNum(comment)); cell.setCellValue(comment); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + rowNum + ":$H$" + rowNum)); rowNum++;// one row space between comment hasComment = true; } } } } } } if (!hasComment) { row = sheet.createRow(rowNum); row.createCell(2); cell = row.getCell(2); cell.setCellValue(rb.getString("event_no_comments", "There is no comments written by participants.")); cell.setCellStyle(styles.get("item_leftBold")); } }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a data excel worksheet for attendee's informaiton, which is in the gradebook import format */// w ww. j av a2 s .c o m private Workbook createAttendanceDataWorksheet(List<SignupMeetingWrapper> wrappers) { String eventTitle = rb.getString("sheet_name_Attendance_data", "Attendance"); Sheet sheet = wb.createSheet(eventTitle); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //Map to store all data Map<String, List<Integer>> m = new HashMap<String, List<Integer>>(); if (wrappers == null) return wb; /* Define column numbers and width here */ int numberOfColumn = wrappers.size() + 1; sheet.setColumnWidth(0, 25 * 256);// event title int rowNum = 0; Cell cell = null; Row titleRow = sheet.createRow(rowNum++); titleRow.setHeightInPoints(rowHigh); for (int i = 0; i <= numberOfColumn; i++) { titleRow.createCell(i).setCellStyle(styles.get("item_leftBold")); } int cellNum = 0; titleRow.getCell(cellNum++).setCellValue(rb.getString("wksheet_attendance_UserID", "UserID")); int index = -1; for (SignupMeetingWrapper wrp : wrappers) { if (wrp.getMeeting().isAllowAttendance()) { index++; titleRow.getCell(cellNum++).setCellValue(wrp.getMeeting().getTitle()); List<SignupTimeslot> tsItems = wrp.getMeeting().getSignupTimeSlots(); if (tsItems != null) { for (SignupTimeslot tsItem : tsItems) { /*strange thing happen for hibernate, tsItem can be null for mySql 4.x*/ List<SignupAttendee> attendees = tsItem == null ? null : getValidAttendees(tsItem.getAttendees()); if (attendees != null) { for (SignupAttendee att : attendees) { //If attended then score is 1 else 0 Integer attended = 0; if (att.isAttended()) { attended = 1; } User attendee = sakaiFacade.getUser(att.getAttendeeUserId()); String attendeeEID = attendee.getEid(); if (m.containsKey(attendeeEID)) { //Integer value=m.get(attendeeEID).get(index); List<Integer> attendanceList = m.get(attendeeEID); if (attendanceList.get(index) != null) { attendanceList.set(index, attendanceList.get(index) + attended); } else { attendanceList.set(index, attended); } } else { List<Integer> newList = Arrays.asList(new Integer[wrappers.size()]); newList.set(index, attended); m.put(attendeeEID, newList); } } } //Recording attendance for waitlisted user Signup-106 List<SignupAttendee> waitingList = tsItem == null ? null : tsItem.getWaitingList(); if (waitingList != null) { //For waitlisted user, if added it is scored, if not ignored Integer attended = 1; for (SignupAttendee wt : waitingList) { if (wt.isAttended()) { User attendee = sakaiFacade.getUser(wt.getAttendeeUserId()); String attendeeEID = attendee.getEid(); if (m.containsKey(attendeeEID)) { //Integer value=m.get(attendeeEID).get(index); List<Integer> attendanceList = m.get(attendeeEID); if (attendanceList.get(index) != null) { attendanceList.set(index, attendanceList.get(index) + attended); } else { attendanceList.set(index, attended); } } else { List<Integer> newList = Arrays.asList(new Integer[wrappers.size()]); newList.set(index, attended); m.put(attendeeEID, newList); } } } } } } } } //populate the map into workbook Iterator it = m.entrySet().iterator(); while (it.hasNext()) { //key value pair of Map Map.Entry pairs = (Map.Entry) it.next(); //create new row Row row = sheet.createRow(rowNum++); for (int i = 0; i <= numberOfColumn; i++) { row.createCell(i).setCellStyle(styles.get("item_left")); } cellNum = 0; row.getCell(cellNum++).setCellValue((String) pairs.getKey()); List<Integer> attendanceList = (List) pairs.getValue(); for (int i = 0; i < attendanceList.size(); i++) { if (attendanceList.get(i) != null) { row.getCell(cellNum++).setCellValue(attendanceList.get(i)); } else { //empty value if null row.getCell(cellNum++).setCellValue(""); } } } return wb; }