1

I'm having a trouble with generating a .xlsx file from a template which is placed in my project resources. First I'm creating a new ExcelPackage, then opening the template which I'm going to fill with some data - but by now I want to simply download the original xlsx template. I'm trying to get the template without any modifications and download as 'Test.xlsx'. When it's downloaded, after opening the file Excel says that it cannot open my file due to invalid format or extension. Does anyone know what I am doing wrong? When I console.log my response.data then evidently there is some binary data ( about 50kb), but excel cannot show this data properly. Here is some code:

Code from .cshtml :

generateReport(){
 this.@http.post(SERVER_URL + "/Mvc/Excel/GenerateReport")
      .then(response => {
          var blob = new Blob([response.data], { type: 'application/ms-excel' });
          var downloadUrl = URL.createObjectURL(blob);
          var a = document.createElement('a');
          a.href = downloadUrl;
          a.download = "Test.xlsx";
          document.body.appendChild(a);
          a.click();
       });
       .catch(error => {
         console.log('error ', error);
         return null;
       });
}

Here is my controller action:

[HttpPost]
public void GenerateReport(){
   string fileName;
   string strfilepath = TemplatePath + "MyTemplate.xlsx"; 
      //TemplatePath - path from my configuration where my xlsx template is stored

   using(ExcelPackage p = new ExcelPackage())
    {
       using(FileStream = new FileStream(strfilepath, FileMode.Open))  
         { 
           p.Load(stream);
           fileName = "TestReport.xlsx";
           Byte[] bin = p.GetAsByteArray();
           
           Response.ClearContent();
           Response.Buffer = true;
           Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
           Response.ContentType = "application/ms-excel";
           Response.Charset = "";
           Response.BinaryWrite(bin);
           Response.Flush();
           Response.End();
         }
    } 
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shadov
  • 33
  • 5
  • `due to invalid format or extension` - have you verified that you are producing an xlsx? Does the file open if you rename it to xls? – GSerg Jun 24 '21 at 14:35
  • @GSerg if I change format to xls the waring still exists when Im trying to open file. Fortunately, it finally opens but I can see only raw binary data in my excel... – Shadov Jun 24 '21 at 14:51
  • 2
    If you're in an MVC action method, you should not be directly manipulating HTTP responses. Action methods should return an action result. In this case, there are lots of file results that allow you to return a binary file. You should use one of those. – mason Jun 24 '21 at 15:16
  • 2
    Why are you using `application/ms-excel` as the MIME type for a .xlsx file? [The correct MIME type is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet](https://stackoverflow.com/questions/4212861/what-is-a-correct-mime-type-for-docx-pptx-etc) – mason Jun 24 '21 at 15:19
  • @mason so you recommend changing the method to return FileResult for example and then return File type variable with my binary data passed? And then in JavaScript use the same response.data ? – Shadov Jun 24 '21 at 15:20
  • That's what I said! – mason Jun 24 '21 at 15:21
  • @mason okay so I will try this out and also I will change MIME type and will let you know – Shadov Jun 24 '21 at 15:24
  • 2
    If I were you, I would hit the URL responsible for generating the file directly. Verify that works through your browser, with no JavaScript. Once you've verified that's all working correctly *then* you know the issue is with the JavaScript and you can troubleshoot that area further. – mason Jun 24 '21 at 15:36
  • 1
    After reading an article [How to export data to Excel in ASP.NET Core 3.0](https://www.infoworld.com/article/3538413/how-to-export-data-to-excel-in-aspnet-core-30.html), I create a project and push to [github](https://github.com/ChrisWong979/ExportExcelDemo). Try and modify it to accommodate your requirement. – Chris Wong Jun 25 '21 at 04:00
  • @mason I solved the problem by passing additionally in $http.post : responseType: 'arraybuffer' and now it opens properly – Shadov Jun 25 '21 at 11:53

1 Answers1

-1

I think the content type should be application/vnd.ms-excel for exporting excel files.

ShakilSunmoon
  • 39
  • 6