Exemplo n.º 1
0
  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());
      }
    }
  }
Exemplo n.º 6
0
  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());
  }