laravel phpexcel 导入导出

laravel框架实现phpExcel导入导出，首先必须在laravel引入第三方类

1、在app目录下创建一个新的文件夹，命名libs（可自定义）

2、（可选）考虑到后面可能会引用很多库，so，在libs下再创建一个phpExcel文件夹,把phpExcel类放入此文件夹下。

3、找到根目录下的composer.json文件

4、找到composer.json中定义的（看我备注）

           "autoload": {  

                 "classmap": [  

                      "database",  

                     "app/libs/phpExcel"  //加入phpExcel类的路径 

                  ],  

              "psr-4": {  

                    "App\\": "app/"  

               }

        },

5、安装composer，windows下可以在百度上下载

6、运行命令行进入项目根目录，执行“composer dumpautoload”，

7、在控制器中use phpExcel

8、在方法中实例化phpExccel对象，打印该对象看phpExcel类是否引入成功。

     $objPHPExcel = new PHPExcel();

     print_r($objPHPExcel);

==========以上是引入phpExcel类步骤（其它第三方类与此类似）============

//导出     控制器中use PHPExcel;  use IOFactory;

 public function phpexcel()  
    {  
      
           //$objPHPExcel = new PHPExcel();  
      
           //print_r($objPHPExcel);  
      
            $query =DB::table('shangp')->get();  
      
            //$query =$this ->db->query($sql);  
      
           //print_r($query);die; 
      
            if(!$query)return false;  
      
            //StartingthePHPExcellibrary  
      
            //加载PHPExcel类  
      
           //$this->load->library('PHPExcel');  
      
            //$this->load ->library('PHPExcel/IOFactory');  
      
            $objPHPExcel= new PHPExcel();  
      
            include_once('../app/libs/phpexcel/phpexcel/IOFactory.php');  
      
            $objPHPExcel->getProperties()-> setTitle("export") ->setDescription("none");  
      
            $objPHPExcel-> setActiveSheetIndex(0);  
      
            //Fieldnamesinthefirstrow  
      
            $fields = DB::select("select COLUMN_NAME from information_schema.COLUMNS where  
      
               table_name = 'shangp'");  
      
           //print_r($fields);die;  
      
            $col = 0;  
      
           foreach($fields as $field){  
      
                $field =$field['COLUMN_NAME'];  
      
                $objPHPExcel-> getActiveSheet() -> setCellValueByColumnAndRow($col, 1,$field);  
      
                $col++;  
      
            }  
      
           //die;  
           //Fetchingthetabledata  
      
           $row = 2;  
      
            foreach($query as $data)  
            {  
      
                 $col =0;  
      
                 foreach($fields as $field)  
      
                 {  
      
                     //print_r($data);  
      
                     $field =$field['COLUMN_NAME'];  
      
                     $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col,$row,!empty($data["$field"])?$data["$field"]:'');  
      
                     $col++;  
      
                 }  
      
                $row++;  
      
            }  
      
            //die;  
      
            $objPHPExcel-> setActiveSheetIndex(0);  
      
            $objWriter =IOFactory :: createWriter($objPHPExcel, 'Excel5');  
      
            //Sendingheaderstoforcetheusertodownloadthefile  
      
            header('Content-Type:application/vnd.ms-excel');  
      
           //header('Content-Disposition:attachment;filename="Products_' .date('dMy') . '.xls"');  
      
            header('Content-Disposition:attachment;filename="Brand_' .date('Y-m-d') . '.xls"');  
      
            header('Cache-Control:max-age=0');  
      
            $objWriter-> save('php://output');  
      
        }  

//导入     控制器中use IOFactory;   use PHPExcel_Cell;

public function ru(Request $request){  
        //print_r($_FILES);die;
        //echo $file_stu;
       $tmp_file =$_FILES ['file_stu'] ['tmp_name'];  
    //print_r($tmp_file);die;
        //var_dump($tmp_name);die;
       $file_types =explode ( ".", $_FILES ['file_stu'] ['name'] );  
  
       $file_type =$file_types [count ( $file_types ) - 1];  
  
       /*判别是不是.xls文件，判别是不是excel文件*/  
  
       if (strtolower( $file_type ) != "xls"){  
  
          $this->error ( '不是Excel文件，重新上传' );  
  
       }  
  
       $savePath ="./excel/";  
  
       /*以时间来命名上传的文件*/  
  
       $str =date('Ymdhis');  
  
       $file_name =$str . "." . $file_type;  
  
       //echo$file_name;die;  
  
       $request->file('file_stu')->move($savePath, $file_name);  
  
       /*是否上传成功*/  
  
       /*if(!copy($tmp_file,$savePath.$file_name)){ 
 
          $this->error ( '上传失败' ); 
 
       }*/  
  
       //要获得新的文件路径+名字  
  
       $fullpath =$savePath.$file_name;  
  
       //echo$fullpath;die;  
  
       $re =$this->read($fullpath,'utf-8');  
  
       //print_r($re);die;  
  
       for($i=1;$i<count($re);$i++){  
  
          //print_r($re);  
  
          //echo$re[$i][1];  
  
          $adds =DB::table('shangp')->insert(['name' => $re[$i][1], 'price' =>$re[$i][2], 'time'=>$re[$i][3]]);  
  
       }  
  
       //die;  
  
       if($adds){  
  
            echo"<script>alert('导入成功');location.href='daoru'</script>";  
  
        }else{  
  
            echo"<script>alert('导入失败');location.href='daoru'</script>";  
  
        }  
    }  

    public function read($filename,$encode='utf-8')  
{  
  
        include_once('../app/libs/phpexcel/phpexcel/IOFactory.php');  
  
        //$this->load ->library('PHPExcel/IOFactory');  
  
        $objReader =IOFactory::createReader('Excel5');  
  
        $objReader->setReadDataOnly(true);  
  
        $objPHPExcel= $objReader->load($filename);  
  
        $objWorksheet= $objPHPExcel->getActiveSheet();  
  
        $highestRow =$objWorksheet->getHighestRow();  
  
        //echo$highestRow;die;  
  
        $highestColumn = $objWorksheet->getHighestColumn();  
  
        //echo$highestColumn;die;  
  
        $highestColumnIndex =PHPExcel_Cell::columnIndexFromString($highestColumn);  
  
        $excelData =array();  
  
        for($row = 1;$row <= $highestRow; $row++) {  
  
            for ($col= 0; $col < $highestColumnIndex; $col++) {  
  
                   $excelData[$row][]=(string)$objWorksheet->getCellByColumnAndRow($col,$row)->getValue();  
  
             }  
  
        }  
  
        return $excelData;  
  
}  

//导出的表单

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<title>Document</title>
</head>
<body>
	<form action="{{url('/index/ru')}}" method="post" enctype="multipart/form-data">
	<input type="hidden" name="_token" value="{{csrf_token()}}">
		<table>
			<tr>
			<td><input type="text" name="tmp_name"></td>
			<td><input type="file" name="file_stu"></td>
			</tr>
			<tr>
				<td><input type="submit" value="上传"></td>
			</tr>
		</table>
	</form>
</body>
</html>