原生php導(dǎo)出csv文件

antyiwei 發(fā)布于2019-06-28 14:15 / 365人閱讀

摘要：附上結(jié)果圖片下圖為方法中導(dǎo)出的練習(xí)數(shù)據(jù)，頭部引入除過(guò)都是封裝了的方法類，導(dǎo)出主要參考下面兩個(gè)方法。

require("./db/dbManager.php");
require("./session.php");
require("./department_utils.php");

class ExportCSV{
    function __construct(){
        $this->db=new dbManager();
        $this->out = fopen("php://output", "w");
    }
    
    function output(&$row){
        for($i=0;$iout,$row);
    }
    
    function user($opts){
        
        if(isset($opts["department"]) && $opts["department"]>0){
            $department=(int)$opts["department"];
            $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
            if(!$name)
                return;
            $fileName=$name."-用戶列表";
        }else{
            $department=0;
            $fileName="所有部門-用戶列表";
        }
        
        if(isset($opts["download"])){
            //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
            $this->set_filename($fileName.".csv");
        }

        $h=["部門","用戶名","姓名","手機(jī)","工號(hào)","身份"];
        $this->output($h);
        $sql="SELECT COUNT(*) FROM users";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" WHERE DepartmentId IN ".$deps;
        }
        $total=$this->db->querySingle($sql);
        $sql="SELECT d.DepartmentName,u.Username,u.Name,u.Phone,u.empno,r.name FROM users u";
        $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
        $sql.=" LEFT JOIN role r ON r.id=u.role";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" WHERE DepartmentId IN ".$deps;
        }
        $sql.=" LIMIT ?,10";
        for($i=0;$i<$total;$i+=10){
            $arr=$this->db->queryAll($sql,[$i]);
            if(!$arr)
                break;
            for($j=0;$joutput($arr[$j]);
        }
    }
    
    function time_range($b,$e){
        return date("Ymd",$b)."-".date("Ymd",$e);
    }
    
    function exam($opts){
        if(isset($opts["department"]) && $opts["department"]>0){
            $department=(int)$opts["department"];
            $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
            if(!$name)
                return;
        }else{
            $department=0;
            $name="所有部門";
        }
        $begin=(int)$opts["begin"];
        $end=(int)$opts["end"];
        $begin_str=date("Y-m-d H:i:s",$begin);
        $end_str=date("Y-m-d H:i:s",$end);
        
        $fileName=$name."-考試-".$this->time_range($begin,$end);
        if(isset($opts["download"])){
            //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
            $this->set_filename($fileName.".csv");
        }
        
        $h=["部門","考試名","時(shí)間","時(shí)長(zhǎng)(分鐘)","總分","及格線(百分制)"];
        $this->output($h);
        
        $sql="SELECT COUNT(*) FROM exam e";
        $sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" AND DepartmentId IN ".$deps;
        }
        $total=$this->db->querySingle($sql,[$begin_str,$end_str]);
        $sql="SELECT d.DepartmentName,e.ExamName,e.ExamTime,e.ExamDuration,e.Score,e.PassScore FROM exam e";
        $sql.=" LEFT JOIN department d ON d.ID=e.DepartmentId";
        $sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" AND DepartmentId IN ".$deps;
        }
        $sql.=" LIMIT ?,10";
        for($i=0;$i<$total;$i+=10){
            $arr=$this->db->queryAll($sql,[$begin_str,$end_str,$i]);
            if(!$arr)
                break;
            for($j=0;$joutput($arr[$j]);
            }
        }
    }
    
    function grade($opts){
        if(isset($opts["department"]) && $opts["department"]>0){
            $department=(int)$opts["department"];
            $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
            if(!$name)
                return;
        }else{
            $department=0;
            $name="所有部門";
        }
        $begin=(int)$opts["begin"];
        $end=(int)$opts["end"];
        $begin_str=date("Y-m-d H:i:s",$begin);
        $end_str=date("Y-m-d H:i:s",$end);
        
        $fileName=$name."-考試成績(jī)-".$this->time_range($begin,$end);
        if(isset($opts["download"])){
            //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
            $this->set_filename($fileName.".csv");
        }
        
        $h=["部門","名字","考試名","時(shí)間","總分","分?jǐn)?shù)"];
        $this->output($h);

        $sql="SELECT COUNT(*) FROM grade g";
        $sql.=" LEFT JOIN exam e ON e.ID=g.ExamId";
        $sql.=" LEFT JOIN users u ON u.ID=g.UserId";
        $sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" AND u.DepartmentId IN ".$deps;
        }
        
        $total=$this->db->querySingle($sql,[$begin_str,$end_str]);
        $sql="SELECT d.DepartmentName,u.Name,e.ExamName,e.ExamTime,e.Score,g.Score FROM grade g";
        $sql.=" LEFT JOIN exam e ON e.ID=g.ExamId";
        $sql.=" LEFT JOIN users u ON u.ID=g.UserId";
        $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
        $sql.=" WHERE e.ExamTime BETWEEN ? AND ?";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" AND u.DepartmentId IN ".$deps;
        }
        $sql.=" LIMIT ?,10";
        for($i=0;$i<$total;$i+=10){
            $arr=$this->db->queryAll($sql,[$begin_str,$end_str,$i]);
            if(!$arr)
                break;
            for($j=0;$joutput($arr[$j]);
            }
        }
    }
    
    function exercise($opts){
        if(isset($opts["department"]) && $opts["department"]>0){
            $department=(int)$opts["department"];
            $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
            if(!$name)
                return;
        }else{
            $department=0;
            $name="所有部門";
        }
        $begin=(int)$opts["begin"];
        $end=(int)$opts["end"];
        
        $fileName=$name."-練習(xí)數(shù)據(jù)-".$this->time_range($begin,$end);
        if(isset($opts["download"])){
            //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
            $this->set_filename($fileName.".csv");
        }
        
        if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
            return;
        $sql="SELECT COUNT(*) FROM EX.exercise e";
        $sql.=" LEFT JOIN users u ON u.ID=e.uid";
        $sql.=" WHERE e.time BETWEEN $begin AND $end";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" AND u.DepartmentId IN ".$deps;
        }
        $total=$this->db->querySingle($sql);
        
        $h=["部門","身份","名字","時(shí)間","結(jié)果"];
        $this->output($h);
        
        $sql="SELECT d.DepartmentName,r.name,u.Name,datetime(e.Time,"unixepoch","localtime"),e.result FROM EX.exercise e";
        $sql.=" LEFT JOIN users u ON u.ID=e.uid";
        $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
        $sql.=" LEFT JOIN role r ON r.ID=u.role";
        $sql.=" WHERE e.time BETWEEN $begin AND $end";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" AND u.DepartmentId IN ".$deps;
        }
        $sql.=" ORDER BY e.time ASC LIMIT ?,10";
        for($i=0;$i<$total;$i+=10){
            $arr=$this->db->queryAll($sql,[$i]);
            if(!$arr)
                break;
            for($j=0;$joutput($arr[$j]);
            }
        }
    }
    
    function set_filename($fileName){
        $userAgent=$_SERVER["HTTP_USER_AGENT"];
        if($userAgent && strstr($userAgent,"Firefox")){
            header("Content-Disposition: attachment;filename*=""."utf8""".urlencode($fileName).""");
        }else{
            header("Content-Disposition: attachment;filename="".urlencode($fileName).""");
        }
    }
    
    function study($opts){
        if(isset($opts["department"]) && $opts["department"]>0){
            $department=(int)$opts["department"];
            $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
            if(!$name)
                return;
        }else{
            $department=0;
            $name="所有部門";
        }
        $begin=(int)$opts["begin"];
        $end=(int)$opts["end"];
        
        $fileName=$name."-學(xué)習(xí)時(shí)間-".$this->time_range($begin,$end);
        if(isset($opts["download"]))
            //header("Content-Disposition: attachment;filename="".$fileName.".csv"");
            $this->set_filename($fileName.".csv");
        
        $sql="SELECT COUNT(*) FROM studyRecord s";
        $sql.=" LEFT JOIN users u ON u.ID=s.UserId";
        $sql.=" WHERE s.Begin BETWEEN $begin AND $end";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" AND u.DepartmentId IN ".$deps;
        }
        $total=$this->db->querySingle($sql);
        
        $h=["部門","身份","名字","時(shí)間","時(shí)長(zhǎng)(秒)","類型"];
        $this->output($h);
        
        $sql="SELECT d.DepartmentName,r.name,u.Name,datetime(s.Begin,"unixepoch","localtime"),s.Duration,s.Type FROM studyRecord s";
        $sql.=" LEFT JOIN users u ON u.ID=s.UserId";
        $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
        $sql.=" LEFT JOIN role r ON r.ID=u.role";
        $sql.=" WHERE s.Begin BETWEEN $begin AND $end";
        $sql.=" LIMIT ?,10";
        for($i=0;$i<$total;$i+=10){
            $arr=$this->db->queryAll($sql,[$i]);
            if(!$arr)
                break;
            for($j=0;$joutput($arr[$j]);
            }
        }
    }
    
    // 每個(gè)學(xué)員學(xué)習(xí)時(shí)間要按學(xué)習(xí)類型分組加起來(lái)
    function study2($opts){
        if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
            return;

        if(isset($opts["department"]) && $opts["department"]>0){
            $department=(int)$opts["department"];
            $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
            if(!$name)
                return;
        }else{
            $department=0;
            $name="所有部門";
        }
        $begin=(int)$opts["begin"];
        $end=(int)$opts["end"];
        
        $fileName=$name."-學(xué)習(xí)時(shí)間-".$this->time_range($begin,$end);
        if(isset($opts["download"]))
            $this->set_filename($fileName.".csv");
        
        $total=9999;
        
        $h=["部門","身份","名字","學(xué)習(xí)時(shí)間(秒)","練習(xí)時(shí)長(zhǎng)(秒)","練習(xí)數(shù)量","練習(xí)正確數(shù)量","練習(xí)正確率"];
        $this->output($h);
        
        $sql="SELECT d.DepartmentName,r.name,u.Name,u.ID FROM users u";
        $sql.=" LEFT JOIN department d ON d.ID=u.DepartmentId";
        $sql.=" LEFT JOIN role r ON r.ID=u.role";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" WHERE u.DepartmentId IN ".$deps;
        }
        $sql.=" LIMIT ?,10";

        for($i=0;$i<$total;$i+=10){
            $arr=$this->db->queryAll($sql,[$i]);
            if(!$arr)
                break;
            for($j=0;$jdb->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);
                if($temp){
                    $arr[$j][]=(int)$temp[0];
                    $arr[$j][]=(int)$temp[1];
                }else{
                    $arr[$j][]=0;
                    $arr[$j][]=0;
                }
                $temp=$this->db->query("SELECT COUNT(*),SUM(e.result) FROM EX.exercise e WHERE e.uid=?",$uid);
                if($temp){
                    $arr[$j][]=(int)$temp[0];
                    $arr[$j][]=(int)$temp[1];
                    if($temp[0]>0){
                        $arr[$j][]=sprintf("%.2f",$temp[1]/$temp[0]);
                    }else{
                        $arr[$j][]=0;
                    }
                }
                $this->output($arr[$j]);
            }
        }
    }
    
    
    function get_libnames(){
        $sql="SELECT Name FROM question_lib group by Name";
        return $this->db->querySingleAll($sql);
    }

    function lib_error($opts){
        $begin=(int)$opts["begin"];
        $end=(int)$opts["end"];
        
        if(isset($opts["department"]) && $opts["department"]>0){
            $department=(int)$opts["department"];
            $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
            if(!$name)
                return;
        }else{
            $department=0;
            $name="所有部門";
        }
        
        $fileName=$name."-高頻錯(cuò)題-".$this->time_range($begin,$end);
        if(isset($opts["download"]))
            $this->set_filename($fileName.".csv");
        
        $libs=$this->get_libnames();
        
        if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
            return;
        
        $h=["題庫(kù)","題型","題目","選項(xiàng)","答案","答題數(shù)","答題正確數(shù)量","正確率"];
        $this->output($h);
        
        $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";
        $sql.=" LEFT JOIN users u ON u.ID=uid";
        $sql.=" WHERE time BETWEEN $begin AND $end";
        if($department>0){
            $deps=department_with_child_sql($this->db,$department);
            $sql.=" AND u.DepartmentId IN ".$deps;
        }
        $sql.=" GROUP BY qid";
        $ret=$this->db->exec($sql);
        if(!$ret){
            return;
        }

        $sql="SELECT q.LibName,q.Type,q.Question,q.Option,q.Answer,s.count,s.result,s.accuracy FROM stat s";
        $sql.=" LEFT JOIN EX.question q ON q.ID=s.qid";
        $sql.=" WHERE q.LibName=?";
        $sql.=" AND s.count>=3";
        $sql.=" ORDER BY s.accuracy ASC";
        $sql.=" LIMIT 0,10";

        for($i=0;$idb->queryAll($sql,$libs[$i]);
            if(!$temp)
                continue;
            for($j=0;$joutput($temp[$j]);
            }
        }
    }
    
    //過(guò)關(guān)記錄
    function manmul_lib($opts){
        $begin=(int)$opts["begin"];
        $end=(int)$opts["end"];
        if(isset($opts["department"]) && $opts["department"]>0){
            $department=(int)$opts["department"];
            $name=$this->db->querySingle("SELECT DepartmentName FROM department WHERE ID=?",[$department]);
            if(!$name)
                return;
        }else{
            $department=0;
            $name="所有部門";
        }
        
        $fileName=$name."-過(guò)關(guān)記錄-".$this->time_range($begin,$end);
        if(isset($opts["download"]))
            $this->set_filename($fileName.".csv");
        
        $libs=$this->get_libnames();
        
        if(!$this->db->exec("ATTACH DATABASE "exercise.db.php" AS "EX""))
            return;
        
        $h=["姓名","部門","過(guò)關(guān)記錄","時(shí)間","難度系數(shù)"];
        $this->output($h);
        
    }
   
}


