laravel框架实现phpExcel导入导出

来源：互联网发布：j罗皇马数据编辑：程序博客网时间：2024/04/30 11:33

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类步骤（其它第三方类与此类似）============

<span style="font-size:18px;">以下开始excel导入导出</span>

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

publicfunction phpexcel(){       //$objPHPExcel = new PHPExcel();       //print_r($objPHPExcel);        $query =DB::table('goods')->get();        //$query =$this ->db->query($sql);       //print_r($query);        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 = 'goods';");       //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 $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 functionru(Request $request){       $tmp_file =$_FILES ['file_stu'] ['tmp_name'];       $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('goods')->insert(['gname' => $re[$i][1], 'gprice' =>$re[$i][2]]);       }       //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;}

phpExcel导入导出终于完成了，赶快尝试一下吧！

0 0