php artisan make:import GoodsImport --model=Models/Prod/Good
<?php
/*namespace App\Imports;
use App\Models\Prod\Good;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToModel;
class GoodsImport implements ToModel
{
public function model(array $row)
{
return new Good([
'cas' => $row[0],
'name_cn' => $row[1],
'status' => 2,
]);
}
}*/
namespace App\Imports;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\SkipsErrors;
use Maatwebsite\Excel\Concerns\SkipsFailures;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
class GoodsImport implements WithMultipleSheets
{
use Importable, SkipsErrors,SkipsFailures;
protected $user_id;
protected $user_name;
protected $company_id;
protected $company_name;
public function __construct($user_id,$user_name,$company_id,$company_name)
{
$this->user_id = $user_id;
$this->user_name = $user_name;
$this->company_id = $company_id;
$this->company_name = $company_name;
}
public function sheets(): array
{
//这里需要注意的是键，这个键可以是sheet表的名称，比如 'sheet1'=> new FirstSheetImport()
return [
0 => new FirstSheetImport($this->user_id,$this->user_name,$this->company_id,$this->company_name),
// 1 => new SecondSheetImport(),
];
}
}
<?php
/**
* Created by PhpStorm.
* User: Lenovo
* Date: 23/12/2019
* Time: 下午2:34
*/
namespace App\Imports;
use App\Models\Prod\Good;
use App\Models\Prod\Prod;
use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;
class FirstSheetImport implements ToCollection
{
protected $cas;
protected $user_name;
protected $company_id;
protected $company_name;
public function __construct($user_id,$user_name,$company_id,$company_name)
{
$this->user_id = $user_id;
$this->user_name = $user_name;
$this->company_id = $company_id;
$this->company_name = $company_name;
}
public function collection(Collection $rows)
{
$flagCount = array(
'fail'=>0,
'success'=>0,
'check'=>0
);
foreach ($rows as $row)
{
//判断商品库Goods表是否已经添加过了该商品
$goodRes = Good::where('cas','=', $row[0])->where('user_id', '=', $this->user_id)->first();
if($goodRes){
$flagCount['fail']++;
continue;
}
$res = Prod::where(['cas'=> $row[0]])->first();
//不存在
if(!$res){
$prod_id = 0;
$status = 2;
$flagCount['check']++;
}else{
$prod_id = $res->poid;
//是危险化学品待补充资料
if($res->is_danger == 1){
$status = 3;
$flagCount['supplement']++;
}else{
$status = 1;
$flagCount['success']++;
}
}
Good::create([
'cas' => $row[0],
'prod_id' =>$prod_id,
'name_cn' => $row[1],
/* 'user_id' => auth('member')->user()->id,
'user_name' => auth('member')->user()->name,
'company_id'=> auth('member')->user()->company_id,
'company_name'=> auth('member')->user()->company_name,*/
'user_id' => $this->user_id,
'user_name' => $this->user_name,
'company_id'=> $this->company_id,
'company_name'=> $this->company_name,
'status' => $status,
]);
}
return $flagCount;
}
}
@extends('admin.base')
@section('content')
<form class="layui-form" id="batch_form" action="{{route("admin.goods.batch")}}" method="post">
@csrf
<div class="layui-card">
<div class="layui-card-header">
<h2>客户信息</h2>
</div>
<div class="layui-card-body">
<div class="layui-row" style="padding:10px 10px;">
<div class="layui-col-xs4" style=" margin-right: 20px;">
<select lay-filter="supplier" name="company_id" id="company_id" lay-search>
<option value="">请选择供应商</option>
@foreach($supplier as $k=>$v)
<option value="{{$v->id}}">{{$v->name_cn ? $v->name_cn : $v->name_en}}</option>
@endforeach
</select>
</div>
<div class="layui-col-xs4">
<select lay-filter="account" id="member_id" name="member_id" lay-verify="" lay-search>
<option value="">请选择供应商关联账户</option>
@foreach($member as $k=>$v)
<option value="{{$v->id}}">{{$v->name}}--{{$v->email}}--{{$v->phone}}</option>
@endforeach
</select>
</div>
</div>
</div>
<div class="layui-card-header">
<h2>商品信息</h2>
</div>
<div class="layui-card-body">
<div class="layui-row" style="padding:10px 10px;">
<div class="layui-form-item">
<div class="layui-col-xs3"></div>
</div>
<div class="layui-form-item">
<p>EXCEL请输入产品的CAS号和英文产品名称,<a href="/static/new/excel/批量新增商品.xlsx" class="red" style="font-size: 16px;margin-left: 20px;">示例下载</a></p>
<div>
<input type="file" name="file" id="batch_goods">
</div>
</div>
<div class="layui-form-item">
<div class="layui-input-block">
<a href="javascript:void(0);" class="layui-btn layui-btn-normal" id="publish_product_btn">立即新增</a>
<a href="{{route('admin.goods.index')}}" class="layui-btn">返 回</a>
</div>
</div>
</div>
</div>
</div>
</form>
@endsection
@section('script')
<script>
layui.use(['layer','table','form'],function () {
var $ = layui.jquery;
var layer = layui.layer;
var form = layui.form;
//选择供应商
form.on('select(supplier)', function(data){
var company_id = data.value;
//查询供应商下账户信息
if(!company_id){
layer.alert('请选择正确的供应商', function(index){
layer.close(index);
$("#company_id").focus();
});
return;
}
$.ajax({
url: '{{route("admin.members")}}',
data:{ "_token": "{{ csrf_token() }}", "company_id":company_id},
type: 'post',
cache: false,
dataType: 'json',
success: function (data) {
var options = '<option value="">请选择供应商关联账户</option>';
$(data.data).each(function (i,n) {
options +='<option value="'+n.id+'">'+n.name+'--'+n.email+'--'+n.phone+'</option>';
});
$("#member_id").empty();
$("#member_id").append(options);
form.render('select');
},
error: function (data) {
layer.confirm(data.msg, function(index){
layer.close(index);
});
}
});
});
});
$("#publish_product_btn").click(function () {
var company_id = $.trim($("#company_id").val());
var member_id = $.trim($("#member_id").val());
//查询供应商下账户信息
if(!company_id){
layer.msg("请选择正确的供应商", {icon: 5});
$("#company_id").focus();
return;
}
if(!member_id){
layer.msg("请选择供应商账户", {icon: 5});
$("#member_id").focus();
return;
}
var file = $("#batch_goods").val();
var exec = (/[.]/.exec(file)) ? /[^.]+$/.exec(file.toLowerCase()) : '';
if(!file){
layer.msg("请选择上传EXCEL文件", {icon: 5});
return;
}
if (exec != "xlsx" && exec != "xls") {
layer.msg("文件格式不对，请上传Excel文件!", {icon: 5});
return;
}
var formData = new FormData($("#batch_form")[0]);;
$.ajax({
url: $("#batch_form").attr('action'),
type: 'post',
cache: false,
dataType: 'json',
data: formData,
contentType: false,
processData: false,
success: function (data) {
layer.msg(data.msg, {icon:1});
},
error: function (data) {
layer.msg(data.msg, {icon:2});
}
});
});
</script>
@endsection
<?php
namespace App\Http\Controllers\Admin;
use App\Imports\GoodsImport;
use App\Library\Util;
use App\Library\Y;
use App\Models\Company\Company;
use App\Models\Member\Member;
use App\Models\Prod\Good;
use App\Models\Prod\Prod;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Maatwebsite\Excel\Facades\Excel;
public function batch(Request $request){
$member_id = $request->get('member_id');
if($member_id == 0){
return Y::error('发布失败，请先选择客户信息！', array('code'=>"NO USER"));
}
if(!$request->hasFile('file')){
return Y::error('上传文件为空！');
}
//获取供应商账户信息
$member = Member::where('id','=',$member_id)->first();
if(!$member) return Y::error('选择的账户不存在！');
$member_name = $member->name;
$company_id = $member->company_id;
$company_name = $member->company_name;
$file = $request->file('file');
//原文件名
//$originalName = $file->getClientOriginalName();
//后缀
$fileExtension = $file->getClientOriginalExtension();
if(! in_array($fileExtension, ['xls', 'xlsx'])) {
return false;
}
//临时绝对路径
$filePath = $file->getRealPath();
$filename = date('Ymdhis').mt_rand(10,99).'.'.$fileExtension;
Storage::disk('public')->put($filename, file_get_contents($filePath));
$filePath = storage_path("app/public/".$filename);
//$filename = "2019-12-23-02-04-31.xls";
//$filePath = storage_path("app/public/".$filename);
Excel::import(new GoodsImport($member_id,$member_name,$company_id,$company_name), $filePath);
Storage::disk('public')->delete($filename);
return Y::success('上传成功！',["code"=>"SUCCESS"]);
}
参考文档：https://docs.laravel-excel.com/3.1/imports/