[PHP知识库]tp6 + excel 导出

composer安装扩展

composer require phpoffice/phpexcel="~1.8"

?导出代码·

   //查询数据库信息
        try {
            $xlsData =ActivityModel::select();
        } catch (\Exception $e) {
            return $e->getMessage();
        }

        //实例化
        $objExcel = new \PHPExcel();

        //设置文档属性
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');


        //设置内容
        $objActSheet = $objExcel->getActiveSheet();
        $key = ord("A");
        $letter = explode(',', "A,B,C,D,E,F,G");
        $arrHeader = array('ID', '活动名称', '活动图片', '活动介绍', '活动金额','排序','状态');
        //填充表头信息
        $lenth = count($arrHeader);
        for ($i = 0; $i < $lenth; $i++) {
            $objActSheet->setCellValue("$letter[$i]1", "$arrHeader[$i]");
        };

        //填充表格信息
        foreach ($xlsData as $k => $v) {
            $k += 2;
            //表格内容
            $objActSheet->setCellValue('A' . $k, $v['id']);
            $objActSheet->setCellValue('B' . $k, $v['activity_name']);
            $objActSheet->setCellValue('C' . $k, $v['activity_img']);
            $objActSheet->setCellValue('D' . $k, $v['activity_desc']);
            $objActSheet->setCellValue('E' . $k, $v['activity_price']);
            $objActSheet->setCellValue('F' . $k, $v['sort']);
            $objActSheet->setCellValue('G' . $k, $v['lifetime']==1?"关闭":"开启");

             图片生成
//            $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing();
//        ;
//            $objDrawing[$k]->setPath(ROOT_PATH."public/static/image/playbtn.png");
//
             设置宽度高度
//            $objDrawing[$k]->setHeight(40);//照片高度
//            $objDrawing[$k]->setWidth(40); //照片宽度
             设置图片要插入的单元格
//            $objDrawing[$k]->setCoordinates('C' . $k);
             图片偏移距离
//            $objDrawing[$k]->setOffsetX(30);
//            $objDrawing[$k]->setOffsetY(12);
//            $objDrawing[$k]->setWorksheet($objExcel->getActiveSheet());

            // 表格高度
            $objActSheet->getRowDimension($k)->setRowHeight(20);
        }

        $width = array(20, 20, 15, 10, 10, 30, 10, 15);
        //设置表格的宽度
        $objActSheet->getColumnDimension('A')->setWidth($width[5]);
        $objActSheet->getColumnDimension('B')->setWidth($width[1]);
        $objActSheet->getColumnDimension('C')->setWidth($width[0]);
        $objActSheet->getColumnDimension('D')->setWidth($width[5]);
        $objActSheet->getColumnDimension('E')->setWidth($width[5]);
        $objActSheet->getColumnDimension('F')->setWidth($width[5]);
        $objActSheet->getColumnDimension('G')->setWidth($width[5]);


        $outfile = md5("活动表" . time()) . ".xlsx";
        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="' . $outfile . '"');
        header("Content-Transfer-Encoding: binary");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');