1. download excel

主要使用的是 xlsx-populate

• npm install xlsx-populate

• excel.js

const xlsx = require('xlsx-populate');
const setHeadAndColumStyle = ({sheet,colNo,columnStyle,headerPos,headerName,headerCellStyle}) => {
    sheet.column(colNo).width(columnStyle.width); // 设置列宽
    sheet.column(colNo).style(columnStyle.style); // 设置列的样式
    sheet.cell(position).value(headerName).style(headerCellStyle); // 设置表头的标题和样式
}
const setCellValue = ({sheet,cellPos,cellValue}) => {
    sheet.cell(position).value(cellValue); // 给excel表格赋值。
    // .style(cellStyle) //虽然这个插件很强大，可以给excel每一个cell的设置样式，但是如果遇到，大量数据的excel文件下载的情况，就会特别影响下载性能，就会弹框报错，因此谨慎设置每一个cell的样式（因为这个设置也不是特别必要。）
}
exports const makeExcelFun = async (headList,datalist,sheetName) => {
     const workbook = await xlsx.fromBlankAsync();
     const sheet = workbook.sheet(0).name(sheetName); // 给excel的tab页签命名。
     headList.forEach((h) => {
        setHeadAndColumStyle({sheet,colNo:h.colNo,columnStyle:h.colStyle,headerPos:`${h.colNo}1`,headerName:h.title,headerCellStyle:h.headerCellStyle});
     });
     datalist.forEach((d,idx) => {
         headList.forEach((h) => {
             setCellValue({sheet,cellPos:`${h.colNo}${idx + 2}`,cellValue:d[h.key]});
         });
     });
     return workbook.outputAsync();
}

• excelHeader.js

const styleObj = {
    basicStyle:{
        colStyle:{
            width: 30,
            style:{
                border:true,
                horizontalAlignment: 'left'
            }
        },
        headerCellStyle: {
            border:true,
            fontSize:'12',
            bold:true,
            fill:{
                type:'solid',
                color:'f3f3f3'
            }
        }
    }
};
const excelHeaderList = [
    {
        title:'姓名',colNo:'A',key:'name'
    },
    {
        title:'性别',colNo:'B',key:'gender'
    },
    {
        title:'班级',colNo:'C',key:'class'
    },
    ...
];
export const excelHeaders = excelHeaderList.map(header => Object.assign(header, {...styleObj.basicStyle}));

• excelData.js

export const excelDataList = [
    {
        title:'张三',
        gender:'男',
        class:'一班'
    },
    {
        title:'李四',
        gender:'男',
        class:'二班'
    },
    {
        title:'王五',
        gender:'女',
        class:'四班'
    },
    {
        title:'孟六',
        gender:'女',
        class:'五班'
    },
    ...
];

• inputDownload.js

import { makeExcelFun } from "./excel.js";
import { excelDataList } from "./excelData.js";
import { excelHeaders } from "./excelData.js";
const excelData = makeExcelFun(excelHeaders, excelHeaders, "一年级");
const blobArray = new Uint8Array(excelData);
const blob = new Blob([blobArray]);
const downloadExcel = (blob,fileName) => {
    if(window.navigator.msSaveOrOpenBlob) {
        window.navigator.msSaveOrOpenBlob(blob, fileName);
    } else {
        const link = document.createElement("a");
        link.href = window.URL.createObjectURL(blob);
        link.target = "_self";
        if(fileName) link.download = fileName;
        link.click();
        // 释放内存 createObjectURL 方法创建的URL 对象的内存，不用的时候，需要被释放。
        window.URL.revokeObjectURL(link.href);
    }
}
downloadExcel(blob, '经开八小三年级名单统计.xlsx');

2. send email