예제 #1
1
  /**
   * All multi-binomial operator tests use this to create a worksheet with a huge set of x operator
   * y formulas. Next we call binomialVerify and verify that they are all how we expect.
   */
  private static void binomialOperator(String operator) {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula(1 + operator + 1);

    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
      r = s.createRow(x);

      for (int y = 1; y < 256 && y > 0; y++) {

        c = r.createCell(y);
        c.setCellFormula("" + x + operator + y);
      }
    }

    // make sure we do the maximum value of the Int operator
    if (s.getLastRowNum() < Short.MAX_VALUE) {
      r = s.getRow(0);
      c = r.createCell(0);
      c.setCellFormula("" + Short.MAX_VALUE + operator + Short.MAX_VALUE);
    }
    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    binomialVerify(operator, wb);
  }
예제 #2
1
  private static void floatTest(String operator) {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values

    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula("" + Float.MIN_VALUE + operator + Float.MIN_VALUE);

    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
      r = s.createRow(x);

      for (int y = 1; y < 256 && y > 0; y = (short) (y + 2)) {

        c = r.createCell(y);
        c.setCellFormula("" + x + "." + y + operator + y + "." + x);
      }
    }
    if (s.getLastRowNum() < Short.MAX_VALUE) {
      r = s.createRow(0);
      c = r.createCell(0);
      c.setCellFormula("" + Float.MAX_VALUE + operator + Float.MAX_VALUE);
    }
    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);

    floatVerify(operator, wb);
  }
  /**
   * Sometimes the 'shared formula' flag (<tt>FormulaRecord.isSharedFormula()</tt>) is set when
   * there is no corresponding SharedFormulaRecord available. SharedFormulaRecord definitions do not
   * span multiple sheets. They are are only defined within a sheet, and thus they do not have a
   * sheet index field (only row and column range fields).<br>
   * So it is important that the code which locates the SharedFormulaRecord for each FormulaRecord
   * does not allow matches across sheets.</br>
   *
   * <p>Prior to bugzilla 44449 (Feb 2008), POI <tt>ValueRecordsAggregate.construct(int, List)</tt>
   * allowed <tt>SharedFormulaRecord</tt>s to be erroneously used across sheets. That incorrect
   * behaviour is shown by this test.
   *
   * <p><b>Notes on how to produce the test spreadsheet</b>: The setup for this test
   * (AbnormalSharedFormulaFlag.xls) is rather fragile, insomuchas re-saving the file (either with
   * Excel or POI) clears the flag.<br>
   *
   * <ol>
   *   <li>A new spreadsheet was created in Excel (File | New | Blank Workbook).
   *   <li>Sheet3 was deleted.
   *   <li>Sheet2!A1 formula was set to '="second formula"', and fill-dragged through A1:A8.
   *   <li>Sheet1!A1 formula was set to '="first formula"', and also fill-dragged through A1:A8.
   *   <li>Four rows on Sheet1 "5" through "8" were deleted ('delete rows' alt-E D, not 'clear'
   *       Del).
   *   <li>The spreadsheet was saved as AbnormalSharedFormulaFlag.xls.
   * </ol>
   *
   * Prior to the row delete action the spreadsheet has two <tt>SharedFormulaRecord</tt>s. One for
   * each sheet. To expose the bug, the shared formulas have been made to overlap.<br>
   * The row delete action (as described here) seems to to delete the <tt>SharedFormulaRecord</tt>
   * from Sheet1 (but not clear the 'shared formula' flags.<br>
   * There are other variations on this theme to create the same effect.
   */
  public void testSpuriousSharedFormulaFlag() {

    long actualCRC =
        getFileCRC(
            HSSFTestDataSamples.openSampleFileStream(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE));
    long expectedCRC = 2277445406L;
    if (actualCRC != expectedCRC) {
      System.err.println("Expected crc " + expectedCRC + " but got " + actualCRC);
      throw failUnexpectedTestFileChange();
    }
    HSSFWorkbook wb =
        HSSFTestDataSamples.openSampleWorkbook(ABNORMAL_SHARED_FORMULA_FLAG_TEST_FILE);

    HSSFSheet s = wb.getSheetAt(0); // Sheet1

    String cellFormula;
    cellFormula = getFormulaFromFirstCell(s, 0); // row "1"
    // the problem is not observable in the first row of the shared formula
    if (!cellFormula.equals("\"first formula\"")) {
      throw new RuntimeException("Something else wrong with this test case");
    }

    // but the problem is observable in rows 2,3,4
    cellFormula = getFormulaFromFirstCell(s, 1); // row "2"
    if (cellFormula.equals("\"second formula\"")) {
      throw new AssertionFailedError("found bug 44449 (Wrong SharedFormulaRecord was used).");
    }
    if (!cellFormula.equals("\"first formula\"")) {
      throw new RuntimeException("Something else wrong with this test case");
    }
  }
