Tables and Table Formulas

In a WPF word processor built with TX Text Control, a table is inserted from the Insert tab and changed on the Table Layout tab, and its cells can add up, average or compare other cells with formulas that follow the syntax of spreadsheet applications. The same tables are open to your code through TextControl.Tables: it inserts and fills them, acts on the table the user is working in and switches on the calculation of formulas.

The examples are event handlers in MainWindow.xaml.cs of a window with a TextControl named textControl1, as in Create Your First WPF Application. Connect them to buttons or menu items of your window; replace SampleApp with the namespace of your project. Tables in documents processed without a user interface are the subject of Tables and Calculations in Document Processing.

How Tables Are Organized

TextControl.Tables returns the TableCollection of the main text, while headers, footers and text frames have their own. The table classes are those of the TXTextControl namespace, the same as in the Windows Forms product: a Table has Rows, Columns and Cells, numbered from 1, and a TableCellFormat holds the borders, colors and number format of a cell, a row or a column.

Your code finds a table in one of two ways:

• By its ID. A table your code adds gets an ID from 10 to 32767 (short.MaxValue), and TableBaseCollection.GetItem with that ID returns it. Tables inserted or pasted by users have no ID.
• By the input position. TableCollection.GetItem without a parameter returns the table that contains the input position, or null.

Widths, heights and border widths of tables are measured in twips, 1/1440 inch, not in the device-independent units of WPF.

Work With Tables in the Ribbon

On the Insert tab, Table in the Tables group inserts a table at the input position: users drag over a grid to choose its size, or click Insert Table... to type the number of rows and columns.

Two tabs change the table that contains the input position:

A WPF ribbon tab works with the editor once the TextControl names it: RibbonTableLayoutTab and RibbonFormulaTab of the TextControl hold the x:Name of the tabs. The tutorial Create a WPF Ribbon Application adds both as a contextual Table Tools group, which the WPF version of TX Text Control Words shows while the input position is in a table:

Properties on the Table Layout tab opens the Table Properties dialog box with the tabs Frame and Color, Size and Formatting and Formulas. Connecting Ribbon Tabs in XAML explains the connections by name, and View Gridlines is TableCollection.GridLines in code: lines that mark the cells on screen and aren't printed.

Insert a Table From Code

The following handler inserts the line item table of an order, with a heading row that is repeated on every page:

using System.Windows;
using TXTextControl;

namespace SampleApp;

public partial class MainWindow : Window
{
    private void InsertTable_Click(object sender, RoutedEventArgs e)
    {
        // No table can be added at the input position, or the text there is protected
        if (!textControl1.Tables.CanAdd || !textControl1.CanEdit)
        {
            return;
        }

        // A heading row and three item rows in three columns, with the ID 10
        textControl1.Tables.Add(4, 3, 10);
        Table table = textControl1.Tables.GetItem(10);

        string[] headings = { "Description", "Quantity", "Price" };

        for (int column = 1; column <= headings.Length; column++)
        {
            table.Cells.GetItem(1, column).Text = headings[column - 1];
        }

        // Repeat the first row at the top of each page
        table.Rows.GetItem(1).IsHeader = true;

        // The first column is 3 inches wide, in twips
        table.Columns.GetItem(1).Width = 3 * 1440;

        table.DescriptiveText = "Line items of the order";

        // Give the keyboard focus back to the editor
        textControl1.Focus();
    }
}

The code works as follows:

• TableCollection.CanAdd tells whether a table can be inserted at the input position. It doesn't check protected text fields and document sections; CanEdit does.
• TableCollection.Add inserts a table with the given rows, columns and ID. Called without parameters, it opens the Insert Table dialog box.
• TableCellCollection.GetItem returns a cell by row and column, and TableCell.Text sets its text.
• TableRow.IsHeader repeats the row at the top of every page the table extends over.
• TableColumn.Width sets the width of a column, and Table.DescriptiveText the alternative text that screen readers and tagged PDF files use; see Accessibility and UI Automation.
• A click on a WPF button takes the keyboard focus. textControl1.Focus() returns it to the editor, so the user can type into the new table right away.

Tables and Calculations sets borders, colors and text distances through TableCellFormat.

Work With the Table at the Input Position

Buttons of your own for table commands should be enabled only while the input position is in a table. The TextControl raises InputPositionChanged whenever the input position moves. Declare the buttons and the event in MainWindow.xaml, where the prefix WPF stands for the namespace TXTextControl.WPF:

<DockPanel>
    <StackPanel DockPanel.Dock="Top" Orientation="Horizontal">
        <Button x:Name="addRowButton" Content="Add Row" Click="AddRow_Click" />
        <Button x:Name="tablePropertiesButton" Content="Table Properties" Click="TableProperties_Click" />
    </StackPanel>
    <WPF:TextControl x:Name="textControl1"
                    InputPositionChanged="TextControl1_InputPositionChanged" />
