Excel File Import

Loading Data from Excel into SpreadSheet

You can easily import data from an Excel file into SpreadSheet.

To load data from Excel into SpreadSheet, you can choose one of the ways below:

a) specifying the necessary file in the SpreadSheet constructor with the help of the url parameter:

webix.ui({
    view:"spreadsheet",
    url: "binary->../common/test.xlsx",
    datatype:"excel"
});

The notation "binary->../common/test.xlsx" allows loading compiled files from the computer and get their content.

The "excel" datatype allows parsing the content of a binary file and getting it in the form necessary for SpreadSheet.

b) calling the load method:

$$("ssheet").load("binary->../common/test.xlsx", "excel");

The parameters are:

• file - (string) the name of the file to load
• datatype - (string) data type (should be "excel" here)<

c) using the parse method to load data to the component:

{ view:"uploader", value:"Load from Excel file", on:{
    onBeforeFileAdd: function(upload){
        $$("ssheet").reset();
        $$("ssheet").parse(upload.file, "excel");
        return false;
    }
}}

Parameters of the parse method are the following:

• file - (object) uploaded file object
• datatype - (string) data type (should be "excel" here)

Import settings

SpreadSheet allows importing a range of settings from an Excel file. Styles, column width and row heights are imported by default. You can also import:

• math formulas

You need to set the math:true property in the SpreadSheed configuration, if it is disabled by some reason.

webix.ui({
    view: "spreadsheet",
    data: base_data,
    math: true
});

• column and row spans

To import spans, you should set the spans:true property in the SpreadSheet config:

webix.ui({
    view: "spreadsheet",
    data: base_data,
    spans: true
});

• sheets

By default, Spreadsheet shows only the first imported sheet. In order to browse all of them, you need to set either the bottombar:true or toolbar:"full" properties in the SpreadSheet configuration:

webix.ui({
    view:"spreadsheet",
    data: base_data,
    toolbar: "full" // or bottombar:true
});

You can also switch between the sheets via the showSheet(name) method:

$$("ssheet").showSheet("Tab 1");

• dates

SpreadSheet allows importing Webix-formatted dates from an Excel file.

Customizing Imported Data

You can customize the presentation of the loaded data: set the name of the sheet and control the number of the loaded rows.

webix.ui({
    view:"spreadsheet",
    url: "binary->../common/test.xlsx@Data[0-10]",
    datatype:"excel"
});

The string value of the url property "binary->files/data.xlsx@Data[1-10]" includes the following parts:

• binary - the name of the proxy object
• ../common/test.xlsx - path to the Excel file
• @ - optional, the delimiter that detaches the link from its parameters (can be omitted together with parameters)
• Data - optional, the name of the Excel sheet to render
• [1-10] - optional, the rows that should be rendered in the viewer

To render rows starting from a certain row, you can set the rows array as [8-].

Related sample:  Import from Excel

Importing Data Offline

The dependencies which are necessary to import data from Excel files into SpreadSheet are generally taken from Webix CDN online catalog. If you need to import data offline, you should complete the following steps:

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

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

webix.env.cdn = "/local_folder";

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

• in case you set the path relative to the current page, it should be specified as:

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

• the absolute path to the repository should be specified as follows:

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

Supported elements/settings for Excel import

• data (including math and dates)
• font
• font size (converts pt into px)
• bold, italic, underline, strike
• background
• color
• borders
• horizontal align
• vertical align
• rows/cols sizes (converts pt into px)
• hidden rows/cols
• merge
• wrap
• indent
• number format (those supported by SpreadSheet)
• named ranges
• links (all links are converted into the HYPERLINK method)
• sheet visibility
• images
• filters
• zoom
• header visibility
• gridlines
• formulas mode

You can compare elements and settings supported by SpreadSheet for import and export to Excel.

Back to top