Creating Data Grids with Spreadsheet Plugin

The optional Spreadsheet plugin lets you insert customizable spreadsheet widgets into your WYSIWYG editor. It provides support for:

• Inserting spreadsheets with and without a header row or column and with any number of rows and columns.
• Referencing cells inside the editor content using smart completion.
• Autoconverting tabular data pasted from Microsoft Excel and Google Sheets into a Spreadsheet instance.
• Converting existing tables to spreadsheet instances and vice versa.
• Inserting spreadsheets using predefined templates.
• Spreadsheet structure manipulation (adding or removing rows and columns).
• Resizing rows and columns.
• Renaming rows and columns headers.
• Single and multi-column sorting.
• Basic data styling like bold, italic, underline and so on.
• Advanced styles support like text font, cell colors and so on.
• Selecting cell type and formatting with data validation support.
• Using formulas.
• Auto filling rows and columns.
• Applying various conditional formatting rules to single and multiple cells, entire columns or spreadsheets.
• Advanced copying and pasting.

The Spreadsheet plugin allows you to create intelligent, data-driven documents right inside your WYSIWYG editor. This makes it a perfect solution for financial, auditing, engineering, technical and science industries.

# Cell Referencing

What makes Spreadsheets special? First-class integration with content editing! You can start typing $ to see cell suggestions from the spreadsheets inside your document. Cell references are a living part of your document, which means that editing the spreadsheet cell value, including via the API, will also update all the references automatically. What is even more convenient, you do not need to see the spreadsheet instance in a long document. Smart suggestions and fuzzy matching will simplify connecting the data from any spreadsheet present in the editor with the editor content.

To display the cell referencing suggestion panel, just type the $ sign (see how to reconfigure the default matching pattern). By default, the suggestion panel will show up to 10 items (the number of suggestions is configurable), narrowed down based on the closest pattern match and the spreadsheet cell order, starting from the top-left corner.

Each suggestion consists of six parts. For example, a suggestion like $spreadsheet1!Symbol:11 consists of:

1. The suggestion prefix ($) – Opens the suggestions panel.
2. The spreadsheet name (spreadsheet1) – Filters suggestions by spreadsheet names (which can be set via the dialog during the spreadsheet insertion).
3. The cell prefix (!) – Separates the spreadsheet name from the column and row names.
4. The column name (Symbol) – Filters suggestions by column names.
5. The column and row name separator (:) – Separates the column name from the row name.
6. The row name (11) – Filters suggestions by row names.

You can try out this feature in the dedicated sample.

# Conditional Formatting

Do you need to add some colors to your data? Conditional formatting allows formatting data (any cell, column or entire data grid) based on its content. For example, you can mark cells red when they are empty, make them green when their value is above some threshold or blue if the cell value contains a specific text.

Conditional formatting comes with a dozen of predefined rules. Any number of rules can be set up for each cell, which allows for complex formatting and handling advanced cases. You can even drag and drop conditional formatting rules, changing their priority to easily create features like a color scale. Put your hands on the color scale example in the working Spreadsheets demo.

# Pasting from Microsoft Excel and Google Sheets

Moving your tabular data from Microsoft Excel or Google Sheets is as simple as copying and pasting it into the editor. Once the automatic tables conversion is enabled, Spreadsheets will take care of converting any table to an advanced Spreadsheet instance with all features available.

# Inserting Spreadsheet Widget

The spreadsheet widget can be inserted into your editor content with the Spreadsheet Properties dialog opened with the Insert Spreadsheet toolbar button (). It can also be added through a predefined spreadsheet template when using the Content Templates plugin.

Below you can see a simple 3x3 spreadsheet widget right after being inserted into the WYSIWYG editor.

# Converting Existing Tables

If you already have tables in your content and plan to use the Spreadsheet plugin, there is no need to recreate the entire table from scratch as you may simply convert it to a spreadsheet widget instance:

Converting all existing tables at once automatically can be also done via configuration option or an API call.

# Spreadsheet Structure Manipulation

The Spreadsheet plugin allows for easy data structure manipulation with options such as Insert row above, Insert row below, Insert column left, Insert column right, Remove row or Remove column.

# Resizing Rows and Columns

