angular7 导入Excel文件

1. 创建一个类型为file的input

   <a href="javascript:;">
      <input type="file" [(ngModel)]="file"
             accept=".csv, application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
             ng2FileSelect (change)="change($event)"/>选择Excel文件
    </a>

2. change事件

这里有三列数据的excel

列1	列2	列3

change(e: Event) {
    this.nzTip = '正在验证数据，请稍后。';
    this.isSpinning = true;
    this.message = '';
    // this._loading = true;
    const node = e.target as HTMLInputElement;
    this.xlsx.import(node.files[0]).then(res => {
      this.excelResult = [];
      console.log(res);
      if (res['sheet名'] && res['sheet名'].length > 0) {
        let i = 1;
        const del = [];
        res['sheet名'].forEach(result => {
          if (result.indexOf("列2") == -1) { //过滤掉第一行
            const excelData = new XXXXXX();
            excelData.index = i;
            if ( result.length > 0 ) {
                if (result[0] == undefined) {
                 // 数据为空
                } else {
                  if (result[0] == '') {
                   
                  } else {
                    excelData.netWorthDate = dayJs(result[0]).format("YYYY-MM-DDT00:00:00ZZ");
                  }
                }
                if (result[1] == undefined) {
               
                  excelData.netWorth = null;
                } else {
                  excelData.netWorth = result[1];
                  if (!this.checkNumber(result[1])) {
                   // 数据类型校验错误，下标添加至数组，用于下一步删除
                    del.push(i - 1);
                  }
                }
                if (result[2] == undefined) {
 
                  excelData.allNetWorth = null;
                } else {
                  excelData.allNetWorth = result[2];
                  if (!this.checkNumber(result[2])) {
                    if (del.indexOf(i - 1) == -1) {
                      del.push(i - 1);
                    }
                  }
                }
                this.excelResult.push(excelData);
              i++;
            }
          }
        });
        // 删除前端校验错误数据
        const potsArr = new Array<XXXXXXXX>();
        for (let n = 0; n < this.excelResult.length; n ++) {
          if (del.indexOf(n) == -1) { // 如果不存在删除的数组中，添加至新数组，用于提交数据
            potsArr.push(this.excelResult[n]);
          }
        }
        // 提交到后端验证准确性
        this.yaxbProductNetWorthService.checkImportExcel(potsArr).subscribe( d => {
          if (d.code == '1') {
            this.file = '';
            } else {
              // 没有错误，调用保存数据
              this.nzTip = '正在保存数据……';
            this.service.upload(this.excelResult).subscribe( r => {
              this.isSpinning = false;
              if (r.code == '1') {
                this._notification.create("success", "成功", '数据已经保存成功！');
                this.subject.destroy('onOk');
              } else {
                this._notification.create("warning", "提示", '服务器执行错误！' + d.code);
              }
            });
            }
          } else {
            this.isSpinning = false;
            this._notification.create("warning", "提示", '服务器执行错误！' + d.code);
          }        });
      } else {
        this.isSpinning = false;
        this._notification.create("warning", "提示", '请将工作表(sheet)名修改为“净值数据”再尝试导入！');
      }
    });
  }

3 excel解析服务

• xlsx.service.ts

  import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { saveAs } from 'file-saver';

import {XlsxConfig} from "./xlsx.config";
import {LazyResult, LazyService} from "../lazy.service";
import {XlsxExportOptions, XlsxExportSheet} from "./xlsx.type";

declare var XLSX: any;

@Injectable({
  providedIn: "root"
})
export class XlsxService {
  constructor(
    private cog: XlsxConfig,
    private http: HttpClient,
    private lazy: LazyService,
  ) {}

  private init(): Promise<LazyResult[]> {
    return this.lazy.load([this.cog.url].concat(this.cog.modules));
  }

  private read(wb: any): { [key: string]: any[][] } {
    const ret: any = {};
    wb.SheetNames.forEach(name => {
      const sheet: any = wb.Sheets[name];
      ret[name] = XLSX.utils.sheet_to_json(sheet, { header: 1 });
    });
    return ret;
  }