예제 #4
0
  /** Writes a function then tests to see if its correct */
  public void refAreaArrayFunctionTest(String function) {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    r = s.createRow(0);

    c = r.createCell(0);
    c.setCellFormula(function + "(A2:A4,B2:B4)");
    c = r.createCell(1);
    c.setCellFormula(function + "($A$2:$A4,B$2:B4)");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(0);

    assertTrue(
        "function =" + function + "(A2:A4,B2:B4)",
        ((function + "(A2:A4,B2:B4)").equals(c.getCellFormula())));

    c = r.getCell(1);
    assertTrue(
        "function =" + function + "($A$2:$A4,B$2:B4)",
        ((function + "($A$2:$A4,B$2:B4)").equals(c.getCellFormula())));
  }
예제 #5
0
  public void testClearShapesForPatriarch() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    patriarch.createSimpleShape(new HSSFClientAnchor());
    patriarch.createSimpleShape(new HSSFClientAnchor());
    patriarch.createCellComment(new HSSFClientAnchor());

    EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);

    assertEquals(agg.getShapeToObjMapping().size(), 6);
    assertEquals(agg.getTailRecords().size(), 1);
    assertEquals(patriarch.getChildren().size(), 3);

    patriarch.clear();

    assertEquals(agg.getShapeToObjMapping().size(), 0);
    assertEquals(agg.getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(agg.getShapeToObjMapping().size(), 0);
    assertEquals(agg.getTailRecords().size(), 0);
    assertEquals(patriarch.getChildren().size(), 0);
  }
예제 #6
0
  public void testRotation() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    HSSFSimpleShape rectangle =
        patriarch.createSimpleShape(
            new HSSFClientAnchor(0, 0, 100, 100, (short) 0, 0, (short) 5, 5));
    rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);

    assertEquals(rectangle.getRotationDegree(), 0);
    rectangle.setRotationDegree((short) 45);
    assertEquals(rectangle.getRotationDegree(), 45);
    rectangle.setFlipHorizontal(true);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();
    rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
    assertEquals(rectangle.getRotationDegree(), 45);
    rectangle.setRotationDegree((short) 30);
    assertEquals(rectangle.getRotationDegree(), 30);

    patriarch.setCoordinates(0, 0, 10, 10);
    rectangle.setString(new HSSFRichTextString("1234"));
  }
예제 #7
0
  public void testAbsRefs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r;
    HSSFCell c;

    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellFormula("A3+A2");
    c = r.createCell(1);
    c.setCellFormula("$A3+$A2");
    c = r.createCell(2);
    c.setCellFormula("A$3+A$2");
    c = r.createCell(3);
    c.setCellFormula("$A$3+$A$2");
    c = r.createCell(4);
    c.setCellFormula("SUM($A$3,$A$2)");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(0);
    assertTrue("A3+A2", ("A3+A2").equals(c.getCellFormula()));
    c = r.getCell(1);
    assertTrue("$A3+$A2", ("$A3+$A2").equals(c.getCellFormula()));
    c = r.getCell(2);
    assertTrue("A$3+A$2", ("A$3+A$2").equals(c.getCellFormula()));
    c = r.getCell(3);
    assertTrue("$A$3+$A$2", ("$A$3+$A$2").equals(c.getCellFormula()));
    c = r.getCell(4);
    assertTrue("SUM($A$3,$A$2)", ("SUM($A$3,$A$2)").equals(c.getCellFormula()));
  }
