[汇总信息] Laravel 上使用 phpexcel的两种方式

文章采集与网上

方式1。使用原生的phpexcel ，

http://blog.csdn.net/CSwfe/article/details/52748046?locationNum=1

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


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


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


4、找到composer.json中定义的（看我备注）
//加入phpExcel类的路径 

           "autoload": {  


                 "classmap": [  


                      "database",  


                       "app/libs/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;
 

    public function phpexcel()  
    {  
      
           //$objPHPExcel = new PHPExcel();  
      
           //print_r($objPHPExcel);  
      
            $query =DB::table('goods')->get();  
      
            //$query =$this ->db->query($sql);  
      
           //print_r($query);  
      
            if(!$query)return false;  
      
            //Starting the PHPExcel library  
      
            //加载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 function ru(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')  
    {  
      
	    // ../  一般情况不管处于什么子目录子需要这样子即可 例如\app\Admin\Controllers\WechatMercharntPay\OrderListTodayController.php
            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导入导出终于完成了，赶快尝试一下吧

第二方法 用集成方法  maatwebsite 集成类，但是不支持一些文件。已经修复

http://blog.csdn.net/zhwxl_zyx/article/details/47251491 

并修复其中的问题 

\vendor\maatwebsite\excel\src\Maatwebsite\Excel\Readers\LaravelExcelReader.php

修改后的版本

https://github.com/yanggg1133/Laravel-Excel

增加 

    /*
    * @desc 返回所有数据
    * @author 绍兴远帆软件有限公司 主营 ewshop网店系统 远帆自动售货机系统 手机话费流量充值系统 票务销售系统 点餐外卖系统 网店进销存系统
    * @website http://www.ewshop.net/
    * */
    public function readAll()
    {

        // ../  一般情况不管处于什么子目录子需要这样子即可 例如\app\Admin\Controllers\WechatMercharntPay\OrderListTodayController.php
        //include_once('../app/libs/phpexcel/phpexcel/IOFactory.php');

        //$this->load ->library('PHPExcel/IOFactory');


        //$this->reader =IOFactory::createReader('Excel5');

        //$this->reader->setReadDataOnly(true);

        $objPHPExcel= $this->excel;

        $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;

    }

Laravel 4

"maatwebsite/excel": "~1.3"

Laravel 5

"maatwebsite/excel": "~2.0"

[plain] view plain copy

print ?

1. "require-dev": {  
2.      "fzaninotto/faker": "~1.4",  
3.      "mockery/mockery": "0.9.*",  
4.      "phpunit/phpunit": "~4.0",  
5.      "phpspec/phpspec": "~2.1",  
6.      "maatwebsite/excel": "~2.0.0"  
7.  },  

添加完后执行 composer update

After updating composer, add the ServiceProvider to the providers array in app/config/app.php

'Maatwebsite\Excel\ExcelServiceProvider',

You can use the facade for shorter code. Add this to your aliasses:

'Excel' => 'Maatwebsite\Excel\Facades\Excel',

The class is binded to the ioC as excel

$excel = App::make('excel');

Laravel 4

Laravel Excel includes several config settings for import-, export-, view- and CSV-specific settings. Use the artisan publish command to publish the config file to your project.

php artisan config:publish maatwebsite/excel

The config files can now be found at app/config/packages/maatwebsite/excel

Laravel 5

To publish the config settings in Laravel 5 use:

php artisan vendor:publish

This will add an excel.php config file to your config folder.

详细用法请参考官网 http://www.maatwebsite.nl/laravel-excel/docs/getting-started

顶

0

踩

0