Excel文件
使用 exceljs 来实现excel文件处理，excel 文件分为 workbook、worksheet、row、cell 这 4 层，解析和生成都是按照这个层次结构来。
nest里解析和生成excel
/**
* @method 解析excel文件
* @returns
*/
@Get('excel')
async handleExcel() {
const workbook = new Workbook();
const workbookData = await workbook.xlsx.readFile('./static/data.xlsx');
workbookData.eachSheet((sheet, index) => {
sheet.eachRow((row, i) => {
const rowData = [];
row.eachCell((cell, j) => {
rowData.push(cell.value);
});
console.log('行' + i, rowData);
});
});
return '解析成功';
}
/**
* @method 生成excel文件
* @returns
*/
@Get('excel/generate')
async generateExcel() {
const workbook = new Workbook();
const worksheet = workbook.addWorksheet('hazymoon');
worksheet.columns = [
{ header: 'ID', key: 'id', width: 20 },
{ header: '姓名', key: 'name', width: 30 },
{ header: '出生日期', key: 'birthday', width: 30 },
{ header: '手机号', key: 'phone', width: 50 },
];
const data = [
{
id: 1,
name: '光光',
birthday: new Date('1994-07-07'),
phone: '13255555555',
},
{
id: 2,
name: '东东',
birthday: new Date('1994-04-14'),
phone: '13222222222',
},
{
id: 3,
name: '小刚',
birthday: new Date('1995-08-08'),
phone: '13211111111',
},
];
worksheet.addRows(data);
worksheet.eachRow((row, rowIndex) => {
row.eachCell((cell) => {
if (rowIndex === 1) {
cell.style = {
font: {
size: 10,
bold: true,
color: { argb: 'ffffff' },
},
alignment: { vertical: 'middle', horizontal: 'center' },
fill: {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: '000000' },
},
border: {
top: { style: 'dashed', color: { argb: '0000ff' } },
left: { style: 'dashed', color: { argb: '0000ff' } },
bottom: { style: 'dashed', color: { argb: '0000ff' } },
right: { style: 'dashed', color: { argb: '0000ff' } },
},
};
} else {
cell.style = {
font: {
size: 10,
bold: true,
},
alignment: { vertical: 'middle', horizontal: 'left' },
border: {
top: { style: 'dashed', color: { argb: '0000ff' } },
left: { style: 'dashed', color: { argb: '0000ff' } },
bottom: { style: 'dashed', color: { argb: '0000ff' } },
right: { style: 'dashed', color: { argb: '0000ff' } },
},
};
}
});
});
workbook.xlsx.writeFile('./static/hazymoon.xlsx');
}
浏览器里生成excel并下载
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<title>excel</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<script src="https://unpkg.com/exceljs@4.4.0/dist/exceljs.min.js"></script>
</head>
<body>
<script>
const { Workbook } = ExcelJS;
generateExcel();
/*
* @method 生成表格
*/
async function generateExcel() {
const workbook = new Workbook();
const worksheet = workbook.addWorksheet('hazymoon');
worksheet.columns = [
{ header: 'ID', key: 'id', width: 20 },
{ header: '姓名', key: 'name', width: 30 },
{ header: '出生日期', key: 'birthday', width: 30 },
{ header: '手机号', key: 'phone', width: 50 },
];
const data = [
{
id: 1,
name: '光光',
birthday: new Date('1994-07-07'),
phone: '13255555555',
},
{
id: 2,
name: '东东',
birthday: new Date('1994-04-14'),
phone: '13222222222',
},
{
id: 3,
name: '小刚',
birthday: new Date('1995-08-08'),
phone: '13211111111',
},
];
worksheet.addRows(data);
worksheet.eachRow((row, rowIndex) => {
row.eachCell((cell) => {
if (rowIndex === 1) {
cell.style = {
font: {
size: 10,
bold: true,
color: { argb: 'ffffff' },
},
alignment: { vertical: 'middle', horizontal: 'center' },
fill: {
type: 'pattern',
pattern: 'solid',
fgColor: { argb: '000000' },
},
border: {
top: { style: 'dashed', color: { argb: '0000ff' } },
left: { style: 'dashed', color: { argb: '0000ff' } },
bottom: { style: 'dashed', color: { argb: '0000ff' } },
right: { style: 'dashed', color: { argb: '0000ff' } },
},
};
} else {
cell.style = {
font: {
size: 10,
bold: true,
},
alignment: { vertical: 'middle', horizontal: 'left' },
border: {
top: { style: 'dashed', color: { argb: '0000ff' } },
left: { style: 'dashed', color: { argb: '0000ff' } },
bottom: { style: 'dashed', color: { argb: '0000ff' } },
right: { style: 'dashed', color: { argb: '0000ff' } },
},
};
}
});
});
const arraybuffer = new ArrayBuffer(10 * 1024 * 1024);
const res = await workbook.xlsx.writeBuffer(arraybuffer);
console.log(res.buffer);
download(res.buffer);
}
/*
* @method 下载表格
*/
function download(arrayBuffer) {
const link = document.createElement('a');
const blob = new Blob([arrayBuffer]);
const url = URL.createObjectURL(blob);
link.href = url;
link.download = 'hazymoon.xlsx';
document.body.appendChild(link);
link.click();
link.addEventListener('click', () => {
link.remove();
});
}
</script>
</body>
</html>