예제 #8
0
  public void testSheetFunctions() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("A");
    HSSFRow r = null;
    HSSFCell c = null;
    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellValue(1);
    c = r.createCell(1);
    c.setCellValue(2);

    s = wb.createSheet("B");
    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellFormula("AVERAGE(A!A1:B1)");
    c = r.createCell(1);
    c.setCellFormula("A!A1+A!B1");
    c = r.createCell(2);
    c.setCellFormula("A!$A$1+A!$B1");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);

    s = wb.getSheet("B");
    r = s.getRow(0);
    c = r.getCell(0);
    assertTrue(
        "expected: AVERAGE(A!A1:B1) got: " + c.getCellFormula(),
        ("AVERAGE(A!A1:B1)").equals(c.getCellFormula()));
    c = r.getCell(1);
    assertTrue(
        "expected: A!A1+A!B1 got: " + c.getCellFormula(), ("A!A1+A!B1").equals(c.getCellFormula()));
  }
예제 #9
0
  public void testDateFormulas() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("testSheet1");
    HSSFRow r = null;
    HSSFCell c = null;

    r = s.createRow(0);
    c = r.createCell(0);

    HSSFCellStyle cellStyle = wb.createCellStyle();
    cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
    c.setCellValue(new Date());
    c.setCellStyle(cellStyle);

    // assertEquals("Checking hour = " + hour, date.getTime().getTime(),
    //              HSSFDateUtil.getJavaDate(excelDate).getTime());

    for (int k = 1; k < 100; k++) {
      r = s.createRow(k);
      c = r.createCell(0);
      c.setCellFormula("A" + (k) + "+1");
      c.setCellStyle(cellStyle);
    }

    HSSFTestDataSamples.writeOutAndReadBack(wb);
  }
예제 #10
0
  /**
   * Test get new id for shapes from existing file File already have for 1 shape on each sheet,
   * because document must contain EscherDgRecord for each sheet
   */
  public void testAllocateNewIds() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("empty.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFPatriarch patriarch = sheet.getDrawingPatriarch();

    /** 2048 - main SpContainer id 2049 - existing shape id */
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2050);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2051);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 2052);

    sheet = wb.getSheetAt(1);
    patriarch = sheet.getDrawingPatriarch();

    /** 3072 - main SpContainer id 3073 - existing shape id */
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3074);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3075);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 3076);

    sheet = wb.getSheetAt(2);
    patriarch = sheet.getDrawingPatriarch();

    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1026);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1027);
    assertEquals(HSSFTestHelper.allocateNewShapeId(patriarch), 1028);
  }
예제 #11
0
  public void testShapeIds() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet1 = wb.createSheet();
    HSSFPatriarch patriarch1 = sheet1.createDrawingPatriarch();
    for (int i = 0; i < 2; i++) {
      patriarch1.createSimpleShape(new HSSFClientAnchor());
    }

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet1 = wb.getSheetAt(0);
    patriarch1 = sheet1.getDrawingPatriarch();

    EscherAggregate agg1 = HSSFTestHelper.getEscherAggregate(patriarch1);
    // last shape ID cached in EscherDgRecord
    EscherDgRecord dg1 = agg1.getEscherContainer().getChildById(EscherDgRecord.RECORD_ID);
    assertEquals(1026, dg1.getLastMSOSPID());

    // iterate over shapes and check shapeId
    EscherContainerRecord spgrContainer = agg1.getEscherContainer().getChildContainers().get(0);
    // root spContainer + 2 spContainers for shapes
    assertEquals(3, spgrContainer.getChildRecords().size());

    EscherSpRecord sp0 =
        ((EscherContainerRecord) spgrContainer.getChild(0)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1024, sp0.getShapeId());

    EscherSpRecord sp1 =
        ((EscherContainerRecord) spgrContainer.getChild(1)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1025, sp1.getShapeId());

    EscherSpRecord sp2 =
        ((EscherContainerRecord) spgrContainer.getChild(2)).getChildById(EscherSpRecord.RECORD_ID);
    assertEquals(1026, sp2.getShapeId());
  }
