laravel phpexcel设置单元格

发布时间：2019-11-25, 20:42:20 分类：PHP | 编辑 off 网址 | 辅助

正文 3810字数 1,119,760阅读

$cellData[]=[];
            $cellData[]=[];
            $cellData[]=[
                '',
                '',
                //'',
                '',
                '',
                '客户签名：'
            ];
            Excel::create($getmdgdxxhy_dpic['ccs']->title.'-'.date('Ymd',time()),function($excel) use ($cellData,$kn){
                $excel->sheet('score', function($sheet) use ($cellData,$kn){

                    $sheet->rows($cellData)->setWidth(
                        array(
                            //调整导出表格单元格宽度
                            'A' => '15%',
                            'B' => '22%',
                            'C' => '22%',
                            'D' => '20%',
                            'E' => '7%',
                            'F' => '7%',
                            'G' => '7%'
                        )
                    )->setFontSize(16);//->setHeight(200);//->setWrapText(true);
                    // 总金额 高亮显示
                    //echo ($kn+23);exit;
                    $sheet->cells('E'.($kn+20), function($cells) {
                        $cells->setBackground('#87eabd');
                        $cells->setFontWeight('bold');
                        //$cells->setFontSize(14);
                    });
                    $sheet->cells('F'.($kn+20), function($cells) {
                        $cells->setBackground('#87eabd');
                        $cells->setFontWeight('bold');
                        //$cells->setFontSize(14);
                    });
                    $sheet->cells('G'.($kn+20), function($cells) {
                        $cells->setBackground('#87eabd');
                        $cells->setFontWeight('bold');
                        //$cells->setFontSize(14);
                    });
                    //第一行到第四行合并单元格
                    $sheet->mergeCells("B2:H2");
                    //$sheet->mergeCells("B9:C9");
                    //$sheet->mergeCells("B11:C11");
                    foreach($cellData as $i=>$v){
                        //echo $i.$kn.'/';
                        $i=$i+1;
                        if($i>10 && $i<($kn+12)){
                            $sheet->getStyle('B'.$i)->getAlignment()->setWrapText(true);
                            $sheet->getStyle('C'.$i)->getAlignment()->setWrapText(true);
                            $sheet->getStyle('D'.$i)->getAlignment()->setWrapText(true);
                            //$sheet->getAlignment()->setHeight(100);
                        }
                        //$sheet->mergeCells('B'.$i.':C'.$i);
                    }
                    //exit;
                    /*for($i=9;$i<$kn;$i++){
                        $sheet->mergeCells('B11'.$i.':C11'.$i);
                    }*/

                    //第一行标题居中、加粗、设置字符大小
                    $sheet->cells("A2:G2", function ($cells) {
                        $cells->setAlignment('center');
                        $cells->setFontWeight('bold');
                        $cells->setFontSize(25);
                    });

                    //设置边框
                    $style_array = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN
                            )
                        )
                    );
                    $sheet->getStyle('B11:G'.(11+$kn))->applyFromArray($style_array);
                });
            })->export('xls');

Run code

Cut to clipboard

（支付宝）给作者钱财以资鼓励（微信）→

标签: laravel10 phpexcel2 单元格1 表格7

有过 7 条评论 »

未知 2019-11-25, 20:43:30

之前用就可以显示四周的边框

$sheet->setBorder('A' . ($i * 22 + 2) . ':H' . ($i * 22 + 7), 'thin');

Run code

Cut to clipboard

最近使用的时候发现边框只剩下竖条
下面这种写法只能设置外层边框，想要全部的边框智能一个一个的设置单元格

 $sheet->cells('A' . ($i * 22 + 13) . ':A' . ($i * 22 + 20), function ($cells) {
                        $cells->setBorder('none', 'thin', 'none', 'thin');
                        });

Run code

Cut to clipboard

最后使用了下面的写法

$style_array = array(
                        'borders' => array(
                            'allborders' => array(
                                'style' => \PHPExcel_Style_Border::BORDER_THIN
                            )
                        )
                    );
$sheet->getStyle('A' . ($i * 21 + 3) . ':I' . ($i * 21 + 6))->applyFromArray($style_array);

Run code

Cut to clipboard

reply | quote | #¹

