PHPExcel实现表格导出

65 阅读 0 评论 43 点赞

我是靠谱客的博主虚幻狗，最近开发中收集的这篇文章主要介绍PHPExcel实现表格导出，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

将下载好或者 PHPExcel的压缩包放在extend下，这是放置第三方类的地方，不要随便配置命名空间，引起不必要的类错误
在这里插入图片描述

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