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

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

Introduction

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

Prototype

public CellRangeAddress getMergedRegion(int index);

Source Link

Document

Returns the merged region at the specified index

Usage

From source file:com.vaadin.addon.spreadsheet.command.RowData.java

public void copy(int rowIndex) {
    isCopied = true;/* ww  w  . j  ava2  s  . co m*/
    this.rowIndex = rowIndex;
    this.maxCol = spreadsheet.getLastColumn();
    cellsData.clear();
    mergedCells.clear();
    commentsWithoutCell.clear();

    Row row = spreadsheet.getActiveSheet().getRow(rowIndex);

    height = row == null ? null : row.getZeroHeight() ? 0.0F : row.getHeightInPoints();

    if (row != null) {
        copyCellsData(row);
    }

    Sheet sheet = spreadsheet.getActiveSheet();
    for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
        CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
        if (mergedRegion.getFirstRow() == rowIndex) {
            mergedCells.add(mergedRegion);
        }
    }

}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Called when number of rows has moved. Spreadsheet needs to update its
 * internal state.// w  w w  .jav  a  2 s . c  o  m
 *
 * Note: If n is negative it would mean the rows has moved up. Positive
 * value indicates that new rows are moved below.
 *
 * @param first
 *            the first row that has changed, 0-based
 * @param last
 *            the last row that has changed, 0-based
 * @param n
 *            the amount of lines that rows has been moved
 */
private void rowsMoved(int first, int last, int n) {
    // Merged regions
    if (n < 0) {
        // Remove merged cells from deleted rows. POI will handle the other
        // updated values.
        for (int row = (first + n); row <= first; ++row) {
            Sheet sheet = getActiveSheet();
            for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
                CellRangeAddress mergedRegion = sheet.getMergedRegion(i);
                if (mergedRegion.getFirstRow() == row) {
                    removeMergedRegion(i);
                }
            }
        }
    }

    // PopupButtons
    if (!sheetPopupButtons.isEmpty()) {
        Map<CellReference, PopupButton> updated = new HashMap<CellReference, PopupButton>();
        for (PopupButton pbutton : sheetPopupButtons.values()) {
            CellReference cell = pbutton.getCellReference();
            unRegisterPopupButton(pbutton);
            int row = cell.getRow();
            if (rowWasRemoved(row, first, n)) {
                // do nothing -> will be removed
            } else if (numberOfRowsAboveWasChanged(row, last, first)) {
                int newRow = cell.getRow() + n;
                int col = cell.getCol();
                CellReference newCell = new CellReference(newRow, col);
                pbutton.setCellReference(newCell);
                updated.put(newCell, pbutton);
            } else {
                updated.put(cell, pbutton);
            }
        }
        sheetPopupButtons = updated;
    }

    // Invalid formula indicators
    int activeSheetIndex = workbook.getActiveSheetIndex();
    HashSet<String> original = invalidFormulas.get(activeSheetIndex);
    if (original != null) {
        HashSet<String> updated = new HashSet<String>();
        for (String key : original) {
            int row = SpreadsheetUtil.getRowFromKey(key) - 1;
            int col = SpreadsheetUtil.getColumnIndexFromKey(key) - 1;
            if (rowWasRemoved(row, first, n)) {
                // do nothing -> will be removed
            } else if (numberOfRowsAboveWasChanged(row, last, first)) {
                // the number of the rows above has changed -> update the
                // row index
                updated.add(SpreadsheetUtil.toKey(col + 1, row + n + 1));
            } else {
                updated.add(key);
            }
        }
        original.clear();
        invalidFormulas.put(activeSheetIndex, updated);
    }
}

From source file:com.vaadin.addon.spreadsheet.Spreadsheet.java

License:Open Source License

/**
 * Merges the given cells. See//from  w w w. j  a  v a 2s  .  c o  m
 * {@link Sheet#addMergedRegion(CellRangeAddress)}.
 * <p>
 * If another existing merged region is completely inside the given range,
 * it is removed. If another existing region either encloses or overlaps the
 * given range, an error is thrown. See
 * {@link CellRangeUtil#intersect(CellRangeAddress, CellRangeAddress)}.
 * <p>
 * Note: POI doesn't seem to update the cells that are "removed" due to the
 * merge - the values for those cells still exist and continue being used in
 * possible formulas. If you need to make sure those values are removed,
 * just delete the cells before creating the merged region.
 * <p>
 * If the added region affects the currently selected cell, a new
 * {@link SelectionChangeEvent} is fired.
 * 
 * @param region
 *            The range of cells to merge
 * @throws IllegalArgumentException
 *             If the given region overlaps with or encloses another
 *             existing region within the sheet.
 */
public void addMergedRegion(CellRangeAddress region) throws IllegalArgumentException {
    final Sheet sheet = getActiveSheet();
    // need to check if there are merged regions already inside the given
    // range, otherwise very bad inconsistencies appear.
    int index = 0;
    while (index < sheet.getNumMergedRegions()) {
        CellRangeAddress existingRegion = sheet.getMergedRegion(index);
        int intersect = CellRangeUtil.intersect(region, existingRegion);
        if (intersect == CellRangeUtil.INSIDE) {
            deleteMergedRegion(index);
        } else if (intersect == CellRangeUtil.OVERLAP || intersect == CellRangeUtil.ENCLOSES) {
            throw new IllegalArgumentException("An existing region " + existingRegion + " "
                    + (intersect == CellRangeUtil.OVERLAP ? "overlaps " : "encloses ") + "the given region "
                    + region);
        } else {
            index++;
        }
    }
    createMergedRegionIntoSheet(region);
    selectionManager.mergedRegionAdded(region);
}

