Tables

The table tools of the ribbon aren't the only way to work with tables: your application can insert, change and remove tables in the Document Editor as well. With the JavaScript API, it gets a table by its ID or at the input position, sets the text and formatting of cells, adds rows and columns, merges cells, calculates cells with formulas and describes a table for screen readers.

The examples build on the ASP.NET Core tutorial, whose Insert Table button adds a table with 5 rows, 5 columns and the ID 10. Add the code of this page to the same view.

How Tables Are Organized

Users insert tables from the ribbon and change them on the Table Layout tab, which the editor shows, together with the Formulas tab, while the input position or the selection is in a table:

Your code works with these objects:

Rows and columns are numbered from 1.

Use the Keyboard in Tables

In a table, the keys of the editor move between cells and extend the table:

The other keys and mouse actions of the editor are listed in Editing Text.

When a table runs over several pages, the paragraph settings keep with next and page break before apply to whole rows, and the first paragraph of a row decides for the row; see Tables in Document Processing.

Insert a Table and Find It Again

TableCollection.add inserts a table at the current input position. It takes the number of rows and columns, an optional ID and a callback that receives true if the table was added. getCanAdd tells you beforehand whether a table can be inserted at the input position.

The ID is how your code finds a table again. Table.setID requires a value from 10 to 32767. TableCollection.getItem passes a table to its callback:

• With an ID as the third parameter, it passes the first table with that ID.
• Without an ID, it passes the table that contains the input position.
• If there is no such table, it passes null.

Because add works asynchronously, call getItem inside the callback of add, as the tutorial does.

Format a Table

The following code turns the first row of table 10 into a header row, gives all cells gray borders and colors the header cells. It also sets a descriptive text, which screen readers use to describe the table:

<input type="button" onclick="formatTable()" value="Format Table" />

<script>
    function formatTable() {
        TXTextControl.tables.getItem(function (table) {
            if (table === null) {
                return; // no table with the ID 10
            }

            // Make row 1 a header row
            table.rows.getItem(function (row) {
                row.setIsHeader(true);
            }, null, 1);

            // Border width, color and text distance in twips for every cell
            table.cells.forEach(function (cell) {
                cell.getCellFormat(function (format) {
                    [format.leftBorder, format.topBorder, format.rightBorder, format.bottomBorder]
                        .forEach(function (border) {
                            border.setWidth(20);
                            border.setColor("#808080");
                            border.setTextDistance(100);
                        });
                });
            });

            // Background color and vertical alignment of the cells in row 1
            for (var column = 1; column <= 5; column++) {
                table.cells.getItem(function (cell) {
                    cell.getCellFormat(function (format) {
                        format.setBackColor("#DDEBF7");
                        format.setVerticalAlignment(TXTextControl.VerticalAlignment.Center);
                    });
                }, null, 1, column);
            }

            // The text a screen reader uses for the table
            table.setDescriptiveText("Sample table with five rows and five columns");
        }, null, 10);
    }
</script>

The code works as follows:

• TableRowCollection.getItem passes the row with the given number. setIsHeader makes it part of the table header, which is repeated at the top of each page when the table extends over several pages.
• forEach calls the function for every cell. TableCell.getCellFormat passes the format of the cell, and changes to it apply to the cell right away.
• leftBorder and the other border properties return a TableCellBorder. setWidth sets the width of the border line and setTextDistance the distance between the border and the text, both in twips (20 twips are 1 point).
• TableCellCollection.getItem passes the cell with a row and column number. setBackColor and setVerticalAlignment change its background and the vertical position of its text, with Top, Center or Bottom from VerticalAlignment.
• setDescriptiveText stores the description of the table, which accessible PDF documents contain.

Click Insert Table and then Format Table: the table gets gray borders and a blue header row.

To set the text of a cell, call TableCell.setText; getText passes the text to a callback. The widths of cells and columns are set in twips with TableCell.setWidth and TableColumn.setWidth, and a minimum row height with TableRow.setMinimumHeight.

Work With the Table at the Input Position

