1. Home
  2. Forum
  3. ASP.NET Core - EJ 2
  4. Opening an Excel Spreadsheet received as JSON from the front-end on ASP .Net Core Web API

Opening an Excel Spreadsheet received as JSON from the front-end on ASP .Net Core Web API

We have a web application with front-end in Angular 9 and backend (API) in ASP .Net Core 5.
I am writing the backend and we have outsourced the front-end development.
I have successfully implemented the backend code to send an Excel Spreadsheet (stored in Azure Storage) to the front-end.
I am now trying to do the reverse - have the API receive an edited Excel Spreadsheet from the front-end.
The front-end developer is using the XlsIO component, and when the user clicks a SAVE button, he says he will be sending me the data in JSON, and sent me this screenshot:



I've looked at numerous examples on how to open Excel Spreadsheets in .Net Core, but they all involve opening a file on a drive.
Actually, I don't even need to open the file - I just need to save it to disk.
Is there a way to save the JSON representation of the Excel spreadsheet that front-end is saving me as an XLSX file?
Could anyone perhaps point me in the right direction on how to open the .XLSX file when it is sent to me in JSON format as in the screenshot above?
I've been struggling with this for two days now. :/

Thank you.

4 Replies 1 reply marked as answer
GK Gayathri KarunaiAnandam Syncfusion Team April 2, 2021 01:47 PM UTC

Hi Fabricio Rodriguez, 

We have checked your reported requirement. We can achieve your requirement using saveAsJson client-side method. To save the excel file we have used ASP.NET workbook server-side action (Save). For more details regarding your requirement. Please refer the below link. 


In the above link, we have done the open/save functionality in a button click event. Please check the above link and get back to us, if you need any further assistance on this. 

Regards, 
Gayathri K 


FR Fabricio Rodriguez April 6, 2021 11:31 AM UTC

Thank you so much for your support. I have implemented the code but I get a NullReferenceException error on this line:

    Stream fileStream = Workbook.Save<Stream>(saveSettings);

Every property in saveSettings has a value, so I can only think that it must be a null somewhere in saveSettings.JSONData?

This is the JSONData that the front-end is sending me:

 

{"Workbook":{"enableRtl":false,"locale":"en-US","openUrl":"https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/open","saveUrl":"https://ej2services.syncfusion.com/production/web-services/api/spreadsheet/save","enablePersistence":false,"cellStyle":{"fontFamily":"Calibri","verticalAlign":"bottom","textIndent":"0pt","backgroundColor":"#ffffff","color":"#000000","textAlign":"left","fontSize":"11pt","fontWeight":"normal","fontStyle":"normal","textDecoration":"none","border":"","borderLeft":"","borderTop":"","borderRight":"","borderBottom":""},"showRibbon":true,"showFormulaBar":true,"showSheetTabs":true,"allowEditing":true,"allowOpen":true,"allowSave":true,"enableContextMenu":true,"selectionSettings":{"mode":"Multiple"},"enableKeyboardNavigation":true,"allowNumberFormatting":true,"enableKeyboardShortcut":true,"enableClipboard":true,"allowCellFormatting":true,"allowSorting":true,"allowResizing":true,"allowHyperlink":true,"allowUndoRedo":true,"allowFiltering":true,"allowWrap":true,"allowInsert":true,"allowDelete":true,"allowDataValidation":true,"allowFindAndReplace":true,"allowMerge":true,"allowConditionalFormat":true,"allowImage":true,"allowChart":true,"activeSheetIndex":0,"cssClass":"","height":"100%","width":"100%","allowScrolling":true,"scrollSettings":{"enableVirtualization":true,"isFinite":false},"definedNames":[],"sheets":[{"columns":[{},{}],"name":"Sheet1","rows":[{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"name"},{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"age"}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"a"},{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"10"}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}},{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"b"},{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"20"}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}},{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"dc"},{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"30"}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"},"value":"apurba"},{"isLocked":true,"style":{"verticalAlign":"Middle"}}]}],"selectedRange":"B7:B7","usedRange":{"rowIndex":6,"colIndex":1},"rowCount":100,"colCount":100,"topLeftCell":"A1","activeCell":"B7","showHeaders":true,"showGridLines":true,"state":"Visible","protectSettings":{},"isProtected":false,"index":0,"id":1},{"columns":[{}],"isProtected":true,"name":"Evaluation Warning","protectSettings":{"selectCells":true,"formatCells":false,"insertLink":false},"rows":[{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]},{"cells":[{"isLocked":true,"style":{"fontSize":"14pt","fontWeight":"Bold","verticalAlign":"Middle"},"value":"Created with a trial version of Syncfusion Essential XlsIO"}],"height":33},{"cells":[{"isLocked":true,"style":{"verticalAlign":"Middle"}}]}],"selectedRange":"A1:A1","usedRange":{"rowIndex":10,"colIndex":0},"rowCount":100,"colCount":100,"topLeftCell":"A1","activeCell":"A1","showHeaders":true,"showGridLines":true,"state":"Visible","index":0,"id":2}]}}

 

Any ideas?

Thank you!

Fabricio Rodriguez


PG Pon Geetha A J Syncfusion Team April 6, 2021 12:56 PM UTC

To: Syncfusion Support <[email protected]>
Subject: RE: Syncfusion support community forum 164116, Opening an Excel Spreadsheet received as JSON from the front-end on ASP .Net Core Web API , has been updated. 
Thank you so much for the support! 
I have a quick question: in the .Net Core sample, you have the following: 
 
  1. public string SaveExcel([FromBody] SaveSettings saveSettings)
  2.         {
  3.             ExcelEngine excelEngine = new ExcelEngine();
  4.             IApplication application = excelEngine.Excel;
  5.             try
  6.             {
  7.                 // Convert Spreadsheet data as Stream
  8.                 string basePath = Startup._env.ContentRootPath.ToString() + \\Files\\ + saveSettings.FileName + ".xlsx";
  9.                 Stream fileStream = Workbook.Save<Stream>(saveSettings);
  10.                 IWorkbook workbook = application.Workbooks.Open(fileStream);
  11.                 var file = System.IO.File.Create(basePath);
  12.                 fileStream.Seek(0, SeekOrigin.Begin);
  13.                 fileStream.CopyTo(file); // to convert the stream to file options
  14.                 file.Dispose();
  15.                 fileStream.Dispose();
  16.                 return basePath;
  17.             }
  18.             catch (Exception ex)
  19.             {
  20.                 return "Failure";
  21.             }
  22.         }
Could you please provide the definitions for “SaveSettings” and “Workbook”? 
 
 


SP Sangeetha Priya Murugan Syncfusion Team April 7, 2021 09:25 AM UTC

Hi Fabricio, 
 
Thank you for your update. 
 
We have checked your reported issue based on your provided details and the JSON saved properly in our end. For your convenience, we have prepared the sample that loads your provided JSON in the created event and saved the file in the server as like as below 
 
created() { 
    var parsedData = JSON.parse(this.response); // Parse the stringified response 
    this.spreadsheetObj.openFromJson({ 
      file: parsedData 
    });  
  } 
  beforeSave(args: beforeSaveEventArgs) { 
    args.isFullPost = false; 
  } 
 
[HttpPost("Save")] 
        [Microsoft.AspNetCore.Cors.EnableCors("MyPolicy")] 
        public string Save([FromForm] 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"; 
            } 
        } 
 
 
 
Note: launch the service first and then open the stackblitz sample for import and export.  
 
Could you please check the above links and get back to us, if you need any further assistance on this. 
 
Regards, 
Sangeetha M 



Marked as answer
Loader.
Up arrow icon