Excel表格下载和读取

SheetJS js-xlsx 的使用

js-xlsx 官方文档:https://sheetjs.gitbooks.io/docs/#sheetjs-js-xlsx
https://docs.sheetjs.com/#sheetjs-js-xlsx

npm xlsx地址:https://www.npmjs.com/package/xlsx

官网:https://sheetjs.com/opensource

npm install file-saver --save
npm install @types/file-saver --save-dev
npm install xlsx --save
<!-- <button mat-raised-button color="primary" (click)="exportTable()">导出</button> -->
<button type="button" class="btn btn-sm btn-success" (click)="exportTable()">导出2</button>
<!-- <div id="exportableTable" class="hidden-table">
    <table>
        <thead>
            <tr>
                <th>就好多了</th>
                <th>就好多了</th>
                <th>就好多了</th>
                <th>就好多了</th>
                <th>就好多了</th>
                <th>就好多了</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>大家尽快</td>
                <td>大家尽快</td>
                <td>大家尽快</td>
                <td>大家尽快</td>
                <td>大家尽快</td>
                <td>大家尽快</td>
            </tr>
            <tr>
                <td>大家尽快</td>
                <td>大家尽快</td>
                <td>大家尽快</td>
                <td>大家尽快</td>
                <td>大家尽快</td>
                <td>大家尽快</td>
            </tr>
        </tbody>
    </table>
</div> -->
import { Component, OnInit } from ‘@angular/core‘;
import * as FileSaver from ‘file-saver‘;
import { saveAs } from "file-saver";
import * as XLSX from ‘xlsx‘;
@Component({
  selector: ‘app-history‘,
  templateUrl: ‘./history.component.html‘,
  styleUrls: [‘./history.component.scss‘]
})
export class HistoryComponent implements OnInit {
  constructor() { }
  ngOnInit(): void {}
  // exportTable() {
  //   const blob = new Blob([document.getElementById(‘exportableTable‘).innerHTML], {
  //     type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"
  //   });
  //   saveAs(blob, ‘test.xls‘);
  // }


  jsonData: any = [
    {
      "姓名": "张三",
      "工號": "22",
      "英文名": "zhangsan",
      "部门": "技术部",
      "性别": "男",
      "手机": "15177641999",
      "个人邮箱": "610336688@qq.com"
    }, {
      "姓名": "李四",
      "工號": "33",
      "英文名": "李四",
      "部门": "人事部",
      "性别": "女",
      "手机": "15177641988",
      "个人邮箱": "978336688@qq.com"
    }];

exportTable() {
    const exportItem = this.jsonData;
    const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(exportItem);
    console.log("worksheet",worksheet)
    const workbook: XLSX.WorkBook = { Sheets: { ‘data‘: worksheet }, SheetNames: [‘data‘] };
    console.log("workbook",workbook)
    const excelBuffer: any = XLSX.write(workbook, { bookType: ‘xlsx‘, type: ‘array‘ });
    console.log("excelBuffer",excelBuffer)
    this.saveAsExcelFile(excelBuffer, ‘report‘);
}

private saveAsExcelFile(buffer: any, fileName: string) {
  const data: Blob = new Blob([buffer], {
    type: ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8‘
  });
  FileSaver.saveAs(data, fileName + ‘.xlsx‘);
}
}

https://www.cnblogs.com/lvsk/p/11970747.html

