Ejemplo n.º 1
0
 /** Tests when rows are null. */
 public final void testShiftRow() {
   Workbook b = _testDataProvider.createWorkbook();
   Sheet s = b.createSheet();
   s.createRow(0).createCell(0).setCellValue("TEST1");
   s.createRow(3).createCell(0).setCellValue("TEST2");
   s.shiftRows(0, 4, 1);
 }
  @Test
  public void rotatedText() throws Exception {
    Workbook workbook = _testDataProvider.createWorkbook();
    fixFonts(workbook);
    Sheet sheet = workbook.createSheet();
    Row row = sheet.createRow(0);

    CellStyle style1 = workbook.createCellStyle();
    style1.setRotation((short) 90);

    Cell cell0 = row.createCell(0);
    cell0.setCellValue("Apache Software Foundation");
    cell0.setCellStyle(style1);

    Cell cell1 = row.createCell(1);
    cell1.setCellValue("Apache Software Foundation");

    for (int i = 0; i < 2; i++) sheet.autoSizeColumn(i);

    int w0 = sheet.getColumnWidth(0);
    int w1 = sheet.getColumnWidth(1);

    assertTrue(
        w0 * 5 < w1); // rotated text occupies at least five times less horizontal space than normal
    // text

    workbook.close();
  }
  /** 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_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());
  }
  /** 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());
    }
  }
Ejemplo n.º 6
0
  /** When shifting rows, the page breaks should go with it */
  public void testShiftRowBreaks() { // TODO - enable XSSF test
    Workbook b = _testDataProvider.createWorkbook();
    Sheet s = b.createSheet();
    Row row = s.createRow(4);
    row.createCell(0).setCellValue("test");
    s.setRowBreak(4);

    s.shiftRows(4, 4, 2);
    assertTrue("Row number 6 should have a pagebreak", s.isRowBroken(6));
  }
  @Test
  public void numericCells() throws Exception {
    Workbook workbook = _testDataProvider.createWorkbook();
    fixFonts(workbook);
    DataFormat df = workbook.getCreationHelper().createDataFormat();
    Sheet sheet = workbook.createSheet();

    Row row = sheet.createRow(0);
    row.createCell(0)
        .setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
    row.createCell(1).setCellValue(10);
    row.createCell(2).setCellValue("10");
    row.createCell(3).setCellFormula("(A1+B1)*1.0"); // a formula that returns '10'

    Cell cell4 = row.createCell(4); // numeric cell with a custom style
    CellStyle style4 = workbook.createCellStyle();
    style4.setDataFormat(df.getFormat("0.0000"));
    cell4.setCellStyle(style4);
    cell4.setCellValue(10); // formatted as '10.0000'

    row.createCell(5).setCellValue("10.0000");

    // autosize not-evaluated cells, formula cells are sized as if the result is 0
    for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);

    assertTrue(
        sheet.getColumnWidth(0)
            < sheet.getColumnWidth(1)); // width of '0' is less then width of '10'
    assertEquals(
        sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // 10 and '10' should be sized equally
    assertEquals(
        sheet.getColumnWidth(3),
        sheet.getColumnWidth(0)); // formula result is unknown, the width is calculated  for '0'
    assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'

    // evaluate formulas and re-autosize
    evaluateWorkbook(workbook);

    for (int i = 0; i < 6; i++) sheet.autoSizeColumn(i);

    assertTrue(
        sheet.getColumnWidth(0)
            < sheet.getColumnWidth(1)); // width of '0' is less then width of '10'
    assertEquals(
        sheet.getColumnWidth(1),
        sheet.getColumnWidth(2)); // columns 1, 2 and 3 should have the same width
    assertEquals(
        sheet.getColumnWidth(2),
        sheet.getColumnWidth(3)); // columns 1, 2 and 3 should have the same width
    assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(5)); // 10.0000 and '10.0000'

    workbook.close();
  }
  /** Passing an incorrect formula to sheet.setArrayFormula should throw FormulaParseException */
  public final void testSetArrayFormula_incorrectFormula() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet();

    try {
      sheet.setArrayFormula(
          "incorrect-formula(C11_C12*D11_D12)", new CellRangeAddress(10, 10, 10, 10));
      fail("expected exception");
    } catch (FormulaParseException e) {
      // expected exception
    }
  }
