Category / Section
How to open and save a Excel file to server in JavaScript Spreadsheet?
4 mins read
Description
This Knowledge Base explains the way to save the Spreadsheet data as Excel file to server and load the Excel into the Spreadsheet.
Solution
You can use “Open()” and “Save()” server methods to achieve this requirement.
JavaScript
[JS]
<!-- File name with .xlsx extension--> <input type="text" id="fileName" /> <input type="button" value="Save as Excel" onclick="saveAsExcel()" /> <input type="button" value="Load Excel" onclick="loadExcel()" /> <div id="Spreadsheet"></div> <script type="text/javascript"> $(function () { $("#Spreadsheet").ejSpreadsheet({ scrollSettings: { height: "100%", width: "100%" } }); }); // Save excel file to the server. function saveAsExcel(args) { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#fileName").val(), exportProp = xlObj.XLExport.getExportProps(); $.ajax({ type: "POST", url: "/Spreadsheet/saveAsExcel", data: { fileName: fileName, sheetModel: exportProp.model, sheetData: exportProp.data }, success: function () { // Success code here. } }); } // Load excel file from the server to the Spreadsheet. function loadExcel(args) { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#fileName").val(); $.ajax({ type: "POST", url: "/Spreadsheet/loadExcel", data: { fileName: fileName }, success: function (data) { xlObj.loadFromJSON(data); } }); } </script>
[Web API]
[OperationContract] [WebGet(BodyStyle = WebMessageBodyStyle.Bare)] [System.Web.Http.ActionName("saveAsExcel")] [AcceptVerbs("POST")] public string saveAsExcel() { string fileName = HttpContext.Current.Request.Params["FileName"]; string sheetModel = HttpContext.Current.Request.Params["sheetModel"], sheetData = HttpContext.Current.Request.Params["sheetData"]; //File Save to server here ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; try { // Convert Spreadsheet data as Stream var fileStream = Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013); fileStream.Position = 0; //Reset reader position IWorkbook workbook = application.Workbooks.Open(fileStream); var filePath = HttpContext.Current.Server.MapPath("~/Files/") + fileName; workbook.SaveAs(filePath); return "Success"; } catch(Exception ex) { return "Failure"; } } [OperationContract] [WebGet(BodyStyle = WebMessageBodyStyle.Bare)] [System.Web.Http.ActionName("loadExcel")] [AcceptVerbs("POST")] public string loadExcel() { string fileName = HttpContext.Current.Request.Params["FileName"], jsonData; try { Stream fileStream = File.Open(HttpContext.Current.Server.MapPath("~/Files/") + fileName, FileMode.Open, FileAccess.Read); ImportRequest impReq = new ImportRequest(); impReq.FileStream = fileStream; jsonData = Spreadsheet.Open(impReq); fileStream.Close(); return jsonData; } catch (Exception ex) { return "Failure"; } }
[MVC]
[CSHTML]
<div style="height:550px;"> @(Html.EJ().Spreadsheet<object>("Spreadsheet") .ScrollSettings(scroll => { scroll.Height("100%"); scroll.Width("100%"); }) ) </div> <!-- File name with .xlsx extension--> <input type="text" id="fileName" /> <input type="button" value="Save as Excel" onclick="saveAsExcel()" /> <input type="button" value="Load Excel" onclick="loadExcel()" /> <div id="Spreadsheet"></div> <script type="text/javascript"> // Save excel file to the server. function saveAsExcel(args) { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#fileName").val(), exportProp = xlObj.XLExport.getExportProps(); $.ajax({ type: "POST", url: "/Spreadsheet/saveAsExcel", data: { fileName: fileName, sheetModel: exportProp.model, sheetData: exportProp.data }, success: function () { // Success code here. } }); } // Load excel file from the server to the Spreadsheet. function loadExcel(args) { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#fileName").val(); $.ajax({ type: "POST", url: "/Spreadsheet/loadExcel", data: { fileName: fileName }, success: function (data) { xlObj.loadFromJSON(data); } }); } </script>
[CONTROLLER]
// Save the Spreadsheet data as Excel to server. [AcceptVerbs(HttpVerbs.Post)] public string saveAsExcel() { string fileName = HttpContext.Current.Request.Params["fileName"]; string sheetModel = HttpContext.Current.Request.Params["sheetModel"], sheetData = HttpContext.Current.Request.Params["sheetData"]; //File Save to server here ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; try { // Convert Spreadsheet data as Stream var fileStream = Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013); fileStream.Position = 0; //Reset reader position IWorkbook workbook = application.Workbooks.Open(fileStream); var filePath = HttpContext.Current.Server.MapPath("~/Files/") + fileName; workbook.SaveAs(filePath); return "Success"; } catch(Exception ex) { return "Failure"; } } //Open saved Excel file from server. [AcceptVerbs(HttpVerbs.Post)] public string loadExcel() { string fileName = HttpContext.Current.Request.Params["FileName"], jsonData; try { Stream fileStream = File.Open(HttpContext.Current.Server.MapPath("~/Files/") + fileName, FileMode.Open, FileAccess.Read); ImportRequest impReq = new ImportRequest(); impReq.FileStream = fileStream; jsonData = Spreadsheet.Open(impReq); fileStream.Close(); return jsonData; } catch (Exception ex) { return "Failure"; } }
ASP
[ASPX]
<ej:Spreadsheet ID="Spreadsheet" runat='server'> <ScrollSettings Width="100%" Height="100%" /> </ej:Spreadsheet> <!-- File name with .xlsx extension--> <input type="text" id="fileName" /> <input type="button" value="Save as Excel" onclick="saveAsExcel()" /> <input type="button" value="Load Excel" onclick="loadExcel()" /> <div id="Spreadsheet"></div> <script type="text/javascript"> // Save excel file to the server. function saveAsExcel(args) { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#fileName").val(), exportProp = xlObj.XLExport.getExportProps(); $.ajax({ type: "POST", url: "/Spreadsheet/saveAsExcel", data: JSON.stringify({ fileName: fileName, sheetModel: exportProp.model, sheetData: exportProp.data }), contentType: "application/json; charset=utf-8", dataType: 'json', success: function () { // Your code here. } }); } // Load excel file from the server. function loadExcel(args) { var xlObj = $("#Spreadsheet").data("ejSpreadsheet"), fileName = $("#fileName").val(); $.ajax({ type: "POST", url: "/Spreadsheet/loadExcel", data: JSON.stringify({ fileName: fileName }), contentType: "application/json; charset=utf-8", dataType: 'json', success: function (data) { xlObj.loadFromJSON(data); } }); } </script>
[CS]
// Save the Spreadsheet data as Excel to server. [WebMethod] public static string saveAsExcel() { string fileName = HttpContext.Current.Request.Params["FileName"]; string sheetModel = HttpContext.Current.Request.Params["sheetModel"], sheetData = HttpContext.Current.Request.Params["sheetData"]; //File Save to server here. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; try { // Convert Spreadsheet data as Stream. var fileStream = Spreadsheet.Save(sheetModel, sheetData, ExportFormat.XLSX, ExcelVersion.Excel2013); fileStream.Position = 0; //Reset reader position IWorkbook workbook = application.Workbooks.Open(fileStream); var filePath = HttpContext.Current.Server.MapPath("~/Files/") + fileName; workbook.SaveAs(filePath); return "Success"; } catch(Exception ex) { return "Failure"; } } //Open saved Excel file from server. [WebMethod] public static string loadExcel() { string fileName = HttpContext.Current.Request.Params["FileName"], jsonData; try { Stream fileStream = File.Open(HttpContext.Current.Server.MapPath("~/Files/") + fileName, FileMode.Open, FileAccess.Read); ImportRequest impReq = new ImportRequest(); impReq.FileStream = fileStream; jsonData = Spreadsheet.Open(impReq); fileStream.Close(); return jsonData; } catch (Exception ex) { return "Failure"; } }