Programmatically table updates
1
Insert, remove and move columns and rows
The following example shows how to manage data programmatically in your javascript spreadsheet.
- Insert a new blank column at the end of the table
- Insert five new blank columns at the beginning of the table
- Insert a new column with pre-populated values at the end of the table
- Insert a new blank row at the end of the table
- Insert a new pre-populated row just after the second row.
- Create ten rows at the end of the table
- Delete the first row
- Delete the last column
- Move the forth row to the first position
- Move the first column to the third position
Source code
<html> <script src="https://bossanova.uk/jspreadsheet/v3/jexcel.js"></script> <script src="https://jsuites.net/v3/jsuites.js"></script> <link rel="stylesheet" href="https://bossanova.uk/jspreadsheet/v3/jexcel.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/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()">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://bossanova.uk/jspreadsheet/v3/jexcel.js"></script> <script src="https://jsuites.net/v3/jsuites.js"></script> <link rel="stylesheet" href="https://bossanova.uk/jspreadsheet/v3/jexcel.css" type="text/css" /> <link rel="stylesheet" href="https://jsuites.net/v3/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, }); </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>