예제 #1
0
        private String ConvertCellValueToString(CellType cellType)
        {
            switch (cellType)
            {
            case CellType.Blank:
                return("");

            case CellType.Boolean:
                return(BooleanCellValue ? "TRUE" : "FALSE");

            case CellType.String:
                return(StringCellValue);

            case CellType.Numeric:
                return(NumericCellValue.ToString());

            case CellType.Error:
                byte errVal = ErrorCellValue;
                return(FormulaError.ForInt(errVal).String);

            case CellType.Formula:
                if (_value != null)
                {
                    FormulaValue fv = (FormulaValue)_value;
                    if (fv.GetFormulaType() != CellType.Formula)
                    {
                        return(ConvertCellValueToString(fv.GetFormulaType()));
                    }
                }
                return("");

            default:
                throw new InvalidOperationException("Unexpected cell type (" + cellType + ")");
            }
        }
예제 #2
0
 /// <summary>
 /// Этот конструктор используется, когда проблема формулы не в токене
 /// </summary>
 /// <param name="formulaError">код ошибки в формуле</param>
 public FormulaComputeResult(FormulaError formulaError)
 {
     _formulaCheckResult = formulaError;
     // ToDo: определить позицию при ошибках, чтобы корректно выделять цветом ошибочные части!!!
     _errorBeginPositionInFormulaText = 0;
     _errorLengthInFormulaText        = 0;
 }
예제 #3
0
        public void TestDivideByZeroWithVariable()
        {
            Formula      test       = new Formula("5/x3", Normalize, IsValid);
            FormulaError testObject = new FormulaError("Cannot divide by zero.");

            Assert.AreEqual(testObject, test.Evaluate(LookUp));
        }
예제 #4
0
        /// <summary>
        /// Updates the Spreadsheet window.
        /// </summary>
        /// <param name="cellname">The name of the cell to be updated.</param>
        private void UpdateSpreadsheetWindow(string cellname)
        {
            object cellContents = spreadsheet.GetCellContents(cellname);
            object cellValue    = spreadsheet.GetCellValue(cellname);

            window.CellName = cellname;

            if (cellContents is Formula)
            {
                window.Contents = "=" + cellContents.ToString();
            }
            else
            {
                window.Contents = cellContents.ToString();
            }

            if (cellValue is FormulaError)
            {
                FormulaError error = (FormulaError)cellValue;
                window.Value = error.Reason;
            }
            else
            {
                window.Value = cellValue.ToString();
            }
        }
예제 #5
0
        /// <summary>
        /// Given a cell value, creates a string representation.
        /// This method determines if a cell's value is a string, double, or formula error
        /// </summary>
        /// <param name="contents"></param>
        /// <returns></returns>
        private string[] valueToString(object value)
        {
            if (value is FormulaError)
            {
                FormulaError err    = (FormulaError)value;
                string       reason = err.Reason;
                string       msg;

                if (reason.Contains("InvalidCastException"))
                {
                    msg = "You just refrenced a string or empty cell. Please revise.";
                }
                else
                {
                    msg = "Unknown reference.";
                }
                return(new string[2] {
                    "Formula Err", msg
                });
            }
            else
            {
                return(new string[2] {
                    value.ToString(), null
                });
            }
        }
예제 #6
0
        /// <summary>
        /// Sets the specified cell back to its original value.
        /// </summary>
        /// <param name="cellName"></param>
        private void ResetCell(string cellName)
        {
            int[] rowCol = GetCellPosition(cellName);
            int   col    = rowCol[0];
            int   row    = rowCol[1];

            string value = ss1.GetCellValue(cellName).ToString();

            if (ss1.GetCellValue(cellName).GetType() == typeof(FormulaError))
            {
                FormulaError error = (FormulaError)ss1.GetCellValue(cellName);
                if (error.Reason == "Circular dependency")
                {
                    spreadsheetPanel1.SetValue(col, row, "#REF");
                }
                else
                {
                    spreadsheetPanel1.SetValue(col, row, "Formula Error!");
                }
            }
            else
            {
                spreadsheetPanel1.SetValue(col, row, ss1.GetCellValue(cellName).ToString());
            }
        }