未知 2019-11-25, 20:44:21

laravel 使用excel 设计表格的样式

if ($res->code == 1) {

            if($query['excel'] == 'Y'){
                if(!($res->results)){
                    return redirect()
                        ->back()
                        ->withErrors('数据为空，请检查报表条件~')
                        ->withInput();
                }

                $arr[] = ['ID','用户ID','用户名称','数据ID','状态','金额(元)','创建日期'];
                $amount = 0.00 ;
                $total = count($res->results);

                foreach($res->results as $val){
                    $arr[] =[$val->id,$val->u_id,$val->username,$val->d_id,$val->type,$val->amount,date('Y-m-d',$val->created_at)];
                    $amount += $val->amount;
                }

                $arr[] = ['','','','','总额：',$amount];
                $arr[] = ['',' ',' ','',''];
                $arr[] = ['','总条数',$total,'',''];
                $arr[] = ['','总金额',$amount,'备注：',''];
                $arr[] = ['','用户注册','','','徒弟收益(11001)',''];
                $arr[] = ['','点击任务','','','师傅收益(11002)',''];
                $arr[] = ['','任务大厅','','','师爷收益(11003)',''];
                $arr[] = ['','徒弟收益','','','代理收益(11004)',''];
                $arr[] = ['','徒孙收益','','','股东收益(11005)',''];
                $arr[] = ['','代理收益','','',''];
                $arr[] = ['','股东收益','','',''];

                $arr[] = ['','','','',''];
                $arr[] = ['','报表用户',$query['username']];
                $arr[] = ['','报表日期',$query['start_date'].'至'.$query['end_date'],];
                $arr[] = ['','报表类型：'.$query['type'],'','',''];

                $cellData =  $arr;

                Excel::create('财务报表',function($excel) use ($cellData){

                    $excel->sheet('财务报表', function($sheet) use ($cellData){
                        $tot = count($cellData) ;
                        $sheet->setWidth(array(
                            'A'     =>  12,
                            'B'     =>  12,
                            'C'     =>  12,
                            'D'     =>  12,
                            'E'     =>  12,
                            'F'     =>  12,
                            'G'     =>  12,
                        ))->rows($cellData)->setFontSize(12);
//
//                        $sheet->row($tot-13, function($row) {
//                            $row->setBackground('#87eabd');
//                        });

                        // 数据内容主题 左对齐
                        $sheet->cells('A1:F'.$tot, function($cells) {
                            $cells->setAlignment('left');
                        });
                        // 菜单 样式
                        $sheet->cells('A1:G1', function($cells) {
                            $cells->setAlignment('center');
                            $cells->setFontWeight('bold');
                        });
                        // 数据统计 样式
                        $sheet->cells('A'.($tot-13).':F'.$tot, function($cells) {
                            $cells->setAlignment('left');
                            $cells->setFontWeight('bold');
                        });
                        // 备注 右对齐
                        $sheet->cells('D'.($tot-11), function($cells) {
                            $cells->setAlignment('right');
                        });
                        // 备注内容样式
                        $sheet->cells('E'.($tot-10).':F'.($tot-6), function($cells) {
                            $cells->setAlignment('left');
                            $cells->setFontColor('#a09b9b');
                            $cells->setFontFamily('Calibri');
                            $cells->setFontWeight('normal');
                            $cells->setFontSize(12);
                        });
                        // 总金额 高亮显示
                        $sheet->cells('F'.($tot-14), function($cells) {
                            $cells->setBackground('#87eabd');
                            $cells->setFontWeight('bold');
                            $cells->setFontSize(14);
                        });

                    });

                })->export('xls');

            }else{
                $totalItems = $res->pagination->total;
                $itemsPerPage = $res->pagination->per_page;
                $currentPage = $res->pagination->current_page;
                if ($http_build_query)
                {
                    $urlPattern = url('finance/listing?'.$http_build_query.'&page=') . '(:num)';
                }else{
                    $urlPattern = url('finance/listing') .'?page=(:num)';
                }
                $paginator = new Paginator($totalItems, $itemsPerPage, $currentPage, $urlPattern);
                $paginator->setPreviousText('上一页');
                $paginator->setNextText('下一页');

                $data['listing']    = $res->results;
                $data['paginator']  = $paginator;
                $data['query']      = $query;

                return view('financeRecords.listing', $data);
            }

        } else {

            return redirect()
                ->back()
                ->withErrors($res->message)
                ->withInput();
        }

