dhtmlxSpreadsheet 3.1: Import and Export to Excel, Number Formatting, and More

| Leave a comment

We continue to gradually enhance our JavaScript components – and now it’s the turn of dhtmlxSpreadsheet to receive new powers and abilities.
javascript spreadsheet v3.1

The JavaScript spreadsheet was completely updated 4 months ago. The recent minor v3.1 brings out several features, which are essential for conveniently working with data:

  • Export of data from Spreadsheet to Excel
  • Import of data from Excel to Spreadsheet
  • Number formatting
  • Auto filling of cells
  • Hotkeys behavior in a range of cells

Before we go over the release details – download the latest trial version of JavaScript spreadsheet 3.1 to test it on the fly.

Import and Export to Excel

The current update enables end users to import Excel data into a web spreadsheet component as well as export data from Spreadsheet to Excel.
Export and import of Excel data into SpreadsheetCheck the samples with import of Excel data into Spreadsheet and export of Spreadsheet data to Excel >

The export and import operations became possible due to our two new WebAssembly-based libraries Excel2Json and Json2Excel. These libraries were developed by our team as open-source tools for converting Excel files into the JSON format and vice versa. They make use of the Rust programming language and WebAssembly.

In order to use the export and import of Excel files into your JavaScript spreadsheet, you only need to take 3 steps:
1) Install the corresponding library for export and import
2) Specify the necessary options in the Spreadsheet configuration and link the component to the installed libraries
3) Apply the related API methods

Read a detailed instruction on the import of data to Excel and export of data to Excel in the documentation.

Number Formatting

V3.1 comes out with an ability to set different formats for numeric values in cells. Spreadsheet default number formats are common (“”), currency (“$#,##0.00”), number (“#,##0.00”), and percent (“#,##0.00%”).

Applying a number format to a value is easy with the setFormat method:

// applies the currency format to the cell A1
spreadsheet.setFormat("A1","currency");

Besides, there is the getFormat method, which allows you to define what number format is applied for a particular cell:

var format = spreadsheet.getFormat("A1");
// ->"currency"

On top of all, you can modify the default number formats or even create custom formats via the formats configuration option. For example, you can change the name of the predefined currency format for “U.S. dollar” and add two new formats with ids – “euro” and “franc”:

var spreadsheet = new dhx.Spreadsheet(document.body, {          
    formats: [
        {
            name: "U.S. Dollar",
            id: "currency",
            mask: "$#,##0.00"
        },
        {
            name: "Euro",
            id: "euro",
            mask: "[$€]#.##0,00",
            example: "1000.50"
        },
        {
            name: "Swiss franc",
            id: "franc",
            mask: "[$CHF ]#.##0,00"
        }
    ]
});

Custom number formattingCheck the sample >

By the way, our Spreadsheet component automatically detects the format of content in cells due to the autoFormat configuration option enabled by default. However, you are free to disable it if you want.

Automatic Filling of Cells with Content

From now on, there is no more need for tediously typing in data by hand, as our JavaScript Spreadsheet is able to fill cells with data automatically. The autofill is based on a range of widespread patterns. For instance, in order to get a sequence of consecutive numbers from 1 to n, you just need to enter 1 and 2 into the first two cells and drag the fill handle to grab and autofill as many cells as you need. The spreadsheet can also automatically create sequences of odd and even numbers. Moreover, there is a pattern for making a series of numbers with letters.

Auto filling of cellsCheck the user guide >

Enhanced Hotkeys Navigation

We’ve improved hotkeys behavior for easier spreadsheet navigation inside a range of cells:

  • Enter – moves the selected cell down within the selected range
  • Shift + Enter – moves the selected cell up within the selected range
  • Tab – moves the selected cell right within the selected range
  • Shift + Tab – moves the selected cell left within the selected range

Hotkeys behavior in a range of cellsCheck the user guide >

So the next step is to download dhtmlxSpreadsheet 3.1 free trial version and test all the new features yourself!

We’re looking forward to your feedback about the release – share your thoughts in the comments below or write to us: info@dhtmlx.com

Current clients are invited to download the latest version in their Client’s Area.

Related Materials:

Leave a Reply