Many table functions start from where the user has placed the cursor. The following code shows the position of the current cell and adds a row below and a column to the right of it:

<input type="button" onclick="showCurrentCell()" value="Show Current Cell" />
<input type="button" onclick="addRowAndColumn()" value="Add Row and Column" />

<script>
    function showCurrentCell() {
        // Without an ID, getItem passes the table at the input position
        TXTextControl.tables.getItem(function (table) {
            if (table === null) {
                console.log("The input position is not in a table.");
                return;
            }

            table.cells.getItemAtInputPosition(function (cell) {
                cell.getRow(function (row) {
                    cell.getColumn(function (column) {
                        console.log("Row " + row + ", column " + column);
                    });
                });
            });
        });
    }

    function addRowAndColumn() {
        TXTextControl.tables.getItem(function (table) {
            if (table === null) {
                return;
            }

            table.rows.add(TXTextControl.TableAddPosition.After, 1, function (rowAdded) {
                table.columns.add(TXTextControl.TableAddPosition.After, function (columnAdded) {
                    console.log("Row added: " + rowAdded + ", column added: " + columnAdded);
                });
            });
        });
    }
</script>

• getItemAtInputPosition passes the cell that contains the input position.
• TableRowCollection.add adds the given number of rows before or after the selected rows, with Before or After from TableAddPosition. TableColumnCollection.add adds a column at the input position.
• TableRowCollection.remove removes the row at the input position, or the selected rows.

Merge and Split Cells

Cells are merged in two steps: select them, then merge the selection. Table.selectCells selects a range from a start row and column to an end row and column. getCanMergeCells tells whether the selection can be merged, which requires more than one selected cell, and mergeCells merges it:

function mergeLastRow() {
  TXTextControl.tables.getItem(function (table) {
    if (table === null) {
      return;
    }

    // Select row 5 from column 1 to column 5, then merge the selected cells
    table.selectCells(5, 1, 5, 5, function () {
      table.getCanMergeCells(function (canMerge) {
        if (canMerge === true) {
          table.mergeCells(function (merged) {
            console.log("Cells merged: " + merged);
          });
        }
      });
    });
  }, null, 10);
}

splitCells splits selected cells that were merged before; only merged cells can be split. Table.split is different: it splits the whole table into two tables above or below the row that contains the input position.

Calculate With Formulas

A table cell can contain a formula that calculates its text from other cells of the same table, as in a spreadsheet. When the text of a referenced cell changes, the cell with the formula is updated automatically. References to other tables or to other documents aren't supported.

The following code fills rows 1 to 4 of column 5 of table 10 with numbers and adds their sum in row 5, with two decimal places:

<input type="button" onclick="addTotal()" value="Add Total" />

<script>
    function addTotal() {
        // Address cells with a column letter and a row number, such as E1
        TXTextControl.setFormulaReferenceStyle(TXTextControl.FormulaReferenceStyle.A1, function () {
            TXTextControl.tables.getItem(function (table) {
                if (table === null) {
                    return; // no table with the ID 10
                }

                // Numbers in rows 1 to 4 of column 5
                ["120", "80.5", "42", "7"].forEach(function (value, index) {
                    table.cells.getItem(function (cell) {
                        cell.getCellFormat(function (format) {
                            format.setTextType(TXTextControl.TextType.Number, function () {
                                cell.setText(value);
                            });
                        });
                    }, null, index + 1, 5);
                });

                // Row 5 adds up the cells above and shows two decimal places
                table.cells.getItem(function (cell) {
                    cell.getCellFormat(function (format) {
                        format.setTextType(TXTextControl.TextType.Number, function () {
                            format.setNumberFormat("#.00", function () {
                                cell.setFormula("SUM(E1:E4)");
                            });
                        });
                    });
                }, null, 5, 5);
            }, null, 10);
        });
    }
</script>

The code works as follows:

