집 > 기사 > 백엔드 개발 > PHP를 사용하여 테이블을 Excel로 내보내는 방법

PHP를 사용하여 테이블을 Excel로 내보내는 방법

Guanhui원래의: 2020-05-15 11:41:443462검색

PHP를 사용하여 Excel 테이블을 내보내는 방법

먼저 PHPExcel 패키지를 다운로드하고 프로젝트에 패키지를 도입한 다음 PHPExcel 개체를 인스턴스화한 다음 테이블의 열과 대기열 속성을 설정합니다. to the table ;마지막으로 테이블 파일 데이터를 출력합니다.

PHPExcel

은 Office Excel 문서를 운영하는 데 사용되는 PHP 클래스 라이브러리로 Microsoft의 OpenXML 표준과 PHP 언어를 기반으로 합니다. 이를 사용하여 다양한 형식의 스프레드시트를 읽고 쓸 수 있습니다

ThinkPHP 예제

public function exportExcel()
{
    //先获取数据
    $where[&#39;comid&#39;] = session(&#39;uid&#39;);
    $res = M(&#39;cheliang&#39;)->where($where)->select();
   // var_dump($res);die;

    //下面就是导出的步骤了
    vendor(&#39;PHPExcel0.Classes.PHPExcel&#39;);

    $objPHPExcel = new \PHPExcel();
    $objPHPExcel->createSheet(0);
    $objPHPExcel->setActiveSheetIndex(0);
   //只需要把你想要的字段改成你自己需要的就可以了！！！
        $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue(&#39;A1&#39;, &#39;考试人&#39;)
            ->setCellValue(&#39;B1&#39;, &#39;车牌&#39;)
            ->setCellValue(&#39;C1&#39;, &#39;考试时间&#39;)
            ->setCellValue(&#39;D1&#39;, &#39;考试分数&#39;)
            ->setCellValue(&#39;E1&#39;, &#39;签名&#39;);

        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;B&#39;)->setWidth(20);//设置单元格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;C&#39;)->setWidth(50);//设置单元格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;D&#39;)->setWidth(20);//设置单元格宽度
        $objPHPExcel->getActiveSheet()->getColumnDimension(&#39;E&#39;)->setWidth(20);//设置单元格宽度
        $objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getFont()->setBold(true);      //第一行是否加粗
        $objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getFont()->setBold(true);      //第一行是否加粗
        $objPHPExcel->getActiveSheet()->getStyle(&#39;C1&#39;)->getFont()->setBold(true);      //第一行是否加粗
        $objPHPExcel->getActiveSheet()->getStyle(&#39;D1&#39;)->getFont()->setBold(true);      //第一行是否加粗
        $objPHPExcel->getActiveSheet()->getStyle(&#39;E1&#39;)->getFont()->setBold(true);      //第一行是否加粗
        //$objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getFont()->setSize(16);         //第一行字体大小
        // 设置垂直居中
        $objPHPExcel->getActiveSheet()->getStyle(&#39;A1&#39;)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;B1&#39;)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;C1&#39;)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;D1&#39;)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
        $objPHPExcel->getActiveSheet()->getStyle(&#39;E1&#39;)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

        // 设置行高度
        $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
        $objPHPExcel->getActiveSheet()->getRowDimension(&#39;1&#39;)->setRowHeight(30);    //第一行行高
        //循环添加数据，注意的是下面的$kk+2，是因为$kk是下标，从0开始的，而第一行是你的标题，所以要从第二行开始才是你的数据
        foreach ($data as $kk => $vv) {
            $no = $kk + 2;
                $currentSheet = $objPHPExcel->getActiveSheet();
                $currentSheet->setCellValue(&#39;A&#39; . $no, $vv[&#39;xueyuan&#39;][&#39;name&#39;]);
                $currentSheet->setCellValue(&#39;B&#39; . $no, $vv[&#39;xueyuan&#39;][&#39;chepaihao&#39;]);
                $currentSheet->setCellValue(&#39;C&#39; . $no, date(&#39;Y-m-d H:i:s&#39;,$vv[&#39;addtime&#39;]).&#39;至&#39;.date(&#39;Y-m-d H:i:s&#39;,$vv[&#39;sbttime&#39;]));
                $currentSheet->setCellValue(&#39;D&#39; . $no, $vv[&#39;score&#39;]);
                //设置单元格高度，这个是重点哦
                $currentSheet->getRowDimension($no) -> setRowHeight(40);
                // 图片生成
                $objDrawing[$kk] = new \PHPExcel_Worksheet_Drawing();//这个就是生成图片的类（重点）
                $objDrawing[$kk]->setPath(&#39;./&#39;.$vv[&#39;qmimg&#39;]);/图片的路径
                // 设置宽度高度
                $objDrawing[$kk]->setHeight(85);//照片高度
                $objDrawing[$kk]->setWidth(100); //照片宽度
                /*设置图片要插入的单元格*/
                $objDrawing[$kk]->setCoordinates(&#39;E&#39;.$no);
                // 图片偏移距离
                 $objDrawing[$kk]->setOffsetX(12);
                 $objDrawing[$kk]->setOffsetY(12);
                $objDrawing[$kk]->setWorksheet($objPHPExcel->getActiveSheet());
        }

    $objPHPExcel->setActiveSheetIndex(0);
    header(&#39;Content-Type: application/vnd.ms-excel&#39;);

    header(&#39;Content-Disposition: attachment;filename="&#39; . &#39;文件名称&#39;. &#39;.xls"&#39;);
    header(&#39;Cache-Control: max-age=0&#39;);
    $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;);
    $objWriter->save(&#39;php://output&#39;);
    exit;
}

PHP를 사용하여 테이블을 Excel로 내보내는 방법

관련 기사