Articles in this section
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.
            }
        } 
 

 

 

 

 

 

Did you find this information helpful?
Yes
No
Help us improve this page
Please provide feedback or comments
Comments
Please  to leave a comment
Access denied
Access denied