Ejemplo n.º 9
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 final void testAutoCreateOtherCells() {
    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet = workbook.createSheet("Sheet1");

    Row row1 = sheet.createRow(0);
    Cell cellA1 = row1.createCell(0);
    Cell cellB1 = row1.createCell(1);
    String formula = "42";
    sheet.setArrayFormula(formula, CellRangeAddress.valueOf("A1:B2"));

    assertEquals(formula, cellA1.getCellFormula());
    assertEquals(formula, cellB1.getCellFormula());
    Row row2 = sheet.getRow(1);
    assertNotNull(row2);
    assertEquals(formula, row2.getCell(0).getCellFormula());
    assertEquals(formula, row2.getCell(1).getCellFormula());
  }
  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());
    }
  }
  /**
   * Calls of cell.getArrayFormulaRange and sheet.removeArrayFormula on a not-array-formula cell
   * throw IllegalStateException
   */
  public final void testArrayFormulas_illegalCalls() {
    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());
    }

    try {
      sheet.removeArrayFormula(cell);
      fail("expected exception");
    } catch (IllegalArgumentException e) {
      assertEquals("Cell A1 is not part of an array formula.", e.getMessage());
    }
  }
Ejemplo n.º 14
0
  public final void testShiftWithNames() {
    Workbook wb = _testDataProvider.createWorkbook();
    Sheet sheet1 = wb.createSheet("Sheet1");
    wb.createSheet("Sheet2");
    Row row = sheet1.createRow(0);
    row.createCell(0).setCellValue(1.1);
    row.createCell(1).setCellValue(2.2);

    Name name1 = wb.createName();
    name1.setNameName("name1");
    name1.setRefersToFormula("Sheet1!$A$1+Sheet1!$B$1");

    Name name2 = wb.createName();
    name2.setNameName("name2");
    name2.setRefersToFormula("Sheet1!$A$1");

    // refers to A1 but on Sheet2. Should stay unaffected.
    Name name3 = wb.createName();
    name3.setNameName("name3");
    name3.setRefersToFormula("Sheet2!$A$1");

    // The scope of this one is Sheet2. Should stay unaffected.
    Name name4 = wb.createName();
    name4.setNameName("name4");
    name4.setRefersToFormula("A1");
    name4.setSheetIndex(1);

    sheet1.shiftRows(0, 1, 2); // shift down the top row on Sheet1.
    name1 = wb.getNameAt(0);
    assertEquals("Sheet1!$A$3+Sheet1!$B$3", name1.getRefersToFormula());

    name2 = wb.getNameAt(1);
    assertEquals("Sheet1!$A$3", name2.getRefersToFormula());

    // name3 and name4 refer to Sheet2 and should not be affected
    name3 = wb.getNameAt(2);
    assertEquals("Sheet2!$A$1", name3.getRefersToFormula());

    name4 = wb.getNameAt(3);
    assertEquals("A1", name4.getRefersToFormula());
  }
  @Test
  public void stringCells() throws Exception {
    Workbook workbook = _testDataProvider.createWorkbook();
    fixFonts(workbook);
    Sheet sheet = workbook.createSheet();
    Row row = sheet.createRow(0);

    Font defaultFont = workbook.getFontAt((short) 0);

    CellStyle style1 = workbook.createCellStyle();
    Font font1 = workbook.createFont();
    font1.setFontHeight((short) (2 * defaultFont.getFontHeight()));
    style1.setFont(font1);

    row.createCell(0).setCellValue("x");
    row.createCell(1).setCellValue("xxxx");
    row.createCell(2).setCellValue("xxxxxxxxxxxx");
    row.createCell(3)
        .setCellValue("Apache\nSoftware Foundation"); // the text is splitted into two lines
    row.createCell(4).setCellValue("Software Foundation");

    Cell cell5 = row.createCell(5);
    cell5.setCellValue("Software Foundation");
    cell5.setCellStyle(
        style1); // same as in column 4 but the font is twice larger than the default font

    for (int i = 0; i < 10; i++) sheet.autoSizeColumn(i);

    assertTrue(
        2 * sheet.getColumnWidth(0)
            < sheet.getColumnWidth(1)); // width is roughly proportional to the number of characters
    assertTrue(2 * sheet.getColumnWidth(1) < sheet.getColumnWidth(2));
    assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3));
    boolean ignoreFontSizeX2 = JvmBugs.hasLineBreakMeasurerBug();
    assertTrue(
        ignoreFontSizeX2
            || sheet.getColumnWidth(5)
                > sheet.getColumnWidth(4)); // larger font results in a wider column width

    workbook.close();
  }
  /** Test that when reading a workbook from input stream, array formulas are recognized */
  public final void testReadArrayFormula() {
    Cell[] cells;

    Workbook workbook = _testDataProvider.createWorkbook();
    Sheet sheet1 = workbook.createSheet();
    cells =
        sheet1
            .setArrayFormula("SUM(A1:A3*B1:B3)", CellRangeAddress.valueOf("C4:C6"))
            .getFlattenedCells();
    assertEquals(3, cells.length);

    cells =
        sheet1
            .setArrayFormula("MAX(A1:A3*B1:B3)", CellRangeAddress.valueOf("A4:A6"))
            .getFlattenedCells();
    assertEquals(3, cells.length);

    Sheet sheet2 = workbook.createSheet();
    cells =
        sheet2
            .setArrayFormula("MIN(A1:A3*B1:B3)", CellRangeAddress.valueOf("D2:D4"))
            .getFlattenedCells();
    assertEquals(3, cells.length);

    workbook = _testDataProvider.writeOutAndReadBack(workbook);
    sheet1 = workbook.getSheetAt(0);
    for (int rownum = 3; rownum <= 5; rownum++) {
      Cell cell1 = sheet1.getRow(rownum).getCell(2);
      assertTrue(cell1.isPartOfArrayFormulaGroup());

      Cell cell2 = sheet1.getRow(rownum).getCell(0);
      assertTrue(cell2.isPartOfArrayFormulaGroup());
    }

    sheet2 = workbook.getSheetAt(1);
    for (int rownum = 1; rownum <= 3; rownum++) {
      Cell cell1 = sheet2.getRow(rownum).getCell(3);
      assertTrue(cell1.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());
    }

    */
  }
  @Test
  public void mergedCells() throws Exception {
    Workbook workbook = _testDataProvider.createWorkbook();
    fixFonts(workbook);
    Sheet sheet = workbook.createSheet();

    Row row = sheet.createRow(0);
    sheet.addMergedRegion(CellRangeAddress.valueOf("A1:B1"));

    Cell cell0 = row.createCell(0);
    cell0.setCellValue("Apache Software Foundation");

    int defaulWidth = sheet.getColumnWidth(0);
    sheet.autoSizeColumn(0);
    // column is unchanged if merged regions are ignored (Excel like behavior)
    assertEquals(defaulWidth, sheet.getColumnWidth(0));

    sheet.autoSizeColumn(0, true);
    assertTrue(sheet.getColumnWidth(0) > defaulWidth);

    workbook.close();
  }
  /**
   * Auto-Sizing a column needs to work when we have rows passed the 32767 boundary. See bug #48079
   */
  @Test
  public void largeRowNumbers() throws Exception {
    Workbook workbook = _testDataProvider.createWorkbook();
    fixFonts(workbook);
    Sheet sheet = workbook.createSheet();

    Row r0 = sheet.createRow(0);
    r0.createCell(0).setCellValue("I am ROW 0");
    Row r200 = sheet.createRow(200);
    r200.createCell(0).setCellValue("I am ROW 200");

    // This should work fine
    sheet.autoSizeColumn(0);

    // Get close to 32767
    Row r32765 = sheet.createRow(32765);
    r32765.createCell(0).setCellValue("Nearly there...");
    sheet.autoSizeColumn(0);

    // To it
    Row r32767 = sheet.createRow(32767);
    r32767.createCell(0).setCellValue("At the boundary");
    sheet.autoSizeColumn(0);

    // And passed it
    Row r32768 = sheet.createRow(32768);
    r32768.createCell(0).setCellValue("Passed");
    Row r32769 = sheet.createRow(32769);
    r32769.createCell(0).setCellValue("More Passed");
    sheet.autoSizeColumn(0);

    // Long way passed
    Row r60708 = sheet.createRow(60708);
    r60708.createCell(0).setCellValue("Near the end");
    sheet.autoSizeColumn(0);

    workbook.close();
  }
  @Test
  public void booleanCells() throws Exception {
    Workbook workbook = _testDataProvider.createWorkbook();
    fixFonts(workbook);
    Sheet sheet = workbook.createSheet();

    Row row = sheet.createRow(0);
    row.createCell(0)
        .setCellValue(0); // getCachedFormulaResult() returns 0 for not evaluated formula cells
    row.createCell(1).setCellValue(true);
    row.createCell(2).setCellValue("TRUE");
    row.createCell(3).setCellFormula("1 > 0"); // a formula that returns true

    // autosize not-evaluated cells, formula cells are sized as if the result is 0
    for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);

    assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0)); // 'true' is wider than '0'
    assertEquals(
        sheet.getColumnWidth(1), sheet.getColumnWidth(2)); // 10 and '10' should be sized equally
    assertEquals(
        sheet.getColumnWidth(3),
        sheet.getColumnWidth(0)); // formula result is unknown, the width is calculated  for '0'

    // evaluate formulas and re-autosize
    evaluateWorkbook(workbook);

    for (int i = 0; i < 4; i++) sheet.autoSizeColumn(i);

    assertTrue(sheet.getColumnWidth(1) > sheet.getColumnWidth(0)); // 'true' is wider than '0'
    assertEquals(
        sheet.getColumnWidth(1),
        sheet.getColumnWidth(2)); // columns 1, 2 and 3 should have the same width
    assertEquals(
        sheet.getColumnWidth(2),
        sheet.getColumnWidth(3)); // columns 1, 2 and 3 should have the same width

    workbook.close();
  }
  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());
    }
  }
  @Test
  public void dateCells() throws Exception {
    Workbook workbook = _testDataProvider.createWorkbook();
    fixFonts(workbook);
    Sheet sheet = workbook.createSheet();
    DataFormat df = workbook.getCreationHelper().createDataFormat();

    CellStyle style1 = workbook.createCellStyle();
    style1.setDataFormat(df.getFormat("m"));

    CellStyle style3 = workbook.createCellStyle();
    style3.setDataFormat(df.getFormat("mmm"));

    CellStyle style5 = workbook.createCellStyle(); // rotated text
    style5.setDataFormat(df.getFormat("mmm/dd/yyyy"));

    Calendar calendar = LocaleUtil.getLocaleCalendar(2010, 0, 1); // Jan 1 2010

    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue(DateUtil.getJavaDate(0)); // default date

    Cell cell1 = row.createCell(1);
    cell1.setCellValue(calendar);
    cell1.setCellStyle(style1);
    row.createCell(2).setCellValue("1"); // column 1 should be sized as '1'

    Cell cell3 = row.createCell(3);
    cell3.setCellValue(calendar);
    cell3.setCellStyle(style3);
    row.createCell(4).setCellValue("Jan");

    Cell cell5 = row.createCell(5);
    cell5.setCellValue(calendar);
    cell5.setCellStyle(style5);
    row.createCell(6).setCellValue("Jan/01/2010");

    Cell cell7 = row.createCell(7);
    cell7.setCellFormula("DATE(2010,1,1)");
    cell7.setCellStyle(style3); // should be sized as 'Jan'

    // autosize not-evaluated cells, formula cells are sized as if the result is 0
    for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);

    assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
    assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1)); // 'mmm' is wider than 'm'
    assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
    assertTrue(
        sheet.getColumnWidth(5) > sheet.getColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
    assertEquals(
        sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'

    // YK: width of not-evaluated formulas that return data is not determined
    // POI seems to conevert '0' to Excel date which is the beginng of the Excel's date system

    // evaluate formulas and re-autosize
    evaluateWorkbook(workbook);

    for (int i = 0; i < 8; i++) sheet.autoSizeColumn(i);

    assertEquals(sheet.getColumnWidth(2), sheet.getColumnWidth(1)); // date formatted as 'm'
    assertTrue(sheet.getColumnWidth(3) > sheet.getColumnWidth(1)); // 'mmm' is wider than 'm'
    assertEquals(sheet.getColumnWidth(4), sheet.getColumnWidth(3)); // date formatted as 'mmm'
    assertTrue(
        sheet.getColumnWidth(5) > sheet.getColumnWidth(3)); // 'mmm/dd/yyyy' is wider than 'mmm'
    assertEquals(
        sheet.getColumnWidth(6), sheet.getColumnWidth(5)); // date formatted as 'mmm/dd/yyyy'
    assertEquals(
        sheet.getColumnWidth(4), sheet.getColumnWidth(7)); // date formula formatted as 'mmm'

    workbook.close();
  }
  /** 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());
  }