PHP实现生成Excel文件并导出的示例详解（php生成php文件）一篇读懂

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