PHP 读取 Excel 文件内容并写入数据库

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

// 连接数据库
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "question";
 
$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// 读取excel文件
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load("question.xlsx");

$array = [];
// 循环遍历所有 Sheet
// [0]=题目 [1]=A [2]=B [3]=C [4]=D ... 最后一项为正确答案
foreach ($spreadsheet->getAllSheets() as $sheet) {
    $sheetName = $sheet->getTitle();
    $sheetData = $sheet->toArray();
    $sheetData = array_slice($sheetData, 1); // 去掉第一行标题
    $sheetData = array_filter($sheetData, function ($item) {
        return !empty($item[0]);
    }); // 去掉空行
    foreach ($sheetData as $item) {
        // 最后一个为选项
        $options = array_slice($item, 1, -1);
        $options = array_filter($options, function ($item) {
            return !empty($item);
        }); // 去掉空选项
        $array[] = [
            'question' => $item[0],
            'options' => $options,
            // 每个字符都进行分割，主要为了分割出多选
            'answer' => str_split($item[count($item) - 1]),
            'type' => $sheetName
        ];
    }
}

// 插入数据库
foreach ($array as $item) {
    $question = $item['question'];
    $options = addslashes(json_encode($item['options']));
    $answer = addslashes(json_encode($item['answer']));
    $type = $item['type'];
    $sql = "INSERT INTO `questions` (`title`, `options`, `answer`, `type`) VALUES ('$question', '$options', '$answer', '$type')";
    $conn->query($sql);
}

// 查询数据库
$jsonData = [];
$sql = "SELECT * FROM `questions`";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $row['options'] = json_decode($row['options'], true);
        $row['answer'] = json_decode($row['answer'], true);
        $jsonData[] = $row;
    }
}
echo $Json = json_encode($jsonData,JSON_PRETTY_PRINT|JSON_UNESCAPED_UNICODE);