Esempio n. 1
0
 private boolean isMerged(Cell cell) {
   if (cell == null) return false;
   for (CellRangeAddress range : mergedRegions) {
     if (range.isInRange(cell.getRowIndex(), cell.getColumnIndex())) return true;
   }
   return false;
 }
  /** 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());
      }
    }
  }
 /**
  * @param srcSheet the sheet to copy.
  * @param destSheet the sheet to create.
  * @param srcRow the row to copy.
  * @param destRow the row to create.
  * @param styleMap -
  */
 public static void copyRow(
     XSSFSheet srcSheet,
     XSSFSheet destSheet,
     XSSFRow srcRow,
     XSSFRow destRow,
     Map<Integer, XSSFCellStyle> styleMap) {
   Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
   destRow.setHeight(srcRow.getHeight());
   for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
     XSSFCell oldCell = srcRow.getCell(j);
     XSSFCell newCell = destRow.getCell(j);
     if (oldCell != null) {
       if (newCell == null) {
         newCell = destRow.createCell(j);
       }
       copyCell(oldCell, newCell, styleMap);
       CellRangeAddress mergedRegion =
           getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());
       if (mergedRegion != null) {
         CellRangeAddress newMergedRegion =
             new CellRangeAddress(
                 mergedRegion.getFirstRow(),
                 mergedRegion.getFirstColumn(),
                 mergedRegion.getLastRow(),
                 mergedRegion.getLastColumn());
         CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
         if (isNewMergedRegion(wrapper, mergedRegions)) {
           mergedRegions.add(wrapper);
           destSheet.addMergedRegion(wrapper.range);
         }
       }
     }
   }
 }
  /** 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 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;
  }
 private CellRangeAddress[] computeNewFormattingRanges(
     CellRangeAddress[] ranges, int startRow, int lastRow) {
   for (CellRangeAddress range : ranges) {
     range.setFirstRow(startRow);
     range.setLastRow(lastRow);
   }
   return ranges;
 }
Esempio n. 7
0
  private void processCell(Element tr, XSSFCell cell) {

    int num = cell.getSheet().getNumMergedRegions();

    //		System.out.println(cell.getCTCell());
    for (int i = 0; i < num; i++) {

      CellRangeAddress c = cell.getSheet().getMergedRegion(i);

      System.out.println(c.getFirstColumn());
      ;
      System.out.println(c.getLastColumn());
      System.out.println(c.getFirstRow());
      System.out.println(c.getLastRow());
      System.out.println();
      System.out.println(cell.getRowIndex());
      System.out.println(cell.getColumnIndex());

      System.out.println("\n\n\n");

      //		System.out.println(cra);

    }

    //		System.exit(0);

    Element td = htmlDocumentFacade.createTableCell();
    Object value;
    switch (cell.getCellType()) {
      case Cell.CELL_TYPE_BLANK:
        value = "\u00a0";
        break;
      case Cell.CELL_TYPE_NUMERIC:
        value = cell.getNumericCellValue();
        break;
      case Cell.CELL_TYPE_BOOLEAN:
        value = cell.getBooleanCellValue();
        break;
      case Cell.CELL_TYPE_FORMULA:
        value = cell.getNumericCellValue();
        break;
      default:
        value = cell.getRichStringCellValue();
        break;
    }
    if (value instanceof XSSFRichTextString) {
      processCellStyle(td, cell.getCellStyle(), (XSSFRichTextString) value);
      td.setTextContent(value.toString());
    } else {
      processCellStyle(td, cell.getCellStyle(), null);
      td.setTextContent(value.toString());
    }
    //		System.err.println(value);
    tr.appendChild(td);
  }
Esempio n. 8
0
  public List<String> getCellConstraints(Cell cell) {
    for (DataValidation _validation : getValidations())
      for (CellRangeAddress _region : _validation.getRegions().getCellRangeAddresses())
        if (_region.isInRange(cell.getRowIndex(), cell.getColumnIndex())) {
          if (_validation.getValidationConstraint().getExplicitListValues() != null)
            return Arrays.asList(_validation.getValidationConstraint().getExplicitListValues());
          else if (_validation.getValidationConstraint().getFormula1() != null) {
            String formula = _validation.getValidationConstraint().getFormula1().split("\"")[1];
            String[] _names = formula.split("!");
            String _sheetName = _names[0];
            String _arrName = _names[1];
            int sheetIndex = evalWorkbook.getSheetIndex(_sheetName);

            EvaluationName nm = evalWorkbook.getName(_arrName, sheetIndex);
            if (nm == null || !nm.isRange()) {
              throw new RuntimeException(
                  "Specified name '" + _arrName + "' is not a range as expected.");
            }

            OperationEvaluationContext ec =
                new OperationEvaluationContext(
                    new WorkbookEvaluator(evalWorkbook, null, null),
                    evalWorkbook,
                    defaultSheet,
                    cell.getRowIndex(),
                    cell.getColumnIndex(),
                    null);

            Ptg[] ptgs = nm.getNameDefinition();
            if (ptgs.length == 1 && ptgs[0] instanceof Area3DPtg) {
              ValueEval result = ec.getArea3DEval((Area3DPtg) ptgs[0]);

              if (result instanceof AreaEvalBase) {
                AreaEvalBase _area = (AreaEvalBase) result;
                ArrayList<String> resultStrings = new ArrayList<>();
                for (int i = _area.getFirstRow(); i <= _area.getLastRow(); i++) {
                  String value =
                      getStringValue(
                          new CellCoord(_area.getFirstSheetIndex(), _area.getFirstColumn(), i));
                  if (value != null && value.length() > 0) resultStrings.add(value);
                }

                return resultStrings;
              }

              return null;
            }
          }
        }

    return null;
  }
Esempio n. 9
0
 /**
  * @param
  * @return void
  */
 private void fillMergedRegion(HSSFSheet sheet, CellRangeAddress address, HSSFCellStyle style) {
   for (int i = address.getFirstRow(); i <= address.getLastRow(); i++) {
     HSSFRow row = sheet.getRow(i);
     if (row == null) row = sheet.createRow(i);
     for (int j = address.getFirstColumn(); j <= address.getLastColumn(); j++) {
       HSSFCell cell = row.getCell(j);
       if (cell == null) {
         cell = row.createCell(j);
         if (style != null) cell.setCellStyle(style);
       }
     }
   }
 }
