项目地址：https://gitee.com/LiangDouJun/nodejsExcel

一、实现效果

1、数据库数据导出

2、excel导入

二、代码实现

// 根据环境加载对应的配置文件
const env = process.env.NODE_ENV || 'development';
require('dotenv').config({ path: `.env.${env}` });const express = require('express');
let multer = require('multer');
let moment = require('moment');
const xlsx = require("node-xlsx");
const fs = require('fs').promises; // 使用异步版本
const path = require('path');
const router = express.Router()
const connection = require('../db/db')// 常量定义 - 从环境变量获取，如果没有则使用默认值
const ALLOWED_FILE_TYPES = ['.xlsx', '.xls'];
const MAX_FILE_SIZE = parseInt(process.env.MAX_FILE_SIZE) || 10 * 1024 * 1024; // 10MB
const MAX_FILES = parseInt(process.env.MAX_FILES) || 5;
const FILE_DELETE_DELAY = parseInt(process.env.FILE_DELETE_DELAY) || 60 * 1000; // 1分钟
const BATCH_SIZE = 1000; // 批量插入大小// 文件类型验证函数
const validateFileType = (file) => {const ext = path.extname(file.originalname).toLowerCase();return ALLOWED_FILE_TYPES.includes(ext);
};let Storage = multer.diskStorage({destination: (req, file, callback) => {// 指定当前这个文件存放的目录callback(null, 'files'); },filename: (req, file, callback) => {console.log('fieldname', file.originalname);// 文件命名：当前时间戳 + "_" + 源文件名称callback(null, new Date().getTime() + '_' + file.originalname); }
});// 文件上传配置
let upload = multer({ storage: Storage,limits: {fileSize: MAX_FILE_SIZE,files: MAX_FILES},fileFilter: (req, file, cb) => {if (!validateFileType(file)) {return cb(new Error('不支持的文件类型，只支持 .xlsx 和 .xls 文件'), false);}cb(null, true);}
}).array('file', MAX_FILES);// 批量插入数据到数据库
const batchInsertData = async (data) => {if (data.length === 0) return;const values = data.map(item => [item.id, item.userId, item.latitude, item.longitude, item.createTime]);const placeholders = values.map(() => '(?,?,?,?,?)').join(',');const sql = `INSERT INTO \`position\` (id, userId, latitude, longitude, createTime) VALUES ${placeholders}`;const flatValues = values.flat();try {const conn = await connection.promise();const [result] = await conn.execute(sql, flatValues);return result;} catch (error) {throw error;}
};// 解析Excel文件数据
const parseExcelData = (fileUrl) => {try {const sheets = xlsx.parse(fileUrl, { cellDates: true });const arr = [];sheets.forEach((sheet) => {for (let i = 1; i < sheet.data.length; i++) {const row = sheet.data[i];if (row && row.length >= 3) { // 确保至少有3列数据arr.push({id: Math.random().toString(36).substring(2, 15),userId: row[0] || '',latitude: row[1] || 0,longitude: row[2] || 0,createTime: moment().utc('+8:00').format('YYYY-MM-DD HH:mm:ss'),});}}});return arr;} catch (error) {throw new Error(`解析Excel文件失败: ${error.message}`);}
};// 安全删除文件
const safeDeleteFile = async (fileUrl) => {try {await fs.unlink(fileUrl);console.log(`文件已删除: ${fileUrl}`);} catch (error) {console.error(`删除文件失败: ${fileUrl}`, error);}
};// 导入Excel
router.post('/loadExcel', function (req, res) {upload(req, res, async (err) => {if (err) {console.error('文件上传错误:', err);return res.status(400).send({status: 1,message: err.message || '导入失败',data: null,});}if (!req.files || req.files.length === 0) {return res.status(400).send({status: 1,message: '请选择要上传的文件',data: null,});}const fileUrl = req.files[0].path;try {// 解析Excel数据const arr = parseExcelData(fileUrl);if (arr.length === 0) {await safeDeleteFile(fileUrl);return res.send({ status: 0, message: '文件解析成功，但没有有效数据', data: { list: [], total: 0 } });}// 批量插入数据const batches = [];for (let i = 0; i < arr.length; i += BATCH_SIZE) {batches.push(arr.slice(i, i + BATCH_SIZE));}for (const batch of batches) {await batchInsertData(batch);}// 延迟删除文件setTimeout(() => {safeDeleteFile(fileUrl);}, FILE_DELETE_DELAY);res.send({ status: 0, message: '导入成功', data: { list: arr, total: arr.length } });} catch (error) {console.error('导入处理错误:', error);// 清理文件await safeDeleteFile(fileUrl);res.status(500).send({status: 1,message: `导入失败: ${error.message}`,data: null,});}});
});// 导出Excel
router.get('/export', async function (req, res) {const sqlStr = 'SELECT userId, latitude, longitude FROM position ORDER BY createTime DESC';try {const conn = await connection.promise();const [data] = await conn.execute(sqlStr);const info = [["用户", "经度", "纬度"],...data.map(({userId, latitude, longitude}) => [userId, latitude, longitude])];const sheetOptions = {'!cols': [{wch: 30}, {wch: 30}, {wch: 30}]};const buffer = xlsx.build([{ name: '位置数据', data: info }], { sheetOptions }); res.setHeader('Content-Type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');// 使用英文文件名，确保兼容性const filename = `position_data_${moment().format('YYYY-MM-DD_HH-mm-ss')}.xlsx`;res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);res.end(buffer, 'binary');} catch (error) {console.error('导出错误:', error);res.status(500).send({status: 1,message: '导出失败',data: null,});}
});module.exports = router;