Example usage for org.apache.poi.ss.usermodel Sheet 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.efaps.esjp.common.file.FileUtil_Base.java

License:Apache License

/**
 * Copy row.//from   ww w  .ja v  a 2 s.  c  o m
 *
 * @param _srcSheet the src sheet
 * @param _destSheet the dest sheet
 * @param _srcRow the src row
 * @param _destRow the dest row
 * @param _styleMap the style map
 */
protected void copyRow(final Sheet _srcSheet, final Sheet _destSheet, final Row _srcRow, final Row _destRow,
        final Map<Integer, CellStyle> _styleMap) {
    final Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<>();
    _destRow.setHeight(_srcRow.getHeight());
    final int deltaRows = _destRow.getRowNum() - _srcRow.getRowNum();
    for (int j = _srcRow.getFirstCellNum(); j <= _srcRow.getLastCellNum(); j++) {
        final Cell oldCell = _srcRow.getCell(j); // ancienne cell
        Cell newCell = _destRow.getCell(j); // new cell
        if (oldCell != null) {
            if (newCell == null) {
                newCell = _destRow.createCell(j);
            }
            copyCell(oldCell, newCell, _styleMap);
            final CellRangeAddress mergedRegion = getMergedRegion(_srcSheet, _srcRow.getRowNum(),
                    (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                final CellRangeAddress newMergedRegion = new CellRangeAddress(
                        mergedRegion.getFirstRow() + deltaRows, mergedRegion.getLastRow() + deltaRows,
                        mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                final CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    _destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
    }
}

---

From source file:org.h819.commons.file.excel.poi.examples.TimesheetDemo.java

License:Apache License

public static void main(String[] args) throws Exception {
    Workbook wb;/*from   w ww. j av a2 s.  co  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.jplus.hyberbin.excel.service.BaseExcelService.java

License:Apache License

/**
 * sheet ?/*from  w w w .  j  a  v  a2  s . co m*/
 * @param sheet
 * @param row
 * @param length
 * @param data
 */
public static void addTitle(Sheet sheet, int row, int length, String data) {
    Row sheetRow = sheet.createRow(row);
    for (int i = 0; i < length; i++) {
        sheetRow.createCell(i);
    }
    CellStyle style = sheet.getWorkbook().createCellStyle(); // ?
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 
    CellRangeAddress cellRangeAddress = new CellRangeAddress(row, row, 0, length - 1);
    sheet.addMergedRegion(cellRangeAddress);
    Cell cell = sheetRow.getCell(0);
    cell.setCellStyle(style);
    cell.setCellValue(data);
}

---

From source file:org.kuali.test.runner.output.PoiHelper.java

License:Educational Community License

/**
 *
 * @param testSuite/*from w  w  w  .ja v a  2  s .co m*/
 * @param kualiTest
 */
public void writeReportHeader(TestSuite testSuite, KualiTest kualiTest) {
    Sheet sheet = wb.getSheetAt(0);
    Row row = sheet.createRow(currentReportRow);
    sheet.addMergedRegion(new CellRangeAddress(currentReportRow, currentReportRow, 0, HEADER_NAMES.length - 1));

    Cell cell = row.createCell(0);

    StringBuilder headerString = new StringBuilder(128);

    headerString.append("Platform: ");

    if (testSuite != null) {
        headerString.append(testSuite.getPlatformName());
        headerString.append(", Test Suite: ");
        headerString.append(testSuite.getName());
    } else {
        headerString.append(kualiTest.getTestHeader().getPlatformName());
        headerString.append(", Test: ");
        headerString.append(kualiTest.getTestHeader().getTestName());
    }

    headerString.append(", Run Date: ");
    headerString.append(Constants.DEFAULT_TIMESTAMP_FORMAT.format(new Date()));
    cell.setCellValue(headerString.toString());
    cell.setCellStyle(cellStyleHeader);

}

---

From source file:org.kuali.test.runner.output.PoiHelper.java

License:Educational Community License

/**
 * /*from  www  .j av  a 2 s. c  o  m*/
 * @param op
 * @param showIndex 
 */
public void writeCommentEntry(Operation op, boolean showIndex) {
    Sheet sheet = wb.getSheetAt(0);
    Row retval = sheet.createRow(++currentReportRow);

    // blank operation number
    Cell cell = retval.createCell(0);
    if (showIndex) {
        cell.setCellValue(op.getIndex());
    }
    cell.setCellStyle(cellStyleNormal);

    sheet.addMergedRegion(new CellRangeAddress(currentReportRow, currentReportRow, 1, HEADER_NAMES.length - 1));

    cell = retval.createCell(1);
    cell.setCellValue(op.getCommentOperation().getComment());
    cell.setCellStyle(cellStyleNormal);
}

---

From source file:org.kuali.test.runner.output.PoiHelper.java

License:Educational Community License

/**
 *
 * @param test/*from  w w w .j a v a 2  s . c  o  m*/
 */
public void writeTestHeader(KualiTest test) {
    Sheet sheet = wb.getSheetAt(0);
    Row row = sheet.createRow(++currentReportRow);
    sheet.addMergedRegion(new CellRangeAddress(currentReportRow, currentReportRow, 0, HEADER_NAMES.length - 1));

    Cell cell = row.createCell(0);
    cell.setCellValue("Test: " + test.getTestHeader().getTestName());
    cell.setCellStyle(cellStyleTestHeader);
}

---

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)/*from  ww w.ja  v  a 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.openpythia.utilities.SSUtilities.java

License:Apache License

public static Row copyRow(Sheet sheet, Row sourceRow, int destination) {
    Row newRow = sheet.createRow(destination);
    // get the last row from the headings
    int lastCol = sheet.getRow(0).getLastCellNum();
    for (int currentCol = 0; currentCol <= lastCol; currentCol++) {
        Cell newCell = newRow.createCell(currentCol);

        // if there is a cell in the template, copy its content and style
        Cell currentCell = sourceRow.getCell(currentCol);
        if (currentCell != null) {
            newCell.setCellStyle(currentCell.getCellStyle());
            newCell.setCellComment(currentCell.getCellComment());
            switch (currentCell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                newCell.setCellValue(currentCell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(currentCell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                String dummy = currentCell.getCellFormula();
                dummy = dummy.replace("Row", String.valueOf(destination + 1));
                newCell.setCellFormula(dummy);
                newCell.setCellFormula(/*from  www  .  jav a2s.  com*/
                        currentCell.getCellFormula().replace("Row", String.valueOf(destination + 1)));
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(currentCell.getBooleanCellValue());
                break;
            default:
            }
        }
    }

    // if the row contains merged regions, copy them to the new row
    int numberMergedRegions = sheet.getNumMergedRegions();
    for (int i = 0; i < numberMergedRegions; i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

        if (mergedRegion.getFirstRow() == sourceRow.getRowNum()
                && mergedRegion.getLastRow() == sourceRow.getRowNum()) {
            // this region is within the row - so copy it
            sheet.addMergedRegion(new CellRangeAddress(destination, destination, mergedRegion.getFirstColumn(),
                    mergedRegion.getLastColumn()));
        }
    }

    return newRow;
}

---

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 v a 2 s.  c  o 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.pivot4j.ui.poi.ExcelExporter.java

License:Common Public License

/**
 * @param context//from   ww  w . j  ava 2 s  . c o  m
 * @param sheet
 * @param regions
 */
protected void mergeCells(TableRenderContext context, Sheet sheet, List<CellRangeAddress> regions) {
    for (CellRangeAddress region : regions) {
        sheet.addMergedRegion(region);

        RegionUtil.setBorderTop(CellStyle.BORDER_THIN, region, sheet, workbook);
        RegionUtil.setBorderLeft(CellStyle.BORDER_THIN, region, sheet, workbook);
        RegionUtil.setBorderBottom(CellStyle.BORDER_THIN, region, sheet, workbook);
        RegionUtil.setBorderRight(CellStyle.BORDER_THIN, region, sheet, workbook);
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• »