Esempio n. 10
0
  /**
   * 取单元格所在的合并区域,如果返回空,则说明没有在合并区域
   *
   * @param cell -- 指定的单元格
   * @return
   */
  private static CellRangeAddress getMergedRegion(HSSFCell cell) {
    HSSFSheet sheet = cell.getSheet();

    CellRangeAddress range = null;
    int mergedNum = sheet.getNumMergedRegions();
    for (int i = 0; i < mergedNum; i++) {
      range = sheet.getMergedRegion(i);
      if (range.getFirstColumn() == cell.getColumnIndex()
          && range.getFirstRow() == cell.getRowIndex()) {
        return range;
      }
    }
    return null;
  }
Esempio n. 11
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());
  }
Esempio n. 12
0
  /**
   * 输出图片到指定的单元格,参考POI例子中的ReportImageUtil类。
   *
   * @param cell -- 单元格
   * @param bytes -- 图片内容
   */
  public static void addImageToSheet(HSSFCell cell, byte[] bytes) {
    if (cell == null) {
      _log.showError("-----insertImageToSheet: cell is null!");
      return;
    }
    if (bytes == null || bytes.length == 0) {
      _log.showError("-----insertImageToSheet: bytes is null!");
      return;
    }

    // 取所在表单对象
    HSSFSheet sheet = cell.getSheet();

    // 取图片输出行与列
    int firstRow = cell.getRowIndex();
    int lastRow = cell.getRowIndex();
    int firstCol = cell.getColumnIndex();
    int lastCol = cell.getColumnIndex();
    // 取单元格所在的区域
    CellRangeAddress range = getMergedRegion(cell);
    if (range != null) {
      firstRow = range.getFirstRow();
      lastRow = range.getLastRow();
      firstCol = range.getFirstColumn();
      lastCol = range.getLastColumn();
    }
    _log.showDebug(
        "---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]");
    // 图片输出要比单元格的高与宽偏5个值,保留单元的边框,宽度1023表示填充满,高度255表示填充满
    HSSFClientAnchor anchor =
        new HSSFClientAnchor(5, 5, 1023, 255, (short) firstCol, firstRow, (short) lastCol, lastRow);
    anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE);

    // 取图片管理器,如果没有则创建
    HSSFPatriarch draw = sheet.getDrawingPatriarch();
    if (draw == null) {
      draw = sheet.createDrawingPatriarch();
    }

    // 插入新图片,返回的新图片序号无效
    sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
    // 上面代码中新建图片的序号没有考虑原有图片数量,所以取原图片数量+1作为新图片的序号
    List<HSSFPicture> lsPicture = getAllPicture(sheet);
    int index = lsPicture.size() + 1;
    _log.showDebug("---------new image index=" + index);

    draw.createPicture(anchor, index);
  }
