Back to Examples

Programmatically table updates


1

Insert, remove and move columns and rows

The following example shows how to manage data programmatically 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. Delete the first row
  8. Delete the last column
  9. Move the forth row to the first position
  10. Move the first column to the third position


Source code

<html>
<script src="https://jexcel.net/v5/jexcel.js"></script>
<script src="https://jexcel.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jexcel.net/v5/jexcel.css" type="text/css" />
<link rel="stylesheet" href="https://jexcel.net/v5/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',
        },
    ],
    license: '39130-64ebc-bd98e-26bc4',
});
</script>

<br>

<ol class='example'>
    <li><a onclick="table1.insertColumn()">Insert a new blank column at the end of the table</a></li>
    <li><a onclick="table1.insertColumn(5, 0, 1, null);">Insert five new blank columns at the beginning of the table</a></li>
    <li><a onclick="table1.insertColumn([ '0.99', '1.22', '3.11', '2.21' ]);">Insert a new column with pre-populated values at the end of the table</a></li>
    <li><a onclick="table1.insertRow()">Insert a new blank row at the end of the table</a></li>
    <li><a onclick="table1.insertRow([ 'Pears', 10, 0.59, '=B2*C2' ], 1);">Insert a new pre-populated row just after the second row</a></li>
    <li><a onclick="table1.insertRow(10);">Create ten rows at the end of the table</a></li>
    <li><a onclick="table1.deleteRow(0, 1);">Delete the first row</a></li>
    <li><a onclick="table1.deleteColumn();">Delete the last column</a></li>
    <li><a onclick="table1.moveRow(3, 0);">Move the forth row to the first position</a></li>
    <li><a onclick="table1.moveColumn(0, 2);">Move the first column to the third position</a></li>
</ol>

</html>


2

Updating column width and row height

Update the table width and height properties.





Source code

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

<div id="spreadsheet1"></div>

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

var table2 = jexcel(document.getElementById('spreadsheet2'), {
    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: '39130-64ebc-bd98e-26bc4',
});
</script>

<select id='columnNumber'>
<option value='0'>Column 1</option>
<option value='1'>Column 2</option>
<option value='2'>Column 3</option>
<option value='3'>Column 4</option>
</select>

<input type='button' value='Set column width to 200px' onclick="table2.setWidth(document.getElementById('columnNumber').value, 200)">
<input type='button' value='Set first row to height 100px' onclick="table2.setHeight(0, 100)">

</html>