nodejs 实现Excel数据导入数据库，以及数据库数据导出excel接口（核心使用了multer和node-xlsx库）

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