Getting started with ASP.NET Webforms SpreadSheet
2 Sep 202112 minutes to read
This section explains you the steps required to populate the Spreadsheet with data, format, and export it as excel file. This section covers only the minimal features that you need to know to get started with the Spreadsheet.
Create your first SpreadSheet ASP.NET Web Forms Application
-
Create a Syncfusion ASP.NET Web Forms application.
-
Drag and drop the SpreadSheet control in the Index.aspx page from the toolbox.
The Spreadsheet is rendered based on default Width and Height. You can also customize the Spreadsheet dimension by setting the Width and Height property in ScrollSettings.
<ej:Spreadsheet ID="Spreadsheet" runat="server">
<ScrollSettings Width="100%" Height="100%" IsResponsive="true" />
</ej:Spreadsheet>Now, the Spreadsheet is rendered with default row and column count.
Populate Spreadsheet with data
Now, this section explains how to populate JSON data to the Spreadsheet. You can set DataSource property in Sheet settings to populate JSON data in Spreadsheet.
<ej:Spreadsheet ID="Spreadsheet" runat="server">
<ClientSideEvents LoadComplete="loadComplete" OpenFailure="openFailure" />
</ej:Spreadsheet>
<script type="text/javascript">
function loadComplete(args) {
var excelFormat = this.XLFormat;
if (!this.isImport) {
this.setWidthToColumns([142, 132, 110, 105, 102, 112, 122, 122, 102]);
excelFormat.format({ "style": { "font-weight": "bold" } }, "A1:H1");
excelFormat.format({ "type": "currency" }, "E2:H11");
this.XLRibbon.updateRibbonIcons();
}
}
function openFailure(args) {
this.alert(args.statusText);
}
</script>protected void Page_Load(object sender, EventArgs e)
{
BindDataSource();
}
private void BindDataSource()
{
var dataSource = GetAllItemDetails().ToList();
this.Spreadsheet.Sheets.Add(new Syncfusion.JavaScript.Models.Sheet()
{
Datasource = dataSource
});
}
private List<ItemDetail> GetAllItemDetails()
{
List<ItemDetail> Items = new List<ItemDetail>();
Items.Add(new ItemDetail() { ItemName = "Casual Shoes", Date = "02/14/2014", Time = "11:34:32 AM", Quantity = 10, Price = 20, Amount = 200, Discount = 1, Profit = 10 });
Items.Add(new ItemDetail() { ItemName = "Sports Shoes", Date = "06/11/2014", Time = "05:56:32 AM", Quantity = 20, Price = 30, Amount = 600, Discount = 5, Profit = 50 });
Items.Add(new ItemDetail() { ItemName = "Formal Shoes", Date = "07/27/2014", Time = "03:32:44 AM", Quantity = 20, Price = 15, Amount = 300, Discount = 7, Profit = 27 });
Items.Add(new ItemDetail() { ItemName = "Sandals & Floaters", Date = "11/21/2014", Time = "06:23:54 AM", Quantity = 15, Price = 20, Amount = 300, Discount = 11, Profit = 67 });
Items.Add(new ItemDetail() { ItemName = "Flip- Flops & Slippers", Date = "06/23/2014", Time = "12:43:59 AM", Quantity = 30, Price = 10, Amount = 300, Discount = 10, Profit = 70 });
Items.Add(new ItemDetail() { ItemName = "Sneakers", Date = "07/22/2014", Time = "10:55:53 AM", Quantity = 40, Price = 20, Amount = 800, Discount = 13, Profit = 66 });
Items.Add(new ItemDetail() { ItemName = "Running Shoes", Date = "02/04/2014", Time = "03:44:34 AM", Quantity = 20, Price = 10, Amount = 200, Discount = 3, Profit = 14 });
Items.Add(new ItemDetail() { ItemName = "Loafers", Date = "11/30/2014", Time = "03:12:52 AM", Quantity = 31, Price = 10, Amount = 310, Discount = 6, Profit = 29 });
Items.Add(new ItemDetail() { ItemName = "Cricket Shoes", Date = "07/09/2014", Time = "11:32:14 AM", Quantity = 41, Price = 30, Amount = 1210, Discount = 12, Profit = 166 });
Items.Add(new ItemDetail() { ItemName = "T-Shirts", Date = "10/31/2014", Time = "12:01:44 AM", Quantity = 50, Price = 10, Amount = 500, Discount = 9, Profit = 55 });
return Items;
}
public class ItemDetail
{
public string ItemName { get; set; }
public string Date { get; set; }
public string Time { get; set; }
public int Quantity { get; set; }
public int Price { get; set; }
public int Amount { get; set; }
public int Discount { get; set; }
public int Profit { get; set; }
}
NOTE
For more details about
data bindingrefer followinglink
Apply Conditional Formatting
Conditional formatting helps you to apply formats to a cell or range with certain color based on the cells values. You can use AllowConditionalFormats property to enable/disable Conditional formats.
To apply conditional formats for a range use setCFRule method. The following code example illustrates this,
<ej:Spreadsheet ID="Spreadsheet" runat="server">
<ClientSideEvents LoadComplete="loadComplete" OpenFailure="openFailure" />
</ej:Spreadsheet>
<script type="text/javascript">
function loadComplete() {
this.XLCFormat.setCFRule({ "action": "greaterthan", "inputs": ["10"], "color": "redft", "range": "D2:D8" });
}
</script>
NOTE
For more details about
Conditional Formattingrefer followinglink
Export Spreadsheet as Excel File
The Spreadsheet can save its data, style, format into an excel file. To enable save option in Spreadsheet set AllowExporting option in ExportSettings as true. Since Spreadsheet uses server side event OnServerExcelExporting to save documents. The following code example illustrates this,
<ej:Spreadsheet ID="Spreadsheet" OnServerExcelExporting="Spreadsheet_ServerExcelExporting" runat="server">
<ClientSideEvents LoadComplete="loadComplete" OpenFailure="openFailure" />
</ej:Spreadsheet>protected void Spreadsheet_ServerExcelExporting(object sender, Syncfusion.JavaScript.Web.SpreadsheetEventArgs e)
{
var args = e.Arguments;
string password = args["password"].ToString();
string sheetModel = args["sheetModel"].ToString();
string sheetData = args["sheetData"].ToString();
if (!string.IsNullOrEmpty(password))
Spreadsheet.Save(sheetModel, sheetData, "sample", ExportFormat.XLSX, ExcelVersion.Excel2013, password);
else
Spreadsheet.Save(sheetModel, sheetData, "sample", ExportFormat.XLSX, ExcelVersion.Excel2013);
}Use shortcut Ctrl + S to save Spreadsheet as excel file.
NOTE