Howto: Use Spreadsheet Formulas in Tables

This chapter explains how to use Excel compatible formulas in Text Control tables.

The source code for this example is contained in the following directories:

• %USERPROFILE%\Documents\TX Text Control 32.0.NET for WPF\CSharp\Howto\Spreadsheet
• %USERPROFILE%\Documents\TX Text Control 32.0.NET for WPF\VB.NET\Howto\Spreadsheet

Used TX Text Control controls:

• TXTextControl.WPF.TextControl

Relevant API links:

• TableCollection

Adding formulas to table cells

Consider a value of a table cell that is calculated dynamically based on other table cell values. Typically, a SUM formula is inserted at the end of a table to create a total sum value. Or a tax value that is calculated based on a tax percentage value and the calculated total sum.

TX Text Control supports 60+ formulas including most mathematical and trigonometric formulas compatible to Microsoft Excel. After starting the sample, a document is loaded that contains a table showing a cash flow calculation.

Now, set the input position into any table cell with a number that doesn't have a green background and change it. Watch the cells with a green background changing automatically based on the formula calculation.

In order to insert a new formula into a table cell, set the input position into any cell. Then select the formula function from the function dropdown box:

Then click the Add function button right next to the dropdown.

Now, type in the range of your formula. For example: SUM(B1:B4). Confirm the formula by clicking the check button right next to the formula text box. In the code, this string is simply passed to the TableCell.Formula property:

• C#
• VB.NET

​private void ApplyFormula() {
        try {
                // Get the current table cell.
                TXTextControl.Table tblCurrentTable = m_txTextControl.Tables.GetItem();
                if (tblCurrentTable != null) {
                        TXTextControl.TableCell tclCurrentTableCell = tblCurrentTable.Cells.GetItem();
                        if (tclCurrentTableCell != null) {
                                // Apply a formula to the current table cell.
                                tclCurrentTableCell.Formula = m_tbxFormula.Text;
                        }
                }
        } catch (Exception exc) {
                // Let TXTextControl do the validation.
                MessageBox.Show(exc.Message, "Formula Error", MessageBoxButton.OK, MessageBoxImage.Error);
        }
}

​Private Sub ApplyFormula()
        Try
                ' Get the current table cell.
                Dim tblCurrentTable As TXTextControl.Table = Me.m_txTextControl.Tables.GetItem()
                If tblCurrentTable IsNot Nothing Then
                        Dim tclCurrentTableCell As TXTextControl.TableCell = tblCurrentTable.Cells.GetItem()
                        If tclCurrentTableCell IsNot Nothing Then
                                ' Apply a formula to the current table cell.
                                tclCurrentTableCell.Formula = Me.m_tbxFormula.Text
                        End If
                End If
        Catch exc As Exception
                ' Let TXTextControl do the validation.
                MessageBox.Show(exc.Message, "Formula Error", MessageBoxButton.OK, MessageBoxImage.Error)
        End Try
End Sub

When changing the values of the referenced table cells, you can see the value changing automatically at your formula cell.