在現(xiàn)在的項目里，不管是電商項目還是別的項目，在管理端都會有導出的功能，比方說訂單表導出，用戶表導出，業(yè)績表導出。這些都需要提前生成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();//創(chuàng)建一個新的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", "訂單狀態(tài)"); //寫入數(shù)據(jù)$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 或 Xlsif ($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.0exit;    }

到這一步其實就已經(jīng)成功了95%了。剩下的5%只需要你調用上面的方法傳入正確的參數(shù)即可

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

這里的$info是你的訂單詳情。必須要做時間篩選，如果不做時間篩選的話 數(shù)據(jù)量一多，你的系統(tǒng)會崩的。

這里的for循環(huán)就是你傳入的 $info。 如果說你的 $info 是空的 那么導出的excel打開的時候還會出現(xiàn)

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實現(xiàn)生成Excel文件并導出的示例詳解的文章就介紹到這了,更多相關PHP生成導出Excel內容請搜索以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持！