</DockPanel>

The handlers in MainWindow.xaml.cs enable the buttons, add a row below the current row and open the Table Properties dialog box:

using System;
using System.Windows;
using TXTextControl;

namespace SampleApp;

public partial class MainWindow : Window
{
    private void TextControl1_InputPositionChanged(object sender, EventArgs e)
    {
        // Enable the table commands only while the input position is in a table
        bool inTable = textControl1.Tables.GetItem() != null;
        addRowButton.IsEnabled = inTable;
        tablePropertiesButton.IsEnabled = inTable;
    }

    private void AddRow_Click(object sender, RoutedEventArgs e)
    {
        Table table = textControl1.Tables.GetItem();

        if (table != null && table.Rows.CanAdd)
        {
            // One row after the row with the input position
            table.Rows.Add(TableAddPosition.After, 1);
        }
    }

    private void TableProperties_Click(object sender, RoutedEventArgs e)
    {
        if (textControl1.Tables.GetItem() != null)
        {
            textControl1.TableFormatDialog();
        }
    }
}

The code works as follows:

• InputPositionChanged passes a plain EventArgs, not a RoutedEventArgs. The handler checks with Tables.GetItem() whether there is a table at the input position.
• TableRowCollection.Add adds rows before or after the row with the input position, with Before or After from TableAddPosition, when CanAdd is true. Columns has the same methods for columns.
• TableFormatDialog opens the Table Properties dialog box for the table at the input position. An overload takes the zero-based tab: 0 for frames and colors, 1 for size and alignment, 2 for formulas and number formats.

To merge cells, select them with Table.Select, which takes a first and a last row and column, and call MergeCells when CanMergeCells is true. SplitCells splits merged cells, and Split divides the table in two. CanTableFormat tells whether the selected text can get table formatting, which depends on the permissions and the edit mode of the document.

TableCreated occurs when a table without an ID is pasted or loaded; Events lists the other table events.

Calculate With Formulas

A table cell can hold a formula that calculates its text from other cells of the same table. When an input cell changes, the formula cell is updated; references to other tables aren't possible.

On the Formulas tab, users place the input position in a cell, choose a function from Functions, add it to the Formula box and complete the range, such as SUM(B1:B4), then confirm with the check mark. Select Cell References picks the range with the mouse. Number Format sets how the result is displayed and whether the cell text is a number, and Formula Settings switches the calculation on or off, selects the A1 or R1C1 reference style and shows the references of a formula.

The following handlers fill the prices of the table from the first example, add a total row and apply a formula typed into a text box of your own:

using System.Windows;
using TXTextControl;

namespace SampleApp;

public partial class MainWindow : Window
{
    private void AddTotal_Click(object sender, RoutedEventArgs e)
    {
        // Address cells with a column letter and a row number, such as C2
        textControl1.FormulaReferenceStyle = FormulaReferenceStyle.A1;
        textControl1.IsFormulaCalculationEnabled = true;

        Table table = textControl1.Tables.GetItem(10);

        if (table == null)
        {
            return;
        }

        // Numbers with two decimal places
        TableCellFormat amountFormat = new TableCellFormat()
        {
            TextType = TextType.Number,
            NumberFormat = "#,##0.00"
        };

        string[] prices = { "199.99", "99.98", "49.50" };

        for (int row = 2; row <= 4; row++)
        {
            TableCell price = table.Cells.GetItem(row, 3);
            price.CellFormat = amountFormat;
            price.Text = prices[row - 2];
        }

        // A row for the total below the last row
        table.Rows.GetItem(4).Select();
        table.Rows.Add(TableAddPosition.After, 1);

        table.Cells.GetItem(5, 1).Text = "Total";

        TableCell total = table.Cells.GetItem(5, 3);
        total.CellFormat = amountFormat;
        total.Formula = "SUM(C2:C4)";

        // Show cell references in the ruler bar while a formula cell is edited
        rulerBar1.FormulaMode = RulerBarFormulaMode.Auto;
    }

    private void ApplyFormula(string formula)
    {
        TableCell cell = textControl1.Tables.GetItem()?.Cells.GetItem();

        if (cell == null)
        {
            return;
        }

        try
        {
            cell.Formula = formula;
        }
        catch (FormulaException exc)
        {
            MessageBox.Show($"The formula is invalid at character {exc.CharacterIndex}.",
                "Formula Error", MessageBoxButton.OK, MessageBoxImage.Error);
        }
    }
}

The code works as follows:

