/// <summary>
        /// REQUIREMENT: cell expression text starts with "=".
        /// Evaluates cell's text as an expression and returns its value.
        /// </summary>
        /// <param name="cell">cell who's text is to be evaluated.</param>
        /// <returns>Evaluated expression.</returns>
        private string EvaluateCellExpression(SpreadsheetCell cell)
        {
            // removes the first character (which should be '=') and whitespace.
            string expression = cell.Text.Substring(1).Replace(" ", string.Empty);

            ExpressionTree et = new ExpressionTree(expression);

            bool variableError = this.HandleCellVariables(et, cell);

            if (!variableError)
            {
                return(et.Evaluate().ToString());
            }
            else
            {
                return(cell.Value);
            }
        }
        /// <summary>
        /// REQUIREMENT: Variable names must be formatted as: "[column letter][row number]"
        /// Defines cell variables in the expression tree and subscribes the
        /// current cell to each variable cell's property changed event.
        /// </summary>
        /// <param name="et">Expression tree.</param>
        /// <param name="currCell">Current spreadsheet cell.</param>\
        /// <returns>If there was an error.</returns>
        private bool HandleCellVariables(ExpressionTree et, SpreadsheetCell currCell)
        {
            bool variableError = false;

            List <string> variableNames = et.GetVariableNames();

            foreach (string name in variableNames)
            {
                SpreadsheetCell varCell = this.GetCellFromName(name);

                if (varCell == null)
                {
                    currCell.SetValue("!(bad reference)");
                    variableError = true;
                }
                else if (varCell == currCell)
                {
                    currCell.SetValue("!(self reference)");
                    variableError = true;
                }
                else if (this.ContainsCircularReference(currCell, varCell))
                {
                    currCell.SetValue("!(circular reference)");
                    variableError = true;
                }
                else
                {
                    // If the cell's value string cannot be parsed as a double, defaults to 0.
                    double value = 0.0;
                    try
                    {
                        value = double.Parse(varCell.Value);
                    }
                    catch (FormatException) { }

                    et.SetVariable(name, value);
                    currCell.SubToCellChange(varCell);
                }
            }

            return(variableError);
        }
Example #3
0
        /// <summary>
        /// Here is where each cell that's changed essentially has a delegate that notifies its respective event has changed.
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public void OnPropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            if (e.PropertyName == "Text")
            {
                BasicCell temporary = sender as BasicCell;
                string    cellname  = temporary.GetCellName();
                DeleteDependency(cellname);
                if (temporary.Text != "" && temporary.Text[0] == '=' && temporary.Text.Length > 1)
                {
                    ExpressionTree tree = new ExpressionTree(temporary.Text.Substring(1));
                    SetDependency(cellname, tree.GetVariables());
                }

                EvaluateCell(sender as Cell);
            }
            else if (e.PropertyName == "BGColor")
            {
                CellPropertyChanged(sender, new PropertyChangedEventArgs("BGColor"));
            }
        }
Example #4
0
        /// <summary>
        /// Function used to evaluate all the cells.
        /// LOGIC LIVES HERE COULDN'T ENCAPSULATE FURTHER
        /// </summary>
        /// <param name="cell"></param>
        private void EvaluateCell(Cell cell)
        {
            BasicCell evalCell = cell as BasicCell;
            bool      error    = false;

            if (string.IsNullOrWhiteSpace(evalCell.Text))
            {
                evalCell.setValue("");
                CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
            }

            else if (evalCell.Text.Length > 1 && evalCell.Text[0] == '=')
            {
                string text = evalCell.Text.Substring(1);

                ExpressionTree evalTree  = new ExpressionTree(text);
                string[]       variables = evalTree.GetVariables();

                foreach (string variableName in variables)
                {
                    if (GetCell(variableName) == null)
                    {
                        CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
                        error = true;
                        break;
                    }

                    Cell   variableCell = GetCell(variableName);
                    double variableValue;

                    if (string.IsNullOrEmpty(variableCell.Value))
                    {
                        evalTree.SetVariable(variableName, 0);
                    }
                    else if (!double.TryParse(variableCell.Value, out variableValue))
                    {
                        evalTree.SetVariable(variableName, 0);
                    }
                    else
                    {
                        evalTree.SetVariable(variableName, variableValue);
                    }

                    string cellToEval = evalCell.ColumnIndex.ToString() + evalCell.RowIndex.ToString();
                    if (variableName == cellToEval)
                    {
                        evalCell.setValue("!(Self Reference)");
                        CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));

                        error = true;
                        break;
                    }
                }

                if (error)
                {
                    return;
                }

                foreach (string variableName in variables)
                {
                    if (IsCircularReference(variableName, evalCell.GetCellName()))
                    {
                        evalCell.setValue("!(Circular Reference!)");
                        CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));

                        error = true;
                        break;
                    }
                }

                if (error)
                {
                    return;
                }
                evalCell.setValue(evalTree.Evaluate().ToString());
                CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
            }

            else
            {
                evalCell.setValue(evalCell.Text);
                CellPropertyChanged(cell, new PropertyChangedEventArgs("Value"));
            }

            string cellName = evalCell.GetCellName();

            if (dependencyDict.ContainsKey(cellName))
            {
                foreach (string dependentCell in dependencyDict[cellName])
                {
                    EvaluateCell(GetCell(dependentCell));
                }
            }
        }
