Thinkphp uses PHPExcel to import Excel

I made a simple Excel import, using PHPExcel (Encyclopedia: a PHP class library used to operate Office Excel documents, based on Microsoft's OpenXML standard and PHP language)

Okay, no more, let's get started...

First you must have the PHPExcel class library, click here to download https://github.com/Zmwherein/PHPExcel.git

Then put it in ThinkPHPLibraryVendor (personal preference, just import it)

As shown:

PHPExcel.php is similar to an entry file. You can go in and see how the method written in it is run...

This is the interface:

First of all, let’s judge whether any files have been uploaded (actually, the ones here should still work) Optimize and write better, but my personal ability is limited for the time being)

 1 public function import() 
 2     { 
 3         // p($data_in_db); 
 4         if ( ! empty($_FILES)) 
 5         { 
 6             $upload = new \Think\Upload(); 
 7             $upload -> maxSize   =  3145728 ; 
 8             $upload -> exts      =  array('xlsx', 'xls'); 
 9             $upload -> rootPath  =  './'; // 设置附件上传根目录
 10             $upload  -> savePath =  '/Upload/excel/'; // 设置附件上传（子）目录
 11             $upload -> subName   =  false;
 12             $upload -> saveName  =  'time';
 13             
 14             $info   =   $upload -> uploadOne($_FILES['import']);
 15             
 16             if( ! $info)
 17             {
 18                 $this->error($upload->getError());
 19             }

Then I ran on and imported the two files PHPExcel.php and PHPExcel/IOFactory.php

1 //导入PHPExcel 和 IOFactory类
2 Vendor('PHPExcel.PHPExcel');
3 Vendor('PHPExcel.PHPExcel.IOFactory');

Because it is an import, so we call the createReader method

$objReader = \PHPExcel_IOFactory::createReader('Excel5');

Set the path, where is the uploaded file, that is, load a file

$objPHPExcel = $objReader -> load($file_path, $encode='utf-8');

What we are talking about here is the number of rows traversed, from A to E (you define it yourself, take whichever you want) i here starts from 2, because the first row is the header, not the data

 1 $sheet = $objPHPExcel -> getSheet(0);
 2             $highestRow = $sheet -> getHighestRow(); // 取得总行数 
 3             // p($highestRow); 
 4             // $highestColumn = $sheet->getHighestColumn(); // 取得总列数 
 5              
 6             for($i=2;$i getActiveSheet() -> getCell("A".$i)->getValue(); 
 9     $data['company']   = $objPHPExcel -> getActiveSheet() -> getCell("B".$i)->getValue();
 10    $data['mobile']    = $objPHPExcel -> getActiveSheet() -> getCell("C".$i)->getValue();
 11     $data['category']  = $objPHPExcel -> getActiveSheet() -> getCell("D".$i)->getValue();
 12    $data['mark']      = $objPHPExcel -> getActiveSheet() -> getCell("E".$i)->getValue();
 13                 
 14                 $allData[] = $data;
 15             }
 16             // p($allData);

The test table looks like this

Now print out $allData to see

Look, the data is out...

The next step is to import it into the database - >You have to make a small judgment before importing. For example, if the phone number in the database is the same as the data I want to import, I don’t want to import something like this-> Does anyone have any better methods

 1  if (empty($allData)) 
 2             { 
 3                 $this -> error(C('MESSAGE.ERROR_NODATA')); 
 4             } 
 5             $data_in_db = M('Excel') -> field('mobile') -> select();//表Excel里所有数据 
 6             foreach ($data_in_db as $key => $val) 
 7             { 
 8                 foreach ($allData as $k => $v) 
 9                 {
 10                     if ($val['mobile'] == $v['mobile'])
 11                     {
 12                         unset($allData[$k]);
 13                     }
 14                 }
 15             }

When importing, there are two methods: one is to splice sql, the other is to use a foreach loop to import

The first method is much faster!!! It is strongly recommended to splice sql to import...but if The imported data is too large, and the spliced ​​sql will be very long, so the data will be relatively large...

Sometimes an execution file timeout error will be reported or 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes OR

The server hangs up directly. Such an error is reported

In this case, you need to modify the max_allowed_packet in my.ini and set it slightly larger. Restart MySql

OK.. Wait for a moment, and almost 2W of data will be imported. In (^_^)

The above is the insertion statement I wrote

 1 $sql = "INSERT INTO `db_excel` (". implode(',',array_keys($allData[0])) .") VALUES "; 
 2             foreach ($allData as $key => $val) 
 3             { 
 4                 $sql .= "("; 
 5                 $sql .= "'".implode("','", $val)."'"; 
 6                 $sql .= "),"; 
 7             } 
 8             $sql = rtrim($sql,','); 
 9             // 出错返回false 否则返回成功行数
 10             $res = D('Excel') -> execute($sql);
 11             if ($res === false)
 12             {
 13                 $this -> error(C('MESSAGE.ERROR_IMPORT'));
 14             }
 15             else if ($res > 0)
 16             {
 17                 $this -> success(C('MESSAGE.SUCCESS_IMPORT'), U('Excel/index'));
 18             }
 19             else if ($res === 0)
 20             {
 21                 $this -> success(C('MESSAGE.SUCCESS_IMPORT_0'), U('Excel/index'));
 22             }

Okay. The above is the use of PHPExcel to import Excel files.

There are still many shortcomings and I kindly ask you to point out the areas that need to be modified.

The above is the content of Thinkphp using PHPExcel to import Excel. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!