thinkPHP excel 导出

拼搏现实的明天。 2022-06-05 08:11 128阅读 0赞

1.  下载PHPExcel，放到指定路径下；
2.  查询数据库数据，导出；

/*全部导出数据方法*/
        public function allExp() { 
            $bgdate = htmlspecialchars($_POST['bgdate']);
            $enddate = htmlspecialchars($_POST['enddate']);
            $map = 'create_time > '.strtotime($bgdate).' and create_time <'.(strtotime($enddate) + 86400);
    
            //链接所导出的数据表
            $xlsModel = D('Bi');
            //“bilibili,mobile,create_time”为所查询的字段，“”查询条件
            $mobile_list  = $xlsModel->field('mobile,create_time')->where($map)->select();
    
            $count=1;//导出Excel序号排列
    
            $data = array();
            //循环查询后的数据，进行每一列
            foreach ($mobile_list as $k=>$mobile_info){
                $data[$k][ID] = $count++;//序号列
                $data[$k][mobile] = $mobile_info['mobile'];//名称列
                $data[$k][create_time] = date("Y-m-d H:i:s",$mobile_info['create_time']);//地址列
            }
    
            //每列表的名称
            foreach ($data as $field=>$v){
                if($field == 'ID'){
                    $headArr[]='序号';
                }
    
                if($field == 'mobile'){
                    $headArr[]='电话';
                }
    
                if($field == 'create_time'){
                    $headArr[]='创建时间';
                }
            }
    
            $filename="bi活动电话信息数据表";//所导出的保存文件名称
            $sss=$this->getExcel($filename,$headArr,$data);//调用导出引用方法
    
    
        }
    
        /*导出引用方法*/
        public  function getExcel($fileName,$headArr,$data) { 
            //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
            //import("Org.Util.PHPExcel");
            //import("Org.Util.PHPExcel.Writer.Excel5");
            //import("Org.Util.PHPExcel.IOFactory.php");
            include_once BASE_PATH . '/ThinkPHP/Extend/Vendor/PHPExcel/PHPExcel.php';
            include_once BASE_PATH . '/ThinkPHP/Extend/Vendor/PHPExcel/PHPExcel/Writer/Excel5';
            include_once BASE_PATH . '/ThinkPHP/Extend/Vendor/PHPExcel/PHPExcel/IOFactory.php';
    
            $date = date("Y_m_d",time());
            $fileName .= "_{$date}.xls";
    
            //创建PHPExcel对象，注意，不能少了\
            $objPHPExcel = new \PHPExcel();
            $objProps = $objPHPExcel->getProperties();
    
            //设置表头
            $key = ord("A");
            //print_r($headArr);exit;
            foreach($headArr as $v){
                $colum = chr($key);
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', $v);
                $key += 1;
            }
    
            $column = 2;
            $objActSheet = $objPHPExcel->getActiveSheet();
    
            //print_r($data);exit;
            foreach($data as $key => $rows){ //行写入
                $span = ord("A");
                foreach($rows as $keyName=>$value){
       // 列写入
                    $j = chr($span);
                    $objActSheet->setCellValue($j.$column, $value);
                    $span++;
                }
                $column++;
            }
    
            $fileName = iconv("utf-8", "gb2312", $fileName);
    
            //重命名表
            //$objPHPExcel->getActiveSheet()->setTitle('test');
            //设置活动单指数到第一个表,所以Excel打开这是第一个表
            $objPHPExcel->setActiveSheetIndex(0);
            ob_end_clean();//清除缓冲区,避免乱码
            header('Content-Type: application/vnd.ms-excel');
            header("Content-Disposition: attachment;filename=\"$fileName\"");
            header('Cache-Control: max-age=0');
    
            $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
            $objWriter->save('php://output'); //文件通过浏览器下载
            exit;
    
        }