Laravel框架使用maatwebsite/excel 导出表格样式自定义

controller

  1 <?php
  2 
  3 namespace App\Http\Controllers\Admin;
  4 
  5 use App\Http\Controllers\PublicController;
  6 use App\Models\ShopGoods;
  7 use App\Models\File;
  8 use App\Models\ShopPackage;
  9 use App\Models\ShopPackageGoods;
 10 use App\Models\ShopOrder;
 11 use App\Models\Member;
 12 use App\Utils\CodeUtil;
 13 use App\Utils\ConstantUtil;
 14 use Illuminate\Http\Request;
 15 use App\Http\Controllers\Controller;
 16 
 17 use Excel;
 18 use PHPExcel_Worksheet_Drawing;
 19 
 20 class PackageController extends Controller
 21 {
 22     
 23     public function export(Request $request)
 24     {
 25         ini_set('memory_limit', '500M');
 26         set_time_limit(0);//设置超时限制为0分钟
 27 
 28         $package_id = $request->input("id");
 29         $list = ShopPackage::find($package_id);
 30         $goods = ShopPackageGoods::where('package_id',$package_id)->get();
 31         $order = ShopOrder::where('package_id',$package_id)->orderBy('id','asc')->limit(1)->first();
 32         $user = Member::where('id',$list['user_id'])->value('nickname');
 33         
 34         $newNumber = substr(strval($package_id+10000),1,4);
 35         $filename = '和品美术包裹明细'.$newNumber;
 36         $cellData[] = [$filename];
 37         $cellData[] = ['图片','编号+名称','金额总','备注'];
 38         
 39         $total = 0;
 40         foreach($goods as $kk=>$vv){
 41             $pic = File::where('id',$vv->goods_pic)->value('uri');
 42             $cellData[] = [$pic,$vv->goods_name,$vv->goods_price,'无'];
 43             $total += $vv->goods_price;
 44         }
 45         
 46         $kh = '客户姓名：'.$user;
 47         $sh = '收货地址：'.$order->consignee.' '.$order->mobile.' '.$order->province.' '.$order->city.' '.$order->district.' '.$order->address;
 48         $cellData[] = [$kh,$sh,'总计金额',$total];
 49         
 50         $cell_count = count($cellData);
 51         
 52         Excel::create($filename,function ($excel) use ($cellData,$cell_count){
 53             $excel->sheet('score',function ($sheet) use ($cellData,$cell_count) {
 54                 
 55                 //init列
 56                 $title_array = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q',
 57                     'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH'];
 58                 //遍历数据
 59                 for($i=0;$i<sizeof($cellData);$i++){
 60                     foreach($cellData[$i] as $k=>$v){
 61                         
 62                         // 设置宽度：
 63                         $sheet->setWidth(array( 'A' => 30,'B' => 84,'C' => 30,'D' => 30));
 64                         // 设置高度：
 65                         $sheet->setHeight(array(
 66                             ($i * 20 + 1) => 40,//设置每行的高度
 67                             ($i * 20 + 2) => 40,
 68                         ));
 69                         // 设置单元格字体样式:
 70                         $sheet->cells(('A1:D200'), function ($cells) {
 71                             $cells->setFontSize(14);//字体大小
 72                             $cells->setFontFamily('simsun');//字体样式
 73                             $cells->setValignment('center');//字体垂直居中
 74                             $cells->setAlignment('center');//字体水平居中
 75                         });
 76                         //字体自动换行
 77                         $sheet->getStyle('A1:D200')->getAlignment()->setWrapText(TRUE);
 78                         
 79                         if($i == 0){
 80                             $sheet->mergeCells('A1:D1');
 81                         }
 82                         //设置图片列高度
 83                         $i>1 && $sheet->setHeight($i+1, 85);
 84                         //设置图片列宽度
 85                         $sheet->setWidth(array('F'=>12));
 86                         //判断图片列，如果是则放图片
 87                         if($k == 0 && $i>1 && $i < ($cell_count-1)){
 88                             $objDrawing = new PHPExcel_Worksheet_Drawing;
 89                             $objDrawing->setPath('.'.$v);
 90                             $objDrawing->setCoordinates($title_array[$k] . ($i+1));
 91                             $objDrawing->setHeight(90);
 92                             // 写入图片在指定格中的X坐标值
 93                             $objDrawing->setOffsetX ( 36 );
 94                             // 写入图片在指定格中的Y坐标值
 95                             $objDrawing->setOffsetY ( 13 );
 96                             $objDrawing->setRotation(1);
 97                             $objDrawing->setWorksheet($sheet);
 98                             continue;
 99                         }
100                         //否则放置文字数据
101                         $sheet->cell($title_array[$k] . ($i+1), function ($cell) use ($v) {
102                             $cell->setValue($v);
103                         });    
104                     }                    
105                 }
106             });
107         })->export('xls');
108     }
109     
110 }
View Code

view

<form class="layui-form" action="/Package/export" enctype="multipart/form-data" method="post" id="ajaxform" style="width:10%;float:left;">
    {{csrf_field()}}
    <input type="hidden" name="id" value="{{$vo['id']}}">
    <div class="panel-body">
        <button class="layui-btn layui-btn-sm"><i class="layui-icon">&#xe601;</i>导出</button>
    </div>
</form>

注：导出图片时，图片一定要在本地存在才可以导出

效果如下：

 

posted @ 2021-05-08 15:22  糖糖Y  阅读(972)  评论(0)  编辑  收藏  举报