SpreadsheetInput Control

Description

Displays a spreadsheet style grid for editing values.

Discussion

The SpreadsheetInput Control (more generally called the Spreadsheet Control) allows you to display a spreadsheet style grid of cells in which the user can enter/edit data. The spreadsheet columns can be resized and also reordered.

For example, in the image below, a SpreadsheetInput Control has been defined with 3 columns and 5 rows. The user can use keyboard navigation (tab, shift-tab, up and down ) to move between the cells in the control.

When the UX is submitted, the data in the control is submitted as a JSON string.

The Spreadsheet control is well-suited for desktop-style, heavy-duty data entry. For a faster, modern web and mobile application, consider using the newer Light Weight List.

For example, if the control had been filled in as shown below:

then, when the UX was submitted, the data submitted for the SpreadsheetInput control would be as string with this value:

"[{"field1":"alpha","field2":"beta","field3":""},{"field1":"","field2":"","field3":"gamma"}]"

To, add a SpreadsheetInput control to a UX, select the More.. control from the Data Controls section of the UX toolbox.

Then select the SpreadsheetInput control from the list.

To configure the control, click the smart field for the Control properties.

This will open the genie where you can configure the control.

The most important property to configure is the Column definitions property. This property allows you to define the columns shown in the SpreadsheetInput control.

Click the smart field to open the editor, as shown in the image below.

You can add columns one at a time by clicking the Add Column button. You can click the Quick Select using Genie hyperlink to add columns to match the fields in a SQL table.

The onNavigate property allows you to specify Javascript code to execute when a cell in the control gets focus.

Defining Row Prefixes and Suffixes

You can define row prefixes (i.e. row labels) and row suffixes so that the control looks even more like a spreadsheet. The prefix displays at the left side of each row and the suffix displays at the right edge of each row. For example, in the image below a prefix has been defined to show the row number.

Here is how the control is configured to show the prefix:

• The Has row prefix (label) property is checked.

  The Row prefix HTML is a defined using Javascript code that can reference row - the zero based row number and data - the array of data with which the control is populated. To show the row number as the row prefix, the Javascript for the Row prefix HTML is set to:

  return (row + 1);

• The Width property is set to 50px to set the width of the prefix column.

  The Style property is set to font-size: 75%; text-align: right; to make the font slightly smaller than the text in the input control and also to right align the text in the row labels.

Methods

Since the SpreadsheetInput control is a standard UX Data Control, you can use the {dialog.object}.setValue() method to populate the cells in the control with data and the {dialog.object}.getValue() method to get the data in the control. When you use these methods, the value is a JSON string.

For example, if the SpreadsheetInput control has 3 columns (field1, field2 and field3), you could use the following Javascript to populate the control:

var _data = [
    {field1: 'alpha', field2: 'beta'},
    {field2: 'gamma', field3: 'delta'},
    {field3: 'epsilon'}
];

var _string = JSON.stringify(_data);
{dialog.object}.setValue('mySpreadsheetControl',_string);

• .setColumnAndPopulate() Method

  You can also dynamically change the columns shown in the control to match the columns in some data with which you want to populate the control. For example, say that the SpreadsheetInput control was initially configured to show a single column (say 'Field1') and you wanted to populate it with the following data:

  [
      {firstname: 'John', lastname: 'Smith', city: 'London'},
      {firstname: 'Harry', lastname: 'Jones', city: 'Boston'},
      {firstname: 'Winston', lastname: 'Flowers', city: 'Harare'}
  ]

• You can call the control's .setColumnsAndPopulate() method.

  For example:

  var obj = {dialog.object}.getControl('mySpreadsheetControl');
  
  var _data = [
      {firstname: 'John', lastname: 'Smith', city: 'London'},
      {firstname: 'Harry', lastname: 'Jones', city: 'Boston'},
      {firstname: 'Winston', lastname: 'Flowers', city: 'Harare'}
  ]
  
  obj.setColumnsAndPopulate(_data);

• The columns shown in the control will now be 'firstname', 'lastname' and 'city'.

• .getState() and .setState() Methods

  Since the columns in the Spreadsheet input control are resizeable and reorderable, you might want to capture the state of the Spreadsheet input control's column layout so that you can restore it the next time the user runs the component.

  For example

  var obj = {dialog.object}.getControl('myspreadsheetcontrol_1');
  'get the spreadhseet control state as a JSON string
  var json = obj.getState();
  
  'store in local storage
  localStorage.setItem('spreasheet_01_State',json);

• Then, to restore the state

  var json = localStorage.getItem('spreasheet_01_State');
  var obj = {dialog.object}.getControl('myspreadsheetcontrol_1');
  obj.setState(json)

