We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
Unfortunately, activation email could not send to your email. Please try again.
Essential JS2

Syncfusion jQuery based widgets are no longer in active development. Switch to our pure JavaScript based next generation Essential JS 2 library.

Syncfusion Feedback

How to load and save a Sql table in Spreadsheet

Platform: jQuery |
Control: ejSpreadSheet |
Published Date: April 2, 2018 |
Last Revised Date: May 22, 2019

Description

This knowledge base explains how to load and save changes to a Sql table in Spreadsheet.

Solution

JavaScript

 

HTML

<input type="button" value="Save Changes to DB" id="saveChangesToDB" />
<div id="FlatSpreadsheet"></div>

 

JS

var dataSource;
$(function () {
    $.ajaxSetup({ async: false });            
    $.getJSON("api/Spreadsheet/GetDataFromDB", {}, function (data) {
        dataSource = data;
    });
    $.ajaxSetup({ async: true });
    $("#FlatSpreadsheet").ejSpreadsheet({
        columnWidth: 110,
        sheets: [
            {
                dataSource: dataSource,
                primaryKey: "OrderID"
            }
        ],
        loadComplete: "onLoadComplete"
    });
    $("#saveChangesToDB").on("click", function () {
        var ssObj = $("#FlatSpreadsheet").data("ejSpreadsheet"), key, rowIdx, sheetIdx = ssObj.getActiveSheetIndex(), editedChanges = ssObj.XLEdit.saveEditingValue().EditedData,
            dataSettings = ssObj.getDataSettings(sheetIdx)[0], isShowHeader = dataSettings.showHeader, dataSource = dataSettings.dataSource, changedDataSource = [], pushedRowColl = [];
        if (!editedChanges.length) {
            ssObj.alert("Please edit any cells and try again!");
            return;
        }
        ssObj.showWaitingPopUp();
        for (key in editedChanges) {
            rowIdx = editedChanges[key].CellIndex.RowIndex;
            if (isShowHeader)
                rowIdx--;
            if (pushedRowColl.indexOf(rowIdx) < 0) {
                changedDataSource.push(dataSource[rowIdx]);
                pushedRowColl.push(rowIdx);
            }
        }
        $.ajax({
            type: "POST",
            url: "api/Spreadsheet/SaveChangesToDatabase",
            data: { editedValues: JSON.stringify(changedDataSource) },
            success: function (response) {
                ssObj.alert(response);
                ssObj.hideWaitingPopUp();
            },
            error: function (err) {
                ssObj.alert(err.statusText);
                ssObj.hideWaitingPopUp();
            }
        });
    });
});
function onLoadComplete(args) {
    if (!this.isImport) {
        this.XLEdit.saveEditingValue(); //Get or reset the saved changes collection after data loaded
    }
}

 

WEBAPI

private static string connetionString = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString;
[AcceptVerbs("Get")]
[OperationContract]
[WebGet(BodyStyle = WebMessageBodyStyle.Bare)]
public HttpResponseMessage GetDataFromDB()
{
    List<Order> orders = new List<Order>();
    SqlConnection sqlCon = new SqlConnection(connetionString);
    SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table]", sqlCon);
    sqlCon.Open();
    SqlDataReader sqlDR = sqlComm.ExecuteReader();
    while (sqlDR.Read())
    {
        Order order = new Order();
        order.OrderID = (int)sqlDR.GetValue(0);
        order.ItemName = sqlDR.GetValue(1).ToString();
        order.Quantity = sqlDR.GetValue(2).ToString();
        order.Price = sqlDR.GetValue(3).ToString();
        order.Amount = sqlDR.GetValue(4).ToString();
        orders.Add(order);
    }
    sqlCon.Close();
 
    JavaScriptSerializer serializer = new JavaScriptSerializer();
    string str = serializer.Serialize(orders);
    return new HttpResponseMessage() { Content = new StringContent(str, Encoding.UTF8, "text/plain") };
}
 
