excel实现文件导出功能,带图片
首先把PHPExcel的压缩包解压,将class文件拿出 放在 tp5 目录下 vendor ,重命名 PHPExcel;如图所示:
控制器代码
public function excelphone(){ $res=Db::table('eb_brand')->select(); vendor("PHPExcel.Classes.PHPExcel"); $resultPHPExcel=new \PHPExcel(); // $resultPHPExcel =new \PHPExcel(); $PHPSheet = $resultPHPExcel->getActiveSheet(); // 设置个表格标题 $PHPSheet->setTitle("代理商"); $PHPSheet->setCellValue("A1", "ID"); $PHPSheet->setCellValue("B1", "品牌名称"); $PHPSheet->setCellValue("C1", "排序"); $PHPSheet->setCellValue("D1", "图片地址"); $i = 2; foreach ($res as $key => $value) { $PHPSheet->setCellValue('A' . $i, '' . $value['id']); $PHPSheet->setCellValue('B' . $i, '' . $value['bname']); $PHPSheet->setCellValue('C' . $i, '' . $value['sort']); //获取到图片信息 $img = DB::table('eb_brand')->where('id', $value['id'])->field('image_input')->find(); $objDrawing[$key] = new \PHPExcel_Worksheet_Drawing(); $objDrawing[$key]->setPath('.' . $img['image_input']); //这里拼接 . 是因为要在根目录下获取 $objDrawing[$key]->setHeight(20);//照片高度 $objDrawing[$key]->setWidth(70); //照片宽度 $objDrawing[$key]->setCoordinates('D' . $key); // 图片偏移距离 $objDrawing[$key]->setOffsetX(12); $objDrawing[$key]->setOffsetY(12); $objDrawing[$key]->setWorksheet($resultPHPExcel->getActiveSheet()); // $PHPSheet->setCellValue('D'.$i,''.$value['image_input']); $i++; } //设置导出文件名 $outputFileName = 'total.xls'; $xlsWriter = new \PHPExcel_Writer_Excel5($resultPHPExcel); ob_end_clean(); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download"); header('Content-Disposition:inline;filename="' . $outputFileName . '"'); header("Content-Transfer-Encoding: binary"); header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Pragma: no-cache"); $xlsWriter->save("php://output"); }
html代码
<div> <form action="{:url('admin/upimg/excelphone')}" method="post" enctype="multipart/form-data"> <input type="submit" value="导出excel" class="layui-btn layui-btn-sm"> </form> </div>
带有居中效果的导出
html代码
<div> <form action="{:url('admin/upimg/excel')}" method="post" enctype="multipart/form-data"> <input type="submit" value="导出excel" class="layui-btn layui-btn-sm"> </form> </div>
控制器代码
public function excel() { //查找要导出的数据表 $list = Db::table('eb_brand')->select(); //导出的文件名称 $file_name = date('Y-m-d_H:i:s') . '表格' . '.xls'; dirname(__FILE__); Loader::import('PHPExcel.Classes.PHPExcel');//引入的是extend下的PHPExcel 必须手动引入 Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory'); $PHPExcel=new \PHPExcel(); $objWriter = new \PHPExcel_Writer_Excel2007($PHPExcel); $PHPSheet = $PHPExcel->getActiveSheet(); //设置水平居中 $PHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $PHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $PHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $PHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); // 设置个表格标题 $PHPSheet->setTitle("代理商"); $PHPSheet->setCellValue("A1", "ID"); $PHPSheet->setCellValue("B1", "品牌名称"); $PHPSheet->setCellValue("C1", "排序"); $PHPSheet->setCellValue("D1", "图片地址"); //设置表格宽度 $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20); $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20); $PHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20); $PHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(80); // 设置垂直居中 $PHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $PHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $PHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $PHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $i = 2; foreach ($list as $key => $value) { $PHPSheet->setCellValue('A' . $i, '' . $value['id']); $PHPSheet->setCellValue('B' . $i, '' . $value['bname']); $PHPSheet->setCellValue('C' . $i, '' . $value['sort']); //获取到图片信息 $img = DB::table('eb_brand')->where('id', $value['id'])->field('image_input')->find(); $objDrawing[$key] = new \PHPExcel_Worksheet_Drawing(); $objDrawing[$key]->setPath('.' . $img['image_input']); //这里拼接 . 是因为要在根目录下获取 $objDrawing[$key]->setHeight(20);//照片高度 $objDrawing[$key]->setWidth(70); //照片宽度 $objDrawing[$key]->setCoordinates('D' . $key); // 图片偏移距离 $objDrawing[$key]->setOffsetX(12); $objDrawing[$key]->setOffsetY(12); $objDrawing[$key]->setWorksheet($PHPExcel->getActiveSheet()); // $PHPSheet->setCellValue('D'.$i,''.$value['image_input']); $i++; } $PHPExcel->setActiveSheetIndex(0); $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, "Excel2007"); header('Content-Disposition: attachment;filename=' . $file_name); header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Cache-Control: max-age=0'); $objWriter->save("php://output"); //文件通过浏览器下载 }