Category / Section
How to update dynamic datasource in Spreadsheet normal scroll mode
4 mins read
Description
This knowledge base explains how to update dynamic datasource in Spreadsheet normal scroll mode.
Solution
This requirement can be achieved by using updateRange() method in Button click event.
[HTML]
<button id="change" onclick="onChangeDatasource(this)">Change datasource</button> <div id="Spreadsheet"></div>
[JS]
window.newDatasource = [
{ "OrderID": 10001, "CustomerID": "VINET", "Freight": 12.256, "Load": 123456, "shipdate": "4/12/2001", "Time": "11:33:56" },
{ "OrderID": 10002, "CustomerID": "TOMSP", "Freight": 23.3, "Load": 1234, "shipdate": "5/12/2010", "Time": "12:12:12" },
{ "OrderID": 10003, "CustomerID": "HANAR", "Freight": 12.256, "Load": 123456, "shipdate": "4/12/2001", "Time": "11:33:56" },
{ "OrderID": 10004, "CustomerID": "VICTE", "Freight": 23.3, "Load": 1234, "shipdate": "5/12/2010", "Time": "12:12:12" },
{ "OrderID": 10005, "CustomerID": "SUPRD", "Freight": 12.256, "Load": 123456, "shipdate": "4/12/2001", "Time": "11:33:56" },
{ "OrderID": 10006, "CustomerID": "CHOPS", "Freight": 23.3, "Load": 1234, "shipdate": "5/12/2010", "Time": "12:12:12" },
];
window.datasource = [{ "ProductID": 1, "ProductName": "Chai", "CategoryID": 1, "QuantityPerUnit": "10 boxes x 20 bags", "UnitPrice": "18.0000", "UnitsInStock": 39 },
{ "ProductID": 2, "ProductName": "Chang", "CategoryID": 1, "QuantityPerUnit": "24 - 12 oz bottles", "UnitPrice": "19.0000", "UnitsInStock": 17 }, { "ProductID": 3, "ProductName": "Aniseed Syrup", "CategoryID": 2, "QuantityPerUnit": "12 - 550 ml bottles", "UnitPrice": "10.0000", "UnitsInStock": 13 },
{ "ProductID": 4, "ProductName": "Chef Anton's Cajun Seasoning", "CategoryID": 2, "QuantityPerUnit": "48 - 6 oz jars", "UnitPrice": "22.0000", "UnitsInStock": 53 },
{ "ProductID": 5, "ProductName": "Chef Anton's Gumbo Mix", "CategoryID": 2, "QuantityPerUnit": "36 boxes", "UnitPrice": "21.3500", "UnitsInStock": 0 },
{ "ProductID": 6, "ProductName": "Grandma's Boysenberry Spread", "CategoryID": 2, "QuantityPerUnit": "12 - 8 oz jars", "UnitPrice": "25.0000", "UnitsInStock": 120 },
{ "ProductID": 7, "ProductName": "Uncle Bob's Organic Dried Pears", "CategoryID": 7, "QuantityPerUnit": "12 - 1 lb pkgs.", "UnitPrice": "30.0000", "UnitsInStock": 15 },
{ "ProductID": 8, "ProductName": "Northwoods Cranberry Sauce", "CategoryID": 2, "QuantityPerUnit": "12 - 12 oz jars", "UnitPrice": "40.0000", "UnitsInStock": 6 },
{ "ProductID": 9, "ProductName": "Mishi Kobe Niku", "CategoryID": 6, "QuantityPerUnit": "18 - 500 g pkgs.", "UnitPrice": "97.0000", "UnitsInStock": 29 },
{ "ProductID": 10, "ProductName": "Ikura", "CategoryID": 8, "QuantityPerUnit": "12 - 200 ml jars", "UnitPrice": "31.0000", "UnitsInStock": 31 }
];
$(function () {
$("#Spreadsheet").ejSpreadsheet({
scrollSettings: {
scrollMode: ej.Spreadsheet.scrollMode.Normal
},
sheets: [{
dataSource: window.datasource
}]
});
});
function onChangeDatasource(args) {
var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), sheetIdx = ssObj.getActiveSheetIndex(), sheet, newDatasource;
//Get Datasource.
newDatasource = window. newDatasource;
//reset workbook.
ssObj.blankWorkbook();
//Update sheet and refresh it.
sheet = ssObj.getSheet();
ssObj.refreshContent(sheetIdx);
//Update new datasource using range settings.
ssObj.updateRange(sheetIdx, { dataSource: newDatasource, startCell: "A1", showHeader: true });
}
[RAZOR]
<button id="change" onclick="onChangeDatasource()">To Change the DataSource</button>
@(Html.EJ().Spreadsheet<object>("Spreadsheet")
.ScrollSettings(scroll =>
{
scroll.ScrollMode(SpreadsheetScrollMode.Normal);
})
.Sheets(sheet =>
{
sheet.Datasource((IEnumerable<object>)ViewBag.Datasource). Add();
})
)
<script type="text/javascript">
function onChangeDatasource(args) {
var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), sheetIdx = ssObj.getActiveSheetIndex(), sheet, newDatasource;
//Get Datasource.
$.ajax({
type: "POST",
url: "Home/GetItemData",
success: function (data) {
newDatasource = JSON.parse(data);
//reset workbook.
ssObj.blankWorkbook();
//Update sheet and refresh it.
sheet = ssObj.getSheet();
ssObj.refreshContent(sheetIdx);
//Update new datasource using range settings.
ssObj.updateRange(sheetIdx, { dataSource: newDatasource, startCell: "A1", showHeader: true });
}
})
}
[CONTROLLER]
[AcceptVerbs(HttpVerbs.Post)]
public object GetItemData()
{
List<ItemDetail> lItems = new List<ItemDetail>();
lItems.Add(new ItemDetail(){ OrderID= 10001, CustomerID= "VINET", Freight= "12.256", Load= 123456, shipdate= "4/12/2001", Time= "11:33:56" });
lItems.Add(new ItemDetail(){ OrderID= 10002, CustomerID= "TOMSP", Freight= "23.3", Load= 1234, shipdate= "5/12/2010", Time= "12:12:12" });
lItems.Add(new ItemDetail(){ OrderID= 10003, CustomerID= "HANAR", Freight= "12.256", Load= 123456, shipdate= "4/12/2001", Time= "11:33:56" });
lItems.Add(new ItemDetail(){ OrderID= 10004, CustomerID= "VICTE", Freight= "23.3", Load= 1234, shipdate= "5/12/2010", Time= "12:12:12" });
lItems.Add(new ItemDetail(){ OrderID= 10005, CustomerID= "SUPRD", Freight= "12.256", Load= 123456, shipdate= "4/12/2001", Time= "11:33:56" });
lItems.Add(new ItemDetail() { OrderID = 10006, CustomerID = "CHOPS", Freight = "23.3", Load = 1234, shipdate = "5/12/2010", Time = "12:12:12" });
return JsonConvert.SerializeObject(lItems);
}
public class ItemDetail
{
public string CustomerID { get; set; }
public int OrderID { get; set; }
public int Load { get; set; }
public string Freight { get; set; }
public string shipdate { get; set; }
public string Time { get; set; }
}
[ASPX]
<button id="change" onclick="onChangeDatasource()">To Change the DataSource</button>
<ej:Spreadsheet ID="Spreadsheet" ShowRibbon="false" runat="server">
<ScrollSettings ScrollMode="Normal" />
<Sheets>
<ej:Sheet ></ej:Sheet><!-- you can define datasource at server side -->
</Sheets>
</ej:Spreadsheet>
<script type = "text/javascript">
function onChangeDatasource(args) {
var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), sheetIdx = ssObj.getActiveSheetIndex(), sheet, newDatasource;
$.ajax({
type: "POST",
url: "Default.aspx/GetItemData",
success: function (data) {
newDatasource = JSON.parse(data);
//reset workbook.
ssObj.blankWorkbook();
//Update sheet and refresh it.
sheet = ssObj.getSheet();
ssObj.refreshContent(sheetIdx);
//Update new datasource using range settings.
ssObj.updateRange(sheetIdx, { dataSource: newDatasource, startCell: "A1", showHeader: true });
}
})
}
</script>
[CS]
[System.Web.Services.WebMethod]
public static object GetItemData()
{
List<ItemDetail> lItems = new List<ItemDetail>();
lItems.Add(new ItemDetail() { OrderID = 10001, CustomerID = "VINET", Freight = "12.256", Load = 123456, shipdate = "4/12/2001", Time = "11:33:56" });
lItems.Add(new ItemDetail() { OrderID = 10002, CustomerID = "TOMSP", Freight = "23.3", Load = 1234, shipdate = "5/12/2010", Time = "12:12:12" });
lItems.Add(new ItemDetail() { OrderID = 10003, CustomerID = "HANAR", Freight = "12.256", Load = 123456, shipdate = "4/12/2001", Time = "11:33:56" });
lItems.Add(new ItemDetail() { OrderID = 10004, CustomerID = "VICTE", Freight = "23.3", Load = 1234, shipdate = "5/12/2010", Time = "12:12:12" });
lItems.Add(new ItemDetail() { OrderID = 10005, CustomerID = "SUPRD", Freight = "12.256", Load = 123456, shipdate = "4/12/2001", Time = "11:33:56" });
lItems.Add(new ItemDetail() { OrderID = 10006, CustomerID = "CHOPS", Freight = "23.3", Load = 1234, shipdate = "5/12/2010", Time = "12:12:12" });
return JsonConvert.SerializeObject(lItems);
}
public class ItemDetail
{
public string CustomerID { get; set; }
public int OrderID { get; set; }
public int Load { get; set; }
public string Freight { get; set; }
public string shipdate { get; set; }
public string Time { get; set; }
}