header("Content-Type: text/plain; charset=GB18030");
//header("Content-Type: application/octet-stream");

if(!isset($_GET["op"]))
    return;
$op=$_GET["op"];
if(!isset($_SESSION["AdminId"]))
    return;
$ec=new ExportCSV();
if(!department_can_admin($ec->db,$_GET["department"],$_SESSION["AdminId"]))
    return;

header("Cache-Control: max-age=0");

if($op=="user")
    $ec->user($_GET);
else if($op=="exam")
    $ec->exam($_GET);
else if($op=="grade")
    $ec->grade($_GET);
else if($op=="exercise")
    $ec->exercise($_GET);
else if($op=="study")
    $ec->study2($_GET);
else if($op=="libe")
    $ec->lib_error($_GET);
else if($op=="manmul")
    $ec->manmul_lib($_GET);

  
?>

一個(gè)模塊的代碼。附上結(jié)果圖片

下圖為study方法中導(dǎo)出的練習(xí)數(shù)據(jù)，頭部引入除過(guò)session都是封裝了crud的db方法類，導(dǎo)出主要參考下面兩個(gè)方法。

方法:

function set_filename($fileName){
        $userAgent=$_SERVER["HTTP_USER_AGENT"];
        if($userAgent && strstr($userAgent,"Firefox")){
            header("Content-Disposition: attachment;filename*=""."utf8""".urlencode($fileName).""");
        }else{
            header("Content-Disposition: attachment;filename="".urlencode($fileName).""");
        }
    }