Esempio n. 13
0
 public static void copyBlock(
     Sheet sheet,
     int startRow,
     int startCol,
     int endRow,
     int endCol,
     boolean copyStyle,
     int rowOffset,
     int colOffset,
     List<CellRangeAddress> mergedRegions) {
   for (int row = startRow; row <= endRow; row++) {
     Row oldRow = sheet.getRow(row);
     if (oldRow == null) continue;
     Row newRow = sheet.getRow(row + rowOffset);
     if (newRow == null) newRow = sheet.createRow(row + rowOffset);
     if (oldRow.getHeight() >= 0) newRow.setHeight(oldRow.getHeight());
     if (logger.isDebugEnabled()) {
       logger.debug("copy row {} to {}", row, row + rowOffset);
       logger.debug("Set row height :{}", newRow.getHeightInPoints());
     }
     for (int col = startCol; col <= endCol; col++) {
       Cell oldCell = oldRow.getCell(col);
       if (oldCell == null) continue;
       Cell newCell = newRow.getCell(col + colOffset);
       if (newCell == null) newCell = newRow.createCell(col + colOffset);
       copyCell(oldCell, newCell, copyStyle, rowOffset, colOffset);
     }
   }
   for (int col = startCol; col <= endCol; col++) {
     if (sheet.getColumnWidth(col) >= 0)
       sheet.setColumnWidth(col + colOffset, sheet.getColumnWidth(col));
   }
   if (mergedRegions != null) {
     for (CellRangeAddress cra : mergedRegions) {
       CellRangeAddress craNew =
           new CellRangeAddress(
               cra.getFirstRow() + rowOffset,
               cra.getLastRow() + rowOffset,
               cra.getFirstColumn() + colOffset,
               cra.getLastColumn() + colOffset);
       sheet.addMergedRegion(craNew);
     }
   }
 }
Esempio n. 14
0
  //  traversal cell
  public void traversalCell(String filePath) {
    try {
      Workbook workBook = null;
      try {
        workBook = new XSSFWorkbook(filePath); // 支持2007
      } catch (Exception ex) {
        workBook = new HSSFWorkbook(new FileInputStream(filePath)); // 支持2003及以前
      }

      // 获得Excel中工作表个数
      System.out.println("工作表个数 :" + workBook.getNumberOfSheets());

      // 循环每个工作表
      for (int i = 0; i < workBook.getNumberOfSheets(); i++) {
        // 创建工作表
        Sheet sheet = workBook.getSheetAt(i);

        int rows = sheet.getPhysicalNumberOfRows(); // 获得行数

        System.out.println(
            "工作表" + sheet.getSheetName() + " 行数 :" + sheet.getPhysicalNumberOfRows());

        if (rows > 0) {
          sheet.getMargin(Sheet.TopMargin);
          for (int r = 0; r < rows; r++) { // 行循环
            Row row = sheet.getRow(r);
            if (row != null) {

              int cells = row.getLastCellNum(); // 获得列数
              for (short c = 0; c < cells; c++) { // 列循环
                Cell cell = row.getCell(c);

                if (cell != null) {
                  String value = getCellData(cell);
                  System.out.println("第" + r + "行 " + "第" + c + "列:" + value);
                }
              }
            }
          }
        }

        // 查询合并的单元格
        for (i = 0; i < sheet.getNumMergedRegions(); i++) {
          System.out.println("第" + i + "个合并单元格");
          CellRangeAddress region = sheet.getMergedRegion(i);
          int row = region.getLastRow() - region.getFirstRow() + 1;
          int col = region.getLastColumn() - region.getFirstColumn() + 1;
          System.out.println("起始行:" + region.getFirstRow());
          System.out.println("起始列:" + region.getFirstColumn());
          System.out.println("所占行:" + row);
          System.out.println("所占列:" + col);
        }
      }

    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
  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());
    }
  }
