We are delighted to announce the official release of DHTMLX Spreadsheet v4.3. Despite its minor status, this update of our JavaScript spreadsheet library comes with a pack of new features to boost your abilities in data management. First of all, it includes a highly requested select editor and the ability to freeze table rows. It has also become possible to enter multiline content into a cell, sort spreadsheet data, and apply the time format for your data. Apart from that, our team has also extended the list of available Excel formulas and designed a new API approach to tracking and handling spreadsheet events.
Evaluate a new version of DHTMLX Spreadsheet >
Let’s get acquainted with new capabilities delivered in v4.3 in more detail.
Select Editor with Cell Validation
Populating tables with repetitive information by hand can be a time-consuming and tedious task. Therefore, we decided to help you automate this process by introducing a select editor providing a drop-down list of options to save time on manual entry. The editor is equipped with cell validation for reducing human error by highlighting the cell when an invalid value is entered.
The editor is brought into play with code or right from the UI.
In terms of programming, the select editor with validation can be specified via the new editor attribute of a cell object.
// add drop-down lists to cells
{ cell: "C2", value: "Banana", editor: {type: "select", options: ["Banana", "Orange", "Pineapple", "Apple"]} },
// more data
];
Check the details on the documentation page dedicated to the updated parse() method.
Another way to activate validation for certain cells and add an array of options in the editor, you need to utilize the setValidation() method with corresponding cell and options parameters.
If it is necessary to disable the editor with validation, you need to set null / 0 / false / undefined as a value for the options parameter.
There are two ways for creating a drop-down list with validation from the UI:
- By typing list items manually
- By using a range of predefined items
End-users can directly access this functionality by selecting the “Data validation” option in the “Data” menu of the spreadsheet. To remove a drop-down list with validation from one or several cells, users will have to click on the “Remove Validation” button in the “Data validation” menu.
Frozen Rows
The ability to freeze particular table panes is crucial for allowing end-users to conveniently compare and analyze worksheets with a lot of data. That is why the possibility to fix spreadsheet columns provided in v4.0 is now complemented with the similar capability for rows. With this novelty, you can keep a specific number of rows at the top of the spreadsheet always visible, while the rest of table rows remain scrollable.
topSplit: 4, // the number of rows to "freeze"
// other config parameters
});
To make this feature workable, you have to use the new topSplit property and indicate the number of rows to be frozen as its value.
Multiline Cell Content
One more useful thing offered in v4.3 is the ability to make a long text appear on multiple lines i.e. wrap text in a cell. This feature will help end-users see all the text even when its cell isn’t active. It can be actuated via the “Wrap” option that appears after clicking on the “Text wrapping” button in the toolbar or in the Format menu. If users need only a part of the content to be visible, they should choose “Clip” in the same menu.
Check the sample >
When end-users change the column’s width, text wrapping will be automatically adjusted.
Sorting Columns
When working with complex Excel-like tables, it is great to have an opportunity to quickly manipulate data using various operations. The new version of our Spreadsheet brings in the ability to sort data in columns via API or UI.
Programmatically, this functionality is enabled with the sortCells() method with two required parameters. The cell parameter specifies the range of cells to be sorted via their ids. The dir parameter determines the direction of sorting and takes the following values:
- “1” for ascending (0 to 9, and/or A to Z) order,
- “-1” for descending (9 to 0, and/or Z to A) order.
topSplit: 1
});
// sorts data on the first sheet
spreadsheet.sortCells("B2:B11", -1);
// sorts data on several sheets
spreadsheet.sortCells("Income!B2:B11,Report!B2:B11, Expenses!C2:C11", 1);
Now, end-users can also sort spreadsheet data on the fly in the following ways:
- By column
- By range
The sorting feature is put into practice via the context menu called with a right click on a cell or via the “Data” menu of the spreadsheet. If it is necessary to exclude any of top rows from sorting, you’ll need to apply the topSplit property.
New Excel Functions
We continue strengthening your capacity to manipulate data with new 70+ Excel functions that work out-of-the-box in JavaScript spreadsheets built with DHTMLX. Starting from v4.3, our Spreadsheet component also supports lookup functions such as LOOKUP, XLOOKUP, VLOOKUP, HLOOKUP, MATCH, XMATCH, INDEX. These formulas are essential when searching for a specific value across many rows and columns. In addition, we’ve also extended the list of predefined Date, Financial, Math, and String functions.
All new formulas are provided in the corresponding section of our documentation and marked with the “added in v4.3” label.
Time Format
In v4.3, the list of predefined number formats provided in our library has been expanded with the time format. Now you can display numeric values in cells of the JavaScript Spreadsheet using 12- or 24-hour time formats. The required time format is set via timeFormat property:
timeFormat: number // 12 | 24
// other config parameters
});
The time format is now available together with other format options in the corresponding menu of the spreadsheet. We’ve also incorporated the TimePicker widget to make it easier for users to manipulate the time parameter. By default, time in cells is shown in the 12-hour format.
Other Improvements
Our development team also designed a new way of working with Spreadsheet events. It is based on a pair of the beforeAction/afterAction events intended to make your code more compact and readable.
if (actionName === "addColumn") {
console.log(actionName, config);
return false;
},
// more actions
});
spreadsheet.events.on("afterAction", (actionName, config) => {
if (actionName === "addColumn") {
console.log(actionName, config)
},
// more actions
});
These events are triggered before an action is performed and help to clarify the nature of this action. As a result, you won’t need to apply before- and after- events each time when some changes occur in the JavaScript spreadsheet.
At the same time, you can still use the traditional approach since all existing Spreadsheet events remain functional:
console.log("A new column is added", cell);
});
spreadsheet.events.on("beforeColumnAdd", function(cell){
console.log("A new column will be added", cell);
return true;
});
The full list of spreadsheet actions is available in this section of our documentation.
There is one more novelty that contributes to a better user experience with DHTMLX Spreadsheet. From now on, you can import and export spreadsheet data to a JSON file.
And lastly, we would like to inform you that v4.3 is the last version of our Spreadsheet library that works in Internet Explorer. In the next versions of our product, the support for this browser will be discontinued.
Look through all the new stuff prepared by our team for this release on the “What’s new” page.
To move from theory to practice right away, you can download a free 30-day trial version of our Spreadsheet component.
If you are already one of the DHTMLX clients, take the opportunity to access DHTMLX Spreadhseet v4.3 via your Client’s Area.
Stay tuned for more DHTMLX releases in the coming months!