thinkphp5 导出含图片的excel表格

最近遇到一个需求,导出后台的数据,其中有一栏是图片也要一并导出。

首先查询mysql获取要导出的数据

$data=$this->model->whereIn('id',$ids)->select();
$data = collection($data)->toArray();

实例化相关导出类

$objPHPExcel = new PHPExcel();   
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objActSheet = $objPHPExcel->getActiveSheet();

设置水平居中,有几列数据,就设置几列

$objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('K')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('L')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->setActiveSheetIndex(0)->getStyle('M')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

设置第一列的标题值

$objActSheet->setCellValue('A1', 'ID');
$objActSheet->setCellValue('B1', '参与者');
$objActSheet->setCellValue('C1', '类型');
$objActSheet->setCellValue('D1', '活动名称');
$objActSheet->setCellValue('E1', '类别');
$objActSheet->setCellValue('F1', '将次(称号)');
$objActSheet->setCellValue('G1', '颁奖单位');
$objActSheet->setCellValue('H1', '级别');
$objActSheet->setCellValue('I1', '获奖日期');
$objActSheet->setCellValue('J1', '获奖者');
$objActSheet->setCellValue('K1', '证明材料');
$objActSheet->setCellValue('L1', '添加单位');
$objActSheet->setCellValue('M1', '添加人');

这些字段中,证明材料一列是图片,数据库存的是图片地址

设置表格宽度和垂直居中

       // 设置个表格宽度
           $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16);
           $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(15);
           $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
           $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
           $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
           $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
           $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
           $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
           $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);
           $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(12);
           $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(12);
           $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(12);
           $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(12); 
           // 垂直居中
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('I')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('J')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('K')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('L')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
           $objPHPExcel->setActiveSheetIndex(0)->getStyle('M')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

组装数据,处理图片

foreach($data as $k=>$v){
               $k +=2;
               $objActSheet->setCellValue('A'.$k, $v['id']);   
               $objActSheet->setCellValue('B'.$k, $participant[$v['participant']]);   
               $objActSheet->setCellValue('C'.$k, $status[$v['status']]);   
               $objActSheet->setCellValue('D'.$k, $v['name']);   
               $objActSheet->setCellValue('E'.$k, $type[$v['type']]);   
               $objActSheet->setCellValue('F'.$k, $v['prizes']);   
               $objActSheet->setCellValue('G'.$k, $v['issuing']);   
               $objActSheet->setCellValue('H'.$k,$level[$v['level']]);   
               $objActSheet->setCellValue('I'.$k, $v['dateofaward']);
               $objActSheet->setCellValue('J'.$k, $v['awardee']);    
                       
               //获取到图片信息  
               // 图片生成
               $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
               $objDrawing[$k]->setPath('.'.$v['certificatefile']);//这里拼接 . 是因为要在根目录下获取
               // 设置宽度高度
               $objDrawing[$k]->setHeight(80);//照片高度
               $objDrawing[$k]->setWidth(80); //照片宽度
               /*设置图片要插入的单元格*/
               $objDrawing[$k]->setCoordinates('K'.$k);
               // 图片偏移距离
               $objDrawing[$k]->setOffsetX(12);
               $objDrawing[$k]->setOffsetY(12);
               $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet());
                   
               // 表格内容   
               $objActSheet->setCellValue('L'.$k, $optionsunit_ids[$v['optionsunit_id']]);   
               $objActSheet->setCellValue('M'.$k, $admin_ids[$v['admin_id']]);
                       
               // 表格高度
               $objActSheet->getRowDimension($k)->setRowHeight(80);
                   
}

最后给文件命名,导出下载

           $fileName = '公示表';
           $fileName = iconv("utf-8", "gb2312", $fileName);
           //重命名表
           // $objPHPExcel->getActiveSheet()->setTitle('test');
           //设置活动单指数到第一个表,所以Excel打开这是第一个表
           $objPHPExcel->setActiveSheetIndex(0);
           header('Content-Type: application/vnd.ms-excel');
           header("Content-Disposition: attachment;filename=\"$fileName\"");
           header('Cache-Control: max-age=0');
           $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
           $objWriter->save('php://output'); //文件通过浏览器下载    

 

转载于:https://www.cnblogs.com/myIvan/p/11431168.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
以下是使用ThinkPHP框架和PHPExcel类库导出Excel表格的步骤: 1.下载并安装PHPExcel类库,将下载的PHPExcel文件夹放入项目的vendor目录下。 2.创建一个控制器,例如ExcelController,并在控制器中添加一个export方法。 3.在export方法中,实例化PHPExcel类库,并设置Excel表格的属性,例如表格标题、表格列名等。 4.从数据库中获取需要导出的数据,并将数据填充到Excel表格中。 5.将Excel表格输出到浏览器,让用户可以下载。 以下是一个示例代码: ```php <?php namespace app\index\controller; use think\Controller; use PHPExcel; use PHPExcel_IOFactory; class ExcelController extends Controller { public function export() { // 实例化PHPExcel类库 $objPHPExcel = new PHPExcel(); // 设置Excel属性 $objPHPExcel->getProperties()->setCreator("ThinkPHP") ->setLastModifiedBy("ThinkPHP") ->setTitle("Office 2007 XLSX Test Document") ->setSubject("Office 2007 XLSX Test Document") ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") ->setKeywords("office 2007 openxml php") ->setCategory("Test result file"); // 设置表格标题 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', '姓名') ->setCellValue('B1', '年龄') ->setCellValue('C1', '性别'); // 从数据库中获取数据 $data = db('user')->select(); // 将数据填充到Excel表格中 $i = 2; foreach ($data as $item) { $objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A' . $i, $item['name']) ->setCellValue('B' . $i, $item['age']) ->setCellValue('C' . $i, $item['gender']); $i++; } // 输出Excel表格到浏览器 header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="user.xlsx"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output'); } } ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值