Exemple #1
0
        private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
        {
            CT_CellFormula f = cell.GetCTCell().f;

            if (f == null)
            {
                return;
            }
            string formula = f.Value;

            if (formula == null || formula.Length <= 0)
            {
                return;
            }
            int sheetIndex = this._wb.GetSheetIndex(cell.Sheet);

            Ptg[]  ptgs          = FormulaParser.Parse(formula, (IFormulaParsingWorkbook)this._fpwb, FormulaType.CELL, sheetIndex);
            string formulaString = FormulaRenderer.ToFormulaString(frwb, ptgs);

            if (formula.Equals(formulaString))
            {
                return;
            }
            f.Value = formulaString;
        }
Exemple #2
0
        /// <summary>
        /// Creates a non shared formula from the shared formula counterpart
        /// </summary>
        /// <param name="si">Shared Group Index</param>
        /// <param name="fpb"></param>
        /// <returns>non shared formula created for the given shared formula and this cell</returns>
        private String ConvertSharedFormula(int si, XSSFEvaluationWorkbook fpb)
        {
            XSSFSheet sheet = (XSSFSheet)Sheet;

            CT_CellFormula f = sheet.GetSharedFormula(si);

            if (f == null)
            {
                throw new InvalidOperationException(
                          "Master cell of a shared formula with sid=" + si + " was not found");
            }

            String sharedFormula = f.Value;
            //Range of cells which the shared formula applies to
            String sharedFormulaRange = f.@ref;

            CellRangeAddress ref1 = CellRangeAddress.ValueOf(sharedFormulaRange);

            int           sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
            SharedFormula sf         = new SharedFormula(SpreadsheetVersion.EXCEL2007);

            Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex, RowIndex);
            Ptg[] fmla = sf.ConvertSharedFormulas(ptgs,
                                                  RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn);
            return(FormulaRenderer.ToFormulaString(fpb, fmla));
        }
Exemple #3
0
        private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
        {
            foreach (ICell c in row)
            {
                XSSFCell cell = (XSSFCell)c;

                CT_Cell ctCell = cell.GetCTCell();
                if (ctCell.IsSetF())
                {
                    CT_CellFormula f       = ctCell.f;
                    String         formula = f.Value;
                    if (formula.Length > 0)
                    {
                        String ShiftedFormula = ShiftFormula(row, formula, Shifter);
                        if (ShiftedFormula != null)
                        {
                            f.Value = (ShiftedFormula);
                        }
                    }

                    if (f.isSetRef())
                    { //Range of cells which the formula applies to.
                        String ref1       = f.@ref;
                        String ShiftedRef = ShiftFormula(row, ref1, Shifter);
                        if (ShiftedRef != null)
                        {
                            f.@ref = ShiftedRef;
                        }
                    }
                }
            }
        }
Exemple #4
0
        private void SetFormula(String formula, FormulaType formulaType)
        {
            IWorkbook wb = _row.Sheet.Workbook;

            if (formula == null)
            {
                ((XSSFWorkbook)wb).OnDeleteFormula(this);
                if (_cell.IsSetF())
                {
                    _cell.unsetF();
                }
                return;
            }

            IFormulaParsingWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);

            //validate through the FormulaParser
            FormulaParser.Parse(formula, fpb, formulaType, wb.GetSheetIndex(this.Sheet), -1);

            CT_CellFormula f = new CT_CellFormula();

            f.Value = formula;
            _cell.f = (f);
            if (_cell.IsSetV())
            {
                _cell.unsetV();
            }
        }
Exemple #5
0
 private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
 {
     foreach (XSSFCell xssfCell in row)
     {
         CT_Cell ctCell = xssfCell.GetCTCell();
         if (ctCell.IsSetF())
         {
             CT_CellFormula f        = ctCell.f;
             string         formula1 = f.Value;
             if (formula1.Length > 0)
             {
                 string str = XSSFRowShifter.ShiftFormula(row, formula1, Shifter);
                 if (str != null)
                 {
                     f.Value = str;
                 }
             }
             if (f.isSetRef())
             {
                 string formula2 = f.@ref;
                 string str      = XSSFRowShifter.ShiftFormula(row, formula2, Shifter);
                 if (str != null)
                 {
                     f.@ref = str;
                 }
             }
         }
     }
 }
