Dynamic Spreadsheet-like Data Grid - jExcel

Dynamic Spreadsheet-like Data Grid - jExcel
File Size: 331 KB
Views Total:
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.

Table Of Contents:

How to use it (v3+, 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.

// External data
url:null,
// Data
data:[[]],
// Copy behavior
copyCompatibility:false,
// Rows and columns definitions
rows:[],
columns:[],
// Deprected legacy options
colHeaders:[],
colWidths:[],
colAlignments:[],
nestedHeaders:null,
// Column width that is used by default
defaultColWidth:50,
// Spare rows and columns
minSpareRows:0,
minSpareCols:0,
// Minimal table dimensions
minDimensions:[0,0],
// Allow Export
allowExport:true,
// Allow column sorting
columnSorting:true,
// Allow column resizing
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 column delete
allowDeleteColumn:true,
// Allow rename column
allowRenameColumn:true,
// Allow comments
allowComments:false,
// Global wrap
wordWrap:false,
// CSV source
csv:null,
// Filename
csvFileName:'jexcel',
// Consider first line as header
csvHeaders:true,
// Delimiters
csvDelimiter:',',
// 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,
// Style
style:null,
// Customize any cell behavior
updateTable:null,

5. Callback functions.

onload:null,
onchange:null,
onbeforechange:null,
oninsertrow:null,
oninsertcolumn:null,
ondeleterow:null,
ondeletecolumn:null,
onmoverow:null,
onmovecolumn:null,
onresizerow:null,
onresizecolumn:null,
onsort:null,
onselection:null,
onpaste:null,
onmerge:null,
onfocus:null,
onblur:null,

6. Localize the plugin.

text:{
  noRecordsFound: 'No records found',
  showingPage: 'Showing page {0} of {1} entries',
  show: 'Show ',
  search: 'Search',
  entries: ' entries',
  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 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]);

// 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]);

// Destroy merged by column name
// destroyMerged: Destroy all merged cells 
myTable.destroyMerge();

// 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]);

// 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]);

// 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]);
moveColumn: 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]);

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(){}, 

  // 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:

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 paulhodel. For more Advanced Usages, please check the demo page or visit the official website.