Run code

Cut to clipboard

reply | quote | #²

未知 2019-11-25, 20:45:29

单元格合并
Excel 导出使用的是 Maatwebsite/Laravel-Excel 2.1 的 composer 包，Excel 表单元格行列合并同时使用时，后用的将会失效。

 $sheet->mergeCells('D1:F1');
 $sheet->mergeCells('D2:F2');

 $sheet->setMergeColumn([
    'columns' => ['D', 'E', 'F'],
    'rows' => [
        [1, 2]
     ]
  ]);

Run code

Cut to clipboard

$sheet->setMergeColumn([
    'columns' => ['D', 'E', 'F'],
    'rows' => [
         [1, 2]
    ]
]);   

$sheet->mergeCells('D1:F1');
$sheet->mergeCells('D2:F2');

Run code

Cut to clipboard

#,未知,2019-11-25,20:45:54, Laravel Excel 下载合并单元格

public function exportExcel() 
        //$course_list 需要打印的课程列表
        $course_list = [];
        $filename = '结算账单' . date('Y-m-d H_i');
        Excel::create($filename, function($excel) use ($course_list) {
            $data[] = ["结算账单"];
            $data[] = ['收入来源：课程结算'];
            $data[] = ['结算周期：2019.09-2019.10';
            $data[] = ['结算时间：2019.09.25'];
            $data[] = ['课程名称', '课程ID','主讲人','主讲人uid','价格(¥)', '会员总数', '获利金额(¥)'];

            foreach ($course_list as $key=>$course) {
                $data[] = $item_info;
            }

            $excel->sheet('课程结算账单', function ($sheet) use ($data, $width_list) {
                //第一行到第四行合并单元格
                $sheet->mergeCells("A1:G1");
                $sheet->mergeCells("A2:G2");
                $sheet->mergeCells("A3:G3");
                $sheet->mergeCells("A4:G4");

                //第一行标题居中、加粗、设置字符大小
                $sheet->cells("A1:G1", function ($cells) {
                    $cells->setAlignment('center');
                    $cells->setFontWeight('bold');
                    $cells->setFontSize(20);
                });

                //子标题文字居中、背景色设置、加粗
                $sheet->cells("A5:G5", function($cells) {
                    $cells->setAlignment('center');
                    $cells->setBackground('#C8EAFF');
                    $cells->setFontWeight('bold');
                    $cells->setFontSize(16);
                });

                //剩下单元格设置
                $sheet->fromArray($data, null, 'A1', false, false)->setFontSize(16);
            });

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

Run code

Cut to clipboard

#,未知,2019-11-25,20:46:24, PHPExcel合并与拆分单元格

$objPHPExcel;
$filepath="c:\temp.xlsx";
try {
    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objPHPExcel = $objReader->load($filepath);
} catch (Exception $e) {
    die();
}
$column_index = "A";
//清空要合并的首行单元格值，用于填充合并后的单元格值
$objPHPExcel->getActiveSheet()->setCellValue($column_index.''.$beginRow,'');
//合并单元格,值为''
$objPHPExcel->getActiveSheet()->mergeCells($column_index.''.$beginRow.":".$column_index.''.$endRow);
//拆分单元格,将清空合并前单元格的值并还原合并前单元格的样式
$objPHPExcel->getActiveSheet()->unmergeCells($column_index.''.$beginRow.":".$column_index.''.$endRow);

Run code

Cut to clipboard

reply | quote | #³

未知 2019-11-25, 21:00:37

Laravel 中使用 PhpOffice 复杂样式的Excel 导出

安装
PhpExcel 已停止维护,laravel-excel目前已更新至3.1版本,虽然很方便,但对于复杂样式的Excel导出还是没有太多办法.特别是需要导出给别的软件导入用的,必须要遵循对方的模板的情况…

laravel-excel中使用的是PhpOffice,可以说是PhpExcel的后续版本,提供了非常全面的样式操作. 在laravel下面方便的用户法就是直接先安装laravel-excel,然后使用PhpOffice\PhpSpreadsheet\Spreadsheet来解决复杂的导出.


composer require maatwebsite/excel

Run code

Cut to clipboard

编程实现

组织
实际应用中,一般是不止导出一种格式的Excel文件的,所以建议建立一个Exports目录,所有导出类都放这里,每一个类负责一种格式导出.


<?php
namespace App\Exports;

class UserExport
{
    protected $prarm;
    
    protected $filename;
    
    public function __construct($prarm,$filename)
    {
        $this->param = $param;
        $this->filename = $filename;
    }
    
    public function outExcel()
    {
        //通过参数$param获取需要导出的数据
        $data = User::where(' .... ')->get();
        
        //TODO 开始各种样式编辑
        
    }
    
}

Run code

Cut to clipboard

样式
新建一个Spreadsheet 对象,操作excel都靠它了


$spreadsheet = new Spreadsheet();

Run code

Cut to clipboard

全局指定的样式(比如字体,对齐,单元格格式)


$styles = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
            'numberFormat' => [
                'formatCode' => \PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_TEXT,
            ],
        ];
$spreadsheet->getDefaultStyle()->getFont()->setName('宋体')->setSize(10);
$spreadsheet->getDefaultStyle()->applyFromArray($styles);

Run code

Cut to clipboard

对单元格操作


$sheet = $spreadsheet->getActiveSheet();

Run code

Cut to clipboard

不显示网络线


$sheet->setShowGridlines(false);

Run code

Cut to clipboard

设置列宽


//设置A列为20宽
$sheet->getColumnDimension('A')->setWidth(20);

Run code

Cut to clipboard

设置行高


//设置第22行的行高为166
$sheet->getRowDimension('12')->setRowHeight(166);

Run code

Cut to clipboard

合并单元格


//从B8到C9合并
$sheet->mergeCells('B8:C9');

Run code

Cut to clipboard

拆分


$sheet->unmergeCells('B10:E14');

Run code

Cut to clipboard

设置单元格内容


$sheet->setCellValue('A2', '姓名');

Run code

Cut to clipboard

换行


$sheet->setCellValue('A1', '这是导入文件模板\n请不要改动格式 \n 注:日期格式: yyyy-mm-dd');
$sheet->getStyle('A1')->getAlignment()->setWrapText(true);

Run code

Cut to clipboard

设置字体和大小


$sheet->getStyle('A1')->getFont()->setSize(14)->setBold(true);

Run code

Cut to clipboard

写入数据


$i=2;
        foreach ($res as $data) {
            $i++;
            $sheet->setCellValue('A' . $i, $data['name']);
            $sheet->setCellValue('B' . $i, $data['last_name_p']);
            $sheet->setCellValue('C' . $i, $data['first_name_p']);
            $sheet->setCellValue('D' . $i, $data['gender']);
            ...
        }

Run code

Cut to clipboard

设置边框


 $styles = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => '000000'],
                ],
            ],
        ];
        $sheet->getStyle('A3:D' . $i)->applyFromArray($styles);

