数组数据导入大胜利？这个可以有！

今天在做excel数据导入，这个网上现成的案例很多，先上一段PHPExcel函数代码，需要的拿走不谢：

// 读取Excle表
    public function readexcel($filename, $encode = 'utf-8')
    {
        $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        $objReader->setReadDataOnly(true);
        $objPHPExcel        = $objReader->load($filename);
        $objWorksheet       = $objPHPExcel->getActiveSheet();
        $highestRow         = $objWorksheet->getHighestRow();
        $highestColumn      = $objWorksheet->getHighestColumn();
        $highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn);
        $excelData          = array();
        for ($row = 3; $row getCellByColumnAndRow($col, $row)->getValue();
            }
        }
        return $excelData;
    }

拿到手的excel文件有26个，最短的文件行数有1169行，最多的有46534行，

一开始按照原有思路，将文件数据取到后循环插入数据库，从下午2点到下午5点3个小时的时间，

public function importexcel(){
        error_reporting(0);
        $tk_array = ["A" => 725, "B" => 726,"C" => 727, "D" => 728,"E" => 729, "F" => 730,
                "G" => 731, "H" => 732,"I" => 733, "J" => 734,"K" => 735, "N" => 736,"O" => 737, "P" => 738,
                "Q" => 739, "R" => 740,"S" => 741, "T" => 742,"U" => 743, "V" => 744,"X" => 745, "Z" => 746
            ];
        // $filename = input('f');
        // if(!isset($filename) || empty($filename)){
        //     echo '/f/abc.xls';die;
        // }

        $filePath = './static/excel/';
        $filenames = scandir($filePath);
        // dump($filenames);die;
        for ($i=2; $i < count filename = './static/excel/' xss=removed>readexcel($filename);
            $totalnum = count($res);

            foreach ($res as $k => $v) {
                $data = $arr = [];
                $data['barcode'] = '';
                if($v[0]){
                    $data['barcode'] = str_replace(']','',str_replace('[','',$v[0]));
                }
                $data['catno'] = $v[1];
                $data['isbn'] = $v[2];
                $data['bookname'] = $v[3];
                $data['accno'] = $v[4];
                $data['publish'] = $v[5];
                $data['price'] = $v[6];
                $data['bookstatus'] = $v[7];
                $data['author'] = $v[8];
                $data['org_id'] = 3;
                if($v[1]){
                    $code = substr($v[1],0,1);
                    if(isset($tk_array[$code])){
                        $pid = $tk_array[$code];//db('book_tk')->where(['code'=>$code])->value('id');
                    }
                }
                $data['parent_id'] = isset($pid) ? $pid : '';

                $arr[] = $data;
                if($totalnum > 4000){
                    try {
                        db('book_list_new')->insert($data);
                    } catch (\Exception $e) {
                        continue;
                    }
                }
            }

            if($totalnum insertAll($arr);
                } catch (Exception $e) {
                    continue;
                }
            }
            if (file_exists($filename)) {
                @unlink($filename);
            }

            // try {
            //     db('book_list_new')->insertAll($arr);
            //     if (file_exists($filename)) {
            //         @unlink($filename);
            //     }
            // } catch (Exception $e) {
            //     continue;
            // }

            echo 'success'.$filenames[$i];
        }
}

这等待期间，我重写了方法，将操作数据步骤拿到了循环外，将数据打包成数组进行操作，

最后完成代码：

public function importexcel(){
        error_reporting(0);
        $tk_array = ["A" => 725, "B" => 726,"C" => 727, "D" => 728,"E" => 729, "F" => 730,
                "G" => 731, "H" => 732,"I" => 733, "J" => 734,"K" => 735, "N" => 736,"O" => 737, "P" => 738,
                "Q" => 739, "R" => 740,"S" => 741, "T" => 742,"U" => 743, "V" => 744,"X" => 745, "Z" => 746
            ];
        // $filename = input('f');
        // if(!isset($filename) || empty($filename)){
        //     echo '/f/abc.xls';die;
        // }

        $filePath = './static/exceltest/';
        $filenames = scandir($filePath);
        for ($i=2; $i < count filename = './static/exceltest/' xss=removed>readexcel($filename);
            $totalnum = count($res);

            $data = [];
            foreach ($res as $k => $v) {
                $data['barcode'] = '';
                if($v[0]){
                    $data['barcode'] = str_replace(']','',str_replace('[','',$v[0]));
                }
                $data['catno'] = $v[1];
                $data['isbn'] = $v[2];
                $data['bookname'] = $v[3];
                $data['accno'] = $v[4];
                $data['publish'] = $v[5];
                $data['price'] = $v[6];
                $data['bookstatus'] = $v[7];
                $data['author'] = $v[8];
                $data['org_id'] = 3;
                if($v[1]){
                    $code = substr($v[1],0,1);
                    if(isset($tk_array[$code])){
                        $pid = $tk_array[$code];//db('book_tk')->where(['code'=>$code])->value('id');
                    }
                }
                $data['parent_id'] = isset($pid) ? $pid : '';
                if($k

最后上两张改良后的效果：

第一张是测试期间发现线上mysql支持插入数据最多不过千，所以测试过程中途出现了几个无语的错误，

像这个：

**SQLSTATE[HY000]: General error: 1436 Thread stack overrun: 246176 bytes used of a 262144 byte stack, and 16000 bytes needed. Use 'mysqld --thread_stack=#' to specify a bigger stack.**

还有个是thinkphp报的数据库语句错误，这个也是因为mysql数据库的配置问题，网上一致都是说是语句错误，语句中有保留字段。

好了，自己看图看效果吧：

[![](http://images.lmyperson.com/FqBxLGzAtDzM9gR0ZORqGSSSxwi9?imageView2/0/q/75|watermark/2/text/QOaFlee8mOWwj-agiCBsbXlwZXJzb24=/font/5qW35L2T/fontsize/400/fill/IzJDMTBGQw==/dissolve/100/gravity/SouthEast/dx/10/dy/10|imageslim)](http://images.lmyperson.com/FqBxLGzAtDzM9gR0ZORqGSSSxwi9)

4分33秒插入260007条数据，配置：每次插入数据库1000条

[![](http://images.lmyperson.com/FqlS6uvdAS-_1HCYDbfKX8R8m9vk?imageView2/0/q/75|watermark/2/text/QOaFlee8mOWwj-agiCBsbXlwZXJzb24=/font/5qW35L2T/fontsize/400/fill/IzJDMTBGQw==/dissolve/100/gravity/SouthEast/dx/10/dy/10|imageslim)](http://images.lmyperson.com/FqlS6uvdAS-_1HCYDbfKX8R8m9vk)

2分20秒插入260007数据，配置：每次插入数据2000条