Keep updated

Documentation

Examples

Programmatically insert, remove and move columns and rows

Dealing with programmatically data changes in your javascript spreadsheet.



  1. Insert a new blank column at the end of the table
  2. Insert five new blank columns at the beginning of the table
  3. Insert a new column with pre-populated values at the end of the table
  4. Insert a new blank row at the end of the table
  5. Insert a new pre-populated row just after the second row.
  6. Create ten rows at the end of the table
  7. Move the forth row to the first position


Source code

<html>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/numeral.js/2.0.6/numeral.min.js"></script>

<script src="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/js/excel-formula.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/js/jquery.jexcel.js"></script>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/jexcel/1.5.7/css/jquery.jexcel.min.css" type="text/css" />

<div id="my"></div>

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

$('#my').jexcel({
    data:data,
    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' },
    ]
});

$('#my').jexcel('updateSettings', {
    table: function (instance, cell, col, row, val, id) {
        if (col == 2 || col == 3) {
            // Get text
            txt = $(cell).text();
            // Format text
            txt = numeral(txt).format('0,0.00');
            // Update cell value
            $(cell).html(' $ ' + txt);
        }
    }
});
</script>

<ol>
    <li><a href='' onclick="$('#my').jexcel('insertColumn'); event.preventDefault(); return false;">Insert a new blank column at the end of the table</a></li>
    <li><a href='' onclick="$('#my').jexcel('insertColumn', 5, null, 0); event.preventDefault(); return false;">Insert five new blank columns at the beginning of the table</a></li>
    <li><a href='' onclick="$('#my').jexcel('insertColumn', [ '0.99', '1.22', '3.11', '2.21' ]); event.preventDefault(); return false;">Insert a new column with pre-populated values at the end of the table</a></li>
    <li><a href='' onclick="$('#my').jexcel('insertRow'); event.preventDefault(); return false;">Insert a new blank row at the end of the table</a></li>
    <li><a href='' onclick="$('#my').jexcel('insertRow', [ 'Pears', 10, 0.59, '=B2*C2' ], 1); event.preventDefault(); return false;">Insert a new pre-populated row just after the second row.</a></li>
    <li><a href='' onclick="$('#my').jexcel('insertRow', 10); event.preventDefault(); return false;">Create ten rows at the end of the table</a></li>
    <li><a href='' onclick="$('#my').jexcel('moveRow', 3, 0); event.preventDefault(); return false;">Move the forth row to the first position</a></li>
</ol>

</html>