Run code

Cut to clipboard

设置字体颜色


$sheet->getStyle('B14')->applyFromArray([
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT,
            ],
        ])->getFont()->getColor()->setRGB('F57C3B');
      
$sheet->getStyle('C8')->getFont()->setBold(true)
            ->setColor(Color::indexedColor(3));

Run code

Cut to clipboard

设置背景色


$sheet->getStyle('B7:G12')->getFill()
            ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
            ->getStartColor()->setRGB('CDFECE');

Run code

Cut to clipboard

保存文件


header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $this->filename . '"');
header('Cache-Control: max-age=0');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xls');
$writer->save('php://output');
return true;

Run code

Cut to clipboard

调用


// blade 中的a标签 
<a href="/users/export/1"  target="_blank">导出用户信息</a>

Route::get('/users/export/{param}', function ($param) {
   $obj = new UserExport($param,'用户列表.xls')
   return $obj->outExcel();
});

Run code

Cut to clipboard

总结
PhpOffice中的api非常的多,写法也很灵活,参数方面和其它api可以参考
PhpSpreadsheet Documentation

#,未知,2019-11-25,21:02:18, PHPExcel 设置excel单元格宽高

#设置单元格宽高
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);#设置单元格行高
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40);#设置单元格宽度
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);

Run code

Cut to clipboard

