JExcel creates web-based spreadsheet applications

jExcelCreate a Web-based spreadsheet application

jExcelIs a lightweight Vanilla javascript plugin for creating Web-based interactive tables and spreadsheets that are compatible with Excel or any other spreadsheet software. You can create interactive tables that are compatible with Excel and can be accessed fromJs Array,JSON,CSV,XSLXFile creation table. You can copy it directly from Excel and paste it injExcelIn the table. And can be customized, can also be combined with third-party library use, supportReact,Vue,JQueryAnd so on.

The original link

The installation

• throughnpmThe installation

npm install jexcel
Copy the code

• Browser direct reference

 <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" />
Copy the code

The basic use

You need to create a div container to display the table

<div id="spreadsheet"></div>
Copy the code

You need to initialize the table in script so that it will be displayed

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', } ] });Copy the code

inReactThe use of

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'))
Copy the code

inVueThe use of

import jexcel from 'jexcel' import 'jexcel/dist/jexcel.css' var data = [ ['Jazz', 'Honda', '2019-02-12', '', true, 'at $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 }) } }Copy the code

Load the data

loadingjavascriptAn array of

<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>
Copy the code

loadingJSONfile

<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>
Copy the code

loadingCSVfile

<div id='my-spreadsheet'></div>

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

Destruction of table

<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>
Copy the code

The original link

Supported data types

The following data types are supported natively

text
numeric
hidden
dropdown
autocomplete
checkbox
radio
calendar
image
color
html
Copy the code

Such as

<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, 'at $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>Copy the code

Other types can be customized

A control that displays the time, for example

<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>Copy the code

• Focus on customization

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

OpenEditor: function(cell) {// Create a new control using native methods},Copy the code

CloseEditor: function(cell, save) {Copy the code

Support for search and paging

<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>
Copy the code

Dynamically set the table content through the program

Add and delete rows and columns

<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,' = C2 B2 * '], [' 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()"> Add a new column </a></li> <li><a onclick="table1.insertColumn(5, 0, 1, null);" > at the beginning of the form to increase five columns blank < / 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', 3, 0, 0], 1); </a></li> <li><a onclick=" table1.insertrow (10);" </a></li> <li><a onclick=" table1.deleterow (0, 1);" > delete the first line < / a > < / li > < li > < a onclick = "table1. DeleteColumn ();" The last column > delete < / a > < / li > < li > < a onclick = "table1. MoveRow (3, 0);" > move to four lines to one line < / a > < / li > < li > < a onclick = "table1. MoveColumn (0, 2);" > Move column 1 to column 3 </a></li> </ OL ></ HTML >Copy the code

Supported events

<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>
Copy the code

Full event list original link

Support right

Support for nested table headers

Lazy loading support

Support for frozen columns

Support column sorting

Support column filtering

Supports customized toolbars

Support for custom styles

Support for customized formulas

Support drag and drop

. More new features are worth exploring

The original link