List of usage examples for org.apache.poi.ss.usermodel Sheet getMergedRegion
public CellRangeAddress getMergedRegion(int index);
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; }