数组数据导入大胜利？这个可以有！
今天在做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 <= $highestRow; $row++) {
for ($col = 0; $col < $highestColumnIndex; $col++) {
$excelData[$row][] = (string) $objWorksheet->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($filenames); $i++) {
$filename = './static/excel/'.$filenames[$i].'';
$res = $this->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 <= 4000){
try {
db('book_list_new')->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($filenames); $i++) {
$filename = './static/exceltest/'.$filenames[$i].'';
$res = $this->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 <= 1000*2){
$arr[] = $data;
}elseif($k <=2000*2){
$arr2[] = $data;
}elseif($k <=3000*2){
$arr3[] = $data;
}elseif($k <=4000*2){
$arr4[] = $data;
}elseif($k <=5000*2){
$arr5[] = $data;
}elseif($k <=6000*2){
$arr6[] = $data;
}elseif($k <=7000*2){
$arr7[] = $data;
}elseif($k <=8000*2){
$arr8[] = $data;
}elseif($k <=9000*2){
$arr9[] = $data;
}elseif($k <=10000*2){
$arr10[] = $data;
}elseif($k <=11000*2){
$arr11[] = $data;
}elseif($k <=12000*2){
$arr12[] = $data;
}elseif($k <=13000*2){
$arr13[] = $data;
}elseif($k <=14000*2){
$arr14[] = $data;
}elseif($k <=15000*2){
$arr15[] = $data;
}elseif($k <=16000*2){
$arr16[] = $data;
}elseif($k <=17000*2){
$arr17[] = $data;
}elseif($k <=18000*2){
$arr18[] = $data;
}elseif($k <=19000*2){
$arr19[] = $data;
}elseif($k <=20000*2){
$arr20[] = $data;
}elseif($k <=21000*2){
$arr21[] = $data;
}elseif($k <=22000*2){
$arr22[] = $data;
}elseif($k <=23000*2){
$arr23[] = $data;
}elseif($k <=24000*2){
$arr24[] = $data;
}elseif($k <=25000*2){
$arr25[] = $data;
}elseif($k <=26000*2){
$arr26[] = $data;
}elseif($k <=27000*2){
$arr27[] = $data;
}elseif($k <=28000*2){
$arr28[] = $data;
}elseif($k <=29000*2){
$arr29[] = $data;
}
// if($k <= 1000){
// $arr[] = $data;
// }elseif($k <=2000){
// $arr2[] = $data;
// }elseif($k <=3000){
// $arr3[] = $data;
// }elseif($k <=4000){
// $arr4[] = $data;
// }elseif($k <=5000){
// $arr5[] = $data;
// }elseif($k <=6000){
// $arr6[] = $data;
// }elseif($k <=7000){
// $arr7[] = $data;
// }elseif($k <=8000){
// $arr8[] = $data;
// }elseif($k <=9000){
// $arr9[] = $data;
// }elseif($k <=10000){
// $arr10[] = $data;
// }elseif($k <=11000){
// $arr11[] = $data;
// }elseif($k <=12000){
// $arr12[] = $data;
// }elseif($k <=13000){
// $arr13[] = $data;
// }elseif($k <=14000){
// $arr14[] = $data;
// }elseif($k <=15000){
// $arr15[] = $data;
// }elseif($k <=16000){
// $arr16[] = $data;
// }elseif($k <=17000){
// $arr17[] = $data;
// }elseif($k <=18000){
// $arr18[] = $data;
// }elseif($k <=19000){
// $arr19[] = $data;
// }elseif($k <=20000){
// $arr20[] = $data;
// }elseif($k <=21000){
// $arr21[] = $data;
// }elseif($k <=22000){
// $arr22[] = $data;
// }elseif($k <=23000){
// $arr23[] = $data;
// }elseif($k <=24000){
// $arr24[] = $data;
// }elseif($k <=25000){
// $arr25[] = $data;
// }elseif($k <=26000){
// $arr26[] = $data;
// }elseif($k <=27000){
// $arr27[] = $data;
// }elseif($k <=28000){
// $arr28[] = $data;
// }elseif($k <=29000){
// $arr29[] = $data;
// }elseif($k <=30000){
// $arr30[] = $data;
// }elseif($k <=31000){
// $arr31[] = $data;
// }elseif($k <=32000){
// $arr32[] = $data;
// }elseif($k <=33000){
// $arr33[] = $data;
// }elseif($k <=34000){
// $arr34[] = $data;
// }elseif($k <=35000){
// $arr35[] = $data;
// }elseif($k <=36000){
// $arr36[] = $data;
// }elseif($k <=37000){
// $arr37[] = $data;
// }elseif($k <=38000){
// $arr38[] = $data;
// }elseif($k <=39000){
// $arr39[] = $data;
// }elseif($k <=40000){
// $arr40[] = $data;
// }elseif($k <=41000){
// $arr41[] = $data;
// }elseif($k <=42000){
// $arr42[] = $data;
// }elseif($k <=43000){
// $arr43[] = $data;
// }elseif($k <=44000){
// $arr44[] = $data;
// }elseif($k <=45000){
// $arr45[] = $data;
// }elseif($k <=46000){
// $arr46[] = $data;
// }elseif($k <=47000){
// $arr47[] = $data;
// }elseif($k <=48000){
// $arr48[] = $data;
// }elseif($k <=49000){
// $arr49[] = $data;
// }elseif($k <=50000){
// $arr50[] = $data;
// }elseif($k <=51000){
// $arr51[] = $data;
// }elseif($k <=52000){
// $arr52[] = $data;
// }elseif($k <=53000){
// $arr53[] = $data;
// }elseif($k <=54000){
// $arr54[] = $data;
// }elseif($k <=55000){
// $arr55[] = $data;
// }elseif($k <=56000){
// $arr56[] = $data;
// }elseif($k <=57000){
// $arr57[] = $data;
// }elseif($k <=58000){
// $arr58[] = $data;
// }elseif($k <=59000){
// $arr59[] = $data;
// }elseif($k <=60000){
// $arr60[] = $data;
// }elseif($k <=61000){
// $arr61[] = $data;
// }elseif($k <=62000){
// $arr62[] = $data;
// }elseif($k <=63000){
// $arr63[] = $data;
// }elseif($k <=64000){
// $arr64[] = $data;
// }elseif($k <=65000){
// $arr65[] = $data;
// }elseif($k <=66000){
// $arr66[] = $data;
// }elseif($k <=67000){
// $arr67[] = $data;
// }elseif($k <=68000){
// $arr68[] = $data;
// }elseif($k <=69000){
// $arr69[] = $data;
// }elseif($k <=70000){
// $arr70[] = $data;
// }
/* ------------------- */
// $arr[] = $data;
// if($totalnum > 4000){
// try {
// db('book_list_test')->insert($data);
// } catch (\Exception $e) {
// continue;
// }
// }
}
if(isset($arr) ){db('book_list_test')->insertAll($arr);unset($arr);}
if(isset($arr2)) {db('book_list_test')->insertAll($arr2);unset($arr2);}
if(isset($arr3)) {db('book_list_test')->insertAll($arr3);unset($arr3);}
if(isset($arr4)) {db('book_list_test')->insertAll($arr4);unset($arr4);}
if(isset($arr5)) {db('book_list_test')->insertAll($arr5);unset($arr5);}
if(isset($arr6)) {db('book_list_test')->insertAll($arr6);unset($arr6);}
if(isset($arr7)) {db('book_list_test')->insertAll($arr7);unset($arr7);}
if(isset($arr8)) {db('book_list_test')->insertAll($arr8);unset($arr8);}
if(isset($arr9)) {db('book_list_test')->insertAll($arr9);unset($arr9);}
if(isset($arr10)){db('book_list_test')->insertAll($arr10);unset($arr10);}
if(isset($arr11)) {db('book_list_test')->insertAll($arr11);unset($arr11);}
if(isset($arr12)) {db('book_list_test')->insertAll($arr12);unset($arr12);}
if(isset($arr13)) {db('book_list_test')->insertAll($arr13);unset($arr13);}
if(isset($arr14)) {db('book_list_test')->insertAll($arr14);unset($arr14);}
if(isset($arr15)) {db('book_list_test')->insertAll($arr15);unset($arr15);}
if(isset($arr16)) {db('book_list_test')->insertAll($arr16);unset($arr16);}
if(isset($arr17)) {db('book_list_test')->insertAll($arr17);unset($arr17);}
if(isset($arr18)) {db('book_list_test')->insertAll($arr18);unset($arr18);}
if(isset($arr19)) {db('book_list_test')->insertAll($arr19);unset($arr19);}
if(isset($arr20)){db('book_list_test')->insertAll($arr20);unset($arr20);}
if(isset($arr21)) {db('book_list_test')->insertAll($arr21);unset($arr21);}
if(isset($arr22)) {db('book_list_test')->insertAll($arr22);unset($arr22);}
if(isset($arr23)) {db('book_list_test')->insertAll($arr23);unset($arr23);}
if(isset($arr24)) {db('book_list_test')->insertAll($arr24);unset($arr24);}
if(isset($arr25)) {db('book_list_test')->insertAll($arr25);unset($arr25);}
if(isset($arr26)) {db('book_list_test')->insertAll($arr26);unset($arr26);}
if(isset($arr27)) {db('book_list_test')->insertAll($arr27);unset($arr27);}
if(isset($arr28)) {db('book_list_test')->insertAll($arr28);unset($arr28);}
if(isset($arr29)) {db('book_list_test')->insertAll($arr29);unset($arr29);}
if(isset($arr30)){db('book_list_test')->insertAll($arr30);unset($arr30);}
if(isset($arr31)) {db('book_list_test')->insertAll($arr31);unset($arr31);}
if(isset($arr32)) {db('book_list_test')->insertAll($arr32);unset($arr32);}
if(isset($arr33)) {db('book_list_test')->insertAll($arr33);unset($arr33);}
if(isset($arr34)) {db('book_list_test')->insertAll($arr34);unset($arr34);}
if(isset($arr35)) {db('book_list_test')->insertAll($arr35);unset($arr35);}
if(isset($arr36)) {db('book_list_test')->insertAll($arr36);unset($arr36);}
if(isset($arr37)) {db('book_list_test')->insertAll($arr37);unset($arr37);}
if(isset($arr38)) {db('book_list_test')->insertAll($arr38);unset($arr38);}
if(isset($arr39)) {db('book_list_test')->insertAll($arr39);unset($arr39);}
if(isset($arr40)){db('book_list_test')->insertAll($arr40);unset($arr40);}
if(isset($arr41)) {db('book_list_test')->insertAll($arr41);unset($arr41);}
if(isset($arr42)) {db('book_list_test')->insertAll($arr42);unset($arr42);}
if(isset($arr43)) {db('book_list_test')->insertAll($arr43);unset($arr43);}
if(isset($arr44)) {db('book_list_test')->insertAll($arr44);unset($arr44);}
if(isset($arr45)) {db('book_list_test')->insertAll($arr45);unset($arr45);}
if(isset($arr46)) {db('book_list_test')->insertAll($arr46);unset($arr46);}
if(isset($arr47)) {db('book_list_test')->insertAll($arr47);unset($arr47);}
if(isset($arr48)) {db('book_list_test')->insertAll($arr48);unset($arr48);}
if(isset($arr49)) {db('book_list_test')->insertAll($arr49);unset($arr49);}
if(isset($arr50)){db('book_list_test')->insertAll($arr50);unset($arr50);}
if(isset($arr51)) {db('book_list_test')->insertAll($arr51);unset($arr51);}
if(isset($arr52)) {db('book_list_test')->insertAll($arr52);unset($arr52);}
if(isset($arr53)) {db('book_list_test')->insertAll($arr53);unset($arr53);}
if(isset($arr54)) {db('book_list_test')->insertAll($arr54);unset($arr54);}
if(isset($arr55)) {db('book_list_test')->insertAll($arr55);unset($arr55);}
if(isset($arr56)) {db('book_list_test')->insertAll($arr56);unset($arr56);}
if(isset($arr57)) {db('book_list_test')->insertAll($arr57);unset($arr57);}
if(isset($arr58)) {db('book_list_test')->insertAll($arr58);unset($arr58);}
if(isset($arr59)) {db('book_list_test')->insertAll($arr59);unset($arr59);}
if(isset($arr60)){db('book_list_test')->insertAll($arr60);unset($arr60);}
if(isset($arr61)) {db('book_list_test')->insertAll($arr61);unset($arr61);}
if(isset($arr62)) {db('book_list_test')->insertAll($arr62);unset($arr62);}
if(isset($arr63)) {db('book_list_test')->insertAll($arr63);unset($arr63);}
if(isset($arr64)) {db('book_list_test')->insertAll($arr64);unset($arr64);}
if(isset($arr65)) {db('book_list_test')->insertAll($arr65);unset($arr65);}
if(isset($arr66)) {db('book_list_test')->insertAll($arr66);unset($arr66);}
if(isset($arr67)) {db('book_list_test')->insertAll($arr67);unset($arr67);}
if(isset($arr68)) {db('book_list_test')->insertAll($arr68);unset($arr68);}
if(isset($arr69)) {db('book_list_test')->insertAll($arr69);unset($arr69);}
if(isset($arr70)){db('book_list_test')->insertAll($arr70);unset($arr70);}
// try {
// db('book_list_test')->insertAll($arr);
// } catch (Exception $e) {
// continue;
// }
if (file_exists($filename)) {
@unlink($filename);
}
// try {
// db('book_list_test')->insertAll($arr);
// if (file_exists($filename)) {
// @unlink($filename);
// }
// } catch (Exception $e) {
// continue;
// }
echo 'success'.$filenames[$i];
}
最后上两张改良后的效果：
第一张是测试期间发现线上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数据库的配置问题，网上一致都是说是语句错误，语句中有保留字段。
好了，自己看图看效果吧：
4分33秒插入260007条数据，配置：每次插入数据库1000条
2分20秒插入260007数据，配置：每次插入数据2000条