예제 #1
0
  /**
   * Creates a non shared formula from the shared formula counterpart
   *
   * @param si Shared Group Index
   * @return non shared formula created for the given shared formula and this cell
   */
  private String convertSharedFormula(int si) {
    XSSFSheet sheet = getSheet();

    CTCellFormula f = sheet.getSharedFormula(si);
    if (f == null)
      throw new IllegalStateException(
          "Master cell of a shared formula with sid=" + si + " was not found");

    String sharedFormula = f.getStringValue();
    // Range of cells which the shared formula applies to
    String sharedFormulaRange = f.getRef();

    CellRangeAddress ref = CellRangeAddress.valueOf(sharedFormulaRange);

    int sheetIndex = sheet.getWorkbook().getSheetIndex(sheet);
    XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(sheet.getWorkbook());
    SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);

    Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex);
    Ptg[] fmla =
        sf.convertSharedFormulas(
            ptgs, getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn());
    return FormulaRenderer.toFormulaString(fpb, fmla);
  }
예제 #2
0
파일: OoxmlUtil.java 프로젝트: zhangrui1/SK
 /**
  * セルに設定された計算式を評価して値を取得する
  *
  * @param nRow 行番号
  * @param nColumn 列番号
  * @return セルの値
  */
 public static Object getDataByEvaluateFormula(XSSFSheet sheet, int nRow, int nColumn) {
   assert sheet != null;
   XSSFRow row = getRowAnyway(sheet, nRow);
   if (row != null) {
     XSSFCell cell = row.getCell(nColumn);
     if (cell != null) {
       FormulaEvaluator eval = sheet.getWorkbook().getCreationHelper().createFormulaEvaluator();
       if (eval != null) {
         CellValue value = eval.evaluate(cell);
         if (value != null) {
           return value.getNumberValue();
         }
       }
     }
   }
   return null;
 }
예제 #3
0
파일: OoxmlUtil.java 프로젝트: zhangrui1/SK
 /**
  * ハイパーリンクの設定
  *
  * @param sheet シート
  * @param nRow 対象行番号
  * @param nColumn 対象列番号
  * @param value ハイパーリンクテキスト
  * @param url ハイパーリンク先URL
  */
 public static void setHyperLink(
     XSSFSheet sheet, int nRow, int nColumn, String value, String url) {
   assert sheet != null;
   XSSFWorkbook workbook = sheet.getWorkbook();
   CreationHelper helper = workbook.getCreationHelper();
   Hyperlink hyperlink = helper.createHyperlink(Hyperlink.LINK_URL);
   hyperlink.setAddress(url);
   XSSFRow row = getRowAnyway(sheet, nRow);
   XSSFCell cell = getCellAnyway(row, nColumn);
   cell.setCellValue(value);
   cell.setHyperlink(hyperlink);
   // ハイパーリンクテキストの装飾
   XSSFFont font = workbook.createFont();
   XSSFCellStyle style = workbook.createCellStyle();
   // font.setColor(new XSSFColor(new Color(0, 0, 255)));
   font.setUnderline(XSSFFont.U_SINGLE);
   style.setFont(font);
   cell.setCellStyle(style);
 }
예제 #4
0
  /**
   * update cell references when shifting rows
   *
   * @param n the number of rows to move
   */
  protected void shift(int n) {
    int rownum = getRowNum() + n;
    CalculationChain calcChain = _sheet.getWorkbook().getCalculationChain();
    int sheetId = (int) _sheet.sheet.getSheetId();
    String msg =
        "Row[rownum="
            + getRowNum()
            + "] contains cell(s) included in a multi-cell array formula. "
            + "You cannot change part of an array.";
    for (Cell c : this) {
      XSSFCell cell = (XSSFCell) c;
      if (cell.isPartOfArrayFormulaGroup()) {
        cell.notifyArrayFormulaChanging(msg);
      }

      // remove the reference in the calculation chain
      if (calcChain != null) calcChain.removeItem(sheetId, cell.getReference());

      CTCell ctCell = cell.getCTCell();
      String r = new CellReference(rownum, cell.getColumnIndex()).formatAsString();
      ctCell.setR(r);
    }
    setRowNum(rownum);
  }
예제 #5
0
 /**
  * Returns the cell at the given (0 based) index, with the {@link
  * org.apache.poi.ss.usermodel.Row.MissingCellPolicy} from the parent Workbook.
  *
  * @return the cell at the given (0 based) index
  */
 public XSSFCell getCell(int cellnum) {
   return getCell(cellnum, _sheet.getWorkbook().getMissingCellPolicy());
 }