前端解析Excel文件

前端解析Excel文件

 

import { Injectable } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { LazyResult, LazyService } from './lazy.service';
import { saveAs } from 'file-saver';

import { XlsxConfig } from '../models/xlsx-config';
import { XlsxExportOptions, XlsxExportSheet } from '../models/xlsx.types';

declare var XLSX: any;

@Injectable({ providedIn: 'root' })
export class ExcelService {
  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();
        if (!FileReader.prototype.readAsBinaryString) {
          // 判断是否可以使用readAsBinaryString，IE10用readAsArrayBuffer,手动将文件流转二进制字符串
          reader.onload = (e: any) => {
            const wb: any = XLSX.read(btoa(this.fixdata(e.target.result)), { type: 'base64' });
            resolver(this.read(wb));
          };
          reader.readAsArrayBuffer(fileOrUrl);
        } else {
          // 不需要兼容IE10用readAsBinaryString
          reader.onload = (e: any) => {
            const wb: any = XLSX.read(e.target.result, { type: 'binary' });
            resolver(this.read(wb));
          };
          reader.readAsBinaryString(fileOrUrl);
        }
        // reader[rABS](fileOrUrl);
      });
    });
  }
  /**
   * 文件流转BinaryString
   */
  fixdata(data) {
    let o = '';
    const bytes = new Uint8Array(data);
    for (let i = 0; i < data.byteLength; i++) {
      o += String.fromCharCode(bytes[i]);
    }
    return o;
  }

  /** 导出 */
  export(options: XlsxExportOptions): Promise<void> {
    return this.init().then(() => {
      const wb: any = XLSX.utils.book_new();
      if (Array.isArray(options.sheets)) {
        (options.sheets as XlsxExportSheet[]).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, {
        bookType: 'xlsx',
        bookSST: false,
        type: 'array',
        ...options.opts,
      });
      saveAs(new Blob([wbout], { type: 'application/octet-stream' }), options.filename || 'export.xlsx');
    });
  }
}

 

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

@Injectable({ providedIn: 'root' })
export class XlsxConfig {
  /**
   * Xlsx library path
   * 可替换成本地的
   */
  url?: string = '//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[] = [];
}

 

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;
}