安装
npm i exceljs
npm i file-saver
npm i @types/file-saver
services
import { Injectable } from '@angular/core';
import { Workbook } from 'exceljs';
import * as fs from 'file-saver';
@Injectable({
providedIn: 'root'
})
export class ExcelService {
workbook: any;
objectKeys = Object.keys;
objectValue = Object.values;
dic = {};
constructor() { }
/**
* @param {*} dic.title // 标题
*
* @param {*} dic.image // 图片，base64格式
* @param {*} dic.imageMerge //string, 图片占据的表格大小
*
* @param {*} dic.headArr // array, 表头数据
* @param {*} dic.headMergeArr // array, 表头需要合并的单元格
* @param {*} dic.headColor // string, 表头背景颜色
* @param {*} dic.headTitleColor // string, 表头文字颜色
* @param {*} dic.headFont // number, 设置表头字体大小
*
* @param {*} dic.bodyArr // array, 表内容数据
* @param {*} dic.bodyMergeArr // array, 表内容需要合并的单元格
* @param {*} dic.bodyColor // string, 表内容背景颜色
* @param {*} dic.bodyTextColor // string, 表内容文字颜色
* @param {*} dic.cellTextColor // Array, [[col, dataType, [number?string] ,[color]]] , 设置特殊列里面满足条件的字体颜色
* col: 指定的列
* dataType: 只能为 'number' 或者 'string'；
* [number?string] ： 参数可以是 number数字类型或 string 类型；如果为 number, 则 必须安降序排列, 即 5,4,3,...
* [color] : 特殊条件满足时，字体的颜色， 数组的长度必须等于[number?string]的数组长度
* @param {*} dic.bodyFont // number, 设置表内容字体大小
* @param {*} dic.cellLength // dic, {cellCount:cellLength}
* @param {*} dic.sigleCellLength // [[col,...],[length,...]] // 设置指定列的长度
*/
exportExcel(excelDic: Object) {
this.dic = excelDic;
this.workbook = new Workbook();
//工作表属性，冻结
const worksheet = this.workbook.addWorksheet('My Sheet',
{
properties: { tabColor: { argb: 'FFC0000' } },
views: [
{ showGridLines: false },
{
state: 'frozen',
xSplit: this.dic['frozen'][0] ? this.dic['frozen'][0] : 0,
ySplit: this.dic['frozen'][1] ? this.dic['frozen'][1] : 0,
topLeftCell: this.dic['frozen'][3] ? this.dic['frozen'][3] : '',
// activeCell: 'B3'
}
]
});
// worksheet.getCell('B3').dataValidation = {
// type: 'decimal',
// operator: 'between',
// allowBlank: true,
// showInputMessage: true,
// formulae: [1.5, 7],
// promptTitle: 'Decimal',
// prompt: 'The value must between 1.5 and 7'
// };
// worksheet.getCell('H5').numFmt = '# ???.???'
if (this.dic['image']) {
this.setImage(worksheet, this.dic['image']);
}
if (this.dic['headArr']['length'] > 0) {
this.setHead(
worksheet,
this.dic['headArr'],
this.dic['headColor'],
this.dic['headTitleColor'],
this.dic['headFont'],
this.dic['headMergeArr'],
this.dic['cellLength'],
this.dic['sigleCellLength']);
}
if (this.dic['bodyArr']['length'] > 0) {
this.setHead(
worksheet,
this.dic['bodyArr'],
this.dic['bodyColor'],
this.dic['bodyTextColor'],
this.dic['bodyFont'],
this.dic['bodyMergeArr'],
this.dic['cellLength'],
this.dic['sigleCellLength'],
this.dic['cellTextColor']);
}
worksheet.getCell('H5').numFmt = '#,#00.00#';
// 将自动筛选器设置为从 A1 到 C1
worksheet.autoFilter = {
from: 'A1',
to: 'H1',
}
// 指定有效值的列表（One，Two，Three，Four）。
// Excel 将提供一个包含这些值的下拉列表。
worksheet.getCell('A2').dataValidation = {
type: 'list',
allowBlank: true,
formulae: ['"One,Two,Three,Four"']
};
// 指定单元格的文本长度必须小于15
worksheet.getCell('F3').dataValidation = {
type: 'textLength',
operator: 'lessThan',
showErrorMessage: true,
allowBlank: true,
formulae: [4]
};
// worksheet.getCell('I2').value = { formula: 'F2+H2', result: 7 };
worksheet.getCell('F2').value = {
formula: 'A1',
result: 10,
shareType: 'shared',
ref: 'A2:B3'
};
const d = new Date();
const date = d.getFullYear() + '.' + (d.getMonth() + 1) + '.' + d.getDate();
this.workbook.xlsx.writeBuffer().then((data) => {
const blob = new Blob([data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8' });
fs.saveAs(blob, this.dic['title'] + '_' + date + '.xlsx');
});
}
// 设置图片
setImage(worksheet: any, imageData: any) {
const loadImage = this.workbook.addImage({ base64: imageData, extension: 'png' });
if (this.dic['imageMerge']) {
worksheet.mergeCells(this.dic['imageMerge']);
}
worksheet.addImage(loadImage, this.dic['imageMerge']);
}
// 设置表的展示格式
setHead(worksheet: any, data: Array<any>, backgroundColor: string, titleColor: string, fontSize: number, mergeArr: Array<any>, cell: Array<any>,
sigleCellLength: Array<any>, cellTextColor?: Array<any>) {
const array = [];
if (data['length'] === 0) {
return;
}
data.forEach(ele => {
array.push(worksheet.addRow(ele));
});
for (const item of array) {
item.eachCell((cell) => {
cell.fill = {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: backgroundColor ? backgroundColor : 'FFFFFF' },
bgColor: { argb: backgroundColor ? backgroundColor : 'FFFFFF' }
};
cell.font = {
bold: true,
color: { argb: titleColor ? titleColor : '000000' },
size: Number(fontSize) ? Number(fontSize) : 14
};
if (cellTextColor && cellTextColor.length > 0) {
const col = cell._value.model.address.split(cell._row._number)[0];
const value = cell._value.model['value'] ? cell._value.model['value'] : undefined;
for (let i = 0; i < cellTextColor.length; i++) {
if (cellTextColor[i][0] === col) {
if (cellTextColor[i][1] === 'string') {
// 为 string
const index = cellTextColor[i][2].indexOf(value);
if (index > -1) {
cell.font = {
bold: true,
color: { argb: cellTextColor[i][3][index] },
size: Number(fontSize) ? Number(fontSize) : 14
};
}
} else {
// 为number
for (let j = 0; j < cellTextColor[i][2]['length']; j++) {
if (Number(value) > Number(cellTextColor[i][2][j])) {
cell.font = {
bold: true,
color: { argb: cellTextColor[i][3][j] },
size: Number(fontSize) ? Number(fontSize) : 14
};
break;
}
}
}
}
}
}
cell.alignment = { vertical: 'middle', horizontal: 'center' };
cell.border = {
// 设置边框粗细
top: { style: 'thin' },
left: { style: 'thin' },
bottom: { style: 'thin' },
right: { style: 'thin' },
color: { argb: '000000' }
};
});
}
if (mergeArr && mergeArr['length'] > 0) {
this.mergeCells(worksheet, mergeArr);
}
// 设置单元格的宽度
if (this.objectValue(cell) && this.objectKeys(cell) && Number(this.objectKeys(cell)) > 0) {
for (let i = 1; i <= Number(this.objectKeys(cell)); i++) {
worksheet.getColumn(i).width = Number(this.objectValue(cell));
}
}
// 设置指定的单个cell宽度
if (sigleCellLength && sigleCellLength.length > 0) {
for (let k = 0; k < sigleCellLength[0].length; k++) {
worksheet.getColumn(Number(sigleCellLength[0][k])).width = Number(sigleCellLength[1][k]);
}
}
}
mergeCells(worksheet, marges) {
for (const marge of marges) {
worksheet.mergeCells(marge);
}
}
}
data
export const appData = {
title: '测试',
headTitleColor: '000',
headArr: [
['time', 'site', 'plant', 'line', 'station', 'model','name','age','total']
],
headColor: 'B284C7',
headFont: 16,
//
bodyArr: [
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'1',100000,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'rrr',500000,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'www',666666666,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'rrr',77777,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'ttt',10000,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'jjj',2557555,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'rrr',25665525,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'www',12345678,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'rrr',1055677,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'ttt',333333,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'jjj',856555,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'rrr',555222,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'www',159753,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'rrr',4560000,0],
['2022/01/24', 'xks', 'F2C1', 'SMT1', 'SPI', 2,'ttt',753.555000,0],
],
bodyColor: 'FFD700',
// bodyMergeArr: ['A2:A3', 'B4:C4', 'E2:E5'],
bodyTextColor: '000080',
//
cellLength: {10: 20},
sigleCellLength: [[1, 21, 50], [30, 50, 50]],
cellTextColor: [
['J', 'number', [108.25, 108.24], ['FF00FF', '008000']],
['I', 'number', [108.25], ['FF00FF']],
['D', 'string', ['SMT1'], [ '008000']],
],
// image: 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAeA',
// imageMerge: 'A1:H20',
frozen:[1,2,'B2'], //第一个纵向，第二页横向
};