  /**
   * 导入Excel并输出JSON，支持 `<input type="file">`、URL 形式
   * @param rABS 加载数据方式 `readAsBinaryString` （默认） 或 `readAsArrayBuffer`，[更多细节](http://t.cn/R3n63A0)
   */
  import(
    fileOrUrl: File | string,
    rABS: 'readAsBinaryString' | 'readAsArrayBuffer' = 'readAsBinaryString',
  ): Promise<{ [key: string]: any[][] }> {
    return new Promise<{ [key: string]: any[][] }>((resolver, reject) => {
      this.init().then(() => {
        // from url
        if (typeof fileOrUrl === 'string') {
          this.http
            .request('GET', fileOrUrl, { responseType: 'arraybuffer' })
            .subscribe(
              (res: ArrayBuffer) => {
                const wb = XLSX.read(new Uint8Array(res), { type: 'array' });
                resolver(this.read(wb));
              },
              (err: any) => {
                reject(err);
              },
            );
          return;
        }
        // from file
        const reader: FileReader = new FileReader();
        reader.onload = (e: any) => {
          const wb: any = XLSX.read(e.target.result, { type: 'binary' });
          resolver(this.read(wb));
        };
        reader[rABS](fileOrUrl);
      });
    });
  }

  /** 导出 */
  export(options: XlsxExportOptions): Promise<void> {
    return this.init().then(() => {
      const wb: any = XLSX.utils.book_new();
      if (Array.isArray(options.sheets)) {
        (<XlsxExportSheet[]>options.sheets).forEach(
          (value: XlsxExportSheet, index: number) => {
            const ws: any = XLSX.utils.aoa_to_sheet(value.data);
            XLSX.utils.book_append_sheet(
              wb,
              ws,
              value.name || `Sheet${index + 1}`,
            );
          },
        );
      } else {
        wb.SheetNames = Object.keys(options.sheets);
        wb.Sheets = options.sheets;
      }

      if (options.callback) { options.callback(wb); }

      const wbout: ArrayBuffer = XLSX.write(
        wb,
        Object.assign(
          {
            bookType: 'xlsx',
            bookSST: false,
            type: 'array',
          },
          options.opts,
        ),
      );
      saveAs(
        new Blob([wbout], { type: 'application/octet-stream' }),
        options.filename || 'export.xlsx',
      );
    });
  }
}

-xlsx.config.ts

import {Injectable} from "@angular/core";

@Injectable({
  providedIn: "root"
})
export class XlsxConfig {
  /**
 * Xlsx library path
   */
  url ? = '//cdn.bootcss.com/xlsx/0.12.13/xlsx.full.min.js';
  /**
 * Defines which Xlsx optional modules should get loaded, e.g:
 *  * `[ '//cdn.bootcss.com/xlsx/0.12.13/cpexcel.js' ]`
   */
  modules?: string[] = [];
}

*xlsx.directive.ts

import { Directive, HostListener, Input } from '@angular/core';
import { XlsxService } from './xlsx.service';
import {XlsxExportOptions} from "./xlsx.type";

@Directive({ selector: '[m2Xlsx]' })
export class XlsxDirective {
  @Input('m2Xlsx') data: XlsxExportOptions;

  constructor(private srv: XlsxService) {}

  @HostListener('click')
  _click() {
    this.srv.export(this.data);
  }
}

• xlsx.module.ts

import { NgModule, ModuleWithProviders } from '@angular/core';
import { CommonModule } from '@angular/common';

import { XlsxDirective } from './xlsx.directive';

const COMPONENTS = [XlsxDirective];

@NgModule({
  imports: [CommonModule],
  declarations: [...COMPONENTS],
  exports: [...COMPONENTS],
})
export class XlsxModule {}

-xlsx.type.ts

export interface XlsxExportOptions {
  /**
   * worksheets in the workbook, e.g:
   * - `{ Sheet1: { A1: { t:"n", v:10000 } } }`
   * - `[['1'], [1]]`
   */
  sheets: { [sheet: string]: any } | XlsxExportSheet[];
  /** save file name, default: `export.xlsx` */
  filename?: string;
  opts?: any;
  /** triggers when saveas */
  callback?: (wb: any) => void;
}

export interface XlsxExportSheet {
  /** arrays to a worksheet */
  data: any[][];
  /** sheet name */
  name?: string;
}