Spreadsheets Integration

The Spreadsheet plugin offers Excel-like functionality right inside CKEditor 4. It allows for creating complex data sheets that integrate smoothly with the existing CKEditor 4 WYSIWYG editor functionality and content, providing data types, formatting and styling, formulas, conditional formatting and more. Refer to the Creating Data Grids with Spreadsheet Plugin article to learn more about the available features.

# Quick Start

There are a few methods that you can use in order to add the Spreadsheet plugin to your CKEditor 4 installation.

# Installation with npm

The Spreadsheet plugin for CKEditor 4 is available through npm. To use it, install the ckeditor4-plugin-spreadsheet npm package as a dependency of your project:

npm install ckeditor4-plugin-spreadsheet

If CKEditor 4 was also installed through npm (using the ckeditor4 package) in your project, the plugin will create a symbolic link itself in the CKEditor 4 plugins/ directory so you do not need to move or copy any files.

If you chose another method to install CKEditor 4, copy or link the spreadsheet folder from the ckeditor4-plugin-spreadsheet package to the CKEditor 4 plugins/ directory.

If you prefer not to touch any files, you may use the CKEDITOR.plugins.addExternal() API instead to load the Spreadsheet plugin directly from the node_modules/ directory:

CKEDITOR.plugins.addExternal( 'spreadsheet', './node_modules/ckeditor4-plugin-spreadsheet/spreadsheet/' );

# Custom Build with Online Builder

The Spreadsheet plugin is also available in the official CKEditor 4 Add-ons Repository. This allows you to automatically include it in any custom build which is created with the online builder.

To help you get started, below you can find direct links to the online builder with Spreadsheet plugin already included:

• Online builder – Standard preset with the Spreadsheet plugin
• Online builder – Full preset with the Spreadsheet plugin

If you prefer to simply download a zip package:

• Standard preset with the Spreadsheet plugin
• Full preset with the Spreadsheet plugin

# Download Manually from the Add-ons Repository

If, for any reason, you prefer to download the plugin code manually, you can do this via the Add-ons Repository. Simply download the Spreadsheet plugin and place it in the CKEditor 4 plugins/ directory.

Do note, however, that this method is not recommended as it requires you to install all plugin dependencies manually, too.

# Enable the Spreadsheet Plugin

The setup is straightforward — you just need to enable the plugin and add the license key in your CKEditor 4 configuration:

CKEDITOR.replace( 'editor', {
    extraPlugins: 'spreadsheet',
    spreadsheet_licenseKey: 'yourLicenseKey'
} );

The value of spreadsheet_licenseKey is unique for each website and can be found in the CKEditor Ecosystem customer dashboard. Go to Website management and press the CKEditor configuration button to obtain it.

This is all. If you are having trouble with setting up the Spreadsheet plugin, please contact us.

# Cell References

In most situations the default settings for cell references should suit your needs. However, you can adjust the configuration to customize the behavior of cell referencing if needed.

# Limiting or increasing the number of suggestions

By default, cell references suggestion panel will show 10 items at a time. Completion matches are narrowed down based on the closest pattern match and the Spreadsheets cell order, starting from the top-left corner.

If you find the maximum number of matches limiting, you can customize it using the spreadsheet_cellReferencesLimit configuration option:

CKEDITOR.replace( 'editor', {
    spreadsheet_licenseKey: 'yourLicenseKey',

    // Increase the number of suggestions to 20.
    spreadsheet_cellReferencesLimit: 20
} );

# Customizing the matching pattern

By default the cell references suggestion panel is triggered by the $ character. To improve performance and pre-validate Spreadsheet names, it also uses a special pattern when resolving completion matches. The default pattern consists of letters a-z, numbers 0-9, underscore _, exclamation mark ! and a colon :.

You can create your own completion pattern if you have any non-standard columns or row names. This can be done using a simple regular expression and the spreadsheet_cellReferencesPattern configuration option:

CKEDITOR.replace( 'editor', {
    spreadsheet_licenseKey: 'yourLicenseKey',

    // Use the `%` character as a marker instead of the default `$`.
    spreadsheet_cellReferencesPattern: /\%[:_\!a-zA-Z0-9À-ž]*$/
} );

# Throttling the completion panel

Quick typers could overheat suggestions if they were triggered for every character typed. For that purpose, cell references completion algorithm utilizes typing throttling which slightly delays refreshing the suggestion list to make it more performant.

You can change the throttling time if you would like to get quicker response from the editor or increase it to improve performance if you target Spreadsheet instances with many rows and columns:

CKEDITOR.replace( 'editor', {
    spreadsheet_licenseKey: 'yourLicenseKey',

    // Decrease the throttling timeout from 200ms to 50ms for better UX:
    spreadsheet_cellReferencesThrottle: 50
} );

# Paste from Excel and Google Sheets

Spreadsheets are able to understand bloated HTML representing tables from external applications like Microsoft Excel and Google Sheets. They are also able to convert it into much more semantic representation while keeping the most important information unchanged.

To convert any tabular data that comes from Microsoft Excel, Google Sheets or a plain HTML website to a Spreadsheet instance automatically after pasting it into the editor, use the spreadsheet_enableAutoConversion option described in the Automatically Convert Existing Tables section below.

# Automatically Convert Existing Tables

Any existing table can be easily transformed into a spreadsheet with the Convert to Spreadsheet option from the table context menu. However, manual conversion may become cumbersome when dealing with content containing dozens of tables. The entire procedure can thus be easily automated thanks to spreadsheet_enableAutoConversion configuration option:

var editor = CKEDITOR.replace( 'editor', {
    extraPlugins: 'spreadsheet',
    spreadsheet_licenseKey: 'yourLicenseKey',
    spreadsheet_enableAutoConversion: true
} );

