Serialize JSON model of Spreadsheet to C# object

[Richard de Zwart]

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]

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., 

 

Sample: http://www.syncfusion.com/downloads/support/forum/127956/ze/SyncfusionMvcApplication2-894740541  

 

Regards, 

Shanmugaraja K 

[Richard de Zwart]

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]

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.  

 

Sample: http://www.syncfusion.com/downloads/support/forum/127956/ze/SyncfusionMvcApplication2_(2)2000869168  

 

Regards, 

Shanmugaraja K