Back to Examples

Programmatically table updates

Insert, remove and move columns and rows

The following example shows how to manage data programmatically in your javascript spreadsheet.

<html>
<script src="https://jspreadsheet.com/v7/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

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

<script>
let 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'],
];

let table1 = jspreadsheet(document.getElementById('spreadsheet'), {
    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',
        },
    ],
    license: 'NDY2YzVmZWM1YTBhY2IzYzQwZGU2OGVmOGE0NzgyZGY5YzhkMzM2ZDM2MjY3MmFjYzQxMTM2NzA2MmU2M2Y0NjJmYmI5NzYwZGRlZDEyMTQ5MDBlZmRmMWU2NDgwMjFhMGIwMmU4NWRmN2FkYWYyMTY4OWNiNTA5ODFkZmU3MzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNeE5UWTJNekk0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==',
});

document.getElementById('btn1').onclick = function() { table1.insertColumn(); };
document.getElementById('btn2').onclick = function() { table1.insertColumn(2, 0, 1); };
document.getElementById('btn3').onclick = function() { table1.insertColumn(['0.99', '1.22', '3.11', '2.21']); };
document.getElementById('btn4').onclick = function() { table1.deleteColumn(); };
document.getElementById('btn5').onclick = function() { table1.moveColumn(0, 2); };
document.getElementById('btn6').onclick = function() { table1.hideColumn(0); };
document.getElementById('btn7').onclick = function() { table1.showColumn(0); };
</script>

<br>

<ol class='example cursor'>
    <li><button id="btn1">Insert a new blank column at the end</button></li>
    <li><button id="btn2">Insert two new blank columns at the beginning</button></li>
    <li><button id="btn3">Click to insert a new column with pre-populated values at the end of the table</button></li>
    <li><button id="btn4">Click to delete the last column</button></li>
    <li><button id="btn5">Click to move the first column to the third position</button></li>
    <li><button id="btn6">Hide the first column</button></li>
    <li><button id="btn7">Show the first column</button></li>
</ol>

</html>

Updating column width and row height

Update the table width and height properties.

<html>
<script src="https://jspreadsheet.com/v7/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jspreadsheet.com/v7/jspreadsheet.css" type="text/css" />
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />

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

<script>
let data2 = [
    [ 'Cheese', 10, 1.10, '=B1*C1'],
    [ 'Apples', 30, 0.40, '=B2*C2'],
    [ 'Carrots', 15, 0.45, '=B3*C3'],
    [ 'Oranges', 20, 0.49, '=B4*C4'],
];

let table2 = jspreadsheet(document.getElementById('spreadsheet'), {
    data:data2,
    colHeaders: [ 'Product', 'Quantity', 'Price', 'Total' ],
    colWidths: [ 300, 100, 100, 100 ],
    columns: [
        { type: 'autocomplete', source:[ 'Apples','Bananas','Carrots','Oranges','Cheese','Pears' ] },
        { type: 'number' },
        { type: 'number' },
        { type: 'number' },
    ],
    rowResize:true,
    license: 'NDY2YzVmZWM1YTBhY2IzYzQwZGU2OGVmOGE0NzgyZGY5YzhkMzM2ZDM2MjY3MmFjYzQxMTM2NzA2MmU2M2Y0NjJmYmI5NzYwZGRlZDEyMTQ5MDBlZmRmMWU2NDgwMjFhMGIwMmU4NWRmN2FkYWYyMTY4OWNiNTA5ODFkZmU3MzQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpNeE5UWTJNekk0TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0ozWldJaUxDSnNiMk5oYkdodmMzUWlYU3dpY0d4aGJpSTZJak0wSWl3aWMyTnZjR1VpT2xzaWRqY2lMQ0oyT0NJc0luWTVJaXdpZGpFd0lpd2lkakV4SWl3aVkyaGhjblJ6SWl3aVptOXliWE1pTENKbWIzSnRkV3hoSWl3aWNHRnljMlZ5SWl3aWNtVnVaR1Z5SWl3aVkyOXRiV1Z1ZEhNaUxDSnBiWEJ2Y25SbGNpSXNJbUpoY2lJc0luWmhiR2xrWVhScGIyNXpJaXdpYzJWaGNtTm9JaXdpY0hKcGJuUWlMQ0p6YUdWbGRITWlMQ0pqYkdsbGJuUWlMQ0p6WlhKMlpYSWlMQ0p6YUdGd1pYTWlYU3dpWkdWdGJ5STZkSEoxWlgwPQ==',
});

document.getElementById("setbtn").onclick = () => table2.setHeader(document.getElementById('col').value)
document.getElementById("getbtn").onclick = () => alert(table2.getHeader(document.getElementById('col').value))
</script>

<br/><select id='col'>
    <option value="0">Column 0</option>
    <option value="1">Column 1</option>
    <option value="2">Column 2</option>
    <option value="3">Column 3</option>
</select>

<input type='button' value='Set' id="setbtn"/>
<input type='button' value='Get' id="getbtn"/>

</html>

Edit this page on GitHub