/** * The purpose of this method is to validate the cell state prior to modification * * @see #notifyArrayFormulaChanging() */ void notifyArrayFormulaChanging(String msg){ CellRangeAddress cra = getArrayFormulaRange(); if(cra.getNumberOfCells() > 1) { throw new IllegalStateException(msg); } //un-register the single-cell array formula from the parent XSSFSheet getRow().getSheet().removeArrayFormula(this); }
protected AbstractCellRangeDataSource(XSSFSheet sheet, CellRangeAddress cellRangeAddress) { this.sheet = sheet; // Make copy since CellRangeAddress is mutable. this.cellRangeAddress = cellRangeAddress.copy(); this.numOfCells = this.cellRangeAddress.getNumberOfCells(); this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); }
protected AbstractCellRangeDataSource(Sheet sheet, CellRangeAddress cellRangeAddress) { this.sheet = sheet; // Make copy since CellRangeAddress is mutable. this.cellRangeAddress = cellRangeAddress.copy(); this.numOfCells = this.cellRangeAddress.getNumberOfCells(); this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); }
/** * The purpose of this method is to validate the cell state prior to modification * * @see #notifyArrayFormulaChanging() */ void notifyArrayFormulaChanging(String msg){ if(isPartOfArrayFormulaGroup()){ CellRangeAddress cra = getArrayFormulaRange(); if(cra.getNumberOfCells() > 1) { throw new IllegalStateException(msg); } //un-register the single-cell array formula from the parent XSSFSheet getRow().getSheet().removeArrayFormula(this); } }
/** * adds a merged region of cells (hence those cells form one) * * @param region (rowfrom/colfrom-rowto/colto) to merge * @param validate whether to validate merged region * @return index of this region * @throws IllegalArgumentException if region contains fewer than 2 cells * @throws IllegalStateException if region intersects with an existing merged region * or multi-cell array formula on this sheet */ private int addMergedRegion(CellRangeAddress region, boolean validate) { if (region.getNumberOfCells() < 2) { throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells"); } region.validate(SpreadsheetVersion.EXCEL97); if (validate) { // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); // Throw IllegalStateException if the argument CellRangeAddress intersects with // a merged region already in this sheet validateMergedRegions(region); } return _sheet.addMergedRegion(region.getFirstRow(), region.getFirstColumn(), region.getLastRow(), region.getLastColumn()); }
private void validateArrayFormulas(CellRangeAddress region) { // FIXME: this may be faster if it looped over array formulas directly rather than looping over each cell in // the region and searching if that cell belongs to an array formula int firstRow = region.getFirstRow(); int firstColumn = region.getFirstColumn(); int lastRow = region.getLastRow(); int lastColumn = region.getLastColumn(); for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) { HSSFRow row = getRow(rowIn); if (row == null) continue; for (int colIn = firstColumn; colIn <= lastColumn; colIn++) { HSSFCell cell = row.getCell(colIn); if (cell == null) continue; if (cell.isPartOfArrayFormulaGroup()) { CellRangeAddress arrayRange = cell.getArrayFormulaRange(); if (arrayRange.getNumberOfCells() > 1 && region.intersects(arrayRange)) { String msg = "The range " + region.formatAsString() + " intersects with a multi-cell array formula. " + "You cannot merge cells of an array."; throw new IllegalStateException(msg); } } } } }
if (arrayRange.getNumberOfCells() > 1 && region.intersects(arrayRange)) { String msg = "The range " + region.formatAsString() + " intersects with a multi-cell array formula. " + "You cannot merge cells of an array.";
/** * Adds a merged region of cells (hence those cells form one). * If validate is true, check to make sure adding the merged region to the sheet doesn't create a corrupt workbook * If validate is false, skips the expensive merged region checks, but may produce a corrupt workbook. * * @param region to merge * @param validate whether to validate merged region * @return index of this region * @throws IllegalArgumentException if region contains fewer than 2 cells (this check is inexpensive and is performed regardless of <tt>validate</tt>) * @throws IllegalStateException if region intersects with a multi-cell array formula * @throws IllegalStateException if region intersects with an existing region on this sheet */ private int addMergedRegion(CellRangeAddress region, boolean validate) { if (region.getNumberOfCells() < 2) { throw new IllegalArgumentException("Merged region " + region.formatAsString() + " must contain 2 or more cells"); } region.validate(SpreadsheetVersion.EXCEL2007); if (validate) { // throw IllegalStateException if the argument CellRangeAddress intersects with // a multi-cell array formula defined in this sheet validateArrayFormulas(region); // Throw IllegalStateException if the argument CellRangeAddress intersects with // a merged region already in this sheet validateMergedRegions(region); } CTMergeCells ctMergeCells = worksheet.isSetMergeCells() ? worksheet.getMergeCells() : worksheet.addNewMergeCells(); CTMergeCell ctMergeCell = ctMergeCells.addNewMergeCell(); ctMergeCell.setRef(region.formatAsString()); return ctMergeCells.sizeOfMergeCellArray(); }
/** * when a cell with a 'master' shared formula is removed, the next cell in the range becomes the master * @param cell The cell that is removed * @param evalWb BaseXSSFEvaluationWorkbook in use, if one exists */ protected void onDeleteFormula(XSSFCell cell, BaseXSSFEvaluationWorkbook evalWb){ CTCellFormula f = cell.getCTCell().getF(); if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) { CellRangeAddress ref = CellRangeAddress.valueOf(f.getRef()); if(ref.getNumberOfCells() > 1){ DONE: for(int i = cell.getRowIndex(); i <= ref.getLastRow(); i++){ XSSFRow row = getRow(i); if(row != null) for(int j = cell.getColumnIndex(); j <= ref.getLastColumn(); j++){ XSSFCell nextCell = row.getCell(j); if(nextCell != null && nextCell != cell && nextCell.getCellType() == CellType.FORMULA){ CTCellFormula nextF = nextCell.getCTCell().getF(); nextF.setStringValue(nextCell.getCellFormula(evalWb)); CellRangeAddress nextRef = new CellRangeAddress( nextCell.getRowIndex(), ref.getLastRow(), nextCell.getColumnIndex(), ref.getLastColumn()); nextF.setRef(nextRef.formatAsString()); sharedFormulas.put((int)nextF.getSi(), nextF); break DONE; } } } } } }
/** * The purpose of this method is to validate the cell state prior to modification * * @see #notifyArrayFormulaChanging() */ void notifyArrayFormulaChanging(String msg){ CellRangeAddress cra = getArrayFormulaRange(); if(cra.getNumberOfCells() > 1) { throw new IllegalStateException(msg); } //un-register the single-cell array formula from the parent XSSFSheet getRow().getSheet().removeArrayFormula(this); }
/** * The purpose of this method is to validate the cell state prior to modification * * @see #notifyArrayFormulaChanging() */ void notifyArrayFormulaChanging(String msg){ CellRangeAddress cra = getArrayFormulaRange(); if(cra.getNumberOfCells() > 1) { throw new IllegalStateException(msg); } //un-register the single-cell array formula from the parent XSSFSheet getRow().getSheet().removeArrayFormula(this); }
/** * The purpose of this method is to validate the cell state prior to modification * * @see #notifyArrayFormulaChanging() */ void notifyArrayFormulaChanging(String msg){ CellRangeAddress cra = getArrayFormulaRange(); if(cra.getNumberOfCells() > 1) { throw new IllegalStateException(msg); } //un-register the single-cell array formula from the parent XSSFSheet getRow().getSheet().removeArrayFormula(this); }
/** * The purpose of this method is to validate the cell state prior to modification * * @see #notifyArrayFormulaChanging() */ void notifyArrayFormulaChanging(String msg){ if(isPartOfArrayFormulaGroup()){ CellRangeAddress cra = getArrayFormulaRange(); if(cra.getNumberOfCells() > 1) { throw new IllegalStateException(msg); } //un-register the single-cell array formula from the parent XSSFSheet getRow().getSheet().removeArrayFormula(this); } }
protected AbstractCellRangeDataSource(Sheet sheet, CellRangeAddress cellRangeAddress) { this.sheet = sheet; // Make copy since CellRangeAddress is mutable. this.cellRangeAddress = cellRangeAddress.copy(); this.numOfCells = this.cellRangeAddress.getNumberOfCells(); this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); }
protected AbstractCellRangeDataSource(XSSFSheet sheet, CellRangeAddress cellRangeAddress) { this.sheet = sheet; // Make copy since CellRangeAddress is mutable. this.cellRangeAddress = cellRangeAddress.copy(); this.numOfCells = this.cellRangeAddress.getNumberOfCells(); this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); }
protected AbstractCellRangeDataSource(Sheet sheet, CellRangeAddress cellRangeAddress) { this.sheet = sheet; // Make copy since CellRangeAddress is mutable. this.cellRangeAddress = cellRangeAddress.copy(); this.numOfCells = this.cellRangeAddress.getNumberOfCells(); this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); }
protected AbstractCellRangeDataSource(Sheet sheet, CellRangeAddress cellRangeAddress) { this.sheet = sheet; // Make copy since CellRangeAddress is mutable. this.cellRangeAddress = cellRangeAddress.copy(); this.numOfCells = this.cellRangeAddress.getNumberOfCells(); this.evaluator = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator(); }
/** * The purpose of this method is to validate the cell state prior to modification * * @see #notifyArrayFormulaChanging() */ void notifyArrayFormulaChanging(String msg){ if(isPartOfArrayFormulaGroup()){ CellRangeAddress cra = getArrayFormulaRange(); if(cra.getNumberOfCells() > 1) { throw new IllegalStateException(msg); } //un-register the single-cell array formula from the parent XSSFSheet getRow().getSheet().removeArrayFormula(this); } }
public static void copyConditionalFormat(org.apache.poi.ss.usermodel.Cell oldCell, org.apache.poi.ss.usermodel.Cell newCell) { SheetConditionalFormatting cf = oldCell.getSheet().getSheetConditionalFormatting(); SheetConditionalFormatting ncf = newCell.getSheet().getSheetConditionalFormatting(); int numCF = cf.getNumConditionalFormattings(); List<ConditionalFormattingRule> rules = new ArrayList<ConditionalFormattingRule>(); for (int i = 0; i < numCF; i++) { ConditionalFormatting f = cf.getConditionalFormattingAt(i); for (CellRangeAddress a : f.getFormattingRanges()) { if (a.getNumberOfCells() == 1 && a.isInRange(oldCell.getRowIndex(), oldCell.getColumnIndex())) { int numR = f.getNumberOfRules(); for (int j = 0; j < numR; ++j) { try { rules.add(f.getRule(j)); } catch (IndexOutOfBoundsException ex) { } } } } } if (!rules.isEmpty()) { ncf.addConditionalFormatting(new CellRangeAddress[]{ new CellRangeAddress(newCell.getRowIndex(), newCell.getRowIndex(), newCell.getColumnIndex(), newCell.getColumnIndex())}, rules.toArray(new ConditionalFormattingRule[0])); } }
private void validateArrayFormulas(CellRangeAddress region){ int firstRow = region.getFirstRow(); int firstColumn = region.getFirstColumn(); int lastRow = region.getLastRow(); int lastColumn = region.getLastColumn(); for (int rowIn = firstRow; rowIn <= lastRow; rowIn++) { for (int colIn = firstColumn; colIn <= lastColumn; colIn++) { XSSFRow row = getRow(rowIn); if (row == null) continue; XSSFCell cell = row.getCell(colIn); if(cell == null) continue; if(cell.isPartOfArrayFormulaGroup()){ CellRangeAddress arrayRange = cell.getArrayFormulaRange(); if (arrayRange.getNumberOfCells() > 1 && ( arrayRange.isInRange(region.getFirstRow(), region.getFirstColumn()) || arrayRange.isInRange(region.getFirstRow(), region.getFirstColumn())) ){ String msg = "The range " + region.formatAsString() + " intersects with a multi-cell array formula. " + "You cannot merge cells of an array."; throw new IllegalStateException(msg); } } } } }