• FormulaReferenceStyle sets how formulas address cells: A1 with a column letter and a row number, R1C1 with row and column numbers.
• IsFormulaCalculationEnabled turns on the automatic calculation.
• TableCellFormat.TextType Number makes the cell text a number that formulas calculate with. In that text and in the format string, the period is always the decimal separator and the comma the thousands separator. NumberFormat #,##0.00 displays it with two decimal places.
• TableCell.Formula sets the formula. A syntax error throws a FormulaException with the one-based CharacterIndex of the first invalid character; an invalid number format throws a NumberFormatException.
• RulerBar.FormulaMode shows cell references in the horizontal RulerBar named rulerBar1: Auto in cells with a formula, Always in every cell, Fixed relative to the cell where the mode was switched on, and None, the default, never.

The total cell shows 349.47 and follows every change of a price. The displayed number uses the separators of the system language, as described in Number Formats. The Spreadsheet sample in Document Editor Examples adds a formula bar to a WPF window this way.

Formula Syntax

Formulas follow the syntax of SpreadsheetML (Office Open XML, ISO/IEC 29500-1, section 18.17.2), which Microsoft Excel and other spreadsheet applications use too. A formula is built from four kinds of elements: constants, cell references and functions stand for values, and operators combine them. In the formula PI()*R[-2]C[-1]^2, the cell two rows up and one column to the left is raised to the power of 2 and multiplied by π:

Constants

A constant is a value written into the formula: a number, a logical value, a string, an error value or an array.

In an array constant, commas separate the values of a row and semicolons separate the rows: {1;2;3} is a column of three values, and SUM({1;2;3}) returns 6. An array holds numbers, logical values and strings. Strings can be compared as well: "Two"="One" returns FALSE, and "FALSE"="FALSE" returns TRUE.

Operators

Arithmetic operators return a number, comparison operators return TRUE or FALSE, and the text operator joins strings:

Functions

A function starts with its uppercase name, followed by its arguments in parentheses, separated by commas. The parentheses are required even without an argument, as in PI(). An argument is a number, a logical value, a string or an array, written as a constant, given as a cell reference or range, or returned by another function. The result of a function is a number, a logical value, a string or an array.

The functions come from the mathematical and trigonometric, statistical, logical, lookup and reference and information functions of SpreadsheetML. Formula Functions lists all 85 with their arguments, defaults and limits, such as COUNT, which doesn't count dates. TableCollection.SupportedFormulaFunctions returns their names at runtime, for example to fill a drop-down list.

Cell References

FormulaReferenceStyle decides whether a formula addresses other cells of its table in A1 or in R1C1 style.

A1 style. A column letter and a row number address a cell. The columns are named A to Z, then AA to AZ, BA to BZ and so on, so B3 is the cell in the second column of the third row. A plain A1 reference is relative to the formula cell, the same as a relative R1C1 reference. A $ before the column, the row or both makes that part absolute, independent of where the formula is: $B$3, $B3 or B$3.

R1C1 style. R with a row number and C with a column number address a cell. In square brackets, the numbers are relative: the number of rows and columns from the formula cell, negative up and to the left, [0] in the same row or column. Without brackets, as in R5C4, they are absolute. For a formula in row 4 and column 3, the relative references point at these cells:

Text Control recommends relative references, because absolute ones return wrong or invalid values once the table is edited or its cells are pasted as a new table.

A range consists of the upper left and the lower right cell, separated by a colon. For a formula in row 3, column 3, the four ranges below cover the numbered areas:

LEFT, ABOVE, RIGHT and BELOW refer to all cells in one direction from the formula cell, as in SUM(ABOVE):

Number Formats

A number format decides how the number of a cell is displayed, whether a user typed it, your code set it or a formula calculated it. It applies when TextType is Number. Without a format, NumberFormat is an empty string: a whole number is shown without decimal places, and a decimal number with all of its decimal places.

A format is made of the placeholders # and 0, the separators . and ,, the percent sign % and other characters or text. The rules:

The period and the comma of a format string mean the decimal and the thousands separator in every language. The displayed number uses the separators of the system language: on a German system, the decimal separator is shown as a comma and the thousands separator as a period.

These characters can be written into a format as they are: $, -, +, /, (, ), :, !, ^, &, ', ~, {, }, <, >, = and the space. Any other single character, such as a currency symbol, a letter or a digit that isn't a placeholder, needs a backslash before it. Words go in double quotes:

TableCollection.SupportedNumberFormats lists common formats, the list of the Number Format box. The blog article Formatting Numbers in Table Cells shows more of them.

Remove a Table

TableBaseCollection.Remove removes a table by its ID, and TableCollection.Remove without a parameter the table at the input position. Users choose Delete Cells, Delete Columns, Delete Rows or Delete Table from Delete on the Table Layout tab.

Next Steps

• Text and Paragraph Formatting: format the text in table cells.
• Merge Blocks: repeat a table row for each record of your data.
• Sections and Page Layout: put a wide table on a landscape page.
• Tables and Calculations: tables in code without a user interface.