Category / Section
How to save and retrieve the Spreadsheet data in database
4 mins read
Description
This Knowledge Base explains the way to save and retrieve the Spreadsheet data as a byte array in Database.
Solution
You can use “Open()” and “Save()” server methods to achieve this requirement.
API Documentation link: https://help.syncfusion.com/js/spreadsheet/open-and-save
JavaScript Solution
[JS]
<div style="margin: 7px;"> <!-- Save Spreadsheet data to database --> <input type="text" id="saveFileName" /> <input type="button" value="Save" id="saveFileToDatabase" /> <!-- Open Spreadsheet data from database --> <select id="importFileName"> <!-- Existing saved files --> <option value="file1">File 1</option> <option value="file2">File 2</option> </select> <input type="button" value="Import" id="openFileFromDatabase" /> </div> <div id="Spreadsheet"></div> <script> $(function () { $("#Spreadsheet").ejSpreadsheet({ sheets: [{ // window.defaultData from http://js.syncfusion.com/demos/web/scripts/xljsondata.min.js file dataSource: window.defaultData }], scrollSettings: { height: "100%", width: "100%", isResponsive: true } }); }); $("#openFileFromDatabase").bind("click", function () { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#importFileName").val(); if (fileName.length) { xlObj.showWaitingPopUp(); $.ajax({ type: "POST", url: "/Spreadsheet/OpenFileFromDB", data: { filename: fileName }, success: function (data) { xlObj.loadFromJSON(JSON.parse(data)); xlObj.hideWaitingPopUp(); } }); } }); $("#saveFileToDatabase").bind("click", function () { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), exportProp = xlObj.XLExport.getExportProps(), filename = $("#saveFileName").val(); $.ajax({ type: "POST", data: { fileName: filename, sheetModel: exportProp.model, sheetData: exportProp.data }, url: "/Spreadsheet/SaveFiletoDB", success: function (data) { // Success code here. } }); }); </script>
[Web API]
string connectionString = ConfigurationManager.ConnectionStrings["FileData"].ConnectionString; [OperationContract] [WebGet(BodyStyle = WebMessageBodyStyle.Bare)] [System.Web.Http.ActionName("SaveFiletoDB")] [AcceptVerbs("POST")] // Save the Spreadsheet data as byte array to database public void SaveFiletoDB() { string fileName = HttpContext.Current.Request.Params["fileName"], sheetModel = HttpContext.Current.Request.Params["sheetModel"], sheetData = HttpContext.Current.Request.Params["sheetData"]; try { if (fileName.Length > 0) { 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(connectionString); sqlCon.Open(); 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(); sqlCon.Close(); } } catch (Exception e) { // Error handling code here. } } //Open Saved Spreadsheet data from database [OperationContract] [WebGet(BodyStyle = WebMessageBodyStyle.Bare)] [System.Web.Http.ActionName("OpenFileFromDB")] [AcceptVerbs("POST")] public string OpenFileFromDB() { string filename = HttpContext.Current.Request.Params["filename"]; ImportRequest importRequest = new ImportRequest(); SqlConnection sqlCon = new SqlConnection(connectionString); 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)); } sqlCon.Close(); return Spreadsheet.Open(importRequest); }
MVC Solution
[CSHTML]
<div style="margin: 7px;"> <!-- Save Spreadsheet data to database --> <input type="text" id="saveFileName" /> <input type="button" value="Save" id="saveFileToDatabase" /> <!-- Open Spreadsheet data from database --> @Html.DropDownList("importFileName", ViewBag.FileNameList as List<SelectListItem>) <input type="button" value="Import" id="openFileFromDatabase" /> </div> <div style="height:550px;"> @(Html.EJ().Spreadsheet<object>("Spreadsheet") .ScrollSettings(scroll => { scroll.Height(550); }) ) </div> </div> <script> $("#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(); $("#saveFileName").val(fileName); } }); } }); $("#saveFileToDatabase").bind("click", function () { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), exportProp = xlObj.XLExport.getExportProps(), filename = $("#saveFileName").val(); $.ajax({ type: "POST", data: { fileName: filename, sheetModel: exportProp.model, sheetData: exportProp.data }, url: "/Home/SaveFiletoDB", success: function (data) { // Success code here. } }); }); </script>
[Controller]
string connectionString = ConfigurationManager.ConnectionStrings["FileData"].ConnectionString; // Save the Spreadsheet data as byte array to database. [AcceptVerbs(HttpVerbs.Post)] public void SaveFiletoDB(string fileName, string sheetModel, string sheetData) { try { if (fileName.Length > 0) { 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(connectionString); sqlCon.Open(); 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(); sqlCon.Close(); } } catch (Exception e) { //Error handling code here. } } //Open saved Spreadsheet data from database [AcceptVerbs(HttpVerbs.Post)] public string OpenFileFromDB(string filename) { ImportRequest importRequest = new ImportRequest(); SqlConnection sqlCon = new SqlConnection(connectionString); 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)); } sqlCon.Close(); return Spreadsheet.Open(importRequest); }
ASP Solution
[ASPX]
<div style="margin: 7px;"> <!-- Save Spreadsheet data to database --> <input type="text" id="saveFileName" /> <input type="button" value="Save" id="saveFileToDatabase" /> <!-- Open Spreadsheet data from database --> <asp:DropDownList ID="importFileName" runat="server" Height='30px' Width='150px' DataTextField="Text"></asp:DropDownList> <input type="button" value="Import" id="openFileFromDatabase" /> </div> <ej:Spreadsheet ID="Spreadsheet1" runat='server'> <ScrollSettings Width="100%" Height="470" IsResponsive="true" /> <Sheets> <ej:Sheet> <RangeSettings> <ej:RangeSetting StartCell="A1" ShowHeader="true" /> </RangeSettings> </ej:Sheet> </Sheets> </ej:Spreadsheet> <script> // Save the Spreadsheet data as byte array to database. $("#saveSpreadsheetToDatabase").bind("click", function () { var xlObj = $("#MainContent_Spreadsheet1").data("ejSpreadsheet"), fileName = $("#saveFileName").val(), exportProps = xlObj.XLExport.getExportProps(); $.ajax({ type: "POST", url: "SpreadsheetFeatures.aspx/SaveSpreadsheetToDB", data: JSON.stringify({ fileName: fileName, sheetModel: exportProps.model, sheetData: exportProps.data }), contentType: "application/json; charset=utf-8", dataType: 'json', success: function (data) { // Success code here. } }); }); // Open the saved Spreadsheet data from database. $("#openFileFromDatabase").bind("click", function () { var xlObj = $("#MainContent_Spreadsheet1").data("ejSpreadsheet"), fileName = $("#MainContent_ImportFileName").val(); xlObj.showWaitingPopUp(); $.ajax({ type: "POST", url: "SpreadsheetFeatures.aspx/OpenSpreadsheetFromDB", data: JSON.stringify({ filename: fileName }), contentType: "application/json; charset=utf-8", dataType: 'json', success: function (data) { xlObj.loadFromJSON(JSON.parse(data.d)); xlObj.hideWaitingPopUp(); } }); }); </script>
[C#]
string connectionString = ConfigurationManager.ConnectionStrings["FileData"].ConnectionString; //Open saved Spreadsheet data from database [WebMethod] public static string OpenSpreadsheetFromDB(string filename) { ImportRequest importRequest = new ImportRequest(); SqlConnection sqlCon = new SqlConnection(connectionString); 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)); } sqlCon.Close(); return Spreadsheet.Open(importRequest); } // Save the Spreadsheet data as byte array to database. [WebMethod] public static void SaveSpreadsheetToDB(string fileName, string sheetModel, string sheetData) { try { if (fileName.Length > 0) { MemoryStream fileStream = (MemoryStream)Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013); Byte[] dataBytes = fileStream.ToArray(); SqlConnection sqlCon = new SqlConnection(connectionString); sqlCon.Open(); 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(); sqlCon.Close(); } } catch (Exception e) { // Error handling code here. } }