Node.js实现Excel读取导入MSSQL及上传导入后原文件下载

Hey, great questions! I've built similar workflows before, so let's walk through how to implement both the Excel-to-MSSQL import and the upload/download functionality step by step. We'll use common Node.js libraries to keep things straightforward.

First, let's grab all the packages we need:

• express: To create our web server
• multer: Handles file uploads
• xlsx: Parses Excel files into JSON
• mssql: Connects to MSSQL and runs queries
• cors (optional): If you're calling the API from a frontend app

Run this command to install them:

npm install express multer xlsx mssql cors

First, create an uploads folder in your project root (we'll store uploaded Excel files here). Then configure multer to handle file uploads and restrict to Excel file types.

Here's the setup code:

const express = require('express');
const multer = require('multer');
const XLSX = require('xlsx');
const sql = require('mssql');
const cors = require('cors');
const path = require('path');
const fs = require('fs');

const app = express();
app.use(cors());
app.use(express.json());

// Ensure uploads directory exists (create if missing)
const uploadDir = './uploads';
if (!fs.existsSync(uploadDir)) {
  fs.mkdirSync(uploadDir);
}

// Configure multer storage to keep unique filenames
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, uploadDir);
  },
  filename: (req, file, cb) => {
    // Add timestamp to avoid duplicate filenames
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, `${uniqueSuffix}-${file.originalname}`);
  }
});

// Filter to only accept Excel files
const fileFilter = (req, file, cb) => {
  const allowedTypes = ['.xlsx', '.xls'];
  const ext = path.extname(file.originalname).toLowerCase();
  if (allowedTypes.includes(ext)) {
    cb(null, true);
  } else {
    cb(new Error('Only Excel files (.xlsx, .xls) are allowed!'), false);
  }
};

const upload = multer({ 
  storage: storage, 
  fileFilter: fileFilter,
  limits: { fileSize: 5 * 1024 * 1024 } // Limit uploads to 5MB
});

Next, set up the MSSQL connection pool (replace the config values with your database credentials) and create the upload endpoint that parses the Excel file and inserts data into the database.

First, the MSSQL config:

const sqlConfig = {
  user: 'your-db-username',
  password: 'your-db-password',
  database: 'your-db-name',
  server: 'your-db-server',
  options: {
    encrypt: true, // Enable for Azure SQL; set to false for local MSSQL
    trustServerCertificate: true // Safe for local development only
  }
};

// Create a reusable connection pool
const pool = new sql.ConnectionPool(sqlConfig);
pool.connect(err => {
  if (err) console.error('MSSQL connection failed:', err);
  else console.log('Successfully connected to MSSQL');
});

Then the upload endpoint (adjust table/column names to match your database schema):

// Upload & import endpoint
app.post('/upload', upload.single('excelFile'), async (req, res) => {
  try {
    if (!req.file) {
      return res.status(400).json({ message: 'No file uploaded' });
    }

    // Parse the Excel file into JSON
    const workbook = XLSX.readFile(req.file.path);
    const worksheetName = workbook.SheetNames[0]; // Use the first sheet
    const excelData = XLSX.utils.sheet_to_json(workbook.Sheets[worksheetName]);

    if (excelData.length === 0) {
      return res.status(400).json({ message: 'Excel file has no data' });
    }

    // Insert data into MSSQL (use parameterized queries to avoid SQL injection)
    const tableName = 'YourTargetTableName';
    const columns = Object.keys(excelData[0]).join(', ');
    const placeholders = Object.keys(excelData[0]).map((_, i) => `@${i}`).join(', ');

    const request = pool.request();
    for (const row of excelData) {
      // Bind values to parameters
      Object.values(row).forEach((value, i) => {
        request.input(i, value);
      });
      await request.query(`INSERT INTO ${tableName} (${columns}) VALUES (${placeholders})`);
    }

    res.status(200).json({ 
      message: `${excelData.length} rows imported successfully`,
      filename: req.file.filename, // Use this for download requests
      originalName: req.file.originalname
    });
  } catch (err) {
    console.error('Import failed:', err);
    res.status(500).json({ message: 'Import failed', error: err.message });
  }
});

Create an endpoint to let users download the uploaded Excel file using the filename returned from the upload endpoint:

// Download endpoint
app.get('/download/:filename', (req, res) => {
  const filename = req.params.filename;
  const filePath = path.join(uploadDir, filename);

  if (!fs.existsSync(filePath)) {
    return res.status(404).json({ message: 'File not found' });
  }

  // Set headers to force download with the original filename
  const originalName = filename.split('-').slice(1).join('-');
  res.setHeader('Content-Disposition', `attachment; filename="${originalName}"`);
  res.download(filePath);
});

Add this to the end to launch your server:

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on port ${PORT}`);
});

• Security: Always use parameterized queries (like we did with request.input()) to prevent SQL injection. Never concatenate user-provided data into SQL strings.
• Validation: Add extra checks for Excel data (e.g., required fields, data type validation) before inserting into the database.
• Cleanup: Set up a cron job to delete old files from the uploads folder periodically to avoid filling up disk space.
• Error Handling: Expand error handling to cover cases like database connection failures, invalid Excel formats, and large file uploads.
• Frontend Integration: If using a frontend, send a multipart/form-data request to /upload with the file field named excelFile, then use the returned filename to call /download/:filename.

内容的提问来源于stack exchange，提问作者ganesan s