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:

The components don't require any special configuration to be exported.

The described functionality is available both in Standard and Pro versions, but pay attention that the IE browser is supported starting from version 10.

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
  • options - (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"));

Related sample:  Export to Excel

Returning a promise with data

The toExcel() method returns a promise object. The promise is resolved with the contents of an Excel file which is ready for downloading. So, if needed, you can access raw data of the exported file (a blob object):

webix.toExcel($$("mylist")).then(function(blob){
    // the logic of processing the blob
});

For example, you may need to disable file download and process raw data according to your needs.

Exporting Data Offline

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

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

  • rename the folder to "extras" and move it to the desired directory
  • set the path to the local CDN repository as in:
webix.cdn = "/local_folder";

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

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

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

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

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

Customizing Export to Excel

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

However, you may need to get some particular data, or customize the file and sheet names.

Export API allows

  1. providing a custom filename and sheet name
  2. stripping HTML tags from cells
  3. setting custom columns for export
  4. defining a custom header, width or a template for data in the specified column
  5. including extra fields into export
  6. rendering raw values or templates set in the widget's dataset
  7. including column and row spans from the Datatable header and body into export
  8. disabling file download in a browser, if necessary
  9. ignoring particular columns during export
  10. outputting certain data from a data set
  11. specifying the desired type of data and setting the format for exported dates and numbers
  • providing a custom filename and sheet name:
webix.toExcel($$("table"), {
     filename: "table", // for filename
     name: "Films" // for sheet name
});

Related sample:  Export to Excel

  • stripping HTML tags from cells:
webix.toExcel($$("table"), {
     filterHTML:true
});
  • setting columns you'd like to see in the export file. There are two ways to set custom columns for export:

You can provide an associative array (hash) with columns' ids as keys. Their values can be either boolean for default parameters (taken from the component configuration) or object for custom parameters:

webix.toExcel($$("table"), {
    columns:{
        "rank":true,
        "title":{ header:"Title", width:250}
    }
});

With such a notation, the order of columns in the resulting file is defined automatically with a for.. in loop. Sometimes it may be unreliable, e.g.:

webix.toExcel($$("table"), {
    columns:{
        "rank":true,
        "title":{ header:"Title", width:250},
        "2016":{ header:"Votes in 2016"},
        "2015":{ header:"Votes in 2015"}
    }
});

The order in the export file will be: "2015", "2016", "rank", "title" since numbers are processed first and come in the ascending order.

To ensure the strict order of columns in the resulting file, you can specify columns as a plain array:

webix.toExcel($$("table"), {
    columns:[
        { id:"rank" },
        { id:"title", header:"Title", width:250},
        { id:"2016", header:"Votes in 2016"},
        { id:"2015", header:"Votes in 2015"}
    ]
});

Note that if you want to get the default column's parameters in this case, you should specify only the column's id (see the "rank" column).

  • defining custom parameters, such as header, width or template for data in the specified column:
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's parameters.

  • rendering raw values from the widget's dataset 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 extra fields into export by forming them right within the export function:
webix.toExcel($$("mylist"), { 
    columns:{ 
        Custom1:{
            template:function(obj){
                return "Year " + obj.year;
            },
            width:50,
            header:"Year"
        },
        //other columns
        title:true
    }
});

Related sample:  Export to Excel

"Custom1" (any name can be used) receives data from the year field even if it is not seen in this component but is presented in its dataset. The field is rendered with width and template as well as header that will be the header of the corresponding column in the resulting table.

  • including column and row spans (if any) into export via the spans option:
webix.toExcel($$("table"), {
   spans:true
});
  • disabling file download in a browser, if necessary, via the download property set to false:
webix.toExcel($$("table"), {
   download:false
}).then(function(blob){
   //process raw data
});
  • ignoring particular columns during export via the ignore property. You need to set its value as an object with a list of columns' names to ignore. For example:
webix.toExcel($$("table"), {
    ignore: { "votes":true, "rating":true }
});
  • outputting certain data from a data set via the filter property. The value of this option should be set as a function which returns the data that should be exported:
webix.toExcel($$("data"), {
    filter:function(obj){
        return obj.checked;
    }
});

Related sample:  Specifying data items for export

  • setting desired data type and format for exported 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"}
    ]
});

Related sample:  Export to Excel: Setting format

Customizing Export for Hierarchical Components

In addition to standard export options, the API allows customizing the output data of hierarchical components (Tree and Treetable) in the following way:

  • not rendering "hyphens" before child nodes in tree-like structures by using the plainOutput option with the true value:
webix.toExcel($$("mytreetable"), {
    columns:{
        plainOutput:true
    }
});
  • rendering separate columns for data on different hierarchy levels, thus rendering data in a table:
webix.toExcel($$("mytreetable"), {
    flatTree:{
        id:"value",
        columns:[
            { header:"Title" },
            { header:"Section" },
            { header:"Subsection" }
        ]
    }
});

The flatTree property object includes the following options:

  • id (string) - the data property which will be rendered in columns
  • columns (array) - an array with columns to which the above data property will be distributed according to the hierarchy level
  • fill (boolean) - optional. If true, allows filling child records with parent data for each child node

Related sample:  Export to PDF, 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 separate ones
  • to set custom names for the sheets or leave the default ones
// exporting all components with custom sheet names
webix.toExcel( 
    [$$("table"), $$("list"), $$("chart")], 
    { filename: "My data", sheets:["Big data", "Small data", "Chart data"]}
);
 
// exporting separate components with default sheet names
webix.toExcel(
    [$$("table"), $$("list")], 
    { filename: "My data" }
);

Related sample:  Export to Excel: Multiple sheets

Back to top