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.
Unfortunately, activation email could not send to your email. Please try again.

Save data from spreadsheet to database

Thread ID:

Created:

Updated:

Platform:

Replies:

130499 May 16,2017 04:42 AM May 18,2017 06:45 AM ASP.NET MVC 1
loading
Tags: Spreadsheet
Anis
Asked On May 16, 2017 04:42 AM

Hi,
Is there a way to save/import data from Spreadsheet control to sql database?
My spreadsheet contains a matrix (n lines and m columns),and the user can add or modify the values (there is excel functions depends on these values), at the end of modification I want to save the data on thisspreadsheet.
Is this possible?

Regards,
Anis

Silambarasan I [Syncfusion]
Replied On May 18, 2017 06:45 AM

Hi Anis, 
 
Thank you for using Syncfusion products. 
 
We have checked your query and we would like to let you know that your requirement “To save/import, update data from Spreadsheet control to SQL database” has been achieved by using ‘Open()’ & ‘Save()’ server-side methods in Spreadsheet.  Please refer the following code example. 
 
CSHTML[VIEW] 
 
<table> 
<tr> 
    //... 
        @Html.DropDownList("ImportFileName", ViewBag.FileNameList as List<SelectListItem>, new { @style = "width:130px;" }) 
    <td> 
        <input type="button" value="Import" id="openFileFromDatabase" /> 
    </td> 
</tr> 
</table> 
 
<table> 
    //... 
    <tr> 
        //... 
        <td> 
            <input type="button" value="Save" id="saveFileToDatabase" /> 
        </td> 
        <td> 
            <input type="button" value="Save Changes" id="saveChangesOnly" /> 
        </td> 
    </tr> 
</table> 
 
@(Html.EJ().Spreadsheet<object>("Spreadsheet") 
    //... 
    .ClientSideEvents(eve => 
    { 
        eve.LoadComplete("onLoadComplete"); 
    }) 
) 
 
<script type="text/javascript"> 
    function onLoadComplete(args) { 
        if (this.isImport) 
            this.XLEdit.saveEditingValue();//Get or reset the saved changes collection on importing data from DB 
    } 
    $("#openFileFromDatabase").bind("click", function () { 
        var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#ImportFileName").val(); 
        if (fileName.length) { 
            ssObj.showWaitingPopUp(); 
            $.ajax({ 
                type: "POST", 
                data: { filename: fileName }, 
                url: "/Home/OpenFileFromDB", 
                success: function (data) { 
                    ssObj.loadFromJSON(JSON.parse(data)); 
                    ssObj.hideWaitingPopUp();                     
                } 
            }); 
        } 
    }); 
 
    $("#saveFileToDatabase").bind("click", function () { 
        var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), exportProp = ssObj.XLExport.getExportProps(), filename = $("#exportFileName").val(); 
        $.ajax({ 
            type: "POST", 
            data: { fileName: filename, sheetModel: exportProp.model, sheetData: exportProp.data }, 
            url: "/Home/SaveAndUpdateToDatabase", 
            success: function (data) { 
                //... 
            } 
        }); 
    }); 
 
    $("#saveChangesOnly").bind("click", function () { 
        var ssObj = $("#Spreadsheet").data("ejSpreadsheet"), filename = $("#exportFileName").val(), editedChanges = ssObj.XLEdit.saveEditingValue(); 
        $.ajax({ 
            type: "POST", 
            data: { fileName: filename, editedValues: JSON.stringify(editedChanges.EditedData) }, 
            url: "/Home/SaveChangesToDatabase", 
            success: function (data) { 
                //... 
            } 
        }); 
    }); 
</script> 
 
 
 
C# [CONTROLLER] 
 
//File Open from database 
[AcceptVerbs(HttpVerbs.Post)] 
public ActionResult OpenFileFromDB(string filename) 
{ 
    ImportRequest importRequest = new ImportRequest(); 
    SqlConnection sqlCon = new SqlConnection(connetionString); 
    SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [FileName] = '" + filename + "'", sqlCon); 
    sqlCon.Open(); 
    SqlDataReader sqlDR = sqlComm.ExecuteReader(); 
    if (sqlDR.Read()) 
    { 
        importRequest.FileStream = new MemoryStream((byte[])sqlDR.GetValue(1)); // Get binary values from DB. 
    } 
    sqlCon.Close(); 
    return Content(Spreadsheet.Open(importRequest)); 
} 
 
