ThinkPhp5.1 + PHPExcel production data import

Data import is needed in many places. How to use ThinkPhp5.1 to make data import? Next, the editor will take you to understand the whole process.

1 Preparation

The editor implements data import through PHPExcel, so before production, you first need to download PHPExcel related components. Currently tp5.1 supports composer installation. The editor installed the PHPExcel component through composer. [Recommendation: thinkphp video tutorial]

Before installation, you first need to make sure that your computer has the composer component. If Composer has not been installed, you can run the following command in Linux and Mac OS X :

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

In Windows, you need to download and run Composer-Setup.exe, download address:

https://www.kancloud.cn/manual/thinkphp5_1/353948

After installing composer, you need to install PHPExcel. Win R, open the run interface, enter cmd, enter the management interface, enter the drive letter where your project is located (here, take the D drive as an example, the project In D:\phpstudy_pro\WWW\myapp.io), then click Enter:

Enter: cd D:\phpstudy_pro\WWW\myapp.io, locate the project Directory

The next step is to install the PHPExcel plug-in. Enter: composer require phpoffice/phpexcel, and click Enter to start installing PHPExcel.

2 Front-end submission page

html

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

js

<script>
    layui.use([&#39;form&#39;, &#39;layer&#39;,&#39;upload&#39;], function () {
        $ = layui.jquery;
        var form = layui.form
            , layer = layui.layer;

        var $ = layui.jquery,
            upload = layui.upload;

        upload.render({
            elem: &#39;#test3&#39;
            ,url: &#39;你的上传路径&#39;
            ,accept: &#39;file&#39; //普通文件
            ,exts: &#39;xls|xlsx&#39;
            ,size:&#39;10240&#39;
            ,done: function(res){
                $(&#39;#test3&#39;).append(&#39;<input type="text" name="file" id="file" lay-verify="file" value="&#39;+res.data +&#39;" />&#39;)
            }
        });
        //监听提交
        form.on(&#39;submit(add)&#39;, function(data){
            console.log(data);
            //发异步，把数据提交给php
            $.post(&#39;{:url(\&#39;saveImportMember\&#39;)}&#39;,$(&#39;form&#39;).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(&#39;操作失败&#39;,{icon: 2});
                }
            })
            return false;
        });
    });
</script>

3 Background processing

Here is an example of uploading a member information table. The field values ​​included are: name, gender, and membership type. (type_id), ID number (identity), membership number (number), contact number (telephone), sort (sort), member status (status).

//上传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文件
$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(&#39;type_id&#39;);
    $data[&#39;type_id&#39;] = $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[&#39;sort&#39;] = $this->DbSy->getSort(5,&#39;sort desc&#39;,array(&#39;type_id&#39;=>$type_id));
    if(!$identity){
        return [&#39;res&#39;=>0,&#39;msg&#39;=>&#39;身份证号不能为空&#39;];
    }
    $data[&#39;identity&#39;] = $identity;
    if(!$name){
        return [&#39;res&#39;=>0,&#39;msg&#39;=>&#39;姓名不能为空&#39;];
    }
    $data[&#39;name&#39;] = $name;
    if($sex==&#39;男&#39;){
        $data[&#39;sex&#39;] = 1;
    }elseif ($sex==&#39;女&#39;){
        $data[&#39;sex&#39;] = 2;
    }else{
        $data[&#39;sex&#39;] = 3;
    }
    $data[&#39;identity&#39;] = $identity;
    $data[&#39;number&#39;] = $this->DbSy->getNumber(5,&#39;sort desc&#39;,array(&#39;type_id&#39;=>$type_id));
   
    if($telephone){
        $data[&#39;telephone&#39;] = $telephone;
    }else{
        $data[&#39;telephone&#39;] = &#39;&#39;;
    }
    $data[&#39;status&#39;] = 5;
    $member = $this->DbSy->FindWhere(5,array(&#39;name&#39;=>$name,&#39;identity&#39;=>$identity,&#39;type_id&#39;=>$type_id));
    if($member){
        /*$data[&#39;updatetime&#39;] = time();*/
        $info = $this->DbSy->editContent(5,$data,array(&#39;id&#39;=>$member[&#39;id&#39;]));
        if($info){
            $total++;
        }
    }else{
        // 读取单元格
        $data[&#39;addtime&#39;] = time();
        $data[&#39;updatetime&#39;] = time();
        $info = $this->DbSy->insertGetId(5,$data);
        if($info){
            $count++;
        }else{
            $error_count++;
        }
    }
}
$msg =  "成功导入".$count."条数据,重复".$total."条数据,导入失败".$error_count."条数据";
if($count > 0){
    return [&#39;res&#39;=>1,&#39;msg&#39;=>$msg];
}else{
    return [&#39;res&#39;=>0,&#39;msg&#39;=>$msg];
}

The above is the editor’s summary of all the processes for importing information using TP5.1 PHPExcel. I hope it will be helpful to everyone.

The above is the detailed content of ThinkPhp5.1 + PHPExcel production data import. For more information, please follow other related articles on the PHP Chinese website!