laravel-admin 导出excel

仅laravel5.*版本参考

composer require maatwebsite/excel:~2.1.0

安装后的设置

在config/app.php中注册服务提供者到providers数组：

Maatwebsite\Excel\ExcelServiceProvider::class,

同样在config/app.php中注册门面到aliases数组：

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

如果想要对Laravel Excel进行更多的自定义配置，执行如下Artisan命令：

php artisan vendor:publish --provider="Maatwebsite\Excel\ExcelServiceProvider"

导出Excel文件

不是自定义文件

在Admin/Extensions下创建ExcelExpoter.php文件

<?php
namespace App\Admin\Extensions;

use Encore\Admin\Grid\Exporters\AbstractExporter;
use Maatwebsite\Excel\Facades\Excel;
use PHPExcel_Worksheet_Drawing;

class ExcelExpoter extends AbstractExporter{
    protected $head = [];
    protected $body = [];
    public function setAttr($head, $body){
        $this->head = $head;
        $this->body = $body;
    }


    public function export(){

         // TODO: Implement export() method.
        $fileName = date('YhdHis').'_'.uniqid();
        Excel::create($fileName,function ($excel){
            $excel->sheet('sheet1',function ($sheet){
                $head = $this->head;
                $body = $this->body;

                $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q',
                    'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH'];
                $rows = collect([$head]);  //写入标题
                $sheet->rows($rows);

                collect($this->getData())->map(function ($item,$k)use ($body,$sheet,$title_array){
                    foreach ($body as $i => $keyName){
                        if($keyName == 'url'){
                            $objDrawing = new PHPExcel_Worksheet_Drawing;
                            $v = public_path('/upload/'). array_get($item, $keyName); //拼接图片地址
                            $objDrawing->setPath( $v );
                            $sp = $title_array[$i];
                            $objDrawing->setCoordinates( $sp . ($k+2) );
                            $sheet->setHeight($k+2, 65); //设置高度
                            $sheet->setWidth(array( $sp =>12));  //设置宽度
                            $objDrawing->setHeight(80);
                            $objDrawing->setOffsetX(1);
                            $objDrawing->setRotation(1);
                            $objDrawing->setWorksheet($sheet);

                        }else{//否则放置文字数据
                            $v = array_get($item, $keyName);
                            $sheet->cell($title_array[$i] . ($k+2), function ($cell) use ($v) {
                                $cell->setValue($v);
                            });
                        }
                    }
                });

            });
        })->export('xls');
    }
}

laravel-admin grid()方法

 $excel = new ExcelExpoter();
excel->setAttr(['用户姓名','地址'], ['username','address']);
$grid->exporter($excel);

 

自定义订单列表 文件【合并单元格】

<?php
namespace App\Admin\Extensions;

use Encore\Admin\Grid\Exporters\AbstractExporter;
use Maatwebsite\Excel\Facades\Excel;
use PHPExcel_Worksheet_Drawing;

class ExcelExpoter extends AbstractExporter{
    protected $head = [];
    protected $body = [];
    protected $title='';
    public function setAttr($head, $body,$title){
        $this->head = $head;
        $this->body = $body;
        $this->title = $title;
    }


    public function export(){

        $title = $this->title;
        $fileName = date('Ymd').'_'.$title;

        Excel::create($fileName, function($excel){
            $excel->sheet('sheet1', function($sheet){
                $head = $this->head;
                $body = $this->body;
                // 导出的表格全部单元格进行自动换行
                $sheet->getStyle('A:T')->getAlignment()->setWrapText(TRUE);

                // 设置单元格宽度
                $sheet->setWidth(array('A' => 20,'B' => 20,'C' => 20,'D' => 20,'E' => 50,'F' => 10,'G' => 10,'H' => 10,
                    'I' => 10,'J' => 30,'K' => 10,'L' => 10,'M' => 10,'N' => 20,'O' => 20,'P' => 20,'Q' => 15,'R' => 15,
                    'S' => 15,'T' => 15,));

                // 设置单元格标题加粗居中
                $sheet->cells('A1:T1', function($cells) {
                    $cells->setFontWeight('bold');
                    $cells->setAlignment('center');
                    $cells->setValignment('center');
                });

                // 设置单元格居中
                $sheet->cells('A:T', function($cells) {
                    $cells->setAlignment('center');
                    $cells->setValignment('center');
                });

                // 设置单元格标题名称
                $sheet->row(1, $head);

//                ['用户姓名','用户收货地址','订单号','商品类型','商品名称','购买数量',
//                    '商品价格','商品总价格']
                $i = 1;
                foreach ($body as $value){
                    $count = count($value['order_goods']);
                    foreach ($value['order_goods'] as $v){
                        $i++;
                        $sheet->row($i,[
                            $value['userName'],
                            $value['userAddress'],
                            $value['orderNo']. "\t",
                            $v['prot_title'],
                            $v['goodName'],
                            $v['goodsNums'],
                            $v['old_price'],
                            $v['total_prices'],
                        ]);
                    }
                    // 如果订单商品数据大于1，怎进行单元格合并
                    if ($count > 1) {
                        $sheet->mergeCells('A'.($i+1-$count).':A'.$i);
                        $sheet->mergeCells('B'.($i+1-$count).':B'.$i);
                        $sheet->mergeCells('C'.($i+1-$count).':C'.$i);
                        $sheet->mergeCells('D'.($i+1-$count).':D'.$i);
                        $sheet->mergeCells('L'.($i+1-$count).':L'.$i);
                        $sheet->mergeCells('M'.($i+1-$count).':M'.$i);
                        $sheet->mergeCells('N'.($i+1-$count).':N'.$i);
                        $sheet->mergeCells('O'.($i+1-$count).':O'.$i);
                        $sheet->mergeCells('P'.($i+1-$count).':P'.$i);
                        $sheet->mergeCells('Q'.($i+1-$count).':Q'.$i);
                        $sheet->mergeCells('R'.($i+1-$count).':R'.$i);
                        $sheet->mergeCells('S'.($i+1-$count).':S'.$i);
                        $sheet->mergeCells('T'.($i+1-$count).':T'.$i);
                    }

                }


            });
        })->export('csv');

        }

}

laravel-admin grid()方法

$excel = new ExcelExpoter();

      
        $orderNo = \request()->get("orderNo");

        if(!empty($orderNo)){
            $where['order.orderNo'] = array('like','%'.$userInfo['orderNo'].'%');
        }

       

        $where['orderStatus'] = 1;

        $orderList = OrderModel::query()->where($where)
            ->select('order.id','order.userName','order.userAddress')
            ->leftJoin('users','users.user_id','=','order.user_id')
            ->orderBy('order.paymentTime','desc')->get()->toArray();

        foreach ($orderList as $key=>$value){
            $orderList[$key]['order_goods'] = OrderGoodsModel::query()
                ->where('orderId',$value['id'])
              ->select('goodName','goodsNums','old_price','goodsNums','total_prices','prot_title')
                ->leftJoin('protype','protype.prot_id','=','order_goods.prot_id')
                ->get()->toArray();
        }

        $excel->setAttr(['用户姓名','用户收货地址','商品类型','商品名称','购买数量',
            '商品价格','商品总价格'], $orderList,'已付款订单');
        $grid->exporter($excel);

        return $grid;