Worksheet is a collection of cells organized in the form of rows and columns that allows you to store, format, and manipulate the data.
You can dynamically add or insert a sheet by one of the following ways,
Add Sheet
button in the sheet tab. This will add a new empty sheet next to current active sheet.Insert
option from the context menu to insert a new empty sheet before the current active sheet.insertSheet
method, you can insert one or more sheets at your desired index.The following code example shows the insert sheet operation in spreadsheet.
@Html.EJS().Spreadsheet("spreadsheet").ShowRibbon(false).ShowFormulaBar(false).Created("created").Sheets(sheet =>
{
sheet.Name("Shipment Details").Range(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
}).Columns(column =>
{
column.Width(130).Add();
column.Width(220).Add();
column.Width(90).Add();
column.Width(140).Add();
column.Width(90).Add();
column.Width(100).Add();
column.Width(100).Add();
}).Add();
}).Render()
<script>
function created() {
// Applies style formatting to the active sheet before inserting a new sheet
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
this.cellFormat({ textAlign: 'center' }, 'D2:H11');
// inserting a new sheet with data at 1st index
// You can also insert empty sheets by specifying the start and end sheet index instead of sheet model
this.insertSheet([{
index: 1,
name: 'Inserted Sheet',
ranges: [{ dataSource: @Html.Raw(JsonConvert.SerializeObject(@ViewBag.DefaultData)) }],
columns: [{ width: 150 }, { width: 110 }, { width: 110 }, { width: 85 }, { width: 85 }, { width: 85 }, { width: 85 },
{ width: 85 }]
}]);
// Applies style formatting for the inserted sheet
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'Inserted Sheet!A1:H1');
this.cellFormat({ textAlign: 'center' }, 'Inserted Sheet!D2:H15');
}
</script>
public IActionResult Index()
{
List<object> data = new List<object>()
{
new { CustomerName= "Romona Heaslip", Model= "Taurus", Color= "Aquamarine", PaymentMode= "Debit Card", DeliveryDate= "07/11/2015", Amount= "8529.22" },
new { CustomerName= "Clare Batterton", Model= "Sparrow", Color= "Pink", PaymentMode= "Cash On Delivery", DeliveryDate= "7/13/2016", Amount= "17866.19" },
new { CustomerName= "Eamon Traise", Model= "Grand Cherokee", Color= "Blue", PaymentMode= "Net Banking", DeliveryDate= "09/04/2015", Amount= "13853.09" },
new { CustomerName= "Julius Gorner", Model= "GTO", Color= "Aquamarine", PaymentMode= "Credit Card", DeliveryDate= "12/15/2017", Amount= "2338.74" },
new { CustomerName= "Jenna Schoolfield", Model= "LX", Color= "Yellow", PaymentMode= "Credit Card", DeliveryDate= "10/08/2014", Amount= "9578.45" },
new { CustomerName= "Marylynne Harring", Model= "Catera", Color= "Green", PaymentMode= "Cash On Delivery", DeliveryDate= "7/01/2017", Amount= "19141.62" },
new { CustomerName= "Vilhelmina Leipelt", Model= "7 Series", Color= "Goldenrod", PaymentMode= "Credit Card", DeliveryDate= "12/20/2015", Amount= "6543.30" },
new { CustomerName= "Barby Heisler", Model= "Corvette", Color= "Red", PaymentMode= "Credit Card", DeliveryDate= "11/24/2014", Amount= "13035.06" },
new { CustomerName= "Karyn Boik", Model= "Regal", Color= "Indigo", PaymentMode= "Debit Card", DeliveryDate= "05/12/2014", Amount= "18488.80" },
new { CustomerName= "Jeanette Pamplin", Model= "S4", Color= "Fuscia", PaymentMode= "Net Banking", DeliveryDate= "12/30/2014", Amount= "12317.04" },
new { CustomerName= "Cristi Espinos", Model= "TL", Color= "Aquamarine", PaymentMode= "Credit Card", DeliveryDate= "12/18/2013", Amount= "6230.13" },
new { CustomerName= "Issy Humm", Model= "Club Wagon", Color= "Pink", PaymentMode= "Cash On Delivery", DeliveryDate= "02/02/2015", Amount= "9709.49" },
new { CustomerName= "Tuesday Fautly", Model= "V8 Vantage", Color= "Crimson", PaymentMode= "Debit Card", DeliveryDate= "11/19/2014", Amount= "9766.10" },
new { CustomerName= "Rosemaria Thomann", Model= "Caravan", Color= "Violet", PaymentMode= "Net Banking", DeliveryDate= "02/08/2014", Amount= "7685.49" },
};
ViewBag.DefaultData = data;
return View();
}
The Spreadsheet has support for removing an existing worksheet. You can dynamically delete the existing sheet by the following way,
Delete
option from context menu.You can dynamically rename an existing worksheet in the following way,
Rename
option from the context menu.By default, the row and column headers are visible in worksheets. You can dynamically show or hide worksheet headers by using one of the following ways,
View
tab, and then select Hide Headers
option to hide both the row and column headers.showHeaders
property in sheets
as true
or false
to show or hide the headers at initial load. By default, the showHeaders
property is enabled in each worksheet.Gridlines act as a border like appearance of cells. They are used to distinguish cells on the worksheet. You can dynamically show or hide gridlines by using one of the following ways,
View
tab, and then select Hide Gridlines
option to hide the gridlines in worksheet.showGridLines
property in sheets
as true
or false
to show or hide the gridlines at initial load. By default, the showGridLines
property is enabled in each worksheet.The following code example shows the headers and gridlines operation in spreadsheet.
@Html.EJS().Spreadsheet("spreadsheet").ShowFormulaBar(false).Created("created").Sheets(sheet =>
{
sheet.Name("Shipment Details").ShowGridLines(false).ShowHeaders(false).Range(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
}).Columns(column =>
{
column.Width(130).Add();
column.Width(220).Add();
column.Width(90).Add();
column.Width(140).Add();
column.Width(90).Add();
column.Width(100).Add();
column.Width(100).Add();
}).Add();
}).Render()
<script>
function created() {
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
this.cellFormat({ textAlign: 'center' }, 'D2:H15');
// The gridlines have been removed to set border for the range of cells
this.setBorder({ border: '1px solid #e0e0e0' }, 'A1:H15');
}
</script>
public IActionResult Index()
{
List<object> data = new List<object>()
{
new { CustomerName= "Romona Heaslip", Model= "Taurus", Color= "Aquamarine", PaymentMode= "Debit Card", DeliveryDate= "07/11/2015", Amount= "8529.22" },
new { CustomerName= "Clare Batterton", Model= "Sparrow", Color= "Pink", PaymentMode= "Cash On Delivery", DeliveryDate= "7/13/2016", Amount= "17866.19" },
new { CustomerName= "Eamon Traise", Model= "Grand Cherokee", Color= "Blue", PaymentMode= "Net Banking", DeliveryDate= "09/04/2015", Amount= "13853.09" },
new { CustomerName= "Julius Gorner", Model= "GTO", Color= "Aquamarine", PaymentMode= "Credit Card", DeliveryDate= "12/15/2017", Amount= "2338.74" },
new { CustomerName= "Jenna Schoolfield", Model= "LX", Color= "Yellow", PaymentMode= "Credit Card", DeliveryDate= "10/08/2014", Amount= "9578.45" },
new { CustomerName= "Marylynne Harring", Model= "Catera", Color= "Green", PaymentMode= "Cash On Delivery", DeliveryDate= "7/01/2017", Amount= "19141.62" },
new { CustomerName= "Vilhelmina Leipelt", Model= "7 Series", Color= "Goldenrod", PaymentMode= "Credit Card", DeliveryDate= "12/20/2015", Amount= "6543.30" },
new { CustomerName= "Barby Heisler", Model= "Corvette", Color= "Red", PaymentMode= "Credit Card", DeliveryDate= "11/24/2014", Amount= "13035.06" },
new { CustomerName= "Karyn Boik", Model= "Regal", Color= "Indigo", PaymentMode= "Debit Card", DeliveryDate= "05/12/2014", Amount= "18488.80" },
new { CustomerName= "Jeanette Pamplin", Model= "S4", Color= "Fuscia", PaymentMode= "Net Banking", DeliveryDate= "12/30/2014", Amount= "12317.04" },
new { CustomerName= "Cristi Espinos", Model= "TL", Color= "Aquamarine", PaymentMode= "Credit Card", DeliveryDate= "12/18/2013", Amount= "6230.13" },
new { CustomerName= "Issy Humm", Model= "Club Wagon", Color= "Pink", PaymentMode= "Cash On Delivery", DeliveryDate= "02/02/2015", Amount= "9709.49" },
new { CustomerName= "Tuesday Fautly", Model= "V8 Vantage", Color= "Crimson", PaymentMode= "Debit Card", DeliveryDate= "11/19/2014", Amount= "9766.10" },
new { CustomerName= "Rosemaria Thomann", Model= "Caravan", Color= "Violet", PaymentMode= "Net Banking", DeliveryDate= "02/08/2014", Amount= "7685.49" },
};
ViewBag.DefaultData = data;
return View();
}
Hiding a worksheet can help prevent unauthorized or accidental changes to your file.
There are three visibility state as like Microsoft Excel,
State | Description |
---|---|
Visible |
You can see the worksheet once the component is loaded. |
Hidden |
This worksheet is not visible, but you can unhide by selecting the sheet from List All Sheets |
dropdown menu. | |
VeryHidden |
This worksheet is not visible and cannot be unhidden. Changing the |
state property to Visible is the only way to view this sheet. |
The following code example shows the three types of sheet visibility state.
@Html.EJS().Spreadsheet("spreadsheet").ShowFormulaBar(false).Created("created").Sheets(sheet =>
{
sheet.Name("Visible Sheet").State(SheetState.Visible).Range(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
}).Columns(column =>
{
column.Width(130).Add();
column.Width(220).Add();
column.Width(90).Add();
column.Width(140).Add();
column.Width(90).Add();
column.Width(100).Add();
column.Width(100).Add();
}).Add();
sheet.Name("Very Hidden Sheet").State(SheetState.VeryHidden).Range(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
}).Columns(column =>
{
column.Width(130).Add();
column.Width(220).Add();
column.Width(90).Add();
column.Width(140).Add();
column.Width(90).Add();
column.Width(100).Add();
column.Width(100).Add();
}).Add();
sheet.Name("Hidden Sheet").State(SheetState.Hidden).Range(ranges =>
{
ranges.DataSource((IEnumerable<object>)ViewBag.DefaultData).Add();
}).Columns(column =>
{
column.Width(130).Add();
column.Width(220).Add();
column.Width(90).Add();
column.Width(140).Add();
column.Width(90).Add();
column.Width(100).Add();
column.Width(100).Add();
}).Add();
}).Render()
<script>
function created() {
// Applies style formatting to active visible sheet
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'A1:H1');
this.cellFormat({ textAlign: 'center' }, 'D2:H11');
// Applies style formatting to active hidden sheet
this.cellFormat({ fontWeight: 'bold', textAlign: 'center' }, 'Hidden Sheet!A1:H1');
this.cellFormat({ textAlign: 'center' }, 'Hidden Sheet!D2:H15');
}
</script>
public IActionResult Index()
{
List<object> data = new List<object>()
{
new { CustomerName= "Romona Heaslip", Model= "Taurus", Color= "Aquamarine", PaymentMode= "Debit Card", DeliveryDate= "07/11/2015", Amount= "8529.22" },
new { CustomerName= "Clare Batterton", Model= "Sparrow", Color= "Pink", PaymentMode= "Cash On Delivery", DeliveryDate= "7/13/2016", Amount= "17866.19" },
new { CustomerName= "Eamon Traise", Model= "Grand Cherokee", Color= "Blue", PaymentMode= "Net Banking", DeliveryDate= "09/04/2015", Amount= "13853.09" },
new { CustomerName= "Julius Gorner", Model= "GTO", Color= "Aquamarine", PaymentMode= "Credit Card", DeliveryDate= "12/15/2017", Amount= "2338.74" },
new { CustomerName= "Jenna Schoolfield", Model= "LX", Color= "Yellow", PaymentMode= "Credit Card", DeliveryDate= "10/08/2014", Amount= "9578.45" },
new { CustomerName= "Marylynne Harring", Model= "Catera", Color= "Green", PaymentMode= "Cash On Delivery", DeliveryDate= "7/01/2017", Amount= "19141.62" },
new { CustomerName= "Vilhelmina Leipelt", Model= "7 Series", Color= "Goldenrod", PaymentMode= "Credit Card", DeliveryDate= "12/20/2015", Amount= "6543.30" },
new { CustomerName= "Barby Heisler", Model= "Corvette", Color= "Red", PaymentMode= "Credit Card", DeliveryDate= "11/24/2014", Amount= "13035.06" },
new { CustomerName= "Karyn Boik", Model= "Regal", Color= "Indigo", PaymentMode= "Debit Card", DeliveryDate= "05/12/2014", Amount= "18488.80" },
new { CustomerName= "Jeanette Pamplin", Model= "S4", Color= "Fuscia", PaymentMode= "Net Banking", DeliveryDate= "12/30/2014", Amount= "12317.04" },
new { CustomerName= "Cristi Espinos", Model= "TL", Color= "Aquamarine", PaymentMode= "Credit Card", DeliveryDate= "12/18/2013", Amount= "6230.13" },
new { CustomerName= "Issy Humm", Model= "Club Wagon", Color= "Pink", PaymentMode= "Cash On Delivery", DeliveryDate= "02/02/2015", Amount= "9709.49" },
new { CustomerName= "Tuesday Fautly", Model= "V8 Vantage", Color= "Crimson", PaymentMode= "Debit Card", DeliveryDate= "11/19/2014", Amount= "9766.10" },
new { CustomerName= "Rosemaria Thomann", Model= "Caravan", Color= "Violet", PaymentMode= "Net Banking", DeliveryDate= "02/08/2014", Amount= "7685.49" },
};
ViewBag.DefaultData = data;
return View();
}