C# - Calculations as objects - Many Calculations with Many Dependencies

user_13036322 0
Viewed 398

I have developed a fairly complex spreadsheet in Excel, and I am tasked with converting it to a C# program.

What I am trying to figure out is how to represent the calculations from my spreadsheet in C#.

The calculations have many dependencies, to the point that it would almost appear to be a web, rather than a nice neat hierarchy.

The design solution I can think of is this:

  • Create an object to represent each calculation.

  • Each object has an integer or double, which contains the calculation. this calc has inputs from other objects and so requires that they are evaluated first before it can be performed.

  • Each object has a second integer "completed", which evaluates to 1 if the previous calculation is successful

  • Each object has a third integer "ready" This item requires all precedent object's "completed" integers evaluate to "1" and if not, the loop skips this object
  • A Loop runs through all objects, until all of the "completed" integers = 1

I hope this makes sense. I am typing up the code for this but I am still pretty green with C# so at least knowing i'm on the right track is a boon :) To clarify, this is a design query, I'm simply looking for someone more experienced with C# than myself, to verify that my method is sensible.

I appreciate any help with this issue, and I'm keen to hear your thoughts! :)

edit*

I believe the "completed" state and "ready" state are required for the loop state check to prevent errors that might occur from attempts to evaluate a calculation where precedents aren't evaluated. Is this necessary?

I have it set to "Any CPU", the default setting.

edit*

For example, one object would be a line "V_dist" It has length, as a property. It's length "V_dist.calc_formula" is calculated from two other objects "hpc*Tan(dang)"

public class inputs
{
    public string input_name;
    public int input_angle;
    public int input_length;
}
public class calculations
{
    public string calc_name; ///calculation name
    public string calc_formula; ///this is just a string containing formula
    public double calculationdoub; ///this is the calculation
    public int completed; ///this will be set to 1 when "calculationdoub" is nonzero
    public int ready; ///this will be set to 1 when dependent object's "completed" property = 1
}
public class Program
{
    public static void Main()
    {
        ///Horizontal Length
        inputs hpc = new inputs();
        hpc.input_name = "Horizontal "P" Length";
        hpc.input_angle = 0;
        hpc.input_length = 200000;

        ///Discharge Angle
        inputs dang = new inputs();
        dang.input_name = "Discharge Angle";
        dang.input_angle = 12;
        dang.input_length = 0;

        ///First calculation object
        calculations V_dist = new calculations();
        V_dist.calc_name = "Vertical distance using discharge angle";
        V_dist.calc_formula = "hpc*Tan(dang)";
        **V_dist.calculationdoub = inputs.hpc.length * Math.Tan(inputs.dang.input_angle);**
        V_dist.completed = 0;
        V_dist.ready = 0;
    }
}

It should be noted that the other features I have yet to add, such as the loop, and the logic controlling the two boolean properties

pseudocode excel c#
1 Answers

You have some good ideas, but if I understand what you are trying to do, I think there is a more idiomatic -- more OOP way to solve this that is also much less complicated. I am presupposing you have a standard spreadsheet, where there are many rows on the spreadsheet that all effectively have the same columns. It may also be you have different columns in different sections of the spreadsheet.

I've converted several spreadsheets to applications, and I have settled on this approach. I think you will love it.

For each set of headers, I would model that as a single object class. Each column would be a property of the class, and each row would be one object instance.

In all but very rare cases, I would say simply model your properties to include the calculations. A simplistic example of a box would be something like this:

public class Box
{
    public double Length { get; set; }
    public double Width { get; set; }
    public double Height { get; set; }

    public double Area
    {
        get { return 2*Height*Width + 2*Length*Height + 2*Length*Width; }
    }

    public double Volume
    {
        get { return Length * Width * Height; }
    }
}

And the idea here is if there are properties (columns in Excel) that use other calculated properties/columns as input, just use the property itself:

public bool IsHuge
{
    get { return Volume > 50; }
}

.NET will handle all of the heavy lifting and dependencies for you.

In most cases, this will FLY in C# compared to Excel, and I don't think you'll have to worry about computational speed in the way you've set up your cascading objects.

When I said all but rare cases, if you have properties that are very computationally expensive, then you can make these properties private and then trigger the calculations.

public class Box
{
    public double Length { get; set; }
    public double Width { get; set; }
    public double Height { get; set; }

    public double Area { get; private set; }
    public double Volume { get; private set; }
    public bool IsHuge { get; private set; }

    public void Calculate()
    {
        Area = 2*Height*Width + 2*Length*Height + 2*Length*Width;
        Volume = Length * Width * Height;
        IsHuge = Volume > 50;
    }
}

Before you go down this path, I'd recommend you do performance testing. Unless you have millions of rows and/or very complex calculations, I doubt this second approach would be worthwhile, and you have the benefit of not needing to define when to calculate. It happens when, and only when, the property is accessed.

user_10018840
Related