Dynamic Spreadsheet-like Data Grid - jExcel
File Size: | 102 KB |
---|---|
Views Total: | 55285 |
Last Update: | |
Publish Date: | |
Official Website: | Go to website |
License: | MIT |

jExcel is a powerful jQuery and Vanilla JavaScript plugin which lets you dynamically generate a spreadsheet like CRUD data grid (table) from local JS data or even JSON, CSV, XSLX files.
Easy to integrate with any third party plugin data manipulation plugin like input mask, currency formatting, key-value dropdown, color picker and much more. Also comes with an onchange handler which will be fired on every change in your spreadsheet.
Note that the latest jExcel (v3.0+) has dropped the jQuery support. You can use it as a vanilla JavaScript library. Download Here.
See Also:
Table Of Contents:
How to use it (v4+, Vanilla JS Version):
1. Import the jExcel and jSuites into the document.
<link rel="stylesheet" href="/path/to/jsuites.css" /> <link rel="stylesheet" href="/path/to/jexcel.css" /> <script src="/path/to/jsuites.js"></script> <script src="/path/to/jexcel.js"></script>
2. Create an empty DIV element to hold your spreadsheet.
<div id="demo"></div>
3. Generate a spreadsheet from data set you provide..
// from JS arrays jexcel(document.getElementById('demo'), { data: [ // data here ]; columns:[ // columns data here ] }); // from a JSON file jexcel(document.getElementById('demo'), { url:'data.json', columns:[ // columns data here ] }); // from a CSV file jexcel(document.getElementById('demo'), { csv:'demo.csv', csvHeaders:true, columns:[ // columns data here ] });
4. All possible options with default values.
// External data url:null, // Data data:null, // Copy behavior copyCompatibility:false, root:null, // Rows and columns definitions rows:[], columns:[], // Deprected legacy options colHeaders:[], colWidths:[], colAlignments:[], nestedHeaders:null, // Column width that is used by default defaultColWidth:50, defaultColAlign:'center', // Spare rows and columns minSpareRows:0, minSpareCols:0, // Minimal table dimensions minDimensions:[0,0], // Allow Export allowExport:true, // @type {boolean} - Include the header titles on download includeHeadersOnDownload:false, // @type {boolean} - Include the header titles on copy includeHeadersOnCopy:false, // Allow column sorting columnSorting:true, // Allow column dragging columnDrag:false, // Allow column resizing columnResize:true, // Allow row resizing rowResize:false, // Allow row dragging rowDrag:true, // Allow table edition editable:true, // Allow new rows allowInsertRow:true, // Allow new rows allowManualInsertRow:true, // Allow new columns allowInsertColumn:true, // Allow new rows allowManualInsertColumn:true, // Allow row delete allowDeleteRow:true, // Allow deleting of all rows allowDeletingAllRows:false, // Allow column delete allowDeleteColumn:true, // Allow rename column allowRenameColumn:true, // Allow comments allowComments:false, // Global wrap wordWrap:false, // Image options imageOptions: null, // CSV source csv:null, // Filename csvFileName:'jexcel', // Consider first line as header csvHeaders:true, // Delimiters csvDelimiter:',', // First row as header parseTableFirstRowAsHeader:false, parseTableAutoCellType:false, // Disable corner selection selectionCopy:true, // Merged cells mergeCells:{}, // Create toolbar toolbar:null, // Allow search search:false, // Create pagination pagination:false, paginationOptions:null, // Full screen fullscreen:false, // Lazy loading lazyLoading:false, loadingSpin:false, // Table overflow tableOverflow:false, tableHeight:'300px', tableWidth:null, // Meta meta: null, // Style style:null, // Execute formulas parseFormulas:true, autoIncrement:true, autoCasting:true, // Security secureFormulas:true, stripHTML:true, // Filters filters:false, footers:null, // Global event dispatcher onevent:null, // Persistance persistance:false, // Customize any cell behavior updateTable:null, // Detach the HTML table when calling updateTable detachForUpdates: false, freezeColumns:null,
5. Callback functions.
onundo: function(instance, historyRecord){ // do something }, onredo: function(instance, historyRecord){ // do something }, onload: function(instance, historyRecord){ // do something }, onchange: function(instance, cell, x, y, value){ // do something }, onbeforechange: function(instance, cell, x, y, value){ // do something }, onafterchanges: function(instance, records){ // do something }, onbeforeinsertrow: function(instance, rowNumber, numOfRows, insertBefore){ // do something }, oninsertrow: function(instance, rowNumber, numOfRows, rowRecords, insertBefore){ // do something }, onbeforeinsertcolumn: function(instance, columnNumber, numOfColumns, insertBefore){ // do something }, oninsertcolumn: function(instance, columnNumber, numOfColumns, historyRecords, insertBefore){ // do something }, onbeforedeleterow: function(instance, rowNumber, numOfRows){ // do something }, ondeleterow: function(instance, rowNumber, numOfRows, rowRecords){ // do something }, onbeforedeletecolumn: function(instance, columnNumber, numOfColumns){ // do something }, ondeletecolumn: function(instance, columnNumber, numOfColumns, historyRecords){ // do something }, onmoverow: function(instance, from, to){ // do something }, onmovecolumn: function(instance, from, to){ // do something }, onresizerow: function(instance, cell, width){ // do something }, onresizecolumn: function(instance, cell, width){ // do something }, onsort: function(instance, cellNum, order){ // do something }, onselection: function(instance, x1, y1, x2, y2, origin){ // do something }, onpaste: function(instance, data){ // do something }, onbeforepaste: function(instance, data, x, y){ // do something }, onmerge: function(instance, cellName, colspan, rowspan){ // do something }, onfocus: function(instance){ // do something }, onblur: function(instance){ // do something }, onchangeheader: function(instance, column, oldValue, newValue){ // do something }, oneditionstart: function(instance, cell, x, y){ // do something }, oneditionend: function(instance, cell, x, y, value, save){ // do something }, onchangestyle: function(instance, o, k, v){ // do something }, onchangemeta: function(instance, o, k, v){ // do something }, onchangepage: function(instance, pageNumber, oldPage){ // do something }, onbeforesave: function(instance, obj, data){ // do something }, onsave: function(instance, obj, data){ // do something },
6. Localize the plugin.
text:{ noRecordsFound: 'No records found', showingPage: 'Showing page {0} of {1} entries', show: 'Show ', search: 'Search', entries: ' entries', columnName: 'Column name', insertANewColumnBefore: 'Insert a new column before', insertANewColumnAfter: 'Insert a new column after', deleteSelectedColumns: 'Delete selected columns', renameThisColumn: 'Rename this column', orderAscending: 'Order ascending', orderDescending: 'Order descending', insertANewRowBefore: 'Insert a new row before', insertANewRowAfter: 'Insert a new row after', deleteSelectedRows: 'Delete selected rows', editComments: 'Edit comments', addComments: 'Add comments', comments: 'Comments', clearComments: 'Clear comments', copy: 'Copy...', paste: 'Paste...', saveAs: 'Save as...', about: 'About', areYouSureToDeleteTheSelectedRows: 'Are you sure to delete the selected rows?', areYouSureToDeleteTheSelectedColumns: 'Are you sure to delete the selected columns?', thisActionWillDestroyAnyExistingMergedCellsAreYouSure: 'This action will destroy any existing merged cells. Are you sure?', thisActionWillClearYourSearchResultsAreYouSure: 'This action will clear your search results. Are you sure?', thereIsAConflictWithAnotherMergedCell: 'There is a conflict with another merged cell', invalidMergeProperties: 'Invalid merged properties', cellAlreadyMerged: 'Cell already merged', noCellsSelected: 'No cells selected', },
7. API methods.
// Get the full or partial table data // @Param boolan onlyHighlighedCells - Get only highlighted cells myTable.getData([bool]); // Get the full or partial table data in JSON format // @Param boolan onlyHighlighedCells - Get only highlighted cells myTable.getData([bool]); // Get the data from one row by number // @Param integer rowNumber - Row number myTable.getRowData([int]); // Get the data from one column by number // @Param integer columnNumber - Column number myTable.getColumnData([int]); // Set the data from one column by number // @Param integer columnNumber - Column number myTable.setColumnData([int], [array]); // Update the table data // @Param json newData - New json data, null will reload what is in memory. myTable.setData([json]); // Merge cells // @Param string columnName - Column name, such as A1. // @Param integer colspan - Number of columns // @Param integer rowspan - Number of rows myTable.setMerge([string], [int], [int]); // Get merged cells properties // @Param string columnName - Column name, such as A1. myTable.getMerge([string]); // Remove merged // @Param string columnName - Column name, such as A1. myTable.removeMerge([string]); // Destroy merged by column name // destroyMerged: Destroy all merged cells myTable.destroyMerge(); // Get current cell DOM // @Param string columnName - str compatible with excel, or as object. myTable.getCell([string]); // Get current cell DOM innerHTML // @Param string columnName - str compatible with excel, or as object. myTable.getLabel([string]); // Get current cell value // @Param mixed cellIdent - str compatible with excel, or as object. myTable.getValue([string]); // Get value from coords // @Param integer x // @Param integer y myTable.getValueFromCoords([integer], [integer]); // Change the cell value // @Param mixed cellIdent - str compatible with excel, or as object. // @Param string Value - new value for the cell myTable.setValue([string], [string]); // Set value from coords // @Param integer x // @Param integer y // @Param string Value - new value for the cell // @Param bool force - update readonly columns myTable.getValueFromCoords([integer], [integer], [string], [bool]); // Reset the table selection // @Param boolean executeBlur - execute the blur from the table myTable.resetSelection([bool]); // Select cells // @Param object startCell - cell object // @Param object endCell - cell object // @Param boolean ignoreEvents - ignore onselection event myTable.updateSelection([cell], [cell], true); // Select cells // @Param integer x1 // @Param integer y1 // @Param integer x2 // @Param integer y2 myTable.updateSelectionFromCoords([integer], [integer], [integer], [integer]); // Get the current column width // @Param integer columnNumber - column number starting on zero myTable.getWidth([integer]); // Change column width // @Param integer columnNumber - column number starting on zero // @Param string newColumnWidth - New column width myTable.setWidth([integer], [integer]); // Get the current row height // @Param integer rowNumber - row number starting on zero myTable.getHeight([integer]); // Change row height // @Param integer rowNumber - row number starting on zero // @Param string newRowHeight- New row height myTable.setHeight([integer], [integer]); // Get the current header by column number // @Param integer columnNumber - Column number starting on zero myTable.getHeader([integer]); // Get all header titles myTable.getHeaders(); // Change header by column // @Param integer columnNumber - column number starting on zero // @Param string columnTitle - New header title myTable.setHeader([integer], [string]); // Get table or cell style // @Param mixed - cell identification or null for the whole table. myTable.getStyle([string]); // Set cell(s) CSS style // @Param mixed - json with whole table style information or just one cell identification. Ex. A1. // @param k [optional]- CSS key // @param v [optional]- CSS value myTable.setSyle([object], [string], [string]); // Remove all style from a cell // @Param string columnName - Column name, example: A1, B3, etc myTable.resetStyle([string]); // Get cell comments // @Param mixed - cell identification or null for the whole table. myTable.getComments([string]); // Set cell comments // @Param cell - cell identification // @Param text - comments myTable.setComments([string], [string]); // Reorder a column asc or desc // @Param boolean sortType - Zero will toggle current option, one for desc, two for asc myTable.orderBy([integer], [boolean]); // Get table definitions myTable.getConfig(); // Add a new column // @param mixed - num of columns to be added or data to be added in one single column // @param int columnNumber - number of columns to be created // @param boolean insertBefore // @param object properties - column properties myTable.insertColumn([mixed], [integer], [boolean], [object]); // Remove column by number // @Param integer columnNumber - Which column should be excluded starting on zero // @param integer numOfColumns - number of columns to be excluded from the reference column myTable.deleteColumn([integer], [integer]); // change the column position // @Param integer columnPosition // @Param integer newColumnPosition myTable.moveColumn([integer], [integer]); // Add a new row // @Param mixed - number of blank lines to be insert or a single array with the data of the new row // @Param integer rowNumber - reference row number // @param boolean insertBefore myTable.insertRow([mixed], [integer], [boolean]); // Remove row by number // @Param integer rowNumber - Which row should be excluded starting on zero // @Param integer numOfRows - number of lines to be excluded myTable.deleteRow([integer], [integer]); // Change the row position // @Param integer rowPosition // @Param integer newRowPosition myTable.moveRow([integer], [integer]); // Get the current data as a CSV file myTable.download(); // Get the table or cell meta information // @Param mixed - cell identification or null for the whole table. myTable.getMeta([string]); // Set the table or cell meta information // @Param mixed - json with whole table meta information. myTable.setMeta([mixed]); // Toogle table fullscreen mode // @Param boolan fullscreen - define fullscreen status as true or false myTable.fullscreen([bool]); // Get the selected rows // @Param boolan asIds - Get the rowNumbers or row DOM elements myTable.getSelectedRows([bool]); // Get the selected columns // @Param boolan asIds - Get the colNumbers or row DOM elements myTable.getSelectedColumns([bool]); // Show column of index numbers myTable.showIndex(); // Hide column of index numbers myTable.hideIndex(); // Search in the table, only if directive is enabled during inialization. // @Param string - Search for word myTable.search([string]); // Reset search table myTable.resetSearch(); // Which page showing on jExcel - Valid only when pagination is true. myTable.whichPage(); // Go to page number- Valid only when pagination is true. // @Param integer - Go to page number myTable.page([integer]); // Undo last changes myTable.undo(); // Redo changes myTable.redo();
How to use it (v2.x, jQuery Version):
1. Add references to jQuery library and the jQuery jExcel plugin's JS & CSS files into the webpage.
<link href="dist/css/jquery.jexcel.css" rel="stylesheet"> <script src="//code.jquery.com/jquery.min.js"></script> <script src="dist/js/jquery.jexcel.js"></script>
2. Load extensions as per your needs. All possible extensions:
- Dropdown
- Calendar picker
- CSV
- Formula
<!-- Dropdown --> <link rel="stylesheet" href="dist/css/jquery.jdropdown.min.css" /> <script src="dist/js/jquery.jdropdown.js"></script> <!-- Calendar picker --> <link rel="stylesheet" href="dist/css/jquery.jcalendar.css" /> <script src="dist/js/jquery.jcalendar.js"></script> <!-- CSV --> <script src="dist/js/jquery.csv.min.js"></script> <!-- Formula --> <script src="dist/js/excel-formula.min.js"></script>
3. Create an empty DIV element that will be served as the container for your spreadsheet.
<div id="demo1"></div>
4. Define an array of JS data to be presented in the spreadsheet.
data1 = [ ['Google', 1998, 807.80], ['Apple', 1976, 116.52], ['Yahoo', 1994, 38.66], ];
5. Initialize the spreadsheet and done.
$('#demo1').jexcel({ data:data1 });
6. Or load the data from an external JSON file.
$('#demo1').jexcel({ url:'/json' });
7. If you'd like to load an external CSV file into the spreadsheet. Requires the CSV extension.
$('#demo1').jexcel({ csv:'demo.csv', csvHeaders:true })
8. Export the spreadsheet data as a CSV file:
$('#demo1').jexcel('download');
9. Default plugin settings to customize the spreadsheet:
$('#demo1').jexcel({ // Column types and configurations columns:[], // Column header titles colHeaders:[], // Column width sizes colWidths:[], // Column alignment colAlignments:[], // Colum header classes colHeaderClasses:[], // Column width that is used by default defaultColWidth:50, // Minimal number of blank rows in the end minSpareRows:0, // Minimal number of blank cols in the end minSpareCols:0, // Minimal table dimensions minDimensions:[0,0], // Custom context menu contextMenu:null, // Allow column sorting columnSorting:true, // Allow column resizing columnResize:true, // Allow row dragging rowDrag:true, // Allow table edition editable:true, // Allow new rows allowInsertRow:true, // Allow new rows allowManualInsertRow:true, // Allow new columns allowInsertColumn:true, // Allow new rows allowManualInsertColumn:true, // Allow row delete allowDeleteRow:true, // Allow column delete allowDeleteColumn:true, // Allow cell commments allowComments:true, // Global wrap wordWrap:false, // Filename csvFileName:'jexcel', // Disable corner selection selectionCopy:true, // Allow Overflow tableOverflow:false, // Allow Overflow tableHeight:200, // Add custom Toolbar toolbar:null });
10. API methods.
// Get the full or partial table data // @Param boolan onlyHighlighedCells - Get only highlighted cells $('#demo1').jexcel('getData', false); // Update the table data // @Param json newData - New json data, null will reload what is in memory. // @Param boolean ignoreSpare - ignore configuration of minimal spareColumn/spareRows $('#demo1').jexcel('setData', [json], false); // Add a new column // @Param integer numberOfColumns - Number of columns should be added // @Param string headerTitle - Header title $('#demo1').jexcel('insertColumn', 1, { header:'Title' }); // Remove column by number // @Param integer columnNumber - Which column should be excluded starting on zero $('#demo1').jexcel('deleteColumn', 1); // Add a new row $('#demo1').jexcel('insertRow', 1); // Remove row by number // @Param integer rowNumber - Which row should be excluded starting on zero $('#demo1').jexcel('deleteRow', 1); // Get the current header by column number // @Param integer columnNumber - Column number starting on zero $('#demo1').jexcel('getHeader', 2); // Change header by column // @Param integer columnNumber - column number starting on zero // @Param string columnTitle - New header title $('#demo1').jexcel('setHeader', 1, 'Title'); // Get the current column width // @Param integer columnNumber - column number starting on zero $('#demo1').jexcel('getWidth', 2); // Change column width // @Param integer columnNumber - column number starting on zero // @Param string newColumnWidth - New column width $('#demo1').jexcel('setWidth', 1, 100); // Reorder a column asc or desc // @Param integer columnNumber - column number starting on zero // @Param smallint sortType - Zero will toggle current option, one for desc, two for asc $('#demo1').jexcel('orderBy', 2); // Get current cell value // @Param mixed cellIdent - str compatible with excel, or as object. $('#demo1').jexcel('getValue', 'A1'); // Change the cell value // @Param mixed cellIdent - str compatible with excel, or as object. // @Param string Value - new value for the cell $('#demo1').jexcel('setValue', 'A1'); // Select cells // @Param object startCell - cell object // @Param object endCell - cell object // @Param boolean ignoreEvents - ignore onselection event $('#demo1').jexcel('updateSelection', [cell], [cell], true); // Get the current data as a CSV file. $('#demo1').jexcel('download'); // Get the current value of one configuration by key // @Param string configuration key $('#demo1').jexcel('getConfig', 'allowInsertColumn'); // Set the value of one configuration by key // @Param string configuration key, @Param mixed configuration value $('#demo1').jexcel('setConfig', 'allowInsertColumn', true); // Get table or cell style // @Param mixed - cell identification or null for the whole table. $('#demo1').jexcel('getStyle', 'A1'); // Set cell(s) CSS style // @Param mixed - json with whole table style information or just one cell identification. Ex. A1. // @param k [optional]- CSS key // @param v [optional]- CSS value $('#demo1').jexcel('setSyle', [ { A1:'background-color:red' }, { B1: 'color:red'} ]); // Get cell comments // @Param mixed - cell identification or null for the whole table. $('#demo1').jexcel('getComments', 'A1'); // Set cell comments // @Param cell - cell identification // @Param text - comments $('#demo1').jexcel('setComments', 'A1', 'My cell comments!'); // Get the table or cell meta information // @Param mixed - cell identification or null for the whole table. $('#demo1').jexcel('getMeta', 'A1'); // Set the table or cell meta information // @Param mixed - json with whole table meta information. $('#demo1').jexcel('setMeta', [ A1: { info1:'test' }, { B1: { info2:'test2', info3:'test3'} } ]);
11. Event handlers.
$('#demo1').jexcel({ // on load onload: function(){}, // before a value is changed onbeforechange: function(){}, // after a value is changed onchange: function(){}, oncomments: null, // after all change events are performed onafterchange: function(){}, // before a new row is inserted onbeforeinsertrow: null, // after a new row is inserted oninsertrow: function(){}, // before a new column is inserter onbeforeinsertcolumn: function(){}, // after a new column is inserted oninsertcolumn: function(){}, // before a row is deleted onbeforedeleterow: null, // after a row is deleted ondeleterow: function(){}, // before a column is deleted onbeforedeletecolumn: null, // after a column is deleted ondeletecolumn: function(){}, // on selection onselection: function(){}, // after a column is sorted onsort: function(){}, // after a row is resized onresizerow: null, // after a column is resized onresizecolumn:null, // after a row is moved onmoverow: function(){}, // after a column is moved onmovecolumn: null, // before paste onbeforepaste: null, // after paster onpaste: null, // after merge onmerge: null, // on table focus onfocus: function(){}, // on table blur onblur: function(){}, // after a header is changed onchangeheader: null, // after start editing oneditionstart: null, // after end editing oneditionend: null, // after styles are changed onchangestyle: null, // after meta channged onchangemeta:null });
Changelog:
v4.13.1 (2023-05-21)
- Update
v4.11.3 (2023-02-14)
- fixes title tooltip
v4.11.1 (2023-02-14)
- readonly checkbox/radio.
v4.10.1 (2022-01-24)
- Filters updates, hideColumn+filters, drag columns
v4.9.11 (2022-01-17)
- resetFilters to refresh viewport.
v4.9.9 (2021-12-28)
- Transpile refactor
v4.9.6 (2021-10-21)
- calendar fixes - auto detection.
v4.7.4 (2021-05-05)
- Update
v4.6.0 (2021-02-15)
- Renamed to Jspreadsheet
v4.5.0 (2021-01-04)
- Fixes, filters, etc.
v4.4.2 (2020-10-28)
- New event oncomments, new method getJsonRow.
v4.4.1 (2020-08-20)
- Updated
v4.3 (2020-06-27)
- New blanks option in the filters.
2020-06-17
- v4.2.3
2020-05-26
- v4.2.0
2020-04-28
- Doc (v4) updated
v4 (2020-04-21)
- Support workbooks/tabs
- Create a dymic jexcel table from a HTML static element
- Highlight the border from cells after CTRL+C
- Footer with formula support
- Multiple columns resize
- JSON update support (Helpers to update a remote server)
- Global super event (centralized method to dispatch all events in one)
- Custom helpers: =PROGRESS (progressbar), =RATING (5 star rating)
- Custom helpers: =COLUMN, =ROW, =CELL, =TABLE, =VALUE information to be used on formula execution
- Dynamic nested header updates
- A new column type for HTML editing
- New flags such as: includeHeadersOnCopy, persistance, filters, autoCasting, freezeColumns
- New events such as: onevent, onchangepage, onbeforesave, onsave
2020-02-11
- v3.9.0: insertRow fix
2020-01-17
- v3.8.0
2019-12-02
- v3.6.3: Bugfix
2019-11-15
- v3.6.2: Parse Text To Numbers in Formulas.
2019-09-27
- v3.5.0: New events, IE compatibility fixes, general fixes, and a few improvements.
2019-08-21
- v3.4.4: Fixes
2019-08-02
- v3.3.3: Fixes, better npm support together with jsuites. etc.
2019-07-09
- v3.0.1
- Doc updated
2019-05-25
- v2.1.0: Mobile touch fixes; Paste fixes & New CSV parser
2019-02-04
- v2.0.2: Fixed download problem
2018-09-04
- v1.5.7: Onmouseover events for the cells
2018-07-01
- CSV Double quote scape on copy/cut. New events onbeforepaste
2018-06-20
- v1.5.3/4: bugfix.
2018-05-18
- v1.5.2: multiple spreadsheet in the same page formula separation fix.
2018-05-02
- v1.5.1: Fixed headers with table overflow and table height. Minor Navigation fixes.
2017-12-07
- formula Number() fix.
2017-09-11
- new version 1.3.3 merged.
2017-05-08
- Arrows control on edition.
2017-05-04
- JS update.
2017-03-22
- New context menu and native dropdown filtering.
2017-03-14
- Undo-redo to include native checkboxes
2017-03-05
- Update jquery.jexcel.css
2017-02-20
- update
2017-02-11
- Formulas, and arrow excel-like navigation improvement.
2017-01-28
- CSV include first row as headers options. Download CSV including
2017-01-26
- Sorting and ordering.
2017-01-19
- Allows for dynamically adding columns.
2017-01-14
- Implemented undo/redo feature
This awesome jQuery plugin is developed by jspreadsheet. For more Advanced Usages, please check the demo page or visit the official website.