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

9/7/2020 JavaScript

jExcel 创建基于 Web 的电子表格应用jExcel 是一个轻量级的vanilla javascript插件,用于创建与Excel或任何其他电子表格软件兼容的基于Web的交互式表格和电子表格,可以创建可以交互的表格,兼容Excel,可以从 Js Array、JSON、CSV、XSLX文件创建表

# jExcel (opens new window) 创建基于 Web 的电子表格应用

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

# 安装

  • 通过 npm安装
npm install jexcel
1
  • 浏览器直接引用
 <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" />
1
2
3
4

# 基本使用

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

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

需要在 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', }
     ]
});
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

微信截图_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'))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

#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 })
  }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

# 加载数据

# 加载 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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 加载 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>
1
2
3
4
5
6
7
8
9
10
11
12

# 加载 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>
1
2
3
4
5
6
7
8
9
10
11
12
13

# 销毁表

<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 支持的数据类型

# 原生支持以下数据类型

text
numeric
hidden
dropdown
autocomplete
checkbox
radio
calendar
image
color
html
1
2
3
4
5
6
7
8
9
10
11

<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

# 可以定制其他的类型

如显示时间的控件 微信截图_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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
  • 定制时的重点
 { type: 'text', title:'Time', width:100, editor:customColumn },
1
openEditor: function(cell) {
// 通过原生方法创建新的控件
},
1
2
3
closeEditor : function(cell, save) {
// 关闭时,获取对应的值信息
},
1
2
3

# 支持搜索和分页

<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

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

增加、删除行和列

<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

# 支持的事件

<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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130

完整的事件列表 (opens new window)

# 支持右键

# 支持嵌套表头

# 支持懒加载

# 支持冻结列

# 支持列排序

# 支持列过滤

# 支持定制化的工具栏

# 支持定制化样式

# 支持定制化公式

# 支持拖拽

... 更多新特性值得你继续探索 (opens new window)