PHP Excel文件导入数据到数据库
阅读原文时间:2023年07月11日阅读:4

1.php部分(本例thinkphp5.1):

下载PHPExcel了扩展http://phpexcel.codeplex.com/

<?php
namespace app\admin\controller;

// 引入phpExcel
use Env;
use vendor\PHPExcel\PHPExcel;

class Upload
{
//上传Excel 导入数据
public function import(){
include_once(Env::get('root_path').'vendor/PHPExcel/PHPExcel.php');
// $objPHPExcel = new \PHPExcel();
$file = request()->file('file');
$info = $file->validate(['size'=>52428800])->move( '../public/uploads');
if($info){
$ext = $info->getExtension(); //获取文件后缀名
if(!in_array($ext,['xlsx','xls'])){
$msg=['code'=>-1,'msg'=>"文件格式有误"];
}else{
$path = $_SERVER['DOCUMENT_ROOT'].'/uploads/'.str_replace('\\', '/', $info->getSaveName()); //上传文件路径
if (!file_exists($path)) {
$msg['res'] = -1;
$msg['msg'] = "上传文件丢失!" . $file->getError();
}

            if ($ext == 'xlsx') {  
                $objReader = \\PHPExcel\_IOFactory::createReader('Excel2007');  
                $objPHPExcel = $objReader->load($path, 'utf-8');  
            } elseif ($ext == 'xls') {  
                $objReader = \\PHPExcel\_IOFactory::createReader('Excel5');  
                $objPHPExcel = $objReader->load($path, 'utf-8');  
            }  

            $sheet = $objPHPExcel->getSheet(0);  
            $highestRow = $sheet->getHighestRow(); // 取得总行数  

// $highestColumn = $sheet->getHighestColumn(); // 取得总列数
$i = 0;
$importRows = 0;
$addData = [];
for ($j = 2; $j <= $highestRow; $j++) { $importRows++; $realName = (string)$objPHPExcel->getActiveSheet()->getCell("A$j")->getValue();//需要导入的realName
$phone = (string)$objPHPExcel->getActiveSheet()->getCell("B$j")->getValue(); //需要导入的phone
$company = (string)$objPHPExcel->getActiveSheet()->getCell("C$j")->getValue(); //需要导入的company
$job = (string)$objPHPExcel->getActiveSheet()->getCell("D$j")->getValue(); //需要导入的job
$email = (string)$objPHPExcel->getActiveSheet()->getCell("E$j")->getValue(); //需要导入的email

                $addData\[\] = \['name'=>$realName,'tel'=>$phone,'company'=>$company,'job'=>$job,'email'=>$email\];  
                $i++;  
            }  
            halt($addData);  //打印数据  
            //数据写入数据库  
            $result = model('')->insertAll($addData);  
            if($result){  
                $msg = \['code'=>0,'msg'=>'导入数据成功!'\];  
            }else{  
                $msg = \['code'=>-1,'msg'=>'导入失败,请重试!'\];  
            }  
        }  
    }  
    return $msg;  
}  

}

配置路由:

'import'=>'admin/Upload/import',

2.前端部分:layui传文件

导入数据 //点击开始上传文件操作

layui.use(['upload'],function(){
  var upload = layui.upload;
  upload.render({
    elem:'.import_btn',
    url:"{:url('admin/import')}",
    accept:'file', //普通文件
    exts:'xls', //允许的文件后缀
    done:function(res){
      //执行导入接口后操作
    }
  });
})

参考链接:https://www.cnblogs.com/52lnamp/p/9237704.html,https://blog.csdn.net/xgs736214763/article/details/78904780(感谢分享)