Exemple #6
0
        private void SetFormula(String formula, FormulaType formulaType)
        {
            XSSFWorkbook wb = (XSSFWorkbook)_row.Sheet.Workbook;

            if (formula == null)
            {
                RemoveFormula();
                return;
            }

            if (wb.CellFormulaValidation)
            {
                IFormulaParsingWorkbook fpb = XSSFEvaluationWorkbook.Create(wb);
                //validate through the FormulaParser
                FormulaParser.Parse(formula, fpb, formulaType, wb.GetSheetIndex(this.Sheet), RowIndex);
            }
            CT_CellFormula f = new CT_CellFormula();

            f.Value = formula;
            _cell.f = (f);
            if (_cell.IsSetV())
            {
                _cell.unsetV();
            }
        }
Exemple #7
0
        internal void SetCellArrayFormula(string formula, CellRangeAddress range)
        {
            this.SetFormula(formula, FormulaType.ARRAY);
            CT_CellFormula f = this._cell.f;

            f.t    = ST_CellFormulaType.array;
            f.@ref = range.FormatAsString();
        }
Exemple #8
0
        internal void SetCellArrayFormula(String formula, CellRangeAddress range)
        {
            SetFormula(formula, FormulaType.Array);
            CT_CellFormula cellFormula = _cell.f;

            cellFormula.t    = (ST_CellFormulaType.array);
            cellFormula.@ref = (range.FormatAsString());
        }
Exemple #9
0
 //public CT_Cell()
 //{
 //    this.extLstField = new CT_ExtensionList();
 //    //this.isField = new CT_Rst();
 //    //this.fField = new CT_CellFormula();
 //    this.sField = (uint)(0);
 //    this.tField = ST_CellType.n;
 //    this.cmField = ((uint)(0));
 //    this.vmField = ((uint)(0));
 //    this.phField = false;
 //}
 public void Set(CT_Cell cell)
 {
     fField      = cell.fField;
     vField      = cell.vField;
     isField     = cell.isField;
     extLstField = cell.extLstField;
     rField      = cell.rField;
     sField      = cell.sField;
     tField      = cell.tField;
     cmField     = cell.cmField;
     vmField     = cell.vmField;
     phField     = cell.phField;
 }
Exemple #10
0
        private string ConvertSharedFormula(int si)
        {
            XSSFSheet      sheet         = (XSSFSheet)this.Sheet;
            CT_CellFormula sharedFormula = sheet.GetSharedFormula(si);

            if (sharedFormula == null)
            {
                throw new InvalidOperationException("Master cell of a shared formula with sid=" + (object)si + " was not found");
            }
            string           formula          = sharedFormula.Value;
            CellRangeAddress cellRangeAddress = CellRangeAddress.ValueOf(sharedFormula.@ref);
            int sheetIndex = sheet.Workbook.GetSheetIndex((ISheet)sheet);
            XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(sheet.Workbook);

            Ptg[] ptgs = new SharedFormula(SpreadsheetVersion.EXCEL2007).ConvertSharedFormulas(FormulaParser.Parse(formula, (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex), this.RowIndex - cellRangeAddress.FirstRow, this.ColumnIndex - cellRangeAddress.FirstColumn);
            return(FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs));
        }
Exemple #11
0
        /**
         * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
         *
         * @param cell the cell to update
         * @param frwb the formula rendering workbbok that returns new sheet name
         */
        private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb)
        {
            CT_CellFormula f = cell.GetCTCell().f;

            if (f != null)
            {
                String formula = f.Value;
                if (formula != null && formula.Length > 0)
                {
                    int    sheetIndex     = _wb.GetSheetIndex(cell.Sheet);
                    Ptg[]  ptgs           = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex);
                    String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs);
                    if (!formula.Equals(updatedFormula))
                    {
                        f.Value = (updatedFormula);
                    }
                }
            }
        }
