Category / Section
How to save and retrieve JavaScript Spreadsheet data as a byte array in the Database?
8 mins read
Description
This Knowledge Base explains the way to save and retrieve the JavaScript Spreadsheet data as a byte array in Database.
Solution
You can use “openFromJson” and “saveAsJson” client side methods to achieve this requirement. If you save the spreadsheet data as a byte array to server, you need to send the file name, json data, content type, version type from client to server. If you load the byte array in spreadsheet, you need to send file name from client to server.
JavaScript Solution
[JS]
<label>File name:</label>
<input type="text" id="filename" value="Sample" placeholder="Specify file name">
<button id="LoadFromDataBase" class="e-btn">LoadFromDataBase</button>
<button id="saveToServer" class="e-btn">SaveToServer</button>
<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', // or 'PUT'
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ FileName: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
console.log(data);
})
}
document.getElementById('saveToServer').onclick = (): void => {
spreadsheet.saveAsJson().then((Json) =>
fetch("/Home/SaveFiletoDB", {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({
FileName: document.getElementById("filename").value,
JSONData: JSON.stringify(Json.jsonObject.Workbook),
ContentType: "Xlsx",
VersionType: "Xlsx",
PDFLayoutSettings: JSON.stringify({ FitSheetOnOnePage: false }),
})
})
.then((basePath) => {
console.log("file saved");
})
);
}
</script>
MVC Solution
[CSHTML]
<label>File name:</label>
<input type="text" id="filename" value="Sample" placeholder="Specify file name">
<button id="LoadFromDataBase" class="e-btn">LoadFromDataBase</button>
<button id="saveToServer" class="e-btn">saveToServer</button>
@Html.EJS().Spreadsheet("spreadsheet").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({
FileName: document.getElementById("filename").value,
JSONData: JSON.stringify(Json.jsonObject.Workbook),
ContentType: "Xlsx",
VersionType: "Xlsx",
PDFLayoutSettings: JSON.stringify({ FitSheetOnOnePage: false }),
})
})
.then((basePath) => {
console.log("file saved");
})
);
}
document.getElementById("LoadFromDataBase").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
fetch('/Home/LoadFromDataBase', {
method: 'POST', // or 'PUT'
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ FileName: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
console.log(data);
})
}
</script>
[Controller]
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
//Open saved Spreadsheet data from database
public ActionResult LoadFromDataBase(FileOptions file)
{
//Load file from database
OpenRequest openRequest = new OpenRequest();
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table1] WHERE [filename] = '" + file.FileName + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
HttpPostedFileBase objFile = (HttpPostedFileBase)new HttpPostedFile((byte[])sqlDR.GetValue(1), file.FileName);
HttpPostedFileBase[] theFiles = new HttpPostedFileBase[1];
theFiles[0] = objFile;
openRequest.File = theFiles;
}
sqlCon.Close();
return Content(Workbook.Open(openRequest));
}
// Save the Spreadsheet data as byte array to database.
public string SaveFiletoDB(SaveSettings saveSettings)
{
//save the file to database
Stream dataStream = Workbook.Save<Stream>(saveSettings);
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].[Table1]([filename], [databytes]) VALUES (@filename, @databytes)", sqlCon);
sqlComm.Parameters.AddWithValue("@filename", saveSettings.FileName);
sqlComm.Parameters.AddWithValue("@databytes", dataBytes);
sqlComm.ExecuteNonQuery();
sqlCon.Close();
return saveSettings.JSONData;
}
public class FileOptions
{
public string FileName { get; set; }
}
public class HttpPostedFile : HttpPostedFileBase
{
private readonly byte[] fileBytes;
public HttpPostedFile(byte[] fileBytes, string fileName)
{
this.fileBytes = fileBytes;
this.InputStream = new MemoryStream(fileBytes);
this.FileName = fileName + ".xlsx";
}
public override int ContentLength => fileBytes.Length;
public override string FileName { get; }
public override Stream InputStream { get; }
}
Core Solution
[CSHTML]
<label>File name:</label>
<input type="text" id="filename" value="Sample" placeholder="Specify file name">
<button id="LoadFromDataBase" class="e-btn">LoadFromDataBase</button>
<button id="saveToServer" class="e-btn">saveToServer</button>
<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({
FileName: document.getElementById("filename").value,
JSONData: JSON.stringify(Json.jsonObject.Workbook),
ContentType: "Xlsx",
VersionType: "Xlsx",
PDFLayoutSettings: JSON.stringify({ FitSheetOnOnePage: false }),
})
})
.then((basePath) => {
console.log("file saved");
})
);
}
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({ FileName: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
spreadsheetObj.openFromJson({ file: data });
})
}
</script>
[Controller]
string connectionString = _configuration.GetConnectionString("DefaultConnection");
//Open saved Spreadsheet data from database
public IActionResult LoadFromDataBase([FromBody]FileOptions file)
{
// Convert Spreadsheet data as Stream.
OpenRequest open = new OpenRequest();
MemoryStream dataStream = new MemoryStream();
SqlConnection sqlCon = new SqlConnection(connectionString);
SqlCommand sqlComm = new SqlCommand("SELECT * FROM [dbo].[Table1] WHERE [filename] = '" + file.FileName + "'", sqlCon);
sqlCon.Open();
SqlDataReader sqlDR = sqlComm.ExecuteReader();
while (sqlDR.Read())
{
dataStream = new MemoryStream((byte[])sqlDR.GetValue(1));
IFormFile formFile = new FormFile(dataStream, 0, dataStream.Length, "", file.FileName + ".xlsx"); // converting MemoryStream to IFormFile
open.File = formFile;
}
sqlCon.Close();
return Content(Workbook.Open(open));
}
// Save the Spreadsheet data as byte array to database.
public IActionResult SaveFiletoDB([FromBody]SaveSettings saveSettings)
{
//save the file to database
MemoryStream dataStream = Workbook.Save<MemoryStream>(saveSettings);
byte[] dataBytes = new BinaryReader(dataStream).ReadBytes(Convert.ToInt32(dataStream.Length));
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
SqlCommand sqlComm = new SqlCommand("INSERT INTO [dbo].[Table1]([filename], [databytes]) VALUES (@filename, @databytes)", sqlCon);
sqlComm.Parameters.AddWithValue("@fileName", saveSettings.FileName);
sqlComm.Parameters.AddWithValue("@dataBytes", dataBytes);
sqlComm.ExecuteNonQuery();
sqlCon.Close();
return Content("Saved in Server");
}
public class FileOptions
{
public string Name { 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 JavaScript Spreadsheet data as a byte array in the 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!
