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年龄24
编号姓名name 2年龄28
编号姓名name 3年龄24
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>