わいえむねっと

    /**
     * Sets formula for this cell.
     * <p>
     * Note, this method only sets the formula string and does not calculate the formula value.
     * To set the precalculated value use {@link #setCellValue(double)} or {@link #setCellValue(String)}
     * </p>
     *
     * @param formula the formula to set, e.g. <code>"SUM(C4:E4)"</code>.
     *  If the argument is <code>null</code> then the current formula is removed.
     * @throws org.apache.poi_v3_9.ss.formula.FormulaParseException if the formula has incorrect syntax or is otherwise invalid
     * @throws IllegalStateException if the operation is not allowed, for example,
     *  when the cell is a part of a multi-cell array formula
     */
    public void setCellFormula(String formula) {
        if(isPartOfArrayFormulaGroup()){
            notifyArrayFormulaChanging();
        }
        setFormula(formula, FormulaType.CELL);
    }

    private void setFormula(String formula, int formulaType) {
        XSSFWorkbook wb = _row.getSheet().getWorkbook();
        if (formula == null) {
            wb.onDeleteFormula(this);
            if(_cell.isSetF()) _cell.unsetF();
            return;
        }

        XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.create(wb);
        //validate through the FormulaParser
        FormulaParser.parse(formula, fpb, formulaType, wb.getSheetIndex(getSheet()));

        CTCellFormula f = CTCellFormula.Factory.newInstance();
        f.setStringValue(formula);
        _cell.setF(f);
        if(_cell.isSetV()) _cell.unsetV();
    }

    /**
     * cache of master shared formulas in this sheet.
     * Master shared formula is the first formula in a group of shared formulas is saved in the f element.
     */
    private Map<Integer, CTCellFormula> sharedFormulas;
    private TreeMap<String,XSSFTable> tables;
    private List<CellRangeAddress> arrayFormulas;
    private XSSFDataValidationHelper dataValidationHelper;    

    void onReadCell(XSSFCell cell){
        //collect cells holding shared formulas
        CTCell ct = cell.getCTCell();
        CTCellFormula f = ct.getF();
        if (f != null && f.getT() == STCellFormulaType.SHARED && f.isSetRef() && f.getStringValue() != null) {
            // save a detached  copy to avoid XmlValueDisconnectedException,
            // this may happen when the master cell of a shared formula is changed
            CTCellFormula sf = (CTCellFormula)f.copy();
            CellRangeAddress sfRef = CellRangeAddress.valueOf(sf.getRef());
            CellReference cellRef = new CellReference(cell);
            // If the shared formula range preceeds the master cell then the preceding  part is discarded, e.g.
            // if the cell is E60 and the shared formula range is C60:M85 then the effective range is E60:M85
            // see more details in https://issues.apache.org/bugzilla/show_bug.cgi?id=51710
            if(cellRef.getCol() > sfRef.getFirstColumn() || cellRef.getRow() > sfRef.getFirstRow()){
                String effectiveRef = new CellRangeAddress(
                        Math.max(cellRef.getRow(), sfRef.getFirstRow()), sfRef.getLastRow(),
                        Math.max(cellRef.getCol(), sfRef.getFirstColumn()), sfRef.getLastColumn()).formatAsString();
                sf.setRef(effectiveRef);
            }

            sharedFormulas.put((int)f.getSi(), sf);
        }
        if (f != null && f.getT() == STCellFormulaType.ARRAY && f.getRef() != null) {
            arrayFormulas.add(CellRangeAddress.valueOf(f.getRef()));
        }
    }

    /**
     * Return a formula for the cell, for example, <code>SUM(C4:E4)</code>
     *
     * @return a formula for the cell
     * @throws IllegalStateException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA
     */
    public String getCellFormula() {
        int cellType = getCellType();
        if(cellType != CELL_TYPE_FORMULA) throw typeMismatch(CELL_TYPE_FORMULA, cellType, false);

        CTCellFormula f = _cell.getF();
        if (isPartOfArrayFormulaGroup() && f == null) {
            XSSFCell cell = getSheet().getFirstCellInArrayFormula(this);
            return cell.getCellFormula();
        }
        if (f.getT() == STCellFormulaType.SHARED) {
            return convertSharedFormula((int)f.getSi());
        }
        return f.getStringValue();
    }

    public CellRange<XSSFCell> setArrayFormula(String formula, CellRangeAddress range) {

        CellRange<XSSFCell> cr = getCellRange(range);

        XSSFCell mainArrayFormulaCell = cr.getTopLeftCell();
        mainArrayFormulaCell.setCellArrayFormula(formula, range);
        arrayFormulas.add(range);
        return cr;
    }