jExcel 创建基于 Web 的电子表格应用

jExcel 创建基于 Web 的电子表格应用

jExcel 是一个轻量级的vanilla javascript插件，用于创建与Excel或任何其他电子表格软件兼容的基于Web的交互式表格和电子表格，可以创建可以交互的表格，兼容Excel，可以从 Js Array、JSON、CSV、XSLX文件创建表格。可以从Excel中直接复制，然后粘贴在jExcel表格中。而且可以定制化，还可以结合第三方的库使用，支持 React、Vue、JQuery等。

jexcel.gif

原文链接

安装

• 通过 npm安装

npm install jexcel

• 浏览器直接引用

 <script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script> <script src="https://bossanova.uk/jsuites/v2/jsuites.js"></script> <link rel="stylesheet" href="https://bossanova.uk/jsuites/v2/jsuites.css" type="text/css" /> <link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" />

基本使用

需要先创建一个div的容器，来显示表格

<div id="spreadsheet"></div>

需要在 script中初始化表格，这样表格就会显示出来了

var data = [    ['Jazz', 'Honda', '2019-02-12', '', true, '$ 2.000,00', '#777700'],    ['Civic', 'Honda', '2018-07-11', '', true, '$ 4.000,01', '#007777'],];jexcel(document.getElementById('spreadsheet'), {    data:data,    columns: [        { type: 'text', title:'Car', width:120 },        { type: 'dropdown', title:'Make', width:200, source:[ "Alfa Romeo", "Audi", "Bmw" ] },        { type: 'calendar', title:'Available', width:200 },        { type: 'image', title:'Photo', width:120 },        { type: 'checkbox', title:'Stock', width:80 },        { type: 'numeric', title:'Price', width:100, mask:'$ #.##,00', decimal:',' },        { type: 'color', width:100, render:'square', }     ]});

微信截图_20200907214625.png

在React中使用

class Jexcel extends React.Component {    constructor(props) {        super(props);        this.options = props.options;        this.wrapper = React.createRef();    }    componentDidMount = function() {        this.el = jexcel(this.wrapper.current, this.options);    }    addRow = function() {        this.el.insertRow();    }    render() {        return (            <div>                <div></div><br/><br/>                <input type='button' value='Add new row' onClick={() => this.addRow()}></input>            </div>        );    }}var options = {    data:[[]],    minDimensions:[10,10],};ReactDOM.render(<Jexcel options={options} />, document.getElementById('spreadsheet'))

在 Vue中使用

import jexcel from 'jexcel'import 'jexcel/dist/jexcel.css'var data = [  ['Jazz', 'Honda', '2019-02-12', '', true, '$ 2.000,00', '#777700'],  ['Civic', 'Honda', '2018-07-11', '', true, '$ 4.000,01', '#007777']]var options = {  data: data,  allowToolbar:true,  columns: [    { type: 'text', title: 'Car', width: '120px' },    { type: 'dropdown', title: 'Make', width: '250px', source: [ 'Alfa Romeo', 'Audi', 'Bmw' ] },    { type: 'calendar', title: 'Available', width: '250px' },    { type: 'image', title: 'Photo', width: '120px' },    { type: 'checkbox', title: 'Stock', width: '80px' },    { type: 'numeric', title: 'Price', width: '100px', mask: '$ #.##,00', decimal: ',' },    { type: 'color', width: '100px', render: 'square' }  ]}export default {  name: 'App',  mounted: function () {    let spreadsheet = jexcel(this.$el, options)    Object.assign(this, { spreadsheet })  }}

加载数据

加载 javascript数组

<div id='my-spreadsheet'></div><script>data = [    ['Mazda', 2001, 2000],    ['Pegeout', 2010, 5000],    ['Honda Fit', 2009, 3000],    ['Honda CRV', 2010, 6000],];jexcel(document.getElementById('my-spreadsheet'), {    data:data,    columns:[        { title:'Model', width:300 },        { title:'Price', width:80 },        { title:'Model', width:100 }    ]});</script>

加载 JSON文件

<div id='my-spreadsheet'></div><script>jexcel(document.getElementById('my-spreadsheet'), {    url:'data.json',    columns:[        { title:'Model', width:300 },        { title:'Price', width:80 },        { title:'Model', width:100 }    ]});</script>

加载 CSV文件

<div id='my-spreadsheet'></div><script>jexcel(document.getElementById('my-spreadsheet'), {    csv:'demo.csv',    csvHeaders:true,    columns:[        { width:300 },        { width:80 },        { width:100 }    ]});</script>

销毁表

<script>var table = jexcel(document.getElementById('my-spreadsheet'), {    csv:'demo.csv',    csvHeaders:true,    columns:[        { width:300 },        { width:80 },        { width:100 }    ]});// If second argument is true will destroy all handlers and you can't create any other instance.jexcel.destroy(document.getElementById('my-spreadsheet'), true);</script>

原文链接

