Continuing the topic of the gradual transition from desktop software to similar online services, in this publication I want to talk about a SpreadSheet widget that can compete with his majesty "Excel". It is an outstanding JavaScript component that provides full-fledged support of any spreadsheets. Meanwhile, this tool is very flexible and can be easily integrated into any environment.
If you need to add some Excel-like tool with extensive functionality and high performance in your business application, you should pay attention to the SpreadSheet widget from the Webix team. Let's figure out what they can offer to us and whether the game is worth the candle.
What is JS Spreadsheet
Webix SpreadSheet is a comprehensive JavaScript solution for working with spreadsheets of varying complexity. The component is one of the most advanced tools from the complex widgets line of the Webix library.
Recently (starting with version 8.2), SpreadSheet has received a new engine and multi-sheet math support. These updates significantly accelerated its performance in comparison to previous versions.
The Widget Functionality
The tool functionality is quite extensive. You can create spreadsheets, manage their data via the user-friendly interface, export data in the required format (Excel, PDF, CSV or PNG), and much more. Besides creating your own sheets, you are able to import local data in Excel format and change them depending on your needs.
In particular, you can work with the tables in this way:
- create, copy and delete sheets
- export, import and print sheets
- manage the history of changes
- stylize the cell contents
- sort, filter and block the cell contents
- add links, pictures, charts and comments into cells and above them
- manage columns and rows:
- add and remove
- hide and show
- resize and freeze
- apply a wide range of formulas and much more.
As you can see, the widget's features are large enough and its abilities are not inferior to its desktop counterpart. To read more information about the SpreadSheet capabilities visit the related article.
The Widget Interface
You can work with the widget via a convenient and user-friendly interface. If you are an experienced Excel user, you won't have any difficulties with understanding the component UI. Visually, it consists of 3 parts:
- Toolbar
- Workspace
- Bottom bar.
The Toolbar contains different controls divided by functional groups. With their help, you can manage the sheet data. Depending on the widget settings, the controls may differ. But we'll talk about it later.
On the workspace of the component, you can look through the spreadsheets you will work with. Using the toolbar controls, you can manage table data and change them on your own.
And at the bottom part of the application, there is a Bottom bar where you can navigate through the sheets, as well as add, delete and rename them.
Now after a brief overview of the widget functionality and interface, let's proceed to how to actually create one with a basic configuration.
How to Create the Widget with Basic Functionality
Webix SpreadSheet is one of the most advanced tools of the Webix library. The widget is distributed under the Pro version of the library but isn't included in it. You can purchase this component as a part of the license package. Learn more on the licensing page.
If you want to check all the advantages and flexibility of working with SpreadSheet (and not only), download the trial Pro version of the library. It includes all complex widgets and is available for free for 30 days.
Now let's see how to include the widget on the page and run it with basic functionality. And this is done in a clear and straightforward way. Considering that SpreadSheet is based on the Webix components, you should first include the library sources. And after that, you need to specify the corresponding links to the .js and .css files of the widget itself. In code it looks like this:
<head>
<!-- Webix Library -->
<script src="codebase/webix.js" type="text/javascript"></script>
<link rel="stylesheet" href="codebase/webix.css" type="text/css">
<!-- SpreadSheet -->
<script type="text/javascript" src="codebase/spreadsheet.js"></script>
<link rel="stylesheet" href="codebase/spreadsheet.css" type="text/css">
</head>
After including the required sources, you can initialize the widget with a few lines of code. To do this, you should call the webix.ui() constructor, passing it an object with the required configuration.
To make sure that the application code will start executing after the HTML page is fully loaded, put the constructor into the webix.ready(function(){ /* constructor */ }) method. In the code it looks like this:
<body>
<script>
webix.ready(function(){
<!-- SpreadSheet Constructor -->
webix.ui({
view: "spreadsheet", url: "some_data_link"
});
});
</script>
</body>
Now you can use the basic features of the SpreadSheet widget. And it is worth mentioning here that you have the ability to customize the component functionality. For instance, you can add the necessary tools or remove unnecessary ones. In the browser you will see the following result:
How to Configure SpreadSheet
The SpreadSheet widget is a kind of constructor. Its peculiarity lies in the fact that it consists of many separate Webix components. Each of them has its own properties and methods. Using these API you can customize one or another element of the interface and its behavior (for example, controls on the toolbar).
Besides, the component itself has a rich set of its own properties and methods that allow you to customize it and manage its behavior. And it is worth clarifying here that you need to use a declarative approach to configure the Webix widgets. It means that all parameters are specified using a most convenient JSON syntax. All you have to do is to set the required properties to corresponding values.
The widget itself is declared via the view:"spreadsheet" expression. And in fact, this is quite enough for creating a SpreadSheet with basic functionality. Now let's see how to enhance its configuration.
Extra Toolbar
We can start with the app toolbar, which includes controls for managing tables. And it is worth noting here that in the default configuration, the toolbar displays only the minimal set of controls required for working with data.
If you want to use an extended set of controls, you need to specify the toolbar property and set it to the "full" value. In the code it looks like this:
{
view: "spreadsheet",
toolbar: "full"
}
In the browser you will see the following result:
There are also cases when the toolbar is not needed at all. To remove it, set the toolbar property to false.
Top Menu
Working with the extended toolbar may not always be convenient. It is also worth considering that if you have a small screen resolution, some of the controls can be hidden. To avoid this, you can add an additional menu at the top of the widget. To do this, you need to specify the menu property and set it to true in the component configuration. In the code it looks like this:
{
view: "spreadsheet",
menu: true
}
And in the browser you will see the following result:
Formula Editor
As it was mentioned above the widget's functionality is not inferior to its desktop elder brother. The proof of this is the full support of all formulas for working with data that are supported in Excel. You can find a complete list of formulas with a detailed description in this article.
And for working with formulas there is a special editor that is located under the toolbar. When you enter the function name, the editor will show a list of possible options that correspond to the entered characters. Besides, when you click on a cell the formula is applied to, the widget will highlight all cells that are within the range of this formula.
Optionally, you can hide this editor and math support will remain the same. To do this, you should set the liveEditor property to false in the widget constructor. In the code it looks like this:
{
view: "spreadsheet",
liveEditor: false
}
Bottom Bar
If you are supposed to work with several files at once, you should activate the bottom bar of the widget, which is hidden by default. With its help you can create new sheets as well as navigate through existing ones.
If you want to show this panel, set the bottombar property to true. In the code it looks like this:
{
view: "spreadsheet",
bottombar: true
}
In the browser, you will see the following result:
It should also be borne in mind that if you are using the extended version of the toolbar (toolbar:"full"), the bottom bar will be displayed by default. To hide it, set the bottombar property to false.
ReadOnly Mode
Sometimes there are situations where you need to use the widget only to display certain information. For such cases, the widget provides a readonly mode.
To enable this feature, set the readonly property to true. Now the widget will display only the workspace with the current sheet (table). All additional panels will be hidden. In the code it looks like this:
{
view: "spreadsheet",
readonly: true
}
In the browser you will see the following result:
Loading Data
In the widget configuration, you can specify the data to be displayed when initial loading. Depending on the data is located (on the client- or server-side), you should use either the data or url properties or the parse() or load() methods. Let's take a closer look at each of these options.
Parsing Client-Side Data
By default, the widget accepts data in JSON format. If the data is on the client-side, you can either set the data property to the object with data or parse the same data through the parse() method.
The data object will contain certain fields in which you can describe corresponding parameters, namely:
- data (cell data)
- styles (cell styling)
- spans (cell fusion)
- table (table parameters)
- and other fields.
You can find a complete list of sheet settings in this article. The object with settings may look something like this:
const sheet_data = {
"styles": [
["wss1",";;center;;;;;;;;;"],
...
],
"sizes": [
[0,1,125],
],
"data": [
[1,1,"Report - July 2016","wss1", "string"],
[1,2,"","wss2"],
...
],
"spans": [
[1,1,5,1]
]
};
You can set the data property to this object in the widget constructor:
{
view: "spreadsheet",
data: sheet_data
}
or pass it to the parse() method as a parameter:
{
view: "spreadsheet",
id: "ssheet"
}
$$("ssheet").parse(sheet_data);
You can see a live demo here.
Loading Server-Side Data
If you need to load the remote data from the server, you can set the url property to the corresponding path the desired file is located:
{
view: "spreadsheet",
url: "sheet_data.js"
}
or load the data via the load() method, passing the corresponding link as a parameter:
{
view: "spreadsheet",
id: "ssheet"
}
$$("ssheet").load("sheet_data.js");
And it should be borne in mind here that the widget can handle data in various formats. For example, if you need to load data in CSV format, you should set the datatype property to the required format:
{
view: "spreadsheet",
id: "ssheet",
url: "sheet_data.csv",
datatype: "csv"
}
or pass the format to the load() method as the second parameter:
$$("ssheet").load("sheet_data.csv", "csv");
You can see a live demo here.
Loading data in Excel format requires special attention. Besides the data type and file path, you need to specify the binary-> proxy object through which the data will be loaded. In the code it looks like this:
{
view: "spreadsheet",
id: "ssheet",
url: "binary->sheet_data.xlsx",
datatype: "excel"
}
or in the same way via the load() method:
$$("ssheet").load("binary->sheet_data.xlsx", "excel");
You can see a live demo here.
Saving Data
The SpreadSheet widget is a fully client-side app. But it also has a special API for working with a server. I have mentioned some of them in the previous section about loading server data.
In addition, you have the ability to specify the path to the server script the AJAX requests will be sent to (via the POST method). It will happen when you make any sheet changes.
To do this, you should set the all property to the server script path in the object of the save property:
{
view: "spreadsheet",
url: "server/get_data.php",
save: {
all: "/server"
}
}
You can see a live demo here.
Now, if you change the sheet, all its data (including the state of the widget) will be automatically sent to the server. And for cases when you need to configure sending requests on certain actions with sheets (adding, renaming, updating or deleting data), you can set RESTful saving.
To track the sheet changes, you should subscribe to the onChange event and set it to the handler. This can be done in the on property object. Inside this handler, you can set up separate requests for each operation that will be sent automatically when some operation will be executed. The widget code with the event handler will look like this:
const server_url = "server/get_data.php"
{
view: "spreadsheet",
url: server_url,
on:{
onChange: function(mode, name, oldName){
switch (mode) {
case "update":
webix.ajax().headers({ "Content-type" : "application/json" }).put(server_url+name, this.serialize());
break;
case "rename":
webix.ajax().headers({ "Content-type" : "application/json" }).put(server_url+oldName, {name:name});
break;
case "remove":
webix.ajax().headers({ "Content-type" : "application/json" }).del(server_url+name);
break;
case "insert":
webix.ajax().headers({ "Content-type" : "application/json" }).post(server_url+name);
}
}
}
}
You can see a live demo here.
This is how you can configure the information saving on the server. It is worth mentioning that the widget is distributed along with Node.js backend. You can easily adapt it to any database you need.
Localization
One of the significant advantages of all Webix widgets is the ease of localization. And it is worth clarifying here that by default all widget labels are specified in English (the en-US locale). But you can modify the current locale or create a custom one. Let's take a look at both of these options.
How to Change Current Locale
All translations of the interface elements of the current locale are stored in the webix.i18n.spreadsheet object. You can localize the following interface elements:
- all labels on the toolbar
- tooltips
- all menu items.
The locale object has the following structure:
export default {
labels: {
"common": "Common",
"currency": "Currency",
...,
},
tooltips: {
"color": "Font color",
"background" : "Background color",
...
},
menus: {
"remove-sheet": "Remove sheet",
"rename-sheet": "Rename sheet",
...
},
table: {
"math-error": "#ERROR!",
"Math-ref-error": "#REF!",
...
},
liveEditor: {
"edit": "Edit:"
},
formats: {
"dateFormat": "mm/dd/yyyy",
"timeFormat": "hh:mm AM/PM",
...
}
};
All you need to do is replace the current values with your custom translation, and the widget will change them in the interface. As you can see, everything is pretty simple here.
How to Create Custom Locale
In the second approach, you should create a custom locale with an identical structure and apply it to the entire widget. To do this, you need to add the corresponding translation to the webix.i18n.locales[localeN].spreadsheet object and specify it some name:
webix.i18n.locales["en-RU"].spreadsheet = {
"labels": {
"common": "General",
"currency": "Currency",
...
},
"tooltips": {
"color": "Text color",
"background": "Background color",
...
}, ...
};
To apply the new locale, you should call the setLocale() method of the webix.i18n object, passing it the custom locale name.
webix.i18n.setLocale("ru-RU");
You can see a live demo here.
This method is more preferable because you have an ability to switch to the original locale at any time.
It is also worth mentioning here that the library has a special platform where users can post their translations. You can check them out in this GitHub repository.
You can also take part in the widget localization and upload your custom locale to the corresponding repository.
Customization
If the basic settings are not enough for you, you have the ability to customize the widget interface and functionality. And for this, you need to dig a little in the widget code. Let's look at some related samples of customizing the interface and functionality.
How to Add Extra Toolbar
For instance, you can put any components between the toolbar and workspace, as well as customize their behavior. To do this, you need to use the subbar property. You may set it to either a specific component or a layout with several components.
For clarity, let's add an extra toolbar with buttons for exporting data in various formats (Excel, PNG, PDF and CSV). To make it more convenient, you can save the toolbar constructor into a separate variable. You should set the subbar property to this variable in the widget configuration. In the code it looks like this:
const extra_toolbar = {
view: "toolbar", css: "webix_ssheet_toolbar",
elements:[
{ view: "button", value: "Export to Excel", click:
function(){
webix.toExcel("ssheet");
}},
{ view: "button", value: "Export to PNG", click: function(){
webix.toPNG("ssheet");
}},
{ view: "button", value: "Export to PDF", click: function(){
webix.toPDF("ssheet", {autowidth: true});
}},
{ view: "button", value: "Export to CSV", click: function(){
webix.toCSV("ssheet");
}},
{}
]
};
webix.ui({
id: "ssheet",
view: "spreadsheet",
subbar: extra_toolbar
});
You can see a live demo here.
As you have already noticed, the library provides special methods for exporting data, which takes only the widget's ID. The data will be downloaded in the required format after clicking on the corresponding button on the extra toolbar. In the browser, you will see the following result:
How to Customize Top Menu
The widget configuration allows you to customize its top menu. For clarity, let's see how to remove some options and add new ones.
First, you need to create an array with options of the top menu. For convenience, you can store it into a separate variable:
const custom_menu = [
{ id: "file", submenu: [
{id: "excel-import"},
{id: "excel-export"}
]},
{ id: "custom-options",
value: "<span class='custom_option'>Custom Options</span>",
submenu: [
{id: "option-a", value: "Option A"},
{id: "option-b", value: "Option B"},
{id: "option-c", value: "Option C"}
]
}
];
In the code above, the first element of the array defines the built-in File option. Its drop-down list of options are responsible for importing and exporting data in Excel format. And the second element is a Custom Options item with a drop-down list of arbitrary options.
Now you should set the menu property to the object which is stored in a variable:
webix.ui({
view: "spreadsheet",
menu: custom_menu
});
And in the browser you will see the following result:
How to Customize Main Toolbar
The widget toolbar consists of blocks, inside which there are buttons grouped by certain functions. You can manage these blocks (hide, show or add new ones), as well as customize the buttons inside the blocks.
To modify the toolbar blocks, you need to use the buttons property, which refers to the buttons collection. This collection contains block names as keys and arrays with button names as values. For example, you can customize the current toolbar and include only 2 built-in blocks, as well as add a new block with a custom button. In the code it looks like this:
{
view: "spreadsheet",
buttons: {
"undo-redo": ["undo", "redo"],
"format": ["format"],
"Extra Block": [
{ view: "button", width: 150, label: "Custom Button" }
]
}
}
And in the browser you will see the following result:
To define a custom toolbar, you need to use the toolbar property (which refers to the toolbar collection) to the object with the corresponding settings. Inside this object, you should form your custom toolbar structure (according to the principle of the layout building), set its sizes, indents, styles, and others. Read more about the toolbar customizing in the related article.
You can see a live demo here.
Conclusion
In this article, you learned about the SpreadSheet widget and its main features. In fact, this publication covers only the tip of the iceberg of all the possibilities that the tool has. As you can see, it is a very functional and flexible component. You can easily set, customize and integrate it with any framework or library you need. You also have the ability to set up interaction with the backend. To read more information about the possibilities and customization follow the widget documentation.
Top comments (0)