使用PhpSpreadsheet将Excel导入到MySQL数据库

学习笔记 2018/06/12 PHP, MYSQL

本文以导入学生成绩表为例,给大家讲解使用PhpSpreadsheet将Excel导入的MySQL数据库。

准备

首先我们需要准备一张MySQL表,表名t_student,表结构如下:

CREATE TABLE `t_student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL COMMENT \'姓名\',
  `chinese` int(6) NOT NULL DEFAULT \'0\' COMMENT \'语文\',
  `maths` int(6) NOT NULL DEFAULT \'0\' COMMENT \'数学\',
  `english` int(6) NOT NULL DEFAULT \'0\' COMMENT \'外语\',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

这是一张学生成绩表,用来存储学生的姓名和语数外三门课程的成绩。

接着,我们准备excel表格文件,我特意制作了一个Excel文件students.xlsx:

\"\"/

然后将Excel文件放置程序应用目录下。当然,实际应用中,我们一般通过web上传到服务器指定目录下,然后再进行导入数据库操作。本文web上传这块就不涉及了,感兴趣的同学可以参考本站文章:Dropzone.js实现文件拖拽上传,功能强大的文件上传组件-WebUploader。

最后,你还需要安装好PhpSpreadsheet,在前一节文章中有介绍,非常简单:使用PhpSpreadsheet读取和写入Excel

导入Excel

准备工作做好后,我们来开始导入。

思路很简单:使用PhpSpreadsheet读取Excel表格中的有用信息,然后组装成sql语句,最后批量插入到MySQL表中。我把代码贴出来。

require \'vendor/autoload.php\';

include(\'conn.php\'); //连接数据库

$reader = \\PhpOffice\\PhpSpreadsheet\\IOFactory::createReader(\'Xlsx\');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load(\'students.xlsx\'); //载入excel表格

$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = \\PhpOffice\\PhpSpreadsheet\\Cell\\Coordinate::columnIndexFromString($highestColumn); // e.g. 5

$lines = $highestRow - 2; 
if ($lines <= 0) {
    exit(\'Excel表格中没有数据\');
}

$sql = "INSERT INTO `t_student` (`name`, `chinese`, `maths`, `english`) VALUES ";

for ($row = 3; $row getCellByColumnAndRow(1, $row)->getValue(); //姓名
    $chinese = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //语文
    $maths = $worksheet->getCellByColumnAndRow(3, $row)->getValue(); //数学
    $english = $worksheet->getCellByColumnAndRow(4, $row)->getValue(); //外语

    $sql .= "(\'$name\',\'$chinese\',\'$maths\',\'$english\'),";
}
$sql = rtrim($sql, ","); //去掉最后一个,号
try {
    $db->query($sql);
    echo \'OK\';
} catch (Exception $e) {
    echo $e->getMessage();
}

$worksheet->getCellByColumnAndRow($col, $row)->getValue()可以获取表格中任意单元格数据内容,$col表示单元格所在的列,以数字表示,A列表示第一列,$row表示所在的行。

我们只需要第三行以后的数据,因此直接从第三行开始循环遍历,获取成绩,组装成SQL语句。

我们使用批量插入MySQL语句,当然你也可以逐条插入,但是效率没有批量插入高。

最后执行导入代码,你会发现数据表里有数据了:

MariaDB [demo]> select * from t_student;
+----+-----------+---------+-------+---------+
| id | name      | chinese | maths | english |
+----+-----------+---------+-------+---------+
| 13 | 王二小    |      82 |    78 |      65 |
| 14 | 李万豪    |      68 |    87 |      79 |
| 15 | 张三丰    |      89 |    90 |      98 |
| 16 | 王老五    |      68 |    81 |      72 |
+----+-----------+---------+-------+---------+
4 rows in set (0.00 sec)


本文地址:https://www.stayed.cn/item/96

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见,何事秋风悲画扇。