Example usage for org.apache.poi.ss.usermodel Sheet addMergedRegion

List of usage examples for org.apache.poi.ss.usermodel Sheet addMergedRegion

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. addMergedRegion

Introduction

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

Prototype

int addMergedRegion(CellRangeAddress region);

Source Link

Document

Adds a merged region of cells (hence those cells form one)

Usage

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

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataTable table, int columnCount,
        String facetType) {//from   ww  w . j ava 2  s  .c o m
    Map<String, UIComponent> map = table.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                sheetRowIndex, //last row  (0-based)
                0, //first column (0-based)
                columnCount //last column  (0-based)
        ));

    }
}

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

License:Apache License

protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }//from   w w w .j av a 2  s . co m
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getPhysicalNumberOfRows() > 0 ? sheet.getLastRowNum() + 1 : 0;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {

                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(value);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                        sheetRowIndex + rowSpan - 1, //last row  (0-based)
                                        i, //first column (0-based)
                                        i //last column  (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, //first row (0-based)
                                    sheetRowIndex, //last row  (0-based)
                                    i, //first column (0-based)
                                    i + colSpan - 1 //last column  (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);

                    }
                    i++;
                }
            }

        }
    }

}

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

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataTable table, int columnCount,
        String facetType) {//ww w .  j  a va2  s  .co m
    Map<String, UIComponent> map = table.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                sheetRowIndex, // last row (0-based)
                0, // first column (0-based)
                columnCount + 1 // last column (0-based)
        ));

    }
}

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

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, SubTable table, int columnCount,
        String facetType) {//from   w  w w  . j  av a 2s  .  c  o  m
    Map<String, UIComponent> map = table.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else if (component instanceof UIPanel) {
            String header = "";
            for (UIComponent child : component.getChildren()) {
                headerValue = exportValue(context, child);
                header = header + headerValue;
            }
            headerValue = header;
        } else {
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                sheetRowIndex, // last row (0-based)
                0, // first column (0-based)
                columnCount // last column (0-based)
        ));

    }
}

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

License:Apache License

protected void tableFacet(FacesContext context, Sheet sheet, DataList list, String facetType) {
    Map<String, UIComponent> map = list.getFacets();
    UIComponent component = map.get(facetType);
    if (component != null) {
        String headerValue = null;
        if (component instanceof HtmlCommandButton) {
            headerValue = exportValue(context, component);
        } else if (component instanceof HtmlCommandLink) {
            headerValue = exportValue(context, component);
        } else {/*from www .j a  v  a  2 s  .c o m*/
            headerValue = exportFacetValue(context, component);
        }

        int sheetRowIndex = sheet.getLastRowNum() + 1;
        Row row = sheet.createRow(sheetRowIndex);
        Cell cell = row.createCell((short) 0);
        cell.setCellValue(headerValue);
        cell.setCellStyle(facetStyle);

        sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                sheetRowIndex, // last row (0-based)
                0, // first column (0-based)
                1 // last column (0-based)
        ));

    }
}

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

License:Apache License

protected void tableColumnGroup(Sheet sheet, DataTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }// ww w  .  j av  a2s . c om
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {
                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellValue(value);
                                cell.setCellStyle(facetStyle);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                        sheetRowIndex + rowSpan - 1, // last row (0-based)
                                        i, // first column (0-based)
                                        i // last column (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);

                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellValue(value);
                            cell.setCellStyle(facetStyle);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                    sheetRowIndex, // last row (0-based)
                                    i, // first column (0-based)
                                    i + colSpan - 1 // last column (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);
                    }

                    i++;
                }
            }

        }

    }
}

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

License:Apache License