Exemple #12
0
        /// <summary>
        /// Update the formulas in specified row using the formula shifting policy specified by shifter
        /// </summary>
        /// <param name="row">the row to update the formulas on</param>
        /// <param name="Shifter">the formula shifting policy</param>
        public override void UpdateRowFormulas(IRow row, FormulaShifter Shifter)
        {
            XSSFSheet sheet = (XSSFSheet)row.Sheet;

            foreach (ICell c in row)
            {
                XSSFCell cell = (XSSFCell)c;

                CT_Cell ctCell = cell.GetCTCell();
                if (ctCell.IsSetF())
                {
                    CT_CellFormula f       = ctCell.f;
                    String         formula = f.Value;
                    if (formula.Length > 0)
                    {
                        String ShiftedFormula = ShiftFormula(row, formula, Shifter);
                        if (ShiftedFormula != null)
                        {
                            f.Value = (ShiftedFormula);
                            if (f.t == ST_CellFormulaType.shared)
                            {
                                int            si = (int)f.si;
                                CT_CellFormula sf = sheet.GetSharedFormula(si);
                                sf.Value = (ShiftedFormula);
                            }
                        }
                    }

                    if (f.isSetRef())
                    { //Range of cells which the formula applies to.
                        String ref1       = f.@ref;
                        String ShiftedRef = ShiftFormula(row, ref1, Shifter);
                        if (ShiftedRef != null)
                        {
                            f.@ref = ShiftedRef;
                        }
                    }
                }
            }
        }
Exemple #13
0
        private static void ConfirmArrayFormulaCell(ICell c, String cellRef, String formulaText, String arrayRangeRef)
        {
            if (c == null)
            {
                throw new AssertionException("Cell should not be null.");
            }
            CT_Cell ctCell = ((XSSFCell)c).GetCTCell();

            Assert.AreEqual(cellRef, ctCell.r);
            if (formulaText == null)
            {
                Assert.IsFalse(ctCell.IsSetF());
                Assert.IsNull(ctCell.f);
            }
            else
            {
                CT_CellFormula f = ctCell.f;
                Assert.AreEqual(arrayRangeRef, f.@ref);
                Assert.AreEqual(formulaText, f.Value);
                Assert.AreEqual(ST_CellFormulaType.array, f.t);
            }
        }
Exemple #14
0
        public static CT_Cell Parse(XElement node, XmlNamespaceManager namespaceManager)
        {
            if (node == null)
            {
                return(null);
            }
            CT_Cell ctObj = new CT_Cell();

            ctObj.r = XmlHelper.ReadString(node.Attribute("r"));
            ctObj.s = XmlHelper.ReadUInt(node.Attribute("s"));
            if (node.Attribute("t") != null)
            {
                ctObj.t = (ST_CellType)Enum.Parse(typeof(ST_CellType), node.Attribute("t").Value);
            }
            ctObj.cm = XmlHelper.ReadUInt(node.Attribute("cm"));
            ctObj.vm = XmlHelper.ReadUInt(node.Attribute("vm"));
            ctObj.ph = XmlHelper.ReadBool(node.Attribute("ph"));
            foreach (XElement childNode in node.ChildElements())
            {
                if (childNode.Name.LocalName == "f")
                {
                    ctObj.f = CT_CellFormula.Parse(childNode, namespaceManager);
                }
                else if (childNode.Name.LocalName == "v")
                {
                    ctObj.v = childNode.Value;
                }
                else if (childNode.Name.LocalName == "is")
                {
                    ctObj.@is = CT_Rst.Parse(childNode, namespaceManager);
                }
                else if (childNode.Name.LocalName == "extLst")
                {
                    ctObj.extLst = CT_ExtensionList.Parse(childNode, namespaceManager);
                }
            }
            return(ctObj);
        }
Exemple #15
0
        public static CT_Cell Parse(XmlNode node, XmlNamespaceManager namespaceManager)
        {
            if (node == null)
            {
                return(null);
            }
            CT_Cell ctObj = new CT_Cell();

            ctObj.r = XmlHelper.ReadString(node.Attributes["r"]);
            ctObj.s = XmlHelper.ReadUInt(node.Attributes["s"]);
            if (node.Attributes["t"] != null)
            {
                ctObj.t = (ST_CellType)Enum.Parse(typeof(ST_CellType), node.Attributes["t"].Value);
            }
            ctObj.cm = XmlHelper.ReadUInt(node.Attributes["cm"]);
            ctObj.vm = XmlHelper.ReadUInt(node.Attributes["vm"]);
            ctObj.ph = XmlHelper.ReadBool(node.Attributes["ph"]);
            foreach (XmlNode childNode in node.ChildNodes)
            {
                if (childNode.LocalName == "f")
                {
                    ctObj.f = CT_CellFormula.Parse(childNode, namespaceManager);
                }
                else if (childNode.LocalName == "v")
                {
                    ctObj.v = childNode.InnerText;
                }
                else if (childNode.LocalName == "is")
                {
                    ctObj.@is = CT_Rst.Parse(childNode, namespaceManager);
                }
                else if (childNode.LocalName == "extLst")
                {
                    ctObj.extLst = CT_ExtensionList.Parse(childNode, namespaceManager);
                }
            }
            return(ctObj);
        }