[AcceptVerbs("Post")]
[OperationContract]
[WebGet(BodyStyle = WebMessageBodyStyle.Bare)]
public HttpResponseMessage SaveChangesToDatabase()
{
    string editedValues = HttpContext.Current.Request.Params["editedValues"];
    JavaScriptSerializer serialize = new JavaScriptSerializer();
    List<Order> editedData = serialize.Deserialize<List<Order>>(editedValues);
 
    SqlConnection sqlCon = new SqlConnection(connetionString);
    sqlCon.Open();
    for (int i = 0; i < editedData.Count; i++)
    {
        var order = editedData[i];
        SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [OrderID] = '" + order.OrderID + "'", sqlCon);
        var hasFileInDB = selectComm.ExecuteScalar();
        if (hasFileInDB == null)
        {
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon);
            sqlComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", order.Price);
            sqlComm.Parameters.AddWithValue("@Amount", order.Amount);
            sqlComm.ExecuteNonQuery();
        }
        else
        {
            SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
            updateComm.CommandTimeout = 0;
            updateComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            updateComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            updateComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            updateComm.Parameters.AddWithValue("@Price", order.Price);
            updateComm.Parameters.AddWithValue("@Amount", order.Amount);
            updateComm.ExecuteNonQuery();
        }
    }
    sqlCon.Close();
    return new HttpResponseMessage() { Content = new StringContent("Successfully updated", Encoding.UTF8, "text/plain") };
}

 

MVC

 

CSHTML

<input type="button" value="Save Changes to DB" id="saveChangesToDB" />
@(Html.EJ().Spreadsheet<object>("FlatSpreadsheet")
    .ColumnWidth(110)
    .Sheets(sheet =>
    {
        sheet.Datasource(@ViewBag.Datasource).PrimaryKey("OrderID").Add();
    })
    .ClientSideEvents(eve => eve.LoadComplete("onLoadComplete"))
)
<script type="text/javascript">    
    $(function () {
        $("#saveChangesToDB").on("click", function () {
            var ssObj = $("#FlatSpreadsheet").data("ejSpreadsheet"), key, rowIdx, sheetIdx = ssObj.getActiveSheetIndex(), editedChanges = ssObj.XLEdit.saveEditingValue().EditedData,
                dataSettings = ssObj.getDataSettings(sheetIdx)[0], isShowHeader = dataSettings.showHeader, dataSource = dataSettings.dataSource, changedDataSource = [], pushedRowColl = [];
            if (!editedChanges.length) {
                ssObj.alert("Please edit any cells and try again!");
                return;
            }
            ssObj.showWaitingPopUp();
            for (key in editedChanges) {
                rowIdx = editedChanges[key].CellIndex.RowIndex;
                if (isShowHeader)
                    rowIdx--;
                if (pushedRowColl.indexOf(rowIdx) < 0) {
                    changedDataSource.push(dataSource[rowIdx]);
                    pushedRowColl.push(rowIdx);
                }
            }
            $.ajax({
                type: "POST",
                url: "Home/SaveChangesToDatabase",
                data: { editedValues: JSON.stringify(changedDataSource) },
                success: function (response) {
                    ssObj.alert(response);
                    ssObj.hideWaitingPopUp();
                },
                error: function (err) {
                    ssObj.alert(err.statusText);
                    ssObj.hideWaitingPopUp();
                }
            });
        });
    });
    function onLoadComplete(args) {
        if (!this.isImport) {
            this.XLEdit.saveEditingValue(); //Get or reset the saved changes collection after data loaded
        }
    }
</script>

 

CONTROLLER

private static string connetionString = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString;
public ActionResult Index()
{
    @ViewBag.Datasource = GetDataFromDB();
    return View();
}
 
private List<Order> GetDataFromDB()
{
    List<Order> orders = new List<Order>();
    SqlConnection sqlCon = new SqlConnection(connetionString);
    SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table]", sqlCon);
    sqlCon.Open();
    SqlDataReader sqlDR = sqlComm.ExecuteReader();
    while (sqlDR.Read())
    {
        Order order = new Order();
        order.OrderID = (int)sqlDR.GetValue(0);
        order.ItemName = sqlDR.GetValue(1).ToString();
        order.Quantity = sqlDR.GetValue(2).ToString();
        order.Price = sqlDR.GetValue(3).ToString();
        order.Amount = sqlDR.GetValue(4).ToString();
        orders.Add(order);
    }
    sqlCon.Close();
    return orders;
}
 
