1.下载最新的PHPExcel组件https://github.com/PHPOffice/PHPExcel
2.将以下两个文件解压至PHPExcel文件夹下并放入extension文件夹下
如图所示
3.在main.php主配置文件下,添加如下代码:
4.在控制器下写如下代码:
- /**
- * 导出EXCEL
- */
- public function actionExport(){
- $objPHPExcel = new PHPExcel();
- $sql = "select * from aff";
- $result = Yii::app()->db->createCommand($sql);
- $query = $result -> queryAll();
- //print_r($query);
- $objPHPExcel->setActiveSheetIndex(0)
- ->setCellValue('A1','会员名称')
- ->setCellValue('B1','会员ID')
- ->setCellValue('C1','收款银行')
- ->setCellValue('D1','收款账号')
- ->setCellValue('E1','收款人')
- ->setCellValue('F1','实发费用')
- ->setCellValue('G1','状态')
- ->setCellValue('H1','结算日期');
- $i=2;
- foreach($query as $v){
- //print_r($v);
- $objPHPExcel->setActiveSheetIndex(0)
- ->setCellValue('A'.$i,$v[username])
- ->setCellValue('B'.$i,$v[userid])
- ->setCellValue('C'.$i,$v[bank])
- ->setCellValue('D'.$i,$v[accountnum])
- ->setCellValue('E'.$i,$v[realname])
- ->setCellValue('F'.$i,$v[amount])
- ->setCellValue('G'.$i,$v[confirm])
- ->setCellValue('H'.$i,$v[remittancetime]);
- //设置填充颜色
- $objPHPExcel->getActiveSheet()->getStyle('F'.$i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
- $objPHPExcel->getActiveSheet()->getStyle('F'.$i)->getFill()->getStartColor()->setARGB('FFFFFF');
- //设置字体靠左
- $objPHPExcel->getActiveSheet()->getStyle('A'.$i.':H'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
- $i++;
- }
- //加粗字体
- $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getFont()->setBold(true);
- //字体靠左
- $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
- //设置每列宽度
- $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(10);
- $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
- $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
- $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15);
- $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
- $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
- $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(10);
- $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12);
- //输出
- $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
- $filename="outexcel.xls";
- header('Content-Type: application/vnd.ms-excel');
- header('Content-Disposition: attachment;filename="'.$filename.'"');
- header('Cache-Control: max-age=0');
- $objWriter->save('php://output');
- }