PHP实现生成Excel文件并导出的示例详解

在现在的项目里，不管是电商项目还是别的项目，在管理端都会有导出的功能，比方说订单表导出，用户表导出，业绩表导出。这些都需要提前生成excel表，然后在导出，实际上是在代码里生成一张excel表，然后通过下载api进行导出的。好了 先给大家讲一下示例

利用php导出excel我们大多会直接生成.xls文件，这种方便快捷。

首先我们先在项目中引入几个类

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;
use \PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

然后我们在进行封装一个生成并导出excel表的方法，这里我们用订单表做示例

//订单信息导出excel
    public function order_outputProjectExcel($info){
        $newExcel = new Spreadsheet();//创建一个新的excel文档
        $objSheet = $newExcel->getActiveSheet();//获取当前操作sheet的对象
        $date = date("Ymd",time());
        $name = "订单信息表";
        $objSheet->setTitle($name);//设置当前sheet的标题
 
        //样式设置 - 合并和拆分
        $objSheet->mergeCells("A1:P1"); //合并单元格
        //$sheet -> unmergeCells("C3:G3"); //拆分单元格
        $objSheet->setCellValue("A1",$name);
 
        //设置第一栏的中文标题
        $objSheet->setCellValue("A2", "编号")
            ->setCellValue("B2", "商品图片")
            ->setCellValue("C2", "商品名称")
            ->setCellValue("D2", "会员编号")
            ->setCellValue("E2", "直属")
            ->setCellValue("F2", "非直属")
            ->setCellValue("G2", "订单号")
            ->setCellValue("H2", "商品单价")
            ->setCellValue("I2", "实付金额")
            ->setCellValue("J2", "商品佣金")
            ->setCellValue("K2", "会员佣金")
            ->setCellValue("L2", "直属佣金")
            ->setCellValue("M2", "非直属佣金")
            ->setCellValue("N2", "支付时间")
            ->setCellValue("O2", "支付渠道")
            ->setCellValue("P2", "订单状态");
 
        //写入数据
        $dataCount = count($info);
        $k = 2;
        
        if($dataCount == 0){
            exit;
        }else{
            for ($i=0;$i<$dataCount;$i++){
                $k = $k + 1;
                $order=$i+1;
                $objSheet->setCellValue("A" . $k, $info[$i]["id"])
                    ->setCellValue("B" . $k, $info[$i]["goods_image"])
                    ->setCellValue("C" . $k, $info[$i]["goods_name"])
                    ->setCellValue("D" . $k, $info[$i]["user_num"])
                    ->setCellValue("E" . $k, $info[$i]["user_upteam"])
                    ->setCellValue("F" . $k, $info[$i]["user_un_upteam"])
                    ->setCellValue("G" . $k, $info[$i]["order_id"])
                    ->setCellValue("H" . $k, $info[$i]["goods_price"])
                    ->setCellValue("I" . $k, $info[$i]["payment"])
                    ->setCellValue("J" . $k, $info[$i]["goods_yongjin"])
                    ->setCellValue("K" . $k, $info[$i]["user_yongjin"])
                    ->setCellValue("L" . $k, $info[$i]["user_up_yongjin"])
                    ->setCellValue("M" . $k, $info[$i]["user_un_upyongjin"])
                    ->setCellValue("N" . $k, $info[$i]["paymenttime"])
                    ->setCellValue("O" . $k, $info[$i]["pay_way_ch"])
                    ->setCellValue("P" . $k, $info[$i]["status_ch"]);
            }
        }
 
        //设定样式
        //所有sheet的表头样式 加粗
        $font = [
            "font" => [
                "bold" => true,
                "size" => 14,
            ],
        ];
        $objSheet->getStyle("A1:P1")->applyFromArray($font);
 
        //样式设置 - 水平、垂直居中
        $styleArray = [
            "alignment" => [
                "horizontal" => Alignment::HORIZONTAL_CENTER,
                "vertical" => Alignment::VERTICAL_CENTER
            ],
        ];
        $objSheet->getStyle("A1:P2")->applyFromArray($styleArray);
 
        //所有sheet的内容样式-加黑色边框
        $borders = [
            "borders" => [
                "outline" => [
                    "borderStyle" => Border::BORDER_THIN,
                    "color" => ["argb" => "000000"],
                ],
                "inside" => [
                    "borderStyle" => Border::BORDER_THIN,
                ]
            ],
        ];
        $objSheet->getStyle("A1:P".$k)->applyFromArray($borders);
 
        //设置宽度
        $cell = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P"];
        foreach($cell as $k=>$v){
            $objSheet->getColumnDimension($v)->setWidth(20);
 
//            $objSheet->getColumnDimension($v)->setAutoSize(true);
        }
 
        $this->downloadExcel($newExcel,$name,"Xlsx");
    }
    
    
    
    
    //下载
    private function downloadExcel($newExcel,$filename,$format)
    {
        ob_end_clean();
        ob_start();
        // $format只能为 Xlsx 或 Xls
        if ($format == "Xlsx") {
            header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        } elseif ($format == "Xls") {
            header("Content-Type: application/vnd.ms-excel");
        }
//  strtolower($format)
        header("Content-Disposition: attachment;filename="
            . $filename . "." . strtolower($format));
        header("Cache-Control: max-age=0");
        $objWriter = IOFactory::createWriter($newExcel, $format);
        $objWriter->save("php://output");

        //通过php保存在本地的时候需要用到
        // $objWriter->save($dir."/demo.xlsx");
 
        //以下为需要用到IE时候设置
        // If you"re serving to IE 9, then the following may be needed
        //header("Cache-Control: max-age=1");
        // If you"re serving to IE over SSL, then the following may be needed
        //header("Expires: Mon, 26 Jul 1997 05:00:00 GMT"); // Date in the past
        //header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT"); // always modified
        //header("Cache-Control: cache, must-revalidate"); // HTTP/1.1
        //header("Pragma: public"); // HTTP/1.0
        exit;
    }

