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. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Serialize JSON model of Spreadsheet to C# object

Thread ID:

Created:

Updated:

Platform:

Replies:

127956 Dec 21,2016 04:09 PM UTC Dec 23,2016 11:35 AM UTC ASP.NET MVC 3
loading
Tags: Spreadsheet
Richard de Zwart
Asked On December 21, 2016 04:09 PM UTC

Hi there,

I save the content of my Spreadsheet control with like this code:

        var xlObj = $("#tabblad").data("ejSpreadsheet");
        var xlData = xlObj.saveAsJSON();

The xlData I post to my MVC Controller after stringify-ing it. So in my controller action I receive the JSON as a string. So far so good. I want to store this string in the database and later retrieve it. Also not a problem. But then when I retrieve the JSON from the database, I want to update some cells in it.

I was looking for some class in the Syncfusion.EJ namespace that understands the JSON format and that gives me an object that I can use like:

sheet.Cells["A1"].Value = 12345;

I do not want to parse the string myself since the format may be different on newer versions of Syncfusion. I want to rely on a Syncfusion class.

Is this possible?

Richard

Shanmugaraja K [Syncfusion]
Replied On December 22, 2016 11:00 AM UTC

Hi Richard, 
 
Thanks for using Syncfusion products, 
 
We have analyzed your requirement, we can achieve your requirement by using open() and save() methods, we have created simple sample with your requirement. In this sample we have saved Spreadsheet data as a Stream to DB then retrieve and edit the data by using Syncfusion XlsIo Library. Please refer the below code examples. 
 
[JS] 
 
..// 
    // Save file to DB  
    $("#saveFileToDatabase").bind("click", function () { 
        var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), exportProp = xlObj.XLExport.getExportProps(), filename = $("#exportFileName").val(); 
        $.ajax({ 
            type: "POST", 
            data: { fileName: filename, sheetModel: exportProp.model, sheetData: exportProp.data }, 
            url: "/Home/SaveAndUpdateFileFromDatabase", 
            success: function (data) { 
                if ($("#ImportFileName").text().indexOf(filename.replace(".xlsx", "")) === -1) 
                    $("#ImportFileName").append("<option value=" + filename + ">" + filename.replace(".xlsx", "") + "</option>"); 
                alert("File Saved Successfully!"); 
            } 
        }); 
    }); 
 
// Open file from DB 
 
    $("#openFileFromDatabase").bind("click", function () { 
        var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#ImportFileName").val(); 
        if (fileName.length) { 
            xlObj.showWaitingPopUp(); 
            $.ajax({ 
                type: "POST", 
                data: { filename: fileName }, 
                url: "/Home/OpenFileFromDB", 
                success: function (data) { 
                    xlObj.loadFromJSON(JSON.parse(data)); 
                    xlObj.hideWaitingPopUp(); 
                    $("#exportFileName").val(fileName); 
                } 
            }); 
        } 
    }); 
..// 
 
[CS] 
 
 
        [AcceptVerbs(HttpVerbs.Post)] 
        public ActionResult ReadAndUpdateDBData() 
        { 
            string fileName = "File2.xlsx"; 
            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 = workbook.Worksheets[0]; 
            sheet.Cells[0].Text = "Spreadsheet";   // C# backend edit process by using XlsIo Library. 
            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); 
            updateComm.CommandTimeout = 0; 
            newDataStream.Position = 0; 
            byte[] dataBytes = new BinaryReader(newDataStream).ReadBytes(Convert.ToInt32(newDataStream.Length)); 
            updateComm.Parameters.AddWithValue("@nFileName", fileName); 
            updateComm.Parameters.AddWithValue("@nFileData", dataBytes); 
            updateComm.ExecuteNonQuery(); 
            sqlCon.Close(); 
            return Content("Successfully updated"); 
        } 
 
        [AcceptVerbs(HttpVerbs.Post)] 
        public ActionResult SaveAndUpdateFileFromDatabase(string fileName, string sheetModel, string sheetData) 
        { 
            Stream dataStream = Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013); 
            dataStream.Position = 0; 
            byte[] dataBytes = new BinaryReader(dataStream).ReadBytes(Convert.ToInt32(dataStream.Length)); 
            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); 
        } 
 
        //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)); 
        } 
 
 
We have attached below sample with these code examples and the same can be downloaded from the below location., 
 
 
Regards, 
Shanmugaraja K 


Richard de Zwart
Replied On December 22, 2016 11:11 AM UTC

