jExcel是一个强大的jQuery和Vanilla JavaScript插件,它可以让您从本地JS数据甚至JSON、CSV、XSLX文件动态生成类似CRUD的电子表格数据网格(表)。
易于与任何第三方插件集成数据操作插件,如输入掩码、货币格式、键值下拉、颜色选择器等。还附带了一个onchange处理程序,它将在电子表格中的每一次更改时启动。
请注意,最新的jExcel(v3.0+)已经放弃了对jQuery的支持。您可以将其用作普通的JavaScript库。在此处下载。
1.将jExcel和jSuites导入到文档中。
<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.创建一个空的DIV元素来保存电子表格。
<div id="demo"></div>
3.根据您提供的数据集生成电子表格。。
// 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.具有默认值的所有可能选项。
// 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.回调函数。
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.对插件进行本地化。
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方法。
// 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();
1.在网页中添加对jQuery库和jQuery jExcel插件的JS和CSS文件的引用。
<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.根据您的需要加载扩展。所有可能的扩展:
<!-- 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.创建一个空的DIV元素,该元素将用作电子表格的容器。
<div id="demo1"></div>
4.定义要在电子表格中显示的JS数据数组。
data1 = [ ['Google', 1998, 807.80], ['Apple', 1976, 116.52], ['Yahoo', 1994, 38.66], ];
5.初始化电子表格并完成。
$('#demo1').jexcel({ data:data1 });
6.或者从外部JSON文件加载数据。
$('#demo1').jexcel({ url:'/json' });
7.如果您想将外部CSV文件加载到电子表格中。需要CSV扩展名。
$('#demo1').jexcel({ csv:'demo.csv', csvHeaders:true })
8.将电子表格数据导出为CSV文件:
$('#demo1').jexcel('download');
9.自定义电子表格的默认插件设置:
$('#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方法。
// 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.事件处理程序。
$('#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 });
版本4.11.3(2023-02-14)
版本4.11.1(2023-02-14)
版本4.10.1(2022-01-24)
版本4.9.11(2022-01-17)
v4.9.9 (2021-12-28)
v4.9.6 (2021-10-21)
v4.7.4 (2021-05-05)
v4.6.0 (2021-02-15)
v4.5.0 (2021-01-04)
第4.4.2版(2020-10-28年)
版本4.4.1(2020-08-20)
版本4.3(2020-06-27)
2020-06-17
2020-05-26
2020-04-28
v4 (2020-04-21)
2020-02-11
2020-01-17
2019-12-02
2019-11-15
2019-09-27
2019-08-21
2019-08-02
2019-07-09
2019-05-25
2019-02-04
2018-09-04
2018-07-01
2018-06-20
2018-05-18
2018-05-02
2017-12-07
2017-09-11
2017-05-08
2017-05-04
2017-03-22
2017-03-14
2017-03-05
2017-02-20
2017-02-11
2017-01-28
2017-01-26
2017-01-19
2017-01-14