//Save changes only
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult SaveChangesToDatabase(string editedValues)
{
    JavaScriptSerializer serialize = new JavaScriptSerializer();
    List<Order> editedData = serialize.Deserialize<List<Order>>(editedValues);
 
    SqlConnection sqlCon = new SqlConnection(connetionString);
    sqlCon.Open();
    for (int i = 0; i < editedData.Count; i++)
    {
        var order = editedData[i];
        SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [OrderID] = '" + order.OrderID + "'", sqlCon);
        var hasFileInDB = selectComm.ExecuteScalar();
        if (hasFileInDB == null)
        {
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon);
            sqlComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", order.Price);
            sqlComm.Parameters.AddWithValue("@Amount", order.Amount);
            sqlComm.ExecuteNonQuery();
        }
        else
        {
            SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
            updateComm.CommandTimeout = 0;
            updateComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            updateComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            updateComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            updateComm.Parameters.AddWithValue("@Price", order.Price);
            updateComm.Parameters.AddWithValue("@Amount", order.Amount);
            updateComm.ExecuteNonQuery();
        }
    }
    sqlCon.Close();
    return Content("Successfully updated");
}

 

ASP

 

ASPX

<input type="button" value="Save Changes to DB" id="saveChangesToDB" />
<ej:Spreadsheet ID="FlatSpreadsheet" ColumnWidth="110" runat="server">
    <Sheets>
        <ej:Sheet PrimaryKey="OrderID"></ej:Sheet>
    </Sheets>
    <ClientSideEvents LoadComplete="onLoadComplete" />
</ej:Spreadsheet>
<script type="text/javascript">
 
    $(function () {
        $("#saveChangesToDB").on("click", function () {
            var ssObj = $("#" + '<% =FlatSpreadsheet.ClientID %>').data("ejSpreadsheet"), key, rowIdx, sheetIdx = ssObj.getActiveSheetIndex(), editedChanges = ssObj.XLEdit.saveEditingValue().EditedData,
                dataSettings = ssObj.getDataSettings(sheetIdx)[0], isShowHeader = dataSettings.showHeader, dataSource = dataSettings.dataSource, changedDataSource = [], pushedRowColl = [];
            if (!editedChanges.length) {
                ssObj.alert("Please edit any cells and try again!");
                return;
            }
            ssObj.showWaitingPopUp();
            for (key in editedChanges) {
                rowIdx = editedChanges[key].CellIndex.RowIndex;
                if (isShowHeader)
                    rowIdx--;
                if (pushedRowColl.indexOf(rowIdx) < 0) {
                    changedDataSource.push(dataSource[rowIdx]);
                    pushedRowColl.push(rowIdx);
                }
            }
            $.ajax({
                type: "POST",
                url: "Default.aspx/SaveChangesToDatabase",
                data: JSON.stringify({ editedValues: JSON.stringify(changedDataSource) }),
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (response) {
                    ssObj.alert(response.d);
                    ssObj.hideWaitingPopUp();
                },
                error: function (err) {
                    ssObj.alert(err.statusText);
                    ssObj.hideWaitingPopUp();
                }
            });
        });
    });
    function onLoadComplete(args) {
        if (!this.isImport) {
            this.XLEdit.saveEditingValue(); //Get or reset the saved changes collection after data loaded
        }
    }
</script>

 

ASHX

private static string connetionString = ConfigurationManager.ConnectionStrings["SpreadDBConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        this.FlatSpreadsheet.Sheets[0].Datasource = GetDataFromDB();
    }
}
 
private List<Order> GetDataFromDB()
{
    List<Order> orders = new List<Order>();
    SqlConnection sqlCon = new SqlConnection(connetionString);
    SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table]", sqlCon);
    sqlCon.Open();
    SqlDataReader sqlDR = sqlComm.ExecuteReader();
    while (sqlDR.Read())
    {
        Order order = new Order();
        order.OrderID = (int)sqlDR.GetValue(0);
        order.ItemName = sqlDR.GetValue(1).ToString();
        order.Quantity = sqlDR.GetValue(2).ToString();
        order.Price = sqlDR.GetValue(3).ToString();
        order.Amount = sqlDR.GetValue(4).ToString();
        orders.Add(order);
    }
    sqlCon.Close();
    return orders;
}
 
