Search results

Data Binding in ASP.NET MVC Spreadsheet control

The Spreadsheet uses DataManager, which supports both RESTful JSON data services and local JavaScript object array binding to a range. The dataSource property can be assigned either with the instance of DataManager or JavaScript object array collection.

To bind data to a cell, use cell data binding support.

Local data

To bind local data to the Spreadsheet, you can assign a JavaScript object array to the dataSource property.

Refer to the following code example for local data binding.

razor
localDataController.cs
@Html.EJS().Spreadsheet("spreadsheet").Sheets((sheet) =>
{
    sheet.Ranges((ranges) =>
    {
        ranges.DataSource(@ViewBag.defaultData).Add();
    }).Add();
}).Render()
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 local data source can also be provided as an instance of the DataManager. By default, DataManager uses JsonAdaptor for local data-binding.

Remote data

To bind remote data to the Spreadsheet control, assign service data as an instance of DataManager to the dataSource property. To interact with remote data source, provide the service endpoint url.

Refer to the following code example for remote data binding.

razor
remoteDataController.cs
@Html.EJS().Spreadsheet("spreadsheet").Sheets(sheet =>
  {
      sheet.Name("Shipment Details").Ranges(ranges =>
      {
          ranges.ShowFieldAsHeader(false).StartCell("A2").Query("new ej.data.Query().select(['OrderID', 'CustomerID', 'ShipName', 'ShipCity', 'ShipCountry', 'Freight']).take(200)").DataSource(dataManger =>
   {
       dataManger.Url("https://js.syncfusion.com/demos/ejServices//wcf/Northwind.svc/Orders").CrossDomain(true);
   }).Add();
      }).Rows(row =>
      {
          row.Cells(cell =>
          {
              cell.Value("Order ID").Add();
              cell.Value("Customer Name").Add();
              cell.Value("Freight").Add();
              cell.Value("Ship Name").Add();
              cell.Value("Ship City").Add();
              cell.Value("Ship Country").Add();
          }).Add();
      }).Columns(column =>
      {
          column.Width(100).Add();
          column.Width(130).Add();
          column.Width(100).Add();
          column.Width(220).Add();
          column.Width(150).Add();
          column.Width(180).Add();
      }).Add();
  }).Render()
public IActionResult Index()
{
    return View();
}

By default, DataManager uses ODataAdaptor for remote data-binding.

Cell data binding

The Spreadsheet control can bind the data to individual cell in a sheet . To achive this you can use the value property.

Refer to the following code example for cell data binding.

razor
cellDataController.cs
@Html.EJS().Spreadsheet("spreadsheet").ShowRibbon(false).ShowFormulaBar(false).Sheets(sheet =>
   {
       sheet.Name("Monthly Budget").SelectedRange("D13").Rows(row =>
       {
           row.Cells(cell =>
           {
               cell.Value("Category").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
               cell.Value("Planned cost").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
               cell.Value("Actual cost").Style(new SpreadsheetCellStyle() { FontWeight = FontWeight.Bold, TextAlign = TextAlign.Center }).Add();
           }).Add();
           row.Cells(cell =>
           {
               cell.Value("Food").Add();
               cell.Value("$7000").Add();
               cell.Value("$8120").Add();
           }).Add();
           row.Cells(cell =>
           {
               cell.Value("Loan").Add();
               cell.Value("$1500").Add();
               cell.Value("$1500").Add();
           }).Add();
           row.Cells(cell =>
           {
               cell.Value("Medical").Add();
               cell.Value("$300").Add();
               cell.Value("$0").Add();
           }).Add();
           row.Cells(cell =>
           {
               cell.Value("Clothing").Add();
               cell.Value("$400").Add();
               cell.Value("$140").Add();
           }).Add();
           row.Cells(cell =>
           {
               cell.Value("Education").Add();
               cell.Value("$900").Add();
               cell.Value("$750").Add();
           }).Add();
           row.Cells(cell =>
           {
               cell.Value("Insurance").Add();
               cell.Value("$30").Add();
               cell.Value("$30").Add();
           }).Add();
           row.Cells(cell =>
           {
               cell.Value("Utilities").Add();
               cell.Value("$130").Add();
               cell.Value("$160").Add();
           }).Add();
       }).Columns(column =>
       {
           column.Width(110).Add();
           column.Width(115).Add();
           column.Width(110).Add();
           column.Width(100).Add();
       }).Add();
   }).Render()
public IActionResult Index()
{
    return View();
}

The cell data binding also supports formula, style, number format, and more.

See Also