예제 #7
0
        /// <summary>
        /// Writes the given contents to a specified cell location.
        /// </summary>
        private void SetCell(int row, int col, string cellVal)
        {
            string cellName = GetCellName(col, row);

            HashSet <string> auxCells = new HashSet <string>(ss1.getDependentCells(cellName));

            ss1.SetContentsOfCell(cellName, cellVal);

            //if the result is a formula error display a formula error message, otherwise set the cell with the result.
            Type a = ss1.GetCellValue(cellName).GetType();

            if (ss1.GetCellValue(cellName).GetType() == typeof(FormulaError))
            {
                FormulaError error = (FormulaError)ss1.GetCellValue(cellName);
                if (error.Reason == "Circular dependency")
                {
                    spreadsheetPanel1.SetValue(col, row, "#REF");
                }
                else
                {
                    spreadsheetPanel1.SetValue(col, row, "Formula Error!");
                }
            }

            else
            {
                spreadsheetPanel1.SetValue(col, row, ss1.GetCellValue(cellName).ToString());
                UpdateCells(new HashSet <string>(ss1.getDependentCells(cellName)));
            }
            UpdateCells(auxCells);
        }
예제 #8
0
/// <summary>
/// helper method that updates the cell value box and the cell name and the cell selected.
/// </summary>
        private void SpreadsheetUpdater()
        {
            String CellNameText = GetCellName();
            Object cellContents = spreadsheet.GetCellContents(CellNameText);
            Object cellValue    = spreadsheet.GetCellValue(CellNameText);


            cellNameBox.Text = CellNameText;


            if (spreadsheet.GetCellContents(CellNameText) is Formula)
            {
                cellContentsBox.Text = "=" + cellContents.ToString();
            }
            else // otherwise just set the textbox equal to the content
            {
                cellContentsBox.Text = cellContents.ToString();
            }


            if (spreadsheet.GetCellValue(CellNameText) is FormulaError)
            {
                FormulaError error = (FormulaError)spreadsheet.GetCellValue(CellNameText);
                cellValueBox.Text = error.Reason;
            }


            cellValueBox.Text = cellValue.ToString();
        }
예제 #9
0
        public void TestDivideByZero()
        {
            Formula      test       = new Formula("(5/0)");
            FormulaError testObject = new FormulaError("Cannot divide by zero.");

            Assert.AreEqual(testObject, test.Evaluate(null));
        }
예제 #10
0
        /// <summary>
        /// Values the of.
        /// </summary>
        /// <param name="errorCode">The error code.</param>
        /// <returns></returns>
        public static ErrorConstant ValueOf(int errorCode)
        {
            if (FormulaError.IsValidCode(errorCode))
            {
                switch ((FormulaErrorEnum)errorCode)
                {
                case FormulaErrorEnum.NULL: return(NULL);

                case FormulaErrorEnum.DIV_0: return(DIV_0);

                case FormulaErrorEnum.VALUE: return(VALUE);

                case FormulaErrorEnum.REF: return(REF);

                case FormulaErrorEnum.NAME: return(NAME);

                case FormulaErrorEnum.NUM: return(NUM);

                case FormulaErrorEnum.NA: return(NA);

                default: break;
                }
            }
            Console.Error.WriteLine("Warning - Unexpected error code (" + errorCode + ")");
            return(new ErrorConstant(errorCode));
        }
예제 #11
0
        /// <summary>
        /// Helper method that updates the spreadsheet, including the text boxes that display
        /// the cell name, value, and content, and also the cell value displayed in the cell itself
        /// </summary>
        private void UpdateSpreadsheet()
        {
            // get the name, content, and value of the current cell
            String cellName     = GetCellName();
            Object cellContents = spreadsheet.GetCellContents(cellName);
            Object cellValue    = spreadsheet.GetCellValue(cellName);

            // set the contents of the textbox equal to that cell name
            cellNameBox.Text = cellName;

            // if the cell content is a Formula, add the '=' back on
            if (spreadsheet.GetCellContents(cellName) is Formula)
            {
                cellContentsBox.Text = "=" + cellContents.ToString();
            }
            else // otherwise just set the textbox equal to the content
            {
                cellContentsBox.Text = cellContents.ToString();
            }

            // check if the cell value is a formula error, say so
            if (spreadsheet.GetCellValue(cellName) is FormulaError)
            {
                // if the value is a formula error, put the message in the text box
                FormulaError error = (FormulaError)spreadsheet.GetCellValue(cellName);
                cellValueBox.Text = error.Reason;
            }

            else // otherwise just set the textbox equal to the value
            {
                cellValueBox.Text = cellValue.ToString();
            }
        }
