1. Home
  2. Forum
  3. ASP.NET Web Forms
  4. Open and save data from SQL Table in Spreadsheet.

Open and save data from SQL Table in Spreadsheet.

Hi,

I have checked and implemented the examples of saving and opening spreadsheet data from sql server both in stream and json format and these are working fine.

But i have to implement loading and saving sql table data in table format in row and columns as .

OrderIDCust_IDCity
123451001Solan
123461002Ghana


On page load i have to load this table data as it is in Spreadsheet and after making changes to table save this table back to sql server as :


OrderIDCust_IDCity
123451001Miami
123461002FIZI
and adding some chages or adding more data to this table

How can i acheive this requirement , any help??

13 Replies
SK Shanmugaraja K Syncfusion Team May 22, 2017 05:07 AM UTC

Hi Pradeep, 
 
Thanks for using Syncfusion products, 
 
We can achieve your requirement by using ejDataManager, we have created simple sample based on your requirement. Here we have sent the edited records details to server by using $.ajax method and saved the edited data into the database. Please refer the below code example, 
 
[ASPX] 
 
 
<input type="button" id="save" value="Save" onclick="saveData()" /> 
 
    <ej:DataManager ID="dataManager1" runat="server" URL="SpreadsheetFeatures.aspx/Data" BatchURL="SpreadsheetFeatures.aspx/Save" Adaptor="WebMethodAdaptor" /> 
    <ej:Spreadsheet ID="Spreadsheet1" runat='server' ClientIDMode="Static"> 
        <ClientSideEvents LoadComplete="loadComplete" /> 
        <ScrollSettings Width="100%" Height="540" IsResponsive="true" /> 
        <Sheets> 
            <ej:Sheet DataManagerID="dataManager1" Query="ej.Query().take(10).select(['OrderID', 'Quantity', 'Price', 'Amount', 'Discount'])" PrimaryKey="OrderID"></ej:Sheet> 
        </Sheets> 
    </ej:Spreadsheet> 
 
    <script type="text/javascript"> 
        function saveData() { 
            var xlObj = $('#Spreadsheet1').data("ejSpreadsheet"), changes = xlObj.getSheet(xlObj.getActiveSheetIndex()).batchChanges.changed; 
            if (changes.length) { 
                $.ajax({ 
                    type: "POST", 
                    url: "SpreadsheetFeatures.aspx/Save", 
                    data: JSON.stringify({ changed: JSON.stringify(changes) }), 
                    contentType: "application/json; charset=utf-8", 
                    dataType: 'json', 
                    success: function (data) { 
                        if (data.d == "Success") 
                            alert("Edited data updated successfully!!!"); 
                        else 
                            alert("Failed!!!"); 
                    } 
                }); 
            } 
        }    
     </script> 
 
 
 
[C#] 
 
 
    [WebMethod()] 
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)] 
    public static object Data(DataManager value) 
    { 
        string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\SpreadsheetData.mdf;Integrated Security=True"; 
        SqlConnection sqlCon = new SqlConnection(dbConnectionStr); 
        SqlCommand sqlComm = new SqlCommand("SELECT [OrderID],[Quantity],[Price],[Amount],[Discount] FROM [dbo].[Table]", sqlCon); 
        sqlCon.Open(); 
        List<Orders> order = new List<Orders>(); 
        SqlDataReader sqlDR = sqlComm.ExecuteReader(); 
        while (sqlDR.Read()) 
        { 
            order.Add(new Orders() { OrderID = (int)sqlDR.GetValue(0), Quantity = (int)sqlDR.GetValue(1), Price = (int)sqlDR.GetValue(2), Amount = (int)sqlDR.GetValue(3), Discount = (int)sqlDR.GetValue(4) }); 
        } 
        sqlCon.Close(); 
        return new { result = order, count = order.Count }; 
    } 
    [WebMethod] 
    public static string Save(string changed) 
    { 
        string changedData = HttpContext.Current.Request.Params["changed"]; 
        JavaScriptSerializer js = new JavaScriptSerializer(); 
        try 
        { 
            List<Orders> changes = js.Deserialize<List<Orders>>(changed); 
            string dbConnectionStr = @"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\SpreadsheetData.mdf;Integrated Security=True"; 
            SqlConnection sqlCon = new SqlConnection(dbConnectionStr); 
            foreach (var i in changes) 
            { 
                SqlCommand sqlComm = new SqlCommand("UPDATE [Table] SET [Quantity] = @Quantity, [Price] = @Price, [Amount] = @Amount, [Discount] = @Discount Where [OrderID] = @OrderID", sqlCon); 
                sqlComm.Parameters.AddWithValue("@OrderID", i.OrderID); 
                sqlComm.Parameters.AddWithValue("@Quantity", i.Quantity); 
                sqlComm.Parameters.AddWithValue("@Price", i.Price); 
                sqlComm.Parameters.AddWithValue("@Amount", i.Amount); 
                sqlComm.Parameters.AddWithValue("@Discount", i.Discount); 
                sqlCon.Open(); 
                sqlComm.ExecuteNonQuery(); 
                sqlCon.Close(); 
            } 
            return "Success"; 
        } 
        catch (Exception ex) 
        { 
            return "Failed"; 
        } 
    } 
 
 
 
