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 + ")");
}
}
/// <summary>
/// Этот конструктор используется, когда проблема формулы не в токене
/// </summary>
/// <param name="formulaError">код ошибки в формуле</param>
public FormulaComputeResult(FormulaError formulaError)
{
_formulaCheckResult = formulaError;
// ToDo: определить позицию при ошибках, чтобы корректно выделять цветом ошибочные части!!!
_errorBeginPositionInFormulaText = 0;
_errorLengthInFormulaText = 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));
}
/// <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();
}
}
/// <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
});
}
}
/// <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());
}
}
/// <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);
}
/// <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();
}
public void TestDivideByZero()
{
Formula test = new Formula("(5/0)");
FormulaError testObject = new FormulaError("Cannot divide by zero.");
Assert.AreEqual(testObject, test.Evaluate(null));
}
/// <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));
}
/// <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();
}
}
/// <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);
}
public void TestDivisionByZero()
{
Formula f1 = new Formula("3/0");
FormulaError x = new FormulaError("Division by zero.");
Assert.AreEqual(x, f1.Evaluate(s => 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);
}
}
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);
}
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));
}
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);
}
/** Creates new ErrPtg */
public ErrPtg(int errorCode)
{
if (!FormulaError.IsValidCode(errorCode))
{
throw new ArgumentException("Invalid error code (" + errorCode + ")");
}
field_1_error_code = errorCode;
}
/// <summary>
/// Этот конструктор используется при успешном расчёте формулы с булевым результатом
/// </summary>
/// <param name="boolValue">булевый результат расчёта формулы</param>
public FormulaComputeResult(bool boolValue)
{
_formulaCheckResult = FormulaError.Ok;
_errorBeginPositionInFormulaText = 0;
_errorLengthInFormulaText = 0;
CalculatedBoolBoolValue = boolValue;
_resultIsBoolean = true;
}
private ErrorEval(FormulaError error)
{
_error = error;
if (!evals.ContainsKey(error))
{
evals.Add(error, this);
}
}
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));
}
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");
}
public void CheckForNullLookUp()
{
Formula f = new Formula("100 / 100");
Assert.IsInstanceOfType(f.Evaluate(null), typeof(FormulaError));
FormulaError err = new FormulaError();
string reason = err.Reason;
}
/**
* 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 + ")~");
}
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
}
/// <summary>
/// Этот конструктор используется при успешном расчёте формулы с числовым результатом
/// </summary>
/// <param name="doubleValue">числовой результат расчёта формулы</param>
public FormulaComputeResult(double doubleValue)
{
_formulaCheckResult = FormulaError.Ok;
_errorBeginPositionInFormulaText = 0;
_errorLengthInFormulaText = 0;
CalculatedDoubleValue = doubleValue;
_resultIsBoolean = false;
}
/// <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());
}
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"));
}
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"));
}
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);
}