#,未知,2019-11-25,21:02:48,

            // 自动调整行宽
            $item_cell = 'A';
            $excel_php->setActiveSheetIndex(0)
                ->getColumnDimension($item_cell)
                ->setAutoSize(true);

Run code

Cut to clipboard

reply | quote | #⁴

未知 2019-11-25, 21:05:16

laravel excel 导出表格
1:创建导出文件，传入数据

$cover = [

['第','一','行'],

['第','二','410000000000000000'.''],//防止数字过长导致格式乱码 例如身份证需在字段后加空格

['第','三','行'],

['',' ',''],//空白行，用于分页打印分割内容，如果不填则会连在一起无法分页

]；

 

表格格式先按照数组格式存储，样式在后续调整

Excel::create(iconv('UTF-8','GBK', '申请材料'), function ($excel) use ($cover) {

    $excel->sheet('score', function ($sheet)use ($cover) {

        $sheet->rows($cover);

//后续操作写在此处

          });

})->export('xls');

2:表格样式和部分功能

设置宽度：

$sheet->setWidth(array( 'A' => 16,'B' => 64));

设置高度：

$sheet->setHeight(array(
    ($i * 20 + 1) => 40,//设置每行的高度
    ($i * 20 + 2) => 40,

));

设置单元格字体样式:

$sheet->cells('A1 :A20'), function ($cells) {

$cells->setFontSize(16);//字体大小

$cells->setFontFamily('simsun');//字体样式

$cells->setValignment('center');//字体垂直居中

$cells->setAlignment('center');//字体水平居中

$cells->setBorder('none','thin', 'none', 'thin');//设置表格边框

});

设置打印时页面边距

$sheet->setPageMargin(array(

    0.8, 0, 0, 0.4));

打印导出图片到文档

$card_id= $cover [3][1] ;//获取到图片数据

$img = DB::select( );//查询图片名

$src = $img[0]->photopath;

if (@getimagesize(storage_path() . '/photo/' . $src) == null) {
    $src = 'error.jpg';

}//判断是否存在图片，不存在则返回错误图片名称

if (sizeof($img) > 0) {
    /*实例化插入图片类*/
    $objDrawing = new PHPExcel_Worksheet_Drawing();
    /*设置图片路径 切记：只能是本地图片*/
    $objDrawing->setPath(storage_path(). '/photo/' . $src);
    /*设置图片高度*/
    $objDrawing->setHeight(120);
    $objDrawing->setWidth(120);
    /*设置图片要插入的单元格*/
    $objDrawing->setCoordinates('I2');
    $objDrawing->setWorksheet($sheet);

}

设置分页打印区域

$print_area= '';

$print_area= $print_area . 'A' . ($i * 23 + 1) . ':I' . ($i * 23 + 22) . ',';//循环生成需打印的内容'A1:B2,A3:B4,'

$print_area= substr($print_area,0,strlen($print_area)-1);//以字符串形式传入打印区域

$sheet->setPrintArea($print_area);

合并单元格

$sheet->mergeCells('A1:B2');

Run code

Cut to clipboard

reply | quote | #⁵

未知 2019-11-25, 21:07:06

针式打印机纸张规格
打印纸的尺寸:(单位CM)
241一等份、二等份、三等份的尺寸分别是:24.1*28、24.1*14、24.1*9.31
120一等份、二等分、三等份的尺寸分别是:12*28、12*14、12*9.31
190一等份、二等分、三等份的尺寸分别是:19*28、19*14、19*9.31
381的尺寸是:38.1*28

reply | quote | #⁶

