如何将后台数据导出成Excel表格

首先生成 Excel 各种格式参照这个网址：

https://blog.csdn.net/u011391184/article/details/70154306

然后是去得到数据，在控制层的方法（获取数据复杂的话就在model层区写，再返回一个数组）：

//导出统计

public function actionExcel(){

$Rmodel=new ReduserMoney();

$objectPHPExcel = new \PHPExcel();

 

//设置表格头的输出

$objectPHPExcel->setActiveSheetIndex()->setCellValue('A1', '总金额');

$objectPHPExcel->setActiveSheetIndex()->setCellValue('B1', '总人数');

$objectPHPExcel->setActiveSheetIndex()->setCellValue('C1', '总成功人数');

$objectPHPExcel->setActiveSheetIndex()->setCellValue('D1', '真实成功人数');

$objectPHPExcel->setActiveSheetIndex()->setCellValue('E1', '总失败人数');

$objectPHPExcel->setActiveSheetIndex()->setCellValue('F1', '真实失败人数');

$objectPHPExcel->setActiveSheetIndex()->setCellValue('G1', '时间');

//跳转到recharge这个model文件的statistics方法去处理数据

$data=$Rmodel::find()->asArray()->all();

 

//指定开始输出数据的行数

$n = 2;

foreach ($data as $v){

$objectPHPExcel->getActiveSheet()->setCellValue('A'.($n) ,$v['user_money']);

$objectPHPExcel->getActiveSheet()->setCellValue('B'.($n) ,$v['user_num']);

$objectPHPExcel->getActiveSheet()->setCellValue('C'.($n) ,$v['success']);

$objectPHPExcel->getActiveSheet()->setCellValue('D'.($n) ,$v['Rsuccess']);

$objectPHPExcel->getActiveSheet()->setCellValue('E'.($n) ,$v['error']);

$objectPHPExcel->getActiveSheet()->setCellValue('F'.($n) ,$v['Rerror']);

$objectPHPExcel->getActiveSheet()->setCellValue('G'.($n) ,$v['card_time']);

$n = $n +1;

}

ob_end_clean();

ob_start();

header('Content-Type : application/vnd.ms-excel');

 

//设置填充的样式和背景色

$objectPHPExcel->getActiveSheet()->getStyle( 'A1:B1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);

$objectPHPExcel->getActiveSheet()->getStyle( 'A1:B1')->getFill()->getStartColor()->setARGB('7fff00ff');

//合并

// $objectPHPExcel->getActiveSheet()->mergeCells( 'A20:A21');

//设置输出文件名及格式

header('Content-Disposition:attachment;filename="小司统计'.date("YmdHis").'.xls"');

 

//导出.xls格式的话使用Excel5,若是想导出.xlsx需要使用Excel2007

$objWriter= \PHPExcel_IOFactory::createWriter($objectPHPExcel,'Excel5');

$objWriter->save('php://output');

ob_end_flush();

 

//清空数据缓存

unset($data);

}