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

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

//订单信息导出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; } 

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

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']); }