iami233
iami233
文章140
标签35
分类4
PHP读取Excel文件内容写入数据库

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

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

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

image-20221114171259581

数据库

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

1
2
3
4
5
6
7
8
9
10
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

代码

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
<?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);

示例

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

idtitleoptionsanswertype
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 数据如下所示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
[{
"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": "判断"
}]
本文作者:iami233
本文链接:https://5ime.cn/excel2sql.html
版权声明:本文采用 CC BY-NC-SA 3.0 CN 协议进行许可