How to import data from PHP Excel to MySQL database-PHP Tutorial-php.cn

How to import data from PHP Excel to MySQL database

不言

Release： 2023-04-02 10:46:01

Original

11315 people have browsed it

This article mainly introduces the method of importing data from PHP Excel to MySQL database. It has certain reference value. Now I share it with you. Friends in need can refer to it

Recommended related mysql video tutorials: "mysql tutorial"

The data export is already available, how can there be no data import? Also using the TP5 framework, you first need to download phpexcel .zip, put it in the vendor directory of the third-party library directory, and then there is a page that allows you to select the Excel file you want to import, and then click the import button to call the interface:

The data in my template is as follows:

The back-end interface code is as follows:

    public function uplExcel(Request $request)
    {
        if (!empty($_FILES[&#39;excel&#39;][&#39;name&#39;])) {
            $fileName = $_FILES[&#39;excel&#39;][&#39;name&#39;];    //得到文件全名
            $dotArray = explode(&#39;.&#39;, $fileName);    //把文件名安.区分，拆分成数组
            $type = end($dotArray);

            if ($type != "xls" && $type != "xlsx") {
                $ret[&#39;res&#39;] = "0";
                $ret[&#39;msg&#39;] = "不是Excel文件，请重新上传!";
                return json_encode($ret);
            }

            //取数组最后一个元素，得到文件类型
            $uploaddir = "../uploads2/" . date("Y-m-d") . &#39;/&#39;;//设置文件保存目录 注意包含
            if (!file_exists($uploaddir)) {
                mkdir($uploaddir, 0777, true);
            }

            $path = $uploaddir . md5(uniqid(rand())) . &#39;.&#39; . $type; //产生随机文件名
            //$path = "images/".$fileName; //客户端上传的文件名；
            //下面必须是tmp_name 因为是从临时文件夹中移动
            move_uploaded_file($_FILES[&#39;excel&#39;][&#39;tmp_name&#39;], $path); //从服务器临时文件拷贝到相应的文件夹下

            $file_path = $path;
            if (!file_exists($path)) {
                $ret[&#39;res&#39;] = "0";
                $ret[&#39;msg&#39;] = "上传文件丢失!" . $_FILES[&#39;excel&#39;][&#39;error&#39;];
                return json_encode($ret);
            }

            //文件的扩展名
            $ext = strtolower(pathinfo($path, PATHINFO_EXTENSION));
            if ($ext == &#39;xlsx&#39;) {
                $objReader = \PHPExcel_IOFactory::createReader(&#39;Excel2007&#39;);
                $objPHPExcel = $objReader->load($file_path, &#39;utf-8&#39;);
            } elseif ($ext == &#39;xls&#39;) {
                $objReader = \PHPExcel_IOFactory::createReader(&#39;Excel5&#39;);
                $objPHPExcel = $objReader->load($file_path, &#39;utf-8&#39;);
            }

            $sheet = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestRow(); // 取得总行数
            $highestColumn = $sheet->getHighestColumn(); // 取得总列数
            $ar = array();
            $i = 0;
            $importRows = 0;
            for ($j = 2; $j <= $highestRow; $j++) {
                $importRows++;

                $realName = (string)$objPHPExcel->getActiveSheet()->getCell("A$j")->getValue();//需要导入的realName
                $phone = (string)$objPHPExcel->getActiveSheet()->getCell("B$j")->getValue();   //需要导入的phone
                $company = (string)$objPHPExcel->getActiveSheet()->getCell("C$j")->getValue(); //需要导入的company
                $job = (string)$objPHPExcel->getActiveSheet()->getCell("D$j")->getValue();     //需要导入的job
                $email = (string)$objPHPExcel->getActiveSheet()->getCell("E$j")->getValue();   //需要导入的email
                $ret[&#39;mdata&#39;] = $this->addMemb($phone, $realName, $company, $job, $email);//这里就是我的数据库添加操作定义的一个方法啦,对应替换为自己的

                if ($ret[&#39;mdata&#39;] && !is_Bool($ret[&#39;mdata&#39;])) {
                    $ar[$i] = $ret[&#39;mdata&#39;];
                    $i++;
                }
            }
            if ($i > 0) {
                $ret[&#39;res&#39;] = "0";
                $ret[&#39;errNum&#39;] = $i;
                $ret[&#39;allNum&#39;] = $importRows;
                $ret[&#39;sucNum&#39;] = $importRows - $i;
                $ret[&#39;mdata&#39;] = $ar;
                $ret[&#39;msg&#39;] = "导入完毕!";
                return json_encode($ret);
            }
            $ret[&#39;res&#39;] = "1";
            $ret[&#39;allNum&#39;] = $importRows;
            $ret[&#39;errNum&#39;] = 0;
            $ret[&#39;sucNum&#39;] = $importRows;
            $ret[&#39;mdata&#39;] = "导入成功!";
            return json_encode($ret);
        } else {
            $ret[&#39;res&#39;] = "0";
            $ret[&#39;msg&#39;] = "上传文件失败!";
            return json_encode($ret);
        }
    }

Copy after login

The above is the entire content of this article, I hope it will be useful for everyone's learning For help, please pay attention to the PHP Chinese website for more related content!

Related recommendations:

PHP allows the arrays with the same value to form a new array instance to explain

Class static calls and Differences between range resolution operators

The above is the detailed content of How to import data from PHP Excel to MySQL database. For more information, please follow other related articles on the PHP Chinese website!