ExcelJS生成百万数据

后端使用express写接口 直接上代码

const express = require('express');
const router = express.Router();
const ExcelJS = require('exceljs');

// 百家姓列表
const familyNames = [
    '赵', '钱', '孙', '李', '周', '吴', '郑', '王', '冯', '陈',
    '褚', '卫', '蒋', '沈', '韩', '杨', '朱', '秦', '尤', '许',
    '何', '吕', '施', '张', '孔', '曹', '严', '华', '金', '魏',
    '陶', '姜', '戚', '谢', '邹', '喻', '柏', '水', '窦', '章',
    '云', '苏', '潘', '葛', '奚', '范', '彭', '郎', '鲁', '韦',
    '昌', '马', '苗', '凤', '花', '方', '俞', '任', '袁', '柳',
    '酆', '鲍', '史', '唐', '费', '廉', '岑', '薛', '雷', '贺',
    '倪', '汤', '滕', '殷', '罗', '毕', '郝', '邬', '安', '常',
    '乐', '于', '时', '傅', '皮', '卞', '齐', '康', '伍', '余',
    '元', '卜', '顾', '孟', '平', '黄', '和', '穆', '萧', '尹'
];

// 生成指定数量的假数据
function generatePatientsData(count) {
    const data = [];
    const departments = ['内科', '外科', '妇产科', '皮肤科', '口腔科'];
    const registrationTypes = ['普通号', '专家号', '急诊号'];
    const registrationStatuses = ['已就诊', '未就诊'];

    for (let i = 1; i <= count; i++) {
        const familyName = familyNames[Math.floor(Math.random() * familyNames.length)];
        const firstName = generateRandomFirstName();
        const medicalCardNumber = generateMedicalCardNumber();
        const idNumber = generateIDNumber();
        const phoneNumber = generatePhoneNumber();
        const department = departments[Math.floor(Math.random() * departments.length)];
        const registrationType = registrationTypes[Math.floor(Math.random() * registrationTypes.length)];
        const registrationFee = (Math.random() * 100).toFixed(2);
        const registrationStatus = registrationStatuses[Math.floor(Math.random() * registrationStatuses.length)];
        const visitDate = generateRandomDate();
        const waitingTime = `${Math.floor(Math.random() * 57)} 分钟`;
        const appointmentTime = generateRandomDate(true);

        const fullName = `${familyName}${firstName}`;
        
        data.push({
            name: fullName,
            medicalCardNumber,
            idNumber,
            phoneNumber,
            department,
            registrationType,
            registrationFee,
            registrationStatus,
            visitDate,
            waitingTime,
            appointmentTime
        });
    }
    return data;
}

// 生成随机的小名
function generateRandomFirstName() {
    const characters = 'abcdefghijklmnopqrstuvwxyz';
    let firstName = '';
    for (let i = 0; i < 2; i++) {
        firstName += characters.charAt(Math.floor(Math.random() * characters.length));
    }
    return firstName;
}

// 生成8位就诊卡号
function generateMedicalCardNumber() {
    let medicalCardNumber = '';
    for (let i = 0; i < 8; i++) {
        medicalCardNumber += Math.floor(Math.random() * 10);
    }
    return `${medicalCardNumber}`;
}

// 生成14位身份证号
function generateIDNumber() {
    let idNumber = '';
    for (let i = 0; i < 14; i++) {
        idNumber += Math.floor(Math.random() * 10);
    }
    return `${idNumber}`;
}

// 生成11位手机号码
function generatePhoneNumber() {
    let phoneNumber = '';
    for (let i = 0; i < 11; i++) {
        phoneNumber += Math.floor(Math.random() * 10);
    }
    return `${phoneNumber}`;
}

// 生成随机日期，参数now为true时生成当前时间之前的随机时间，否则生成当前时间之后的随机时间
function generateRandomDate(now = false) {
    const today = now ? new Date() : new Date(Date.now() + Math.floor(Math.random() * (365 * 24 * 60 * 60 * 1000)));
    return today.toLocaleString();
}

router.get('/generate-excel', async (req, res) => {
    try {
        // 创建一个新的工作簿
        const workbook = new ExcelJS.Workbook();
        const worksheet = workbook.addWorksheet('数据');

        // 添加表头
        worksheet.columns = [
            { header: '患者姓名', key: 'name', width: 20 },
            { header: '就诊卡号', key: 'medicalCardNumber', width: 20 },
            { header: '身份证号', key: 'idNumber', width: 20 },
            { header: '手机号码', key: 'phoneNumber', width: 20 },
            { header: '挂号科室', key: 'department', width: 20 },
            { header: '挂号类型', key: 'registrationType', width: 20 },
            { header: '挂号费', key: 'registrationFee', width: 15 },
            { header: '挂号状态', key: 'registrationStatus', width: 15 },
            { header: '就诊日期', key: 'visitDate', width: 15 },
            { header: '候诊时间', key: 'waitingTime', width: 15 },
            { header: '预约时间', key: 'appointmentTime', width: 25 }
        ];

        // 逐步生成数据并添加到工作表
        for (let i = 1; i <= 10000; i++) {
            const rowData = generatePatientsData(1)[0];
            worksheet.addRow(rowData);
        }

        // 设置响应头，告诉浏览器返回的是一个 Excel 文件
        res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        res.setHeader('Content-Disposition', 'attachment; filename="data.xlsx"');

        // 将工作簿数据写入到响应流
        await workbook.xlsx.write(res);

        // 结束响应
        res.end();
    } catch (error) {
        console.error('Error generating Excel:', error);
        res.status(500).send('Error generating Excel');
    }
});

module.exports = router;

前段我使用的vue3

<button @click="downloadExcel">生成并下载</button>



//请求的接口
const downloadExcel = async () => {
  try {
    const response = await axios.get('http://localhost:3000/generate-excel', {
      responseType: 'blob'  // 告诉 Axios 返回的是二进制数据
    });

    // 创建 Blob 对象
    const blob = new Blob([response.data], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

    // 创建下载链接
    const url = URL.createObjectURL(blob);

    // 创建一个<a>元素并设置属性
    const link = document.createElement('a');
    link.href = url;
    link.download = 'patients.xlsx'; // 下载文件名

    // 点击<a>元素进行下载
    link.click();

    // 清理 URL 对象
    URL.revokeObjectURL(url);
  } catch (error) {
    console.error('下载出错:', error);
  }
};