pecl install xlswriter

extension=xlswriter.so

memory_limit = 1024M
max_execution_time = 3600

<?php
// 1. 初始化Excel對(duì)象
$config = ['path' => '/tmp']; // 臨時(shí)目錄（可選）
$excel = new \Vtiful\Kernel\Excel($config);
$file = $excel->fileName('export.xlsx')->header(['ID', 'Name', 'Email']);
 
// 2. 設(shè)置HTTP頭直接下載
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="export.xlsx"');
header('Cache-Control: max-age=0');
$file->output();
 
// 3. 連接數(shù)據(jù)庫(kù)
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
 
// 4. 分頁(yè)查詢并寫入數(shù)據(jù)
$pageSize = 10000; // 每頁(yè)數(shù)據(jù)量
$page = 1;
do {
    $offset = ($page - 1) * $pageSize;
    $stmt = $pdo->prepare("SELECT id, name, email FROM users LIMIT :offset, :limit");
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->bindValue(':limit', $pageSize, PDO::PARAM_INT);
    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
    if (empty($data)) {
        break;
    }
 
    // 寫入當(dāng)前頁(yè)數(shù)據(jù)
    foreach ($data as $row) {
        $file->data([$row['id'], $row['name'], $row['email']]);
    }
 
    $page++;
    ob_flush(); // 刷新輸出緩沖區(qū)
    flush();
} while (true);
 
// 5. 結(jié)束寫入
$file->output();

<?php
// 1. 上傳文件處理
$uploadFile = $_FILES['file']['tmp_name'];
if (!is_uploaded_file($uploadFile)) {
    die('非法文件');
}
 
// 2. 初始化Excel讀取器
$excel = new \Vtiful\Kernel\Excel();
$excel->openFile($uploadFile);
$sheet = $excel->getSheet();
 
// 3. 連接數(shù)據(jù)庫(kù)
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$pdo->beginTransaction();
 
// 4. 分塊讀取并插入
$batchSize = 5000; // 每批插入量
$batchData = [];
$currentRow = 0;
 
try {
    while ($row = $sheet->nextRow()) {
        $currentRow++;
        if ($currentRow === 1) {
            continue; // 跳過(guò)標(biāo)題行
        }
 
        // 數(shù)據(jù)校驗(yàn)（示例）
        if (empty($row[1]) || !filter_var($row[2], FILTER_VALIDATE_EMAIL)) {
            error_log("Invalid data at row $currentRow: " . json_encode($row));
            continue;
        }
 
        // 構(gòu)建批量插入數(shù)據(jù)
        $batchData[] = [
            'id' => $row[0],
            'name' => $row[1],
            'email' => $row[2]
        ];
 
        // 批量插入
        if (count($batchData) >= $batchSize) {
            insertBatch($pdo, $batchData);
            $batchData = [];
        }
    }
 
    // 插入剩余數(shù)據(jù)
    if (!empty($batchData)) {
        insertBatch($pdo, $batchData);
    }
 
    $pdo->commit();
    echo "導(dǎo)入成功！";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "導(dǎo)入失敗: " . $e->getMessage();
}
 
// 批量插入函數(shù)
function insertBatch($pdo, $data) {
    $sql = "INSERT INTO users (id, name, email) VALUES ";
    $values = [];
    $placeholders = [];
    foreach ($data as $item) {
        $values[] = $item['id'];
        $values[] = $item['name'];
        $values[] = $item['email'];
        $placeholders[] = '(?, ?, ?)';
    }
    $sql .= implode(', ', $placeholders);
    $stmt = $pdo->prepare($sql);
    $stmt->execute($values);
}

innodb_buffer_pool_size = 2G
innodb_flush_log_at_trx_commit = 0

$file = $excel->fileName('export.xlsx')->setCompressionLevel(6);