tp5 实现excel导出

首先下载下来 composer   

composer require phpoffice/phpexcel

将下载好的PHPExcel的第三方类放置在extend中（放置第三方类的地方）

<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()
    {
        $PHPExcel=new \PHPExcel();
        $PHPSheet = $PHPExcel->getActiveSheet();
        // 设置个表格标题
        $PHPSheet->setTitle("代理商");
        $PHPSheet->setCellValue("A1", "ID");
        $PHPSheet->setCellValue("B1", "分类名称");

        //设置表格宽度
        $PHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
        $PHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);


        // 设置垂直居中
        $PHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $PHPExcel->setActiveSheetIndex(0)->getStyle('B')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        $list = \app\admin\model\Category::select();
        $list = (new \think\Collection($list))->toArray();
        $list = get_cate_list($list);
        $i=2;
        foreach ($list as $key => $value) {
            $PHPSheet->setCellValue('A' . $i, '' . $value['id']);
            $PHPSheet->setCellValue('B' . $i, '' . $value['cate_name']);;
            $i++;
        };
        $file_name = date('Y-m-d_H:i:s') . '表格' . '.xls';
        $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");  //文件通过浏览器下载
    }

 

下方是可以实现图片

 

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"); //文件通过浏览器下载
}