Home > Backend Development > PHP Tutorial > php怎么导出excel

php怎么导出excel

PHPz
Release: 2020-06-05 10:27:43
Original
2956 people have browsed it

php怎么导出excel

php怎么导出excel?

如何自定义的导出,如何定义自己想要的模板呢?

1.首先下载phpExcel 文件

2.解压文件到php的vender的文件夹下面

236d158e776c259d3867aec17d5de0c.png

3.进行编写导出的代码

  在cntroller中新建立一个文件(防止与其他的逻辑代码混合)

public function clubpeople(){<br>//可以进行自定义传值,比如一个id,去向数据库找那个取出值
    if(isset($_GET[&#39;club_id&#39;])){
        $clubid=$_GET[&#39;club_id&#39;];
    }
    //获取到get参数
            //1.从数据库中导出需要进行要导出的数据
            $list =Db::query(&#39;SELECT stu_list.stu_name,stu_list.stu_department,stu_list.stu_phone,stu_list.stu_email,stu_list.stu_profess
            ,club_branch.* FROM club_branch INNER JOIN stu_list ON club_branch.stu_number = stu_list.stu_number
             where club_branch.club_id=:acc order by club_branch.club_branch_name &#39;,[&#39;acc&#39;=>$clubid]);
              
    //重要补助
        //2.加载PHPExcle类库
        vendor(&#39;PHPExcel.PHPExcel&#39;);
        //3.实例化PHPExcel类
        $objPHPExcel = new \PHPExcel();
        //4.激活当前的sheet表
        $objPHPExcel->setActiveSheetIndex(0);
        //5.设置表格头(即excel表格的第一行)
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue(&#39;A1&#39;, &#39;部门&#39;)
                ->setCellValue(&#39;B1&#39;, &#39;职位&#39;)
                ->setCellValue(&#39;C1&#39;, &#39;学号&#39;)
                ->setCellValue(&#39;D1&#39;, &#39;学生姓名&#39;)
                ->setCellValue(&#39;E1&#39;, &#39;联系方式&#39;)
                ->setCellValue(&#39;F1&#39;, &#39;邮箱&#39;)
                ->setCellValue(&#39;G1&#39;, &#39;院系&#39;)
                ->setCellValue(&#39;H1&#39;, &#39;专业&#39;);
        //设置A列水平居中
        $objPHPExcel->setActiveSheetIndex(0)->getStyle(&#39;A1&#39;)->getAlignment()
                    ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        //设置单元格宽度
        //6.循环刚取出来的数组,将数据逐一添加到excel表格。
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;B&#39;)->setWidth(10);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;C&#39;)->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;E&#39;)->setWidth(20);
        $objPHPExcel->setActiveSheetIndex(0)->getColumnDimension(&#39;F&#39;)->setWidth(30);
          
        for($i=0;$i<count($list);$i++){
            $objPHPExcel->getActiveSheet()->setCellValue(&#39;A&#39;.($i+2),$list[$i][&#39;club_branch_name&#39;]);//ID
            $objPHPExcel->getActiveSheet()->setCellValue(&#39;B&#39;.($i+2),$list[$i][&#39;club_position&#39;]);//标签码
            $objPHPExcel->getActiveSheet()->setCellValue(&#39;C&#39;.($i+2),$list[$i][&#39;stu_number&#39;]);//防伪码
            $objPHPExcel->getActiveSheet()->setCellValue(&#39;D&#39;.($i+2),$list[$i][&#39;stu_name&#39;]);//ID
            $objPHPExcel->getActiveSheet()->setCellValue(&#39;E&#39;.($i+2),$list[$i][&#39;stu_phone&#39;]);//标签码
            $objPHPExcel->getActiveSheet()->setCellValue(&#39;F&#39;.($i+2),$list[$i][&#39;stu_email&#39;]);//ID
            $objPHPExcel->getActiveSheet()->setCellValue(&#39;G&#39;.($i+2),$list[$i][&#39;stu_department&#39;]);//标签码
            $objPHPExcel->getActiveSheet()->setCellValue(&#39;H&#39;.($i+2),$list[$i][&#39;stu_profess&#39;]);//防伪码
        }
        //7.设置保存的Excel表格名称
        $filename = $club_name.&#39;人员名单&#39;.date(&#39;ymd&#39;,time()).&#39;.xls&#39;;
        //8.设置当前激活的sheet表格名称;
        $objPHPExcel->getActiveSheet()->setTitle(&#39;人员名单&#39;);
        //9.设置浏览器窗口下载表格
        header("Content-Type: application/force-download"); 
        header("Content-Type: application/octet-stream"); 
        header("Content-Type: application/download"); 
        header(&#39;Content-Disposition:inline;filename="&#39;.$filename.&#39;"&#39;); 
        //生成excel文件
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, &#39;Excel5&#39;);
        //下载文件在浏览器窗口
        $objWriter->save(&#39;php://output&#39;);
        exit;
}
Copy after login

  这样一个下载的表格就完成了,

2:如何进行解析表格存放在数据库中呢

public function getExcel(){ <br>//首先要判断当前是否与文件传过来,并判断文件传输是否正确
   if(isset($_FILES["file"]) && ($_FILES["file"]["error"] == 0)){       
          $file = $_FILES[&#39;file&#39;];
          $path = $file[&#39;tmp_name&#39;];
            //读取excel
             $arr = $this->excel($path, 0);
             $result =$arr;//用来记录当前操作的错误之类的
             //当解析完数据结构之后,就可以进行实地的插入数据库操作了,此处省略掉这个部分~
   
       $number = mt_rand(999, 9999);//随机数返回前端,取名字
       session(&#39;file&#39;.$number ,$arg);  //保存当前的一个报告结果数组,可以对报告进行下载
       $resultr[&#39;status&#39;]=1;
       $resultr[&#39;msg&#39;]= $number;
       return json($resultr);
       
   }else{
       $resultarr[&#39;status&#39;]=0;
       $resultarr[&#39;msg&#39;]=&#39;文件上传发生错误,,稍后再试&#39;;
       return json($resultarr);
   }
    
   }
Copy after login
public function excel($filePath=&#39;&#39;, $sheet=0){
                  
                  import("Org.Util.PHPExcel");
                  import("Org.Util.PHPExcel.Reader.Excel5");
                 import("Org.Util.PHPExcel.Reader.Excel2007");
          
                 if(empty($filePath) or !file_exists($filePath)){die(&#39;file not exists&#39;);}
                 $PHPReader = new \PHPExcel_Reader_Excel2007();        //建立reader对象
                 if(!$PHPReader->canRead($filePath)){
                     $PHPReader = new \PHPExcel_Reader_Excel5();
                    if(!$PHPReader->canRead($filePath)){
                          echo &#39;no Excel&#39;;
                        return ;
                      }
                 }
                 $PHPExcel = $PHPReader->load($filePath);        //建立excel对象
                 $currentSheet = $PHPExcel->getSheet($sheet);        //**读取excel文件中的指定工作表*/
                $allColumn = $currentSheet->getHighestColumn();        //**取得最大的列号*/
                $allRow = $currentSheet->getHighestRow();        //**取得一共有多少行*/
                 $data = array();
                 for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){        //循环读取每个单元格的内容。注意行从1开始,列从A开始
                     for($colIndex=&#39;A&#39;;$colIndex<=$allColumn;$colIndex++){
                         $addr = $colIndex.$rowIndex;
                         $cell = $currentSheet->getCell($addr)->getValue();
                         if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
                             $cell = $cell->__toString();
                         }
                         $data[$rowIndex][$colIndex] = $cell;
                     }
                 }
                 return $data;
             }
Copy after login

            

omg~~三段代码完成的功能分别是导出excel,还有就是上传excel,然后可以进行解析。

更多相关知识,请访问PHP中文网

Related labels:
php
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template