Tables and Table Formulas

Order line items, price lists and schedules are tables, and in a Windows Forms TextControl users build them the way they would in a word processor: they insert a table from the ribbon, add and merge rows and columns, set borders and colors, and let cells calculate totals with formulas that work like spreadsheet formulas. Your code does the same through the Tables collection, reacts to the table the user is working in and opens the built-in dialog boxes.

The examples are methods of Form1.cs in the application of Create Your First Windows Forms Application, with a TextControl named textControl1 and a ribbon. Call them from the Click handler of a button or a menu item, and keep the namespace that Visual Studio created around the class. Tables in documents that are built without a user interface, with ServerTextControl, are the subject of Tables and Calculations in Document Processing.

How Tables Are Organized

TextControl.Tables returns the TableCollection of the text part with the input focus: the main text, or the header, footer or text frame the user is editing. Document Object Model explains this rule. A Table has Rows, Columns and Cells, numbered from 1, and the formatting of a cell, a row or a column is a TableCellFormat.

Two things decide which table your code works with:

• An ID. A table that your code adds gets an ID between 10 and 32767 (short.MaxValue), and TableBaseCollection.GetItem returns it by that ID. Tables that users insert or paste have no ID of their own.
• The input position. TableCollection.GetItem without a parameter returns the table that contains the input position, or null. Methods such as Rows.Add work on the row, column or cell at the input position or on the selected cells.

Widths, heights, border widths and distances are measured in twips, 1/1440 inch.

Work With Tables in the Ribbon

The Insert tab of the ribbon inserts a table at the input position: click Table in the Tables group and drag over the grid to choose the number of rows and columns, or click Insert Table... to enter them in a dialog box.

Everything else is on two tabs that work on the table at the input position:

Properties opens the Table Properties dialog box with the tabs Frame and Color, Size and Formatting and Formulas:

The two tabs are usually shown as contextual tabs that appear only while the input position is in a table, like Table Tools in the screenshot. Create a Ribbon Application with Sidebar Support adds this group to a RibbonForm, and Built-in Ribbon Tabs shows other ways to display them. The gridlines that View Gridlines shows or hides are TableCollection.GridLines in code; they mark cell borders on screen and aren't printed.

Insert a Table From Code

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

using System;
using System.Windows.Forms;
using TXTextControl;

public partial class Form1 : Form
{
    private void InsertLineItemTable()
    {
        // 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
        table.Columns.GetItem(1).Width = 3 * 1440;

        // The text screen readers and tagged PDF files use for the table
        table.DescriptiveText = "Line items of the order";
    }
}

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 a number of rows and columns and an ID at the input position. Without parameters, Add opens the Insert Table dialog box instead.
• The cell texts, the header row that is repeated on each page and the column width work as in Fill a Table From Data, which explains Cells.GetItem, IsHeader and Width.
• Table.DescriptiveText is the alternative text of the table, which Accessibility describes.

Borders, background colors and text distances are properties of TableCellFormat, which you assign to a cell, a row or a column. Format Cells, Rows and Columns lists them, and the blog article A Step-by-Step Guide to Formatting Table Cells in TX Text Control Programmatically Using C# goes through the attributes one by one.

Work With the Table at the Input Position

Commands of your own, such as buttons in a custom toolbar, usually act on the table the user is working in. The following methods add a row below the current row, merge the first row into one cell and open the Table Properties dialog box:

using System;
using System.Windows.Forms;
using TXTextControl;

