如何用PHP将Excel成对列合并后插入MySQL对应表列

Hey there! Let's walk through how to get your Excel data into that MySQL table exactly how you need it. I'll break this down into easy-to-follow steps, including updating that outdated PHP code you started with.

Let's make sure we're aligned on how Excel columns translate to your MySQL table:

• Excel's lon → MySQL's cord1; Excel's lat → MySQL's cord2
• Pair up columns like x,x1 and y,y1, then merge them into a single column formatted as x,x1/y,y1
• Columns like site1, site2, name1, name2 stay as-is, no changes needed

First, we need to build a table that matches your required structure. Since some column names have special characters (like / and ,), we'll wrap them in backticks to avoid syntax errors. Here's an example SQL statement:

CREATE TABLE your_target_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cord1 DECIMAL(10,6) NOT NULL, -- Ideal for longitude values
    cord2 DECIMAL(10,6) NOT NULL, -- Ideal for latitude values
    site1 VARCHAR(50),
    site2 VARCHAR(50),
    `x,x1/y,y1` VARCHAR(50),
    `x,x2/y,y2` VARCHAR(50),
    name1 VARCHAR(50),
    name2 VARCHAR(50),
    `x,x3/y,y3` VARCHAR(50),
    `x,x4/y,y4` VARCHAR(50),
    name3 VARCHAR(50),
    name4 VARCHAR(50),
    -- Add any remaining columns following the same pattern
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Adjust the data types (like VARCHAR length) to match your actual data needs.

Your original code uses the deprecated mysql_* functions — these were removed in PHP 7, so we'll switch to mysqli (the modern replacement) for safety and compatibility. We'll also use PhpSpreadsheet (a popular Excel reading library) to handle the Excel file.

3.1 Set Up PhpSpreadsheet #

First, install the library via Composer (if you don't have Composer, grab it from getcomposer.org):

composer require phpoffice/phpspreadsheet

3.2 Full Working Code #

<?php
require 'vendor/autoload.php'; // Load PhpSpreadsheet

use PhpOffice\PhpSpreadsheet\IOFactory;

// Database connection details
$hostname = "localhost";
$database = "DB";
$username = "root";
$password = "";

// Establish mysqli connection
$conn = new mysqli($hostname, $username, $password, $database);

// Check for connection errors
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Path to your Excel file
$excelFile = "your_excel_data.xlsx";
$spreadsheet = IOFactory::load($excelFile);
$worksheet = $spreadsheet->getActiveSheet();
$totalRows = $worksheet->getHighestRow();

// Loop through each row (start at row 2 to skip the header)
for ($row = 2; $row <= $totalRows; $row++) {
    // Read base columns (adjust cell letters to match your Excel's column order)
    $cord1 = $worksheet->getCell('A' . $row)->getValue();
    $cord2 = $worksheet->getCell('B' . $row)->getValue();
    $site1 = $worksheet->getCell('C' . $row)->getValue();
    $site2 = $worksheet->getCell('D' . $row)->getValue();

    // Merge x/y pairs into the required format
    $xX1 = $worksheet->getCell('E' . $row)->getValue();
    $yY1 = $worksheet->getCell('F' . $row)->getValue();
    $xX1yY1 = $xX1 . "/" . $yY1;

    $xX2 = $worksheet->getCell('G' . $row)->getValue();
    $yY2 = $worksheet->getCell('H' . $row)->getValue();
    $xX2yY2 = $xX2 . "/" . $yY2;

    $name1 = $worksheet->getCell('I' . $row)->getValue();
    $name2 = $worksheet->getCell('J' . $row)->getValue();

    $xX3 = $worksheet->getCell('K' . $row)->getValue();
    $yY3 = $worksheet->getCell('L' . $row)->getValue();
    $xX3yY3 = $xX3 . "/" . $yY3;

    $xX4 = $worksheet->getCell('M' . $row)->getValue();
    $yY4 = $worksheet->getCell('N' . $row)->getValue();
    $xX4yY4 = $xX4 . "/" . $yY4;

    $name3 = $worksheet->getCell('O' . $row)->getValue();
    $name4 = $worksheet->getCell('P' . $row)->getValue();

    // Prepare insert query (use backticks for special column names)
    $sql = "INSERT INTO your_target_table 
            (cord1, cord2, site1, site2, `x,x1/y,y1`, `x,x2/y,y2`, name1, name2, `x,x3/y,y3`, `x,x4/y,y4`, name3, name4)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    // Use prepared statements to prevent SQL injection
    $stmt = $conn->prepare($sql);
    // Bind parameters: 'd' = decimal, 's' = string (match your table's data types)
    $stmt->bind_param("ddssssssssss", $cord1, $cord2, $site1, $site2, $xX1yY1, $xX2yY2, $name1, $name2, $xX3yY3, $xX4yY4, $name3, $name4);

    // Execute the insert
    if ($stmt->execute()) {
        echo "Row " . $row . " inserted successfully<br>";
    } else {
        echo "Failed to insert row " . $row . ": " . $stmt->error . "<br>";
    }

    $stmt->close();
}

// Clean up connections
$conn->close();
?>

3.3 Key Notes #

• Excel Column Alignment: Double-check the cell letters (A, B, C, etc.) match your actual Excel file's column order. If your headers are in a different sequence, adjust these accordingly.
• SQL Injection Protection: We use prepared statements to avoid security risks — never directly concatenate user/excel data into SQL queries.
• Deprecated Functions: The old mysql_* functions are gone, so mysqli is the way to go for modern PHP versions.

If you don't want to write code, you can manually prepare the data in Excel first:

1. Add new columns next to your x,xn and y,yn pairs. For example, in cell G1, enter =E1&"/"&F1 (if E is x,x1 and F is y,y1), then drag the formula down to all rows.
2. Delete the original x,xn and y,yn columns, leaving only the merged ones.
3. Save the file as a CSV.
4. Import the CSV into MySQL using LOAD DATA INFILE:

LOAD DATA INFILE 'your_prepared_data.csv'
INTO TABLE your_target_table
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS -- Skip the header row
(cord1, cord2, site1, site2, `x,x1/y,y1`, `x,x2/y,y2`, name1, name2, `x,x3/y,y3`, `x,x4/y,y4`, name3, name4);

This is great for small datasets where manual work is feasible.

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