原生php导出csv文件

来自技术流笔记
跳转至: 导航搜索
  1 require('./db/dbManager.php');
  2 require('./session.php');
  3 require('./department_utils.php');
  4 
  5 class ExportCSV{
  6     function __construct(){
  7         $this->db=new dbManager();
  8         $this->out = fopen('php://output', 'w');
  9     }
 10     
 11     function output(&$row){
 12         for($i=0;$i<count($row);$i++){
 13             $row[$i]=iconv('utf-8','gb18030',$row[$i]);
 14         }
 15         fputcsv($this->out,$row);
 16     }
 17     
 18     function user($opts){
 19         
 20         if(isset($opts['department']) && $opts['department']>0){
 21             $department=(int)$opts['department'];
 22             $name=$this->db->querySingle('SELECT DepartmentName FROM department WHERE ID=?',[$department]);
 23             if(!$name)
 24                 return;
 25             $fileName=$name.'-用户列表';
 26         }else{
 27             $department=0;
 28             $fileName='所有部门-用户列表';
 29         }
 30         
 31         if(isset($opts['download'])){
 32             //header('Content-Disposition: attachment;filename="'.$fileName.'.csv"');
 33             $this->set_filename($fileName.'.csv');
 34         }
 35 
 36         $h=['部门','用户名','姓名','手机','工号','身份'];
 37         $this->output($h);
 38         $sql='SELECT COUNT(*) FROM users';
 39         if($department>0){
 40             $deps=department_with_child_sql($this->db,$department);
 41             $sql.=' WHERE DepartmentId IN '.$deps;
 42         }
 43         $total=$this->db->querySingle($sql);
 44         $sql='SELECT d.DepartmentName,u.Username,u.Name,u.Phone,u.empno,r.name FROM users u';
 45         $sql.=' LEFT JOIN department d ON d.ID=u.DepartmentId';
 46         $sql.=' LEFT JOIN role r ON r.id=u.role';
 47         if($department>0){
 48             $deps=department_with_child_sql($this->db,$department);
 49             $sql.=' WHERE DepartmentId IN '.$deps;
 50         }
 51         $sql.=' LIMIT ?,10';
 52         for($i=0;$i<$total;$i+=10){
 53             $arr=$this->db->queryAll($sql,[$i]);
 54             if(!$arr)
 55                 break;
 56             for($j=0;$j<count($arr);$j++)
 57                 $this->output($arr[$j]);
 58         }
 59     }
 60     
 61     function time_range($b,$e){
 62         return date('Ymd',$b).'-'.date('Ymd',$e);
 63     }
 64     
 65     function exam($opts){
 66         if(isset($opts['department']) && $opts['department']>0){
 67             $department=(int)$opts['department'];
 68             $name=$this->db->querySingle('SELECT DepartmentName FROM department WHERE ID=?',[$department]);
 69             if(!$name)
 70                 return;
 71         }else{
 72             $department=0;
 73             $name='所有部门';
 74         }
 75         $begin=(int)$opts['begin'];
 76         $end=(int)$opts['end'];
 77         $begin_str=date('Y-m-d H:i:s',$begin);
 78         $end_str=date('Y-m-d H:i:s',$end);
 79         
 80         $fileName=$name.'-考试-'.$this->time_range($begin,$end);
 81         if(isset($opts['download'])){
 82             //header('Content-Disposition: attachment;filename="'.$fileName.'.csv"');
 83             $this->set_filename($fileName.'.csv');
 84         }
 85         
 86         $h=['部门','考试名','时间','时长(分钟)','总分','及格线(百分制)'];
 87         $this->output($h);
 88         
 89         $sql='SELECT COUNT(*) FROM exam e';
 90         $sql.=' WHERE e.ExamTime BETWEEN ? AND ?';
 91         if($department>0){
 92             $deps=department_with_child_sql($this->db,$department);
 93             $sql.=' AND DepartmentId IN '.$deps;
 94         }
 95         $total=$this->db->querySingle($sql,[$begin_str,$end_str]);
 96         $sql='SELECT d.DepartmentName,e.ExamName,e.ExamTime,e.ExamDuration,e.Score,e.PassScore FROM exam e';
 97         $sql.=' LEFT JOIN department d ON d.ID=e.DepartmentId';
 98         $sql.=' WHERE e.ExamTime BETWEEN ? AND ?';
 99         if($department>0){
100             $deps=department_with_child_sql($this->db,$department);
101             $sql.=' AND DepartmentId IN '.$deps;
102         }
103         $sql.=' LIMIT ?,10';
104         for($i=0;$i<$total;$i+=10){
105             $arr=$this->db->queryAll($sql,[$begin_str,$end_str,$i]);
106             if(!$arr)
107                 break;
108             for($j=0;$j<count($arr);$j++){
109                 if(!$arr[$j][0])
110                     $arr[$j][0]='所有部门';
111                 $this->output($arr[$j]);
112             }
113         }
114     }
115     
116     function grade($opts){
117         if(isset($opts['department']) && $opts['department']>0){
118             $department=(int)$opts['department'];
119             $name=$this->db->querySingle('SELECT DepartmentName FROM department WHERE ID=?',[$department]);
120             if(!$name)
121                 return;
122         }else{
123             $department=0;
124             $name='所有部门';
125         }
126         $begin=(int)$opts['begin'];
127         $end=(int)$opts['end'];
128         $begin_str=date('Y-m-d H:i:s',$begin);
129         $end_str=date('Y-m-d H:i:s',$end);
130         
131         $fileName=$name.'-考试成绩-'.$this->time_range($begin,$end);
132         if(isset($opts['download'])){
133             //header('Content-Disposition: attachment;filename="'.$fileName.'.csv"');
134             $this->set_filename($fileName.'.csv');
135         }
136         
137         $h=['部门','名字','考试名','时间','总分','分数'];
138         $this->output($h);
139 
140         $sql='SELECT COUNT(*) FROM grade g';
141         $sql.=' LEFT JOIN exam e ON e.ID=g.ExamId';
142         $sql.=' LEFT JOIN users u ON u.ID=g.UserId';
143         $sql.=' WHERE e.ExamTime BETWEEN ? AND ?';
144         if($department>0){
145             $deps=department_with_child_sql($this->db,$department);
146             $sql.=' AND u.DepartmentId IN '.$deps;
147         }
148         
149         $total=$this->db->querySingle($sql,[$begin_str,$end_str]);
150         $sql='SELECT d.DepartmentName,u.Name,e.ExamName,e.ExamTime,e.Score,g.Score FROM grade g';
151         $sql.=' LEFT JOIN exam e ON e.ID=g.ExamId';
152         $sql.=' LEFT JOIN users u ON u.ID=g.UserId';
153         $sql.=' LEFT JOIN department d ON d.ID=u.DepartmentId';
154         $sql.=' WHERE e.ExamTime BETWEEN ? AND ?';
155         if($department>0){
156             $deps=department_with_child_sql($this->db,$department);
157             $sql.=' AND u.DepartmentId IN '.$deps;
158         }
159         $sql.=' LIMIT ?,10';
160         for($i=0;$i<$total;$i+=10){
161             $arr=$this->db->queryAll($sql,[$begin_str,$end_str,$i]);
162             if(!$arr)
163                 break;
164             for($j=0;$j<count($arr);$j++){
165                 if(!$arr[$j][0])
166                     $arr[$j][0]='所有部门';
167                 $this->output($arr[$j]);
168             }
169         }
170     }
171     
172     function exercise($opts){
173         if(isset($opts['department']) && $opts['department']>0){
174             $department=(int)$opts['department'];
175             $name=$this->db->querySingle('SELECT DepartmentName FROM department WHERE ID=?',[$department]);
176             if(!$name)
177                 return;
178         }else{
179             $department=0;
180             $name='所有部门';
181         }
182         $begin=(int)$opts['begin'];
183         $end=(int)$opts['end'];
184         
185         $fileName=$name.'-练习数据-'.$this->time_range($begin,$end);
186         if(isset($opts['download'])){
187             //header('Content-Disposition: attachment;filename="'.$fileName.'.csv"');
188             $this->set_filename($fileName.'.csv');
189         }
190         
191         if(!$this->db->exec('ATTACH DATABASE "exercise.db.php" AS "EX"'))
192             return;
193         $sql='SELECT COUNT(*) FROM EX.exercise e';
194         $sql.=' LEFT JOIN users u ON u.ID=e.uid';
195         $sql.=" WHERE e.time BETWEEN $begin AND $end";
196         if($department>0){
197             $deps=department_with_child_sql($this->db,$department);
198             $sql.=' AND u.DepartmentId IN '.$deps;
199         }
200         $total=$this->db->querySingle($sql);
201         
202         $h=['部门','身份','名字','时间','结果'];
203         $this->output($h);
204         
205         $sql='SELECT d.DepartmentName,r.name,u.Name,datetime(e.Time,"unixepoch","localtime"),e.result FROM EX.exercise e';
206         $sql.=' LEFT JOIN users u ON u.ID=e.uid';
207         $sql.=' LEFT JOIN department d ON d.ID=u.DepartmentId';
208         $sql.=' LEFT JOIN role r ON r.ID=u.role';
209         $sql.=" WHERE e.time BETWEEN $begin AND $end";
210         if($department>0){
211             $deps=department_with_child_sql($this->db,$department);
212             $sql.=' AND u.DepartmentId IN '.$deps;
213         }
214         $sql.=' ORDER BY e.time ASC LIMIT ?,10';
215         for($i=0;$i<$total;$i+=10){
216             $arr=$this->db->queryAll($sql,[$i]);
217             if(!$arr)
218                 break;
219             for($j=0;$j<count($arr);$j++){
220                 if(!$arr[$j][4])
221                     $arr[$j][4]='错误';
222                 else
223                     $arr[$j][4]='正确';
224                 $this->output($arr[$j]);
225             }
226         }
227     }
228     
229     function set_filename($fileName){
230         $userAgent=$_SERVER['HTTP_USER_AGENT'];
231         if($userAgent && strstr($userAgent,"Firefox")){
232             header('Content-Disposition: attachment;filename*="'."utf8''".urlencode($fileName).'"');
233         }else{
234             header('Content-Disposition: attachment;filename="'.urlencode($fileName).'"');
235         }
236     }
237     
238     function study($opts){
239         if(isset($opts['department']) && $opts['department']>0){
240             $department=(int)$opts['department'];
241             $name=$this->db->querySingle('SELECT DepartmentName FROM department WHERE ID=?',[$department]);
242             if(!$name)
243                 return;
244         }else{
245             $department=0;
246             $name='所有部门';
247         }
248         $begin=(int)$opts['begin'];
249         $end=(int)$opts['end'];
250         
251         $fileName=$name.'-学习时间-'.$this->time_range($begin,$end);
252         if(isset($opts['download']))
253             //header('Content-Disposition: attachment;filename="'.$fileName.'.csv"');
254             $this->set_filename($fileName.'.csv');
255         
256         $sql='SELECT COUNT(*) FROM studyRecord s';
257         $sql.=' LEFT JOIN users u ON u.ID=s.UserId';
258         $sql.=" WHERE s.Begin BETWEEN $begin AND $end";
259         if($department>0){
260             $deps=department_with_child_sql($this->db,$department);
261             $sql.=' AND u.DepartmentId IN '.$deps;
262         }
263         $total=$this->db->querySingle($sql);
264         
265         $h=['部门','身份','名字','时间','时长(秒)','类型'];
266         $this->output($h);
267         
268         $sql='SELECT d.DepartmentName,r.name,u.Name,datetime(s.Begin,"unixepoch","localtime"),s.Duration,s.Type FROM studyRecord s';
269         $sql.=' LEFT JOIN users u ON u.ID=s.UserId';
270         $sql.=' LEFT JOIN department d ON d.ID=u.DepartmentId';
271         $sql.=' LEFT JOIN role r ON r.ID=u.role';
272         $sql.=" WHERE s.Begin BETWEEN $begin AND $end";
273         $sql.=" LIMIT ?,10";
274         for($i=0;$i<$total;$i+=10){
275             $arr=$this->db->queryAll($sql,[$i]);
276             if(!$arr)
277                 break;
278             for($j=0;$j<count($arr);$j++){
279                 if($arr[$j][5]==0)
280                     $arr[$j][5]='学习';
281                 else
282                     $arr[$j][5]='练习';
283                 $this->output($arr[$j]);
284             }
285         }
286     }
287     
288     // 每个学员学习时间要按学习类型分组加起来
289     function study2($opts){
290         if(!$this->db->exec('ATTACH DATABASE "exercise.db.php" AS "EX"'))
291             return;
292 
293         if(isset($opts['department']) && $opts['department']>0){
294             $department=(int)$opts['department'];
295             $name=$this->db->querySingle('SELECT DepartmentName FROM department WHERE ID=?',[$department]);
296             if(!$name)
297                 return;
298         }else{
299             $department=0;
300             $name='所有部门';
301         }
302         $begin=(int)$opts['begin'];
303         $end=(int)$opts['end'];
304         
305         $fileName=$name.'-学习时间-'.$this->time_range($begin,$end);
306         if(isset($opts['download']))
307             $this->set_filename($fileName.'.csv');
308         
309         $total=9999;
310         
311         $h=['部门','身份','名字','学习时间(秒)','练习时长(秒)','练习数量','练习正确数量','练习正确率'];
312         $this->output($h);
313         
314         $sql='SELECT d.DepartmentName,r.name,u.Name,u.ID FROM users u';
315         $sql.=' LEFT JOIN department d ON d.ID=u.DepartmentId';
316         $sql.=' LEFT JOIN role r ON r.ID=u.role';
317         if($department>0){
318             $deps=department_with_child_sql($this->db,$department);
319             $sql.=' WHERE u.DepartmentId IN '.$deps;
320         }
321         $sql.=' LIMIT ?,10';
322 
323         for($i=0;$i<$total;$i+=10){
324             $arr=$this->db->queryAll($sql,[$i]);
325             if(!$arr)
326                 break;
327             for($j=0;$j<count($arr);$j++){
328                 $uid=array_pop($arr[$j]);
329                 $temp=$this->db->query("SELECT sum(-s.Duration*(s.Type-1)),sum(s.Duration*s.Type) FROM studyRecord s WHERE s.UserId=? AND s.Begin BETWEEN $begin AND $end",$uid);
330                 if($temp){
331                     $arr[$j][]=(int)$temp[0];
332                     $arr[$j][]=(int)$temp[1];
333                 }else{
334                     $arr[$j][]=0;
335                     $arr[$j][]=0;
336                 }
337                 $temp=$this->db->query('SELECT COUNT(*),SUM(e.result) FROM EX.exercise e WHERE e.uid=?',$uid);
338                 if($temp){
339                     $arr[$j][]=(int)$temp[0];
340                     $arr[$j][]=(int)$temp[1];
341                     if($temp[0]>0){
342                         $arr[$j][]=sprintf("%.2f",$temp[1]/$temp[0]);
343                     }else{
344                         $arr[$j][]=0;
345                     }
346                 }
347                 $this->output($arr[$j]);
348             }
349         }
350     }
351     
352     
353     function get_libnames(){
354         $sql='SELECT Name FROM question_lib group by Name';
355         return $this->db->querySingleAll($sql);
356     }
357 
358     function lib_error($opts){
359         $begin=(int)$opts['begin'];
360         $end=(int)$opts['end'];
361         
362         if(isset($opts['department']) && $opts['department']>0){
363             $department=(int)$opts['department'];
364             $name=$this->db->querySingle('SELECT DepartmentName FROM department WHERE ID=?',[$department]);
365             if(!$name)
366                 return;
367         }else{
368             $department=0;
369             $name='所有部门';
370         }
371         
372         $fileName=$name.'-高频错题-'.$this->time_range($begin,$end);
373         if(isset($opts['download']))
374             $this->set_filename($fileName.'.csv');
375         
376         $libs=$this->get_libnames();
377         
378         if(!$this->db->exec('ATTACH DATABASE "exercise.db.php" AS "EX"'))
379             return;
380         
381         $h=['题库','题型','题目','选项','答案','答题数','答题正确数量','正确率'];
382         $this->output($h);
383         
384         $sql='CREATE TEMP VIEW stat AS SELECT qid,count(*) as count,sum(result) as result,sum(result)*1.0/count(*)*1.0 as accuracy FROM EX.exercise';
385         $sql.=' LEFT JOIN users u ON u.ID=uid';
386         $sql.=" WHERE time BETWEEN $begin AND $end";
387         if($department>0){
388             $deps=department_with_child_sql($this->db,$department);
389             $sql.=' AND u.DepartmentId IN '.$deps;
390         }
391         $sql.=' GROUP BY qid';
392         $ret=$this->db->exec($sql);
393         if(!$ret){
394             return;
395         }
396 
397         $sql='SELECT q.LibName,q.Type,q.Question,q.Option,q.Answer,s.count,s.result,s.accuracy FROM stat s';
398         $sql.=' LEFT JOIN EX.question q ON q.ID=s.qid';
399         $sql.=' WHERE q.LibName=?';
400         $sql.=' AND s.count>=3';
401         $sql.=' ORDER BY s.accuracy ASC';
402         $sql.=' LIMIT 0,10';
403 
404         for($i=0;$i<count($libs);$i++){
405             $temp=$this->db->queryAll($sql,$libs[$i]);
406             if(!$temp)
407                 continue;
408             for($j=0;$j<count($temp);$j++){
409                 $this->output($temp[$j]);
410             }
411         }
412     }
413     
414     //过关记录
415     function manmul_lib($opts){
416         $begin=(int)$opts['begin'];
417         $end=(int)$opts['end'];
418         if(isset($opts['department']) && $opts['department']>0){
419             $department=(int)$opts['department'];
420             $name=$this->db->querySingle('SELECT DepartmentName FROM department WHERE ID=?',[$department]);
421             if(!$name)
422                 return;
423         }else{
424             $department=0;
425             $name='所有部门';
426         }
427         
428         $fileName=$name.'-过关记录-'.$this->time_range($begin,$end);
429         if(isset($opts['download']))
430             $this->set_filename($fileName.'.csv');
431         
432         $libs=$this->get_libnames();
433         
434         if(!$this->db->exec('ATTACH DATABASE "exercise.db.php" AS "EX"'))
435             return;
436         
437         $h=['姓名','部门','过关记录','时间','难度系数'];
438         $this->output($h);
439         
440     }
441    
442 }
443 
444 
445 header('Content-Type: text/plain; charset=GB18030');
446 //header('Content-Type: application/octet-stream');
447 
448 if(!isset($_GET['op']))
449     return;
450 $op=$_GET['op'];
451 if(!isset($_SESSION['AdminId']))
452     return;
453 $ec=new ExportCSV();
454 if(!department_can_admin($ec->db,$_GET['department'],$_SESSION['AdminId']))
455     return;
456 
457 header('Cache-Control: max-age=0');
458 
459 if($op=='user')
460     $ec->user($_GET);
461 else if($op=='exam')
462     $ec->exam($_GET);
463 else if($op=='grade')
464     $ec->grade($_GET);
465 else if($op=='exercise')
466     $ec->exercise($_GET);
467 else if($op=='study')
468     $ec->study2($_GET);
469 else if($op=='libe')
470     $ec->lib_error($_GET);
471 else if($op=='manmul')
472     $ec->manmul_lib($_GET);
473 
474   
475 ?>

下图为study方法中导出的练习数据,头部引入除过session都是封装了crud的db方法类,导出主要参考下面两个方法。

方法:

1 function set_filename($fileName){
2         $userAgent=$_SERVER['HTTP_USER_AGENT'];
3         if($userAgent && strstr($userAgent,"Firefox")){
4             header('Content-Disposition: attachment;filename*="'."utf8''".urlencode($fileName).'"');
5         }else{
6             header('Content-Disposition: attachment;filename="'.urlencode($fileName).'"');
7         }
8     }

方法:

1 function output(&$row){
2         for($i=0;$i<count($row);$i++){
3             $row[$i]=iconv('utf-8','gb18030',$row[$i]);
4         }
5         fputcsv($this->out,$row);
6     }