How to export tables to Excel with PHP-PHP Problem-php.cn

How to export tables to Excel with PHP

Guanhui

Release： 2023-03-01 08:14:02

Original

3493 people have browsed it

How to export tables to Excel with PHP

How does PHP realize the export of table Excel

First download the PHPExcel package and introduce the package into the project; then instantiate the PHPExcel object; then Set the columns of the table and the queue attributes; then add the data to the table; and finally output the table file data.

PHPExcel

is a PHP class library used to operate Office Excel documents. It is based on Microsoft's OpenXML standard and PHP language . You can use it to read and write spreadsheets in different formats

ThinkPHP Example

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;
}

Copy after login