首先把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>
带有居中效果的
//导出excelpublic 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');
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"); //文件通过浏览器下载}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
