Let\u2019s get acquainted with new capabilities delivered in v4.3 in more detail.<\/p>\n
Select Editor with Cell Validation<\/h5>\n
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<\/a> 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. <\/p>\n The editor is brought into play with code or right from the UI.<\/p>\n In terms of programming, the select editor with validation can be specified via the new editor<\/strong> attribute of a cell<\/strong> object.<\/p>\n Check the details on the documentation page<\/a> dedicated to the updated parse()<\/strong> method.<\/em><\/p>\n Another way to activate validation for certain cells and add an array of options in the editor, you need to utilize the setValidation<\/a>()<\/strong> method with corresponding cell<\/strong> and options<\/strong> parameters.<\/p>\n If it is necessary to disable the editor with validation, you need to set null \/ 0 \/ false \/ undefined<\/em> as a value for the options<\/strong> parameter.<\/p>\n There are two ways for creating a drop-down list with validation from the UI:<\/p>\n 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.<\/p>\n 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<\/a>, while the rest of table rows remain scrollable. Check the sample ><\/a><\/p>\n To make this feature workable, you have to use the new topSplit<\/a><\/strong> property and indicate the number of rows to be frozen as its value.<\/p>\n 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<\/a>. This feature will help end-users see all the text even when its cell isn\u2019t 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. When end-users change the column\u2019s width, text wrapping will be automatically adjusted.<\/p>\n 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<\/a> via API or UI. <\/p>\n Programmatically, this functionality is enabled with the sortCells<\/a>()<\/strong> method with two required parameters. The cell<\/strong> parameter specifies the range of cells to be sorted via their ids. The dir<\/strong> parameter determines the direction of sorting and takes the following values:<\/p>\n Now, end-users can also sort spreadsheet data on the fly in the following ways:<\/p>\n 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\u2019ll need to apply the topSplit<\/a><\/strong> property.<\/p>\n 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\u2019ve also extended the list of predefined Date, Financial, Math, and String functions.
\n \/\/ add drop-down lists to cells<\/span>
\n {<\/span> cell:<\/span> "C2"<\/span>,<\/span> value:<\/span> "Banana"<\/span>,<\/span> editor:<\/span> {<\/span>type:<\/span> "select"<\/span>,<\/span> options:<\/span> [<\/span>"Banana"<\/span>,<\/span> "Orange"<\/span>,<\/span> "Pineapple"<\/span>,<\/span> "Apple"<\/span>]<\/span>}<\/span> }<\/span>,<\/span>
\n \/\/ more data<\/span>
\n]<\/span>;<\/span><\/div><\/div>\n\n
![\"Spreadsheet](\"https:\/\/dhtmlx.com\/blog\/wp-content\/uploads\/2022\/05\/image3.gif\")
Check the sample ><\/a><\/p>\n\n
![\"Spreadsheet](\"https:\/\/dhtmlx.com\/blog\/wp-content\/uploads\/2022\/05\/image2-1.gif\")
Check the sample ><\/a><\/p>\nFrozen Rows<\/h5>\n
\n![\"Spreadsheet](\"https:\/\/dhtmlx.com\/blog\/wp-content\/uploads\/2022\/05\/image7.gif\")
<\/p>\n
\n topSplit:<\/span> 4<\/span>,<\/span> \/\/ the number of rows to "freeze"<\/span>
\n \/\/ other config parameters<\/span>
\n}<\/span>)<\/span>;<\/span><\/div><\/div>\nMultiline Cell Content<\/h5>\n
\n![\"DHTMLX](\"https:\/\/dhtmlx.com\/blog\/wp-content\/uploads\/2022\/05\/image6.gif\")
Check the sample ><\/a><\/p>\nSorting Columns<\/h5>\n
\n
\n topSplit:<\/span> 1<\/span>
\n}<\/span>)<\/span>;<\/span>
\n
\n\/\/ sorts data on the first sheet<\/span>
\nspreadsheet.sortCells<\/span>(<\/span>"B2:B11"<\/span>,<\/span> -<\/span>1<\/span>)<\/span>;<\/span>
\n
\n\/\/ sorts data on several sheets<\/span>
\nspreadsheet.sortCells<\/span>(<\/span>"Income!B2:B11,Report!B2:B11, Expenses!C2:C11"<\/span>,<\/span> 1<\/span>)<\/span>;<\/span><\/div><\/div>\n\n
![\"DHTMLX](\"https:\/\/dhtmlx.com\/blog\/wp-content\/uploads\/2022\/05\/image1.gif\")
Check the sample ><\/a><\/p>\n\n
![\"DHTMLX](\"https:\/\/dhtmlx.com\/blog\/wp-content\/uploads\/2022\/05\/image5.gif\")
Check the sample ><\/a><\/p>\nNew Excel Functions<\/h5>\n
\n