Esempio n. 16
0
  public void serialize(LittleEndianOutput out) {
    _range.serialize(out);

    _guid.serialize(out);
    out.writeInt(0x00000002); // TODO const
    out.writeInt(_linkOpts);

    if ((_linkOpts & HLINK_LABEL) != 0) {
      out.writeInt(_label.length());
      StringUtil.putUnicodeLE(_label, out);
    }
    if ((_linkOpts & HLINK_TARGET_FRAME) != 0) {
      out.writeInt(_targetFrame.length());
      StringUtil.putUnicodeLE(_targetFrame, out);
    }

    if ((_linkOpts & HLINK_URL) != 0 && (_linkOpts & HLINK_UNC_PATH) != 0) {
      out.writeInt(_address.length());
      StringUtil.putUnicodeLE(_address, out);
    }

    if ((_linkOpts & HLINK_URL) != 0 && (_linkOpts & HLINK_UNC_PATH) == 0) {
      _moniker.serialize(out);
      if (URL_MONIKER.equals(_moniker)) {
        if (_uninterpretedTail == null) {
          out.writeInt(_address.length() * 2);
          StringUtil.putUnicodeLE(_address, out);
        } else {
          out.writeInt(_address.length() * 2 + TAIL_SIZE);
          StringUtil.putUnicodeLE(_address, out);
          writeTail(_uninterpretedTail, out);
        }
      } else if (FILE_MONIKER.equals(_moniker)) {
        out.writeShort(_fileOpts);
        out.writeInt(_shortFilename.length());
        StringUtil.putCompressedUnicode(_shortFilename, out);
        writeTail(_uninterpretedTail, out);
        if (_address == null) {
          out.writeInt(0);
        } else {
          int addrLen = _address.length() * 2;
          out.writeInt(addrLen + 6);
          out.writeInt(addrLen);
          out.writeShort(0x0003); // TODO const
          StringUtil.putUnicodeLE(_address, out);
        }
      }
    }
    if ((_linkOpts & HLINK_PLACE) != 0) {
      out.writeInt(_textMark.length());
      StringUtil.putUnicodeLE(_textMark, out);
    }
  }
  /** 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_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());
  }
  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());
    }

    */
  }
  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());
    }
  }
Esempio n. 22
0
 public Object clone() {
   HyperlinkRecord rec = new HyperlinkRecord();
   rec._range = _range.copy();
   rec._guid = _guid;
   rec._linkOpts = _linkOpts;
   rec._fileOpts = _fileOpts;
   rec._label = _label;
   rec._address = _address;
   rec._moniker = _moniker;
   rec._shortFilename = _shortFilename;
   rec._targetFrame = _targetFrame;
   rec._textMark = _textMark;
   rec._uninterpretedTail = _uninterpretedTail;
   return rec;
 }
  public void testOneSeriePlot() throws Exception {
    Workbook wb = new XSSFWorkbook();
    Sheet sheet = new SheetBuilder(wb, plotData).build();
    Drawing drawing = sheet.createDrawingPatriarch();
    ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 10, 30);
    Chart chart = drawing.createChart(anchor);

    ChartAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
    ChartAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);

    ScatterChartData scatterChartData = chart.getChartDataFactory().createScatterChartData();

    ChartDataSource<String> xs =
        DataSources.fromStringCellRange(sheet, CellRangeAddress.valueOf("A1:J1"));
    ChartDataSource<Number> ys =
        DataSources.fromNumericCellRange(sheet, CellRangeAddress.valueOf("A2:J2"));
    ScatterChartSerie serie = scatterChartData.addSerie(xs, ys);

    assertNotNull(serie);
    assertEquals(1, scatterChartData.getSeries().size());
    assertTrue(scatterChartData.getSeries().contains(serie));

    chart.plot(scatterChartData, bottomAxis, leftAxis);
  }
  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());
    }
  }
Esempio n. 26
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();
  }
