CKEditor Ecosystem Documentation
Search
Contribute to this guideReport an issue
Home/CKEditor 4/Examples

Creating Data Grids with Spreadsheet Plugin Documentation

The optional, commercial Spreadsheet plugin, introduced in CKEditor 4.13, offers Excel-like functionality available right inside CKEditor 4. It allows you to create intelligent, data-driven documents in your WYSIWYG editor. This makes it a perfect solution for financial, auditing, engineering, technical and science industries.

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

When enabled, the Spreadsheet plugin allows you to insert advanced spreadsheet widgets into your rich-text content and provides the following features:

  • Inserting spreadsheets with and without a header row or column and with any number of rows and columns.
  • Creating auto-updating references to spreadsheet cells right inside editor content.
  • Autoconverting 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, removing and resizing rows and columns).
  • Renaming row and column headers.
  • Single and multi-column sorting.
  • Basic data styling like bold, italic, underline and so on.
  • Changing cell alignment, text and background color, font size and font family.
  • 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.

For a more detailed Spreadsheet plugin functionality overview, refer to the Creating Data Grids with Spreadsheet Plugin article. See the Spreadsheets Integration guide for information about how to install and configure the plugin.

Some of the existing CKEditor 4 features are not supported in the spreadsheet widget at the moment. This means that applying content changes with the Styles and Format dropdowns or inserting emoji inside the spreadsheet widget content will have no effect.

Cell Referencing

The cell referencing feature was introduced in version 1.2.0 of the Spreadsheet plugin.

This feature allows you to create a reference to any spreadsheet cell inside the CKEditor 4 content. Creating references is super simple thanks to an intuitive autocomplete panel showing the best matching suggestions for the currently typed query. It supports fuzzy matching, which means you need to only type a part of the spreadsheet, row or column name to get relevant suggestions. This makes creating references very quick.

Whenever the spreadsheet cells are updated or their values change, the references are also auto-updated. This keeps the entire content up to date with no user action needed.

To display the cell referencing suggestion panel, just type the $ sign. By default, the suggestion panel will show up to 10 items, 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.

Spreadsheet, column and row name filtering uses fuzzy matching. Some examples:

  • Type !sheet to match spreadsheet1 and spreadsheet2 but not custom-data1.
  • Type $!... to take all spreadsheet instances in the content into account.
  • Type $!ch to match the all the columns showing the change in all available spreadsheet instances.
  • Type $sheet1!ch to match the same columns but only for spreadsheet instances whose names contain the sheet1 text.

Get Sample Source Code

  • Creating data grids with Spreadsheet plugin 
                    
    CopyDownload
    <!doctype html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="robots" content="noindex, nofollow">
  <title>Creating data grids with Spreadsheet plugin</title>
  <script src="https://cdn.ckeditor.com/4.16.1/standard-all/ckeditor.js"></script>
</head>

<body>
  <textarea cols="80" id="editor1" name="editor1" rows="10" data-sample-short>&lt;p&gt;This is some &lt;strong&gt;sample text&lt;/strong&gt;. You are using &lt;a href=&quot;https://ckeditor.com/&quot;&gt;CKEditor&lt;/a&gt;.&lt;/p&gt;</textarea>
  <script>
    CKEDITOR.replace('editor1', {
      width: 'auto',
      height: 550,
      extraAllowedContent: 'h1;a[!href]',

      spreadsheet_licenseKey: LICENSE_KEY,
      spreadsheet_enableAutoConversion: true,

      plugins: 'spreadsheet,' +
        'ajax,' +
        'link,' +
        'undo,' +
        'justify,' +
        'enterkey,' +
        'about,' +
        'basicstyles,' +
        'clipboard,' +
        'elementspath,' +
        'floatingspace,' +
        'htmlwriter,' +
        'removeformat,' +
        'sourcedialog,' +
        'tab,' +
        'toolbar,' +
        'undo,' +
        'resize,' +
        'wysiwygarea,' +
        'contextmenu,' +
        'colorbutton,' +
        'pastefromword,' +
        'pastefromgdocs,' +
        'image,' +
        'format,' +
        'font,' +
        'list,' +
        'magicline,' +
        'maximize,' +
        'print,' +
        'table',

      toolbar: [{
          name: 'insert',
          items: ['Spreadsheet', 'Table', 'Image']
        },
        {
          name: 'basicstyles',
          items: ['Bold', 'Italic', 'Underline', 'Strike', 'RemoveFormat']
        },
        {
          name: 'colors',
          items: ['TextColor', 'BGColor']
        },
        {
          name: 'align',
          items: ['JustifyLeft', 'JustifyCenter', 'JustifyRight', 'JustifyBlock']
        },
        {
          name: 'links',
          items: ['Link', 'Unlink']
        },
        {
          name: 'styles',
          items: ['Font', 'FontSize']
        },
        {
          name: 'document',
          items: ['Print']
        },
        {
          name: 'tools',
          items: ['Maximize']
        }
      ]
    });
  </script>
</body>

</html>
  • Cell Referencing 
                    
    CopyDownload
    <!doctype html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="robots" content="noindex, nofollow">
  <title>Cell Referencing</title>
  <script src="https://cdn.ckeditor.com/4.16.1/standard-all/ckeditor.js"></script>
</head>

<body>
  <textarea cols="80" id="editor2" name="editor2" rows="10" data-sample-short>&lt;p&gt;This is some &lt;strong&gt;sample text&lt;/strong&gt;. You are using &lt;a href=&quot;https://ckeditor.com/&quot;&gt;CKEditor&lt;/a&gt;.&lt;/p&gt;</textarea>
  <script>
    CKEDITOR.replace('editor2', {
      width: 'auto',
      height: 750,
      extraAllowedContent: 'h1;a[!href]',

      spreadsheet_licenseKey: LICENSE_KEY,
      spreadsheet_enableAutoConversion: true,

      plugins: 'spreadsheet,' +
        'ajax,' +
        'link,' +
        'undo,' +
        'justify,' +
        'enterkey,' +
        'about,' +
        'basicstyles,' +
        'clipboard,' +
        'elementspath,' +
        'floatingspace,' +
        'htmlwriter,' +
        'removeformat,' +
        'sourcedialog,' +
        'tab,' +
        'toolbar,' +
        'undo,' +
        'resize,' +
        'wysiwygarea,' +
        'contextmenu,' +
        'colorbutton,' +
        'pastefromword,' +
        'pastefromgdocs,' +
        'image,' +
        'format,' +
        'font,' +
        'list,' +
        'magicline,' +
        'maximize,' +
        'print,' +
        'table',

      toolbar: [{
          name: 'insert',
          items: ['Spreadsheet', 'Table', 'Image']
        },
        {
          name: 'basicstyles',
          items: ['Bold', 'Italic', 'Underline', 'Strike', 'RemoveFormat']
        },
        {
          name: 'colors',
          items: ['TextColor', 'BGColor']
        },
        {
          name: 'align',
          items: ['JustifyLeft', 'JustifyCenter', 'JustifyRight', 'JustifyBlock']
        },
        {
          name: 'links',
          items: ['Link', 'Unlink']
        },
        {
          name: 'styles',
          items: ['Font', 'FontSize']
        },
        {
          name: 'document',
          items: ['Print']
        },
        {
          name: 'tools',
          items: ['Maximize']
        }
      ]
    });
  </script>
</body>

</html>