Storing a Formula, Not the Result

[waxman]

Hello all,

I'm working on a basic spreadsheet application, and I am trying to figure out how to store a formula in an array cell, and not the result it gives. The formula is the operation done on two other cells in the array, and is stored in a third cell. For instance, cell S[0][0] could hold S[0][1] + S[0][2]. I would like the formula "S[0][1] + S[0][2]" stored instead of "10" or "3.7" because I would like the values at S[0][1] and/or S[0][2] to be able to be changed, thus affecting the result stored in S[0][0].

I hope that didn't make anyone cross-eyed.

The code I'm working with is below.

Tips: My array is float S[7][6], and 'o' holds either +, -, * or /

        //holding cell values for arithmetic
        z = S[i][j];	
	x = S[k][l];
	y = S[m][p];

	//performing arithmatic
        /*this is where I want the array to hold the formula
        instead of just the result*/
	if (o == '+')
             z = x + y;
	else if (o == '-')
             z = x - y;
	else if (o == '*')
             z = x * y;
	else if ((o == '/') && (y != 0))
             z = x / y;
	else if ((o == '/') && (y == 0))	{
	     cout << "Cannot divide by zero. Please try again." << endl;
	     break;
	}
	else    {
             cout << "Invalid operation. Please try again." << endl;
                  return 0;
	}

	//display the changed array
	S[i][j] = z;
	displayGrid(S);

Any help you can give would be greatly appreciated.

Thank you!

[jsmith]

That is a very complicated request... are you sure you want to do this?

[hannes]

You can just store your formula as an std::string or char*.I don't know an ohter way to do it.

hannes

[jsmith]

There are other ways which involve expression templates that implement lazy evaluation. However, that is a very,
very, very advanced programming technique that requires a high degree of competence in template programming.

Having said that, storing a formula works too, it just means that OP has to implement an expression parser and
evaluator, which is also not trivial, but perhaps not as hard as the expression template solution.

[Abramus]

I agree with jsmith that in general this is quite complex problem, but with some assumptions it can be made much simplier.

Assumption I'm thinking of is that each formula must contain exactly one basic operator and two arguments. What we need to do then is to parse the string representing this formula and store somehere:
- positions of cells that are arguments of the operation,
- pointer to a function that implements the operation.

Parsing itself should be trivial as we know that string representing the formula has very limited format of "argument operation argument".

[Abramus]

@jsmith
Expression templates can only be used if formulas are known at compile time, what obiously is not true in case of a "real" spreadsheet applications. Or maybe we think about somethig different?

[waxman]

Hi all,

Thanks for the suggestions! Considering I'm still pretty new at this, I'm not sure I want to take on any "very, very very advanced" programming techniques. (I'm having enough trouble with intermediate-difficulty stuff!)

@Abramus is correct in the assumption that every cell formula is stored identically - cell, operator, cell. Would your suggestion mean that I would have to create an array of strings as opposed to an array of floats? Also, how would I then handle cells that just contain a static value?

Random thought: Would creating an array of Structs be an option? Each struct could hold two cell values and an operator. When the function reads into the struct, it could pull out the information, perform the operation, and store the result in a 'result' array. Not sure how well that would work (if at all), it was just an idea I had.

Thank you all again for the ideas.

[Abramus]

The simplest, and not really efficient (but working) solution:
Store contents of each cell as a string.
If you want to get value of a cell, then first decide if it contains a formula or not (for example, if the first character of the string is a letter then this string contains formula).
To parse a cell that contains a number you can use for example std::stringstream.
To parse a cell that contains a formula you must write your own function.
If any of formula’s arguments is a formula itself, you must calculate value of this internal formula first (use recursion).

void parse_formula(const std::string & formula, operation_type & operation, int & x_col, int & x_row, int & y_col, int & y_row)
{
   // This function should parse <formula>, and then set <operation>, <x_col>, <x_row>, <y_col>, and <y_row>
   // Implementation of this function depends on the assumed format of a formula
}

float get_cell_value(int col, int row)
{
   const std::string & str = S[col][row];

   if(std::isalpha(str[0])) // formula
   {
      int x_col, x_row, y_col, y_row;
      operation_type op;
      parse_formula(str, op, x_col, x_row, y_col, y_row);

      float x = get_cell_value(x_col, x_row);
      float y = get_cell_value(y_col, y_row);

      switch(op)
      {
         case operation_addition:     return x + y;
         case operation_substraction: return x - y;
         ...
      }
   }
   else // number
   {
      std::stringstream buf(str);
      float num;
      buf >> num;
      return num;
   }
}

[waxman]

Thanks so much for the help everyone! After much wailing and gnashing of teeth, I figured out a solution involving structs! Each struct took the form:

struct Cell
	{
		char k;
		char l;
		char m;
		char p;
		char o;
		float staticVal;
		float displayVal;
	};

Where k & l were the column & row of one operand, m & p were the other operand, and o was the operator. If the user just entered a direct value (ie 35) it would go into staticVal for direct display.

I then wrote a "calculate" class that performed the operation by grabbing the info from each struct cell in the array and storing the result in displayVal.

If you're really that interested, I can give you the full code, but it's a few hundred lines of pain.

Once again, thank you for all the help! I might be by again for future challenges.

-waxman

[jsmith]

I would strongly recommend that you use more meaningful names for the members of the struct.
What's wrong with "lhs_row", "lhs_col", "rhs_row", "rhs_col", and "operation"?

For that matter, do this:

struct CellName {
    CellName() : row(), column() {}
    CellName( int r, int c ) : row( r ), column( c ) {}
    int row;
    int column;
};

enum Operation { Op_None, Op_Add, Op_Sub, Op_Mul, Op_Div };

struct CellValueOrFormula {
    explicit CellValueOrFormula( double fixedVal ) : lhsOfFormula(), operation( Op_None ),
        rhsOfFormula(), fixedValue( fixedVal ) {}
    CellValueOrFormula( const CellName& lhs, Operation op, const CellName& rhs ) :
        lhsOfFormula( lhs ), operation( op ), rhsOfFormula( rhs ), fixedValue() {}

    CellName lhsOfFormula;
    Operation operation;
    CellName rhsOfFormula;
    double     fixedValue;
};

This is much more self-documenting than k, l, m, o, and p.