Contribute to this guideReport an issue

guideCreating Data Grids with Spreadsheet Plugin

This feature is provided as a commercial product and provides integration with our partner solution, Handsontable. A license can be purchased here.

The Spreadsheet plugin is compatible with CKEditor 4 starting from version 4.13. It is not included in the CKEditor presets available from the Download site. Follow the Spreadsheets Integration guide to enable it.

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

A spreadsheet inserted into CKEditor 4 WYSIWYG editor.

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.

# 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 (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.

Spreadsheet plugin widget inserted into CKEditor 4 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:

Spreadsheet plugin converts existing table context menu option.

# 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.

Spreadsheet plugin context menu with rows and columns manipulation options

# 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.

The row and column resizing mechanism of the Spreadsheet plugin.

# 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:

Spreadsheet plugin widget with multisort.

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.

# 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:

Spreadsheet plugin widget with basic styling.

# 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.

Spreadsheet plugin with the Cell Type and Format dialog.

# 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.

Spreadsheet plugin with formula preview.

Refer to the official Handsontable formulas reference for more details.

# 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.

Spreadsheet plugin with the Conditional Formatting dialog.

# 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.

A data grid with the auto fill feature visible.

# 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).

A partially duplicated spreadsheet instance created with copy and paste.

# 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.

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.