如何使用PHP将Excel表格导入到MySQL数据库？

2019年09月18日 17:31:48 黎建湛
原文链接 ：
链接：http://note.youdao.com/noteshare?id=b0287ac7f2e612a6ae4bc6f7be5f416a&sub=C7C62B8099F9419CBE711F447181F5A3

项目需求：需要将一个Excel表格数据如客户信息、学生成绩表导入到系统数据库中，然后在系统中进行进一步操作，如给导入的客户群发短信，统计学生成绩排名。PHP导入Excel避免了人工录入信息的麻烦和出错，提高效率。
环境支持：php7.0版本以上,Mysql5.5以上，Nginx网站服务器
开发须知：① 数据量过大，导致服务器出现异常，需要缓解服务器处理压力
② 读取与写入的数据条数需要确定下来
③ 数据表中字段标识，重复的数据插入返回错误码和信息
④ 数据库使用InnoDB引擎，开启处理事务
开发准备：
① composer下载并安装
② 开发框架（Thinkphp\Laravel）
③ 插件 PhpSpreadsheet
④ 需要导入的Excel表

步骤：

1. 首先，安装composer包管理工具,官网地址：https://www.phpcomposer.com/
   
2. 利用composer包安装PHP框架，这里不详细说了
3. 下载 phpspreadsheet 输入命令 composer require phpoffice/phpspreadsheet
   
4. 在框架内部生成composer.json文件，告诉composer您的项目所依赖的包，稍微注意一下你项目的插件位置
   
5. 开始数据库表的创建工作，首先我们需要准备一张MySQL表，表名business_charges，表结构如下：
   
6. 然后将Excel文件放置程序应用目录下。当然，实际应用中，我们一般通过web上传到服务器指定目录下，然后再进行导入数据库操作。所以我取得是上传到服务器上得文件目录名称，当然你们可以使用$_FILE超全局变量来对xlsx文档得获取。
7. 准备工作做好后，我们来开始导入。使用PhpSpreadsheet将读取的Excel表格中的有用信息批量地插入到MySQL表。创建一个控制器 ExcelController.php。
   PHP源码：

在这里插入代码片
<?php
namespace app\controller\admin;

use app\annotation\GetMapping;
use app\annotation\JwtAuth;
use app\annotation\PostMapping;
use app\annotation\RequestParam;
use app\controller\BaseController;
use app\exception\ApiException;
use app\util\ValidationUtils;
use Illuminate\Database\Capsule\Manager as Db;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class ExcelController extends BaseController {
    /**
     * 导入excel表
     *
     * @PostMapping("/admin/imports")
     * @JwtAuth("admin")
     * @RequestParam(name="xlsxFile",type="JsonObject")
     * @param string $xlsxFile
     */
    public function imports($xlsxFile)
    {
        // 超时提示错误
        set_time_limit(600);
        ini_set('memory_limit', '2048M');

        ValidationUtils::validate(['xlsxFile'=>$xlsxFile],[
            'xlsxFile@StrNotEmpty|>>>:请上传文件'
        ]);

        // 处理文件目录
        $xlsxFile = _ROOT_."/{$xlsxFile}";

        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $spreadsheet = $reader->load($xlsxFile);
        $worksheet = $spreadsheet->getActiveSheet();

        // 总行数
        $hignestRow = $worksheet->getHighestRow();
        // 总列数
        $highestColumn = $worksheet->getHighestColumn();
        $lines = $hignestRow - 2;

        if($lines <= 0){
            throw new ApiException("Excel表格中没有数据");
        }

        if($hignestRow >= 500){
            throw new ApiException("数据量过大，请删除些数据");
        }

        // 定义数组
        $data = [];

        for($row = 2; $row <= $hignestRow; ++$row){
            $phoneNumber = $worksheet->getCellByColumnAndRow(3,$row)->getValue();
            $Nowscharges = $worksheet->getCellByColumnAndRow(7,$row)->getValue();
            $list = Db::table('business_charges')->where('phoneNum','=',$phoneNumber)->get();
            $setList = $list->toArray();
            $listPhoneNum = $setList->phoneNum;
                $data[] = [
                    'businessAccount' => $worksheet->getCellByColumnAndRow(1,$row)->getValue(),
                    'businessName' => $worksheet->getCellByColumnAndRow(2,$row)->getValue(),
                    'phoneNum' => $phoneNumber,
                    'agentCompany' => $worksheet->getCellByColumnAndRow(4,$row)->getValue(),
                    'orderNum' => $worksheet->getCellByColumnAndRow(5,$row)->getValue(),
                    'orderMoney' => $worksheet->getCellByColumnAndRow(6,$row)->getValue(),
                    'charges' => $Nowscharges,
                    'human' => $worksheet->getCellByColumnAndRow(8,$row)->getValue(),
                    'ftime' => $worksheet->getCellByColumnAndRow(9,$row)->getValue(),
                    'jtime' => $worksheet->getCellByColumnAndRow(10,$row)->getValue(),
                    'activeTag' => $worksheet->getCellByColumnAndRow(11,$row)->getValue(),
                    'remarks' => $worksheet->getCellByColumnAndRow(12,$row)->getValue(),
                    'createTime' => date('Y-m-d H:i:s')
                ];
        }

        // 开启事务
        Db::beginTransaction();

        $res = Db::table('business_charges')->insert($data);

        if($res){
            // 提交事务
            Db::commit();
            throw new ApiException("导入成功");
        }else{
            // 回滚事务
            Db::rollback();
            throw new ApiException("导入失败");
        }

        // 删除临时文件
        unlink($xlsxFile);

    }

8. $worksheet->getCellByColumnAndRow($col, $row)->getValue()可以获取表格中任意单元格数据内容，$col表示单元格所在的列，以数字表示，A列表示第一列，$row表示所在的行。最后使用sql语句将其导入到数据库中。
9. 导入成功后，返回提示成功信息