支持的数据类型

原生支持以下数据类型

textnumerichiddendropdownautocompletecheckboxradiocalendarimagecolorhtml

如

<html><script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script><script src="https://bossanova.uk/jsuites/v3/jsuites.js"></script><link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" /><link rel="stylesheet" href="https://bossanova.uk/jsuites/v3/jsuites.css" type="text/css" /><div id="spreadsheet"></div><script>var data = [    ['Jazz', 'Honda', '2019-02-12', '', true, '$ 2.000,00', '#777700'],    ['Civic', 'Honda', '2018-07-11', '', true, '$ 4.000,01', '#007777'],];jexcel(document.getElementById('spreadsheet'), {    data:data,    columns: [        { type: 'text', title:'Car', width:120 },        { type: 'dropdown', title:'Make', width:200, source:[ "Alfa Romeo", "Audi", "Bmw" ] },        { type: 'calendar', title:'Available', width:200 },        { type: 'image', title:'Photo', width:120 },        { type: 'checkbox', title:'Stock', width:80 },        { type: 'numeric', title:'Price', width:100, mask:'$ #.##,00', decimal:',' },        { type: 'color', width:100, render:'square', }     ]});</script></html>

image

可以定制其他的类型

如显示时间的控件

微信截图_20200907220335.png

<html><script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script><script src="https://bossanova.uk/jsuites/v3/jsuites.js"></script><link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" /><link rel="stylesheet" href="https://bossanova.uk/jsuites/v3/jsuites.css" type="text/css" /><link rel="stylesheet" type="text/css" href="http://weareoutman.github.io/clockpicker/dist/jquery-clockpicker.min.css" /><script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script><script src="https://weareoutman.github.io/clockpicker/dist/jquery-clockpicker.min.js"></script><div id="custom"></div><script>var data2 = [    ['PHP', '14:00'],    ['Javascript', '16:30'],];var customColumn = {    // Methods    closeEditor : function(cell, save) {        var value = cell.children[0].value;        cell.innerHTML = value;        return value;    },    openEditor : function(cell) {        // Create input        var element = document.createElement('input');        element.value = cell.innerHTML;        // Update cell        cell.classList.add('editor');        cell.innerHTML = '';        cell.appendChild(element);        $(element).clockpicker({            afterHide:function() {                setTimeout(function() {                    // To avoid double call                    if (cell.children[0]) {                        myTable.closeEditor(cell, true);                    }                });            }        });        // Focus on the element        element.focus();    },    getValue : function(cell) {        return cell.innerHTML;    },    setValue : function(cell, value) {        cell.innerHTML = value;    }}myTable = jexcel(document.getElementById('custom'), {    data:data2,    columns: [        { type: 'text', title:'Course Title', width:300 },        { type: 'text', title:'Time', width:100, editor:customColumn },     ]});</script></html>

• 定制时的重点

 { type: 'text', title:'Time', width:100, editor:customColumn },

