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