c# API Export Excel (ClosedXML) and Download in Angular 6

Question

I made a post from Angular to an C# Api with success passaing and mount an Excel with (ClosedXML).

I can format and save in a folder my finished Excel File as I wish. But I need to return this Excel to Angular as binary or similar as download event in an Angular 6 button in page.

I know the error was in the final lines of code because I have and I could save my excel file locally if I wished,

The end of C# API Method:

[HttpPost]
        [Route("api/qqq/generateExcel")]
        public Task<HttpResponseMessage> Post(List<MyObject> list)
        {
        ...
var localWb = new XLWorkbook();

            localWb = arquivo.GerarExcel(listaRegras); 

            //----------------------
            var filePath = "C:\\Users\\folder";
            var fileName = "Export.xlsx"; 

            using (MemoryStream ms = new MemoryStream())
            {
                localWb.SaveAs(ms);
                using (FileStream file = new FileStream(filePath + "\\Export.xlsx", FileMode.Open, FileAccess.Read))
                {
                    HttpResponseMessage httpResponseMessage = new HttpResponseMessage();
                    httpResponseMessage.Content = new ByteArrayContent(ms.GetBuffer());
                    httpResponseMessage.Content.Headers.Add("x-filename", fileName);
                    httpResponseMessage.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                    httpResponseMessage.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                    httpResponseMessage.Content.Headers.ContentDisposition.FileName = fileName;
                    httpResponseMessage.Content.Headers.ContentLength = file.Length;
                    httpResponseMessage.StatusCode = HttpStatusCode.OK;
                   // return httpResponseMessage;
                    return CreateResponse(HttpStatusCode.OK, httpResponseMessage);
                }    
            }

My angular service:

gerarExcel(listaXYZ) {
    return this.http.post(this.apiUrl, listaXYZ);
  }

** component**

exportToExcel(event): void {

    let lista= this.regras;
    let configs = '{ "config1": "valorConfig1", "config2": "valorConfig2" }';
    console.log(listaXYZ);
    //console.log(lista.length);

    if (lista != null) {
      this.myservice
        .gerarExcel(listaXYZ)
        .subscribe(data => {

          this.downloadFile(data)
          console.log(<any>data);

        },
          (error: HttpErrorResponse) => {
           ...
          },
          () => {
            ...
          }
        );
    }
    else {
      ...
    }


downloadFile(data: IDownloadArquivo) {
  var url = window.URL.createObjectURL(data.blob);

  let a = document.createElement("a");
  document.body.appendChild(a);

  a.style.display = "none";
  a.href = url;
  a.target = "_blank";
  a.download = data.fileName;
  a.click();

  a.remove();
}

How can I do it, nothing happing ???

score 0 · Accepted Answer · answered Aug 30 '18 at 03:56

After a lot of researches and no answers i found the solution. I change CloseXML to OfficeOpenXml because this last one can be inserted so easy on Response content using Content = new ByteArrayContent(p) (see above in API Method)

It was wonderful because OfficeOpenXml can make a stylish workbook as you wish!!!

The end of C# API Method: The return must be public HttpResponseMessage (without TASK)

public HttpResponseMessage GenerateMyExcel(List<someModel> mylist)
{
...
return httpResponseMessage;
...}

Exemple:

     [HttpPost]
            [Route("api/someModel/generateMyExcel")]
             public HttpResponseMessage GenerateMyExcel(List<someModel> mylist)
            { 
                var listSomeModelJson  = JsonConvert.SerializeObject(mylist);

var MyExcelObjectToMakeAWorkbook = new MyExcelObjectToMakeAWorkbook();

                using (var package = MyExcelObjectToMakeAWorkbook .GenerateAnWorkbook(mylist)
                {
                    var p = package.GetAsByteArray();

                    HttpResponseMessage httpResponseMessage = new HttpResponseMessage();

                    httpResponseMessage = new HttpResponseMessage
                    {
                        StatusCode = HttpStatusCode.OK,
                        Content = new ByteArrayContent(p)
                    };
                    httpResponseMessage.Content.Headers.Add("x-filename", "name.xlsx");
                    //.XLSX
                    httpResponseMessage.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
                    httpResponseMessage.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                    httpResponseMessage.Content.Headers.ContentDisposition.FileName = "nome.xlsx";
                    httpResponseMessage.Content.Headers.ContentLength = p.Length;
                    httpResponseMessage.StatusCode = HttpStatusCode.OK;
                    return httpResponseMessage;
                }
            }

The component

...

    this.myModelService
          .generateExcelByPost(this.MyObjectList)
          .subscribe(
            (data) => {
              var file = new Blob([data], { type: 'application/vnd.ms-excel' });
              var fileURL = URL.createObjectURL(file);
              let a = document.createElement("a");
              document.body.appendChild(a);
              a.style.display = "none";
              a.href = fileURL;
              a.target = "_blank";
              a.download = "ListOfSomeModel.xlsx";
              a.click();
              a.remove();
            }
          )
...

The Service

generateExcelByPost(list: any) {
    let headers = new HttpHeaders();
    headers.set('Content-Type', 'application/json');
    return this.http.post(API_Url,list,
      {
        headers: headers,
        responseType: "arraybuffer"
      }
    )
  }

c# API Export Excel (ClosedXML) and Download in Angular 6

1 Answers1