Example #1
0
 /// <summary>
 /// Get the column filter by specified address code of column (A, TZ, etc.)
 /// </summary>
 /// <param name="columnCode">the alphabet of address code used to locate a column in spreadsheet</param>
 /// <returns>instance of column filter, which contains the candidates list and selected items by user</returns>
 public AutoColumnFilterBody this[string columnCode]
 {
     get
     {
         int index = RGUtility.GetNumberOfChar(columnCode);
         return(this[index]);
     }
 }
Example #2
0
        public object Evaluate(ReoGridCell cell, ICompiledFormula cformula)
        {
            var grid = this.Workbook;

            if (grid == null)
            {
                return(null);
            }

            var formulaContext = (ReoScriptCompiledFormula)cformula;

            //var cell = formulaContext.Cell;
            var formula = formulaContext.Formula;

            List <ReferenceRange> referencedRanges = formulaContext.ReferencedCellOrRanges as List <ReferenceRange>;

            if (referencedRanges == null)
            {
                formulaContext.ReferencedCellOrRanges = referencedRanges = new List <ReferenceRange>();
            }
            else
            {
                referencedRanges.Clear();
            }

            // todo: improve: only create script context once
            //                when set data to a range
            var ctx = grid.Srm.CreateContext();

            // create an global variable getter
            ctx.ExternalVariableGetter = (id) =>
            {
#if FORMULA_CELL_INSTANCE_REF
                if (id.StartsWith("$"))
                {
                    var address = id.Substring(1);
                    if (ReoGridPos.IsValidAddress(address))
                    {
                        var pos = new ReoGridPos(address);
                        return(new RSCellObject(this, pos, cells[pos.Row, pos.Col]));
                    }
                    else
                    {
                        return(null);
                    }
                }
                else
#endif // FORMULA_CELL_INSTANCE_REF
                if (ReoGridPos.IsValidAddress(id))
                {
                    var pos = new ReoGridPos(id);
                    referencedRanges.Add(new ReferenceRange(grid, pos));

                    var cell = grid.GetCell(pos);
                    return(cell == null ? 0 : cell.InnerData);
                }
                else
                {
                    NamedRange range = grid.GetNamedRange(id);

                    if (range != null)
                    {
                        referencedRanges.Add(range);

                        var referencedCell = grid.GetCell(range.StartPos);
                        return((referencedCell == null || referencedCell.InnerData == null) ? 0 : referencedCell.InnerData);
                    }
                    else
                    {
                        return(null);
                    }
                }
            };

            try
            {
                // preprocess range syntax
                formula = RGUtility.RangeReferenceRegex.Replace(formula, (m) =>
                {
                    if (m.Groups["to_col"].Length > 0 && m.Groups["to_row"].Length > 0 &&
                        m.Groups["from_col"].Length > 0 && m.Groups["from_row"].Length > 0)
                    {
                        // range
                        int fromRow = -1;
                        if (!int.TryParse(m.Groups["from_row"].Value, out fromRow))
                        {
                            return("null");
                        }
                        fromRow--;

                        int toRow = -1;
                        if (!int.TryParse(m.Groups["to_row"].Value, out toRow))
                        {
                            return("null");
                        }
                        toRow--;

                        int fromCol = RGUtility.GetNumberOfChar(m.Groups["from_col"].Value);
                        int toCol   = RGUtility.GetNumberOfChar(m.Groups["to_col"].Value);

                        if (fromRow < 0)
                        {
                            fromRow = 0;
                        }
                        if (fromCol < 0)
                        {
                            fromCol = 0;
                        }
                        if (toRow > grid.RowCount - 1)
                        {
                            toRow = grid.RowCount - 1;
                        }
                        if (toCol > grid.RowCount - 1)
                        {
                            toCol = grid.ColumnCount - 1;
                        }

                        ReoGridRange range = new ReoGridRange(fromRow, fromCol, toRow - fromRow + 1, toCol - fromCol + 1);
                        referencedRanges.Add(new ReferenceRange(grid, range));

                        return(string.Format("new Range({0},{1},{2},{3})", range.Row, range.Col, range.Rows, range.Cols));
                    }
                    else
                    {
                        return(m.Value);
                    }
                });

                return(grid.Srm.CalcExpression(formula, ctx));
            }
            catch (ReoScriptException ex)
            {
                Logger.Log("formula", string.Format("error to evaluate formula: ", ex.Message));
                throw new FormulaEvalutionException(ex, "#ERR: " + ex.Message);
            }
        }