exportExcle() {

       // 使用 XLSX.utils.aoa_to_sheet(excleData);
        // const excleData = [
        //     [‘周一‘, ‘周二‘, ‘周三‘, ‘周四‘, ‘周五‘],
        //     [‘语文‘, ‘数学‘, ‘历史‘, ‘政治‘, ‘英语‘],
        //     [‘数学‘, ‘数学‘, ‘政治‘, ‘英语‘, ‘英语‘],
        //     [‘政治‘, ‘英语‘, ‘历史‘, ‘政治‘, ‘数学‘],
        // ];

       // 使用 XLSX.utils.json_to_sheet(excleData);
        const excleData = [
            {周一: ‘语文‘, 周二: ‘数学‘, 周三: ‘历史‘, 周四: ‘政治‘, 周五: ‘英语‘},
            {周一: ‘数学‘, 周二: ‘数学‘, 周三: ‘政治‘, 周四: ‘英语‘, 周五: ‘英语‘},
            {周一: ‘政治‘, 周二: ‘英语‘, 周三: ‘历史‘, 周四: ‘政治‘, 周五: ‘数学‘},
        ];

        // 设置表格样式,!cols为列宽
        const options = {
          ‘!cols‘: [
                { wpx: 100 },
                { wpx: 100 },
                { wpx: 100 },
                { wpx: 100 },
                { wpx: 100 },
        ]};
       // 制作工作表的方式有很多种,以数组和对象为例
        // const worksheet: XLSX.WorkSheet = XLSX.utils.aoa_to_sheet(excleData);
        const worksheet: XLSX.WorkSheet = XLSX.utils.json_to_sheet(excleData);

       // 使用指定的单元格作为起点插入数据,r:行, c:列,详情看官网文档
        // XLSX.utils.sheet_add_aoa(worksheet, [[“数学”,“语文”], [“政治”,“语文”], [“历史”,“政治”], ], {origin: {r: 2, c: 5}});
        worksheet[‘!cols‘] = options[‘!cols‘];

        // 新建一个工作簿
        const workbook: XLSX.WorkBook = XLSX.utils.book_new();

        /* 将工作表添加到工作簿*/
        XLSX.utils.book_append_sheet(workbook, worksheet, ‘Sheet1‘);
        /* 输出工作表, 由文件名决定的输出格式*/
        XLSX.writeFile(workbook, ‘排庭表.xlsx‘);
    }

ExcelJS

https://github.com/exceljs/exceljs/blob/master/README_zh.md
https://github.com/exceljs/exceljs

import { AfterViewInit, Component, ElementRef, EmbeddedViewRef, OnInit, TemplateRef, ViewChild, ViewContainerRef } from ‘@angular/core‘;
import * as FileSaver from ‘file-saver‘;
import { saveAs } from "file-saver";
import * as XLSX from ‘xlsx‘;
import * as Excel from ‘exceljs‘;
@Component({
  selector: ‘app-root‘,
  templateUrl: ‘./app.component.html‘,
  styleUrls: [‘./app.component.css‘]
})
export class AppComponent implements OnInit {

  constructor() { }

  ngOnInit(): void {
    this.exportExcle()
  }

  exportExcle() {

    const excleData = [
      [‘周一‘, ‘周二‘],
      [‘语文‘, ‘数学‘],
      [‘数学‘, ‘英语‘],
    ];
    const workbook = new Excel.Workbook();
    const sheet = workbook.addWorksheet(‘Sheet1‘);
    sheet.addRows(excleData);
    sheet.eachRow((row, rowNumber) => {
      row.eachCell((cell, colNumber) => {
        // 对齐样式
        if (rowNumber === 1 || colNumber === 1) {
          cell.alignment = { vertical: ‘middle‘, horizontal: ‘center‘, wrapText: true };
        } else {
          cell.alignment = { vertical: ‘top‘, horizontal: ‘left‘, wrapText: true };
        }
        // 边框
        cell.border = {
          top: { style: ‘thin‘ },
          left: { style: ‘thin‘ },
          bottom: { style: ‘thin‘ },
          right: { style: ‘thin‘ },
        };
        // 设置列宽
        sheet.getColumn(colNumber).width = 25;
        // sheet.getColumn(colNumber).width = 25;
      });
    });
    sheet.getRow(1).height = 30;
    workbook.xlsx.writeBuffer()
      .then((buffer) => {
        // done
        this.saveAsExcelFile(buffer, ‘课表‘);
      });
  }
  saveAsExcelFile(buffer: any, fileName: string) {
    const data: Blob = new Blob([buffer], {
      type: ‘application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8‘
    });
    FileSaver.saveAs(data, fileName + ‘.xlsx‘);
  }

}

Excel表格下载和读取

上一篇:border 边框


下一篇:new Date() iso不支持兼容性问题