/** * @return the text format of this range. Single cell ranges are formatted * like single cell references (e.g. 'A1' instead of 'A1:A1'). */ public String formatAsString() { return formatAsString(null, false); }
private void validateMergedRegions(CellRangeAddress candidateRegion) { for (final CellRangeAddress existingRegion : getMergedRegions()) { if (existingRegion.intersects(candidateRegion)) { throw new IllegalStateException("Cannot add merged region " + candidateRegion.formatAsString() + " to sheet because it overlaps with an existing merged region (" + existingRegion.formatAsString() + ")."); } } }
public String getReferenceText() { CellRangeAddress cra = new CellRangeAddress(_firstRow, _firstRow+_height-1, _firstColumn, _firstColumn+_width-1); return cra.formatAsString(); }
@Override public String getFormulaString() { return cellRangeAddress.formatAsString(sheet.getSheetName(), true); }
/** * Verify that candidate region does not intersect with an existing merged region in this sheet * * @param candidateRegion the range of cells to verify * @throws IllegalStateException if candidate region intersects an existing merged region in this sheet (or candidateRegion is already merged in this sheet) */ private void validateMergedRegions(CellRangeAddress candidateRegion) { for (final CellRangeAddress existingRegion : getMergedRegions()) { if (existingRegion.intersects(candidateRegion)) { throw new IllegalStateException("Cannot add merged region " + candidateRegion.formatAsString() + " to sheet because it overlaps with an existing merged region (" + existingRegion.formatAsString() + ")."); } } }
@Override public String getDataRangeReference() { return cellRangeAddress.formatAsString(sheet.getSheetName(), true); }
public String prettyPrint() { StringBuilder builder = new StringBuilder(); for(CellRangeAddress address : regions.getCellRangeAddresses()) { builder.append(address.formatAsString()); } builder.append(" => "); builder.append(this.validationConstraint.prettyPrint()); return builder.toString(); }
@Override public void setFormattingRanges(CellRangeAddress[] ranges) { if (ranges == null) { throw new IllegalArgumentException("cellRanges must not be null"); } final StringBuilder sb = new StringBuilder(); boolean first = true; for (CellRangeAddress range : ranges) { if (!first) { sb.append(" "); } else { first = false; } sb.append(range.formatAsString()); } _cf.setSqref(Collections.singletonList(sb.toString())); }
/** * Verify that no merged regions intersect another merged region in this sheet. * * @throws IllegalStateException if at least one region intersects with another merged region in this sheet */ private void checkForIntersectingMergedRegions() { final List<CellRangeAddress> regions = getMergedRegions(); final int size = regions.size(); for (int i=0; i < size; i++) { final CellRangeAddress region = regions.get(i); for (final CellRangeAddress other : regions.subList(i+1, regions.size())) { if (region.intersects(other)) { String msg = "The range " + region.formatAsString() + " intersects with another merged region " + other.formatAsString() + " in this sheet"; throw new IllegalStateException(msg); } } } }
/** * Verify that no merged regions intersect another merged region in this sheet. * * @throws IllegalStateException if at least one region intersects with another merged region in this sheet */ private void checkForIntersectingMergedRegions() { final List<CellRangeAddress> regions = getMergedRegions(); final int size = regions.size(); for (int i=0; i < size; i++) { final CellRangeAddress region = regions.get(i); for (final CellRangeAddress other : regions.subList(i+1, regions.size())) { if (region.intersects(other)) { String msg = "The range " + region.formatAsString() + " intersects with another merged region " + other.formatAsString() + " in this sheet"; throw new IllegalStateException(msg); } } } }
/** * Ignore errors across a range of cells. * * @param region Range of cells. * @param ignoredErrorTypes Types of error to ignore there. */ public void addIgnoredErrors(CellRangeAddress region, IgnoredErrorType... ignoredErrorTypes) { region.validate(SpreadsheetVersion.EXCEL2007); addIgnoredErrors(region.formatAsString(), ignoredErrorTypes); }
/** * @param range * @return * @since POI 4.0.0 */ public String formatRange(CellRangeAddress range) { final XSSFSheet sheet = getSheet(); return (sheet == null) ? null : range.formatAsString(sheet.getSheetName(), true); }
@Override public String toString() { StringBuffer buffer = new StringBuffer(); buffer.append("[HYPERLINK RECORD]\n"); buffer.append(" .range = ").append(_range.formatAsString()).append("\n"); buffer.append(" .guid = ").append(_guid.formatAsString()).append("\n"); buffer.append(" .linkOpts= ").append(HexDump.intToHex(_linkOpts)).append("\n"); buffer.append(" .label = ").append(getLabel()).append("\n"); if ((_linkOpts & HLINK_TARGET_FRAME) != 0) { buffer.append(" .targetFrame= ").append(getTargetFrame()).append("\n"); } if((_linkOpts & HLINK_URL) != 0 && _moniker != null) { buffer.append(" .moniker = ").append(_moniker.formatAsString()).append("\n"); } if ((_linkOpts & HLINK_PLACE) != 0) { buffer.append(" .textMark= ").append(getTextMark()).append("\n"); } buffer.append(" .address = ").append(getAddress()).append("\n"); buffer.append("[/HYPERLINK RECORD]\n"); return buffer.toString(); }
/* package */ void setCellArrayFormula(String formula, CellRangeAddress range) { setFormula(formula, FormulaType.ARRAY); CTCellFormula cellFormula = _cell.getF(); cellFormula.setT(STCellFormulaType.ARRAY); cellFormula.setRef(range.formatAsString()); }
/*package*/ static void updateHyperlinks(Sheet sheet, FormulaShifter formulaShifter) { int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet); List<? extends Hyperlink> hyperlinkList = sheet.getHyperlinkList(); for (Hyperlink hyperlink : hyperlinkList) { XSSFHyperlink xhyperlink = (XSSFHyperlink) hyperlink; String cellRef = xhyperlink.getCellRef(); CellRangeAddress cra = CellRangeAddress.valueOf(cellRef); CellRangeAddress shiftedRange = BaseRowColShifter.shiftRange(formulaShifter, cra, sheetIndex); if (shiftedRange != null && shiftedRange != cra) { // shiftedRange should not be null. If shiftedRange is null, that means // that a hyperlink wasn't deleted at the beginning of shiftRows when // identifying rows that should be removed because they will be overwritten xhyperlink.setCellReference(shiftedRange.formatAsString()); } } }
/** * 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); } } } } }
void onReadCell(XSSFCell cell){ //collect cells holding shared formulas CTCell ct = cell.getCTCell(); CTCellFormula f = ct.getF(); if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) { // save a detached copy to avoid XmlValueDisconnectedException, // this may happen when the master cell of a shared formula is changed CTCellFormula sf = (CTCellFormula)f.copy(); CellRangeAddress sfRef = CellRangeAddress.valueOf(sf.getRef()); CellReference cellRef = new CellReference(cell); // If the shared formula range precedes the master cell then the preceding part is discarded, e.g. // if the cell is E60 and the shared formula range is C60:M85 then the effective range is E60:M85 // see more details in https://issues.apache.org/bugzilla/show_bug.cgi?id=51710 if(cellRef.getCol() > sfRef.getFirstColumn() || cellRef.getRow() > sfRef.getFirstRow()){ String effectiveRef = new CellRangeAddress( Math.max(cellRef.getRow(), sfRef.getFirstRow()), sfRef.getLastRow(), Math.max(cellRef.getCol(), sfRef.getFirstColumn()), sfRef.getLastColumn()).formatAsString(); sf.setRef(effectiveRef); } sharedFormulas.put((int)f.getSi(), sf); } if (f != null && f.getT() == STCellFormulaType.ARRAY && f.getRef() != null) { arrayFormulas.add(CellRangeAddress.valueOf(f.getRef())); } }
/** * 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; } } } } } }
@Override public XSSFAutoFilter setAutoFilter(CellRangeAddress range) { CTAutoFilter af = worksheet.getAutoFilter(); if(af == null) { af = worksheet.addNewAutoFilter(); } CellRangeAddress norm = new CellRangeAddress(range.getFirstRow(), range.getLastRow(), range.getFirstColumn(), range.getLastColumn()); String ref = norm.formatAsString(); af.setRef(ref); XSSFWorkbook wb = getWorkbook(); int sheetIndex = getWorkbook().getSheetIndex(this); XSSFName name = wb.getBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex); if (name == null) { name = wb.createBuiltInName(XSSFName.BUILTIN_FILTER_DB, sheetIndex); } name.getCTName().setHidden(true); CellReference r1 = new CellReference(getSheetName(), range.getFirstRow(), range.getFirstColumn(), true, true); CellReference r2 = new CellReference(null, range.getLastRow(), range.getLastColumn(), true, true); String fmla = r1.formatAsString() + ":" + r2.formatAsString(); name.setRefersToFormula(fmla); return new XSSFAutoFilter(this); }