How to implement excel import and export in thinkPHP5.0? (code example)

The content of this article is to introduce how thinkPHP5.0 implements excel import and export? (code example). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

excel import and export

Introduce the third-party phpexcel class library and place it under ThinkPHP\Library\Vendor\demo, a folder you created yourself demo

Put Excel.class under ThinkPHP\Library\Org\class and create the folder you created class

Controller:

<?php
namespace Admin\Controller;
use Think\Controller;
class ExcelController extends Controller {
    public function excelList(){
        $this->display();
    }
//    导入
    public function import(){
        if(!empty($_FILES[&#39;file_stu&#39;][&#39;name&#39;])){
            $tmp_file = $_FILES[&#39;file_stu&#39;][&#39;tmp_name&#39;];    //临时文件名
            $file_types = explode(&#39;.&#39;,$_FILES[&#39;file_stu&#39;][&#39;name&#39;]); //  拆分文件名
            $file_type = $file_types [count ( $file_types ) - 1];   //  文件类型
            /*判断是否为excel文件*/
            if($file_type == &#39;xls&#39; || $file_type == &#39;xlsx&#39;|| $file_type == &#39;csv&#39;){    //  符合类型
                /*上传业务*/
                $upload = new \Think\Upload();
                $upload->maxSize   =     3145728 ;
                $upload->exts      =     array(&#39;xls&#39;, &#39;csv&#39;, &#39;xlsx&#39;);
                $upload->rootPath  =      &#39;./Public&#39;;
                $upload->savePath  =      &#39;/Excel/&#39;;
                $upload->saveName  =      date(&#39;YmdHis&#39;);
                $info   =   $upload->upload();
                if(!$info) {    // 上传错误提示错误信息
                    $this->error($upload->getError());
                }else{  // 上传成功

                    //  读取文件
                    $filename=&#39;./Public&#39;.$info[&#39;file_stu&#39;][&#39;savepath&#39;].$info[&#39;file_stu&#39;][&#39;savename&#39;];
                    import("Org.Yufan.ExcelReader");
                    vendor(&#39;PHPExcel.PHPExcel&#39;);
                    $reader = \PHPExcel_IOFactory::createReader(&#39;Excel2007&#39;); //设置以Excel5格式(Excel97-2003工作簿)
                    $PHPExcel = $reader->load($filename); // 载入excel文件
                    $sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
                    $highestRow = $sheet->getHighestRow(); // 取得总行数
                    var_dump($highestRow);
                    $highestColumm = $sheet->getHighestColumn(); // 取得总列数

                    /** 循环读取每个单元格的数据 */
                    $data = array();
                    for ($row = 2; $row <= $highestRow; $row++){//行数是以第1行开始

                        if($column = &#39;A&#39;){
                            $data[&#39;name&#39;] = $sheet->getCell($column.$row)->getValue();
                        }
                        if($column = &#39;B&#39;){
                            $data[&#39;account&#39;] = $sheet->getCell($column.$row)->getValue();
                        }
                        if($column = &#39;C&#39;){
                            $data[&#39;password&#39;] = $sheet->getCell($column.$row)->getValue();
                        }
                        M(&#39;data&#39;)->add($data);
                    }
                        $this->success(&#39;导入数据库成功&#39;,U(&#39;Excel/show&#39;));
                }
            } else{ //  不符合类型业务
                $this->error(&#39;不是excel文件，请重新上传...&#39;);
            }
        }else{
            $this->error(&#39;(⊙o⊙)~没传数据就导入&#39;);
        }
    }
    //导出
    public function export(){
        import("ORG.Yufan.Excel");
        $list = M(&#39;data&#39;)->select();
        if($list == null){
            $this->error(&#39;数据库信息为空...&#39;,__APP__.&#39;/Admin/Excel/show&#39;);
        }else{
            $row=array();
            $row[0]=array(&#39;平台名称&#39;,&#39;帐号&#39;,&#39;密码&#39;);
            $i=1;
            foreach($list as $v){
                $row[$i][&#39;name&#39;] = $v[&#39;name&#39;];
                $row[$i][&#39;account&#39;] = $v[&#39;account&#39;];
                $row[$i][&#39;password&#39;] = $v[&#39;password&#39;];
                $i++;
            }
            $xls = new \Excel_XML(&#39;UTF-8&#39;, false, &#39;datalist&#39;);
            $xls->addArray($row);
            $xls->generateXML(date(&#39;YmdHis&#39;));
        }
    }
    public function show(){
        $m = M(&#39;data&#39;);
        $data = $m->select();
        $this->assign(&#39;data&#39;,$data);
        $this->display();
    }
}

View: (excelList. html)

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Excel导入导出</title>
</head>
<body>

<p class="page-header">
    <h1>TP DEMO
        <small>Excel导入导出练习</small>
    </h1>
</p>

<form method="post" action="{:U(&#39;Excel/import&#39;)}" class="form-signin" enctype="multipart/form-data" >
    <input name="file_stu" type="file" class="form-control">
    <button class="btn btn-lg btn-primary btn-block">导入</button>
</form>
</body>
</html>

View: (show.html)

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>表格展示</title>
</head>
<body>
<p class="page-header">
    <h1>TP DEMO
        <small>Excel导入导出练习</small>
    </h1>
</p>

<table class="table">
    <tr>
        <td><h4><b>平台名称</b></h4></td>
        <td><h4><b>帐号</b></h4></td>
        <td><h4><b>密码</b></h4></td>
    </tr>
    <foreach name="data" item="vo">
        <tr>
            <td>{$vo.name}</td>
            <td>{$vo.account}</td>
            <td>{$vo.password}</td>
        </tr>
    </foreach>
</table>



<form action="{:U(&#39;Excel/export&#39;)}" class="form-signin">
    <button class="btn btn-lg btn-primary btn-block">导出数据库数据</button>
</form>

</body>
</html>

Thank you everyone for browsing here~~~~

The above is the detailed content of How to implement excel import and export in thinkPHP5.0? (code example). For more information, please follow other related articles on the PHP Chinese website!