項目地址：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;