Configuration

You can adjust the desired settings of dhtmlxSpreadSheet to meet your needs. The available configuration options allow you to limit the number of rows and columns, change the toolbar appearance and control the visibility of the menu and the editing bar. You can also initialize SpreadSheet in the readonly mode, if needed.

Toolbar

The toolbar of the SpreadSheet consists of several blocks of controls that can be changed according to your needs. By default, there are the following blocks of controls in the toolbar: "undo","colors","decoration","align","format","help":

Basic toolbar

You can add more blocks from the list: "lock", "clear", "rows", "columns", "file".

Extra buttons

The structure of toolbar can be adjusted via the toolbarBlocks configuration option of the component, which is an array with strings presenting the names of controls.

var spreadsheet = new dhx.Spreadsheet(document.body, {
    // full toolbar
    toolbarBlocks: [
        "undo", "colors", "decoration", "align", "lock", "clear", 
        "rows", "columns", "help", "format", "file"
    ]
});

Related sample:  Full toolbar

You can also specify your own structure of the toolbar by enumerating necessary elements in the toolbarBlocks array in the desired order, for example:

var spreadsheet = new dhx.Spreadsheet("cont", {
    toolbarBlocks: ["help","colors", "align", "decoration", "lock", "clear"]
});

Custom toolbar

Toolbar is highly customizable. You can add new controls, change the icons of controls and apply the desired icon pack.

Editing bar

Since the structure of SpreadSheet is flexible, you can switch on/off the editing bar to get the desired look and feel of the component. Use the editLine configuration option to hide/show the editing bar:

var spreadsheet = new dhx.Spreadsheet(document.body, {
    editLine: true
});

Related sample:  Disabled edit line

Number of rows and columns

When SpreadSheet is initialized, it has the initial configuration of grid which consists of 1000 columns and 25 rows. However, when this limit runs out, additional rows and columns are rendered automatically, so you don't need to add them. Nevertheless, you can specify the exact number of rows and columns in the grid, if you want to limit them. Use the colsCount and rowsCount options for this purpose:

var spreadsheet = new dhx.Spreadsheet(document.body, {          
    colsCount: 10,
    rowsCount: 10
});

Related sample:  Custom cells count

Menu

The menu of the SpreadSheet is hidden by default. You can switch it on/off via the corresponding configuration option menu:

var spreadsheet = new dhx.Spreadsheet(document.body, {
    menu: false
});

Related sample:  Menu

Read-only mode

It is also possible to enable the read-only mode to prevent editing of SpreadSheet cells via the readonly configuration option:

var spreadsheet = new dhx.Spreadsheet("cont", {
    readonly: true
});

Related sample:  Readonly mode

You can also customize the readonly behavior of SpreadSheet.

Auto detection of cell format

By default the format of a cell content is detected automatically. You can disable automatic detection of the cell format via the autoFormat config option:

var spreadsheet = new dhx.Spreadsheet(document.body, {          
    autoFormat:false
});

Read about number formatting in the related article.

Custom number formats for cells

There are 4 default formats that can be applied to the values of cells:

defaultFormats = [
    { name: "Common", id: "common", mask: "", example: "2702.31" },
    { name: "Number", id: "number", mask: "#,##0.00", example: "2702.31" },
    { name: "Percent", id: "percent", mask: "#,##0.00%", example: "27.0231" },
    { name: "Currency", id: "currency", mask: "$#,##0.00", example: "2702.31" }
];

You can redefine configuration of default formats or specify your own number format via the formats config option:

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"
        }
    ]
});

Related sample:  Custom formats

Check the details in the Number Formatting article.

Path to export/import modules

dhtmlxSpreadSheet provides the possibility to import/export data in the Excel format. The component uses WebAssembly-based libraries: Excel2Json and JSON2Excel for import/export of data.

After installing the necessary library, you need to set path to the worker.js file (either local or at CDN) via the corresponding configuration option - importModulePath or exportModulePath:

var spreadsheet = new dhx.Spreadsheet(document.body, {  
    importModulePath: "../libs/excel2json/1.0/worker.js"
    exportModulePath: "../libs/json2excel/1.0/worker.js"
});

All the details are given in the Data Loading and Export article.

Back to top