Example #5
0
        public void setValue(spreadSheetCell cell)
        {
            //we dont want to actually change the cell's text property, so we use toDelimit to store a copy
            string toDelimit = cell.Text;

            bool InvalidName = false;
            bool SelfRef = false;
            bool CircleRef = false;

            //if the cell's text starts with an "=" then we will need to perform operations on the cell
            if (toDelimit.StartsWith("="))
            {
                InvalidName = false;
                //this is an empty string that will store the new expression to be passed to the tree
                string newExpression = "";

                //This is going to help us grab all the important elements of the cell such as the constants and the variables
                var importantElements = Regex.Matches(toDelimit, @"\d+|\w+|\S").OfType<Match>().Select(m => m.Value);

                //string s will either be an operator, parenthesis, or cell name
                foreach (string s in importantElements)
                {
                    //if a letter is the first character in the string, then it is a cell name
                    if (s[0] > 64 && s[0] < 91)
                    {
                        //The following checks for a letter in the substring following the first letter
                        try
                        {
                            Int32.Parse(s.Substring(1));
                        }
                        catch (FormatException)
                        {
                            InvalidName = true;
                        }
                        //Check to make sure that the cell is within the range specific (1-50)
                        if (InvalidName == false)
                        {
                            if (Int32.Parse(s.Substring(1)) < 51 && Int32.Parse(s.Substring(1)) > 0)
                            {
                                InvalidName = false;
                            }
                            else
                                InvalidName = true;
                        }

                        if (cell.ReturnName() == s)
                        {
                            SelfRef = true;
                            break;
                        }

                        if (InvalidName == false && GetCell(s).Value != "")
                        {
                            //Check circular reference of cell about to be added
                            if (circleRef(cell.ReturnName(), s, GetCell(s).Text))
                            {
                                CircleRef = true;
                                break;
                            }

                            //If we get to this point we know we aren't adding a circular ref
                            newExpression += GetCell(s).Value;
                        }
                        //Default value, simply add to new expression
                        else if (InvalidName == false && GetCell(s).Value == "")
                        {
                            newExpression += "0";
                        }
                    }
                    else
                    {
                        newExpression += s;
                    }
                }

                if (CircleRef == false && SelfRef == false && InvalidName == false)
                {
                    ExpressionTree Exp = new ExpressionTree();
                    Exp.BuildTree(newExpression.Substring(1));
                    cell.Value = Exp.Evaluate().ToString();
                }

            }
            else
                if (InvalidName == false && SelfRef == false)
                    cell.Value = toDelimit;

            if (CircleRef)
            {
                cell.Value = "!(circular reference)";
            }
            if (InvalidName)
            {
                cell.Value = "!(bad cell name)";
            }
            if (SelfRef)
            {
                cell.Value = "!(self reference)";
            }
        }
        protected void OnPropertyChanged(object sender, PropertyChangedEventArgs e)
        {
            SpreadsheetCell sheetCell   = (SpreadsheetCell)sender;
            string          temp_string = sheetCell.Text;

            // If the text is a formula
            if (temp_string == string.Empty || temp_string[0] != '=') // If the text is NOT a formula
            {
                sheetCell.Text  = temp_string;
                sheetCell.Value = sheetCell.Text;
            }
            else //(temp_string != string.Empty || temp_string[0] == '=')
            {
                if (temp_string.Length > 2)
                {
                    string newExpression = temp_string.Substring(1); // Take out the '=' in the text, the rest will be an expression
                                                                     // Create a tree from that expression
                    ExpressionTree tree = new ExpressionTree(newExpression);

                    // Go through every variables in the tree dictionary
                    foreach (string key in tree.GetVariableNames())
                    {
                        // We are using a dictionary because dictionary cannot add the same key twice
                        // So it is easier to catch acception and check whether the function went into a circular loop
                        try
                        {
                            circularReferenceKeys.Add(sheetCell, 1);
                        }
                        catch (ArgumentException)
                        {
                            if (!this.CheckCircularReference())
                            {
                                Ok = 1;
                                return;
                            }
                        };

                        // MAKE THE KEY TRUE
                        // These booleans are here to evaluate the formula
                        // Covers when formula is less than 4
                        // When its 3 or 2, its in the correct format (Char Int (Int))
                        // Covers when the formula is larger than 1
                        // Anything that evaluate false from this will not be considered a proper formula
                        bool four  = key.Length < 4;
                        bool three = true;
                        if (key.Length == 3)
                        {
                            // three = Int32.TryParse(key[1].ToString(), out int temp1) && Int32.TryParse(key[2].ToString(), out int temp2);
                            if (Int32.TryParse(key[1].ToString(), out int temp1))
                            {
                                if (Int32.TryParse(key[2].ToString(), out int temp2))
                                {
                                    Int32.TryParse(key.Substring(1), out int temp3);
                                    if (temp3 > 50)
                                    {
                                        three = false;
                                    }
                                    else
                                    {
                                        three = true;
                                    }
                                }
                                else
                                {
                                    three = false;
                                }
                            }
                        }
                        bool two = true;
                        if (key.Length == 2)
                        {
                            two = Int32.TryParse(key[1].ToString(), out int tempp1);
                            if (tempp1 == 0)
                            {
                                two = false;
                            }
                        }
                        bool one = key.Length > 1;
                        if (four && three && two && one)
                        {
                            // Here we are doing the Try-Catch key
                            // Because if the program fails from creating a cell from that key
                            // that means the formula is bad
                            try
                            {
                                // Make a Cell out of that tree dictionary key
                                Cell cell = this.GetCellFromName(key);

                                double val = 0.0;
                                if (double.TryParse(cell.Value, out val))
                                {
                                    tree.SetVariable(key, val);
                                    // Evaluate the tree expression
                                    temp_string = tree.Evaluate().ToString();
                                    // Set the value of the cell
                                    sheetCell.Value = temp_string;
                                    // Only allow the most recent cell that caused the issue to change its value
                                    if (Ok == 1)
                                    {
                                        sheetCell.Value = "!Circular Reference";
                                        Ok = 0;
                                        circularReferenceKeys.Clear();
                                    }
                                }
                                else
                                {
                                    if (cell.Value == "")
                                    {
                                        sheetCell.Value = 0.ToString();
                                    }
                                    else
                                    {
                                        // Only allow the most recent cell that caused the issue to change its value
                                        if (cell.Value != "!Self Reference")
                                        {
                                            sheetCell.Value = cell.Value;
                                        }
                                    }
                                }

                                if (GetNameFromCell(sheetCell) == GetNameFromCell(cell))
                                {
                                    sheetCell.Value = "!Self Reference";
                                }

                                // Subsribe the cell to the PropertyChanged event
                                cell.ReferencedCellPropertyChanged += sheetCell.OnPropertyChanged;
                            }
                            catch (KeyNotFoundException)
                            {
                                sheetCell.Value = "!Bad Input";
                            };
                        }
                        else
                        {
                            sheetCell.Value = "!Bad Input";
                        }
                    }

                    if (tree.GetVariableNames().Count == 0)
                    {
                        // Evaluate the tree expression
                        temp_string = tree.Evaluate().ToString();
                        // Set the value of the cell
                        sheetCell.Value = temp_string;
                    }
                }
                else
                {
                    if (temp_string.Length == 2 && Int32.TryParse(temp_string[1].ToString(), out int temp))
                    {
                        sheetCell.Value = temp_string[1].ToString();
                    }
                    else
                    {
                        sheetCell.Value = "!Bad Input";
                    }
                }
            }

            // Invoke the CellPropertyChanged event
            this.OnCellPropertyChanged(sheetCell, e);
        }