把数据导出为excel格式的文件,过程很简单,加上下载
phpexcel
插件的时间,网速快的话。整个流程完成,十分钟左右。这是项目实例。
1.首先 composer require "phpoffice/phpexcel": "dev-develop" -vvv下载此插件
2.然后在Autoloader.php修改
register
()
方法
路径、具体代码 如下
路径:vendor/phpoffice/phpexcel/classes/phpexcel/autoloader.php
public static function
register
()
{
/*if (function_exists('__autoload')) {
// Register any existing autoloader function with SPL, so we don't get any clashes
spl_autoload_register('__autoload');
}
// Register ourselves with SPL
if (version_compare(PHP_VERSION, '5.3.0') >= 0) {
return spl_autoload_register(array('PHPExcel_Autoloader', 'load'), true, true);
} else {
return spl_autoload_register(array('PHPExcel_Autoloader', 'load'));
}*/
//以上代码是原生的,注释掉,添加如下代码
$functions
=
spl_autoload_functions
()
;
foreach
(
$functions
as
$function
)
spl_autoload_unregister
(
$function
)
;
$functions
=
array_merge
(
array
(
array
(
'PHPExcel_Autoloader'
,
'Load'
))
,
$functions
)
;
foreach
(
$functions
as
$function
)
$x
=
spl_autoload_register
(
$function
)
;
return
$x
;
}
3.在控制其中按如下代码格式写就好了(改成自己的字段,表头)
(1)在控制器中引用命名空间
(2)其次在控制器中的代码如下,
//
导出mysql数据到
excel
文件
public function
actionExport
()
{
$objectPHPExcel
= new
PHPExcel
()
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
;
//
数据开始
//mysql查询语
$products
=
Consumerinfo
::
find
()
->
orderBy
(
'data_time DESC'
)
->
all
()
;
$n
=
1
;
//
用于数据循环
//
报表头的输出
$objectPHPExcel
->
getActiveSheet
()
->
mergeCells
(
'B1:G1'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'B1'
,
'
销售数据表
'
)
;
// $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2','
产品信息表
');
// $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('B2','
产品信息表
');
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
getStyle
(
'B1'
)
->
getFont
()
->
setSize
(
24
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
getStyle
(
'B1'
)
->
getAlignment
()
->
setHorizontal
(\
PHPExcel_Style_Alignment
::
HORIZONTAL_CENTER
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'B2'
,
'
日期:
'
.
date
(
"Y
年
m
月
j
日
"
))
;
/* $objectPHPExcel->setActiveSheetIndex(0)->setCellValue('G2','
状态:
2[
待审核
],3[
审核通过
],4[
审核未通过
]');
$objectPHPExcel->setActiveSheetIndex(0)->getStyle('G2')
->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);*/
//
表头
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'A'
)
->
setWidth
(
5
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'B3'
,
'
组名
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'B'
)
->
setWidth
(
10
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'C3'
,
'
组员
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'C'
)
->
setWidth
(
17
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'D3'
,
'
销售微信号
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'D'
)
->
setWidth
(
22
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'E3'
,
'
来源渠道
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'E'
)
->
setWidth
(
10
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'F3'
,
'
数据时间
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'F'
)
->
setWidth
(
25
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'G3'
,
'
创建时间
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'G'
)
->
setWidth
(
25
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'H3'
,
'
总共人数
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'H'
)
->
setWidth
(
15
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'I3'
,
'
减少人数
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'I'
)
->
setWidth
(
15
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'J3'
,
'
新增人数
'
)
;
$objectPHPExcel
->
getActiveSheet
()
->
getColumnDimension
(
'J'
)
->
setWidth
(
15
)
;
$objectPHPExcel
->
setActiveSheetIndex
(
0
)
->
setCellValue
(
'K3'
,
'
状态
'
)
;
foreach
(
$products
as
$product
) {
//
数据
$n
++
;
switch
(
$product
->
status
){
case
2
:
$status
=
"
未审核
"
;
break
;
case
3
:
$status
=
"
未审核
"
;
break
;
case
4
:
$status
=
"
未审核
"
;
break
;
default:break
;
}
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'B'
.
(
$n
+
2
)
,
$product
->
group_name
)
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'C'
.
(
$n
+
2
)
,
$product
->
acount_name
)
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'D'
.
(
$n
+
2
)
,
$product
->
sellwechat_num
)
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'E'
.
(
$n
+
2
)
,
$product
->
sources
)
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'F'
.
(
$n
+
2
)
,
date
(
'Y-m-d H:i:s'
,
$product
->
data_time
))
;//这里加了个date函数处理时间数据
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'G'
.
(
$n
+
2
)
,
date
(
'Y-m-d H:i:s'
,
$product
->
create_time
))
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'H'
.
(
$n
+
2
)
,
$product
->
total_persons
)
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'I'
.
(
$n
+
2
)
,
$product
->
reduce_persons
)
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'J'
.
(
$n
+
2
)
,
$product
->
increase_persons
)
;
$objectPHPExcel
->
getActiveSheet
()
->
setCellValue
(
'K'
.
(
$n
+
2
)
,
$status
)
;
}
//
数据结束
ob_end_clean
()
;
ob_start
()
;
header
(
'Content-Type : application/vnd.ms-excel'
)
;
header
(
'Content-Disposition:attachment;filename="'
.
'sales_datas-'
.
date
(
"Ymj"
)
.
'.xls"'
)
;//给文件命名
$objWriter
=
\
PHPExcel_IOFactory
::
createWriter
(
$objectPHPExcel
,
'Excel5'
)
;
$objWriter
->
save
(
'php://output'
)
;//输出excel文件,并自动下载
}