public Spreadsheet(int maxRows, int maxCols)
{
this.mColumnCount = maxCols;
this.mRowCount = maxRows;
this.mDependencies = new Dictionary <string, HashSet <string> >();
int i = 0, j = 0;
for (; i < maxRows; i++) //Separate loops for columns and rows in case they are not equal
{
for (j = 0; j < maxCols; j++)
{
Cell temp = new SheetCell();
mSheet[i, j] = temp;
mSheet[i, j].mRowIndex = i; //Give that cell the correct row index
mSheet[i, j].mColumnIndex = j; //Give that cell the correct column index
mSheet[i, j].PropertyChanged += OnPropertyChanged; //Subscribe to that cell's PropertyChanged event
}
}
mUndoStack = new Stack <ICmd>();
mRedoStack = new Stack <ICmd>();
}
public void Evaluate(Cell toEvaluate) //Decide if the cell is a formula or not and updates value accordingly
{
SheetCell temp = toEvaluate as SheetCell;
temp.Value = toEvaluate.Text; //Assume the cell isn't a formula; set mValue to mText
temp.Text = toEvaluate.Text; //Set mText to mText
toEvaluate = temp;
if (temp.Text != "")
{
if (temp.Text.ElementAt(0) == '=') //If the value starts with =, the input is a formula and it must be evaluated
{
string formula = temp.Text, sub = "";
int colIndex = 0, rowIndex = 0, i = 0;
Cell inFormula;
bool badref = false;
alphaBool alpha = alphaBool.NOT;
string cellName = toAlpha(toEvaluate.mColumnIndex.ToString()) + (toEvaluate.mRowIndex + 1).ToString();
formula = formula.Replace(" ", ""); //Get rid of any whitespace in the formula
formula = formula.Remove(0, 1); //Remove '='
temp.mTree = new ExpTree(formula);
Dictionary <string, double> variables = temp.mTree.GetVars(); //Retrieve the variables from the tree
var keys = variables.Keys; //Get the variable names
double varVal = 0;
for (i = 0; i < keys.Count; i++) //Lookup each variable in the spreadsheet
{
string thing = keys.ElementAt(i);
varVal = 0;
sub = thing.Substring(1); //Get the row index by separating the letter and number
alpha = isAlpha(thing.ElementAt(0));
if (alpha != alphaBool.NOT && int.TryParse(sub, out rowIndex) && rowIndex > 0 && rowIndex < 50) //If the variable has the form "A thru Z""1-50"
{
colIndex = (int)thing[0] - 'A'; //Get column index as an int
rowIndex--; //Got the rowIndex as an int in the conditional, now subtract one since rows go 1-50 and index goes 0-49
inFormula = GetCell(rowIndex, colIndex); //Get the cell in the formula (eg =A1, this would get A1)
double.TryParse(mSheet[rowIndex, colIndex].Value, out varVal);
temp.mTree.SetVar(thing, varVal); //Set the variable (if tryParse is unsuccessful, varVal = 0)
}
else //If the variable has a variable name that isn't a cell name
{
badref = true;
temp.Value = "!(bad reference)"; //Updated the value, not the text so user can hopefully figure out why it's a bad reference
}
}
if (!badref)
{
double result = temp.mTree.Eval();
temp.Value = result.ToString(); //Update current cell's value
UpdateTableAdd(temp); //Add the appropriate dependencies
UpdateTableDelete(temp); //Remove old dependencies
if (!checkSelfReference(cellName) && !checkCircularReference(cellName))
{
UpdateDependencies(temp); //Update current dependencies
}
else
{
temp.Value = "!(self reference)";
}
}
CellPropertyChanged(toEvaluate, new PropertyChangedEventArgs("Value")); //Update the UI
}
}
else
{
toEvaluate.Text = "";
CellPropertyChanged(toEvaluate, new PropertyChangedEventArgs("Value")); //Update the UI
}
}
/// <summary>
/// Method to call from Event handler for Sheet DataGridView CellValuePushed event.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
/// <param name="sheet"></param>
public static void SheetCellValuePushed(object sender, DataGridViewCellValueEventArgs e, Sheet sheet)
{
try
{
//identify cell type from range given by row/column
switch (sheet.GetCellTypeAtCoordinates(e.RowIndex, e.ColumnIndex))
{
case Sheet.CellTypes.Empty:
{
//if empty range, ignore
#if debugcell
e.Value = String.Format("({0},{1});", e.RowIndex, e.ColumnIndex);
#endif
break;
}
case Sheet.CellTypes.XCategory:
{
//if x category range, identify category-item @ x, y
e.Value = sheet.GetCategoryItemNameAtCoordinates(e.RowIndex, e.ColumnIndex, true);
break;
}
case Sheet.CellTypes.YCategory:
{
//if y category range, identify category-item @ x, y
e.Value = sheet.GetCategoryItemNameAtCoordinates(e.RowIndex, e.ColumnIndex, true);
break;
}
case Sheet.CellTypes.Value:
{
Int32 cellIndex = -1;
SheetCell cell = default(SheetCell);
cellIndex = sheet.GetValueIndexAtCoordinates(e.RowIndex, e.ColumnIndex);
if (cellIndex != -1)
{
cell = sheet.Cells[cellIndex];
}
else
{
throw new ArgumentOutOfRangeException(String.Format("Cell index ({0}) was out of range: ({1}...{2})", cellIndex, 0, sheet.Cells.Count - 1));
}
//update cell with display value.
cell.Value = e.Value.ToString();
break;
}
}
}
catch (Exception ex)
{
Log.Write(ex, MethodBase.GetCurrentMethod(), EventLogEntryType.Error);
throw;
}
}
//private static int _p = default(int);
//public static int p
//{
// get { return _p; }
// set { _p = value; }
//}
static void Main(string[] args)
{
SheetCell cellP = new SheetCell("2");
SheetCell cellQ = new SheetCell("3");
SheetCell cellR = new SheetCell("4");
SheetCell cellS = new SheetCell("5");
SheetCell cellT = new SheetCell("6");
SheetCell cellResult = new SheetCell();
//float p = 2.0F;
//float q = 3.0F;
//float r = 4.0F;
//float s = 5.0F;
//float t = 6.0F;
//float result = default(float);
//y = p + q * r - s / t
Console.WriteLine("======");
Console.WriteLine(" calculate it straight up ");
Console.WriteLine(String.Format("p + q * r - s / t = {0} + {1} * {2} - {3} / {4} = {5}", Single.Parse(cellP.Value), Single.Parse(cellQ.Value), Single.Parse(cellR.Value), Single.Parse(cellS.Value), Single.Parse(cellT.Value), Single.Parse(cellP.Value) + (Single.Parse(cellQ.Value) * Single.Parse(cellR.Value)) - (Single.Parse(cellS.Value) / Single.Parse(cellT.Value))));
#if operation_statement
//this is how MDSS formulas work now
//these are parsed rescursively, and run as each node is identified
OperationBase rootOperation = null;
OperationBase parentOperation1 = null;
OperationBase childOperation1 = null;
OperationBase childOperation2 = null;
rootOperation = new OperationBinary();
parentOperation1 = new OperationBinary();
childOperation1 = new OperationBinary();
childOperation2 = new OperationBinary();
//define q * r
childOperation1.Operands["Left"] = new OperandLiteral(/*q*/ Single.Parse(cellQ.Value));
childOperation1.Operands["Right"] = new OperandLiteral(/*r*/ Single.Parse(cellR.Value));
childOperation1.Operator = new OperatorMultiply();
//define p + (q*r)
parentOperation1.Operands["Left"] = new OperandLiteral(/*p*/ Single.Parse(cellP.Value));
parentOperation1.Operands["Right"] = new OperandOperation(childOperation1);
parentOperation1.Operator = new OperatorAdd();
//define s / t
childOperation2.Operands["Left"] = new OperandLiteral(/*s*/ Single.Parse(cellS.Value));
childOperation2.Operands["Right"] = new OperandLiteral(/*t*/ Single.Parse(cellT.Value));
childOperation2.Operator = new OperatorDivide();
//define p+(q*r) - (s/t)
rootOperation.Operands["Left"] = new OperandOperation(parentOperation1);
rootOperation.Operands["Right"] = new OperandOperation(childOperation2);
rootOperation.Operator = new OperatorSubtract();
Console.WriteLine("======");
Console.WriteLine(" calculate it with Operations ");
cellResult.Value = rootOperation.Run(new FormulaExecutionContext()).ToString();
Console.WriteLine("rootOperation:" + cellResult.Value);
#endif
#if expression_statement
//this is how I would like MDSS formulas to work in the future
//these will need to be parsed recursively,
// but instead of being Run, each node will need to contribute an expression component
// to the overall composition of a statement expression.
//the formula must be parsed when first entered or subsequently changed,
// and stored for later invocation.
//the means by which parameters (cells) are attached is TBD.
Console.WriteLine("======");
Console.WriteLine(" do it without invoke calls on every sub-expression; embed one within another ");
ParameterExpression parameterP = Expression.Parameter(typeof(float), "p");
ParameterExpression parameterQ = Expression.Parameter(typeof(float), "q");
ParameterExpression parameterR = Expression.Parameter(typeof(float), "r");
ParameterExpression parameterS = Expression.Parameter(typeof(float), "s");
ParameterExpression parameterT = Expression.Parameter(typeof(float), "t");
BinaryExpression multiplyExpressionQ_R = Expression.Multiply(parameterQ, parameterR);
BinaryExpression addExpressionP_QR = Expression.Add(parameterP, multiplyExpressionQ_R);
BinaryExpression divideExpressionS_T = Expression.Divide(parameterS, parameterT);
BinaryExpression subtractExpressionPQR_ST = Expression.Subtract(addExpressionP_QR, divideExpressionS_T);
ParameterExpression parameterResultExpression = Expression.Variable(typeof(float), "result");
ParameterExpression[] parameterResultExpressionList = new ParameterExpression[] { parameterResultExpression };
ParameterExpression[] inputParametersExpression = new ParameterExpression[] { parameterP, parameterQ, parameterR, parameterS, parameterT };
//adding cell *values* to array will not automatically update
//adding lambdas that *returns* new array of current cell values may automatically update???
Func <Object[]> inputParametersExpressionArgumentsDelegate =
() =>
{
return(new Object[] { Single.Parse(cellP.Value), Single.Parse(cellQ.Value), Single.Parse(cellR.Value), Single.Parse(cellS.Value), Single.Parse(cellT.Value) });
};
Delegate someFormulaExpressionDelegate =
Expression.Lambda
(
Expression.Block
(
parameterResultExpressionList,
Expression.Assign
(
parameterResultExpression,
subtractExpressionPQR_ST
)
),
inputParametersExpression
).Compile();
cellResult.Value = someFormulaExpressionDelegate.DynamicInvoke(inputParametersExpressionArgumentsDelegate()).ToString();
Console.WriteLine(String.Format("someFormulaExpressionDelegate DynamicInvoke (with p={0}):{1}", Single.Parse(cellP.Value), cellResult.Value));
#endif
//change variable p in y = p + q * r - s / t
Console.WriteLine();
Console.WriteLine("======");
cellP.Value = 10.ToString();
Console.WriteLine(String.Format("changed variable p to {0} in y = p + q * r - s / t", Single.Parse(cellP.Value)));
Console.WriteLine("calculate it straight up");
Console.WriteLine(String.Format("p + q * r - s / t = {0} + {1} * {2} - {3} / {4} = {5}", Single.Parse(cellP.Value), Single.Parse(cellQ.Value), Single.Parse(cellR.Value), Single.Parse(cellS.Value), Single.Parse(cellT.Value), Single.Parse(cellP.Value) + (Single.Parse(cellQ.Value) * Single.Parse(cellR.Value)) - (Single.Parse(cellS.Value) / Single.Parse(cellT.Value))));
Console.WriteLine("======");
Console.WriteLine("recalc operation with new value");
cellResult.Value = rootOperation.Run(new FormulaExecutionContext()).ToString();
Console.WriteLine(String.Format("rootOperation uses constants, not variables, (with p={0}){1}:", Single.Parse(cellP.Value), cellResult.Value));
Console.WriteLine("======");
Console.WriteLine("recalc expression with new value");
cellResult.Value = someFormulaExpressionDelegate.DynamicInvoke(inputParametersExpressionArgumentsDelegate()).ToString();
Console.WriteLine(String.Format("someFormulaExpressionDelegate DynamicInvoke (with p={0}):{1}", Single.Parse(cellP.Value), cellResult.Value));
//to replace formula made of operations with formula made of expressions, need to
//a) be able to build formula dynamically at runtime from text formula string, and
//b) invoke with cell references for assigner and assignee cells.
//
//Unfortunately, the Expressions above are not tied to variable references (to cells) but inputParametersExpression,
//so formula must be handed variables (cells) at the time that the formula is executed.
//Need to embed variable reference for this to work right with expressions; the way to do this
// may be with closures, and I'll need to create a Lambda expression, not just a simple expression.
//If I can hand off a lambda that will evaluate the current value of a cell, then this should work.
//
//Operations are using cell reference that are evaluated at the time that the formula is executed,
//which works but is slow. I would like to use a mechanism that can find the cell reference once
//when the formula is evaluated and constructed,
//and then use that every time the formula is used, until the formula changes. However, I need to
//also be sure that changing the layout of the
//sheet will not cause problems. (I think it should not, as the criteria used to find a cell
//should find the same one regardless of where it is displayed.)
//
//Expression lambda should be an Action of T, but with no input param or return values.
//Instead, all cell references, assigner and assignee, should be embedded as variables in a code block.
//see also
//http://community.bartdesmet.net/blogs/bart/archive/2009/08/10/expression-trees-take-two-introducing-system-linq-expressions-v4-0.aspx
#region Statement
#if example
var to = Expression.Parameter(typeof(int), "to");
var res = Expression.Variable(typeof(List <int>), "res");
var n = Expression.Variable(typeof(int), "n");
var found = Expression.Variable(typeof(bool), "found");
var d = Expression.Variable(typeof(int), "d");
var breakOuter = Expression.Label();
var breakInner = Expression.Label();
var getPrimes =
// Func<int, List<int>> getPrimes =
Expression.Lambda <Func <int, List <int> > >(
// {
Expression.Block(
// List<int> res;
new [] { res },
// res = new List<int>();
Expression.Assign(
res,
Expression.New(typeof(List <int>))
),
// {
Expression.Block(
// int n;
new [] { n },
// n = 2;
Expression.Assign(
n,
Expression.Constant(2)
),
// while (true)
Expression.Loop(
// {
Expression.Block(
// if
Expression.IfThen(
// (!
Expression.Not(
// (n <= to)
Expression.LessThanOrEqual(
n,
to
)
// )
),
// break;
Expression.Break(breakOuter)
),
// {
Expression.Block(
// bool found;
new[] { found },
// found = false;
Expression.Assign(
found,
Expression.Constant(false)
),
// {
Expression.Block(
// int d;
new [] { d },
// d = 2;
Expression.Assign(
d,
Expression.Constant(2)
),
// while (true)
Expression.Loop(
// {
Expression.Block(
// if
Expression.IfThen(
// (!
Expression.Not(
// d <= Math.Sqrt(n)
Expression.LessThanOrEqual(
d,
Expression.Convert(
Expression.Call(
null,
typeof(Math).GetMethod("Sqrt"),
Expression.Convert(
n,
typeof(double)
)
),
typeof(int)
)
)
// )
),
// break;
Expression.Break(breakInner)
),
// {
Expression.Block(
// if (n % d == 0)
Expression.IfThen(
Expression.Equal(
Expression.Modulo(
n,
d
),
Expression.Constant(0)
),
// {
Expression.Block(
// found = true;
Expression.Assign(
found,
Expression.Constant(true)
),
// break;
Expression.Break(breakInner)
// }
)
)
// }
),
// d++;
Expression.PostIncrementAssign(d)
// }
),
breakInner
)
),
// if
Expression.IfThen(
// (!found)
Expression.Not(found),
// res.Add(n);
Expression.Call(
res,
typeof(List <int>).GetMethod("Add"),
n
)
)
),
// n++;
Expression.PostIncrementAssign(n)
// }
),
breakOuter
)
),
res
),
to
// }
).Compile();
foreach (var num in getPrimes(100))
{
Console.WriteLine(num);
}
#endif
#endregion Statement
#region article
//see also
//http://stackoverflow.com/questions/1644146/user-defined-formulas-in-c-sharp
//I have written an open source project, Dynamic Expresso, that can convert text expression written using a C# syntax into delegates (or expression tree). Expressions are parsed and transformed into Expression Trees without using compilation or reflection.
//You can write something like:
//var interpreter = new Interpreter();
//var result = interpreter.Eval("8 / 2 + 2");
//or
//var interpreter = new Interpreter()
// .SetVariable("service", new ServiceExample());
//string expression = "x > 4 ? service.SomeMethod() : service.AnotherMethod()";
//Lambda parsedExpression = interpreter.Parse(expression,
// new Parameter("x", typeof(int)));
//parsedExpression.Invoke(5);
//My work is based on Scott Gu article http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx .
//see also
//https://github.com/davideicardi/DynamicExpresso/
#endregion Article
#region Article
//see also
#endregion Article
Console.ReadLine();
}
