示例#1
0
  protected final String formatReferenceAsString() {
    CellReference topLeft =
        new CellReference(
            getFirstRow(), getFirstColumn(), !isFirstRowRelative(), !isFirstColRelative());
    CellReference botRight =
        new CellReference(
            getLastRow(), getLastColumn(), !isLastRowRelative(), !isLastColRelative());

    if (AreaReference.isWholeColumnReference(SpreadsheetVersion.EXCEL97, topLeft, botRight)) {
      return (new AreaReference(topLeft, botRight)).formatAsString();
    }
    return topLeft.formatAsString() + ":" + botRight.formatAsString();
  }
  public void printWorksheetContent() throws Exception {
    FrontDesk desk = FrontDesk.getInstance();

    // read policy & rule
    DynamicPolicy policy = SifMarshaller.unmarshal(new File(policyPath));

    @SuppressWarnings("unchecked")
    DynamicInspectionRequest<Workbook> req =
        (DynamicInspectionRequest<Workbook>)
            desk.requestNewDynamicInspection("fubar", new File(filepath));
    FrontDesk.getInstance().scan();
    FrontDesk.getInstance().register(policy);
    FrontDesk.getInstance().setPolicy(policy);

    DynamicPolicyRule rule = (DynamicPolicyRule) policy.getRuleByName("greaterThan0");
    assertTrue("No rule with name \"greaterThan0\" was found", rule != null);

    // write TestInput in PoiWorkbook
    POIWriter writer = new POIWriter();
    writer.insertTestInput(rule, req.getExternalSpreadsheet());
    Workbook wb = req.getExternalSpreadsheet();

    Sheet sheet1 = wb.getSheetAt(0);
    for (Row row : sheet1) {
      for (Cell cell : row) {
        CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
        System.out.print(row.getRowNum() + ", " + cell.getColumnIndex());
        System.out.print(" - ");
        System.out.print(cellRef.formatAsString());
        System.out.print(" - ");

        switch (cell.getCellType()) {
          case Cell.CELL_TYPE_STRING:
            System.out.println(cell.getRichStringCellValue().getString());
            break;
          case Cell.CELL_TYPE_NUMERIC:
            if (DateUtil.isCellDateFormatted(cell)) {
              System.out.println(cell.getDateCellValue());
            } else {
              System.out.println(cell.getNumericCellValue());
            }
            break;
          case Cell.CELL_TYPE_BOOLEAN:
            System.out.println(cell.getBooleanCellValue());
            break;
          case Cell.CELL_TYPE_FORMULA:
            System.out.println(cell.getCellFormula());
            break;
          default:
            System.out.println();
        }
      }
    }
  }
  public void testModifyArrayCells_removeCell() {
    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();

    Row srow = sheet.getRow(cra.getFirstRow());
    assertSame(srow, scell.getRow());
    srow.removeCell(scell);
    assertNull(srow.getCell(cra.getFirstColumn()));

    // re-create the removed cell
    scell = srow.createCell(cra.getFirstColumn());
    assertEquals(Cell.CELL_TYPE_BLANK, scell.getCellType());
    assertFalse(scell.isPartOfArrayFormulaGroup());

    // we cannot remove 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 {
        mrow.removeCell(mcell);
        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 Row.removeCell leaves the row
      // in the state that it was in prior to the invocation
      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_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());
    }
  }
示例#6
0
 public static String offsetCellIndex(String cellIndex, int rowOffset, int colOffset) {
   CellReference cell = new CellReference(cellIndex);
   CellReference newCell = new CellReference(cell.getRow() + rowOffset, cell.getCol() + colOffset);
   return newCell.formatAsString().replaceAll("\\$", "");
 }
示例#7
0
 public static String getCellIndex(int row, int col) {
   CellReference cell = new CellReference(row, col);
   return cell.formatAsString().replaceAll("\\$", "");
 }