How to Import Data in ThinkPHP 5.1 Using PHPExcel

Data import is a common requirement; this guide shows how to implement it in ThinkPHP 5.1 using the PHPExcel library.

1. Preparation – Ensure Composer is installed. On Linux/Mac run:

<code>curl -sS https://getcomposer.org/installer | php
mv composer.phar /usr/local/bin/composer</code>

On Windows download and run Composer-Setup.exe from the official site. After Composer is ready, install PHPExcel via:

<code>composer require phpoffice/phpexcel</code>

2. Front‑end submission page

HTML form (Layui style) for uploading the Excel file:

<code>&lt;form class="layui-form" enctype="multipart/form-data"&gt;
    &lt;input type="hidden" name="type_id" value="{$type_id}"&gt;
    &lt;div class="layui-form-item" style="margin-left: 42px;"&gt;
        &lt;div class="layui-input-inline" style="width: 122px;"&gt;
            &lt;button type="button" class="layui-btn" name="file" lay-verify="file" id="test3"&gt;&lt;i class="layui-icon"&gt;&lt;/i&gt;上传文件&lt;/button&gt;
        &lt;/div&gt;
    &lt;/div&gt;
    &lt;div class="layui-form-item" style="margin-left: 42px;"&gt;
        &lt;div class="layui-input-inline"&gt;
            &lt;button class="layui-btn" lay-filter="add" lay-submit="add"&gt;导入&lt;/button&gt;
        &lt;/div&gt;
    &lt;/div&gt;
    &lt;div class="layui-form-item"&gt;
        &lt;div class="layui-input-block"&gt;
            &lt;div style="line-height: 35px;"&gt;
                注：
                &lt;p&gt;1. 文件大小：请上传小于10M的文件&lt;/p&gt;
                &lt;p&gt;2. 文件类型：上传时首先 &lt;a href="/import/member.xlsx"&gt;下载导入模板&lt;/a&gt;，填好信息后上传&lt;/p&gt;
            &lt;/div&gt;
        &lt;/div&gt;
    &lt;/div&gt;
&lt;/form&gt;</code>

JavaScript (Layui) to handle file upload and form submission:

<code>&lt;script&gt;
layui.use(['form','layer','upload'], function(){
    $ = layui.jquery;
    var form = layui.form,
        layer = layui.layer,
        upload = layui.upload;

    upload.render({
        elem: '#test3',
        url: '你的上传路径',
        accept: 'file', //普通文件
        exts: 'xls|xlsx',
        size: 10240,
        done: function(res){
            $('#test3').append('&lt;input type="text" name="file" id="file" lay-verify="file" value="'+res.data+'" /&gt;');
        }
    });

    //监听提交
    form.on('submit(add)', function(data){
        console.log(data);
        $.post('{:url('saveImportMember')}', $('form').serialize(), function(data){
            if(data.res == 1){
                layer.msg(data.msg);
                setTimeout(function(){parent.window.location.reload();}, 1000);
            } else if(data.res == 0){
                layer.alert(data.msg,{icon:2});
            } else {
                layer.alert('操作失败',{icon:2});
            }
        });
        return false;
    });
});
&lt;/script&gt;</code>

3. Backend processing – PHP code that receives the uploaded Excel file, reads its contents, validates fields, and inserts or updates member records.

<code>//上传excel文件
$file = Request::param('file');
//获取文件路径
$filePath = Env::get('root_path').'public'.DIRECTORY_SEPARATOR.$file;
if($filePath == ''){
    return ['res'=>0,'msg'=>'你上传的文件为空'];
}
$suffix = $this->DbSy->GetFileExt($file);
if($suffix=="xlsx"){
    $reader = \PHPExcel_IOFactory::createReader('Excel2007');
}else{
    $reader = \PHPExcel_IOFactory::createReader('Excel5');
}
$excel = $reader->load($filePath,$encode='utf-8');
$sheet = $excel->getSheet(0);
$row_num = $sheet->getHighestRow();
$col_num = $sheet->getHighestColumn();
$time = time();
$data = [];
$count = 0;
$total = 0;
$error_count = 0;
for($i = 2; $i <= $row_num; $i++){
    $type_id = Request::param('type_id');
    $data['type_id'] = $type_id;
    $name = $sheet->getCell("A".$i)->getValue();
    $sex = $sheet->getCell("B".$i)->getValue();
    $identity = $sheet->getCell("C".$i)->getValue();
    $telephone = $sheet->getCell("F".$i)->getValue();
    $data['sort'] = $this->DbSy->getSort(5,'sort desc',['type_id'=>$type_id]);
    if(!$identity){
        return ['res'=>0,'msg'=>'身份证号不能为空'];
    }
    $data['identity'] = $identity;
    if(!$name){
        return ['res'=>0,'msg'=>'姓名不能为空'];
    }
    $data['name'] = $name;
    if($sex=='男'){
        $data['sex'] = 1;
    }elseif($sex=='女'){
        $data['sex'] = 2;
    }else{
        $data['sex'] = 3;
    }
    $data['number'] = $this->DbSy->getNumber(5,'sort desc',['type_id'=>$type_id]);
    $data['telephone'] = $telephone ? $telephone : '';
    $data['status'] = 5;
    $member = $this->DbSy->FindWhere(5,['name'=>$name,'identity'=>$identity,'type_id'=>$type_id]);
    if($member){
        $info = $this->DbSy->editContent(5,$data,['id'=>$member['id']]);
        if($info){
            $total++;
        }
    }else{
        $data['addtime'] = $time;
        $data['updatetime'] = $time;
        $info = $this->DbSy->insertGetId(5,$data);
        if($info){
            $count++;
        }else{
            $error_count++;
        }
    }
}
$msg = "成功导入".$count."条数据,重复".$total."条数据,导入失败".$error_count."条数据";
if($count > 0){
    return ['res'=>1,'msg'=>$msg];
}else{
    return ['res'=>0,'msg'=>$msg];
}
</code>

The article concludes with a link to the original WeChat article for further reading.