Spreadsheet Deep Customizations

This tutorial addresses examples that require a highly customized spreadsheet. The Spreadsheet form element in Joget uses the Handsontable library, specifically version 14.3.0 for Joget DX 8. The library offers a wealth of plugins and APIs to customize cell appearance, selection, dynamic data, validation, and much more.

You can add more spreadsheet properties that are available in the library documentation. The configuration is formatted as JSON. Usually, a single object and value in Custom Settings would suffice for simple configurations. For more complex configurations, combine this with a Custom HTML form element to complete the functionality.
Below are examples of customization referenced from the documentation Handsontable.

Limit Cell Selection

You can limit the cell(s) that can be selected to only a single cell, using the following code:

{
    selectionMode: 'single'
}

Copy and paste the code above into Custom Settings. This configuration restricts the selection to a single cell, which is useful for forms where multiple cell selections could cause errors or confusion.

For more information, see the selectionMode section.

Custom Invalid Cell Style

You can also change the appearance of a cell if the value does not match regex validation (regex configurable in spreadsheet properties).

1. Add a Custom HTML form element to write a simple class style. Use the !important notation if the style is being overridden.

   <style>
       .invalidCellCustom {
           background: pink !important;
       }
   </style>

2. Copy and paste this code snippet into Custom Settings.
   

   {
       invalidCellClassName: 'invalidCellCustom'
   }

This customization highlights invalid cells, making it easier for users to identify and correct errors. For more information, see the invalidCellClassName section.

Get Spreadsheet Handsontable Instance by Form Element ID

To obtain the spreadsheet, a hands-on instance is needed to use core functions. Follow the steps below.

1. Copy and paste this code snippet into Custom Settings.
   

   {
       "afterInit": function() {
           var hot = this;
           $(hot.rootElement).data("hot", hot);
       }
   }

2. Use a Custom HTML form element to get the hot instance.
   

   <script>
       $(function() {
           var hot = FormUtil.getField("_yourSpreadsheetFormElementIdHere_").data("hot");
           // Example usage:
           // console.log(hot.getSettings());
           // hot.setDataAtRowProp(0, '_yourcellColumnIdHere_', '_myNewValue_');
       });
   </script>

Application: This allows you to access and manipulate the spreadsheet instance using core Handsontable functions, enabling advanced interactions and data handling.

For more information, see the Core documentation.

Add New Row Using JavaScript

To Programmatically add new rows to the spreadsheet, use the code below.

<script>
    var col = hot.countRows(); 
    hot.alter('insert_row', col, 1);   
    hot.setDataAtCell(col, 0, '-Name-');
    hot.setDataAtCell(col, 1, '-Surname-');
    hot.setDataAtCell(col, 2, '-Age-');
</script>

This script allows you to dynamically add rows to the spreadsheet, which is useful for forms that need to grow based on user input or external data.

These examples demonstrate how to leverage the extensive customization capabilities of the Handsontable library to enhance your Joget forms. Applying these techniques allows you to create highly interactive and functional spreadsheets tailored to your specific needs.

Reference Fiddle

For a practical demonstration of how to add new rows using JavaScript in Handsontable, visit this example Fiddle.