tp5.1使用phpexecel导入数据到数据库

本文链接：https://blog.youkuaiyun.com/wanlainet/article/details/107185190

tp5.1文件引入各种坑，花费一下午时间终于搞定了

1.phpexcel的下载与安装

运行：composer require phpoffice/phpexcel

2、PHPexcel的引用

在需要引用PHPexcel这个第三方类库的当前文件的顶部直接调用（切记，5.1已经弃用了vendor方法和load::import方法），以下五个类需要同时引用，我由于只引用了前面两个，导致花费了很长时间没找到原因！
use PHPExcel;
use PHPExcel_IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

在TP5.1中，导入第三方包在vendor文件夹中，先用compose require 加包成功，再use引用，再new才能成功，在前面不声明use是不能加载第三方包的，除非你要使用require或者include那另外说！

以下是控制器Upload类完整代码：

<?php

namespace app\index\controller;

use think\Controller;
use think\facade\Env;

use PHPExcel;
use PHPExcel_IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
class Upload extends Controller
{
    public function index(){
        return view();
    }
    public function save()
    {
        ini_set('memory_limit','2048M');
        header("content-type:text/html;charset=utf-8");
        //上传excel文件
        $file = request()->file('file');
        $files = $_FILES['file'];
        //将文件保存到public/uploads目录下面
        $info = $file->validate(['size'=>10485760000000,'ext'=>'xls,xlsx'])->move( './uploads');
        if($info){
            //获取上传到后台的文件名
            $fileName = $info->getSaveName();
            //获取文件路径
            $filePath = Env::get('root_path').'public'.DIRECTORY_SEPARATOR.'uploads'.DIRECTORY_SEPARATOR.$fileName;
            //获取文件后缀
            $suffix = $info->getExtension();
            //判断哪种类型
            if($suffix=="xlsx"){
                $reader = \PHPExcel_IOFactory::createReader('Excel2007');
            }else{
                $reader = \PHPExcel_IOFactory::createReader('Excel5');
            }
        }else{
            $this->error('文件过大或格式不正确导致上传失败-_-!');
        }
        $objContent = $reader -> load($files['tmp_name']);
        $sheetContent = $objContent -> getSheet(0) -> toArray();
        unset($sheetContent[0]);
        foreach ($sheetContent as $k => $v){
            $arr['word'] = $v[0];
            $arr['pinyin'] = $v[1];
            $arr['zhuyin'] = $v[2];
            $arr['cixing'] = $v[3];
            $arr['explanation'] = $v[4];
            $arr['more'] = $v[5];
            $res[] = $arr;
        }
        $res = db('article_ciyu') -> insertAll($res);
        if($res){
            echo "成功";
        }else{
            echo "失败";
        }
    }

}

以下是前端代码：

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>php导入execl到数据库</title>
</head>
<body>
<form action="{:url('save')}" method="post" enctype="multipart/form-data">
    <div class="time-add clearfix">
        <a class="dianji" href="javascript:;">
            <input type="file" name="file" id="fileupload" class="inputfile" style="position: absolute;display: none;"/>
            <label class="span" for="fileupload">批量导入</label>
            <input type="submit" name="submit" class="submit" value="提交" />
        </a>
    </div>
</form>

</body>
</html>

亲测，可以完美将execel表格数据导入到mysql数据库！