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

2022年11月15日技术约 912 字大概 4 分钟

近期遇到了一个需求，需要将 Excel类型 的题库导入数据库中（具体格式如下图所示），百度了一番找到了一个合适的库 PhpSpreadsheet

PhpSpreadsheet 是一个用纯 PHP 编写的库，它提供了一组类，允许您读取和写入各种电子表格文件格式，例如 Excel 和 LibreOffice Calc。

数据库

先设计一下数据库， id ， 题目 ， 答案 ， 类型 肯定这四个是必须的。

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `questions`;
CREATE TABLE `questions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `options` text NOT NULL,
  `answer` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

安装

1	`composer require phpoffice/phpspreadsheet`

代码

关键的地方都写了注释，这里抛砖引玉各位可以根据实际情况进行修改

<?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);

示例

插入 数据库 的数据如下所示

id	title	options	answer	type
1	首次将椭圆曲线用于密码学，建立公开密钥加密的演算法是在那一年？	[“1982\u5e74”,”1985\u5e74”,”1990\u5e74”,”1992\u5e74”]	[“B”]	单选
2	从长远来看，区块链的应用可以分为（）阶段。	[“\u53ef\u4fe1\u534f\u540c\u5e73\u53f0”,”\u4e1a\u52a1\u96c6\u4e2d\u5904\u7406”,”\u6807\u51c6\u4ef7\u503c\u4f20\u8f93\u9636\u6bb5”,”\u5206\u5e03\u5f0f\u5546\u4e1a\u57fa\u7840\u8bbe\u65bd”]	[“A”,”C”,”D”]	多选
3	云计算是大数据存储和分析的重要基础设施。	[“\u5bf9”,”\u9519”]	[“A”]	判断

查询返回的 json 数据如下所示

[{
	"id": "1",
	"question": "首次将椭圆曲线用于密码学，建立公开密钥加密的演算法是在那一年？",
	"options": ["1982年", "1985年", "1990年", "1992年"],
	"answer": ["B"],
	"type": "单选"
}, {
	"id": "2",
	"question": "从长远来看，区块链的应用可以分为（ ）阶段。",
	"options": ["可信协同平台", "业务集中处理", "标准价值传输阶段", "分布式商业基础设施"],
	"answer": ["A", "C", "D"],
	"type": "多选"
}, {
	"id": "3",
	"question": "云计算是大数据存储和分析的重要基础设施。",
	"options": ["对", "错"],
	"answer": ["A"],
	"type": "判断"
}]