注册登录

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

日常开发中，我们经常遇到这样的场景，需要将一个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：

202203131824002390220000

然后将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。

计算机