美文网首页让前端飞Web前端之路设计方案
jExcel 创建基于 Web 的电子表格应用

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

作者: CoderMiner | 来源:发表于2020-09-07 22:26 被阅读0次

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>

原文链接

支持的数据类型

原生支持以下数据类型

text
numeric
hidden
dropdown
autocomplete
checkbox
radio
calendar
image
color
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 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="http://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>

完整的事件列表
原文链接

支持右键

支持嵌套表头

支持懒加载

支持冻结列

支持列排序

支持列过滤

支持定制化的工具栏

支持定制化样式

支持定制化公式

支持拖拽

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

原文链接

相关文章

网友评论

    本文标题：jExcel 创建基于 Web 的电子表格应用

    本文链接：https://www.haomeiwen.com/subject/svqrektx.html

    延伸阅读

    深度阅读

    • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

    栏目导航

    热点阅读

    让前端飞

    Web前端之路

    设计方案

    技术分享

    前端实践

    前端技术转载

    关于我们|服务条款|联系我们|jExcel 创建基于 Web 的电子表格应用|投稿指南|网站地图|RSS订阅|排版工具|手机版

    提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

    Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

    备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

    本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

    所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！