protected void tableColumnGroup(Sheet sheet, SubTable table, String facetType) {
    ColumnGroup cg = table.getColumnGroup(facetType);
    List<UIComponent> headerComponentList = null;
    if (cg != null) {
        headerComponentList = cg.getChildren();
    }//ww w .java 2  s  . c o  m
    if (headerComponentList != null) {
        for (UIComponent component : headerComponentList) {
            if (component instanceof org.primefaces.component.row.Row) {
                org.primefaces.component.row.Row row = (org.primefaces.component.row.Row) component;
                int sheetRowIndex = sheet.getLastRowNum() + 1;
                Row xlRow = sheet.createRow(sheetRowIndex);
                int i = 0;
                for (UIComponent rowComponent : row.getChildren()) {
                    UIColumn column = (UIColumn) rowComponent;
                    String value = null;
                    if (facetType.equalsIgnoreCase("header")) {
                        value = column.getHeaderText();
                    } else {
                        value = column.getFooterText();
                    }
                    int rowSpan = column.getRowspan();
                    int colSpan = column.getColspan();

                    Cell cell = xlRow.getCell(i);

                    if (rowSpan > 1 || colSpan > 1) {

                        if (rowSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            Boolean rowSpanFlag = false;
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    rowSpanFlag = true;
                                }

                            }
                            if (!rowSpanFlag) {
                                cell.setCellStyle(cellStyle);
                                cell.setCellValue(value);
                                sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                        sheetRowIndex + rowSpan - 1, // last row (0-based)
                                        i, // first column (0-based)
                                        i // last column (0-based)
                                ));
                            }
                        }
                        if (colSpan > 1) {
                            cell = xlRow.createCell((short) i);
                            for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                                CellRangeAddress merged = sheet.getMergedRegion(j);
                                if (merged.isInRange(sheetRowIndex, i)) {
                                    cell = xlRow.createCell((short) ++i);
                                }
                            }
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(value);
                            sheet.addMergedRegion(new CellRangeAddress(sheetRowIndex, // first row (0-based)
                                    sheetRowIndex, // last row (0-based)
                                    i, // first column (0-based)
                                    i + colSpan - 1 // last column (0-based)
                            ));
                            i = i + colSpan - 1;
                        }
                    } else {
                        cell = xlRow.createCell((short) i);
                        for (int j = 0; j < sheet.getNumMergedRegions(); j++) {
                            CellRangeAddress merged = sheet.getMergedRegion(j);
                            if (merged.isInRange(sheetRowIndex, i)) {
                                cell = xlRow.createCell((short) ++i);
                            }
                        }
                        cell.setCellValue(value);
                        cell.setCellStyle(facetStyle);

                    }
                    i++;
                }
            }

        }
    }

}

From source file:org.projectforge.excel.ExportSheet.java

License:Open Source License

private static Row copyRow(Sheet worksheet, int rowNum) {
    Row sourceRow = worksheet.getRow(rowNum);

    //Save the text of any formula before they are altered by row shifting
    String[] formulasArray = new String[sourceRow.getLastCellNum()];
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        if (sourceRow.getCell(i) != null && sourceRow.getCell(i).getCellType() == Cell.CELL_TYPE_FORMULA)
            formulasArray[i] = sourceRow.getCell(i).getCellFormula();
    }//w  w w.java  2  s . c o  m

    worksheet.shiftRows(rowNum, worksheet.getLastRowNum(), 1);
    Row newRow = sourceRow; //Now sourceRow is the empty line, so let's rename it
    sourceRow = worksheet.getRow(rowNum + 1); //Now the source row is at rowNum+1

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        Cell oldCell = sourceRow.getCell(i);
        Cell newCell;

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            continue;
        } else {
            newCell = newRow.createCell(i);
        }

        // Copy style from old cell and apply to new cell
        CellStyle newCellStyle = worksheet.getWorkbook().createCellStyle();
        newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
        newCell.setCellStyle(newCellStyle);

        // If there is a cell comment, copy
        if (oldCell.getCellComment() != null) {
            newCell.setCellComment(oldCell.getCellComment());
        }

        // If there is a cell hyperlink, copy
        if (oldCell.getHyperlink() != null) {
            newCell.setHyperlink(oldCell.getHyperlink());
        }

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());

        // Set the cell data value
        switch (oldCell.getCellType()) {
        case Cell.CELL_TYPE_BLANK:
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case Cell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(formulasArray[i]);
            break;
        case Cell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getRichStringCellValue());
            break;
        default:
            break;
        }
    }

    // If there are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())),
                    cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    return newRow;
}

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  om*/
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  w w . java 2  s .c  om
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"));
    }

}