예제 #12
0
  private static void operationRefTest(String operator) {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula("A2" + operator + "A3");

    for (int x = 1; x < Short.MAX_VALUE && x > 0; x = (short) (x * 2)) {
      r = s.createRow(x);

      for (int y = 1; y < 256 && y > 0; y++) {

        String ref = null;
        String ref2 = null;
        short refx1 = 0;
        short refy1 = 0;
        short refx2 = 0;
        short refy2 = 0;
        if (x + 50 < Short.MAX_VALUE) {
          refx1 = (short) (x + 50);
          refx2 = (short) (x + 46);
        } else {
          refx1 = (short) (x - 4);
          refx2 = (short) (x - 3);
        }

        if (y + 50 < 255) {
          refy1 = (short) (y + 50);
          refy2 = (short) (y + 49);
        } else {
          refy1 = (short) (y - 4);
          refy2 = (short) (y - 3);
        }

        c = r.getCell(y);
        CellReference cr = new CellReference(refx1, refy1, false, false);
        ref = cr.formatAsString();
        cr = new CellReference(refx2, refy2, false, false);
        ref2 = cr.formatAsString();

        c = r.createCell(y);
        c.setCellFormula("" + ref + operator + ref2);
      }
    }

    // make sure we do the maximum value of the Int operator
    if (s.getLastRowNum() < Short.MAX_VALUE) {
      r = s.getRow(0);
      c = r.createCell(0);
      c.setCellFormula("" + "B1" + operator + "IV255");
    }

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    operationalRefVerify(operator, wb);
  }
  /** tests <tt>NameXPtg.toFormulaString(Workbook)</tt> and logic in Workbook below that */
  public void testReadFormulaContainingExternalFunction() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");

    String expectedFormula = "YEARFRAC(B1,C1)";
    HSSFSheet sht = wb.getSheetAt(0);
    String cellFormula = sht.getRow(0).getCell(0).getCellFormula();
    assertEquals(expectedFormula, cellFormula);
  }
예제 #14
0
 /**
  * Test that the HSSFFormulaEvaluator can evaluate simple named ranges (single cells and
  * rectangular areas)
  */
 public void testEvaluateSimple() {
   HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("testNames.xls");
   HSSFSheet sheet = wb.getSheetAt(0);
   HSSFCell cell = sheet.getRow(8).getCell(0);
   HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
   CellValue cv = fe.evaluate(cell);
   assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
   assertEquals(3.72, cv.getNumberValue(), 0.0);
 }
 public void testEvaluate() {
   HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
   HSSFSheet sheet = wb.getSheetAt(0);
   HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
   confirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0 / 90.0);
   confirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0);
   confirmCellEval(sheet, 2, 0, fe, "YEARFRAC(B3,C3,D3)", 0.0);
   confirmCellEval(sheet, 3, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6);
   confirmCellEval(sheet, 4, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2);
 }
예제 #16
0
  /**
   * Example from
   * http://office.microsoft.com/en-us/excel-help/intercept-function-HP010062512.aspx?CTT=5&origin=HA010277524
   */
  public void testFromFile() {

    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("intercept.xls");
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

    HSSFSheet example1 = wb.getSheet("Example 1");
    HSSFCell a8 = example1.getRow(7).getCell(0);
    assertEquals("INTERCEPT(A2:A6,B2:B6)", a8.getCellFormula());
    fe.evaluate(a8);
    assertEquals(0.048387097, a8.getNumericCellValue(), 0.000000001);
  }
