Examples of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula

Package org.openxmlformats.schemas.spreadsheetml.x2006.main

Examples of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula

org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula


        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();
    }

View Full Code Here

                }
                _cell.setT(STCellType.S);
                break;
            case CELL_TYPE_FORMULA:
                if(!_cell.isSetF()){
                    CTCellFormula f =  CTCellFormula.Factory.newInstance();
                    f.setStringValue("0");
                    _cell.setF(f);
                    if(_cell.isSetT()) _cell.unsetT();
                }
                break;
            default:

View Full Code Here

     *
     * @param cell the cell to update
     * @param frwb the formula rendering workbbok that returns new sheet name
     */
    private void updateFormula(XSSFCell cell, FormulaRenderingWorkbook frwb) {
        CTCellFormula f = cell.getCTCell().getF();
        if (f != null) {
            String formula = f.getStringValue();
            if (formula != null) {
                int sheetIndex = _wb.getSheetIndex(cell.getSheet());
                Ptg[] ptgs = FormulaParser.parse(formula, _fpwb, FormulaType.CELL, sheetIndex);
                String updatedFormula = FormulaRenderer.toFormulaString(frwb, ptgs);
                if (!formula.equals(updatedFormula)) f.setStringValue(updatedFormula);
            }
        }
    }

View Full Code Here

        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.isSetRef()) { //Range of cells which the formula applies to.
                    String ref = f.getRef();
                    String shiftedRef = shiftFormula(row, ref, shifter);
                    if (shiftedRef != null) f.setRef(shiftedRef);
                }
            }


        }
    }

View Full Code Here

        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());
        }
    }

View Full Code Here

        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 = row.getSheet().getSharedFormula(si);
                            sf.setStringValue(shiftedFormula);
                        }
                    }


                }

View Full Code Here

    }


    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 precedes 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) {

View Full Code Here

    @Override
    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();
    }

View Full Code Here

     * @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());

View Full Code Here

        setFormula(formula, FormulaType.CELL);
    }


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

View Full Code Here

0 1 2 3 4 5 6 7 8 9

TOP

Related Classes of org.openxmlformats.schemas.spreadsheetml.x2006.main.CTCellFormula

com.narirelays.ems.utils.ExcelUtility

org.apache.poi.hssf.extractor.HXFExcelExtractor

org.apache.poi.hssf.HSSFXML

org.apache.poi.hssf.model.SharedStringsTable

org.apache.poi.xssf.eventusermodel.TestReadOnlySharedStringsTable

org.apache.poi.xssf.eventusermodel.XSSFReader$SheetIterator

org.apache.poi.xssf.model.CommentsTable

org.apache.poi.xssf.model.Control

org.apache.poi.xssf.model.Drawing

org.apache.poi.xssf.model.ExternalLinksTable

All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.