方法：

function output(&$row){
        for($i=0;$iout,$row);
    }

云服務(wù)器 GPU云服務(wù)器 php導(dǎo)出csv文件 php導(dǎo)出csv封裝 php頁(yè)面導(dǎo)出csv js導(dǎo)出數(shù)據(jù)csv

文章版權(quán)歸作者所有，未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為，您可以聯(lián)系管理員刪除。

轉(zhuǎn)載請(qǐng)注明本文地址：http://specialneedsforspecialkids.com/yun/26073.html

發(fā)表評(píng)論

登陸后可評(píng)論

0條評(píng)論

antyiwei

男|高級(jí)講師

我要關(guān)注我要私信

TA的文章

移動(dòng)端 h5開(kāi)發(fā)相關(guān)內(nèi)容總結(jié)(三)

閱讀 1136·2019-08-30 12:44
前端資源收集整理

閱讀 642·2019-08-29 13:03
CSS基礎(chǔ)篇-- position屬性講解

閱讀 2551·2019-08-28 18:15
JS之繼承（ES5 & ES6）

閱讀 2419·2019-08-26 10:41
react開(kāi)發(fā)vscode插件推薦

閱讀 3082·2019-08-26 10:28
每日 30 秒 ? 扼住焦點(diǎn)的喉嚨

閱讀 3029·2019-08-23 16:54
LeetCode18.四數(shù)之和 JavaScript

閱讀 1983·2019-08-23 15:16
js前端對(duì)表單的字段md5加密

閱讀 802·2019-08-23 14:55

国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

上云采購(gòu)季！| 2核2G4M爆款云服務(wù)器低至59元/年，更有多臺(tái)、長(zhǎng)期優(yōu)惠，快來(lái)選購(gòu)！

原生php導(dǎo)出csv文件

相關(guān)文章