//Save changes only
[WebMethod]
public static string SaveChangesToDatabase(string editedValues)
{
    JavaScriptSerializer serialize = new JavaScriptSerializer();
    List<Order> editedData = serialize.Deserialize<List<Order>>(editedValues);
 
    SqlConnection sqlCon = new SqlConnection(connetionString);
    sqlCon.Open();
    for (int i = 0; i < editedData.Count; i++)
    {
        var order = editedData[i];
        SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [OrderID] = '" + order.OrderID + "'", sqlCon);
        var hasFileInDB = selectComm.ExecuteScalar();
        if (hasFileInDB == null)
        {
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([OrderID], [ItemName], [Quantity], [Price], [Amount]) VALUES (@OrderID, @ItemName, @Quantity, @Price, @Amount)", sqlCon);
            sqlComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            sqlComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            sqlComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            sqlComm.Parameters.AddWithValue("@Price", order.Price);
            sqlComm.Parameters.AddWithValue("@Amount", order.Amount);
            sqlComm.ExecuteNonQuery();
        }
        else
        {
            SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET ItemName=@ItemName, Quantity=@Quantity, Price=@Price, Amount=@Amount WHERE OrderID=@OrderID", sqlCon); // Update edited data to DB.
            updateComm.CommandTimeout = 0;
            updateComm.Parameters.AddWithValue("@OrderID", order.OrderID);
            updateComm.Parameters.AddWithValue("@ItemName", order.ItemName);
            updateComm.Parameters.AddWithValue("@Quantity", order.Quantity);
            updateComm.Parameters.AddWithValue("@Price", order.Price);
            updateComm.Parameters.AddWithValue("@Amount", order.Amount);
            updateComm.ExecuteNonQuery();
        }
    }
    sqlCon.Close();
    return "Successfully updated";
}

 

ADD COMMENT
You must log in to leave a comment
Comments
esayas
Feb 27, 2021

Thank you that great one to try. I am try to use the mvc one, but brings error at EJ(), and some nuget required better to mention.I appreciate to know aspnet core 3 MVC using Visual studio 2019 of CSHTML and controller compatible code. I like and use on the try of syncfusion spreadsheets UI for now to choose from other EPPul, ...

Reply
Sangeetha Priya Murugan [Syncfusion]
Mar 01, 2021

Hi esayas,

Thank you for your update.

We have checked your reported requirement and we would suggest you to refer the below help documentation link to getting started with our EJ1 controls in ASP.NET MVC.

https://help.syncfusion.com/aspnetmvc/getting-started-vs-2019 https://help.syncfusion.com/aspnetmvc/spreadsheet/getting-started

For spreadsheet server side dependencies, please refer the below link.

https://help.syncfusion.com/aspnetmvc/spreadsheet/open-and-save#server-dependencies

Regards, Sangeetha M

esayas
Mar 07, 2021

thank you for the update. How I can insert spreadsheet each cell data to the mysql database table in aspnetcore 3 mvc & visual studio 2019 using latest sync fusion spreadsheet or latest of above code that compatible with aspnetcore 3 MVC & visual studio 2019 editer. not excel file import.

Reply
Sangeetha Priya Murugan [Syncfusion]
Mar 09, 2021

Hi esayas,

Thank you for your update.

We have checked your reported requirement and we would like to let you know that in this knowledge base document we have load the data from the sql table initially. And save the edited changes in the database via button click event. The below documentation link help to render the EJ1 spreadsheet with in ASP.NET MVC with VS 2019.

https://help.syncfusion.com/aspnetmvc/getting-started-vs-2019

https://help.syncfusion.com/aspnetmvc/spreadsheet/getting-started

Could you please check the above details and get back to us, if you need any further assistance on this.

Regards, Sangeetha M

esayas
Mar 09, 2021

thank you, I will check it

Reply

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Up arrow icon

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile