public static void GetAllSheetFormulas()
{
// this will return a line for each equation on the current worksheet where the cell reference is assumed to be a variable
// iterate through used range
Excel.Application app = (Excel.Application)ExcelDnaUtil.Application;
Excel.Worksheet sht = (Excel.Worksheet)app.ActiveSheet;
Excel.Range usedRange = sht.UsedRange;
var exprs = new List <ComputerAlgebra.Expression>();
var system = new List <Equal>();
var constants = new List <Arrow>();
foreach (Excel.Range rng in usedRange.Cells)
{
if ((bool)rng.HasFormula)
{
// if the cell has a formula, obtain the math equation for it
Debug.WriteLine($"{rng.Address} has formula");
string formula = rng.Formula.ToString();
Debug.WriteLine($"formula: {formula}");
// remove the equal sign
formula = formula.Substring(1);
Debug.WriteLine($"formula: {formula}");
// take that thing and remove the leading = sign
// TODO: anything required for the $C$2 names?
// create an expression, and an equal equation
// add to the systme of equations
ComputerAlgebra.Expression thisCell = formula;
Debug.WriteLine($"parsed expr: {thisCell.ToPrettyString()}");
exprs.Add(thisCell);
//system.Add(Equal.New(thisCell, 0));
}
else if (rng.Value.ToString() != "")
{
// if the cell is not blank process it
Debug.WriteLine($"{rng.Address} is constant");
// create an arrow function for the constant
Arrow thisCell = Arrow.New(rng.Address.Replace("$", ""), rng.Value.ToString());
Debug.Print($"arrow func: {thisCell.ToPrettyString()}");
constants.Add(thisCell);
}
}
// run through the exprs and sub in the arrows (constants)
foreach (var expr in exprs)
{
var evalExpr = expr.Evaluate(constants);
Debug.WriteLine($"eval expr: {expr.ToPrettyString()} \t->\t {evalExpr.ToPrettyString()}");
}
}