예제 #12
0
        /// <summary>
        /// Helper method that will update cell dependents when their parent cell gets changed.
        /// This method first clears all the cells in the spreadsheet (needed for the case when
        /// we open a spreadsheet to overwrite the current one), and then it updates the actual
        /// cells in the spreadsheet to display the correct values.
        /// </summary>
        /// <param name="cellName">The name of the cell to update</param>
        private void UpdateCell(string cellName)
        {
            // data needed to update cell
            int    cellColIndex;
            int    cellRowIndex;
            string cellValue;

            // helper methods return the index of the row and column
            cellColIndex = GetColIndex(cellName);
            cellRowIndex = GetRowIndex(cellName);

            // check if the cells value is a formula error, if it is say so
            if ((spreadsheet.GetCellValue(cellName) is FormulaError))
            {
                // if the value is a formula error, put the message in the text box
                FormulaError error = (FormulaError)spreadsheet.GetCellValue(cellName);
                cellValue = error.Reason;
            }
            else // otherwise, set cellValue to the cell's value
            {
                cellValue = spreadsheet.GetCellValue(cellName).ToString();
            }

            // update the value of the cell
            spreadsheetPanel1.SetValue(cellColIndex, cellRowIndex, cellValue);
        }
예제 #13
0
        public void TestDivisionByZero()
        {
            Formula      f1 = new Formula("3/0");
            FormulaError x  = new FormulaError("Division by zero.");

            Assert.AreEqual(x, f1.Evaluate(s => 0));
        }
예제 #14
0
        /// <summary>
        /// Преобразование кода ошибки в локализованную строку
        /// </summary>
        /// <param name="formulaError">код ошибки</param>
        /// <returns>локализованная строка с текстом ошибки</returns>
        public static string TokenErrorToString(FormulaError formulaError)
        {
            switch (formulaError)
            {
            case FormulaError.UnexpectedSymbols:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorUnexpectedSymbols));

            case FormulaError.ClosingBracketNotOpened:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorClosingBracketNotOpened));

            case FormulaError.LastTokenCantBeOperation:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorLastTokenCantBeOperation));

            case FormulaError.OpeningBracketNotClosed:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorOpeningBracketNotClosed));

            case FormulaError.SimilarTokensOneByOne:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorSimilarTokensOneByOne));

            case FormulaError.TokenMustBeOperation:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorTokenMustBeOperation));

            case FormulaError.TokenMustBeValue:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorTokenMustBeValue));

            case FormulaError.MultipluDotInNumber:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorMultiplyPointInNumber));

            case FormulaError.TokenPointsAbsentItem:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorTokenPointsAbsentItem));

            case FormulaError.DotCantBeLastSymbolOfNumber:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorPointCantBeLastSymbolOfNumber));

            case FormulaError.UnknownMathOperation:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorUnknownMathOperation));

            case FormulaError.UnknownLogicOperation:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorUnknownLogicOperation));

            case FormulaError.CantOperateMathAndLogicValues:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorCantOperateMathAndLogicValues));

            case FormulaError.ThisFormulaPartMustBeLogic:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorThisFormulaPartMustBeLogic));

            case FormulaError.ThisFormulaPartMustBeMath:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorThisFormulaPartMustBeMath));

            case FormulaError.Exception:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorException));

            case FormulaError.DivisionByZero:
                return(LanguageManager.GetPhrase(Phrases.FormulaErrorDivisionByZero));

            default:
                return("Untranslated: " + formulaError);
            }
        }
예제 #15
0
        public void PublicTestDivisionByZero()
        {
            Formula f        = new Formula("(1 + 2) / (3 - 3)", s => s, s => true);
            Object  result   = f.Evaluate(BasicLookup);
            Object  expected = new FormulaError("Division by zero.");

            Assert.AreEqual(expected, result);
        }
예제 #16
0
        public void ClosingParenthesisDivideByZero()
        {
            Formula      f = new Formula("(2+6)/0");
            FormulaError e = (FormulaError)f.Evaluate(s => 0.0);

            Assert.AreEqual("Attempted to divide by zero.", e.Reason);
            Assert.IsInstanceOfType(new Formula("(2+6)/0").Evaluate(s => 0.0), typeof(FormulaError));
        }
예제 #17
0
        public void PublicTestVariableLookupFailure()
        {
            Formula f        = new Formula("((X * Y) - ZOOANIMALS)", s => s, s => true);
            Object  result   = f.Evaluate(BasicLookup);
            Object  expected = new FormulaError("Variable lookup failed.");

            Assert.AreEqual(expected, result);
        }
예제 #18
0
        /** Creates new ErrPtg */

        public ErrPtg(int errorCode)
        {
            if (!FormulaError.IsValidCode(errorCode))
            {
                throw new ArgumentException("Invalid error code (" + errorCode + ")");
            }
            field_1_error_code = errorCode;
        }
예제 #19
0
 /// <summary>
 /// Этот конструктор используется при успешном расчёте формулы с булевым результатом
 /// </summary>
 /// <param name="boolValue">булевый результат расчёта формулы</param>
 public FormulaComputeResult(bool boolValue)
 {
     _formulaCheckResult = FormulaError.Ok;
     _errorBeginPositionInFormulaText = 0;
     _errorLengthInFormulaText        = 0;
     CalculatedBoolBoolValue          = boolValue;
     _resultIsBoolean = true;
 }
