如何用Node.js将Excel文件导入MySQL？求参考资料与示例代码

Sure thing! Importing Excel data into MySQL using Node.js is a straightforward task once you have the right tools. Here's everything you need to know:

Recommended Node.js Modules #

• SheetJS (xlsx): The industry-standard library for reading/writing Excel files (supports .xlsx, .xls, .csv, and more). It’s lightweight, fast, and handles most common Excel use cases.
• mysql2: A modern, promise-based alternative to the older mysql module. It offers better performance and integrates seamlessly with async/await syntax for cleaner code.
• exceljs: A great pick if you need advanced Excel features (like streaming large files, styling, or reading/writing complex formulas).

Step-by-Step Example Code #

Let’s walk through a complete working example using xlsx and mysql2:

1. Set Up Your Project #

First, initialize your project and install dependencies:

npm init -y
npm install xlsx mysql2

2. Prepare Your Excel File #

Assume your Excel file (data.xlsx) has a sheet named "Customers" with columns: customer_id, full_name, email, phone.

3. Import Script Code #

Create a file excel-to-mysql.js:

const xlsx = require('xlsx');
const mysql = require('mysql2/promise');

// Configuration
const EXCEL_PATH = './data.xlsx';
const DB_CREDENTIALS = {
  host: 'localhost',
  user: 'your_db_user',
  password: 'your_db_password',
  database: 'your_database_name'
};

async function importData() {
  try {
    // 1. Read and parse the Excel file
    const workbook = xlsx.readFile(EXCEL_PATH);
    const targetSheet = workbook.SheetNames[0]; // Use first sheet
    const sheetData = workbook.Sheets[targetSheet];
    
    // Convert sheet data to JSON format
    const excelRows = xlsx.utils.sheet_to_json(sheetData);
    console.log(`Loaded ${excelRows.length} rows from Excel`);

    // 2. Connect to MySQL
    const dbConnection = await mysql.createConnection(DB_CREDENTIALS);
    console.log('Successfully connected to MySQL');

    // 3. Create table (if it doesn't exist)
    await dbConnection.execute(`
      CREATE TABLE IF NOT EXISTS customers (
        customer_id INT PRIMARY KEY,
        full_name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        phone VARCHAR(20)
      )
    `);
    console.log('Customers table is ready');

    // 4. Bulk insert data (far more efficient than single inserts)
    if (excelRows.length > 0) {
      // Generate placeholders for bulk insert
      const placeholders = excelRows.map(() => '(?, ?, ?, ?)').join(', ');
      // Flatten row data into a single array for the query
      const insertValues = excelRows.flatMap(row => [
        row.customer_id, 
        row.full_name, 
        row.email, 
        row.phone
      ]);

      await dbConnection.execute(
        `INSERT INTO customers (customer_id, full_name, email, phone) VALUES ${placeholders}`,
        insertValues
      );

      console.log(`Successfully inserted ${excelRows.length} rows into MySQL`);
    }

    // Clean up: close the database connection
    await dbConnection.end();
  } catch (error) {
    console.error('Import failed:', error.message);
    process.exit(1);
  }
}

// Run the import process
importData();

4. Execute the Script #

node excel-to-mysql.js

Key Best Practices #

• Bulk Inserts: Always use bulk inserts instead of inserting one row at a time—this cuts down on database round-trips and drastically speeds up imports for large datasets.
• Data Validation: Add checks for required fields, valid data types, and duplicate entries before inserting to avoid database errors (e.g., check if an email already exists in the table).
• Stream Large Files: For Excel files with 10k+ rows, use exceljs’s streaming feature to avoid loading the entire file into memory at once.
• Error Handling: Extend the error handling to catch specific issues like missing Excel files, invalid database credentials, or duplicate primary keys.

Learning Resources #

• SheetJS Documentation: Covers all features of the xlsx library, including parsing different Excel formats and converting data to JSON.
• mysql2 Documentation: Details on connection management, query execution, and using async/await for database operations.
• Node.js MySQL Guides: Look for tutorials focused on bulk data insertion and data migration—these will deepen your understanding of efficient database interactions.

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