Export to Excel

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

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

For example, to export a datatable to an Excel document, you need to start with the view initialization:

{
    view:"datatable",
    id: "myDatatable",
    // datatable configuration
    ...
}

And then the call of the toExcel() method should follow:

webix.toExcel($$("myDataTable"));

Related sample: Export to Excel

Customizing Export

By default: - 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 named "Data".

You can change the default behavior and add some other settings.

You can

provide a custom filename and sheet name
strip HTML tags from the cells
set custom columns for export
define custom header, width or template for data in the specified column
include extra fields into export
render raw values or templates set in the widget's dataset
include column and row spans from Datatable header and body into export
disable file download in a browser, if necessary
ignore particular columns during export
include hidden columns and rows during export
hide any columns and rows during export
include cell styles from Datatable header and body into export
export row heights
omit the header or footer of Datatable during export
output certain data from a data set
specify the desired type of data and set format for exported dates and numbers

Below you will find detailed descriptions of export possibilities:

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 the cells:

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

setting columns you'd like to see in the export file. There are two ways to set the custom columns for export:

You can provide an object with column ids as keys. Their values can be either boolean for default parameters (taken from 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 the 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 the 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($$("myDataTable"), {
    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.

including extra fields into export by forming them right within the export function:

webix.toExcel(datatable, {
    columns:{
        Custom1:{
            template:function(obj){
                return "Year " + obj.year;
            },
            width:50,
            header:"Year"
        },
        // other columns
        title:true
    }
});

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

rendering raw values from the 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 column and row spans from Datatable header and body (if any) into export:

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

Note that only spans applied to columns' headers will be exported, since the DataTable rowspans and colspans functionality is the feature available only in the PRO version.

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

including hidden columns and rows during export via the hidden property. Set the property to true:

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

The hidden columns/rows will be initially hidden in the exported file. You can show them via the corresponding controls in Excel.

hiding any columns and rows during export even if they are not actually hidden in the table. To hide a particular row, provide a callback with your logic as a value for the hide field. Below all rows with ID < 20 will be hidden in the exported file:

webix.toExcel(table, {
  hide:(obj)=>{
    if(obj.id < 20)return true;
  }
});

The callback receives a row object as a parameter.

To hide a particular column set the hidden field to the config object of the desired column to true:

webix.toExcel($$("table"), {
  columns:{
    rank:true,
    // title column will be hidden
    title:{ width:1500, hidden:true },
    year:true,
    votes:true
  }
});

including cell styles from Datatable header and body into export:

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

exporting row heights. This setting is regulated by the heights option, which is set to false by default and can take the following values:
- true - to export only custom row heights (different from the default rowHeight);
- "all" - to export both custom and default row heights;
- false - default, row heights are not exported.

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

If the styles:true option is set, the heights option is automatically set to "all".

omitting the header or footer of a datatable during the export. For this you need to use the header/footer option and set it to false:

webix.toExcel($$("table"), {
   header:false,
   footer:false
});

outputting certain data from the data set of Datatable 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;
    }
});

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

If you have not checked yet, be sure to visit site of our main product Webix best ui framework and page of ui datagrid product.