第一种:
引入PHPExcel文件
控制器文件user.php
<?php
namespace app\avikcm\controller;
use think\Controller;
use app\avikcm\controller\Base;
class User extends Base
{
/**
* 导入数据库
* [lstaa description]
* @return [type] [description]
*/
public function lstaa()
{
return view('listaa');
}
/**
* 数据导入
* @return \think\response\Json
*/
public function daoruAll(){
set_time_limit(8000);
//设置文件上传的最大限制
ini_set('memory_limit','1024M');
//接收前台文件
$ex = $_FILES['excel'];
$ex2 = $ex['name'];
// dump($ex2);die;
if($ex2 !== ''){
//重设置文件名
$filename = time().substr($ex['name'],stripos($ex['name'],'.'));
//$path = './excel/'.$filename;//设置移动路径
$path=APP_PATH. '../public/uploads/'.$filename;
move_uploaded_file($ex['tmp_name'],$path);
//表用函数方法 返回数组
$exfn1 = $this->_readExcel($path);
//去除数组的前三个数组
// $exfn1=array_slice($exfn,2);
for($i=0; $i < count($exfn1); $i++) {
$exfn2[$i]['name'] = $exfn1[$i]['0'];
}
for($i=0;$i<count($exfn2);$i++) {
$data = [
'name' => $exfn2[$i]['name']
];
$rat = db('dirty')->insert($data);
}
if($rat){
echo "<script>alert('添加成功');history.go(-1)</script>";
}else{
echo "<script>alert('添加失败');history.go(-1)</script>";
}
}else{
echo "<script>alert('导入数据不能为空');history.go(-1)</script>";
}
}
//创建一个读取excel数据,可用于入库
public function _readExcel($path)
{
//引用PHPexcel 类
include_once(APP_PATH."../PHPExcel/PHPExcel.php");
include_once(APP_PATH."../PHPExcel/PHPExcel/IOFactory.php");//静态类
$type = 'Excel5';//设置为Excel5代表支持2003或以下版本,Excel2007代表2007版
$xlsReader = \PHPExcel_IOFactory::createReader($type);
$xlsReader->setReadDataOnly(true);
$xlsReader->setLoadSheetsOnly(true);
$Sheets = $xlsReader->load($path);
//开始读取上传到服务器中的Excel文件,返回一个二维数组
$dataArray = $Sheets->getSheet(0)->toArray();
return $dataArray;
}
}
第二种:
借鉴:tp5导入excel到数据库_tp5 导入excel_流情的博客-CSDN博客
html页面
<button type="button" tooltip="关键词Excel导入" type="file" id="test3" class="btn btn-sm btn-yellow" >关键词Excel导入</button>
//引用layUI框架里面的按钮
<link rel="stylesheet" href="/layuiadmin/layui/css/layui.css" media="all">
<link rel="stylesheet" href="/layuiadmin/style/admin.css" media="all">
<script src="/layuiadmin/layui/layui.js"></script>
<script type="text/javascript">
layui.use(['layer', 'table', 'form','laydate','upload'], function () {
var $ = layui.jquery;
var layer = layui.layer;
var form = layui.form;
var table = layui.table;
var upload = layui.upload;
//导入Excel
//指定允许上传的文件类型
upload.render({
elem: '#test3'
,url: "{:url('Cate/ceshi')}" //此处配置你自己的上传接口即可
,multiple: true
,accept: 'file' //普通文件
,data:{"_token":"{{ csrf_token() }}"}
,done: function(res){
layer.msg('导入Excel成功');
console.log(res);
if (res.code == 1) {
layer.msg(res.msg, {icon: 1}, function () {
dataTable.reload({page: {curr: 1}});
})
} else {
layer.msg(res.msg, {icon: 2})
}
}
});
})
</script>
controler控制器
/**
* 数据导入
* @return \think\response\Json
*/
public function getExt1($filename)
{
$arr = explode('.',$filename);
return array_pop($arr);
}
public function save(){
header("Access-Control-Allow-Origin: *");
header("Access-Control-Allow-Methods: POST, GET, OPTIONS, PUT, DELETE");
header("Access-Control-Allow-Headers: Origin, No-Cache, X-Requested-With, If-Modified-Since, Pragma, Last-Modified, Cache-Control, Expires, Content-Type, X-E4M-With");
header("Content-Type: text/html; charset=utf-8");
if(request() -> isPost())
{
vendor("PHPExcel.PHPExcel");
$objPHPExcel =new \PHPExcel();
// var_dump($objPHPExcel);die;
//获取表单上传文件
$file = request()->file('file');
// print_r($file);die;
$info = $file->validate(['ext' => 'xlsx,xls'])->move(ROOT_PATH . 'uploads/file'); //上传验证后缀名,以及上传之后移动的地址
if($info)
{
$exclePath = $info->getSaveName(); //获取文件名
$file_name = ROOT_PATH . 'uploads/file/' . DS . $exclePath;//上传文件的地址
$name = $this->getExt1($file);
if($name =='xlsx' ){
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
}else {
$objReader =\PHPExcel_IOFactory::createReader('Excel5');
}
//$objReader =\PHPExcel_IOFactory::createReader("Excel2007");
$obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8'); //加载文件内容,编码utf-8
$excel_array=$obj_PHPExcel->getSheet(0)->toArray(); //转换为数组格式
// print_r($excel_array);die;
array_shift($excel_array); //删除第一个数组(标题);
$city = [];
$i=0;
foreach($excel_array as $k=>$v) {
//$click_time = strtotime($v[2]);//点击时间转为时间戳
$city[$k]['ceshi1'] = isset($v[0])?$v[0]:"无";
$city[$k]['ceshi2'] = isset($v[1])?$v[1]:"无";
$city[$k]['ceshi3'] = isset($v[2])?$v[2]:"无";
$city[$k]['ceshi4'] = $v[3];
$i++;
}
// print_r($city);die;
$count=db("cate")->insertAll($city);
$array=array('code'=>'1','msg'=>'success','data'=>array('count'=>$count));
return json_encode($array);
}else
{
return $file->getError();
}
}
}
干货满满,加油!