Esempio n. 27
0
  private HSSFSheet createWorkSheet(HSSFWorkbook workbook) {
    HSSFSheet sheet = workbook.createSheet("TIH-Demurage");

    SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();

    // Condition 1: Formula Is   =A2=A1   (White Font)
    ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");
    org.apache.poi.ss.usermodel.PatternFormatting fill1 = rule1.createPatternFormatting();
    fill1.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
    fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);

    CellRangeAddress[] regions = {CellRangeAddress.valueOf("A1:C5")};

    sheetCF.addConditionalFormatting(regions, rule1);

    sheet.createRow(0).createCell(1).setCellValue("Shade Alternating Rows");
    sheet
        .createRow(1)
        .createCell(1)
        .setCellValue("Condition: Formula Is  =MOD(ROW(),2)   (Light Green Fill)");
    return sheet;
  }
  @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();
  }
Esempio n. 29
0
  public static void main(String[] args) throws Exception {
    Workbook wb;

    if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook();
    else wb = new XSSFWorkbook();

    Map<String, CellStyle> styles = createStyles(wb);

    Sheet sheet = wb.createSheet("Timesheet");
    PrintSetup printSetup = sheet.getPrintSetup();
    printSetup.setLandscape(true);
    sheet.setFitToPage(true);
    sheet.setHorizontallyCenter(true);

    // title row
    Row titleRow = sheet.createRow(0);
    titleRow.setHeightInPoints(45);
    Cell titleCell = titleRow.createCell(0);
    titleCell.setCellValue("Weekly Timesheet");
    titleCell.setCellStyle(styles.get("title"));
    sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));

    // header row
    Row headerRow = sheet.createRow(1);
    headerRow.setHeightInPoints(40);
    Cell headerCell;
    for (int i = 0; i < titles.length; i++) {
      headerCell = headerRow.createCell(i);
      headerCell.setCellValue(titles[i]);
      headerCell.setCellStyle(styles.get("header"));
    }

    int rownum = 2;
    for (int i = 0; i < 10; i++) {
      Row row = sheet.createRow(rownum++);
      for (int j = 0; j < titles.length; j++) {
        Cell cell = row.createCell(j);
        if (j == 9) {
          // the 10th cell contains sum over week days, e.g. SUM(C3:I3)
          String ref = "C" + rownum + ":I" + rownum;
          cell.setCellFormula("SUM(" + ref + ")");
          cell.setCellStyle(styles.get("formula"));
        } else if (j == 11) {
          cell.setCellFormula("J" + rownum + "-K" + rownum);
          cell.setCellStyle(styles.get("formula"));
        } else {
          cell.setCellStyle(styles.get("cell"));
        }
      }
    }

    // row with totals below
    Row sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(35);
    Cell cell;
    cell = sumRow.createCell(0);
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellValue("Total Hrs:");
    cell.setCellStyle(styles.get("formula"));

    for (int j = 2; j < 12; j++) {
      cell = sumRow.createCell(j);
      String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12";
      cell.setCellFormula("SUM(" + ref + ")");
      if (j >= 9) cell.setCellStyle(styles.get("formula_2"));
      else cell.setCellStyle(styles.get("formula"));
    }
    rownum++;
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Regular Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("L13");
    cell.setCellStyle(styles.get("formula_2"));
    sumRow = sheet.createRow(rownum++);
    sumRow.setHeightInPoints(25);
    cell = sumRow.createCell(0);
    cell.setCellValue("Total Overtime Hours");
    cell.setCellStyle(styles.get("formula"));
    cell = sumRow.createCell(1);
    cell.setCellFormula("K13");
    cell.setCellStyle(styles.get("formula_2"));

    // set sample data
    for (int i = 0; i < sample_data.length; i++) {
      Row row = sheet.getRow(2 + i);
      for (int j = 0; j < sample_data[i].length; j++) {
        if (sample_data[i][j] == null) continue;

        if (sample_data[i][j] instanceof String) {
          row.getCell(j).setCellValue((String) sample_data[i][j]);
        } else {
          row.getCell(j).setCellValue((Double) sample_data[i][j]);
        }
      }
    }

    // finally set column widths, the width is measured in units of 1/256th of a character width
    sheet.setColumnWidth(0, 30 * 256); // 30 characters wide
    for (int i = 2; i < 9; i++) {
      sheet.setColumnWidth(i, 6 * 256); // 6 characters wide
    }
    sheet.setColumnWidth(10, 10 * 256); // 10 characters wide

    // Write the output to a file
    String file = "timesheet.xls";
    if (wb instanceof XSSFWorkbook) file += "x";
    FileOutputStream out = new FileOutputStream(file);
    wb.write(out);
    out.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());
  }