tp5之 Excel文件导入数据库

最新推荐文章于 2024-05-24 18:23:07 发布

灼灼桃花夭

最新推荐文章于 2024-05-24 18:23:07 发布

阅读量457

点赞数

分类专栏： tp5

本文链接：https://blog.csdn.net/qq_39835505/article/details/100100887

版权

tp5 专栏收录该内容

39 篇文章 0 订阅

订阅专栏

参考地址，参考下面，引入phpexcel 包

https://blog.csdn.net/weixin_42122979/article/details/80804460

html 页面

<div class="wrapper wrapper-content animated fadeInRight">
    
    <div class="row">     
        <div class="col-sm-6">
            <div class="ibox float-e-margins">
                <div class="ibox-title">
                    <h5>Excel导入数据库</h5>
                </div>
              
                <div class="ibox-content">
                    <form class="form-horizontal m-t" id="signupForm" action="{:url('admin/excel/addexcel')}" method="post" enctype="multipart/form-data">
                        <div class="form-group">
                            <label class="col-sm-3 control-label-left">数据表名称：</label>
                            <div class="col-sm-8">
                                <input id="table" name="table"  placeholder="以前缀开头" class="form-control" type="text">
                                <!-- <span class="help-block m-b-none"><i class="fa fa-info-circle"></i> 这里写点提示的内容</span> -->
                            </div>
                        </div>
                        <div class="form-group">
                            <label class="col-sm-3 control-label-left">选择Excel文件：</label>
                            <div class="col-sm-8">
                                <input type="file" name="file">
                            </div>
                        </div>
                       
                        <div class="form-group">
                            <div class="col-sm-8 col-sm-offset-3">
                                <button class="btn btn-primary" id="edit" type="submit">提交</button>
                            </div>
                        </div>
                    </form>
                </div>
            </div>
        </div>
    </div>
</div>

php代码

public function addexcel()
{
	vendor("PHPExcel.PHPExcel");   //获取PHPExcel类 
    $excel = new \PHPExcel();  

    $file = request()->file('file');  

    // var_dump($file);
    // die;

    // $info = $file->validate(['size'=>15678,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');

    $info = $file->validate(['ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');

    //  ["pathName":"SplFileInfo":private]=>
		  // string(105) "E:\phpstudy2018\PHPTutorial\WWW\gaoxie\h_layui\public\excel\20190827\cf76bbbc649be41f65c2bc9406dc4c8f.xls"
		  // ["fileName":"SplFileInfo":private]=>
		  // string(36) "cf76bbbc649be41f65c2bc9406dc4c8f.xls"

    if($info){
    	$exclePath = $info->getSaveName();  //获取文件名  
        $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath;   //上传文件的地址  

        $na = explode('.',$exclePath);
      
        if($na['1'] =='xlsx' ){
		    $objReader = \PHPExcel_IOFactory::createReader('Excel2007'); 
		}else{
		    $objReader = \PHPExcel_IOFactory::createReader('Excel5'); 
		}


        $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8  
        
        $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式  
        // array_shift($excel_array);  //删除第一个数组(标题);  
        $arr  = reset($excel_array);
        unset($excel_array[0]);
        $data = [];  
        $i=0;  
        // 下面有几个字段名就得有几个语句
        foreach($excel_array as $k=>$v) {  
            // $data[$k][$arr[0]] = $v[0];  
            // $data[$k][$arr[1]] = $v[1];  
            // $data[$k][$arr[2]] = $v[2];  
        	$count = count($v);

        	for($n = 0;$n < $count; $n++){

            	$data[$k][$arr[$n]] = $v[$n]; 
            }
            $i++;  
        }

        $table = $_POST['table'];
        
        // $res = Db::table('sp_excel')->insertAll($data);
        $res = Db::table($table)->insertAll($data);

        echo "<pre>"; 
        var_dump($res);
        echo '导入数据库成功';
        // var_dump($data);
        die;

    }
}