preface

This article is based on Angular V7.2.7 and was first written on April 17.

The code is based on Angular 7.2.7, but the syntax is based on Angular 4.x and above. In the process of project development, we often need to interact with the back end of files, such as picture uploading, Excel import and export, etc. Here we only discuss import and export of Excel.


Excel import

Excel import is as simple as installing the XLSX plug-in in Angular.

  • Install the XLSX plug-in

npm install xlsx --save

  • Import in Component

import * as XLSX from 'xlsx';

  • The key code
import * as XLSX from 'xlsx';

excelData = [];

importExcel(evt: any) {
    /* wire up file reader */
    const target: DataTransfer = <DataTransfer>(evt.target);
    if(target.files.length ! == 1) throw new Error('Cannot use multiple files');
    const reader: FileReader = new FileReader();
    reader.onload = (e: any) => {
      /* read workbook */
      const bstr: string = e.target.result;
      const wb: XLSX.WorkBook = XLSX.read(bstr, { type: 'binary' });

      /* grab first sheet */
      const wsname: string = wb.SheetNames[0];
      const ws: XLSX.WorkSheet = wb.Sheets[wsname];

      /* save data */
      this.excelData = (XLSX.utils.sheet_to_json(ws, { header: 1 }));

      evt.target.value = ""/ / empty}; reader.readAsBinaryString(target.files[0]); }Copy the code


Excel export

The traditional export function is generally implemented in the back end, and the back end generates the Url or file stream to the front end. Note: this is directly downloaded through the browser download function. Generally, there are the following ways to achieve:

  • Get request + window.open(URL)

The back end returns the URL or stream of a file that can be downloaded directly. The premise is that the HTTP request is GET.

  • Post requests the + tag

Front-end code:

exportExcel(codeList: string[]) {
    return this.http.post(this.ExportExcelByCodesUrl, codeList, {
      responseType: 'arraybuffer'// Set the response type. Observe:"response",// Return response header headers: {'Content-Type': 'application/json' }
    })
      .subscribe((response:any)=>{
        this.downLoadFile(response, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8")
      })

  }

 /**
   * Method is use to download file.
   * @param data - Array Buffer data
   * @param type - type of the document.
 */
downLoadFile(data: any, type: string) {
     var blob = new Blob([data.body], { type: type});
     let downloadElement = document.createElement('a');
     lethref = window.URL.createObjectURL(blob); // Create a download link downloadElement.href = href;let filename = data.headers.get("Download-FileName"); // Custom header downloadElement.download = decodeURI(filename); document.body.appendChild(downloadElement); downloadElement.click(); / / click to download the document. The body. RemoveChild (downloadElement); / / the download is complete remove elements window. URL. RevokeObjectURL (href); // Release the blob object}Copy the code

Back-end code:

The back end is Asp.Net Core 2.1

 public IActionResult CreateExcel(string fileName,List<ExportProductModel> list)
 {
   string[] propertyNames = {""}; // Business code string[] propertyNameCn = {""}; // MemoryStream MS = ExcelsHelper<ExportProductModel>.ListToExcel(fileName, list, propertyNames, propertyNameCn); HttpContext.Response.Headers.Add("Download-FileName",WebUtility.UrlEncode(fileName));
   return File(ms, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;", WebUtility.UrlEncode(fileName));
 }

services.AddCors(options =>
                 {
                   options.AddPolicy("AllowAllOrigin", builder =>
                                     {
                                       builder.AllowAnyHeader()
                                         .AllowAnyMethod()
                                         .AllowAnyOrigin()
                                         .AllowCredentials()
                                         .WithExposedHeaders("Download-FileName"); 

                                     });
                 });Copy the code

The key point here is to set the response header across domains (i.e., “download-filename”), which each language has its own implementation. If not set, the front end cannot get the response header.

  • Post request + Form + IFrame tag


conclusion

I have encountered the following problems in the development process, toss about for a long time:

  • There is no corresponding MIME type at the front and back ends, so the file cannot be downloaded successfully. Here is a complete list of MIME types
  • The response header cannot be obtained for two reasons:

(1) No cross-domain response header is set on the backend

(2) The syntax of the FRONT-END HTTP request is incorrectly written, and the HTTP Response body has been obtained, rather than the complete HTTP response. Observe :” Response”


Finally, I hope this article can help you, mutual encouragement!