예제 #17
0
  public void testTextForSimpleShape() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    HSSFSimpleShape shape = patriarch.createSimpleShape(new HSSFClientAnchor());
    shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);

    EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);
    assertEquals(agg.getShapeToObjMapping().size(), 2);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    shape = (HSSFSimpleShape) patriarch.getChildren().get(0);

    agg = HSSFTestHelper.getEscherAggregate(patriarch);
    assertEquals(agg.getShapeToObjMapping().size(), 2);

    shape.setString(new HSSFRichTextString("string1"));
    assertEquals(shape.getString().getString(), "string1");

    assertNotNull(
        HSSFTestHelper.getEscherContainer(shape).getChildById(EscherTextboxRecord.RECORD_ID));
    assertEquals(agg.getShapeToObjMapping().size(), 2);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    shape = (HSSFSimpleShape) patriarch.getChildren().get(0);

    assertNotNull(HSSFTestHelper.getTextObjRecord(shape));
    assertEquals(shape.getString().getString(), "string1");
    assertNotNull(
        HSSFTestHelper.getEscherContainer(shape).getChildById(EscherTextboxRecord.RECORD_ID));
    assertEquals(agg.getShapeToObjMapping().size(), 2);
  }
예제 #18
0
 /**
  * HSSFShape tree bust be built correctly Check file with such records structure: -patriarch
  * --shape --group ---group ----shape ----shape ---shape ---group ----shape ----shape
  */
 public void testDrawingGroups() {
   HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
   HSSFSheet sheet = wb.getSheet("groups");
   HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
   assertEquals(patriarch.getChildren().size(), 2);
   HSSFShapeGroup group = (HSSFShapeGroup) patriarch.getChildren().get(1);
   assertEquals(3, group.getChildren().size());
   HSSFShapeGroup group1 = (HSSFShapeGroup) group.getChildren().get(0);
   assertEquals(2, group1.getChildren().size());
   group1 = (HSSFShapeGroup) group.getChildren().get(2);
   assertEquals(2, group1.getChildren().size());
 }
예제 #19
0
  public void testShapeFlip() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet();
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();

    HSSFSimpleShape rectangle = patriarch.createSimpleShape(new HSSFClientAnchor());
    rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);

    assertEquals(rectangle.isFlipVertical(), false);
    assertEquals(rectangle.isFlipHorizontal(), false);

    rectangle.setFlipVertical(true);
    assertEquals(rectangle.isFlipVertical(), true);
    rectangle.setFlipHorizontal(true);
    assertEquals(rectangle.isFlipHorizontal(), true);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);

    assertEquals(rectangle.isFlipHorizontal(), true);
    rectangle.setFlipHorizontal(false);
    assertEquals(rectangle.isFlipHorizontal(), false);

    assertEquals(rectangle.isFlipVertical(), true);
    rectangle.setFlipVertical(false);
    assertEquals(rectangle.isFlipVertical(), false);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    sheet = wb.getSheetAt(0);
    patriarch = sheet.getDrawingPatriarch();

    rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);

    assertEquals(rectangle.isFlipVertical(), false);
    assertEquals(rectangle.isFlipHorizontal(), false);
  }
예제 #20
0
  public void testSumIf() {
    String function = "SUMIF(A1:A5,\">4000\",B1:B5)";

    HSSFWorkbook wb = openSample("sumifformula.xls");

    HSSFSheet s = wb.getSheetAt(0);
    HSSFRow r = s.getRow(0);
    HSSFCell c = r.getCell(2);
    assertEquals(function, c.getCellFormula());

    wb = new HSSFWorkbook();
    s = wb.createSheet();

    r = s.createRow(0);
    c = r.createCell(0);
    c.setCellValue(1000);
    c = r.createCell(1);
    c.setCellValue(1);

    r = s.createRow(1);
    c = r.createCell(0);
    c.setCellValue(2000);
    c = r.createCell(1);
    c.setCellValue(2);

    r = s.createRow(2);
    c = r.createCell(0);
    c.setCellValue(3000);
    c = r.createCell(1);
    c.setCellValue(3);

    r = s.createRow(3);
    c = r.createCell(0);
    c.setCellValue(4000);
    c = r.createCell(1);
    c.setCellValue(4);

    r = s.createRow(4);
    c = r.createCell(0);
    c.setCellValue(5000);
    c = r.createCell(1);
    c.setCellValue(5);

    r = s.getRow(0);
    c = r.createCell(2);
    c.setCellFormula(function);

    HSSFTestDataSamples.writeOutAndReadBack(wb);
  }
  private void processFile(String filename) throws Exception {
    HSSFRequest req = new HSSFRequest();
    mockListen = new MockHSSFListener();
    listener = new FormatTrackingHSSFListener(mockListen);
    req.addListenerForAllRecords(listener);

    HSSFEventFactory factory = new HSSFEventFactory();
    try {
      InputStream is = HSSFTestDataSamples.openSampleFileStream(filename);
      POIFSFileSystem fs = new POIFSFileSystem(is);
      factory.processWorkbookEvents(req, fs);
    } catch (IOException e) {
      throw new RuntimeException(e);
    }
  }
