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());
  }
Esempio n. 2
0
 /**
  * This is similar to the other paste, but take range as a parameter. This is used together with
  * getRange.
  *
  * @param table SharpTableModel you are pasting to
  * @param range range you are pasting to
  */
 public void paste(SpreadsheetTableModelClipboardInterface table, CellRange range) {
   // if region to paste to is out of bounds
   if (range != null) {
     int rowOff = range.getStartRow() - source.getStartRow();
     int colOff = range.getStartCol() - source.getStartCol();
     table.fromString(text, '\t', rowOff, colOff, range);
   }
 }
Esempio n. 3
0
  /**
   * Tests if a cell range can be used as the source for a pattern drag-copy.
   *
   * @param cellRange
   * @return
   */
  private static boolean isPatternSource(CellRange cellRange) {
    // don't allow empty cells
    if (cellRange.hasEmptyCells()) {
      return false;
    }

    // test for any unacceptable geos in the range
    ArrayList<GeoElement> list = cellRange.toGeoList();
    for (GeoElement geo : list) {
      if (!(geo.isGeoNumeric() || geo.isGeoFunction() || geo.isGeoPoint())) {
        return false;
      }
    }

    return true;
  }
Esempio n. 4
0
  /**
   * This gets the actual range of a paste from a corner point. This is actually a helper method for
   * paste
   *
   * @param corner the upper left corner coordinate
   * @return the actual cell range; null if it's beyond the table range
   * @param model the SharpTableModel you are using
   */
  public CellRange getRange(SpreadsheetTableModelClipboardInterface model, CellPoint corner) {
    // limit to paste region
    int rowLimit = model.getRowCount() - 1;
    int colLimit = model.getColumnCount() - 1;

    // calculate dimensions of clipboard
    int rowMax = (corner.getRow() + source.getHeight()) - 1;
    int colMax = (corner.getCol() + source.getWidth()) - 1;

    // cannot paste to nonexistent cells
    if ((corner.getRow() < 0) || (corner.getCol() < 0)) {
      return null;
    } else {
      // paste as much as you can
      return new CellRange(
          corner, new CellPoint(Math.min(rowMax, rowLimit), Math.min(colMax, colLimit)));
    }
  }
  public void testModifyArrayCells_removeRow() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells
    CellRangeAddress cra = CellRangeAddress.valueOf("B5");
    CellRange<? extends Cell> srange = sheet.setArrayFormula("SUM(A4:A6,B4:B6)", cra);
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());

    Row srow = scell.getRow();
    assertSame(srow, sheet.getRow(cra.getFirstRow()));
    sheet.removeRow(srow);
    assertNull(sheet.getRow(cra.getFirstRow()));

    // re-create the removed row and cell
    scell = sheet.createRow(cra.getFirstRow()).createCell(cra.getFirstColumn());
    assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
    assertFalse(scell.isPartOfArrayFormulaGroup());

    // we cannot remove rows with cells included in a multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      int columnIndex = mcell.getColumnIndex();
      Row mrow = mcell.getRow();
      try {
        sheet.removeRow(mrow);
        fail("expected exception");
      } catch (IllegalStateException e) {
        String msg =
            "Row[rownum="
                + mrow.getRowNum()
                + "] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Row.removeCell leaves the row
      // in the state that it was in prior to the invocation
      assertSame(mrow, sheet.getRow(mrow.getRowNum()));
      assertSame(mcell, mrow.getCell(columnIndex));
      assertTrue(mcell.isPartOfArrayFormulaGroup());
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
    }
  }
  public void testModifyArrayCells_setCellFormula() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertTrue(scell.isPartOfArrayFormulaGroup());
    scell.setCellFormula("SUM(A4,A6)");
    // we are now a normal formula cell
    assertEquals("SUM(A4,A6)", scell.getCellFormula());
    assertFalse(scell.isPartOfArrayFormulaGroup());
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    // check that setting formula result works
    assertEquals(0.0, scell.getNumericCellValue());
    scell.setCellValue(33.0);
    assertEquals(33.0, scell.getNumericCellValue());

    // multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      // we cannot set individual formulas for cells included in an array formula
      try {
        assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
        mcell.setCellFormula("A1+A2");
        fail("expected exception");
      } catch (IllegalStateException e) {
        CellReference ref = new CellReference(mcell);
        String msg =
            "Cell "
                + ref.formatAsString()
                + " is part of a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Cell.setCellFormula leaves the cell
      // in the state that it was in prior to the invocation
      assertEquals("A1:A3*B1:B3", mcell.getCellFormula());
      assertTrue(mcell.isPartOfArrayFormulaGroup());
    }
  }
  public void testModifyArrayCells_shiftRows() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells - we can change the cell type
    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals("SUM(A4:A6,B4:B6)", scell.getCellFormula());
    sheet.shiftRows(0, 0, 1);
    sheet.shiftRows(0, 1, 1);

    // we cannot set individual formulas for cells included in an array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));

    try {
      sheet.shiftRows(0, 0, 1);
      fail("expected exception");
    } catch (IllegalStateException e) {
      String msg =
          "Row[rownum=0] contains cell(s) included in a multi-cell array formula. You cannot change part of an array.";
      assertEquals(msg, e.getMessage());
    }
    /*
     TODO: enable shifting the whole array

    sheet.shiftRows(0, 2, 1);
    //the array C1:C3 is now C2:C4
    CellRangeAddress cra = CellRangeAddress.valueOf("C2:C4");
    for(Cell mcell : mrange){
        //TODO define equals and hashcode for CellRangeAddress
        assertEquals(cra.formatAsString(), mcell.getArrayFormulaRange().formatAsString());
        assertEquals("A2:A4*B2:B4", mcell.getCellFormula());
        assertTrue(mcell.isPartOfArrayFormulaGroup());
        assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
    }

    */
  }
  /** 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 void testModifyArrayCells_setCellType() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formulas behave just like normal cells -
    // changing cell type removes the array formula and associated cached result
    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertEquals(0.0, scell.getNumericCellValue());
    scell.setCellType(Cell.CELL_TYPE_STRING);
    assertEquals(Cell.CELL_TYPE_STRING, scell.getCellType());
    scell.setCellValue("string cell");
    assertEquals("string cell", scell.getStringCellValue());

    // once you create a multi-cell array formula, you cannot change the type of its cells
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      try {
        assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
        mcell.setCellType(Cell.CELL_TYPE_NUMERIC);
        fail("expected exception");
      } catch (IllegalStateException e) {
        CellReference ref = new CellReference(mcell);
        String msg =
            "Cell "
                + ref.formatAsString()
                + " is part of a multi-cell array formula. You cannot change part of an array.";
        assertEquals(msg, e.getMessage());
      }
      // a failed invocation of Cell.setCellType leaves the cell
      // in the state that it was in prior to the invocation
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
      assertTrue(mcell.isPartOfArrayFormulaGroup());
    }
  }
  /** Test that we can set pre-calculated formula result for array formulas */
  public void testModifyArrayCells_setFormulaResult() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    // single-cell array formula
    CellRange<? extends Cell> srange =
        sheet.setArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.valueOf("B5"));
    Cell scell = srange.getTopLeftCell();
    assertEquals(Cell.CELL_TYPE_FORMULA, scell.getCellType());
    assertEquals(0.0, scell.getNumericCellValue());
    scell.setCellValue(1.1);
    assertEquals(1.1, scell.getNumericCellValue());

    // multi-cell array formula
    CellRange<? extends Cell> mrange =
        sheet.setArrayFormula("A1:A3*B1:B3", CellRangeAddress.valueOf("C1:C3"));
    for (Cell mcell : mrange) {
      assertEquals(Cell.CELL_TYPE_FORMULA, mcell.getCellType());
      assertEquals(0.0, mcell.getNumericCellValue());
      double fmlaResult = 1.2;
      mcell.setCellValue(fmlaResult);
      assertEquals(fmlaResult, mcell.getNumericCellValue());
    }
  }