Category / Section
How to save and retrieve the JavaScript Spreadsheet data as JSON in database?
4 mins read
Description
This knowledge base explains in detail on how to save and retrieve the JavaScript Spreadsheet data as JSON in Database.
Solution
You can use “openFromJson” and “saveAsJson” client side methods to achieve this requirement. If you save the spreadsheet data as a JSON to database, you need to send the file name and json data from client side to server side. If you open the JSON in spreadsheet, you need to send file name from client side to server side.
JavaScript Solution
[JS]
<button id="LoadFromDataBase" class="e-btn">LoadFromDataBase</button>
<button id="saveToServer" class="e-btn">saveToServer</button>
<label>File name:</label>
<input type="text" id="filename" value="Sample" placeholder="Specify file name">
<div id="spreadsheet"></div>
<script>
let spreadsheet: Spreadsheet = new Spreadsheet({
sheets: [
{
name: 'Car Sales Report',
ranges: [{ dataSource: (dataSource as any).defaultData }], // you can refer this datasource in this link https://ej2.syncfusion.com/javascript/demos/spreadsheet/default/datasource.js
rows: [
{
index: 30,
cells: [
{ index: 4, value: 'Total Amount:', style: { fontWeight: 'bold', textAlign: 'right' } },
{ formula: '=SUM(F2:F30)', style: { fontWeight: 'bold' } },
]
}],
columns: [
{ width: 180 }, { width: 130 }, { width: 130 }, { width: 180 },
{ width: 130 }, { width: 120 }
]
}],
openUrl: '/Home/LoadFromDataBase',
saveUrl: '/Home/SaveFiletoDB'
});
//Render initialized Spreadsheet component
spreadsheet.appendTo('#spreadsheet');
document.getElementById('LoadFromDataBase').onclick = (): void => {
fetch('/Home/LoadFromDataBase', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ Name: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
spreadsheet.openFromJson({ file: data }); // convert the json data to file and loaded into spreadsheet
})
}
document.getElementById('saveToServer').onclick = (): void => {
spreadsheet.saveAsJson().then(Json => (fetch('/Home/SaveFiletoDB', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ Name: document.getElementById("filename").value, JSONData: JSON.stringify(Json.jsonObject), ContentType: "Xlsx", VersionType: "Xlsx" }), // send the filename, json data, content type, version type from client to server
})
.then((response) => response.json())
.then((data) => {
console.log(data);
})))
}
</script>
MVC Solution
[CSHTML]
<button id="LoadFromDataBase" class="e-btn"> LoadFromDataBase </button>
<button id="saveToServer" class="e-btn"> saveToServer</button>
<label>File name:</label>
<input type="text" id="filename" value="Sample" placeholder="Specify file name">
@Html.EJS().Spreadsheet("spreadsheet").OpenUrl("Home/LoadFromDataBase").SaveUrl("Home/SaveFiletoDB").Render()
<script>
document.getElementById("saveToServer").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
spreadsheetObj.saveAsJson().then(Json => (fetch('/Home/SaveFiletoDB', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ Name: document.getElementById("filename").value, JSONData: JSON.stringify(Json.jsonObject), ContentType: "Xlsx", VersionType: "Xlsx" }), // send the filename, json data, content type, version type from client to server
})
.then((response) => response.json())
.then((data) => {
console.log(data);
})))
}
document.getElementById("LoadFromDataBase").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
fetch('/Home/LoadFromDataBase', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ name: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
spreadsheetObj.openFromJson({ file: data }); // convert the json data to file and loaded into spreadsheet
})
}
</script>
[Controller]
// Save the Spreadsheet data as Json data to database.
public IActionResult SaveFiletoDB([FromBody]SaveOptions saveSettings)
{
//Save JSON to database
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[SpreadsheetData]([filename], [data]) VALUES (@filename, @data)", sqlCon);
sqlComm.Parameters.AddWithValue("@filename", saveSettings.Name);
sqlComm.Parameters.AddWithValue("@data", saveSettings.JSONData);
sqlComm.ExecuteNonQuery();
sqlCon.Close(); }
public string LoadFromDataBase([FromBody]FileOptions file)
{
OpenRequest open = new OpenRequest();
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
//Load JSON from database
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[SpreadsheetData] WHERE [filename] = '" + file.Name + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
string JSONData = "";
while (sqlDR.Read())
{
JSONData = sqlDR.GetValue(1).ToString();
open.File = JSONData;
}
sqlCon.Close();
string data = JSONData;
return data; // returning JSON data to spreadsheet control
}
public class FileOptions
{
public string Name { get; set; }
}
public class SaveOptions
{
public string Name { get; set; }
public object JSONData { get; set; }
}
Core Solution
[CSHTML]
<button id="LoadFromDataBase" class="e-btn">LoadFromDataBase</button>
<button id="saveToServer" class="e-btn">saveToServer</button>
<label>File name:</label>
<input type="text" id="filename" value="Sample" placeholder="Specify file name">
<ejs-spreadsheet id="spreadsheet" openUrl="Home/LoadFromDataBase" saveUrl="Home/SaveFiletoDB">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Car Sales Report">
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
document.getElementById("saveToServer").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
spreadsheetObj.saveAsJson().then(Json => (fetch('/Home/SaveFiletoDB', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ Name: document.getElementById("filename").value, JSONData: JSON.stringify(Json.jsonObject), ContentType: "Xlsx", VersionType: "Xlsx" }), // send the filename, json data, content type, version type from client to server
})
.then((response) => response.json())
.then((data) => {
console.log(data);
})))
}
document.getElementById("LoadFromDataBase").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
fetch('/Home/LoadFromDataBase', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ name: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
spreadsheetObj.openFromJson({ file: data }); // convert the json data to file and loaded into spreadsheet
})
}
</script>
[Controller]
public HomeController(IConfiguration configuration)
{
_configuration = configuration;
connectionString = _configuration.GetConnectionString("DefaultConnection");
}
public IActionResult SaveFiletoDB([FromBody]SaveOptions saveSettings)
{
//Save JSON to database
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[SpreadsheetData]([filename], [data]) VALUES (@filename, @data)", sqlCon);
sqlComm.Parameters.AddWithValue("@filename", saveSettings.Name);
sqlComm.Parameters.AddWithValue("@data", saveSettings.JSONData);
sqlComm.ExecuteNonQuery();
sqlCon.Close();
return Content("Saved in Server");
}
public string LoadFromDataBase([FromBody]FileOptions file)
{
OpenRequest open = new OpenRequest();
//Load JSON from database
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[SpreadsheetData] WHERE [filename] = '" + file.Name + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
string JSONData = "";
while (sqlDR.Read())
{
JSONData = sqlDR.GetValue(1).ToString();
open.File = JSONData;
}
sqlCon.Close();
string data = JSONData;
return data; // returning JSON data to spreadsheet control
}
public class FileOptions
{
public string Name { get; set; }
}
public class SaveOptions
{
public string Name { get; set; }
public object JSONData { get; set; }
}
Screenshot:
Also please refer the below UG Documentation link,
https://ej2.syncfusion.com/documentation/api/spreadsheet/#openfromjson
https://ej2.syncfusion.com/documentation/api/spreadsheet/#saveasjson
Conclusion
I hope you enjoyed learning about how to save and retrieve the JavaScript Spreadsheet data as JSON in database.
You can refer to our JavaScript Spreadsheet feature tour page to know about its other groundbreaking feature representations and documentation, and how to quickly get started for configuration specifications. You can also explore our JavaScript Spreadsheet example to understand how to create and manipulate data.
For current customers, you can check out our components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our other controls.
If you have any queries or require clarifications, please let us know in the comments section below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!