From source file:com.vaadin.addon.spreadsheet.SpreadsheetFactory.java

License:Open Source License

/**
 * Loads merged region(s) configuration for the currently active sheet and
 * sets it into the shared state./*from w ww.ja  v  a 2  s.  co m*/
 *
 * @param spreadsheet
 *            Target Spreadsheet
 */
static void loadMergedRegions(Spreadsheet spreadsheet) {
    final Sheet sheet = spreadsheet.getActiveSheet();
    spreadsheet.getState().mergedRegions = null;
    spreadsheet.mergedRegionCounter = 0;
    int numMergedRegions = sheet.getNumMergedRegions();
    if (numMergedRegions > 0) {
        spreadsheet.getState().mergedRegions = new ArrayList<MergedRegion>(numMergedRegions);
        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress cra = sheet.getMergedRegion(i);
            MergedRegion mergedRegion = new MergedRegion();
            mergedRegion.col1 = cra.getFirstColumn() + 1;
            mergedRegion.col2 = cra.getLastColumn() + 1;
            mergedRegion.row1 = cra.getFirstRow() + 1;
            mergedRegion.row2 = cra.getLastRow() + 1;
            mergedRegion.id = spreadsheet.mergedRegionCounter++;
            spreadsheet.getState().mergedRegions.add(mergedRegion);
        }
    }
}

From source file:de.topicmapslab.jexc.utility.XlsxCellUtils.java

License:Apache License

/**
 * Returns the cell range of the given cell
 * //from w  w  w. j  a va2  s.  c  o  m
 * @param cell
 *            the cell
 * @return the cell range of merged region the cell is part of or
 *         <code>null</code>
 */
public static CellRangeAddress getCellRange(Cell cell) {
    Sheet s = cell.getSheet();
    for (int i = 0; i < s.getNumMergedRegions(); i++) {
        CellRangeAddress a = s.getMergedRegion(i);
        if (a.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
            return a;
        }
    }
    return null;
}

From source file:fr.openwide.core.export.excel.AbstractExcelTableExport.java

License:Apache License

/**
 * Redimensionne les colonnes qui contiennent des rgions fusionnes.
 * /*from  www. j  a v a 2s  .  com*/
 * Dans POI, les rgions fusionnes ne sont pas prises en compte dans le autoSizeColumn.
 * Quand on fusionne des cellules sur une mme colonne, on corrige la taille de cette colonne si ncessaire.
 * 
 * @param sheet feuille de calcul
 * @param columns map contenant l'en-tte et les informations d'une colonne
 */
protected void resizeMergedColumns(Sheet sheet, Collection<ColumnInformation> columns) {
    if (sheet.getNumMergedRegions() > 0) {
        List<ColumnInformation> columnsInfo = new ArrayList<ColumnInformation>(columns);

        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress mergedRegion = sheet.getMergedRegion(i);

            if (mergedRegion.getFirstColumn() == mergedRegion.getLastColumn()) {
                int columnIndex = mergedRegion.getFirstColumn();

                String headerText = getColumnLabel(columnsInfo.get(columnIndex).getHeaderKey());

                int headerSize = (int) (headerText.length() * 300 * COLUMN_RESIZE_RATIO);
                if (sheet.getColumnWidth(columnIndex) < headerSize) {
                    sheet.setColumnWidth(columnIndex, headerSize);
                }
            }
        }
    }
}

From source file:generate.CopyRow.java

private static void copyAnyMergedRegions(Sheet worksheet, Row sourceRow, Row newRow) {
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        copyMergeRegion(worksheet, sourceRow, newRow, worksheet.getMergedRegion(i));
    }/*from  w w w .ja v a  2s  .  c  o  m*/
}

From source file:generate.CopyRow.java

public static int getNbOfMergedRegions(Sheet sheet, int row) {
    int count = 0;
    for (int i = 0; i < sheet.getNumMergedRegions(); ++i) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        if (range.getFirstRow() <= row && range.getLastRow() >= row)
            ++count;/*from   w w w  .ja  v  a 2 s.  c  o  m*/
    }
    return count;
}

From source file:invoiceapplication.CopyRowOriginal.java

private static void copyAnyMergedRegions(Sheet worksheet, Row sourceRow, Row newRow) {
    System.out.println(worksheet.getNumMergedRegions());
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        copyMergeRegion(worksheet, sourceRow, newRow, worksheet.getMergedRegion(i));
    }/*from  w  ww .  j a  va 2  s. co  m*/
}

From source file:me.utils.excel.ImportExcelme.java

License:Open Source License

/**   
* ???   //w  ww.j a v  a 2  s .  com
* @param sheet   
* @param row   
* @param column   
* @return   
*/
public Object getMergedRegionValue(Sheet sheet, int row, int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();

    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress ca = sheet.getMergedRegion(i);
        int firstColumn = ca.getFirstColumn();
        int lastColumn = ca.getLastColumn();
        int firstRow = ca.getFirstRow();
        int lastRow = ca.getLastRow();

        if (row >= firstRow && row <= lastRow) {

            if (column >= firstColumn && column <= lastColumn) {
                Row row_ = this.getRow(row);
                return this.getCellValue(row_, column);
            }
        }
    }

    return null;
}