• Enhanced .getValue() and .setValue() Methods

  The .getValue() and .setValue() methods have been enhanced when applied to a pre-populated spreadsheet control.

  .getValue(spreadsheetControlName)

  Returns the primary key of the currently selected row. If the primary key has multiple columns, the returned value is '|||' delimited (e.g. '10248|||51').

  .getValue(spreadsheetControlName::columnName)

  Returns an array of data from the specified column in the spreadsheet control.

  .getValue(spreadsheetControlName::columnName::row)

  Returns the value of a specified cell where row is the zero-based row number.

  .setValue(spreadsheetControlName::primaryKey, primaryKeyValue)

  Sets the selected row to the row with the specified primary key. If the primary key has multiple columns, specify the primary key as a '|||' delimited value (e.g. '10248|||51').

  .setValue(spreadsheetControlName::rowNumber, rowNumber)

  Sets the selected row to the specified rowNumber.

  .setValue(spreadsheetControlName::rcolumn::columName, values)

  Sets the values in the specified column. values is a comma-delimited list of values. For example, if the spreadsheet shows 10 rows, the first value is inserted into row 0, the second into row 1, etc. To include a comma in a value, encode it as {comma}.

  .setValue(spreadsheetControlName::rcolumn::columName::row, value)

  Sets the value in a specific cell.

Linked Spreadsheets (Parent-Child)

When you define pre-populated spreadsheet controls, you can link them in a parent-child relationship.

For example, say you have a UX component with two spreadsheet controls - one based on (say) Northwind Customers and the other based on (say) Northwind Orders. When you select a row in the Customers spreadsheet, you would like the Orders spreadsheet to show only the orders for the selected customer.

To define linked spreadsheets, open the builder for the child spreadsheet and check the Has parent spreadsheet property. Then specify the name of the parent spreadsheet in the Parent spreadsheet name property.

The Linking field name property need only be specified if the name of the linking field in the child spreadsheet is not the same as the name of the primary key field in the parent spreadsheet.

Linked Spreadsheets

In this video we show how to define linked (i.e. parent-child) spreadsheet controls.

• Watch video

Editing Data in a SQL Table

The Spreadsheet control can be configured to populate directly from a SQL table and perform CRUD (Create, Read, Update, Delete) operations. This is useful for creating efficient data entry interfaces.

To use this feature, select Pre-populated in the Type property of the control. Then, configure the Pre-populate Settings:

Connection string

The connection string to the database containing the table.

Table

The name of the table to populate the control.

Primary key

The primary key field(s) of the selected table.

Fields

(Optional) The fields from the selected table to display. If blank, all fields are shown.

Filter

(Optional) Filter the records shown. Can reference Arguments.

Order

(Optional) Sort order for the records.

Width conversion factor

Multiplier for column width calculation based on field size.

Page size

Number of records per page.

Viewport size

Number of rows displayed by the Spreadsheet. If fewer than page size, a scroller is shown.

Allow CRUD operations

Enable Create, Read, Update, Delete operations on the data.

The builder dialog is shown below:

Editing Data in a SQL Table

In this video series, we show how a Spreadsheet control can be populated with data from a SQL table and how you can perform CRUD operations on the data.

• Watch Video 1
• Watch Video 2
• Watch Video 3
• Watch Video 4
• Watch Video 5

Conditional Styling

You can define a Javascript function to dynamically return the style for a cell in the spreadsheet. The Javascript function can reference the data in the spreadsheet, enabling conditional styling (e.g., highlighting negative values in red).

Conditional Styles

In this video we show how you can define conditional styles. For example, you might want to display values that are negative in red.

• Watch video

Pre-populated Spreadsheet Features

When the Spreadsheet control is pre-populated, several built-in features are enabled:

Filtering

Filter records by clicking the filter icon in the column heading.

Sorting

Sort data by clicking the column heading. First click sorts ascending, second descending, and third removes the sort.

Alternating Row Colors

Specify that the spreadsheet should be rendered with alternating row colors for better readability.

Advanced Search

You can add an Advanced Search control to the UX component to search records within the Spreadsheet.

For example, you can specify that the spreadsheet should be rendered with alternating row colors.

When you create a pre-populated spreadsheet control and you specify a table and you select fields (as opposed to leaving the fields property blank, which selects all fields), you can set properties on individual fields (i.e. columns) in the spreadsheet.

When you click the smart field for Column definitions, the following dialog is shown.

Advanced Features

Recent enhancements allow for greater flexibility in managing large datasets and dynamic structures within the Spreadsheet Control. Key features include:

Dynamic Columns

Users can be permitted to add columns at runtime via the "Allow column add" property.

Header Buttons

Javascript actions (such as "Delete Column") can be attached to buttons embedded directly in column headers.

Virtualization

For large datasets (e.g., thousands of rows), "virtualization" can be enabled. This renders only a small subset of rows (e.g., 10) in the DOM at any one time, while providing a scroller to navigate the full dataset efficiently.

Dirty State Tracking

When data is edited, a special `__dirty` property is added to the modified row's JSON object and set to `true`. This allows server-side logic to easily identify and process only the records that have changed.

Additionally, the structure of the spreadsheet can be dynamically updated to match incoming data (JSON or CSV) using the `setSpreadsheetLayoutAndPopulate` method.

Dynamic Columns, Virtualization, and Data Handling

A comprehensive overview of dynamic column manipulation, virtualization for large datasets, and analyzing the JSON submission structure.

• Watch Video

Download Component

Videos

Using the Spreadsheet Input Control

In certain applications where a user needs to input data quickly, a spreadsheet style data entry control can be very efficient.

In this video we show how you can add a Spreadsheet Input control to a UX component.

• Watch Video

Download Component

See Also

• Light Weight List