Excel
IMPORT MODULE

An Excel file operation based on sheetjs.

Note: You can also use js-xlsx to be another Fork by sheetjs library that provides additional options including: images, styles, and more. Finally, use callback option to render your excel.

Dependencies

The sheetjs script file takes the form of lazy loading,you can change the default CDN path via Global Configuration. By default: https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js.

Use local path

// angular.json
{
  "glob": "**/{xlsx.full.min,cpexcel}.js",
  "input": "./node_modules/xlsx/dist",
  "output": "assets/xlsx/"
}
// global-config.module.ts
const alainConfig: AlainConfig = {
  xlsx: {
    url: '/assets/xlsx/xlsx.full.min.js',
    modules: [`/assets/xlsx/cpexcel.js`]
  }
};

Examples

result:

Import

Import Excel and output JSON, support File, URL.

expand codeexpand code
import { ChangeDetectionStrategy, ChangeDetectorRef, Component } from '@angular/core';
import { XlsxService } from '@delon/abc/xlsx';

@Component({
  selector: 'components-xlsx-import',
  template: `
    <button nz-button (click)="url()">Via Url</button>
    <input type="file" (change)="change($event)" multiple="false" class="ml-sm" />
    <p class="mt-sm">result: {{ data | json }}</p>
  `,
  changeDetection: ChangeDetectionStrategy.OnPush,
})
export class ComponentsXlsxImportComponent {
  data: any;

  constructor(private xlsx: XlsxService, private cdr: ChangeDetectorRef) {}

  url(): void {
    this.xlsx.import(`./assets/demo.xlsx`).then(res => {
      this.data = res;
      this.cdr.detectChanges();
    });
  }

  change(e: Event): void {
    const node = e.target as HTMLInputElement;
    this.xlsx.import(node.files![0]).then(res => {
      this.data = res;
      this.cdr.detectChanges();
    });
    node.value = '';
  }
}
姓名年龄
编号姓名name 1年龄29
编号姓名name 2年龄28
编号姓名name 3年龄29
  • 1
  • 2
  • 3
  • 4
  • 5
  • •••
  • 34
  • Export

    Export Excel and automatically pop save dialog.

    expand codeexpand code
    import { Component } from '@angular/core';
    
    import { STColumn, STData } from '@delon/abc/st';
    import { XlsxService } from '@delon/abc/xlsx';
    
    @Component({
      selector: 'components-xlsx-export',
      template: `
        <button nz-button (click)="download('xlsx')">Export Xlsx</button>
        <button nz-button (click)="download('csv')">Export Csv</button>
        <st [data]="users" [ps]="3" [columns]="columns" class="mt-sm"></st>
      `
    })
    export class ComponentsXlsxExportComponent {
      constructor(private xlsx: XlsxService) {}
    
      users: STData[] = Array(100)
        .fill({})
        .map((_, idx) => ({
          id: idx + 1,
          name: `name ${idx + 1}`,
          age: Math.ceil(Math.random() * 10) + 20
        }));
      columns: STColumn[] = [
        { title: '编号', index: 'id', type: 'checkbox' },
        { title: '姓名', index: 'name' },
        { title: '年龄', index: 'age' }
      ];
    
      download(format: 'xlsx' | 'csv'): void {
        const data = [this.columns.map(i => i.title)];
        this.users.forEach(i => data.push(this.columns.map(c => i[c.index as string])));
        this.xlsx.export({
          sheets: [
            {
              data,
              name: 'sheet name'
            }
          ],
          format
        });
      }
    }

    API

    LazyService

    PropertyDescriptionTypeDefault
    import(fileOrUrl: File | string)Import Excel, return JSONPromise<{ [key: string]: any[][] }>-
    export(options: XlsxExportOptions)Export ExcelPromise<void>-
    numberToSchema(val: number)Numeric to schema namestring-

    XlsxExportOptions

    PropertyDescriptionTypeDefault
    [sheets]Data source{ [sheet: string]: WorkSheet } | XlsxExportSheet[]-
    [filename]file name of excelstringexport.xlsx
    [opts]Excel options, see WritingOptionsWritingOptions-
    [callback]Trigger before saving(wb: WorkBook) => void-

    xlsx

    xlsx directive.

    <div [xlsx]="XlsxExportOptions">Export</div>