Example usage for org.apache.poi.ss.usermodel PrintSetup setLandscape

List of usage examples for org.apache.poi.ss.usermodel PrintSetup setLandscape

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. PrintSetup
  6. setLandscape

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel PrintSetup setLandscape.

Prototype

void setLandscape(boolean ls);

Source Link

Document

Set whether to print in landscape

Usage

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  w ww. j ava 2s  .c o  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.ExcelPrintSetupFactory.java

License:Open Source License

/**
 * Performs the page setup and searches a matching page format for the report.
 *
 * @param printSetup/*from   w w w  .j av a2  s.  com*/
 *          the print setup object of the current sheet.
 * @param pageformat
 *          the pageformat defined for the report.
 * @param paperdef
 *          the excel paper size property (may be null).
 * @param paperOrientation
 *          the paper orientation, either "Landscape" or "Portrait"
 */
public static void performPageSetup(final PrintSetup printSetup, final PhysicalPageBox pageformat,
        final String paperdef, final String paperOrientation) {
    short pageCode = ExcelPrintSetupFactory.parsePaperSizeProperty(paperdef);
    if (pageCode == -1) {
        pageCode = ExcelPrintSetupFactory.computePaperSize(pageformat);
    }
    if (pageCode != -1) {
        printSetup.setPaperSize(pageCode);
    }
    if (paperOrientation != null && paperOrientation.equals("auto") == false) {
        printSetup.setLandscape("Landscape".equalsIgnoreCase(paperOrientation));
    } else {
        final boolean landscape = pageformat.getWidth() > pageformat.getHeight();
        printSetup.setLandscape(landscape);
    }
}

From source file:org.primefaces.component.export.ExcelExporter.java

License:Open Source License

protected void applyOptions(Workbook wb, DataTable table, Sheet sheet, ExporterOptions options) {
    facetStyle = wb.createCellStyle();//from w  ww  . j a  va  2s .co  m
    facetStyle.setAlignment((short) CellStyle.ALIGN_CENTER);
    facetStyle.setVerticalAlignment((short) CellStyle.VERTICAL_CENTER);
    facetStyle.setWrapText(true);
    applyFacetOptions(wb, options, facetStyle);

    cellStyle = wb.createCellStyle();
    cellStyle.setAlignment((short) CellStyle.ALIGN_LEFT);
    applyCellOptions(wb, options, cellStyle);

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);
}

From source file:org.primefaces.extensions.component.exporter.ExcelExporter.java

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();// w  ww. ja  v a  2s .co  m
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();

    facetStyleLeftAlign = wb.createCellStyle();
    facetStyleCenterAlign = wb.createCellStyle();
    facetStyleRightAlign = wb.createCellStyle();
    cellStyleLeftAlign = wb.createCellStyle();
    cellStyleCenterAlign = wb.createCellStyle();
    cellStyleRightAlign = wb.createCellStyle();

    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

            if (table.getHeader() != null && !subTable) {
                tableFacet(context, sheet, table, columnsCount, "header");

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable)
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}

From source file:org.primefaces.extensions.showcase.util.ExcelCustomExporter.java

License:Apache License

