DHTMLX Spreadsheet 4.2 with 130+ New Functions, Boolean Operators, Date Format, Row Resizing, and Much More

It is time to make one more serious step in the evolution of our JavaScript Spreadsheet component. So meet DHTMLX Spreadsheet v4.2. This release offers plenty of novelties that will help you to edit and format data more efficiently. It includes a collection of new functions, support for logical (boolean) operators, date format, the possibility to change the height of rows and align text within them, and numerous API upgrades.

Download the latest version 4.2 of DHTMLX Spreadsheet >

Let us provide you with a deeper insight into this update.

New Functions

Introduced in v4.0, built-in functions have quickly become one of the most popular functionalities among our clients who use DHTMLX Spreadsheet in their web applications. That is why v4.2 offers higher diversity of Excel functions.

For version 4.2, we significantly increased the number of math formulas that will help to execute a greater range of arithmetical operations. We added functions that are used in trigonometric equations (SIN, COS, TAN, COT, SEC, etc.), logarithmic calculations (LN, LOG, LOG10), estimation of standard deviation based on various criteria (STDEV, STDEVA, STDEVP, STDEVPA), and more.

We also broadened the choice of available string functions. For example, now end-users can put to use such functions as SEARCH for detecting the location (as a number) of one string within another, EXACT for comparing two text strings, SUBSTITUTE for replacing an existing piece of text with a new one, and many other text formatting options.

The financial functions will come in handy when preparing financial reports or budget forecasts. They allow performing various financial calculations, including the net present value of an investment (NPV), asset depreciation (DB), nominal annual interest rate (NOMINAL), internal rate of return (IRR), etc.

The RegEx (regular expressions) functions can be extremely useful in working with text. A regular expression is a special text string that helps to create patterns for matching, locating, and managing textual data. Our release includes regular expressions for finding and replacing specific information (REGEXREPLACE), checking if a text string matches a regular expression (REGEXMATCH), and extracting the part of the string that matches the given pattern (REGEXEXTRACT).

With new information functions such as ISBLANK, ISTEXT, ISNUMBER, it is really easy to check on the content and formatting of any cell in a spreadsheet.

In general, the list of supported formulas in the DHTMLX Spreadsheet library has been extended to 171. All of the spreadsheet functions are fully compatible with Excel and Google. You can find all of the reviewed and other new functions in the snippet below and choose those that are needed for your project.

Boolean Operators

Starting from v4.2, DHTMLX Spreadsheet provides built-in support for boolean operators (also known as logical). In essence, they are basic mathematical symbols (=,>,<). They are utilized for comparing values stored in two or more cells and returning either TRUE or FALSE as a result. You can also create more elaborate formulas by combining boolean operators with newly added logical functions (AND, OR, NOT, IF, etc.). Thus, you will be able to carry out more than one comparison or test multiple conditions.
Check the sample >

Date Format

We continue expanding the list of number formats available in our JavaScript Spreadsheet. Apart from the existing default number formats, now it also supports the date format. It enables you to specify the way how dates should be shown in cells of a spreadsheet built with DHTMLX.

const spreadsheet = new dhx.Spreadsheet("spreadsheet", {
    dateFormat: "%D/%M/%Y"
});

For this purpose, you have to use the dateFormat property. By default, dates are shown in the %d/%m/%Y format. The full list of symbols for creating date formats can be found in the documentation.

As we have mentioned earlier, the release includes an array of functions allowing you to manipulate various forms of data within a spreadsheet, including date parameters. They range from basic DAY, MONTH, and YEAR to more complex functions such as NETWORKDAYS for showing the number of workdays during the specified period of time, excluding weekends. When working with the new date format, you can make use of the integrated mini-calendar that facilitates the practical application of the date format.

DHTMLX Spreadsheet 4.2 - date functionsCheck the sample >

Changing the Height of Particular Rows

When working with tabular data, it can be useful to have an opportunity to resize sheets. Therefore, we decided to complement the DHTMLX Spreadsheet with the possibility to change the height of rows right from the UI. As a result, end-users can easily set the size of spreadsheet rows to their liking via drag-and-drop.

DHTMLX Spreadsheet -Row resizing
Check the sample >

You can also benefit from updates in the parse() and serialize() methods. New rows and cols attributes make it possible to save the current state of row height and column width for each sheet separately. Moreover, sheets can be easily exported/imported with the necessary sizes of rows/columns.

Vertical Align Button

Once you start using the new row resizing option mentioned above, you will certainly need a tool to vertically adjust the position of cell data. No worries, we took care of it. The toolbar of our spreadsheet component now has a new button serving for vertical alignment.

DHTMLX Spreadsheet  4.2- Vertical Align
Check the sample >

Thus, the Align section of the toolbar is split into two parts – Horizontal align and Vertical align. With the latter option, end-users can vertically align content at the top, center, and bottom parts of a cell. The center alignment is enabled by default. Moreover, we’ve also updated locale options for the Align section.

Other Improvements

The release also brings along a range of API enhancements. For example, we added two methods for clearing a JavaScript spreadsheet – clear() and clearSheet(). While the clear() method helps to remove all spreadsheet content, the clearSheet() method clears a specific sheet. These methods come with special events (beforeClear, afterClear, beforeSheetClear, afterSheetClear) that are actuated before and after the clearing. There is a chance to remove the selection from particular cells of a spreadsheet using the removeSelectedCell() method. You can also take advantage of the setActiveSheet() method to set the active sheet by its id.

Go over all the updates and fixes provided in DHTMLX Spreadsheet v4.2 by visiting the “What’s new” section of our documentation.

We hope that the new version of the DHTMLX Spreadsheet will be really useful in your web projects. If you are eager to share thoughts on this minor update or offer any suggestions on how to make our product even better, feel free to use the comment section below.

Take a chance to test the DHTMLX Spreadsheet v4.2 by downloading a free 30-day trial version.

If you are a current DHTMLX user, get access to the latest version of our JS Spreadsheet component via your Client’s Area.

Stay tuned for more updates on DHTMLX JavaScript libraries and UI widgets!

Related Materials:

Advance your web development with DHTMLX

Gantt chart
Event calendar
Diagram library
30+ other JS components