Also, we have attached sample with these code example and the same can be downloaded from the below location, 
 
 
Regards, 
Shanmugaraja K 


JE Jose Eslava May 14, 2018 08:54 PM UTC

How to opening spreadsheet data from sql server after a dropdownlist selection?

I attache source code sample without success.

SP Sangeetha Priya Murugan Syncfusion Team May 15, 2018 12:54 PM UTC

Hi Jose, 
 
We would like to let you know that, we have already provided a solution for your requirement “To open spreadsheet data from Sql server based on dropdownlist selection” in the below KB link for all platforms (JS, MVC & ASP). However, for your convenience – we have prepared an ASP.NET Web Form sample to demonstrate your requirement and the same can be downloaded from the following sample link. 
 
 
 
Could you please check the above sample & KB link and get back to us if you need any further assistance?  
 
Regards, 
Sangeetha Priya M 


PS Prashad Sri Tharanga November 15, 2020 04:15 PM UTC

How can apply this to ASP.Net Core with ejs-spreadsheet ? 
I want pass spreadsheet entered data  to HttpPost method

SP Sangeetha Priya Murugan Syncfusion Team November 16, 2020 06:41 AM UTC

Hi Prashad Sri Tharanga, 
 
Thank you for your update. 
 
We have checked your reported requirement and it can be achievable in our spreadsheet by using the openFromJson and saveAsJson method. We have already demonstrated this in our KB documentation itself. Please find the link below. 
 
 
Could you please check the above link and get back to us, if you need any further assistance on this. 
 
Regards, 
Sangeetha M 


PS Prashad Sri Tharanga November 17, 2020 11:02 AM UTC

This script  does not pass data to the POST method, its  coming up as null. Please check the below screenshot.

<script>
   
    document.getElementById("saveToServer").onclick = function ()  {
        var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
       
        spreadsheetObj.saveAsJson().then((Json) =>
            fetch("/Stages/SaveFiletoDB", {
                method: 'POST',
                headers: {
                    'Content-Type': 'application/json',
                },
                body: JSON.stringify({
                    FileName: document.getElementById("filename").value,
                    JSONData: JSON.stringify(Json.jsonObject.Workbook),
                    ContentType: "Xlsx",
                    VersionType: "Xlsx",
                })
            })
                .then((basePath) => {
                    console.log("file saved");
                })
        );
    }



SP Sangeetha Priya Murugan Syncfusion Team November 17, 2020 12:41 PM UTC

Hi Prashad Sri Tharanga, 
 
Thank you for your update. 
 
We have checked your reported requirement to send the spreadsheet data to the server. And it can be achievable in our spreadsheet by using openFromJson and saveAsJson in a button click event. 
 
 
<label>File name:</label> 
<input type="text" id="filename" value="Sample1" placeholder="Specify file name"> 
<button id="LoadFromDataBase" class="e-btn">LoadFromDataBase</button> 
<button id="saveToServer" class="e-btn">saveToServer</button> 
 
<ejs-spreadsheet id="spreadsheet" openUrl="Home/LoadFromDataBase" saveUrl="Home/SaveFiletoDB"> 
    <e-spreadsheet-sheets> 
        <e-spreadsheet-sheet name="Car Sales Report"> 
        </e-spreadsheet-sheet> 
    </e-spreadsheet-sheets> 
</ejs-spreadsheet> 
 
