Spreadsheet
- Overview
- Simple Spreadsheet
- Multiple Sheets
- Ajax Loading
- Sheet Management
- Sheet Component
- Multi Fuctional Spreadsheet
- Events
Overview
The spreadsheet module can generate a blank empty spreadsheet with standard alphabetical columns and numeric rows, and then load an array of data into that table.
The purpose of the spreadsheet module is to layout columns and rows, handle multiple sheets of data, map data into the table and extract it in array format. The real power of this module is realised when it is combined with many other table modules like range selection, editing and clipboard to give a comprehensive spreadsheet experience.
Checkout the Multi Fuctional Spreadsheet Demo at the end of this page for an example of how to use multiple modules togeather to build a powerful spreadsheeting tool.
Formulas
The spreadsheet module does not currently support cell based formulas, but this will be comming in a future release.
Data Format
The spreadsheet module works differently than other modules, and is designed to handle spreadsheet data as an array of row arrays rather than objects. This essentially replicates the grid format of a spreadsheet:
[
[9937, "", "", 7749, 9816, 4355, 8279, "", ""],
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
[1924, 8734, 1819, 1838, 2330, 7921, 9219, "", 3537],
["", 8665, 5875, 9732, 1926, "", 9743, 8388, ""],
[7040, 4861, 2988, 5584, 2344, 9749, 8872, 9177, 6246],
[6334, 1674, 2967, "", 9353, 396, 6006, 8572 , ""],
[6359, "", 2580, 5723, 9801, 554, 1044, 5266, 8532],
[7278, 6971, 2232, 5720, 5665, 7231, 1165, "", 168],
]
Because it is designed to handle array formatted data across multiple sheets it has its own properties and functions for loading and retreiving data from the table.
Compatability
Because the spreadsheet module lays out data in a very rigid spreadsheet format, it is not compatible with ant modules that add other custom elements into rows or columns, such as pagination, grouping, responsive collaspe, column calculations etc.
Simple Spreadsheet
If you only want to display a single sheet of data, then there is are a series of quick options you can setup to get you spreadsheet up and running.
You can enable spreadsheet mode by setting the spreadsheet option to true:
var table = new Tabulator("#example-table", {
spreadsheet:true,
});
Sheet Size
You can define dimensions of your sheet using a couple of options, Tabulator will then generate the needed empty columns and rows for your spreadsheet.
It is worth noting that if you load in data with more rows or columns than your current sheet, the sheet will be expanded to fit the data, you can never have a sheet smaller than the data it contains
Columns
You can define the numbr of columns you want in your spreadsheet using the spreadsheetColumns option. This option has a default value of 50 if not set.
var table = new Tabulator("#example-table", {
spreadsheet:true,
spreadsheetColumns:100, //create spreadsheet with 100 columns
});
Rows
You can define the numbr of rows you want in your spreadsheet using the spreadsheetRows option. This option has a default value of 50 if not set.
var table = new Tabulator("#example-table", {
spreadsheet:true,
spreadsheetRows:100, //create spreadsheet with 100 rows
});
Loading Data
You can load array formatted data into your spreadsheet in one of two ways.
You can either load it at the point the table is loaded using the spreadsheetData option:
var table = new Tabulator("#example-table", {
spreadsheet:true,
spreadsheetData:[
[9937, "", "", 7749, 9816, 4355, 8279, "", ""],
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
[1924, 8734, 1819, 1838, 2330, 7921, 9219, "", 3537],
["", 8665, 5875, 9732, 1926, "", 9743, 8388, ""],
]
});
Or you can load it at any point after the table is built, using the setSheetData function, passing the array as the first argument in the function:
var data = [
[9937, "", "", 7749, 9816, 4355, 8279, "", ""],
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
[1924, 8734, 1819, 1838, 2330, 7921, 9219, "", 3537],
["", 8665, 5875, 9732, 1926, "", 9743, 8388, ""],
];
table.setSheetData(data);
Spreadsheet Data Loading
It is very important to note that when using the spreadsheet module, you must only use the spreadsheet functions and options for loading data into the table, this is because the module maps that data into a format that Tabulator can understand. If you use any of the standard data options or and data manipulation functions like setData, it will result in the table malfunctioning.
Cell Setup
All cells in the spreadsheet will be setup with the same column definition, you can customize this using any of the standard column definition options by passing a object to the spreadsheetColumnDefinition option.
For exammple if you wanted to add an input editor to all cells then you could the following:
var table = new Tabulator("#example-table", {
spreadsheet:true,
spreadsheetColumnDefinition:{editor:"input"}, //add an input editor to every cell
});
Row Header
A good way to improve the functionality of the spreadsheet is to use the tables built in row header functionality to add a row header to each row with that rows number displayed.
The spreadsheet module injects the index for each row onto its _id property, so if you set that as the field of the header row then each row will show its assigned row number:
var table = new Tabulator("#example-table", {
spreadsheet:true,
rowHeader:{field:"_id", hozAlign:"center", headerSort:false},
});
Exporting Data
You can extract data from your sheet at any point by calling the getSheetData function. This will return an array of sheet data in the same format as outlined above
var data = table.setSheetData(data);
Export Data Range
It is worth noting that by default the spreadsheet will only export the rows and columns of a spreadheet that actually contain data (ie. it ignores a column or row if all of it cells have a value of undefined).
For example if you had a table with 50 columns and only the first 10 contained data, only the first 10 would be exported. This prevents large numbers of empty rows and columns from clogging up a data set.
Only empty rows and columns after the data are ignored, empty cells before the data (ie above and to the left of data) are included so that the data does not change its index in the table
If you would like to export the entirety of a dataset including empty columns and rows, you can do this using the spreadsheetOutputFull option.
var table = new Tabulator("#example-table", {
spreadsheet:true,
spreadsheetOutputFull: true, //export all data including empty columns
});
Editor Empty Values
When using editors to manipulate data, if a user edits a cell and leaves it blank, it will usually get stored with a value of an empty string rather than undefined, which will result in it being included in the exported data.
If you are using a spreadsheet with editors, it is recomended that you use the editorEmptyValue option to force empty cells to a value ofundefined to prevent them being erroneously included in the exported data.
var table = new Tabulator("#example-table", {
spreadsheet:true,
editorEmptyValue:undefined, //ensure empty values are set to undefined so they arent included in spreadsheet output data
});
Multiple Sheets
If you are looking to include multiple sheets in your table then Tabulator has a range of options to setup the table and make it easier for your users to interact with data across multiple sheets
Sheet Definitions
If you want to load multiple sheets of data into yout table then you can pass an array of sheet definition objects to the spreadsheetSheets option. A sheet definition object contains the the data for the sheets as well as meta data about the sheet such a its size and title.
var table = new Tabulator("#example-table", {
spreadsheet:true,
spreadsheetSheets:[
{
title:"Sales Info",
key:"info",
columns:20,
rows:20,
data:[
[9937, "", "", 7749, 9816, 4355, 8279, "", ""],
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
]
},
{
title:"Profitablility",
key:"profit",
data:[
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
[1924, 8734, 1819, 1838, 2330, 7921, 9219, "", 3537],
]
},
]
});
The following options can be set on a sheets definition object:
- title - The title displayed in the sheets tab
- key - The key used to reference the sheet when calling functions (if this is not defined it will default to the title)
- columns - the number of columns in the sheet (if not defined it will default to the spreadsheetColumns option)
- rows - the number of rows in the sheet (if not defined it will default to the spreadsheetRows option)
- data - the data array for the sheet
You can either use the spreadsheetSheets to load multiple sheets or the spreadsheetData to load a single sheet. Yu cannot use both at the same time
Tabs
By default Tabulator will load in multiple sheets and show the contents of the first sheet, you can then programatically choose when to show other sheets to the user.
If you would prefer the user to be able to navigate through the sheets themelves via some tabs in the footer, enable the spreadsheetSheetTabs option:
var table = new Tabulator("#example-table", {
spreadsheet:true,
spreadsheetSheetTabs:true, //show spreadsheet tabs in footer
});
Tabs Element
By default tabulator will load the the tabs into the table footer, if you would prefer the tabs were inserted elsewhere in the DOM, then you can pass the CSS query selector or the DOM Node to the spreadsheetSheetTabsElement option and the tabs will be inserted there instead
var table = new Tabulator("#example-table", {
spreadsheet:true,
spreadsheetSheetTabsElement:"#table-tabs", //insert tabs in element with id of table-tabs
});
If you take this approach you will need to make sure you add CSS styling for the tabs holder and tab elements. Details of the classes used for tab styling can be found in the CSS Styling Documentation
Ajax Loading
In addition to the spreadsheetData and spreadsheetSheets options for loading your spreadsheets from local data, you can also make use of the ajax module to request your spreadsheet data from a remote source.
Set the standard ajaxURL option to the source URL for your spreadsheet data. This enpoint should either return an array of sheet data or an array of structure definition objects as outline above. The spreadsheet module can detect which format it is and will load the data appropriately.
var table = new Tabulator("#example-table", {
spreadsheet:true,
ajaxURL:"http://www.mysite.com/sheets", //url endpoint for sheet data
});
You can also use all other standard ajax options for configuring your request, checkout the Ajax Documentation for full details
Sheet Management
The spreadsheet module provieds a wide range of functions for interacting with sheets. Most of these functions can either be called on both the table or on a matching function on a specific Sheet Component
Sheet Lookups
A lot of the management functions have a first argument that is the sheet lookup define which sheet the action should be carried out on. This can take one of three values:
- undefined - if you leave the field empty it will default to the currently active sheet
- sheeet component - you can pass in the sheet component for a sheet you want to action
- key - pass in the string for the key of the sheet you want to action
For eaxample the function below will clear the data for the sheet with a key of "info":
table.clearSheet("info"); // clear info tab data
Set Sheets
You can replace all sheets in the table using the setSheets function. This function takes an array of sheet definitions as its first argument. This function return an array of the newly created Sheet Components:
var sheetDefs = [
{
title:"Sales Info",
key:"info",
columns:20,
rows:20,
data:[
[9937, "", "", 7749, 9816, 4355, 8279, "", ""],
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
]
},
{
title:"Profitablility",
key:"profit",
data:[
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
[1924, 8734, 1819, 1838, 2330, 7921, 9219, "", 3537],
]
},
];
var sheetComponents = table.setSheets(sheetDefs);
Add Sheet
You can add a new sheet to the end of the existing set of sheets by calling the addSheet function, passing the sheet definition for the sheet as the first argument. The function returns the Sheet Component for the new sheet:
var sheetDef = {
title:"New Sheet",
key:"new",
data:[
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
[1924, 8734, 1819, 1838, 2330, 7921, 9219, "", 3537],
]
};
var sheet = table.addSheet(sheetDef);
Get Sheet Definitions
You can get the sheet definitions for all current sheets by using the getSheetDefinitions function:
var sheetDefs = table.getSheetDefinitions();
This is particularly useful if you want to capture state of all the current sheets, you can then load this back into the table via the setSheets function at a later date to restore the tables state.
Get All Sheet Components
You can retreive an array of all current Sheet Components using the getSheets function:
var sheets = table.getSheets();
Get Sheet Component
You can retreive a Sheet Component for a specific sheet using the getSheet function, the first argument of this function is a Sheet Lookup for the sheet you want to retrieve:
var sheet = table.getSheet("info"); //get the sheet component for the info sheet
Set Sheet Data
You can replace the data on a sheet by calling the setSheetData function, the first argument of this function is a Sheet Lookup for the sheet you want to retrieve, the seciond argument is the data array:
var data = [
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
[1924, 8734, 1819, 1838, 2330, 7921, 9219, "", 3537],
];
table.setSheetData("info", data); //set data on info sheet
If you only pass data into this function it will replace the data on the currently active sheet:
var sheet = table.getSheet("info"); //get the sheet component for the info sheet:
var data = [
[2380, "", 6977, 8896, 4012, 3803, 5408, 3415, 3056],
[9180, "", 39, 9445, 3917, "", 18, 5239, 2516],
[1924, 8734, 1819, 1838, 2330, 7921, 9219, "", 3537],
];
table.setSheetData(data); //set data on the active sheet
Get Sheet Data
You can retrieve the data from a sheet by calling the getSheetData function, the first argument of this function is a Sheet Lookup for the sheet you want to retrieve:
var data = table.getSheetData("info"); //get the data from the info sheet
Clear Sheet
You can clear all data on a sheet by calling the clearSheet function, the first argument of this function is a Sheet Lookup for the sheet you want to clear:
table.clearSheet("info"); //clear the data from the info sheet
Make Sheet Active
You can make a sheet active by calling the activeSheet function, the first argument of this function is a Sheet Lookup for the sheet you want to make active:
table.activeSheet("info"); //make the info sheet active
Remove Sheet
You can remove a sheet from the table by calling the removeSheet function, the first argument of this function is a Sheet Lookup for the sheet you want to remove:
table.removeSheet("info"); //remove the info sheet from the table
Sheet Component
The sheet component provides access to the functionality of a particular sheet, and provides a wide range of functions for manipulating that sheet.
Full details of the sheet component can be found in the Components Documentation
Multi Fuctional Spreadsheet
The real power of this module is realised when it is combined with other table modules like range selection, editing and clipboard to give a comprehensive spreadsheet experience.
Try some of the following on the table below:
- Editing a cell - Double click on a cell or focus on a cell and press the enter key
- Copy a cell - Click on a cell with a value in, press ctrl+c click on another cell and press ctrl+v
- Navigate the table - Click on a cell and use the arrow keys to navigate round the table
- Select a range - Click and drag across multiple cells
- Expand a range - After selecting a range hold down the shift key and either drag the mouse or use the arrow keys to change the size of the selected range
- Copy a range - After selecting a range, press ctrl+c then click on a different cell and press ctrl+v to paste the range starting from that cell
- Paste to fill - Select a cell or range with values in, press ctrl+c then select a different size range (more than one cell) and press ctrl+v. Notice how it pastes the data to fill the range, either duplicating rows and columns as needed, or hiding data that wont fit.
- Duplicating a column - Click on a column header to select a whole column, press ctrl+c, click into another column header and press ctrl+v
- Duplicating a row - Click on a row header to select a whole row, press ctrl+c, click into another row header and press ctrl+v
Range Selection
To setup the range selection module to work with a spreadsheet, you should enable the module with the selectableRange option (you can either restrict the number of ranges or allow as many as you like)
You should also enable column and row headers with the selectableRangeColumns and selectableRangeRows options.
You can also allow users to clear the contents of a selected range by pressing the backspace or delete keys, by enabling the selectableRangeClearCells option.
var table = new Tabulator("#example-table", {
selectableRange:1, //allow only one range at a time
selectableRangeColumns:true,
selectableRangeRows:true,
selectableRangeClearCells:true,
});
Edit
In order for the spreadsheet navigation to work smoothly, it is recomended that the editTriggerEvent option be set to either click or dblclick
You shoudl also use the editorEmptyValue option to force empty cells to a value ofundefined to prevent them being erroneously included in exported data.
var table = new Tabulator("#example-table", {
editTriggerEvent:"dblclick", //trigger edit on double click
editorEmptyValue:undefined,
});
Clipboard
In order for the copy and paste functionality to work correctly, you will need to enable the clipboard with the clipboard option
You will also need to set the clipboardCopyRowRange, clipboardPasteParser and clipboardPasteAction options to range
In addition it is also worth removing column headers from the clipboards copy data using the clipboardCopyConfigoption
It may also be worth disabling the style copying if you are planning on pasting data into other spreadsheets, as it will make the output cleaner. This can be done using the clipboardCopyStyledoption
var table = new Tabulator("#example-table", {
clipboard:true,
clipboardCopyRowRange:"range",
clipboardPasteParser:"range",
clipboardPasteAction:"range",
clipboardCopyConfig:{
rowHeaders:false, //do not include row headers in clipboard output
columnHeaders:false, //do not include column headers in clipboard output
},
clipboardCopyStyled:false,
});
Row Header
Setup the row header to function as part of the spreadsheet. To do this, disable resizability, freeze the column, set the width, center align the column and add the "rownum" formater:
var table = new Tabulator("#example-table", {
rowHeader: {resizable: false, frozen: true, width:40, hozAlign:"center", formatter: "rownum", field:"rownum", accessorClipboard:"rownum"},
});
Columns
You should setup the defaults for your columns to disable header sorting, set a standard width, add the default editor and center your column headers, and ensure that columns can only be resized from the header:
var table = new Tabulator("#example-table", {
columnDefaults:{
headerSort:false,
headerHozAlign:"center",
editor:"input",
resizable:"header",
width:100,
},
});
Events
A range of events are available for spreadsheets. See the Spreadsheet Events section for more information.