@Override
public void export(ActionEvent event, String tableId, FacesContext context, String filename, String tableTitle,
        boolean pageOnly, boolean selectionOnly, String encodingType, MethodExpression preProcessor,
        MethodExpression postProcessor, boolean subTable) throws IOException {

    wb = new XSSFWorkbook();
    String safeName = WorkbookUtil.createSafeSheetName(filename);
    Sheet sheet = wb.createSheet(safeName);

    cellStyle = wb.createCellStyle();// w  w  w. jav  a 2s  .co m
    facetStyle = wb.createCellStyle();
    titleStyle = wb.createCellStyle();
    createCustomFonts();

    int maxColumns = 0;
    StringTokenizer st = new StringTokenizer(tableId, ",");
    while (st.hasMoreElements()) {
        String tableName = (String) st.nextElement();
        UIComponent component = SearchExpressionFacade.resolveComponent(context, event.getComponent(),
                tableName);
        if (component == null) {
            throw new FacesException("Cannot find component \"" + tableName + "\" in view.");
        }
        if (!(component instanceof DataTable || component instanceof DataList)) {
            throw new FacesException("Unsupported datasource target:\"" + component.getClass().getName()
                    + "\", exporter must target a PrimeFaces DataTable/DataList.");
        }

        DataList list = null;
        DataTable table = null;
        int cols = 0;
        if (preProcessor != null) {
            preProcessor.invoke(context.getELContext(), new Object[] { wb });
        }
        if (tableTitle != null && !tableTitle.isEmpty() && !tableId.contains("" + ",")) {
            Row titleRow = sheet.createRow(sheet.getLastRowNum());
            int cellIndex = titleRow.getLastCellNum() == -1 ? 0 : titleRow.getLastCellNum();
            Cell cell = titleRow.createCell(cellIndex);
            cell.setCellValue(new XSSFRichTextString(tableTitle));
            Font titleFont = wb.createFont();
            titleFont.setBold(true);
            titleStyle.setFont(titleFont);
            cell.setCellStyle(titleStyle);
            sheet.createRow(sheet.getLastRowNum() + 3);

        }
        if (component instanceof DataList) {
            list = (DataList) component;

            if (list.getHeader() != null) {
                tableFacet(context, sheet, list, "header");
            }
            if (pageOnly) {
                exportPageOnly(context, list, sheet);
            } else {
                exportAll(context, list, sheet);
            }
            cols = list.getRowCount();
        } else {

            table = (DataTable) component;
            int columnsCount = getColumnsCount(table);

            if (table.getHeader() != null && !subTable) {
                tableFacet(context, sheet, table, columnsCount, "header");

            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "header");
            }

            addColumnFacets(table, sheet, ColumnType.HEADER);

            if (pageOnly) {
                exportPageOnly(context, table, sheet);
            } else if (selectionOnly) {
                exportSelectionOnly(context, table, sheet);
            } else {
                exportAll(context, table, sheet, subTable);
            }

            if (table.hasFooterColumn() && !subTable) {
                addColumnFacets(table, sheet, ColumnType.FOOTER);
            }
            if (!subTable) {
                tableColumnGroup(sheet, table, "footer");
            }
            table.setRowIndex(-1);
            if (postProcessor != null) {
                postProcessor.invoke(context.getELContext(), new Object[] { wb });
            }
            cols = table.getColumnsCount();

            if (maxColumns < cols) {
                maxColumns = cols;
            }
        }
        sheet.createRow(sheet.getLastRowNum() + Integer.parseInt(datasetPadding));
    }

    if (!subTable) {
        for (int i = 0; i < maxColumns; i++) {
            sheet.autoSizeColumn((short) i);
        }
    }

    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
    sheet.setPrintGridlines(true);

    writeExcelToResponse(context.getExternalContext(), wb, filename);

}

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  w  w . ja v  a  2  s  .c  om
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
 *///  ww 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
 *///  w  ww  .j  a  v a 2 s  . com
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 w  w  . j  a  v a  2s.c om*/
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;
}

From source file:org.unitime.timetable.export.XLSPrinter.java

License:Apache License

public XLSPrinter(OutputStream output, boolean checkLast) {
    iOutput = output;/*from w ww  . ja v  a 2  s  . com*/
    iCheckLast = checkLast;
    iWorkbook = new HSSFWorkbook();
    iSheet = iWorkbook.createSheet();
    iSheet.setDisplayGridlines(false);
    iSheet.setPrintGridlines(false);
    iSheet.setFitToPage(true);
    iSheet.setHorizontallyCenter(true);
    PrintSetup printSetup = iSheet.getPrintSetup();
    printSetup.setLandscape(true);
    iSheet.setAutobreaks(true);
    printSetup.setFitHeight((short) 1);
    printSetup.setFitWidth((short) 1);
    iStyles = new HashMap<String, CellStyle>();

    CellStyle style;

    style = iWorkbook.createCellStyle();
    style.setBorderBottom(BorderStyle.THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFont(getFont(true, false, false, Color.BLACK));
    style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
    style.setWrapText(true);
    iStyles.put("header", style);

    style = iWorkbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.LEFT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFont(getFont(false, false, false, Color.BLACK));
    style.setWrapText(true);
    iStyles.put("plain", style);

    style = iWorkbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.RIGHT);
    style.setVerticalAlignment(VerticalAlignment.TOP);
    style.setFont(getFont(false, false, false, Color.BLACK));
    iStyles.put("number", style);
}