Exemple #16
0
        /**
         * package/hierarchy use only - reuse an existing evaluation workbook if available for caching
         *
         * @param fpb evaluation workbook for reuse, if available, or null to create a new one as needed
         * @return a formula for the cell
         * @throws InvalidOperationException if the cell type returned by {@link #getCellType()} is not CELL_TYPE_FORMULA
         */
        protected internal String GetCellFormula(XSSFEvaluationWorkbook fpb)
        {
            CellType cellType = CellType;

            if (cellType != CellType.Formula)
            {
                throw TypeMismatch(CellType.Formula, cellType, false);
            }

            CT_CellFormula f = _cell.f;

            if (IsPartOfArrayFormulaGroup && f == null)
            {
                XSSFCell cell = ((XSSFSheet)Sheet).GetFirstCellInArrayFormula(this);
                return(cell.GetCellFormula(fpb));
            }
            if (f.t == ST_CellFormulaType.shared)
            {
                //return ConvertSharedFormula((int)f.si);
                return(ConvertSharedFormula((int)f.si, fpb == null ? XSSFEvaluationWorkbook.Create(Sheet.Workbook) : fpb));
            }
            return(f.Value);
        }
Exemple #17
0
        /**
         * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook.
         *
         * @param cell the cell to update
         * @param frwb the formula rendering workbbok that returns new sheet name
         */
        private void UpdateFormula(XSSFCell cell, String oldName, String newName)
        {
            CT_CellFormula f = cell.GetCTCell().f;

            if (f != null)
            {
                String formula = f.Value;
                if (formula != null && formula.Length > 0)
                {
                    int   sheetIndex = _wb.GetSheetIndex(cell.Sheet);
                    Ptg[] ptgs       = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex, -1);
                    foreach (Ptg ptg in ptgs)
                    {
                        UpdatePtg(ptg, oldName, newName);
                    }
                    String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs);
                    if (!formula.Equals(updatedFormula))
                    {
                        f.Value = (updatedFormula);
                    }
                }
            }
        }
Exemple #18
0
        /// <summary>
        /// Set the cells type (numeric, formula or string)
        /// </summary>
        /// <param name="cellType"></param>
        public void SetCellType(CellType cellType)
        {
            CellType prevType = CellType;

            if (IsPartOfArrayFormulaGroup)
            {
                NotifyArrayFormulaChanging();
            }
            if (prevType == CellType.Formula && cellType != CellType.Formula)
            {
                ((XSSFWorkbook)Sheet.Workbook).OnDeleteFormula(this);
            }

            switch (cellType)
            {
            case CellType.Blank:
                SetBlank();
                break;

            case CellType.Boolean:
                String newVal = ConvertCellValueToBoolean() ? TRUE_AS_STRING : FALSE_AS_STRING;
                _cell.t = (ST_CellType.b);
                _cell.v = (newVal);
                break;

            case CellType.Numeric:
                _cell.t = (ST_CellType.n);
                break;

            case CellType.Error:
                _cell.t = (ST_CellType.e);
                break;

            case CellType.String:
                if (prevType != CellType.String)
                {
                    String             str = ConvertCellValueToString();
                    XSSFRichTextString rt  = new XSSFRichTextString(str);
                    rt.SetStylesTableReference(_stylesSource);
                    int sRef = _sharedStringSource.AddEntry(rt.GetCTRst());
                    _cell.v = sRef.ToString();
                }
                _cell.t = (ST_CellType.s);
                break;

            case CellType.Formula:
                if (!_cell.IsSetF())
                {
                    CT_CellFormula f = new CT_CellFormula();
                    f.Value = "0";
                    _cell.f = (f);
                    if (_cell.IsSetT())
                    {
                        _cell.unsetT();
                    }
                }
                break;

            default:
                throw new ArgumentException("Illegal cell type: " + cellType);
            }
            if (cellType != CellType.Formula && _cell.IsSetF())
            {
                _cell.unsetF();
            }
        }
Exemple #19
0
 public void unsetF()
 {
     this.fField = null;
 }