广西南宁市 2020-01-03, 11:17:09

            case 'join__data':
                $item_type_id=Request::input('m')+0;
                $datattime=Request::input('datattime');

                if(!$datattime) $datattime=time();
                else $datattime=strtotime($datattime);

                //echo $datattime; regdate
                //获取所有代理共享站
                $join=DB::table('join')
                ->select('id','join_user_id as join_id','join_user_name','join_phone','manager_phone','item_type_id')
                //->where('item_type_id',$item_type_id)
                ->where('status',1)
                ->get();
                //dump($join_lists);

                //今日注册会员
                $users=DB::table('users')
                ->select('id as users_id','from_id as users_from_id','regdate')
                ->where('from_id','>',0)
                //->where('regdate','>',$datattime)
                ->get();
                //dump($users);

                //今日订单
                $order_services=DB::table('order_services')
                ->select('id as order_services_id','arrival_money as order_services_arrival_money','indate as o_indate','servicered_id')
                ->where('status',3)
                //->where('indate','>',$datattime)
                ->get();

                //今日收益
                $gd_incomelog=DB::table('gd_incomelog')
                ->select('id as gd_incomelog_id','money as gd_incomelog_money','fromuid as gd_incomelog_fromuid','indate as g_indate')
                ->where('is_money_active',1)
                //->where('indate','>',$datattime)
                ->get();

                //今日提现
                $get_income=DB::table('get_income')
                ->select('id as get_income_id','get_money as get_income_get_money','fromuid as get_income_fromuid','indate as vgi_indate')
                ->where('status',1)
                //->where('indate','>',$datattime)
                ->get();

                foreach($join as $k=>&$v){
                    foreach($users as $ku=>$vu){
                        if($v->join_id==$vu->users_from_id){
                            $v->join_num_total++; //累计关注人数
                            if($vu->regdate>$datattime) $v->join_num++;//当日本级累计关注人数

                            //当日成交订单
                            foreach($order_services as $ko=>$vo){
                                if($vu->id==$vo->servicered_id){
                                    if($vo->o_indate>$datattime){
                                        $v->join_vo_num++; //当日成交订单量
                                        $v->join_vo_arrival_money++; //当日成交订单金额
                                    }
                                    $v->join_vo_num_total++;
                                    $v->join_vo_arrival_money_total++;
                                }
                            }
                        }
                    }
                    foreach($gd_incomelog as $kg=>$vg){
                        if($v->join_id==$vg->gd_incomelog_fromuid){
                            if($vg->g_indate>$datattime) $v->join_get_income_get_money++; //当日收益金额
                            $v->join_get_income_get_money_total++;
                        }
                    }
                    foreach($get_income as $kgi=>$vgi){
                        if($v->join_id==$vgi->get_income_fromuid){
                            if($vgi->vgi_indate>$datattime) $v->join_vgi_money++; //当日提现金额
                            $v->join_vgi_money_total++;
                        }
                    }

                }

                //dump($join);exit;
                
                //导出报表
                $item_type_id_name=date('Y年m月d日',$datattime).($item_type_id==1?'代理人':'共享站').'统计表';
                $cellData[]=[$item_type_id_name];
                $cellData[]=[
                    '序号',
                    ($item_type_id==1?'代理人':'共享站'),
                    '电话号码',
                    '关注人数',
                    '',
                    '',
                    '',

                    '成交单量',
                    '',
                    '',
                    '',

                    '成交金额',
                    '',
                    '',
                    '',

                    '收益金额',
                    '',
                    '',
                    '',

                    '提现金额',
                    '',
                    '',
                    '',

                    '备注'
                ];
                
                
                
                
                $cellData[]=[
                    ' ',
                    ' ',
                    ' ',
                    '当日本级',
                    '当日一级',
                    '当日二级',
                    '累计关注',

                    '当日本级',
                    '当日一级',
                    '当日二级',
                    '累计成交',

                    '当日本级',
                    '当日一级',
                    '当日二级',
                    '累计成交',

                    '当日本级',
                    '当日一级',
                    '当日二级',
                    '累计收益',

                    '当日本级',
                    '当日一级',
                    '当日二级',
                    '累计提现',
                ];
                
                //一级
                foreach($join as $k1=>&$v1){
                    foreach($join as $k2=>$v2){
                        //1级
                        if($v2->manager_phone==$v1->join_phone){
                            $v1->r1=$v2;
                            //2级
                            foreach($join as $k3=>$v3){
                                if($v3->manager_phone==$v2->join_phone) $v1->r2=$v3;
                            }
                        }
                    }
                }
                //dump($join);exit;
                foreach($join as $k=>$v){
                    if($v->item_type_id==$item_type_id){
                        $cellData[]=[
                            $k+1,
                            $v->join_user_name,
                            $v->join_phone,

                            //关注人数
                            $v->join_num+0,
                            $v->r1->join_num+0,
                            $v->r2->join_num+0,
                            $v->join_num_total+0,

                            //成交单量
                            $v->join_vo_num+0,
                            $v->r1->join_vo_num+0,
                            $v->r2->join_vo_num+0,
                            $v->join_vo_num_total+0,

                            //成交金额
                            $v->join_vo_arrival_money+0,
                            $v->r1->join_vo_arrival_money+0,
                            $v->r2->join_vo_arrival_money+0,
                            $v->join_vo_arrival_money_total+0,

                            //收益金额
                            $v->join_get_income_get_money+0,
                            $v->r1->join_get_income_get_money+0,
                            $v->r2->join_get_income_get_money+0,
                            $v->join_get_income_get_money_total+0,

                            //提现金额
                            $v->join_vgi_money+0,
                            $v->r1->join_vgi_money+0,
                            $v->r2->join_vgi_money+0,
                            $v->join_vgi_money_total+0,
                        ];
                    }
                }
                Excel::create($item_type_id_name,function($excel) use ($cellData){
                    $excel->sheet('score', function($sheet) use ($cellData){
    
                        $sheet->rows($cellData)->setWidth(
                            array(
                                //调整导出表格单元格宽度
                                'A' => '9',
                                'B' => '12',
                                'C' => '12',
                                'D' => '9',
                                'E' => '9',
                                'F' => '9',
                                'G' => '12',
                                'H' => '9',
                                'I' => '9',
                                'J' => '9',
                                'K' => '12',
                                'L' => '9',
                                'M' => '9',
                                'N' => '9',
                                'O' => '12',
                                'P' => '9',
                                'Q' => '9',
                                'R' => '9',
                                'S' => '12',
                                'T' => '9',
                                'V' => '9',
                                'U' => '9',
                                'X' => '20',
                                'W' => '15',
                            )
                            );
                            //->setFontSize(20);//->setHeight(200);//->setWrapText(true);
    
                        //不显示网络线
                        //$sheet->setShowGridlines(false);
    
                        //第一行到第四行合并单元格
                        $sheet->mergeCells("A1:X1");
                        $sheet->mergeCells("A2:A3");
                        $sheet->mergeCells("B2:B3");
                        $sheet->mergeCells("C2:C3");
                        $sheet->mergeCells("D2:G2");
                        $sheet->mergeCells("H2:K2");
                        $sheet->mergeCells("L2:O2");
                        $sheet->mergeCells("P2:S2");
                        $sheet->mergeCells("T2:W2");
                        $sheet->mergeCells("X2:X2");
                        $sheet->cells("D2:G2", function ($cells) {
                            $cells->setAlignment('center');$cells->setFontWeight('bold');
                        });
                        $sheet->cells("H2:K2", function ($cells) {
                            $cells->setAlignment('center');$cells->setFontWeight('bold');
                        });
                        $sheet->cells("L2:O2", function ($cells) {
                            $cells->setAlignment('center');$cells->setFontWeight('bold');
                        });
                        $sheet->cells("P2:S2", function ($cells) {
                            $cells->setAlignment('center');$cells->setFontWeight('bold');
                        });
                        $sheet->cells("T2:W2", function ($cells) {
                            $cells->setAlignment('center');$cells->setFontWeight('bold');
                        });
                        //第一行标题居中、加粗、设置字符大小
                        $sheet->cells("A1:X1", function ($cells) {
                            $cells->setAlignment('center');
                            $cells->setFontWeight('bold');
                            $cells->setFontSize(20);
                        });

                        $sheet->cells("A2:C2", function ($cells) {
                            $cells->setAlignment('center');
                            $cells->setFontWeight('bold');
                        });

                        $sheet->cells("X2", function ($cells) {
                            $cells->setAlignment('center');
                            $cells->setFontWeight('bold');
                        });
                    });
                })->export('xls');
        
                

            break;

Run code

Cut to clipboard

reply | quote | #⁷

Records:7 1/1 «‹1 ›»

lizhenqiu blog

laravel phpexcel设置单元格

相关日志

有过 7 条评论 »

留下評論