[AcceptVerbs(HttpVerbs.Post)] 
public ActionResult SaveAndUpdateToDatabase(string fileName, string sheetModel, string sheetData) 
{ 
    MemoryStream dataStream = (MemoryStream)Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013);             
    byte[] dataBytes = dataStream.ToArray(); 
    SqlConnection sqlCon = new SqlConnection(connetionString); 
    sqlCon.Open(); 
    SqlCommand selectComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [FileName] = '" + fileName + "'", sqlCon); 
    var hasFileInDB = selectComm.ExecuteScalar(); 
    if (hasFileInDB == null) 
    { 
        SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table]([FileName], [FileData]) VALUES (@FileName, @FileData)", sqlCon); 
        sqlComm.Parameters.AddWithValue("@FileName", fileName); 
        sqlComm.Parameters.AddWithValue("@FileData", dataBytes); 
        sqlComm.ExecuteNonQuery(); 
    } 
    else 
    { 
        SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET FileData=@nFileData WHERE FileName=@nFileName", sqlCon); // Update edited data to DB. 
        updateComm.CommandTimeout = 0; 
        updateComm.Parameters.AddWithValue("@nFileName", fileName); 
        updateComm.Parameters.AddWithValue("@nFileData", dataBytes); 
        updateComm.ExecuteNonQuery(); 
    } 
    sqlCon.Close(); 
    return Content(fileName); 
} 
 
//Save changes only 
[AcceptVerbs(HttpVerbs.Post)] 
public ActionResult SaveChangesToDatabase(string fileName, string editedValues) 
{ 
    JavaScriptSerializer serialize = new JavaScriptSerializer(); 
    List<CellDetail> editedData = serialize.Deserialize<List<CellDetail>>(editedValues); 
 
    MemoryStream dataStream = new MemoryStream(); 
    SqlConnection sqlCon = new SqlConnection(connetionString); 
    SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [FileName] = '" + fileName + "'", sqlCon); 
    sqlCon.Open(); 
    SqlDataReader sqlDR = sqlComm.ExecuteReader(); 
    if (sqlDR.Read()) 
    { 
        dataStream = new MemoryStream((byte[])sqlDR.GetValue(1)); // Get Stream values from DB. 
    } 
    sqlDR.Close(); 
    ExcelEngine excelEngine = new ExcelEngine(); 
    IApplication application = excelEngine.Excel; 
    IWorkbook workbook = application.Workbooks.Open(dataStream); 
    IWorksheet sheet; 
    for (int i = 0; i < editedData.Count; i++) 
    { 
        CellDetail data = editedData[i]; 
        sheet = workbook.Worksheets[data.SheetIndex - 1]; 
        // C# backend edit process by using XlsIo Library. 
        sheet.Rows[data.CellIndex.RowIndex].Cells[data.CellIndex.ColIndex].Value = data.Value; 
    } 
    MemoryStream newDataStream = new MemoryStream(); 
    workbook.Version = ExcelVersion.Excel2013; 
    workbook.Application.DefaultVersion = ExcelVersion.Excel2013; 
    workbook.SaveAs(newDataStream); 
 
    SqlCommand updateComm = new SqlCommand("UPDATE [dbo].[Table] SET FileData=@nFileData WHERE FileName=@nFileName", sqlCon);             
    byte[] dataBytes = newDataStream.ToArray(); 
    updateComm.Parameters.AddWithValue("@nFileName", fileName); 
    updateComm.Parameters.AddWithValue("@nFileData", dataBytes); 
    updateComm.ExecuteNonQuery(); 
    sqlCon.Close(); 
    return Content("Successfully updated"); 
} 
 
 
For your convenience, we have prepared a sample to demonstrate your requirement and the same can be downloaded from the below link, 
 
Could you please check the above sample and get back to us if we misunderstood your requirement? Also, please refer the below KB link, 
 
Regards, 
Silambarasan 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

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.

;