Some data points or observations may contain quite long data which might be inconvenient or even unreadable in narrow rows or columns. To solve this issue, you can resize entire columns and rows.

# Renaming Rows and Columns Headers

It is important to label your data in a clear and understandable way. Spreadsheet plugin allows easy rows and columns headers renaming via a simple dialog:

# Basic Styles Support

While spreadsheets are mostly about data, their presentation is also very important. Basic styles like bold, italic or alignment come handy when trying to highlight or make some parts of data or individual spreadsheet cells more visible:

# Advanced Styles Support

Spreadsheets are integrated with the Color Button and Font features. This allows for changing the font family, font size, background and text colors for any spreadsheet instance present in the editor.

# Data Types, Formats and Validation

Data is the essence of every spreadsheet or data grid element. The Spreadsheet plugin provides a few data types:

• Text – The default type formatted as a plain text that allows any input.
• Numeric – Reserved for numeric values like accounting records. You can choose one from of the available patterns to format data, like percent, financial, currency, etc.
• Date – Type for presenting dates with a few formats available.
• Time – Time format providing conversion between 12h and 24h format.
• Password – Password type used for masking cell data.

Additionally, each data type has a strict validation so it is clearly visible when something is not right with the data.

# Sorting

Sorting is a must to efficiently analyze and present any tabular data, thus the Spreadsheet plugin allows for sorting data based on single or multiple columns:

To sort by a single column, just click the column header. To use multisort, any column which should be added to sorting should be clicked while the Ctrl key is pressed.

# Formulas

Formulas provide support for mathematical expression calculations based on input data. Just type = and the rest of the formula, using arithmetic expressions and cell references to customize the output. Supported features:

• Any numbers, negative and positive, as float or integer.
• Arithmetic operations such as: +, -, /, *, %, ^.
• Logical operations such as: AND(), OR(), NOT(), XOR().
• Comparison operations such as: =, >, >=, <, <=, <>.
• All JavaScript Math constants such as: PI(), E(), LN10(), LN2(), LOG10E(), LOG2E(), SQRT1_2(), SQRT2().
• Error handling: #DIV/0!, #ERROR!, #VALUE!, #REF!, #NAME?, #N/A, #NUM!.
• String operations such as: & (concatenation eq. =-(2&5) will return -25).
• Relative and absolute cell references such as: A1, $A1, A$1, $A$1.
• Built-in variables such as: TRUE, FALSE, NULL.
• Custom variables.
• Nested functions.
• Dynamic updates.

Refer to the official Handsontable formulas reference for more details.

# Auto Fill in All Directions

Auto fill is a mechanism that allows for easy duplication of content in order to fill empty spreadsheet rows and columns (or both at the same time). The little square (fill handle) in the bottom right-hand corner of the selected cell can be dragged to repeat the values from the cell, just like it can be done in Microsoft Excel or Google Sheets.

# Duplicating Spreadsheet Part with Copy and Paste

Apart from creating new spreadsheet instances from scratch or from templates, it is easy to duplicate a part or an entire existing spreadsheet with a simple copy and paste (using Ctrl/Cmd+C and Ctrl/Cmd+V).

# Frontend Integration

One of the main features of a WYSIWYG editor is the ability to show the created content in a way that it appears almost identical as during the edition. With the Spreadsheet plugin is it possible to show a regular HTML table with all the styling and colors preserved. Another possibility is to show a fully interactive spreadsheet instance (the same as used during editing) with all styles. The latter option is available thanks to the frontend integration adapter.

# Spreadsheets Demo

See the working “Creating Data Grids with Spreadsheet Plugin” sample that showcases the most important features like data styling, sorting, conditional formatting, formulas and more.

# Related Features

Refer to the following resources for more information about tabular data support in CKEditor 4:

• The Paste from Excel plugin allows you to also paste content from Microsoft Excel and maintain original content structure and formatting.
• The Table plugin provides support for creating and editing complex tables including features like resizing, styling, custom selection and complex structural changes (merge, split, insert, remove cells, rows, columns).
• The Spreadsheets Integration guide explains how to enable and use the Spreadsheet plugin in CKEditor 4 as well as how to customize some of its features.