thinkphp 框架中使用区块狗系统 APP 开发 T:/ I8O.2853.296O 黎灿 +PHPExcel，按模板导出 excel
1 public function exportAll()
2 {
3 // 获取年月和考勤天数
4 $date = $this->_GET(‘date’);
5 $days = $this->_GET(‘days’);
6
7 require_once(“Classes/PHPExcel.php”);
8 include(“Classes/PHPExcel/IOFactory.php”);
9 $temPath = “./TempFile/kaoqing.xlsx”;
10 // 检查文件路径
11 if(!file_exists($temPath)){
12 $this->error(‘ 模板不存在 ’);
13 return;
14 }
15 // 加载模板
16 $phpexcel = PHPExcel_IOFactory::createReader(“Excel2007”)->load($temPath);
17
18 $card_create_db =M(‘Qyattendance_record’);
19 $groupMember = $card_create_db->alias(“r”)->join(‘tp_Qyusers as u on u.id =r.uid’,’left’)
20 ->where(“date_format(r.date,’%Y/%m’) ='”.$date.”‘”)
21 ->group(‘uid’)
22 ->field(‘uid,r.name,u.name as fullname, count(r.date) as realdays’)
23 ->select();
24 $rowIndex = 5;
25 $i = 0;
26 foreach($groupMember as $item){
27 // 一览表写入数据
28 // 检查名称
29 if(empty($item[‘fullname’])){
30 $item[‘fullname’] = “ 空名称 ”.$i;
31 }
32 $phpexcel->getSheet(0)->setCellValue(‘B’.$rowIndex, $item[‘name’]);
33 $phpexcel->getSheet(0)->setCellValue(‘C’.$rowIndex, $item[‘fullname’]);
34 $phpexcel->getSheet(0)->setCellValue(‘D’.$rowIndex, $days);
35 $phpexcel->getSheet(0)->setCellValue(‘E’.$rowIndex, $item[‘realdays’]);
36 if($i > 0){
37 // 拷贝表
38 $cloned_sheet = clone $phpexcel->getSheet(1);
39 $cloned_sheet->setTitle($item[‘fullname’]);
40 $phpexcel->addSheet($cloned_sheet);
41 }else{
42 $phpexcel->getSheet(1)->setTitle($item[‘fullname’]);
43 }
44 // 获取员工考勤数据
45 $members = $card_create_db->alias(“r”)->join(‘tp_Qyusers as u on u.id =r.uid’,’left’)
46 ->where(“date_format(r.date,’%Y/%m’) ='”.$date.”‘ and r.uid = ‘”.$item[‘uid’].”‘”)
47 ->order(‘uid’)
48 ->field(‘r.uid,r.name as rname,r.creatime,r.outtime,r.worktime,r.date,u.name as fullname,outtime–creatime-3600 as seconds’)
49 ->select();
50 $memberIndex = $i + 1;
51 // 写入员工详细考勤数据
52 $vindex = 2;
53 $j = 1;
54 foreach($members as $v){
55 // 编号
56 $phpexcel->getSheet($memberIndex)->setCellValue(‘A’.$vindex,$j);
57 // 姓名
58 $phpexcel->getSheet($memberIndex)->setCellValue(‘B’.$vindex,$v[‘fullname’]);
59 // 日期
60 $phpexcel->getSheet($memberIndex)->setCellValue(‘C’.$vindex,$v[‘date’]);
61 // 检查时间
62 if(empty($v[‘creatime’])){
63 $starttime = ”;
64 }else{
65 $starttime = date(‘H:i:s’,$v[‘creatime’]);
66 }
67 if(empty($v[‘outtime’])){
68 $endtime = ”;
69 }else{
70 $endtime = date(‘H:i:s’,$v[‘outtime’]);
71 }
72 // 上班时间
73 $phpexcel->getSheet($memberIndex)->setCellValue(‘D’.$vindex,$starttime);
74 // 下班时间
75 $phpexcel->getSheet($memberIndex)->setCellValue(‘E’.$vindex,$endtime);
76 // 休息时间
77 $phpexcel->getSheet($memberIndex)->setCellValue(‘F’.$vindex,’01:00′);
78 // 计算考勤时间
79 $time = ”;
80 $remark = ”;
81 $seconds = (int)$v[‘seconds’];
82 if($seconds > 0){
83 $hours = floor($seconds/3600);
84 $minutes = floor($seconds%3600/60);
85 $time = $hours.”:”.$minutes;
86 if($seconds < 28800){
87 $remark = ‘ 考勤时间不足 ’;
88 }
89 }else{
90 $time = ’00:00′;
91 }
92 if(empty($v[‘outtime’])){
93 $remark = ‘ 未签下班 ’;
94 }
95 $phpexcel->getSheet($memberIndex)->setCellValue(‘G’.$vindex,$time);
96 // 备注
97 $phpexcel->getSheet($memberIndex)->setCellValue(‘H’.$vindex,$remark);
98 $vindex++;
99 $j++;
100 }
101 $i++;
102 $rowIndex++;
103 }
104
105 // 导出属性设置
106 $date = str_replace(“/”,”_”,$date);
107 $outputFileName = “kaoqing_”.$date.”.xlsx”;
108 require_once(“Classes/PHPExcel/Writer/Excel2007.php”);
109 $objWriter = new PHPExcel_Writer_Excel2007($phpexcel);
110 header(“Content-Type: application/force-download”);
111 header(“Content-Type: application/octet-stream”);
112 header(“Content-Type: application/download”);
113 header(‘Content-Disposition:inline;filename=”‘.$outputFileName.'”‘);
114 header(“Content-Transfer-Encoding: binary”);
115 header(“Expires: Mon, 26 Jul 1997 05:00:00 GMT”);
116 header(“Last-Modified: ” . gmdate(“D, d M Y H:i:s”) . ” GMT”);
117 header(“Cache-Control: must-revalidate, post-check=0, pre-check=0”);
118 header(“Pragma: no-cache”);
119 $objWriter->save(‘php://output’);
120 }