public partial class Form1 : Form
{
    private void AddRowBelow()
    {
        // The table that contains the input position, or null
        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 MergeFirstRow()
    {
        Table table = textControl1.Tables.GetItem();

        if (table == null)
        {
            return;
        }

        // Select row 1 from the first to the last column and merge it
        table.Select(1, 1, 1, table.Columns.Count);

        if (table.CanMergeCells)
        {
            table.MergeCells();
        }
    }

    private void ShowTableProperties()
    {
        // The dialog box works on the table at the input position
        if (textControl1.Tables.GetItem() != null)
        {
            textControl1.TableFormatDialog();
        }
    }
}

The code works as follows:

• Tables.GetItem() returns null when the input position isn't in a table, so each method checks it first. table.Cells.GetItem() without parameters returns the cell with the input position, whose Row and Column tell where the user is.
• TableRowCollection.Add adds rows before or after the row with the input position, with Before or After from TableAddPosition. CanAdd tells whether that is possible. Columns work the same way through Columns.
• Table.Select with a first and last row and column selects a range of cells. MergeCells merges the selection if CanMergeCells is true, and SplitCells splits merged cells again. Split divides the whole table in two.
• TableFormatDialog opens the Table Properties dialog box. An overload takes the zero-based index of the tab to show: 0 for frames and colors, 1 for size and alignment, 2 for formulas and number formats.

To move the input position into a cell from code, set InputPosition. TableCell.Start is one-based and the InputPosition constructor takes a zero-based text position, so pass cell.Start - 1.

To enable such commands only while they can work, check Tables.GetItem() in a handler of the InputPositionChanged event. CanTableFormat tells whether the selected text can get table formatting, which depends on the document's permissions and edit mode.

Tables arrive in the document in other ways too. TableCreated occurs when a table without an ID is pasted or loaded, and TableFormatChanged when the user changes the design of a selected table. Both pass a TableEventArgs with the table. Events lists all events of the editor.

Calculate With Formulas

A table cell can calculate its text from other cells of the same table with a formula, as in a spreadsheet. When the text of a referenced cell changes, the cell with the formula is updated. References to other tables aren't possible.

Users write formulas on the Formulas tab. They place the input position in a cell, choose a function from Functions and add it to the Formula box, type the cell range, for example SUM(B1:B4), and confirm it with the check mark next to the box. Select Cell References picks the range with the mouse instead. The Number Format group sets how the result is displayed and whether the cell text is a number, and Formula Settings switches the calculation on and off, chooses the reference style and shows the references of a formula.

The following method fills the prices of the table from the first example and adds a row with their sum:

using System;
using System.Windows.Forms;
using TXTextControl;

public partial class Form1 : Form
{
    private void AddTotal()
    {
        // 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;
    }
}

The code works as follows:

• FormulaReferenceStyle sets how formulas address cells: A1 with a column letter and a row number, R1C1 with a row and a column number.
• IsFormulaCalculationEnabled turns on the calculation that updates formula cells when an input cell changes.
• TableCellFormat.TextType with Number makes the text of a cell a number that formulas can calculate with. The text is read with the period as decimal separator and the comma as thousands separator, which is why the prices in the code are written 199.99. NumberFormat #,##0.00 shows the result with a thousands separator and two decimal places.
• TableCell.Formula sets the formula of the total cell.
• RulerBar.FormulaMode Auto shows the cell references in the ruler bar while the input position is in a cell with a formula. rulerBar1 is the horizontal ruler on your form, and Rulers and Status Bar describes the other modes.

Run the form, insert the table and add the total: the cell shows 349.47. Change a price, and the total follows. On screen, the separators are those of the system language, as Number Formats explains.

Check a Formula the User Enters

A formula with a syntax error throws a FormulaException, whose CharacterIndex is the one-based position of the first invalid character. An invalid number format throws a NumberFormatException in the same way. When a text box of your own takes formulas, catch the exception:

using System;
using System.Windows.Forms;
using TXTextControl;

public partial class Form1 : Form
{
    private void ApplyFormula(string formula)
    {
        Table table = textControl1.Tables.GetItem();
        TableCell cell = table?.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", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

The Spreadsheet how-to of the samples that the setup installs, in Howto\Spreadsheet, builds a formula bar this way, with a function list, a formula box, the reference style and the number format above the editor; see Document Editor Examples.

The user selects a function from the function list and clicks Add function:

The sample then asks for the cell range of the formula and passes the text the user typed to Formula, catching the exception as above:

Excel formulas in tables are part of the Enterprise and Professional editions; see Editions and Feature Availability.

Formula Syntax

Formulas follow the syntax of SpreadsheetML formulas in Office Open XML (ISO/IEC 29500-1, section 18.17.2), which spreadsheet applications such as Microsoft Excel use as well. A formula has 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, written in R1C1 style:

1. 2 is a numerical constant.
2. ^ raises the value of the referenced cell to the power of 2, and * multiplies the result by π.
3. PI() is a function that returns π, 3.14159265...
4. R[-2]C[-1] is a cell reference: the value of the cell two rows up and one column to the left of the formula cell.

Constants

A constant is a value written into the formula itself. Formulas know five kinds: numerical, logical, string, error and array constants.

An array constant is a matrix of numbers, logical values and strings. Commas separate the values of a row and semicolons separate the rows, so {1;2;3} is a column of three values. Strings can be compared: "Two"="One" returns FALSE, and "FALSE"="FALSE" returns TRUE.

Operators

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

Functions

A function starts with its name in uppercase letters, followed by its arguments in parentheses, separated by commas. The parentheses are there even when the function takes no argument, as in PI():

An argument is a number, a logical value, a string or an array. It can be written as a constant, taken from a cell reference or a range, or be the result of another function. A function returns a number, a logical value, a string or an array of them.

Formulas can use 85 mathematical and trigonometric, statistical, logical, lookup and reference and information functions. Formula Functions lists each one with its arguments and defaults, the limits of functions such as COUNT, which doesn't count dates, and the Excel functions of loaded worksheets. TableCollection.SupportedFormulaFunctions returns their names at runtime.

Cell References

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

R1C1 style. R with a row number and C with a column number address a cell. A number in square brackets is relative: it counts the rows and columns from the formula cell, with negative numbers going up and to the left and [0] staying in the same row or column. Text Control recommends relative references. For a formula in row 4 and column 3, the references below point at these cells of the table:

A number without brackets is absolute: R5C4 is the cell in row 5 and column 4, wherever the formula is. Absolute references work, but when the table is edited or its cells are pasted as a new table, they return wrong or invalid values. Use relative references in tables that users edit.

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, the same as a relative R1C1 reference. A $ before the column, the row or both makes that part absolute: $B$3, $B3 or B$3.

Ranges. Two references separated by a colon form a range from the upper left to the lower right cell, which is how a function such as SUM takes many cells at once. In a formula in row 3 of column 3, the ranges below add up the numbered areas:

In A1 style, the first range is SUM(A2:B3).

LEFT, ABOVE, RIGHT and BELOW stand for all cells next to the formula cell in one direction:

Number Formats

A number format decides how the number of a cell is displayed, whether the user typed it, your code set it or a formula calculated it. It applies when TableCellFormat.TextType is Number, and TableCellFormat.NumberFormat holds the format string. Users choose one in the Number Format group of the Formulas tab.

Without a format, NumberFormat is an empty string and a number is shown as it is: a whole number without decimal places and a decimal number with all of its decimal places. A format string is made of the placeholders # and 0, the separators . and ,, the percent sign % and other characters or text, as described in Office Open XML (ISO/IEC 29500-1, section 18.8.31). TableCollection.SupportedNumberFormats returns a list of common formats, the same list that the Number Format box offers.

Decimal and Thousands Separators

In the text of a cell whose type is Number, and in a format string, the period is the decimal separator and the comma the thousands separator, whatever the language of the system. The number on screen uses the separators of the system language instead: on a German system, the decimal separator is displayed as a comma and the thousands separator as a period. Your code writes 12.5 and #,##0.00, and a German user sees 12,50.

Placeholders and Decimal Places

# and 0 stand for the digits of the number. # shows only the digits the number has, and 0 shows a zero where the number has no digit:

A period after the placeholders shows decimal places. The number of placeholders after it is the maximum number of decimal places, and a 0 there shows the place even when it is zero. Without a period, the number is rounded to an integer:

Thousands Separator and Scaling

A comma with placeholders after it inserts a thousands separator after every third digit. A comma with no placeholder after it divides the number by 1,000 instead, once for each such comma, so that large amounts are shown in thousands or millions:

Percentages

A % together with one or more placeholders multiplies the number by 100 and shows the percent sign where it stands in the format, before or after the digits:

Symbols and Text

A number can be combined with characters and text in three ways:

• Characters written as they are: $, -, +, /, (, ), :, !, ^, &, ', ~, {, }, <, >, = and the space.
• Any other single character after a backslash, such as a currency symbol, a letter, a double quote or a digit that isn't a placeholder, as in \3.
• Words and longer text in double quotes.

An invalid format string throws a NumberFormatException, which Calculate With Formulas introduces together with FormulaException. The blog article Formatting Numbers in Table Cells shows number formats while the user types.

Remove a Table

TableBaseCollection.Remove removes the table with an ID, and TableCollection.Remove without a parameter the table at the input position. In the ribbon, Delete on the Table Layout tab offers Delete Cells, Delete Columns, Delete Rows and Delete Table.

Next Steps

• Text and Paragraph Formatting: format the text inside table cells.
• Merge Blocks: repeat a table row for each record in a template.
• Sections and Page Layout: give a wide table a page in landscape orientation.
• Tables and Calculations: create and read tables with ServerTextControl.