Node.js新手求指导：xlsx/csv文件上传读写及Mongoose存储实现与可行性

Absolutely feasible—this is a common backend task, and we’ll walk through it step by step so you can build it as a Node.js beginner. Let’s break it down into manageable parts:

1. Set Up Your Project & Install Dependencies #

First, initialize a new Node.js project and install the packages we’ll need:

• express: Web framework to handle HTTP requests
• multer: Handles file uploads
• xlsx: Reads/writes XLSX files
• csv-parser + csv-writer: Reads/writes CSV files
• mongoose: Connects to MongoDB and defines schemas
• dotenv: Manages environment variables (optional but recommended)

Run these commands in your project folder:

npm init -y
npm install express multer xlsx csv-parser csv-writer mongoose dotenv

2. Configure File Upload with Multer #

We’ll use Multer to accept only XLSX/CSV files and store them temporarily (you can delete them after processing). Create a uploads folder in your project first—Multer won’t create it automatically.

Here’s the basic setup in your app.js:

require('dotenv').config();
const express = require('express');
const multer = require('multer');
const app = express();

// Configure Multer for file uploads
const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, './uploads/'); // Store files in the uploads folder
  },
  filename: (req, file, cb) => {
    cb(null, Date.now() + '-' + file.originalname); // Add timestamp to avoid filename conflicts
  }
});

// Filter to accept only XLSX/CSV files
const fileFilter = (req, file, cb) => {
  const allowedTypes = [
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    'text/csv'
  ];
  if (allowedTypes.includes(file.mimetype)) {
    cb(null, true);
  } else {
    cb(new Error('Only XLSX and CSV files are allowed!'), false);
  }
};

const upload = multer({ storage: storage, fileFilter: fileFilter });

// Test route to confirm server works
app.get('/', (req, res) => {
  res.send('File upload server running!');
});

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

3. Read Uploaded Files (XLSX/CSV) #

Next, create a route to handle uploads and read the file content. We’ll write separate logic for XLSX and CSV files.

Add this route to app.js:

const fs = require('fs');
const XLSX = require('xlsx');
const csv = require('csv-parser');

app.post('/upload', upload.single('file'), (req, res) => {
  if (!req.file) {
    return res.status(400).send('No file uploaded!');
  }

  const filePath = req.file.path;
  let fileData = [];

  // Handle XLSX files (synchronous processing)
  if (req.file.mimetype === 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') {
    const workbook = XLSX.readFile(filePath);
    const sheetName = workbook.SheetNames[0];
    const worksheet = workbook.Sheets[sheetName];
    fileData = XLSX.utils.sheet_to_json(worksheet); // Convert sheet to JSON
    processAndSaveData(fileData, req.file, res);
  }

  // Handle CSV files (asynchronous processing)
  else if (req.file.mimetype === 'text/csv') {
    fs.createReadStream(filePath)
      .pipe(csv())
      .on('data', (row) => fileData.push(row))
      .on('end', () => {
        processAndSaveData(fileData, req.file, res);
      });
  }
});

4. Set Up Mongoose Schema & Database Connection #

Now, let’s connect to MongoDB and define a schema for your data. Create a models folder and add DataModel.js:

const mongoose = require('mongoose');

// Define your schema based on the structure of your XLSX/CSV data
// Example: If your file has name, email, age columns
const dataSchema = new mongoose.Schema({
  name: { type: String, required: true },
  email: { type: String, required: true, unique: true },
  age: { type: Number, required: true },
  importedAt: { type: Date, default: Date.now }
});

module.exports = mongoose.model('Data', dataSchema);

Then, add the database connection to app.js (make sure you have a .env file with MONGODB_URI=your_mongodb_connection_string):

const mongoose = require('mongoose');

// Connect to MongoDB
mongoose.connect(process.env.MONGODB_URI)
  .then(() => console.log('Connected to MongoDB'))
  .catch(err => console.error('MongoDB connection error:', err));

5. Process, Edit & Save Data to Database #

Create the processAndSaveData function we referenced earlier. This function will let you edit the data before saving it to the database:

const Data = require('./models/DataModel');

async function processAndSaveData(fileData, file, res) {
  try {
    // Example: Clean up and edit data
    const editedData = fileData.map(item => ({
      name: item.name.trim(),
      email: item.email.toLowerCase().trim(),
      age: parseInt(item.age) || 0 // Ensure age is a number, default to 0 if invalid
    }));

    // Save all data to MongoDB (bulk insert for efficiency)
    await Data.insertMany(editedData);

    // Optional: Delete the uploaded file after processing to save space
    fs.unlinkSync(file.path);

    res.status(200).send(`Successfully imported ${editedData.length} entries to database!`);
  } catch (err) {
    console.error('Error processing data:', err);
    // Handle duplicate email errors specifically
    if (err.code === 11000) {
      return res.status(400).send('Duplicate email found in the file!');
    }
    res.status(500).send('Failed to process file and save data');
  }
}

6. Edit & Rewrite Files (Optional) #

If you need to edit the original file and save the changes, here’s how to do it for both file types:

Edit XLSX File #

// Example: Update the first data row's name
const workbook = XLSX.readFile(filePath);
const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];
worksheet['A2'].v = 'Updated Name'; // A2 corresponds to the second row (first data row)
XLSX.writeFile(workbook, `./uploads/edited-${file.originalname}`);

Edit CSV File #

const createCsvWriter = require('csv-writer').createObjectCsvWriter;

// Example: Write edited data to a new CSV file
const csvWriter = createCsvWriter({
  path: `./uploads/edited-${file.originalname}`,
  header: [
    {id: 'name', title: 'NAME'},
    {id: 'email', title: 'EMAIL'},
    {id: 'age', title: 'AGE'}
  ]
});

csvWriter.writeRecords(editedData)
  .then(() => console.log('Edited CSV file saved successfully'));

Key Tips for Beginners #

• Data Validation: Add more checks (like email format validation) before saving to avoid bad data in your database.
• File Size Limits: Add a limits option to Multer to prevent oversized files (e.g., limits: { fileSize: 1024 * 1024 * 5 } for a 5MB max).
• Async/Await: Stick with async/await for asynchronous operations to avoid messy callback chains.
• Error Handling: Expand error messages to be more specific—this will make debugging much easier.

内容的提问来源于stack exchange，提问作者Pradeep Maurya