예제 #22
0
  /* assert shape properties when reading shapes from a existing workbook */
  public void testReadExistingRectangle() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
    HSSFSheet sheet = wb.getSheet("rectangles");
    HSSFPatriarch drawing = sheet.getDrawingPatriarch();
    assertEquals(1, drawing.getChildren().size());

    HSSFSimpleShape shape = (HSSFSimpleShape) drawing.getChildren().get(0);
    assertEquals(shape.isNoFill(), false);
    assertEquals(shape.getLineStyle(), HSSFShape.LINESTYLE_DASHDOTGEL);
    assertEquals(shape.getLineStyleColor(), 0x616161);
    assertEquals(HexDump.toHex(shape.getFillColor()), shape.getFillColor(), 0x2CE03D);
    assertEquals(shape.getLineWidth(), HSSFShape.LINEWIDTH_ONE_PT * 2);
    assertEquals(shape.getString().getString(), "POItest");
    assertEquals(shape.getRotationDegree(), 27);
  }
  @Override
  public void setUp() {
    HSSFRequest req = new HSSFRequest();
    mockListen = new MockHSSFListener();
    listener = new SheetRecordCollectingListener(mockListen);
    req.addListenerForAllRecords(listener);

    HSSFEventFactory factory = new HSSFEventFactory();
    try {
      InputStream is = HSSFTestDataSamples.openSampleFileStream("3dFormulas.xls");
      POIFSFileSystem fs = new POIFSFileSystem(is);
      factory.processWorkbookEvents(req, fs);
    } catch (IOException e) {
      throw new RuntimeException(e);
    }
  }
예제 #24
0
  public void testLogicalFormulas() {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("A");
    HSSFRow r = null;
    HSSFCell c = null;
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula("IF(A1<A2,B1,B2)");

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(1);
    assertEquals("Formula in cell 1 ", "IF(A1<A2,B1,B2)", c.getCellFormula());
  }
예제 #25
0
  public void testReadExistingImage() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
    HSSFSheet sheet = wb.getSheet("pictures");
    HSSFPatriarch drawing = sheet.getDrawingPatriarch();
    assertEquals(1, drawing.getChildren().size());
    HSSFPicture picture = (HSSFPicture) drawing.getChildren().get(0);

    assertEquals(picture.getPictureIndex(), 2);
    assertEquals(picture.getLineStyleColor(), HSSFShape.LINESTYLE__COLOR_DEFAULT);
    assertEquals(picture.getFillColor(), 0x5DC943);
    assertEquals(picture.getLineWidth(), HSSFShape.LINEWIDTH_DEFAULT);
    assertEquals(picture.getLineStyle(), HSSFShape.LINESTYLE_DEFAULT);
    assertEquals(picture.isNoFill(), false);

    picture.setPictureIndex(2);
    assertEquals(picture.getPictureIndex(), 2);
  }
예제 #26
0
  /** Add 1+1 -- WHoohoo! */
  public void testBasicAddIntegers() {

    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.createRow(1);
    c = r.createCell(1);
    c.setCellFormula(1 + "+" + 1);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);
    r = s.getRow(1);
    c = r.getCell(1);

    assertTrue("Formula is as expected", ("1+1".equals(c.getCellFormula())));
  }