<script> 
    document.getElementById("saveToServer").onclick = function () { 
        var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet'); 
        spreadsheetObj.saveAsJson().then((Json) => { 
            var formData = new FormData(); 
            formData.append('JSONData', JSON.stringify(Json.jsonObject.Workbook)); 
            formData.append('FileName', document.getElementById("filename").value); 
            formData.append('saveType', 'Xlsx'); 
            fetch('Home/SaveFiletoDB', { 
                method: 'POST', 
                body: formData 
            }).then((basePath) => { 
                console.log("file saved"); 
            }) 
        } 
        ); 
    } 
 
    document.getElementById("LoadFromDataBase").onclick = function () { 
        var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet'); 
        fetch('/Home/LoadFromDataBase', { 
            method: 'POST', 
            headers: { 
                'Content-Type': 'application/json', 
            }, 
            body: JSON.stringify({ Name: (document.getElementById("filename")).value }), 
        }) 
            .then((response) => response.json()) 
            .then((data) => { 
                spreadsheetObj.openFromJson({ file: data }); 
            }) 
    } 
</script> 
 
//Open saved Spreadsheet data from database 
        public IActionResult LoadFromDataBase([FromBody]FileOptions file) 
        { 
            // Convert Spreadsheet data as Stream. 
            OpenRequest open = new OpenRequest(); 
            MemoryStream dataStream = new MemoryStream(); 
            SqlConnection sqlCon = new SqlConnection(connectionString); 
            SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table1] WHERE [filename] = '" + file.Name + "'", sqlCon); 
            sqlCon.Open(); 
            SqlDataReader sqlDR = sqlComm.ExecuteReader(); 
            while (sqlDR.Read()) 
            { 
                dataStream = new MemoryStream((byte[])sqlDR.GetValue(1)); 
                IFormFile formFile = new FormFile(dataStream, 0, dataStream.Length, "", file.Name + ".xlsx"); // converting MemoryStream to IFormFile  
                open.File = formFile; 
            } 
            sqlCon.Close(); 
            return Content(Workbook.Open(open)); 
        } 
 
        // Save the Spreadsheet data as byte array to database. 
        public IActionResult SaveFiletoDB(SaveSettings saveSettings) 
        { 
            //save the file to database 
            MemoryStream dataStream = Workbook.Save<MemoryStream>(saveSettings); 
            byte[] dataBytes = new BinaryReader(dataStream).ReadBytes(Convert.ToInt32(dataStream.Length)); 
            SqlConnection sqlCon = new SqlConnection(connectionString); 
            sqlCon.Open(); 
            SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table1]([filename], [databytes]) VALUES (@filename, @databytes)", sqlCon); 
            sqlComm.Parameters.AddWithValue("@fileName", saveSettings.FileName); 
            sqlComm.Parameters.AddWithValue("@dataBytes", dataBytes); 
            sqlComm.ExecuteNonQuery(); 
            sqlCon.Close(); 
            return Content("Saved in Server"); 
        } 
 
For your convenience, we have prepared the sample based on your requirement. Please find the link below. 
 
 
Could you please check the above link and get back to us, if you need any further assistance on this. 
 
Regards, 
Sangeetha M 


PS Prashad Sri Tharanga November 18, 2020 06:47 AM UTC

Hi 
Sangeetha M 

Sample project very helpful.
Thank You.

SP Sangeetha Priya Murugan Syncfusion Team November 18, 2020 08:46 AM UTC

 
Hi Prashad Sri Tharanga 
  
Thank you for your update. Please feel free to contact us, if you need any further assistance on this.  
  
Regards,  
Sangeetha M  



PS Prashad Sri Tharanga November 18, 2020 10:08 AM UTC

Hi Sangeetha Priya Murugan
Need Your Help
I got Json data like  this. 

