文章来源(hbsjsd.cn)湖北高端网站定制开发公司-速建时代
1.下载安装地址 https://github.com/PHPOffice/PhpSpreadsheet
2. 引用
use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Writer\Xlsx; use PhpOffice\PhpSpreadsheet\IOFactory;
3.实例化
$phpexcel=new Spreadsheet(); $phpexcel->setActiveSheetIndex(0); $sheet=$phpexcel->getActiveSheet();
4.数据组合
$sheet->setTitle('订单明细'); $sheet->setCellValue('A1','姓名') ->setCellValue('B1','手机号') ->setCellValue('C1','订单号') ->setCellValue('D1','收货信息') ->setCellValue('F1','属性') ->setCellValue('G1','金额') ->setCellValue('D2','收货人') ->setCellValue('E2','地址') ->setCellValue('G2','支付金额') ->setCellValue('H2','优惠劵') ->setCellValue('I2','会员优惠') ->setCellValue('J1','订单生成时间') ->setCellValue('K1','完成时间') ->setCellValue('L1','订单状态') ->setCellValue('M1','会员号') ->setCellValue('N1','商品名称') ->setCellValue('O1','商品规格'); $sheet->getStyle('A1:O2')->getAlignment()->setHorizontal( \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER); $sheet->getStyle('A1:O2')->getAlignment()->setVertical( \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER); $sheet->mergeCells('A1:A2') ->mergeCells('B1:B2') ->mergeCells('C1:C2') ->mergeCells('D1:E1') ->mergeCells('F1:F2') ->mergeCells('G1:I1') ->mergeCells('J1:J2') ->mergeCells('K1:K2') ->mergeCells('M1:M2') ->mergeCells('N1:N2') ->mergeCells('O1:O2') ->mergeCells('L1:L2'); $sheet->getStyle('A1:O2')->getFont()->setBold(true); $sheet->getStyle('G1:I2')->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); $sheet->getRowDimension(1)->setRowHeight(18); $sheet->getRowDimension(2)->setRowHeight(18); $sheet->getColumnDimension('A')->setWidth(12); $sheet->getColumnDimension('B')->setAutoSize(true); $sheet->getColumnDimension('C')->setAutoSize(true); $sheet->getColumnDimension('J')->setAutoSize(true); $sheet->getColumnDimension('K')->setAutoSize(true); $sheet->getColumnDimension('L')->setWidth(12); $sheet->getColumnDimension('D')->setWidth(12); $sheet->getColumnDimension('E')->setWidth(30); $sheet->getStyle('E')->getAlignment()->setWrapText(true); $sheet->getColumnDimension('O')->setWidth(25); $sheet->getColumnDimension('N')->setWidth(30); $sheet->getStyle('N')->getAlignment()->setWrapText(true); $currow=0; foreach ($res as $key=>$v){ $currow=$key+3; $sheet->setCellValue('A'.$currow,$v['address']['name']) ->setCellValue('B'.$currow,$v['address']['phone']) ->setCellValue('C'.$currow,' '.$v['out_trade_no']) ->setCellValue('D'.$currow,$v['address']['name']) ->setCellValue('E'.$currow,$v['address']['address'].$v['address']['detail']) ->setCellValue('F'.$currow,$v['attr']) ->setCellValue('G'.$currow,$v['amount']) ->setCellValue('H'.$currow,$v['goods_tag']) ->setCellValue('I'.$currow,$v['price_popu']) ->setCellValue('J'.$currow,$v['time']) ->setCellValue('K'.$currow,$v['status_time']) ->setCellValue('L'.$currow,status($v['status'])) ->setCellValue('M'.$currow,$v['payer']) ->setCellValue('N'.$currow,$v['description']) ->setCellValue('O'.$currow,attr($v['attr']).'-'.$v['uid'].'-'.$v['pid'].'-'.'购买件数'.$v['num']); } $sheet->setCellValue('A'.($currow+1),"合计"); $sheet->setCellValue('B'.($currow+1),"=sum(G3:G".$currow.")"); $sheet->setCellValue('C'.($currow+1),"完成交易额"); $sheet->setCellValue('D'.($currow+1),$this->status(3)); $sheet->setCellValue('E'.($currow+1),"待发货"); $sheet->setCellValue('F'.($currow+1),$this->status(1)); $sheet->setCellValue('G'.($currow+1),"退款金额"); $sheet->setCellValue('H'.($currow+1),$this->status('4,5,6,7')); $sheet->setCellValue('I'.($currow+1),"待签收"); $sheet->setCellValue('J'.($currow+1),$this->status('2')); $style_array = array( 'borders' => array( 'allborders' => array( 'style' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN ) ) ); $sheet->getStyle('A1:O'.($currow+1))->applyFromArray($style_array); $time=date('Y-m-d', time()); $filename="订单详情".$time; $this->excelsave($phpexcel,$filename,'Xls');
5.浏览器下载
protected function excelsave($phpexcel,$filename,$format){ // $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'); } header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format)); header('Cache-Control: max-age=0'); $objWriter = IOFactory::createWriter($phpexcel, $format); $objWriter->save('php://output'); }
[声明]原创不易,请转发者备注下文章来源(hbsjsd.cn)【速建时代】。