예제 #27
0
  /** tests order wrting out == order writing in for a given formula */
  private static void orderTest(String formula) {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet();
    HSSFRow r = null;
    HSSFCell c = null;

    // get our minimum values
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula(formula);

    wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
    s = wb.getSheetAt(0);

    // get our minimum values
    r = s.getRow(0);
    c = r.getCell(1);
    assertTrue("minval Formula is as expected", formula.equals(c.getCellFormula()));
  }
  public void testParse() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
    Ptg[] ptgs = HSSFFormulaParser.parse("YEARFRAC(B1,C1)", wb);
    assertEquals(4, ptgs.length);
    assertEquals(NameXPtg.class, ptgs[0].getClass());

    wb.getSheetAt(0).getRow(0).createCell(6).setCellFormula("YEARFRAC(C1,B1)");
    if (false) {
      // In case you fancy checking in excel
      try {
        File tempFile = TempFile.createTempFile("testExtFunc", ".xls");
        FileOutputStream fout = new FileOutputStream(tempFile);
        wb.write(fout);
        fout.close();
        System.out.println("check out " + tempFile.getAbsolutePath());
      } catch (IOException e) {
        throw new RuntimeException(e);
      }
    }
  }
예제 #29
0
  public void testStringFormulas() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet s = wb.createSheet("A");
    HSSFRow r = null;
    HSSFCell c = null;
    r = s.createRow(0);
    c = r.createCell(1);
    c.setCellFormula("UPPER(\"abc\")");
    c = r.createCell(2);
    c.setCellFormula("LOWER(\"ABC\")");
    c = r.createCell(3);
    c.setCellFormula("CONCATENATE(\" my \",\" name \")");

    HSSFTestDataSamples.writeOutAndReadBack(wb);

    wb = openSample("StringFormulas.xls");
    s = wb.getSheetAt(0);
    r = s.getRow(0);
    c = r.getCell(0);
    assertEquals("UPPER(\"xyz\")", c.getCellFormula());
  }
  public void testFormulas() {

    FormulaRecord[] fRecs = mockListen.getFormulaRecords();

    // Check our formula records
    assertEquals(6, fRecs.length);

    InternalWorkbook stubWB = listener.getStubWorkbook();
    assertNotNull(stubWB);
    HSSFWorkbook stubHSSF = listener.getStubHSSFWorkbook();
    assertNotNull(stubHSSF);

    // Check these stubs have the right stuff on them
    assertEquals("Sheet1", stubWB.getSheetName(0));
    assertEquals("Sheet1", stubHSSF.getSheetName(0));
    assertEquals("S2", stubWB.getSheetName(1));
    assertEquals("S2", stubHSSF.getSheetName(1));
    assertEquals("Sh3", stubWB.getSheetName(2));
    assertEquals("Sh3", stubHSSF.getSheetName(2));

    // Check we can get the formula without breaking
    for (int i = 0; i < fRecs.length; i++) {
      HSSFFormulaParser.toFormulaString(stubHSSF, fRecs[i].getParsedExpression());
    }

    // Peer into just one formula, and check that
    //  all the ptgs give back the right things
    Ptg[] ptgs = fRecs[0].getParsedExpression();
    assertEquals(1, ptgs.length);
    assertTrue(ptgs[0] instanceof Ref3DPtg);

    Ref3DPtg ptg = (Ref3DPtg) ptgs[0];
    HSSFEvaluationWorkbook book = HSSFEvaluationWorkbook.create(stubHSSF);
    assertEquals("Sheet1!A1", ptg.toFormulaString(book));

    // Now check we get the right formula back for
    //  a few sample ones
    FormulaRecord fr;

    // Sheet 1 A2 is on same sheet
    fr = fRecs[0];
    assertEquals(1, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals(
        "Sheet1!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));

    // Sheet 1 A5 is to another sheet
    fr = fRecs[3];
    assertEquals(4, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals("'S2'!A1", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));

    // Sheet 1 A7 is to another sheet, range
    fr = fRecs[5];
    assertEquals(6, fr.getRow());
    assertEquals(0, fr.getColumn());
    assertEquals(
        "SUM(Sh3!A1:A4)", HSSFFormulaParser.toFormulaString(stubHSSF, fr.getParsedExpression()));

    // Now, load via Usermodel and re-check
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("3dFormulas.xls");
    assertEquals("Sheet1!A1", wb.getSheetAt(0).getRow(1).getCell(0).getCellFormula());
    assertEquals("SUM(Sh3!A1:A4)", wb.getSheetAt(0).getRow(6).getCell(0).getCellFormula());
  }