thinkPHP3.2.3将Excel数据导入MySQL

野性酷女 2022-12-27 09:17 181阅读 0赞

**准备工作：**  
1、下载PHPExcel\_1.8.0插件；  
2、在ThinkPHP\\Library\\Vendor目录下新建PHPExcel文件夹；  
3、将PHPExcel\_1.8.0\\Classes目录下的文件拷贝到ThinkPHP\\Library\\Vendor\\PHPExcel文件夹中。

**HTML代码：**

<form method="post" action="{:U('index/phpexcel')}" enctype="multipart/form-data">
    	<h3>导入拍品成交信息Excel表：</h3>
    	<input type="file" name="file_cj"/>
    	<input type="submit" value="导入" />
    </form>

**Controller代码：**

public function  phpexcel(){ 
    	    	ini_set('memory_limit','1024M');
    			if (!empty($_FILES)) { 
    				$config = array(
    					'exts' => array('xlsx','xls'),
    					'maxSize' => 8388608,
    					'rootPath' =>"./Public/",
    					'savePath' => 'Uploads/',
    				);
    				$upload = new \Think\Upload($config);
    				if (!$info = $upload -> upload()) { 
    					$this->error($upload -> getError());
    				}
    				vendor("PHPExcel.PHPExcel");
    				$file_name = $upload -> rootPath.$info['file_cj']['savepath'].$info['file_cj']['savename'];
    				$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
    				if ($extension == 'xlsx') { 
    					$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
    					$objPHPExcel = $objReader -> load($file_name, $encode = 'utf-8');
    				} else if ($extension == 'xls'){ 
    					$objReader = \PHPExcel_IOFactory::createReader('Excel5');
    					$objPHPExcel = $objReader -> load($file_name, $encode = 'utf-8');
    				}
    				$sheet = $objPHPExcel->getSheet(0);
    				$highestRow = $sheet->getHighestRow();//获取总行数
    				$highestColumn = $sheet->getHighestColumn(); //获取总列数
    				//D('chengjiao') -> execute('truncate table chengjiao');//每次导入清空数据库原有数据
                    for($i = 2; $i <= $highestRow; $i++){    //从Excel第二行开始导入
    	                $data['type'] = $objPHPExcel -> getActiveSheet() -> getCell("A".$i) -> getValue();
    	                $data['lot'] = $objPHPExcel -> getActiveSheet() -> getCell("B".$i) -> getValue();
    	                $data['name'] = $objPHPExcel->getActiveSheet()->getCell("C".$i) -> getValue();
    	                $data['pjgs'] = $objPHPExcel -> getActiveSheet() -> getCell("D".$i) -> getValue();
    	                $data['pjfs'] = $objPHPExcel -> getActiveSheet() -> getCell("E".$i) -> getValue();
    	                $data['price'] = $objPHPExcel -> getActiveSheet() -> getCell("F".$i) -> getValue();
    	                $data['cjsj'] = $objPHPExcel -> getActiveSheet() -> getCell("G".$i) -> getValue();
    	                $data['pmgs'] = $objPHPExcel -> getActiveSheet() -> getCell("H".$i) -> getValue();
    	                //这个位置写数据库中的表名
    					D('chengjiao')->add($data);
    				}
    				$this->success('导入成功!');
    			} else { 
    				$this->error("请选择上传的文件");
    			}
    		}