Examples with CTCellFormula org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula used on opensource projects

Example 1 with CTCellFormula

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula in project poi by apache.

the class XSSFCell method convertSharedFormula.

/**
     * 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, XSSFEvaluationWorkbook fpb) {
    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);
    SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
    Ptg[] ptgs = FormulaParser.parse(sharedFormula, fpb, FormulaType.CELL, sheetIndex, getRowIndex());
    Ptg[] fmla = sf.convertSharedFormulas(ptgs, getRowIndex() - ref.getFirstRow(), getColumnIndex() - ref.getFirstColumn());
    return FormulaRenderer.toFormulaString(fpb, fmla);
}

Example 2 with CTCellFormula

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula in project poi by apache.

the class XSSFCell method setCellType.

/**
     * Set the cells type (numeric, formula or string)
     *
     * @throws IllegalArgumentException if the specified cell type is invalid
     */
@Override
public void setCellType(CellType cellType) {
    CellType prevType = getCellTypeEnum();
    if (isPartOfArrayFormulaGroup()) {
        notifyArrayFormulaChanging();
    }
    if (prevType == CellType.FORMULA && cellType != CellType.FORMULA) {
        getSheet().getWorkbook().onDeleteFormula(this);
    }
    switch(cellType) {
        case NUMERIC:
            _cell.setT(STCellType.N);
            break;
        case STRING:
            if (prevType != CellType.STRING) {
                String str = convertCellValueToString();
                XSSFRichTextString rt = new XSSFRichTextString(str);
                rt.setStylesTableReference(_stylesSource);
                int sRef = _sharedStringSource.addEntry(rt.getCTRst());
                _cell.setV(Integer.toString(sRef));
            }
            _cell.setT(STCellType.S);
            break;
        case FORMULA:
            if (!_cell.isSetF()) {
                CTCellFormula f = CTCellFormula.Factory.newInstance();
                f.setStringValue("0");
                _cell.setF(f);
                if (_cell.isSetT()) {
                    _cell.unsetT();
                }
            }
            break;
        case BLANK:
            setBlank();
            break;
        case BOOLEAN:
            String newVal = convertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING;
            _cell.setT(STCellType.B);
            _cell.setV(newVal);
            break;
        case ERROR:
            _cell.setT(STCellType.E);
            break;
        default:
            throw new IllegalArgumentException("Illegal cell type: " + cellType);
    }
    if (cellType != CellType.FORMULA && _cell.isSetF()) {
        _cell.unsetF();
    }
}

Example 3 with CTCellFormula

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula in project poi by apache.

the class XSSFCell method setCellArrayFormula.

/* package */
void setCellArrayFormula(String formula, CellRangeAddress range) {
    setFormula(formula, FormulaType.ARRAY);
    CTCellFormula cellFormula = _cell.getF();
    cellFormula.setT(STCellFormulaType.ARRAY);
    cellFormula.setRef(range.formatAsString());
}

Example 4 with CTCellFormula

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula in project poi by apache.

the class XSSFRowShifter method updateRowFormulas.

/**
     * Update the formulas in specified row using the formula shifting policy specified by shifter
     *
     * @param row the row to update the formulas on
     * @param shifter the formula shifting policy
     */
@Internal
public void updateRowFormulas(Row row, FormulaShifter shifter) {
    XSSFSheet sheet = (XSSFSheet) row.getSheet();
    for (Cell c : row) {
        XSSFCell cell = (XSSFCell) c;
        CTCell ctCell = cell.getCTCell();
        if (ctCell.isSetF()) {
            CTCellFormula f = ctCell.getF();
            String formula = f.getStringValue();
            if (formula.length() > 0) {
                String shiftedFormula = shiftFormula(row, formula, shifter);
                if (shiftedFormula != null) {
                    f.setStringValue(shiftedFormula);
                    if (f.getT() == STCellFormulaType.SHARED) {
                        int si = (int) f.getSi();
                        CTCellFormula sf = sheet.getSharedFormula(si);
                        sf.setStringValue(shiftedFormula);
                        updateRefInCTCellFormula(row, shifter, sf);
                    }
                }
            }
            //Range of cells which the formula applies to.
            updateRefInCTCellFormula(row, shifter, f);
        }
    }
}

Example 5 with CTCellFormula

use of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula in project poi by apache.

the class TestXSSFSheetUpdateArrayFormulas method confirmArrayFormulaCell.

private static void confirmArrayFormulaCell(XSSFCell c, String cellRef, String formulaText, String arrayRangeRef) {
    if (c == null) {
        throw new AssertionFailedError("Cell should not be null.");
    }
    CTCell ctCell = c.getCTCell();
    assertEquals(cellRef, ctCell.getR());
    if (formulaText == null) {
        assertFalse(ctCell.isSetF());
        assertNull(ctCell.getF());
    } else {
        CTCellFormula f = ctCell.getF();
        assertEquals(arrayRangeRef, f.getRef());
        assertEquals(formulaText, f.getStringValue());
        assertEquals(STCellFormulaType.ARRAY, f.getT());
    }
}