Hi Shanmugaraja,

Thanks for your answer. I think I understand what you mean, but let me summarize to make sure:
  1. I do not POST the Json of the spreadsheet component  itself to my controller, but instead send the export properties
  2. On the server in my controller I use the ExcelEngine component to read the posted data and convert it into a worksheet
  3. I can then set cell values and convert it back to JSON to store it in the database ( I do not want to store binary-data, I prefer JSON)

Am I correct?

Richard

Shanmugaraja K [Syncfusion]
Replied On December 23, 2016 11:35 AM UTC

Hi Richard, 
 
Thanks for your update, 
 
We would like to let you know that our ExcelEngine can open and process Stream only. So, we have converted the posted Spreadsheet properties to Stream and edit some values by using ExcelEngine. Then convert back to JSON string by using Open() method of Spreadsheet and saved it to DataBase. Please refer the below code examples, 
 
[JS] 
 
 
    $("#openFileFromDatabase").bind("click", function () { 
        var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#ImportFileName").val(); 
        if (fileName.length) { 
            xlObj.showWaitingPopUp(); 
            $.ajax({ 
                type: "POST", 
                data: { filename: fileName }, 
                url: "/Home/OpenFileFromDB", 
                success: function (data) { 
                    xlObj.loadFromJSON(JSON.parse(data)); 
                    xlObj.hideWaitingPopUp(); 
                    $("#exportFileName").val(fileName); 
                } 
            }); 
        } 
    }); 
 
    $("#saveFileToDatabase").bind("click", function () { 
        var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), exportProp = xlObj.XLExport.getExportProps(), filename = $("#exportFileName").val(); 
        $.ajax({ 
            type: "POST", 
            data: { fileName: filename, sheetModel: exportProp.model, sheetData: exportProp.data }, 
            url: "/Home/SaveAndUpdateFiletoDB", 
            success: function (data) { 
                if ($("#ImportFileName").text().indexOf(filename.replace(".xlsx", "")) === -1) 
                    $("#ImportFileName").append("<option value=" + filename + ">" + filename.replace(".xlsx", "") + "</option>"); 
                alert("File Saved Successfully!"); 
            } 
        }); 
    }); 
 
 
[CS] 
 
 
        [AcceptVerbs(HttpVerbs.Post)] 
        public string SaveAndUpdateFiletoDB(string fileName, string sheetModel, string sheetData) 
        { 
            Stream dataStream = Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013); 
            dataStream.Position = 0; 
            ExcelEngine engine = new ExcelEngine(); 
            IApplication application = engine.Excel; 
            IWorkbook workbook = application.Workbooks.Open(dataStream); 
            IWorksheet worksheet = workbook.Worksheets[0]; 
            worksheet.Cells[0].Text = "Spreadsheet"; 
            MemoryStream newStream = new MemoryStream(); 
            workbook.Version = ExcelVersion.Excel2013; 
            workbook.Application.DefaultVersion = ExcelVersion.Excel2013; 
            // Reset stream position. 
            workbook.SaveAs(newStream); 
            ImportRequest ImpReq = new ImportRequest(); 
            newStream.Position = 0; 
            ImpReq.FileStream = newStream; 
            // Convert Stream to string here. 
            string SpreadsheetData = Spreadsheet.Open(ImpReq); 
            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); 
                // Saved Spreadsheet data as JSON in DB 
                sqlComm.Parameters.AddWithValue("@FileData", SpreadsheetData); 
                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", SpreadsheetData); 
                updateComm.ExecuteNonQuery(); 
            } 
            sqlCon.Close(); 
            return fileName; 
        } 
 
        //File Open from database 
        [AcceptVerbs(HttpVerbs.Post)] 
        public string OpenFileFromDB(string filename) 
        { 
            ImportRequest importRequest = new ImportRequest(); 
            SqlConnection sqlCon = new SqlConnection(connetionString); 
            SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table] WHERE [FileName] = '" + filename + "'", sqlCon); 
            string JsonData = ""; 
            sqlCon.Open(); 
            SqlDataReader sqlDR = sqlComm.ExecuteReader(); 
            if (sqlDR.Read()) 
            { 
                // Retrive JSON from DB 
                JsonData = (string)sqlDR.GetValue(1); 
            } 
            sqlCon.Close(); 
            return JsonData; 
        } 
 
 
 
We have attached sample with these code examples, please check whether this satisfies your requirement.  
 
 
Regards, 
Shanmugaraja K 


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.

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

;