"{\"openUrl\":\"Stages/LoadFromDataBase\",\"saveUrl\":\"Stages/SaveFiletoDB\",\"enableRtl\":false,\"locale\":\"en-US\",\"enablePersistence\":false,\"cellStyle\":{\"fontFamily\":\"Calibri\",\"verticalAlign\":\"bottom\",\"textIndent\":\"0pt\",\"backgroundColor\":\"#ffffff\",\"color\":\"#000000\",\"textAlign\":\"left\",\"fontSize\":\"11pt\",\"fontWeight\":\"normal\",\"fontStyle\":\"normal\",\"textDecoration\":\"none\",\"border\":\"\",\"borderLeft\":\"\",\"borderTop\":\"\",\"borderRight\":\"\",\"borderBottom\":\"\"},\"showRibbon\":true,\"showFormulaBar\":true,\"showSheetTabs\":true,\"allowEditing\":true,\"allowOpen\":true,\"allowSave\":true,\"enableContextMenu\":true,\"selectionSettings\":{\"mode\":\"Multiple\"},\"enableKeyboardNavigation\":true,\"allowNumberFormatting\":true,\"enableKeyboardShortcut\":true,\"enableClipboard\":true,\"allowCellFormatting\":true,\"allowSorting\":true,\"allowResizing\":true,\"allowHyperlink\":true,\"allowUndoRedo\":true,\"allowFiltering\":true,\"allowWrap\":true,\"allowInsert\":true,\"allowDelete\":true,\"allowDataValidation\":true,\"allowFindAndReplace\":true,\"allowMerge\":true,\"allowConditionalFormat\":true,\"allowImage\":true,\"activeSheetIndex\":0,\"cssClass\":\"\",\"height\":\"100%\",\"width\":\"100%\",\"allowScrolling\":true,\"scrollSettings\":{\"enableVirtualization\":true,\"isFinite\":false},\"definedNames\":[],\"sheets\":[{\"name\":\"Stages\",\"rowCount\":100,\"colCount\":100,\"topLeftCell\":\"A1\",\"activeCell\":\"C3\",\"selectedRange\":\"C3:C3\",\"usedRange\":{\"rowIndex\":2,\"colIndex\":2},\"rows\":[{\"cells\":[{\"value\":\"10\"},{\"value\":\"20\"},{\"value\":\"20\"}]},{\"cells\":[{\"value\":\"10\"},{\"value\":\"30\"},{\"value\":\"30\"}]}],\"showHeaders\":true,\"showGridLines\":true,\"state\":\"Visible\",\"protectSettings\":{},\"isProtected\":false,\"index\":0}]}"

But  I want to get Spreadsheet cell entered data to list for save database. How can i deserialize the data or cell wise, Its not in the sample project 

Thank You




SP Sangeetha Priya Murugan Syncfusion Team November 23, 2020 06:58 AM UTC

Hi Prashad Sri Tharanga,  
  
Thank you for your update.  
 
We have checked your reported requirement and it can be achievable in our spreadsheet by using the DataManager and saveaAsJson method as like as below. 
 
 
<script> 
    var json = []; 
    document.getElementById('save').addEventListener('click', function () { 
        var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet'); 
        json = []; 
        var obj = {}; 
        var ssObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet'); 
        var usedRange = ssObj.getActiveSheet().usedRange; 
        var selIndex = [1, 0, usedRange.rowIndex, usedRange.colIndex]; 
        var range = 
            ssObj.getActiveSheet().name + 
            "!" + 
            ejs.spreadsheet.getRangeAddress([1, 0, selIndex[2], selIndex[3]]); 
        ssObj.getData(range).then( 
            (value) => { 
                (value).forEach( 
                    (cell, key) => { 
                        if (cell) { 
                            // constructing the key value object 
                            var indexes = ejs.spreadsheet.getRangeIndexes(key); 
                            if (key.indexOf("A") > -1) { 
                                obj["employeeID"] = cell.value; 
                            } else if (key.indexOf("B") > -1) { 
                                obj["lastName"] = cell.value; 
                            } else if (key.indexOf("C") > -1) { 
                                obj["firstName"] = cell.value; 
                            } else if (key.indexOf("D") > -1) { 
                                obj["title"] = cell.value; 
                            } else if (key.indexOf("E") > -1) { 
                                obj["titleOfCourtesy"] = cell.value; 
                            } 
                            if (indexes[1] === selIndex[3]) { 
                                // row last index 
                                json.push(obj); 
                                obj = {}; 
                            } 
 
                        } 
                    } 
                ); 
                console.log(json); 
                var formData = new FormData(); 
                formData.append('JSONData', JSON.stringify(json)); 
                fetch("https://localhost:44355/Home/SaveChangesToDatabase", { method: 'Post', body: formData }) 
                    .then((response) => { 
                        var spreadObj = ej.base.getComponent(document.getElementById("spreadsheet"), "spreadsheet"); 
                        response.blob().then((response) => { 
                            if (response.ok) { 
                                return response.json(); 
                            } else { 
                                return Promise.reject({ 
                                    message: response.statusText 
                                }); 
                            } 
                        }).then((data) => { 
                            alert(data); 
                        }); 
                    }) 
            } 
        ); 
 
    }); 
