/** * @return the 0-based row of the last cell that contains this hyperlink */ public int getLastRow() { return _range.getLastRow(); }
@Override public String toString() { StringBuffer retval = new StringBuffer(); retval.append("[MERGEDCELLS]").append("\n"); retval.append(" .numregions =").append(getNumAreas()).append("\n"); for (int k = 0; k < _numberOfRegions; k++) { CellRangeAddress r = _regions[_startIndex + k]; retval.append(" .rowfrom =").append(r.getFirstRow()).append("\n"); retval.append(" .rowto =").append(r.getLastRow()).append("\n"); retval.append(" .colfrom =").append(r.getFirstColumn()).append("\n"); retval.append(" .colto =").append(r.getLastColumn()).append("\n"); } retval.append("[MERGEDCELLS]").append("\n"); return retval.toString(); }
/** * Removes all border properties from this {@link PropertyTemplate} for the * specified range. * * @parm range - {@link CellRangeAddress} range of cells to remove borders. */ private void removeBorderColors(CellRangeAddress range) { Set<String> properties = new HashSet<>(); properties.add(CellUtil.TOP_BORDER_COLOR); properties.add(CellUtil.BOTTOM_BORDER_COLOR); properties.add(CellUtil.LEFT_BORDER_COLOR); properties.add(CellUtil.RIGHT_BORDER_COLOR); for (int row = range.getFirstRow(); row <= range.getLastRow(); row++) { for (int col = range.getFirstColumn(); col <= range .getLastColumn(); col++) { removeProperties(row, col, properties); } } }
/** * Removes all border properties from this {@link PropertyTemplate} for the * specified range. * * @parm range - {@link CellRangeAddress} range of cells to remove borders. */ private void removeBorders(CellRangeAddress range) { Set<String> properties = new HashSet<>(); properties.add(CellUtil.BORDER_TOP); properties.add(CellUtil.BORDER_BOTTOM); properties.add(CellUtil.BORDER_LEFT); properties.add(CellUtil.BORDER_RIGHT); for (int row = range.getFirstRow(); row <= range.getLastRow(); row++) { for (int col = range.getFirstColumn(); col <= range .getLastColumn(); col++) { removeProperties(row, col, properties); } } removeBorderColors(range); }
public CellRangeAddress copy() { return new CellRangeAddress(getFirstRow(), getLastRow(), getFirstColumn(), getLastColumn()); }
/** * Create an enclosing CellRange for the two cell ranges. * * @return enclosing CellRange */ public static CellRangeAddress createEnclosingCellRange(CellRangeAddress crA, CellRangeAddress crB) { if( crB == null) { return crA.copy(); } int minRow = lt(crB.getFirstRow(), crA.getFirstRow()) ?crB.getFirstRow() :crA.getFirstRow(); int maxRow = gt(crB.getLastRow(), crA.getLastRow()) ?crB.getLastRow() :crA.getLastRow(); int minCol = lt(crB.getFirstColumn(),crA.getFirstColumn())?crB.getFirstColumn():crA.getFirstColumn(); int maxCol = gt(crB.getLastColumn(), crA.getLastColumn()) ?crB.getLastColumn() :crA.getLastColumn(); return new CellRangeAddress(minRow, maxRow, minCol, maxCol); }
/** * 判断指定的单元格是否是合并单元格 * * @param sheet {@link Sheet} * @param row 行号 * @param column 列号 * @return 是否是合并单元格 */ public static boolean isMergedRegion(Sheet sheet, int row, int column) { final int sheetMergeCount = sheet.getNumMergedRegions(); CellRangeAddress ca; for (int i = 0; i < sheetMergeCount; i++) { ca = sheet.getMergedRegion(i); if (row >= ca.getFirstRow() && row <= ca.getLastRow() && column >= ca.getFirstColumn() && column <= ca.getLastColumn()) { return true; } } return false; }
public static CellRangeAddress shiftRange(FormulaShifter formulaShifter, CellRangeAddress cra, int currentExternSheetIx) { // FormulaShifter works well in terms of Ptgs - so convert CellRangeAddress to AreaPtg (and back) here AreaPtg aptg = new AreaPtg(cra.getFirstRow(), cra.getLastRow(), cra.getFirstColumn(), cra.getLastColumn(), false, false, false, false); Ptg[] ptgs = { aptg, }; if (!formulaShifter.adjustFormula(ptgs, currentExternSheetIx)) { return cra; } Ptg ptg0 = ptgs[0]; if (ptg0 instanceof AreaPtg) { AreaPtg bptg = (AreaPtg) ptg0; return new CellRangeAddress(bptg.getFirstRow(), bptg.getLastRow(), bptg.getFirstColumn(), bptg.getLastColumn()); } if (ptg0 instanceof AreaErrPtg) { return null; } throw new IllegalStateException("Unexpected shifted ptg class (" + ptg0.getClass().getName() + ")"); }
/** * 判断指定的单元格是否是合并单元格 * * @param sheet {@link Sheet} * @param row 行号 * @param column 列号 * @return 是否是合并单元格 */ public static boolean isMergedRegion(Sheet sheet, int row, int column) { final int sheetMergeCount = sheet.getNumMergedRegions(); CellRangeAddress ca; for (int i = 0; i < sheetMergeCount; i++) { ca = sheet.getMergedRegion(i); if (row >= ca.getFirstRow() && row <= ca.getLastRow() && column >= ca.getFirstColumn() && column <= ca.getLastColumn()) { return true; } } return false; }
/** * <p> * Draws the left border for a range of cells * </p> * * @param range * - {@link CellRangeAddress} range of cells on which borders are * drawn. * @param borderType * - Type of border to draw. {@link BorderStyle}. */ private void drawLeftBorder(CellRangeAddress range, BorderStyle borderType) { int firstRow = range.getFirstRow(); int lastRow = range.getLastRow(); int col = range.getFirstColumn(); for (int i = firstRow; i <= lastRow; i++) { addProperty(i, col, CellUtil.BORDER_LEFT, borderType); if (borderType == BorderStyle.NONE && col > 0) { addProperty(i, col - 1, CellUtil.BORDER_RIGHT, borderType); } } }
/** * Check if cell range A contains cell range B (B <= A) * * TODO: move this into {@link CellRangeAddressBase} * * @param crA cell range A * @param crB cell range B * @return true if cell range A contains cell range B */ public static boolean contains(CellRangeAddress crA, CellRangeAddress crB) { return le(crA.getFirstRow(), crB.getFirstRow()) && ge(crA.getLastRow(), crB.getLastRow()) && le(crA.getFirstColumn(), crB.getFirstColumn()) && ge(crA.getLastColumn(), crB.getLastColumn()); }
/** * Sets the left border style for a region of cells by manipulating the cell style of the individual * cells on the left * * @param border The new border * @param region The region that should have the border * @param sheet The sheet that the region is on. * @since POI 3.16 beta 1 */ public static void setBorderLeft(BorderStyle border, CellRangeAddress region, Sheet sheet) { int rowStart = region.getFirstRow(); int rowEnd = region.getLastRow(); int column = region.getFirstColumn(); CellPropertySetter cps = new CellPropertySetter(CellUtil.BORDER_LEFT, border); for (int i = rowStart; i <= rowEnd; i++) { cps.setProperty(CellUtil.getRow(i, sheet), column); } }
/** * Sets the left border color for a region of cells by manipulating the cell style of the individual * cells on the left * * @param color The color of the border * @param region The region that should have the border * @param sheet The sheet that the region is on. * @since POI 3.15 beta 2 */ public static void setLeftBorderColor(int color, CellRangeAddress region, Sheet sheet) { int rowStart = region.getFirstRow(); int rowEnd = region.getLastRow(); int column = region.getFirstColumn(); CellPropertySetter cps = new CellPropertySetter(CellUtil.LEFT_BORDER_COLOR, color); for (int i = rowStart; i <= rowEnd; i++) { cps.setProperty(CellUtil.getRow(i, sheet), column); } }
/** * Sets the right border style for a region of cells by manipulating the cell style of the individual * cells on the right * * @param border The new border * @param region The region that should have the border * @param sheet The sheet that the region is on. * @since POI 3.16 beta 1 */ public static void setBorderRight(BorderStyle border, CellRangeAddress region, Sheet sheet) { int rowStart = region.getFirstRow(); int rowEnd = region.getLastRow(); int column = region.getLastColumn(); CellPropertySetter cps = new CellPropertySetter(CellUtil.BORDER_RIGHT, border); for (int i = rowStart; i <= rowEnd; i++) { cps.setProperty(CellUtil.getRow(i, sheet), column); } }
/** * Sets the right border color for a region of cells by manipulating the cell style of the individual * cells on the right * * @param color The color of the border * @param region The region that should have the border * @param sheet The sheet that the region is on. * @since POI 3.15 beta 2 */ public static void setRightBorderColor(int color, CellRangeAddress region, Sheet sheet) { int rowStart = region.getFirstRow(); int rowEnd = region.getLastRow(); int column = region.getLastColumn(); CellPropertySetter cps = new CellPropertySetter(CellUtil.RIGHT_BORDER_COLOR, color); for (int i = rowStart; i <= rowEnd; i++) { cps.setProperty(CellUtil.getRow(i, sheet), column); } }
/** * Sets the bottom border color for a region of cells by manipulating the cell style of the individual * cells on the bottom * * @param color The color of the border * @param region The region that should have the border * @param sheet The sheet that the region is on. * @since POI 3.15 beta 2 */ public static void setBottomBorderColor(int color, CellRangeAddress region, Sheet sheet) { int colStart = region.getFirstColumn(); int colEnd = region.getLastColumn(); int rowIndex = region.getLastRow(); CellPropertySetter cps = new CellPropertySetter(CellUtil.BOTTOM_BORDER_COLOR, color); Row row = CellUtil.getRow(rowIndex, sheet); for (int i = colStart; i <= colEnd; i++) { cps.setProperty(row, i); } }
/** * Sets the bottom border style for a region of cells by manipulating the cell style of the individual * cells on the bottom * * @param border The new border * @param region The region that should have the border * @param sheet The sheet that the region is on. * @since POI 3.16 beta 1 */ public static void setBorderBottom(BorderStyle border, CellRangeAddress region, Sheet sheet) { int colStart = region.getFirstColumn(); int colEnd = region.getLastColumn(); int rowIndex = region.getLastRow(); CellPropertySetter cps = new CellPropertySetter(CellUtil.BORDER_BOTTOM, border); Row row = CellUtil.getRow(rowIndex, sheet); for (int i = colStart; i <= colEnd; i++) { cps.setProperty(row, i); } }
public void serialize(LittleEndianOutput out) { out.writeShort(getFirstRow()); out.writeShort(getLastRow()); out.writeShort(getFirstColumn()); out.writeShort(getLastColumn()); }
public void setArrayFormula(CellRangeAddress r, Ptg[] ptgs) { ArrayRecord arr = new ArrayRecord(Formula.create(ptgs), new CellRangeAddress8Bit(r.getFirstRow(), r.getLastRow(), r.getFirstColumn(), r.getLastColumn())); _sharedValueManager.addArrayRecord(arr); } /**
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); } } } } }