• setFormulaReferenceStyle sets how formulas address cells, with a value of FormulaReferenceStyle. With A1, a column letter and a row number address a cell: B3 is row 3 of column 2. With R1C1, the same cell is R3C2, and a relative reference such as R[-3]C[-2] addresses the cell three rows above and two columns to the left of the formula cell.
• TableCellFormat.setTextType with Number from TextType makes the text of a cell a number. In the cell text and in number formats, the period is the decimal separator and the comma the thousands separator. With Text, the text is shown as it is.
• setNumberFormat sets how the calculated number is displayed. #.00 shows two decimal places.
• TableCell.setFormula sets the formula of the cell. getFormula passes it to a callback.

Click Insert Table and then Add Total: cell E5 shows 249.50. Change one of the numbers above it, and the sum is updated. The displayed number uses the decimal and thousands separators of the system language.

With the input position in E5, the Formulas tab shows the formula and the number format of the cell, and the ruler bar shows the column letters and row numbers when its formula mode is Auto (see "Calculation and Cell References" below):

Formula Syntax

The syntax follows the formulas of spreadsheets as specified in Office Open XML (ISO/IEC 29500-1). A formula consists of:

• Functions with their arguments separated by commas, such as SUM(E1:E4), AVERAGE(A1:A3) or IF(A1>100,1,0). Mathematical functions such as ROUND, statistical functions such as MAX and logical functions such as IF are supported.
• Cell references and ranges. A range is two references separated by a colon. LEFT, ABOVE, RIGHT and BELOW refer to the cells next to the formula cell, as in SUM(ABOVE).
• Constants: numbers such as 4, 3.937 or 5e4, the logical values TRUE and FALSE, strings in double quotes such as "text" and arrays in braces such as {1,2,3;4,5}.
• Operators: the arithmetic operators +, -, *, /, % and ^, the comparison operators =, >, <, >=, <= and <> and the operator &, which joins text.

In A1 style, a $ makes a column or row absolute, as in $B$3. Tables in Document Processing describes constants, operators and cell references with examples, and Formula Functions lists every function with its arguments.

A number format consists of the placeholders # and 0, the separators . and ,, % and other text. 0 shows a digit even when it is zero, and text in quotes is shown as it is: "EUR"#.00 shows 29.99 as EUR 29.99, and #% shows 0.52 as 52%. Only formats for positive numbers are supported. Number Formats in Document Processing describes rounding, thousands separators, scaling and escaped characters. The blog article Formatting Numbers in Table Cells shows more examples.

Calculation and Cell References

• setIsFormulaCalculationEnabled turns the automatic calculation on or off, and getIsFormulaCalculationEnabled passes the current state.
• RulerBarViewGenerator.setFormulaMode shows the cell references in the ruler bar of TXTextControl.rulerBar. With Auto from RulerBarFormulaMode, they appear when the input position is in a cell with a formula; with Always, they are shown relative to the current cell.

function showCellReferences() {
  TXTextControl.rulerBar.setFormulaMode(TXTextControl.RulerBarFormulaMode.Auto);
}

Formulas are kept when XLSX documents are loaded as tables, as described in Supported File Formats. The blog article Online Sample: Combining Form Fields and Formulas calculates a form with formulas.

Nested Tables

A table can be inserted into a cell of another table. Table.nestedTables returns the tables inside a table, getNestedLevel passes the nesting level (1 for a table that isn't nested), and getOuterTable passes the table that contains it.

Remove a Table

removeById removes the first table with an ID, and removeAtInputPosition removes the table that contains the input position:

function removeTable() {
  TXTextControl.tables.removeById(10, function (removed) {
    console.log("Table removed: " + removed);
  });
}

The editor raises tableDeleted when a table has been deleted, and tableCreated when a table without an ID is created by pasting or by loading a document. tableCreated isn't raised for tables that your code adds with add; use the callback of add instead. See Events.

The blog article Document Editor: Useful JavaScript Functions for Tables shows more functions, such as exporting a table to JSON.

Next Steps

• Text and Paragraph Formatting: format the text inside cells.
• Images: insert images into the document.
• Merge Blocks: repeat table rows for each record in a template.
• Tables in Document Processing: create tables on the server with ServerTextControl.