public final void testShiftWithMergedRegions() { Workbook wb = _testDataProvider.createWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow(0); row.createCell(0).setCellValue(1.1); row.createCell(1).setCellValue(2.2); CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2); assertEquals("A1:C1", region.formatAsString()); sheet.addMergedRegion(region); sheet.shiftRows(0, 1, 2); region = sheet.getMergedRegion(0); assertEquals("A3:C3", region.formatAsString()); }
public int addConditionalFormatting( CellRangeAddress[] regions, ConditionalFormattingRule[] cfRules) { if (regions == null) { throw new IllegalArgumentException("regions must not be null"); } for (CellRangeAddress range : regions) range.validate(SpreadsheetVersion.EXCEL2007); if (cfRules == null) { throw new IllegalArgumentException("cfRules must not be null"); } if (cfRules.length == 0) { throw new IllegalArgumentException("cfRules must not be empty"); } if (cfRules.length > 3) { throw new IllegalArgumentException("Number of rules must not exceed 3"); } CellRangeAddress[] mergeCellRanges = CellRangeUtil.mergeCellRanges(regions); CTConditionalFormatting cf = _sheet.getCTWorksheet().addNewConditionalFormatting(); List<String> refs = new ArrayList<String>(); for (CellRangeAddress a : mergeCellRanges) refs.add(a.formatAsString()); cf.setSqref(refs); int priority = 1; for (CTConditionalFormatting c : _sheet.getCTWorksheet().getConditionalFormattingArray()) { priority += c.sizeOfCfRuleArray(); } for (ConditionalFormattingRule rule : cfRules) { XSSFConditionalFormattingRule xRule = (XSSFConditionalFormattingRule) rule; xRule.getCTCfRule().setPriority(priority++); cf.addNewCfRule().set(xRule.getCTCfRule()); } return _sheet.getCTWorksheet().sizeOfConditionalFormattingArray() - 1; }
/** Set multi-cell array formula */ public final void testSetArrayFormula_multiCell() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); // multi-cell formula // rows 3-5 don't exist yet assertNull(sheet.getRow(3)); assertNull(sheet.getRow(4)); assertNull(sheet.getRow(5)); CellRangeAddress range = CellRangeAddress.valueOf("C4:C6"); Cell[] cells = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range).getFlattenedCells(); assertEquals(3, cells.length); // sheet.setArrayFormula creates rows and cells for the designated range assertSame(cells[0], sheet.getRow(3).getCell(2)); assertSame(cells[1], sheet.getRow(4).getCell(2)); assertSame(cells[2], sheet.getRow(5).getCell(2)); for (Cell acell : cells) { assertTrue(acell.isPartOfArrayFormulaGroup()); assertEquals(Cell.CELL_TYPE_FORMULA, acell.getCellType()); assertEquals("SUM(A1:A3*B1:B3)", acell.getCellFormula()); // retrieve the range and check it is the same assertEquals(range.formatAsString(), acell.getArrayFormulaRange().formatAsString()); } }
public void testModifyArrayCells_mergeCells() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); assertEquals(0, sheet.getNumMergedRegions()); // single-cell array formulas behave just like normal cells CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5")); Cell scell = srange.getTopLeftCell(); sheet.addMergedRegion(CellRangeAddress.valueOf("B5:C6")); // we are still an array formula assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType()); assertTrue(scell.isPartOfArrayFormulaGroup()); assertEquals(1, sheet.getNumMergedRegions()); // we cannot merge cells included in an array formula CellRange<? extends Cell> mrange = sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3")); CellRangeAddress cra = CellRangeAddress.valueOf("C1:C3"); try { sheet.addMergedRegion(cra); fail("expected exception"); } catch (IllegalStateException e) { String msg = "The range " + cra.formatAsString() + " intersects with a multi-cell array formula. You cannot merge cells of an array."; assertEquals(msg, e.getMessage()); } // the number of merged regions remains the same assertEquals(1, sheet.getNumMergedRegions()); }
/** create and remove array formulas */ public final void testRemoveArrayFormula() { Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2); assertEquals("C4:C6", range.formatAsString()); CellRange<?> cr = sheet.setArrayFormula("SUM(A1:A3*B1:B3)", range); assertEquals(3, cr.size()); // remove the formula cells in C4:C6 CellRange<?> dcells = sheet.removeArrayFormula(cr.getTopLeftCell()); // removeArrayFormula should return the same cells as setArrayFormula assertTrue(Arrays.equals(cr.getFlattenedCells(), dcells.getFlattenedCells())); for (Cell acell : cr) { assertFalse(acell.isPartOfArrayFormulaGroup()); assertEquals(Cell.CELL_TYPE_BLANK, acell.getCellType()); } // cells C4:C6 are not included in array formula, // invocation of sheet.removeArrayFormula on any of them throws IllegalArgumentException for (Cell acell : cr) { try { sheet.removeArrayFormula(acell); fail("expected exception"); } catch (IllegalArgumentException e) { String ref = new CellReference(acell).formatAsString(); assertEquals("Cell " + ref + " is not part of an array formula.", e.getMessage()); } } }
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(); }
/** Set single-cell array formula */ public final void testSetArrayFormula_singleCell() { Cell[] cells; Workbook workbook = _testDataProvider.createWorkbook(); Sheet sheet = workbook.createSheet(); Cell cell = sheet.createRow(0).createCell(0); assertFalse(cell.isPartOfArrayFormulaGroup()); try { cell.getArrayFormulaRange(); fail("expected exception"); } catch (IllegalStateException e) { assertEquals("Cell A1 is not part of an array formula.", e.getMessage()); } // row 3 does not yet exist assertNull(sheet.getRow(2)); CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2); cells = sheet.setArrayFormula("SUM(C11:C12*D11:D12)", range).getFlattenedCells(); assertEquals(1, cells.length); // sheet.setArrayFormula creates rows and cells for the designated range assertNotNull(sheet.getRow(2)); cell = sheet.getRow(2).getCell(2); assertNotNull(cell); assertTrue(cell.isPartOfArrayFormulaGroup()); // retrieve the range and check it is the same assertEquals(range.formatAsString(), cell.getArrayFormulaRange().formatAsString()); // check the formula assertEquals("SUM(C11:C12*D11:D12)", cell.getCellFormula()); }