Spreadsheet is a tabular format consisting of rows and columns. The intersection point of rows and columns are called as cells. The list of operations that you can perform in rows and columns are,
You can insert rows or columns anywhere in a spreadsheet. Use the allowInsert
property to enable or disable the insert option in Spreadsheet.
The rows can be inserted in the following ways,
insertRow
method, you can insert the rows once the component is loaded.The following code example shows the options for inserting rows in the spreadsheet.
ej.base.enableRipple(true);
var columns = [
{ width: 20 }, { width: 90 }, { width: 220 }, { width: 90 }, { width: 140 }, { width: 90 }, { width: 100 }, { width: 100 }
];
// Rows model which is going to insert dynamically
var rowsModel = [{
index: 9, // Need to specify the index for the first row collection, the specified rows will be inserted in this index.
cells: [{ value: '' }, { value: '8' }, { value: 'Northwoods Cranberry Sauce' }, { value: '3' }, { value: '12 - 12 oz jars' },
{ value: '40.00' }, { value: '6' }, { value: 'false' }]
},
{
cells: [{ value: '' }, { value: '9' }, { value: 'Mishi Kobe Niku' }, { value: '4' }, { value: '18 - 500 g pkgs.' }, { value: '97.00' },
{ value: '29' }, { value: 'true' }]
}];
var sheets = [{ ranges: [{ dataSource: data, startCell: 'B1' }], columns: columns }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
sheets: sheets,
created: function () {
// Applies style formatting before inserting the rows
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'B1:H1');
// inserting a empty row at 0th index
spreadsheet.insertRow();
// inserting 2 rows at the 9th index with data
spreadsheet.insertRow(rowsModel);
// Applies style formatting after the rows are inserted
spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
},
// Removed the unwanted support for this samples
showRibbon: false, showFormulaBar: false, showSheetTabs: false
});
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
<title>EJ2 SpreadSheet</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Typescript UI Controls">
<meta name="author" content="Syncfusion">
<link rel="shortcut icon" href="resources/favicon.ico">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-spreadsheet/styles/material.css" rel="stylesheet">
<link href="styles.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
<script src="system.config.js"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
<!--Element which is going to render-->
<div id="container">
<div id="spreadsheet"></div>
</div>
<script>
var ele = document.getElementById('container');
if(ele) {
ele.style.visibility = "visible";
}
</script>
<script src="index.js" type="text/javascript"></script>
</body></html>
The columns can be inserted in the following ways,
insertColumn
method, you can insert the columns once the component is loaded.The following code example shows the options for inserting columns in the spreadsheet.
ej.base.enableRipple(true);
var columns = [{ width: 90 }, { width: 220 }, { width: 90 }, { width: 140 }, { width: 100 }, { width: 100 }];
var sheets = [{ ranges: [{ dataSource: data, startCell: 'A2' }], columns: columns }];
// Cells model which we are going to update in the inserted 5th column dynamically
var cellsModel = [{ value: 'Unit Price', style: { fontWeight: 'bold', textAlign: 'center' } }, { value: '18.00' },
{ value: '19.00' }, { value: '10.00' }, { value: '22.00' }, { value: '21.35' }, { value: '25.00' }, { value: '30.00' },
{ value: '21.00' }, { value: '40.00' }, { value: '97.00' }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
sheets: sheets,
created: function () {
// Applies style formatting before inserting the column
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A2:G2');
// inserting a empty column at 0th index
spreadsheet.insertColumn();
// inserting 1 column at the 5th index with column model
spreadsheet.insertColumn([{ index: 5, width: 90 }]);
var rowIndex = 1;
// Updating the 5th column data
cellsModel.forEach(function (cell) {
spreadsheet.updateCell(cell, ej.spreadsheet.getCellAddress(rowIndex, 5)); rowIndex++;
});
// Applies style formatting after the columns are inserted
spreadsheet.cellFormat({ textAlign: 'center' }, 'B3:B12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D3:D12');
spreadsheet.cellFormat({ textAlign: 'center' }, 'F3:H12');
},
// Removed the unwanted support for this samples
showRibbon: false, showFormulaBar: false, showSheetTabs: false
});
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
<title>EJ2 SpreadSheet</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Typescript UI Controls">
<meta name="author" content="Syncfusion">
<link rel="shortcut icon" href="resources/favicon.ico">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-spreadsheet/styles/material.css" rel="stylesheet">
<link href="styles.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
<script src="system.config.js"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
<!--Element which is going to render-->
<div id="container">
<div id="spreadsheet"></div>
</div>
<script>
var ele = document.getElementById('container');
if(ele) {
ele.style.visibility = "visible";
}
</script>
<script src="index.js" type="text/javascript"></script>
</body></html>
Delete support provides an option for deleting the rows and columns in the spreadsheet. Use allowDelete
property to enable or disable the delete option in Spreadsheet.
The rows and columns can be deleted dynamically in the following ways,
delete
method, you can delete the loaded rows and columns.The following code example shows the delete operation of rows and columns in the spreadsheet.
ej.base.enableRipple(true);
var columns = [{ width: 90 }, { width: 220 }, { width: 90 }, { width: 140 }, { width: 90 }, { width: 100 }, { width: 100 }];
var sheets = [{ name: 'Sheet1', ranges: [{ dataSource: data }], columns: columns }, { name: 'Sheet2', ranges: [{ dataSource: data }], columns: columns }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
sheets: sheets,
allowDelete: true, // to enable or disable the delete option in Spreadsheet
created: function () {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
// deleting the rows from 8th to 10th index. To delete row, the third argument of enum type is passed as 'Row', the last argument specifies the sheet name or index in which the delete operation will perform. By default,active sheet will be considered. It is applicable only for model type Row and Column.
spreadsheet.delete(8, 10, 'Row', 0); // startIndex, endIndex, Row, sheet index
// deleting the 2nd and 5th indexed columns
spreadsheet.delete(2, 2, 'Column', 'Sheet2');
spreadsheet.delete(5, 5, 'Column');
spreadsheet.delete(0, 0, "Sheet"); // delete the first sheet. sheet index starts from 0
// Applies style formatting after deleted the rows and columns
spreadsheet.cellFormat({ textAlign: 'center' }, 'A2:A8');
spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:G8');
},
// Removed the unwanted support for this samples
showRibbon: false, showFormulaBar: false
});
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
<title>EJ2 SpreadSheet</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Typescript UI Controls">
<meta name="author" content="Syncfusion">
<link rel="shortcut icon" href="resources/favicon.ico">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-spreadsheet/styles/material.css" rel="stylesheet">
<link href="styles.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
<script src="system.config.js"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
<!--Element which is going to render-->
<div id="container">
<div id="spreadsheet"></div>
</div>
<script>
var ele = document.getElementById('container');
if(ele) {
ele.style.visibility = "visible";
}
</script>
<script src="index.js" type="text/javascript"></script>
</body></html>
The following features have some limitations in Insert/Delete:
You can show or hide the rows and columns in the spreadsheet through property binding, method, and context menu.
The rows can be hidden or shown through the following ways,
hidden
property in row, you can hide/show the rows at initial load.hideRow
method, you can hide the rows by specifying the start and end row index, set the last argument hide
as false
to unhide the hidden rows.The columns can be hidden or shown through following ways,
hidden
property in columns, you can hide/show the columns at initial load.hideColumn
method, you can hide the columns by specifying the start and end column index, set the last argument hide
as false
to unhide the hidden columns.The following code example shows the hide/show rows and columns operation in the spreadsheet.
ej.base.enableRipple(true);
// Hiding the 1st and 2nd column index through property binding
var columns = [{ width: 150 }, { width: 100, hidden: true }, { width: 100, hidden: true }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }, { width: 80 }];
// Hiding the 2nd and 3rd row index through property binding
var rows = [{ index: 2, hidden: true }, { hidden: true }];
var sheets = [{ ranges: [{ dataSource: data }], columns: columns, rows: rows }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
sheets: sheets,
created: function () {
spreadsheet.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
// Unhide the 2nd index hidden column
spreadsheet.hideColumn(1, 1, false);
// Unhide the 3rd index hidden row
spreadsheet.hideRow(3, 3, false);
// Hiding the 6th index column
spreadsheet.hideColumn(6);
// Hiding the 8th and 9th index row
spreadsheet.hideRow(8, 9);
spreadsheet.cellFormat({ textAlign: 'center' }, 'D2:H11');
},
// Removed the unwanted support for this samples
showRibbon: false, showFormulaBar: false, showSheetTabs: false
});
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
<title>EJ2 SpreadSheet</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Typescript UI Controls">
<meta name="author" content="Syncfusion">
<link rel="shortcut icon" href="resources/favicon.ico">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-spreadsheet/styles/material.css" rel="stylesheet">
<link href="styles.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
<script src="system.config.js"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
<!--Element which is going to render-->
<div id="container">
<div id="spreadsheet"></div>
</div>
<script>
var ele = document.getElementById('container');
if(ele) {
ele.style.visibility = "visible";
}
</script>
<script src="index.js" type="text/javascript"></script>
</body></html>
You can change the size of rows and columns in the spreadsheet by using setRowsHeight and setColumnsWidth methods.
You can change the height of single or multiple rows by using the setRowsHeight method.
You can provide the following type of ranges to the method:
['2:2']
['1:100']
['1:10', '15:25', '30:40']
[Sheet1!1:50, 'Sheet2!1:50', 'Sheet3!1:50']
The following code example shows how to change the height for single/multiple rows in the spreadsheet.
ej.base.enableRipple(true);
var sheets = [{ ranges: [{ dataSource: data }], }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
sheets: sheets,
created: function () {
spreadsheet.setRowsHeight(40, ['2']);
// To change height for multiple rows
spreadsheet.setRowsHeight(40, ['4:8', '10:12']);
},
});
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
<title>EJ2 SpreadSheet</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Typescript UI Controls">
<meta name="author" content="Syncfusion">
<link rel="shortcut icon" href="resources/favicon.ico">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-spreadsheet/styles/material.css" rel="stylesheet">
<link href="styles.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
<script src="system.config.js"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
<!--Element which is going to render-->
<div id="container">
<div id="spreadsheet"></div>
</div>
<script>
var ele = document.getElementById('container');
if(ele) {
ele.style.visibility = "visible";
}
</script>
<script src="index.js" type="text/javascript"></script>
</body></html>
You can change the width of single or multiple columns by using the setColumnsWidth method.
You can provide the following type of ranges to the method:
['F:F']
['A:F']
['A:C', 'G:I', 'K:M']
[Sheet1!A:H, 'Sheet2!A:H', 'Sheet3!A:H']
The following code example shows how to change the width for single/multiple columns in the spreadsheet.
ej.base.enableRipple(true);
var sheets = [{ ranges: [{ dataSource: data }] }];
var spreadsheet = new ej.spreadsheet.Spreadsheet({
sheets: sheets,
created: function () {
// To change width of single column
this.spreadsheet.setColumnsWidth(100, ['F']);
// To change width of multiple columns
this.spreadsheet.setColumnsWidth(120, ['A:C', 'G:I', 'K:M']);
},
});
spreadsheet.appendTo('#spreadsheet');
<!DOCTYPE html><html lang="en"><head>
<title>EJ2 SpreadSheet</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Typescript UI Controls">
<meta name="author" content="Syncfusion">
<link rel="shortcut icon" href="resources/favicon.ico">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-base/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-inputs/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-buttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-splitbuttons/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-lists/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-navigations/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-popups/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-dropdowns/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-grids/styles/material.css" rel="stylesheet">
<link href="//cdn.syncfusion.com/ej2/21.2.3/ej2-spreadsheet/styles/material.css" rel="stylesheet">
<link href="styles.css" rel="stylesheet">
<script src="https://cdnjs.cloudflare.com/ajax/libs/core-js/2.4.1/shim.min.js"></script>
<script src="system.config.js"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
<script src="https://cdn.syncfusion.com/ej2/21.2.3/dist/ej2.min.js" type="text/javascript"></script>
<script src="es5-datasource.js" type="text/javascript"></script>
</head>
<body>
<!--Element which is going to render-->
<div id="container">
<div id="spreadsheet"></div>
</div>
<script>
var ele = document.getElementById('container');
if(ele) {
ele.style.visibility = "visible";
}
</script>
<script src="index.js" type="text/javascript"></script>
</body></html>