到这一步其实就已经成功了95%了。剩下的5%只需要你调用上面的方法传入正确的参数即可

model("Apimodel")->order_outputProjectExcel($info);

这里的$info是你的订单详情。必须要做时间筛选，如果不做时间筛选的话 数据量一多，你的系统会崩的。

这里的for循环就是你传入的 $info。 如果说你的 $info 是空的 那么导出的excel打开的时候还会出现

for ($i=0;$i<$dataCount;$i++){
    $k = $k + 1;
    $order=$i+1;
    $objSheet->setCellValue("A" . $k, $info[$i]["id"])
        ->setCellValue("B" . $k, $info[$i]["goods_image"])
        ->setCellValue("C" . $k, $info[$i]["goods_name"])
        ->setCellValue("D" . $k, $info[$i]["user_num"])
        ->setCellValue("E" . $k, $info[$i]["user_upteam"])
        ->setCellValue("F" . $k, $info[$i]["user_un_upteam"])
        ->setCellValue("G" . $k, $info[$i]["order_id"])
        ->setCellValue("H" . $k, $info[$i]["goods_price"])
        ->setCellValue("I" . $k, $info[$i]["payment"])
        ->setCellValue("J" . $k, $info[$i]["goods_yongjin"])
        ->setCellValue("K" . $k, $info[$i]["user_yongjin"])
        ->setCellValue("L" . $k, $info[$i]["user_up_yongjin"])
        ->setCellValue("M" . $k, $info[$i]["user_un_upyongjin"])
        ->setCellValue("N" . $k, $info[$i]["paymenttime"])
        ->setCellValue("O" . $k, $info[$i]["pay_way_ch"])
        ->setCellValue("P" . $k, $info[$i]["status_ch"]);
  }

最后就是这样的效果

到此这篇关于PHP实现生成Excel文件并导出的示例详解的文章就介绍到这了,更多相关PHP生成导出Excel内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！