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://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="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 = jspreadsheet(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: 'MWVhODY4NmUyMjJkMGU2OTkwOWYwNjgwOTNiNWYxNDNhMTM0MzQzNDlmNTk5MWNjMjAzYTQwNjNmMDQ3ZTRmZWVhMWU3NDMxMWVhY2IxYjFhOTEwZjg5MzQ1OGRkYTI5MjJiMGRmN2FhMWRlNDI1M2ZhYWQ0MmFjM2M5ZDc5MzEsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalE1TlRrM01Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09', }); </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://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="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 = jspreadsheet(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: 'MWVhODY4NmUyMjJkMGU2OTkwOWYwNjgwOTNiNWYxNDNhMTM0MzQzNDlmNTk5MWNjMjAzYTQwNjNmMDQ3ZTRmZWVhMWU3NDMxMWVhY2IxYjFhOTEwZjg5MzQ1OGRkYTI5MjJiMGRmN2FhMWRlNDI1M2ZhYWQ0MmFjM2M5ZDc5MzEsZXlKdVlXMWxJam9pU25Od2NtVmhaSE5vWldWMElpd2laR0YwWlNJNk1UWTVOalE1TlRrM01Dd2laRzl0WVdsdUlqcGJJbXB6Y0hKbFlXUnphR1ZsZEM1amIyMGlMQ0pqYjJSbGMyRnVaR0p2ZUM1cGJ5SXNJbXB6YUdWc2JDNXVaWFFpTENKamMySXVZWEJ3SWl3aWQyVmlJaXdpYkc5allXeG9iM04wSWwwc0luQnNZVzRpT2lJek5DSXNJbk5qYjNCbElqcGJJblkzSWl3aWRqZ2lMQ0oyT1NJc0luWXhNQ0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owSWl3aVltRnlJaXdpZG1Gc2FXUmhkR2x2Ym5NaUxDSnpaV0Z5WTJnaUxDSndjbWx1ZENJc0luTm9aV1YwY3lKZExDSmtaVzF2SWpwMGNuVmxmUT09', }); </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>