List of usage examples for org.apache.poi.ss.usermodel Sheet setColumnWidth
void setColumnWidth(int columnIndex, int width);
The maximum column width for an individual cell is 255 characters.
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 ww w . ja v a 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.hlc.utility.excel.ExcelOutputHandler.java
License:Apache License
/** * Export excel.//from w ww . jav a2 s . co m * * @param type the type * @param dataSet the data set * @param out the out */ @SuppressWarnings({ "rawtypes", "unchecked" }) public void exportExcel(Class<?> type, Collection<?> dataSet, OutputStream out) { Excel excelAnn = type.getAnnotation(Excel.class); if (excelAnn == null) { throw new ExcelException("The Class <" + type + "> did not Excel"); } if (dataSet == null || dataSet.size() == 0) { dataSet = new ArrayList(); } String title = excelAnn.value(); if (StringUtils.isEmpty(title)) { title = "Sheet"; } List<String> exportFieldTitle = new ArrayList<String>(); List<Integer> exportFieldWidth = new ArrayList<Integer>(); List<Method> methodObj = new ArrayList<Method>(); Map<String, TypeHandler> converters = new HashMap<String, TypeHandler>(); try { // Step1 ?? Field fileds[] = type.getDeclaredFields(); for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; ExcelColumn column = field.getAnnotation(ExcelColumn.class); if (column != null) { exportFieldTitle.add(column.value()); exportFieldWidth.add(column.width()); Method getMethod = ReflectionUtils.getValueMethod(field, type); methodObj.add(getMethod); if (column.converter() != TypeHandler.class) { converters.put(getMethod.getName().toString(), column.converter().newInstance()); } else { converters.put(getMethod.getName().toString(), TypeHandlerFactory.getHandler(field.getType())); } } } // Step2 Excel Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(title); int index = 0; Row row = sheet.createRow(index); for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) { Cell cell = row.createCell(i); RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i)); cell.setCellValue(text); } for (int i = 0; i < exportFieldWidth.size(); i++) { sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i)); } Iterator<?> its = dataSet.iterator(); while (its.hasNext()) { index++; row = sheet.createRow(index); Object t = its.next(); for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) { Cell cell = row.createCell(k); Method getMethod = methodObj.get(k); Object value = getMethod.invoke(t, new Object[] {}); if (value == null) { cell.setCellValue(""); } TypeHandler conveter = converters.get(getMethod.getName()); if (conveter == null) { continue; } cell.setCellValue(conveter.typeToString(value)); } } workbook.write(out); } catch (Exception e) { throw new ExcelException("Excel processing error?", e); } }
From source file:org.netxilia.impexp.impl.ExcelExportService.java
License:Open Source License
@Override public void exportSheetTo(INetxiliaSystem workbookProcessor, SheetFullName sheetName, OutputStream out, IProcessingConsole console)//ww w.j a va 2 s. c o m throws ExportException, NetxiliaResourceException, NetxiliaBusinessException { Workbook poiWorkbook = new HSSFWorkbook(); Sheet poiSheet = poiWorkbook.createSheet(sheetName.getSheetName()); ISheet nxSheet = null; try { nxSheet = workbookProcessor.getWorkbook(sheetName.getWorkbookId()).getSheet(sheetName.getSheetName()); SheetData nxSheetData = nxSheet.receiveSheet().getNonBlocking(); for (AreaReference area : nxSheetData.getSpans()) { poiSheet.addMergedRegion(new CellRangeAddress(area.getFirstRowIndex(), area.getLastRowIndex(), area.getFirstColumnIndex(), area.getLastColumnIndex())); } // cells Matrix<CellData> nxCells = nxSheet.receiveCells(AreaReference.ALL).getNonBlocking(); int rowIndex = 0; for (List<CellData> nxRow : nxCells.getRows()) { Row poiRow = poiSheet.createRow(rowIndex); for (CellData nxCell : nxRow) { if (nxCell != null) { Cell poiCell = poiRow.createCell(nxCell.getReference().getColumnIndex()); try { copyCellValue(nxCell, poiCell); } catch (Exception ex) { if (console != null) { console.println("Error " + nxCell.getReference() + ":" + ex); } } } } rowIndex++; } // columns List<ColumnData> nxColumns = nxSheet.receiveColumns(Range.ALL).getNonBlocking(); for (int c = 0; c < nxColumns.size(); ++c) { ColumnData col = nxColumns.get(c); if (col.getWidth() > 0) { poiSheet.setColumnWidth(c, PoiUtils.pixel2WidthUnits(col.getWidth())); } PoiUtils.netxiliaStyle2Poi(col.getStyles(), poiSheet.getWorkbook(), poiSheet.getColumnStyle(c)); } } catch (StorageException e) { throw new ExportException(e); } // close the workbook try { poiWorkbook.write(out); } catch (IOException e) { throw new ExportException(e); } }
From source file:org.opentestsystem.delivery.testreg.rest.ExcelBasedTemplateCreator.java
License:Open Source License
private void setColumnStyles(int totalColumns, CellStyle style, Sheet sheet) { for (int columnNo = 0; columnNo < totalColumns; columnNo++) { sheet.setDefaultColumnStyle(columnNo, style); sheet.setColumnHidden(columnNo, false); sheet.setColumnWidth(columnNo, DEFAULT_COLUMN_WIDTH); }//from w w w . j a va2s . c o m }
From source file:org.pentaho.reporting.engine.classic.core.modules.output.table.xls.helper.ExcelPrinterBase.java
License:Open Source License
protected void configureSheetColumnWidths(Sheet sheet, SlimSheetLayout sheetLayout, int columnCount) { // Set column widths .. for (int col = 0; col < columnCount; col++) { final double cellWidth = StrictGeomUtility.toExternalValue(sheetLayout.getCellWidth(col, col + 1)); final double poiCellWidth = (cellWidth * getScaleFactor()); sheet.setColumnWidth(col, Math.min(255 * 256, (int) poiCellWidth)); }/* ww w . j ava 2 s. c o m*/ }
From source file:org.phenotips.export.internal.SpreadsheetExporter.java
License:Open Source License
protected void write(DataSection section, Sheet sheet) { DataCell[][] cells = section.getMatrix(); Styler styler = new Styler(); Row row;//from w w w . j a va 2s .co m for (Integer y = 0; y <= section.getMaxY(); y++) { row = sheet.createRow(y); Integer maxLines = 0; for (Integer x = 0; x <= section.getMaxX(); x++) { DataCell dataCell = cells[x][y]; if (dataCell == null) { continue; } Cell cell = row.createCell(x); cell.setCellValue(dataCell.getValue()); styler.style(dataCell, cell, this.wBook); if (dataCell.getNumberOfLines() != null) { maxLines = maxLines < dataCell.getNumberOfLines() ? dataCell.getNumberOfLines() : maxLines; } } if (maxLines > 1) { Integer height = maxLines * 400; row.setHeight(height.shortValue()); } } for (int col = 0; section.getMaxX() >= col; col++) { sheet.autoSizeColumn(col); if (sheet.getColumnWidth(col) > (DataToCellConverter.charactersPerLine * 210)) { sheet.setColumnWidth(col, DataToCellConverter.charactersPerLine * 210); } } /** Merging has to be done after autosizing because otherwise autosizing breaks */ for (Integer y = 0; y <= section.getMaxY(); y++) { for (Integer x = 0; x <= section.getMaxX(); x++) { DataCell dataCell = cells[x][y]; if (dataCell != null && dataCell.getMergeX() != null) { sheet.addMergedRegion(new CellRangeAddress(y, y, x, x + dataCell.getMergeX())); } /* * No longer will be merging cells on the Y axis, but keep this code for future reference. if * (dataCell.getYBoundry() != null) { sheet.addMergedRegion(new CellRangeAddress(dataCell.y, * dataCell.getYBoundry(), dataCell.x, dataCell.x)); } */ } } }
From source file:org.riflemansd.businessprofit.excel.ExcelExampleFont.java
License:Open Source License
public static void main(String[] args) { // create a new file FileOutputStream out = null;/*from www.j ava 2 s . co m*/ try { out = new FileOutputStream("workbook.xls"); } catch (FileNotFoundException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } // create a new workbook Workbook wb = new HSSFWorkbook(); // create a new sheet Sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 3 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); CellStyle cs3 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); //set font 1 to 12 point type f.setFontHeightInPoints((short) 12); //make it blue f.setColor((short) 0xc); // make it bold //arial is the default font f.setBoldweight(Font.BOLDWEIGHT_BOLD); //set font 2 to 10 point type f2.setFontHeightInPoints((short) 10); //make it red f2.setColor((short) Font.COLOR_RED); //make it bold f2.setBoldweight(Font.BOLDWEIGHT_BOLD); f2.setStrikeout(true); //set cell stlye cs.setFont(f); //set the cell format cs.setDataFormat(df.getFormat("#,##0.0")); //set a thin border cs2.setBorderBottom(cs2.BORDER_THIN); //fill w fg fill color cs2.setFillPattern((short) CellStyle.SOLID_FOREGROUND); //set the cell format to text see DataFormat for a full list cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); // set the font cs2.setFont(f2); // set the sheet name in Unicode wb.setSheetName(0, "\u0422\u0435\u0441\u0442\u043E\u0432\u0430\u044F " + "\u0421\u0442\u0440\u0430\u043D\u0438\u0447\u043A\u0430"); // in case of plain ascii // wb.setSheetName(0, "HSSF Test"); // create a sheet with 30 rows (0-29) int rownum; for (rownum = (short) 0; rownum < 30; rownum++) { // create a row r = s.createRow(rownum); // on every other row if ((rownum % 2) == 0) { // make the row height bigger (in twips - 1/20 of a point) r.setHeight((short) 0x249); } //r.setRowNum(( short ) rownum); // create 10 cells (0-9) (the += 2 becomes apparent later for (short cellnum = (short) 0; cellnum < 10; cellnum += 2) { // create a numeric cell c = r.createCell(cellnum); // do some goofy math to demonstrate decimals c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); String cellValue; // create a string cell (see why += 2 in the c = r.createCell((short) (cellnum + 1)); // on every other row if ((rownum % 2) == 0) { // set this cell to the first cell style we defined c.setCellStyle(cs); // set the cell's string value to "Test" c.setCellValue("Test"); } else { c.setCellStyle(cs2); // set the cell's string value to "\u0422\u0435\u0441\u0442" c.setCellValue("\u0422\u0435\u0441\u0442"); } // make this column a bit wider s.setColumnWidth((short) (cellnum + 1), (short) ((50 * 8) / ((double) 1 / 20))); } } //draw a thick black border on the row at the bottom using BLANKS // advance 2 rows rownum++; rownum++; r = s.createRow(rownum); // define the third style to be the default // except with a thick black border at the bottom cs3.setBorderBottom(cs3.BORDER_THICK); //create 50 cells for (short cellnum = (short) 0; cellnum < 50; cellnum++) { //create a blank type cell (no value) c = r.createCell(cellnum); // set it to the thick black border style c.setCellStyle(cs3); } //end draw thick black border // demonstrate adding/naming and deleting a sheet // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); //end deleted sheet try { // write the workbook to the output stream // close our file (don't blow out our file handles wb.write(out); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } try { out.close(); } catch (IOException ex) { Logger.getLogger(ExcelExampleFont.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:org.sakaiproject.signup.tool.downloadEvents.EventWorksheet.java
License:Educational Community License
/** * Create a data excel worksheet for attendee's informaiton *//*from w ww . ja v a 2 s .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 a2s.co 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 ww w . ja v a 2s . co 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")); } }