PHP reads Excel file content and writes it to database

 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
 twenty one
 twenty two
 twenty three
 twenty four
 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);

 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" : "判断"
 } ]