Category / Section
How to open and save an Excel file to server in Spreadsheet?
Description
This article explains how to save the JavaScript Spreadsheet data as an Excel file to the server and load the Excel file into the Spreadsheet.
Solution
You can use “openFromJson” and “saveAsJson” client-side methods to achieve this requirement. If you save the spreadsheet as an Excel file to the server, you need to send the file name, JSON data, content type, and version type from the client to the server. If you open the Excel in the spreadsheet, you need to send the file name from the client to the server.
JavaScript Solution
[JS]
<label>File name:</label>
<input type="text" id="filename" value="Sample" placeholder="Specify file name">
<button id="loadExcel" class="e-btn">Load Excel</button>
<button id="saveExcel" class="e-btn">Save Excel</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/LoadExcel',
saveUrl: '/Home/SaveExcel'
});
//Render initialized Spreadsheet component
spreadsheet.appendTo('#spreadsheet');
document.getElementById('loadExcel').onclick = (): void => {
fetch('/Home/LoadExcel', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ FileName: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
console.log(data);
spreadsheet.openFromJson({ file: data });
})
}
document.getElementById('saveExcel').onclick = (): void => {
spreadsheet.saveAsJson().then((Json) =>
fetch("/Home/SaveExcel", {
method: 'POST', // or 'PUT'
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="loadExcel" class="e-btn">Load Excel</button>
<button id="saveExcel" class="e-btn">Save as Excel</button>
@Html.EJS().Spreadsheet("spreadsheet").Render()
<script>
document.getElementById("saveExcel").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
spreadsheetObj.saveAsJson().then((Json) =>
fetch("/Home/SaveExcel", {
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("loadExcel").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
fetch('/Home/LoadExcel, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ FileName: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
console.log(data);
spreadsheetObj.openFromJson({ file: data });
})
}
</script>
[Controller]
public string LoadExcel(FileOptions file)
{
ExcelEngine excelEngine = new ExcelEngine();
IWorkbook workbook;
FileStream fs = System.IO.File.Open(HttpContext.Server.MapPath("~/Files/") + file.FileName + ".xlsx", FileMode.Open); // converting excel file to stream
workbook = excelEngine.Excel.Workbooks.Open(fs, ExcelOpenType.Automatic);
MemoryStream outputStream = new MemoryStream();
workbook.SaveAs(outputStream);
HttpPostedFileBase fileBase = (HttpPostedFileBase)new HttpPostedFile(outputStream.ToArray(), file.FileName + ".xlsx");
HttpPostedFileBase[] files = new HttpPostedFileBase[1];
files[0] = fileBase;
OpenRequest open = new OpenRequest();
open.File = files;
fs.Close();
return Workbook.Open(open);
}
public string SaveExcel(SaveSettings saveSettings)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
try
{
// Convert Spreadsheet data as Stream
string basePath = HttpContext.Server.MapPath("~/Files/" + saveSettings.FileName + ".xlsx");
Stream fileStream = Workbook.Save<Stream>(saveSettings);
IWorkbook workbook = application.Workbooks.Open(fileStream);
var file = System.IO.File.Create(basePath);
fileStream.Seek(0, SeekOrigin.Begin);
fileStream.CopyTo(file); // to convert the stream to file options
file.Dispose();
fileStream.Dispose();
return "";
}
catch (Exception ex)
{
return "Failure";
}
}
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="loadExcel" class="e-btn">Load Excel</button>
<button id="saveExcel" class="e-btn">Save as Excel</button>
<ejs-spreadsheet id="spreadsheet">
<e-spreadsheet-sheets>
<e-spreadsheet-sheet name="Car Sales Report">
</e-spreadsheet-sheet>
</e-spreadsheet-sheets>
</ejs-spreadsheet>
<script>
document.getElementById("saveExcel").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
spreadsheetObj.saveAsJson().then((Json) =>
fetch("http://localhost:49371/Spreadsheet/Save", {
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("loadExcel").onclick = function () {
var spreadsheetObj = ej.base.getComponent(document.getElementById('spreadsheet'), 'spreadsheet');
fetch('http://localhost:49371/Spreadsheet/Open', {
method: 'POST',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ FileName: (document.getElementById("filename")).value }),
})
.then((response) => response.json())
.then((data) => {
console.log(data);
spreadsheetObj.openFromJson({ file: data });
})
}
</script>
[Controller]
public IActionResult LoadExcel([FromBody]FileOptions file)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
OpenRequest open = new OpenRequest();
string filePath = Startup._env.ContentRootPath.ToString() + "\\Files\\" + file.Name + ".xlsx";
FileStream inputStream1 = new FileStream(filePath, FileMode.Open);
IFormFile formFile = new FormFile(inputStream1, 0, inputStream1.Length, "", file.Name + ".xlsx"); // converting MemoryStream to IFormFile
open.File = formFile;
var content = Workbook.Open(open);
inputStream1.Close();
return Content(content);
}
public string SaveExcel([FromBody] SaveSettings saveSettings)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
try
{
// Convert Spreadsheet data as Stream
string basePath = Startup._env.ContentRootPath.ToString() + "\\Files\\" + saveSettings.FileName + ".xlsx";
Stream fileStream = Workbook.Save<Stream>(saveSettings);
IWorkbook workbook = application.Workbooks.Open(fileStream);
var file = System.IO.File.Create(basePath);
fileStream.Seek(0, SeekOrigin.Begin);
fileStream.CopyTo(file); // to convert the stream to file options
file.Dispose();
fileStream.Dispose();
return basePath;
}
catch (Exception ex)
{
return "Failure";
}
}
public class FileOptions
{
public string Name { get; set; }
}
Screenshot:
JavaScript Solution
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
We hope you enjoyed learning about how to open and save a excel file to server in the Spreadsheet in JavaScript.
You can refer to our JavaScript Spreadsheet feature tour page to know about its other groundbreaking feature representations 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, BoldDesk Support, or feedback portal. We are always happy to assist you!
