AdminCommon.php
7.2 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
<?php
namespace app\admin\controller;
use app\api\controller\Common;
use app\common\controller\Backend;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\Db;
/**
* 后台公共
*
* @icon fa fa-circle-o
*/
class AdminCommon extends Backend
{
//导出表格
public static function exportExcel($expTitle,$expCellName,$expTableData,$fileName){
$topNumber = 1;//表头有几行占用
$cellKey = array(
'A','B','C','D','E','F','G','H','I','J','K','L','M',
'N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM',
'AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
);
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setTitle($expTitle);
// $spreadsheet->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s'));
//处理表头
foreach ($expCellName as $k=>$v) {
$sheet->setCellValue($cellKey[$k].$topNumber, $v[1]);//设置表头数据
// $spreadsheet->getActiveSheet()->freezePane($cellKey[$k].($topNumber+1));//冻结窗口
$sheet->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(false);//设置是否加粗
}
//处理数据
//设置单元格居中显示
$styleArray = [
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
],
];
//表头居中
foreach ($expCellName as $k2=>$v2) {
$sheet->getStyle($cellKey[$k2].'1')->applyFromArray($styleArray);
}
foreach ($expTableData as $k=>$v) {
foreach ($expCellName as $k1=>$v1) {
$sheet->setCellValue($cellKey[$k1].($k+1+$topNumber), $v[$v1[0]]);
// $sheet->getColumnDimension($cellKey[$k1])->setWidth(20);//每列宽度
$sheet->getColumnDimension($cellKey[$k1])->setWidth($v1[2]);//每列宽度
$sheet->getStyle($cellKey[$k1].($k+1+$topNumber))->applyFromArray($styleArray);
}
}
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');//告诉浏览器将要输出excel03文件
header('Content-Disposition: attachment;filename="'.$fileName.'.xls"');
header('Cache-Control: max-age=0');
$writer = IOFactory::createWriter($spreadsheet, 'Xls'); //按照指定格式生成Excel文件
$writer->save('php://output');
exit;
}
//转换fastadmin 时间
public static function getDateByFloatValue($dateValue = 0,$calendar_type = 1900){
// Excel中的日期存储的是数值类型，计算的是从1900年1月1日到现在的数值
if (1900 == $calendar_type) { // WINDOWS中EXCEL 日期是从1900年1月1日的基本日期
$myBaseDate = 25569;// php是从 1970-01-01 25569是到1900-01-01所相差的天数
if ($dateValue < 60) {
--$myBaseDate;
}
} else {// MAC中EXCEL日期是从1904年1月1日的基本日期(25569-24107 = 4*365 + 2) 其中2天是润年的时间差？
$myBaseDate = 24107;
}
// 执行转换
if ($dateValue >= 1) {
$utcDays = $dateValue - $myBaseDate;
$returnValue = round($utcDays * 86400);
if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
$returnValue = (integer)$returnValue;
}
} else {
// 函数对浮点数进行四舍五入
$hours = round($dateValue * 24);
$mins = round($dateValue * 1440) - round($hours * 60);
$secs = round($dateValue * 86400) - round($hours * 3600) - round($mins * 60);
$returnValue = (integer)gmmktime($hours, $mins, $secs);
}
return $returnValue-3600*8;// 返回时间戳
}
//导出套餐订单列表
public static function exportOrderList($where){
$expTableData = Common::selectWhereData('order',$where,'createtime,r_id,order_sn,price,content,total_num,use_num,not_use_num,advice,address,organization,pin_user,mark','order_sn asc');
//查询手机号
$r_ids = array_unique(array_column($expTableData,'r_id'));
$res_mobile = Common::selectWhereData('registers',['id'=>['in',$r_ids]],'id,mobile');
foreach($expTableData as $key => $r){
$user_name = Db::name('registers')->where(['id'=>$r['r_id']])->value('user_name');
$expTableData[$key]['user_name'] = $user_name;//Db::name('user')->where(['id'=>$user_id])->value('nickname');
}
$expCellName = [
['createtime','订单时间',15],
['order_sn','订单编号',15],
['user_name','客户姓名',15],
['price','订单金额',15],
['content','购买服务内容',20],
['total_num','购买次数',15],
['use_num','已用服务次数',15],
['not_use_num','剩余服务次数',15],
['advice','所属顾问',10],
['mobile','VIP手机号',20],
['address','详细地址',30],
['organization','归属机构',30],
['pin_user','品拓人员',15],
['mark','备注',40],
];
foreach ($expTableData as &$v) {
$v['mobile'] = '';
foreach ($res_mobile as $value){
if($v['r_id'] == $value['id']){
$v['mobile'] = $value['mobile'];
}
}
$v['createtime'] = date('Y-m-d H:i:s',$v['createtime']);
}
$expTitle = "套餐订单列表";
$fileName = "套餐订单列表";//文件名
self::exportExcel($expTitle,$expCellName,$expTableData,$fileName);
}
//导出服务订单列表
public static function exportServiceList($where){
$expTableData = Common::selectWhereData('service_order',$where,'r_id,order_sn,customer_name,service_time,service_content,service_name,assess,mark','order_sn asc');
//查询手机号
$r_ids = array_unique(array_column($expTableData,'r_id'));
$res_mobile = Common::selectWhereData('registers',['id'=>['in',$r_ids]],'id,mobile');
$expCellName = [
['order_sn','服务订单编号',20],
['customer_name','客户姓名',10],
['service_time','服务时间',20],
['service_content','服务项目',30],
['service_name','服务员',10],
['mobile','VIP手机号',20],
['assess','评价',30],
['mark','备注',40],
];
foreach ($expTableData as &$v) {
$v['mobile'] = '';
foreach ($res_mobile as $value){
if($v['r_id'] == $value['id']){
$v['mobile'] = $value['mobile'];
}
}
$v['service_time'] = date('Y-m-d H:i:s',$v['service_time']);
}
$expTitle = "服务订单列表";
$fileName = "服务订单列表";//文件名
self::exportExcel($expTitle,$expCellName,$expTableData,$fileName);
}
}