To gain more control over which table elements are converted or to add custom conversion algorithm, one may use addTableTransformation() API method instead:

CKEDITOR.plugins.spreadsheet.addTableTransformation( editor, function( element ) {
    // Here you can provide any custom code. If it returns "true"
    // table element will be converted into a Spreadsheet instance.
    return !!element.attributes[ 'data-spreadsheet' ];
} );

The above API uses addTransformations method underneath, adding special filter to CKEditor 4 instance.

# Frontend Layer Integration

The standard CKEditor 4 output produces a regular HTML table from any spreadsheet widget. This gives huge flexibility thanks to using a standardized structure and allows integrating with a variety of tools or workflows out-of-the-box. However, there are cases where one would like to present the content created in CKEditor 4 with rich spreadsheets functionality on the frontend layer.

This is where the Spreadsheets frontend adapter comes into play. With a minimal configuration, one can simply recreate the spreadsheet with all its styling on the frontend layer.

This requires including the adapter scripts and the stylesheet which is present in the Spreadsheet plugin package (adapters/frontend.js):

<link rel="stylesheet" href="pluginPath/libs/handsontable/dist/handsontable.full.min.css">

<script src="pluginPath/libs/handsontable/dist/handsontable.full.min.js"></script>
<script src="pluginPath/adapters/frontend.js"></script>

And initializing it with the license key:

CKE4Spreadsheet.licenseKey = 'yourLicenseKey';

The frontend adapter will automatically transform all available spreadsheet instances. To transform only specific instances, additional configuration options should be set:

CKE4Spreadsheet.autoConvert = false;
CKE4Spreadsheet.convert( document.querySelector( 'container' ) );

# Content Templates Plugin Integration

The Spreadsheet plugin allows you to create highly customized spreadsheet widget instances from scratch. However, there are also cases when it is more convenient to insert a ready-to-use spreadsheet with a predefined structure and formatting.

This is possible thanks to integrating the Spreadsheet plugin with the Content Templates plugin. The Content Templates plugin should be enabled and correctly configured first, though:

CKEDITOR.replace( 'editor', {
    extraPlugins: 'spreadsheet',
    spreadsheet_licenseKey: 'yourLicenseKey',
    templates: 'spreadsheets',
    templates_files: [ 'spreadsheet_templates.js' ]
} );

Refer to the templates and templates_files configuration options API documentation for more details.

And the spreadsheet_templates.js file may look as follows:

CKEDITOR.addTemplates( 'spreadsheets', {
    imagesPath: CKEDITOR.getUrl( 'path/to/images/folder/' ),
    templates: [ {
        title: 'Spreadsheet',
        image: 'template1.gif', // Thumbnail image used in the Content Templates dialog.
        description: '2x2 Spreadsheet',
        html: '' +
            '<table data-cke-spreadsheet-widget="1">' +
                '<thead>' +
                    '<tr>' +
                        '<th></th>' +
                        '<th data-sort-order="asc" data-sort-priority="0">Column 1</th>' +
                        '<th>Column 2</th>' +
                    '</tr>' +
                '</thead>' +
                '<tbody>' +
                    '<tr>' +
                        '<th>1</th>' +
                        '<td></td>' +
                        '<td data-type="numeric" data-type-pattern="0,0.00"></td>' +
                    '</tr>' +
                    '<tr>' +
                        '<th>2</th>' +
                        '<td></td>' +
                        '<td data-type="numeric" data-type-pattern="0,0.00"></td>' +
                    '</tr>' +
                '</tbody>' +
            '</table>'
    } ]
} );

The above template will allow inserting a 2x2 spreadsheet widget with the default ascending sorting order (in the first column) and the numeric data type (in the second column) with just two clicks. See it in action in the working “Creating Data Grids with Spreadsheet Plugin” sample.

# Manipulating the Spreadsheet Widget Data via API

The Spreadsheet plugin uses the Handsontable library internally. While the Handsontable API can be accessed directly with CKEDITOR.instances.editor.plugins.spreadsheet.Spreadsheet, it is not recommended since any direct API calls may break the integration.

For that reason, CKEditor exposes its own API that can be used to insert, update and fetch the data from any spreadsheet widget.

# Inserting a New Spreadsheet Widget

To initialize a spreadsheet instance at the current editor selection:

CKEDITOR.plugins.spreadsheet.toSpreadsheet( editor, [
    [ 1.1, 1.2, 1.3 ],
    [ 2.1, 2.2, 2.3 ],
    [ 3.1, 3.2, 3.3 ]
] );

This method accepts an array of arrays as the data source.

# Updating Data in an Existing Spreadsheet Widget

To load new data into an existing spreadsheet widget:

CKEDITOR.plugins.spreadsheet.loadData( widget, [
    [ 1.1, 1.2, 1.3 ],
    [ 2.1, 2.2, 2.3 ],
    [ 3.1, 3.2, 3.3 ]
] );

# Fetching Data from the Spreadsheet Widget

To get the data from an existing spreadsheet widget:

var data = CKEDITOR.plugins.spreadsheet.toData( widget );

console.log( data );
// [
//	[ 1.1, 1.2, 1.3 ],
//	[ 2.1, 2.2, 2.3 ],
//	[ 3.1, 3.2, 3.3 ]
// ]

The above API call returns the spreadsheet widget data as an array of arrays.

# Browser Support

The spreadsheet plugin is fully supported in the latest Chrome, Firefox, Safari and Edge browsers.

Support for Internet Explorer 11 is limited: the plugin is usable but there are known, visible issues.

If your application requires wider support for Internet Explorer 11 browser or you have encountered any issues using it, please contact us.

# Functionality Overview

Refer to the Creating Data Grids with Spreadsheet Plugin article for an overview of all available features of the Spreadsheet plugin.

# Demo

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