Overview

In the Create an Excel-like Custom View article and the Insert and Delete rows from an Excel-like Custom View article, we introduced how to customize Kintone into an Excel-like custom view using Handsontable. These articles use the Community version of Handsontable (v6.2.2).

This article introduces how to implement the Commercial version of Handsontable (v12.4.0), which includes advanced features such as column filtering and nested rows.

Use Cases of Kintone and the Commercial License of Handsontable

• Create Kintone customizations for internal usage
  Since the commercial license only charges the developers, users of apps that are customized with Handsontable do not need to pay for the license. Use Handsontable to customize Kintone for providing intuitive user interfaces for the App users.
• Develop and sell Kintone plug-ins
  If the plug-in developer has the commercial license, Handsontable won't charge end-users for using the library within the plug-in.

Using the latest Commercial license Handsontable to customize Kintone enables the following:

• Features for placing buttons to download data as CSV
• Features to create parent-child rows
• Features to add filters in columns

Initial Setup

This section goes through the steps of building a Kintone App and the required settings before implementing the Handsontable customization.

Set up the Field Settings

First, create a new App.

Set the fields in the sample App as follows.

Set up a Custom View

1. Navigate to the App settings, and click on the View tab.

2. Click the [+] button to create a new view, and choose the Custom View option. Take note of the View ID as it is needed for the JavaScript code later introduced in this article.

3. On the HTML Code option, enter the following HTML and click Save.

   1	<div id="sheet"></div>

4. Click on Update App to apply the changes.

Refer to the following article for more details on custom views:

Create a Custom View

Set up the JavaScript/CSS Settings

This tutorial uses the commercial version of Hansontable (v12.4.0). Download the JavaScript/CSS files from the official Handsontable site .

1. Navigate to the Download ZIP tab and download the ZIP file

2. Extract the ZIP file.

3. Navigate to the Kintone App's settings, and click on JavaScript and CSS Customization.

4. Click the Add File button to add the following files from the extracted Zip file, and click Save:

   • JavaScript
     /handsontable/dist/handsontable.full.min.js

   • CSS
     /handsontable/dist/handsontable.full.min.css

5. Click on Update App to apply the changes.

For more details on how to upload JavaScript/CSS files to an App, refer to the Customize the app using JavaScript and CSS help article

Input Data

Manually enter a few records of sample data. The customization in this article will convert the data into a spreadsheet-like interface with Handsontable's advanced features.

Implement the Handsontable Customization

This section explains how to use JavaScript to implement the spreadsheet-like interface with the row parent-child feature.

Prepare the following JavaScript code in a text editor and upload the file into the JavaScript settings of the App with reference to the Uploading JavaScript and CSS files article. Note that the new JavaScript file needs to be located under the JavaScript library because the files are read sequentially, starting from the top file.

For View ID, enter the View ID noted from the Set up a Custom View section.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

(() => {

  'use strict';

  // Event Handler
  kintone.events.on(['app.record.index.show'], (event) => {
    if (event.viewId !== {INSERT_VIEWID_HERE}) return;

    const container = document.getElementById('sheet');
    container.style.marginTop = '10px';
    container.style.marginLeft = '10px';

    // Initialize Handsontable
    new Handsontable(container, {
      data: event.records.map(row => {
        return {
          'Record Number': row.record_number.value,
          'Company Name': row.company_name.value,
          'Person in Charge': row.person_in_charge.value,
          'Date': row.date.value,
          'Success Rate': row.success_rate.value,
          'Product Name': row.product_name.value,
          'Day of Visit': null,
          'Visit Details': null,
          __children: row.table.value.map(trow => {
            return {
              'Day of Visit': trow.value.day_visit.value,
              'Visit Details': trow.value.visit_details.value,
            };
          })

        };
      }),
      // column headers
      colHeaders: ['Record Number', 'Company Name', 'Person in Charge', 'Date', 'Success Rate', 'Product Name', 'Day of Visit', 'Visit Details'],
      // turn on nested rows option
      nestedRows: true,

      // other settings
      licenseKey: 'non-commercial-and-evaluation',
      filters: true,
      height: 'auto',
      contextMenu: [],
    });

    return event;
  });
})();

In this sample, the record is displayed by retrieving event.records from the record list page's event object. An array of objects are created with event.records.map and sent to the data object in Handsontable (line 15). Note that the names must match the headers specified in colHeaders (line 35).

To attach child rows, an array of fields in the table are passed to __children (line 25). Additionally, nestedRows is set to true (line 37).

Since this is for evaluation purposes, licenseKey is specified as non-commercial-and-evaluation (line 40).

The result of this sample is as shown below.

About the Commercial Version of Handsontable

The Commercial license allows users to use the latest Handsontable features. End-users using the developed product will not need to pay for the license. The Commercial license also comes with premium support.

For details on the plan, see the Handsontable pricing page .

Features

Here are some features that differ from the Community version.

Column menu

Enables a configurable drop-down menu for the table's column headers.

Nested headers

Enables multiple levels of nested column headers to group columns.

Column filter

Enables filtering of data by values or by a set of conditions. Filters can be customized to allow more complex filtering.

Row sorting

Allows more control over sorting. The following example shows that the sort feature is turned off for the "Brand" column.

Row parent-child

The feature introduced in this article that enables nested rows. Reflecting the parent-child relationship of the data in the table makes the table easier to read.

CSV export

Enables exporting of data to CSV format.

Integration with React/Vue/Angular

The package can be integrated with React / Vue / Angular .

References

• Create an Excel-like Custom View
• Insert and Delete rows from an Excel-like Custom View