</script> 
 
public object LoadFromTable() 
        { 
            List<EmployeeData> datas = new List<EmployeeData>(); 
            SqlConnection sqlCon = new SqlConnection(connectionString); 
            SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Employees]", sqlCon); 
            sqlCon.Open(); 
            SqlDataReader sqlDR = sqlComm.ExecuteReader(); 
            while (sqlDR.Read()) 
            { 
                EmployeeData data = new EmployeeData(); 
                data.employeeID = (int)sqlDR.GetValue(0); 
                data.lastName = sqlDR.GetValue(1).ToString(); 
                data.firstName = sqlDR.GetValue(2).ToString(); 
                data.title = sqlDR.GetValue(3).ToString(); 
                data.titleOfCourtesy = sqlDR.GetValue(4).ToString(); 
                datas.Add(data); 
            } 
            sqlCon.Close(); 
            return new { Result= datas, Count= datas.Count }; 
        } 
 
        [AcceptVerbs("Post")] 
        public string SaveChangesToDatabase(string JSONData) 
        { 
            JavaScriptSerializer serialize = new JavaScriptSerializer(); 
            List<EmployeeData> editedData = serialize.Deserialize<List<EmployeeData>>(JSONData); 
            SqlConnection sqlCon = new SqlConnection(connectionString); 
            sqlCon.Open(); 
            for (int i = 0; i < editedData.Count; i++) 
            { 
    
                var order = editedData[i]; 
                SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Employees] WHERE [EmployeeID] = '" + order.employeeID + "'", sqlCon); 
                var hasFileInDB = selectComm.ExecuteScalar(); 
                if (hasFileInDB == null) 
                { 
                    SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Employees]([EmployeeID], [LastName], [FirstName], [Title], [TitleOfCourtesy]) VALUES (@EmployeeID, @LastName, @FirstName, @Title, @TitleOfCourtesy)", sqlCon); 
                    sqlComm.Parameters.AddWithValue("@EmployeeID", order.employeeID); 
                    sqlComm.Parameters.AddWithValue("@LastName", order.lastName); 
                    sqlComm.Parameters.AddWithValue("@FirstName", order.firstName); 
                    sqlComm.Parameters.AddWithValue("@Title", order.title); 
                    sqlComm.Parameters.AddWithValue("@TitleOfCourtesy", order.titleOfCourtesy); 
                    sqlComm.ExecuteNonQuery(); 
                } 
                else 
                { 
                    SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Employees] SET LastName=@LastName, FirstName=@FirstName, Title=@Title, TitleOfCourtesy=@TitleOfCourtesy WHERE EmployeeID=@EmployeeID", sqlCon); // Update edited data to DB. 
                    updateComm.CommandTimeout = 0; 
                    updateComm.Parameters.AddWithValue("@EmployeeID", order.employeeID); 
                    updateComm.Parameters.AddWithValue("@LastName", order.lastName); 
                    updateComm.Parameters.AddWithValue("@FirstName", order.firstName); 
                    updateComm.Parameters.AddWithValue("@Title", order.title); 
                    updateComm.Parameters.AddWithValue("@TitleOfCourtesy", order.titleOfCourtesy); 
                    updateComm.ExecuteNonQuery(); 
                } 
            } 
            sqlCon.Close(); 
            return "Successfully updated"; 
        }   
 
 
Could you please check the above details and get back to us, if you need any further assistance on this. 
 
Regards, 
Sangeetha M 



PS Prashad Sri Tharanga November 27, 2020 04:35 PM UTC

Hi Sangeetha Priya Murugan

 Sample code very helpful.

Thank You.

SP Sangeetha Priya Murugan Syncfusion Team November 30, 2020 06:26 AM UTC

 
Hi Prashad Sri Tharanga  
   
Thank you for your update. Please feel free to contact us, if you need any further assistance on this.   
   
Regards,   
Sangeetha M   


Loader.
Up arrow icon