예제 #20
0
 private ErrorEval(FormulaError error)
 {
     _error = error;
     if (!evals.ContainsKey(error))
     {
         evals.Add(error, this);
     }
 }
예제 #21
0
        public void TestDivideByZeroSlightlyComplexGetReason()
        {
            Formula      f  = new Formula("2 / (5.5-3.3 - 1.1 - 0.1 - 1.0)");
            FormulaError fe = (FormulaError)f.Evaluate(s => 0);

            Assert.AreEqual("Error. A division by 0 occurred", fe.Reason);
            Assert.IsInstanceOfType(f.Evaluate(s => 0), typeof(FormulaError));
        }
예제 #22
0
        public void TestGetReason()
        {
            Formula      f      = new Formula("1/0");
            FormulaError result = (FormulaError)f.Evaluate(lookup);
            String       Reason = result.Reason;

            Assert.AreEqual(Reason, "Divition By Zero Is Not Allowed");
        }
예제 #23
0
        public void CheckForNullLookUp()
        {
            Formula f = new Formula("100 / 100");

            Assert.IsInstanceOfType(f.Evaluate(null), typeof(FormulaError));

            FormulaError err    = new FormulaError();
            string       reason = err.Reason;
        }
예제 #24
0
        /**
         * Converts error codes to text.  Handles non-standard error codes OK.
         * For debug/test purposes (and for formatting error messages).
         * @return the String representation of the specified Excel error code.
         */

        public static string GetText(int errorCode)
        {
            if (FormulaError.IsValidCode(errorCode))
            {
                return(FormulaError.ForInt(errorCode).String);
            }
            // Give a special string, based on ~, to make clear this isn't a standard Excel error
            return("~non~std~err(" + errorCode + ")~");
        }
예제 #25
0
        public void TestGetCellValueFormulaError()
        {
            AbstractSpreadsheet s = buildSheet();

            s.SetContentsOfCell("B1", "apple");
            FormulaError val = (FormulaError)s.GetCellValue("A1");

            Assert.AreEqual("Lookup of a variable failed", val.Reason); // Lookup of B1 should fail since it does not map to number
        }
예제 #26
0
        /// <summary>
        /// Этот конструктор используется при успешном расчёте формулы с числовым результатом
        /// </summary>
        /// <param name="doubleValue">числовой результат расчёта формулы</param>
        public FormulaComputeResult(double doubleValue)
        {
            _formulaCheckResult = FormulaError.Ok;
            _errorBeginPositionInFormulaText = 0;
            _errorLengthInFormulaText        = 0;

            CalculatedDoubleValue = doubleValue;
            _resultIsBoolean      = false;
        }
예제 #27
0
 /// <summary>
 /// Retrieves the value from a cell from the spreadsheet object
 /// </summary>
 /// <param name="name"></param>
 /// <returns></returns>
 private string ReturnCellValue(string name)
 {
     if (spreadsheet.GetCellValue(name) is FormulaError) // value can be a FormulaError
     {
         FormulaError error = (FormulaError)spreadsheet.GetCellValue(name);
         return(error.Reason);
     }
     return(spreadsheet.GetCellValue(name).ToString());
 }
예제 #28
0
        public void SetContentsOfCell07()
        {
            SpreadsheetTests01 s1 = new SpreadsheetTests01(new Regex("^[A-Z]*[1-2]$"));

            s1.SetContentsOfCell("A2", "=A1 + B1");

            FormulaError err = (FormulaError)s1.GetCellValue("A2");

            Assert.AreEqual(new FormulaError(err.Reason), s1.GetCellValue("A2"));
        }
예제 #29
0
        public void SetContentsOfCell09()
        {
            SpreadsheetTests01 s1 = new SpreadsheetTests01();

            s1.SetContentsOfCell("a52", "=A1 + B1");

            FormulaError err = (FormulaError)s1.GetCellValue("A52");

            Assert.AreEqual(new FormulaError(err.Reason), s1.GetCellValue("A52"));
        }
예제 #30
0
        private static void ConfirmError(HSSFFormulaEvaluator fe, ICell cell, String formulaText,
                                         FormulaError expectedErrorCode)
        {
            cell.CellFormula = (formulaText);
            fe.NotifyUpdateCell(cell);
            CellValue result = fe.Evaluate(cell);

            Assert.AreEqual(result.CellType, CellType.Error);
            Assert.AreEqual(expectedErrorCode.Code, result.ErrorValue);
        }