PHP实现导出Excel表格,设置高,宽,字体大小等样式,带图片导出

60 篇文章 0 订阅

第一步:需要把PHPExcel引入到项目中;我这里把他已经把这个文件上传到博客了,下面是网址,下载下来直接就可以放入到vendor目录下面就行,不用其他操作。
https://download.csdn.net/download/weixin_42489436/11248777

第二步:就是后台导出代码了:

 public function daochucheliang()
    {
        //先获取数据
        $where['comid'] = session('uid');
        $res = M('cheliang')->where($where)->select();
       // var_dump($res);die;

        //下面就是导出的步骤了
        vendor('PHPExcel0.Classes.PHPExcel');

        $objPHPExcel = new \PHPExcel();
        $objPHPExcel->createSheet(0);
        $objPHPExcel->setActiveSheetIndex(0);
       //只需要把你想要的字段改成你自己需要的就可以了!!!
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A1', '考试人')
                ->setCellValue('B1', '车牌')
                ->setCellValue('C1', '考试时间')
                ->setCellValue('D1', '考试分数')
                ->setCellValue('E1', '签名');
		
		//设置表头行高
        $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(35);
        $objPHPExcel->getActiveSheet()->getRowDimension(2)->setRowHeight(22);
        $objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(20);
  
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);//设置单元格宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(50);//设置单元格宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);//设置单元格宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);//设置单元格宽度
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);      //第一行是否加粗
            $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);      //第一行是否加粗
            $objPHPExcel->getActiveSheet()->getStyle('C1')->getFont()->setBold(true);      //第一行是否加粗
            $objPHPExcel->getActiveSheet()->getStyle('D1')->getFont()->setBold(true);      //第一行是否加粗
            $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->setBold(true);      //第一行是否加粗
            //$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(16);         //第一行字体大小

        //设置字体样式
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setName('黑体');
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(20);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A3:AE3')->getFont()->setBold(true);
            
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setName('宋体');
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(16);
        
        $objPHPExcel->getActiveSheet()->getStyle('A4:AE'.($k+2))->getFont()->setSize(10);
        
            // 设置垂直居中
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('C1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('D1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('E1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

       //设置居中
        $PHPExcel->getActiveSheet()->getStyle('A1:AE'.($k+2))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            
        //所有垂直居中
        $PHPExcel->getActiveSheet()->getStyle('A1:AE'.($k+2))->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            
        //设置单元格边框
        $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
        
        //设置自动换行
        $PHPExcel->getActiveSheet()->getStyle('A3:AE'.($k+2))->getAlignment()->setWrapText(true);
         
            // 设置行高度
            //设置默认行高
            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); 
            
			//设置每一行行高
            $objPHPExcel->getActiveSheet()->getRowDimension($key+4)->setRowHeight(30);
            //第一行行高
            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);               
            
			//合并单元格
    	    $objPHPExcel->getActiveSheet()->mergeCells('A1:AE1');
        	$objPHPExcel->getActiveSheet()->mergeCells('A2:AE2');
 
            //循环添加数据,注意的是下面的$kk+2,是因为$kk是下标,从0开始的,而第一行是你的标题,所以要从第二行开始才是你的数据
            foreach ($data as $kk => $vv) {
                $no = $kk + 2;
                    $currentSheet = $objPHPExcel->getActiveSheet();
                    $currentSheet->setCellValue('A' . $no, $vv['xueyuan']['name']);
                    $currentSheet->setCellValue('B' . $no, $vv['xueyuan']['chepaihao']);
                    $currentSheet->setCellValue('C' . $no, date('Y-m-d H:i:s',$vv['addtime']).'至'.date('Y-m-d H:i:s',$vv['sbttime']));
                    $currentSheet->setCellValue('D' . $no, $vv['score']);
                    //设置单元格高度,这个是重点哦
                    $currentSheet->getRowDimension($no) -> setRowHeight(40);
                    // 图片生成
                    $objDrawing[$kk] = new \PHPExcel_Worksheet_Drawing();//这个就是生成图片的类(重点)
                    $objDrawing[$kk]->setPath('./'.$vv['qmimg']);/图片的路径
                    // 设置宽度高度
                    $objDrawing[$kk]->setHeight(85);//照片高度
                    $objDrawing[$kk]->setWidth(100); //照片宽度
                    /*设置图片要插入的单元格*/
                    $objDrawing[$kk]->setCoordinates('E'.$no);
                    // 图片偏移距离
                     $objDrawing[$kk]->setOffsetX(12);
                     $objDrawing[$kk]->setOffsetY(12);
                    $objDrawing[$kk]->setWorksheet($objPHPExcel->getActiveSheet());
            }

        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');

        header('Content-Disposition: attachment;filename="' . '文件名称'. '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }

原文链接:https://blog.csdn.net/weixin_42489436/article/details/92833412

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值