openEditor: function(cell) {// 通过原生方法创建新的控件}，

closeEditor : function(cell, save) {// 关闭时，获取对应的值信息},

支持搜索和分页

<html><script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script><script src="https://bossanova.uk/jsuites/v3/jsuites.js"></script><link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" /><link rel="stylesheet" href="https://bossanova.uk/jsuites/v3/jsuites.css" type="text/css" /><link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.datatables.css" type="text/css" /><div id="spreadsheet"></div><script>jexcel(document.getElementById('spreadsheet'), {    csv:'https://bossanova.uk/jexcel/v4/demo.csv',    csvHeaders:true,    search:true,    pagination:10,    columns: [        { type:'text', width:300 },        { type:'text', width:200 },        { type:'text', width:100 },        { type:'text', width:100 },        { type:'text', width:100 },     ]});<script></script></html>

image

通过程序动态设置表格内容

增加、删除行和列

<html><script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script><script src="https://bossanova.uk/jsuites/v3/jsuites.js"></script><link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" /><link rel="stylesheet" href="https://bossanova.uk/jsuites/v3/jsuites.css" type="text/css" /><div id="spreadsheet1"></div><script>var data1 = [    [ 'Cheese', 10, 1.10, '=B1*C1'],    [ 'Apples', 30, 0.40, '=B2*C2'],    [ 'Carrots', 15, 0.45, '=B3*C3'],    [ 'Oranges', 20, 0.49, '=B4*C4'],];var table1 = jexcel(document.getElementById('spreadsheet1'), {    data:data1,    columns: [        {            title: 'Product',            type: 'autocomplete',            source:[ 'Apples','Bananas','Carrots','Oranges','Cheese','Pears' ],            width:'300px',        },        {            title: 'Quantity',            type: 'number',            width:'100px',        },        {            title: 'Price',            type: 'number',            width:'100px',        },        {            title: 'Total',            type: 'number',            width:'100px',        },    ],    rowResize: true,    columnDrag: true,});</script><br><ol class='example'>    <li><a onclick="table1.insertColumn()">在表格末尾增加新的一列</a></li>    <li><a onclick="table1.insertColumn(5, 0, 1, null);">在表格开头增加5列空白表格</a></li>    <li><a onclick="table1.insertColumn([ '0.99', '1.22', '3.11', '2.21' ]);">在表格末尾增加带数据的列</a></li>    <li><a onclick="table1.insertRow()">在末尾增加新的一行</a></li>    <li><a onclick="table1.insertRow([ 'Pears', 10, 0.59, '=B2*C2' ], 1);">在第二行后增加新的带数据的一行</a></li>    <li><a onclick="table1.insertRow(10);">创建10行在表格末尾</a></li>    <li><a onclick="table1.deleteRow(0, 1);">删除第一行</a></li>    <li><a onclick="table1.deleteColumn();">删除最后一列</a></li>    <li><a onclick="table1.moveRow(3, 0);">移动地四行到一行</a></li>    <li><a onclick="table1.moveColumn(0, 2);">移动第一列到第三列的位置</a></li></ol></html>

支持的事件

<html><script src="https://bossanova.uk/jexcel/v4/jexcel.js"></script><script src="https://bossanova.uk/jsuites/v3/jsuites.js"></script><link rel="stylesheet" href="https://bossanova.uk/jexcel/v4/jexcel.css" type="text/css" /><link rel="stylesheet" href="https://bossanova.uk/jsuites/v3/jsuites.css" type="text/css" /><div id="spreadsheet"></div><script>var changed = function(instance, cell, x, y, value) {    var cellName = jexcel.getColumnNameFromId([x,y]);    $('#log').append('New change on cell ' + cellName + ' to: ' + value + '');}var beforeChange = function(instance, cell, x, y, value) {    var cellName = jexcel.getColumnNameFromId([x,y]);    $('#log').append('The cell ' + cellName + ' will be changed');}var insertedRow = function(instance) {    $('#log').append('Row added');}var insertedColumn = function(instance) {    $('#log').append('Column added');}var deletedRow = function(instance) {    $('#log').append('Row deleted');}var deletedColumn = function(instance) {    $('#log').append('Column deleted');}var sort = function(instance, cellNum, order) {    var order = (order) ? 'desc' : 'asc';    $('#log').append('The column  ' + cellNum + ' sorted by ' + order + '');}var resizeColumn = function(instance, cell, width) {    $('#log').append('The column  ' + cell + ' resized to width ' + width + ' px');}var resizeRow = function(instance, cell, height) {    $('#log').append('The row  ' + cell + ' resized to height ' + height + ' px');}var selectionActive = function(instance, x1, y1, x2, y2, origin) {    var cellName1 = jexcel.getColumnNameFromId([x1, y1]);    var cellName2 = jexcel.getColumnNameFromId([x2, y2]);    $('#log').append('The selection from ' + cellName1 + ' to ' + cellName2 + '');}var loaded = function(instance) {    $('#log').append('New data is loaded');}var moveRow = function(instance, from, to) {    $('#log').append('The row ' + from + ' was move to the position of ' + to + ' ');}var moveColumn = function(instance, from, to) {    $('#log').append('The col ' + from + ' was move to the position of ' + to + ' ');}var blur = function(instance) {    $('#log').append('The table ' + $(instance).prop('id') + ' is blur');}var focus = function(instance) {    $('#log').append('The table ' + $(instance).prop('id') + ' is focus');}var paste = function(data) {    $('#log').append('Paste on the table ' + $(instance).prop('id') + '');}var data = [    ['Mazda', 2001, 2000, '2006-01-01'],    ['Pegeout', 2010, 5000, '2005-01-01'],    ['Honda Fit', 2009, 3000, '2004-01-01'],    ['Honda CRV', 2010, 6000, '2003-01-01'],];jexcel(document.getElementById('spreadsheet'), {    data:data,    rowResize:true,    columnDrag:true,    columns: [        { type: 'text', width:'200' },        { type: 'text', width:'100' },        { type: 'text', width:'100' },        { type: 'calendar', width:'100' },    ],    onchange: changed,    onbeforechange: beforeChange,    oninsertrow: insertedRow,    oninsertcolumn: insertedColumn,    ondeleterow: deletedRow,    ondeletecolumn: deletedColumn,    onselection: selectionActive,    onsort: sort,    onresizerow: resizeRow,    onresizecolumn: resizeColumn,    onmoverow: moveRow,    onmovecolumn: moveColumn,    onload: loaded,    onblur: blur,    onfocus: focus,    onpaste: paste,});</script></html>

完整的事件列表
原文链接

支持右键

支持嵌套表头

支持懒加载

支持冻结列

支持列排序

支持列过滤

支持定制化的工具栏

支持定制化样式

支持定制化公式

支持拖拽

…
更多新特性值得你继续探索

原文链接

原著是一个有趣的人,若有侵权,请通知删除