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