nestjs 导出excel

1、安装

npm install exceljs

2、封装导出函数

import * as ExcelJS from 'exceljs';
import { Response } from 'express';
import { DelFlagEnum, SexEnum, StatusEnum } from './base-enum';

/**
 * 
 * @param options 
 * data : 数据
 * header : 表头。
 * dictMap : 字典映射 主要用与将数据转为中文展示
 * sheetName : sheet名称
 * 
 * 参数示例：
·*  {
        sheetName: '调度日志',
        data: list.data.list,
        header: [
            { title: '日志编号', dataIndex: 'jobLogId' },
            { title: '任务名称', dataIndex: 'jobName' },
            { title: '任务组名', dataIndex: 'jobGroup' },
            { title: '调用目标字符串', dataIndex: 'invokeTarget' },
            { title: '日志信息', dataIndex: 'jobMessage' },
            { title: '执行时间', dataIndex: 'createTime',formateStr:(value)=>value.format('YYYY-MM-DD HH:mm:ss') },
        ],
        dictMap: {
            status: {
                '0': '成功',
                '1': '失败',
            },
            jobGroup: {
                SYSTEM: '系统',
                DEFAULT: '默认',
            },
        },
    }
 * @param res 
 */

/**
 * 通用枚举映射配置
 */
export const commonExportMap = {
    status: {
        [StatusEnum.NORMAL]: '正常',
        [StatusEnum.STOP]: '停用',
    },
    sex: {
        [SexEnum.MAN]: '男',
        [SexEnum.WOMAN]: '女',
    },
    delFlag: {
        [DelFlagEnum.NORMAL]: '正常',
        [DelFlagEnum.DELETE]: '已删除',
    }
};

//导出excel
export async function ExportExcel(
    options: {
        data: any[];
        header: any[];
        dictMap?: any;
        sheetName?: string;
    }, res: Response
) {

    let data = options.data;
    // 创建excel实例
    const workBook = new ExcelJS.Workbook();
    // 创建sheet
    const sheetName = options.sheetName || 'Sheet1';
    // 将sheet添加到工作簿中
    const worksheet = workBook.addWorksheet(sheetName);
    // 创建sheetName的列. 格式化表头数据
    worksheet.columns = options.header.map(item => ({
        header: item.title,
        key: item.key,
        width: item.width || 20,
    }));

    const dictMap = { ...options.dictMap, ...commonExportMap };

    data = data.map(item => {
        // 用来保存每一行的数据。对数据进行格式化、转换后返回
        const newItem = {}
        options.header.forEach(field => {
            // 通过头部的key 获取字段名称 用来下面去取数据上的值 同时也判断是否需要哦映射为中文
            const fieldName = field.key;
            // 获取字段值
            const dataValue = item[fieldName];
            if (dictMap[fieldName]) {
                newItem[fieldName] = dictMap[fieldName][dataValue] || dataValue;
            } else {
                newItem[fieldName] = dataValue;
            }
            if (field.formateStr && typeof field.formateStr == 'function') {
                newItem[fieldName] = field.formateStr(newItem[fieldName]);
            }
        });
        return newItem;
    });

    // 定义表头样式
    const headerStyle: any = {
        font: {
            size: 10,
            bold: true,
            color: { argb: 'ffffff' },
        },
        alignment: { vertical: 'middle', horizontal: 'center' },
        fill: {
            type: 'pattern',
            pattern: 'solid',
            fgColor: { argb: '808080' },
        },
        border: {
            top: { style: 'thin', color: { argb: '9e9e9e' } },
            left: { style: 'thin', color: { argb: '9e9e9e' } },
            bottom: { style: 'thin', color: { argb: '9e9e9e' } },
            right: { style: 'thin', color: { argb: '9e9e9e' } },
        },
    };

    const headerRow = worksheet.getRow(1);
    headerRow.eachCell((cell) => {
        cell.style = headerStyle;
    });

    worksheet.columns.forEach((column) => {
        column.alignment = { vertical: 'middle', horizontal: 'center' };
    });

    worksheet.addRows(data);

    const buffer = await workBook.xlsx.writeBuffer();
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', 'attachment;filename=sheet.xlsx');
    res.setHeader('Access-Control-Expose-Headers', 'Content-Disposition');
    res.setHeader('X-Content-Type-Options', 'nosniff');
    // 将 Excel 文件的二进制流数据返回给客户端
    res.end(buffer, 'binary');

}

3、调用

// 控制器。记得必须要有 @Res()
@Post("/exportExcel")
exportExcel(@Body() body: ListUserDto, @Res() res: Response) {
    return this.userService.exportExcel(body, res)
}


//service
async exportExcel(body: ListUserDto, res: Response) {
    const options = {
        data: await this.userRepository.find({
            where: {
                ...whereFilter(body, {
                    excludeFields: ['userName']
                }),
                userName: Like(`%${body.userName || ''}%`)
            }
        }),
        header: [
            {
                key: 'userName',
                title: '用户名'
            },
            {
                key: 'nickName',
                title: '昵称'
            },
            {
                key: 'email',
                title: '邮箱'
            },
            {
                key: 'phoneNumber',
                title: '手机号'
            },
        ]
    }
    return await ExportExcel(options, res)
}

4、前端处理

// 对返回的流进行处理 然后下载
exportUserExcel().then(res => {
    const time = dayjs(new Date()).format("YYYY-MM-DD HH:mm:ss")
    let url = window.URL.createObjectURL(new Blob([res as any]))
    let link = document.createElement('a')
    link.style.display = 'none'
    link.href = url
    link.setAttribute('download', `用户列表${time}.xls`)
    document.body.appendChild(link)
    link.click()
    document.body.removeChild(link); //下载完成移除元素
    window.URL.revokeObjectURL(url); //释放掉blob对象
    message.success("下载完成")
})



// 要记得添加 responseType 为 'blob'
export const exportUserExcel = () => {
    return request<any>({
        url: api.exportUserExcel,
        method: "POST",
        responseType: "blob"
    })
}


// 响应拦截器过滤一下  response.config.responseType != 'blob'
instance.interceptors.response.use((response) => {
    console.log(response)
    if (response.data && response.data.code != 200 && response.config.responseType != 'blob') {
        message.error(response.data.message)
    }
    return response.data;
}, error => {
    message.error(error.message)
    return Promise.reject(error);
});