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

原创文章 作者:月光光 2018年06月09日 11:23helloweba.net 标签:PHP 

日常开发中,我们经常遇到这样的场景,需要将一个Excel表格数据如客户信息、学生成绩表导入到系统数据库中,然后在系统中进行进一步操作,如给导入的客户群发短信,统计学生成绩排名。PHP导入Excel避免了人工录入信息的麻烦和出错,提高效率。

本文以导入学生成绩表为例,给大家讲解使用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 <= $highestRow; ++$row) {
    $name = $worksheet->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)

数据库连接文件已打包在源代码中,欢迎下载。

下一节,我继续给大家讲解导出Excel。

声明:本文为原创文章,helloweba.net和作者拥有版权,如需转载,请注明来源于helloweba.net并保留原文链接:https://www.helloweba.net/php/562.html

0条评论