Adding formulas to table cells

A core functionality of spreadsheets is the possibility to add functionality to a cell. This enables users to add a specific formula to a cell. The value of this cell is calculated based on this formula and the values of other referenced cells. In this sample, a basic sum function is implemented.

Each TableCell has text that is displayed and additional data that is stored in the TableCell.Name property.

Type in numeric values into cell B1 and B2. Now, select cell B3 and click on the Sum button.

A sum function is inserted automatically that totalizes the values of the above cells.

An identifier and the specific formula with the connected cells are stored in the TableCell.Name property:

[C#]
private const string FormulaIdentifier = "_tx_formula_";

private void CreateSumFormula(TXTextControl.TableCell tableCell)
{
    tableCell.Text = string.Empty;
    tableCell.Name 
        = FormulaIdentifier + ",sum,2,"             // Identifier string, formula type ("sum") and first row (2)
        + (tableCell.Row - 1).ToString() + ","      // Last row
        + tableCell.Column.ToString();              // Column

    UpdateSumCells();
}
[Visual Basic]
Private Const FormulaIdentifier As String = "_tx_formula_"

Private Sub CreateSumFormula(ByVal tableCell As TXTextControl.TableCell)
    tableCell.Text = String.Empty
    ' Identifier string, formula type ("sum") and first row (2)
    ' Last row
    tableCell.Name = FormulaIdentifier & ",sum,2," & (tableCell.Row - 1).ToString() & "," & tableCell.Column.ToString()
    ' Column
    UpdateSumCells()
End Sub

On updating the results, the UpdateSumCells method loops through all cells looking for stored formulas. If a formula is found, the sum of the cells, which are part of the cell range defined in the formula is calculated and used as the TableCell.Text of this cell.

[C#]
private void UpdateSumCells(TXTextControl.Table table)
{
    foreach (TXTextControl.TableCell cell in table.Cells)
    {
        if (!cell.Name.StartsWith(FormulaIdentifier)) continue;

        string[] splitValues = cell.Name.Split(',');
        if ((splitValues.Length < 5) || (splitValues[1] != "sum")) continue;

        int sum = 0, column = Convert.ToInt32(splitValues[4]);

        for (int row = Convert.ToInt32(splitValues[2]); row <= Convert.ToInt32(splitValues[3]); row++)
        {
            int number;
            Int32.TryParse(textControl.Tables.GetItem().Cells.GetItem(row, column).Text, out number);

            sum += number;
        }

        cell.Text = sum.ToString();
    }
}
[Visual Basic]
Private Sub UpdateSumCells(ByVal table As TXTextControl.Table)
    For Each cell As TXTextControl.TableCell In table.Cells
        If Not cell.Name.StartsWith(FormulaIdentifier) Then
            Continue For
        End If


        Dim splitValues As String() = cell.Name.Split(","c)
        If (splitValues.Length < 5) OrElse (splitValues(1) <> "sum") Then
            Continue For
        End If


        Dim sum As Integer = 0, column As Integer = Convert.ToInt32(splitValues(4))

        For row As Integer = Convert.ToInt32(splitValues(2)) To Convert.ToInt32(splitValues(3))
            Dim number As Integer
            Int32.TryParse(TextControl1.Tables.GetItem().Cells.GetItem(row, column).Text, number)

            sum += number
        Next

        cell.Text = sum.ToString()
    Next
End Sub

<< Creating a spreadsheet interface using tables