php excel导入mysql数据库

require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

$inputFileName = 'path/to/your/file.xlsx';
$spreadsheet = IOFactory::load($inputFileName);
$worksheet = $spreadsheet->getActiveSheet();

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    foreach ($cellIterator as $cell) {
        echo $cell->getValue() . "\t";
    }
    echo "\n";
}

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("连接失败: " . $conn->connect_error);
}

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    $data = [];
    foreach ($cellIterator as $cell) {
        $data[] = $cell->getValue();
    }

    $sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("sss", $data[0], $data[1], $data[2]);
    $stmt->execute();
}

$stmt->close();
$conn->close();

$conn->begin_transaction();

foreach ($worksheet->getRowIterator() as $row) {
    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false);
    $data = [];
    foreach ($cellIterator as $cell) {
        $data[] = $cell->getValue();
    }

    $sql = "INSERT INTO table_name (column1, column2, column3) VALUES (?, ?, ?)";
    $stmt = $conn->prepare($sql);
    $stmt->bind_param("sss", $data[0], $data[1], $data[2]);
    $stmt->execute();
}

$conn->commit();