Data Components Export to Excel

You can export Webix data components to the Excel format. Note that there are some peculiarities for the following components:

Export is available both in Standard and Pro versions

To export data from a data component into an Excel document, you need to call the toExcel method. The method takes 2 parameters:

id - (string, object) the id or the object of the exported view,
config - (object) optional, a set of configuration options that define what data will be displayed in the Excel file.

For example, to export a list to an Excel document, you need to call the toExcel() method that takes the view object or its ID as a parameter:

webix.ui({
    view:"list",
    id: "mylist",
    // list configuration
});
 
webix.toExcel($$("mylist"));

The toExcel method returns all data specified in the dataset of a component, or in the columns parameter of the DataTable view. The data are exported into an Excel document with the "Data" name and the "Data" sheet name.

Related sample: Export to Excel

Exporting Data Offline

The dependencies which are used for providing export possibilities are taken from the Webix CDN online catalog. If you need to implement data export offline, you should complete the following steps:

1. Download the package with CDN-files from https://github.com/webix-hub/cdn-extras.

If you use Webix version 5.0 or later, use the file xlsx.core.styles.min.js.
For a version earlier than 5.0, choose the file xlsx.core.min.js.

2. Rename the folder to "extras" and move it to the desired directory.
3. set the path to the local CDN repository as in:

webix.env.cdn = "/local_folder";

Note that specifying the path to the local CDN repository has some peculiarities:

1) if you set the path relative to the current page, it should be specified like this:

webix.env.cdn = "../../../../local_folder";

2) the absolute path to the repository should be specified as follows:

webix.env.cdn = "http://localhost/local_folder";

Customizing Export

There are some common export customization settings that are also applicable to the toExcel method. There are also some specific settings described below.

Export API allows customizing export:

providing a custom filename and sheet name
defining a custom header, width or a template for data in the specified column
rendering raw values or templates set in the widget's dataset
including column and row spans from the Datatable header and body into export
including hidden columns and rows during export
specifying the desired type of data and setting the format for exported dates and numbers
stubbing empty cells
exporting images

Providing a custom filename and sheet name

You can change the default file name and the default name of the sheet that will contain the exported data:

webix.toExcel($$("table"), {
    filename: "table", // for filename
    name: "Films" // for sheet name
});

Related sample: Export to Excel

Multisheet Export to Excel

If you have several data components on a page, e.g. in a multiview, you can export their data into separate sheets of an Excel file. You can customize the way of data exporting in the following ways:

to export all the data components or only some of them
to set custom names for the sheets or leave the default ones

// exporting all components with custom sheet names
webix.toExcel(
  [{id:"table", options: {name:"Big data"}},
   {id:"list", options: {name:"Small data"}},
   {id:"chart", options: {name:"Chart data"}}], 
  {
    filename:"My data"
  }
);
 
 
// exporting separate components with default sheet names
webix.toExcel(
    [$$("table"), $$("list")],
    { filename: "My data" }
);

Defining custom parameters for data in the specified column

You can define the following parameters: header, width, and template. For example:

webix.toExcel($$("mylist"), {
    columns:{
       "title":{ header:"Title", width:200, template:webix.template("#id#.#name#") },
       "year":{ header:"Year", width:100 }
    }
});

The column will be rendered with the stated additional properties, which may differ from the component parameters.

Rendering raw values from the dataset

You can render raw values from the dataset of a widget via setting the rawValues option to true:

webix.ui({
    rows:[
        {
            view:"datatable",
            id:"table",
            data:grid_data,
            columns:[
                { id:"title", fillspace:true, template:"Film #title#" }
            ]
        }
    ]
});
 
webix.toExcel($$("table"), {
    rawValues:true
});

By default, the rawValues option is set to false and the template set in the dataset is rendered.

Including column and row spans

If there are any column or row spans, you can include them into export via the spans option:

webix.toExcel($$("table"), {
    spans:true
});

Including hidden columns and rows

You can include hidden columns of DataTable, TreeTable, Spreadsheet into the export file by setting the hidden field to true:

webix.toExcel($$("table"), {
    hidden:true
});

In the Excel file the hidden columns/rows will be initially hidden. Users can show them via the corresponding controls in Excel tables.

Setting desired data type and format for exported dates and numbers

You can set the data type and format for dates and numbers in particular columns via the related properties - exportType and exportFormat. Possible values are the following:

for the exportType option: "number", "string", "boolean", "date"
for the exportFormat option: the formats supported by the SheetJS library, e.g.:"d-mmm-yy", "#,##0.00", etc. Since data in columns can be pre-formatted, you'd better apply the rawValues:true option in the export settings to avoid conflicts with Webix formatting

webix.toExcel($$("table"), {
    rawValues:true,
    columns:[
        { id:"year",  header:"Released", exportType:"date",   exportFormat:"d-mmm-yy"},
        { id:"votes", header:"Votes",    exportType:"number", exportFormat:"#,##0.00"}
    ]
});

Stubbing empty cells

You can stub empty cells. When an empty cell is stubbed in the exported file it keeps its space empty and the content of the adjacent cells cannot overflow it. To stub cells, set the stubCells property to true:

webix.toExcel($$("table"), {
  stubCells: true
});

Exporting images

If necessary, you can export images and charts (as images) together with data while exporting any view to Excel by using the $exportView() or $getExportValue() functions.

Check the following examples: