I am trying to import data into MySQL from an Excel file. My table has 2 foreign keys project_id and site_id when importing I am selecting these 2 fields from dropdowns in my View. Is there a way I can map these 2 fields to my import collection? Mind you, the 2 fields do not exist in the import file (for integrity reasons) but they do exist in the table.
Collection
namespace App\Imports;
use App\Proposal;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class ProposalsImport implements ToModel, WithHeadingRow
{
public function model(array $row)
{
return new Proposal([
'building' => $row['building'],
'floor' => $row['floor'],
'area' => $row['area'],
'room' => $row['room'],
'luminaire' => $row['luminaire'],
'actual_qty' => $row['actual_qty'],
'installed_qty' => $row['installed_qty'],
]);
}
}
Controller
public function import()
{
Excel::import(new ProposalsImport, 'proposals.xlsx');
}
View
@extends('projectmanagement/proposals.base')
@section('action-content')
<!-- Main content -->
<section class="content">
<div class="container">
<div class="box">
<div class="box-header">
</div>
<!-- /.box-header -->
<div class="box-body" data-widget="box-refresh">
@if (session('status'))
<div style="padding-top: 0px;padding-bottom: 0px;"
class="alert alert_cust alert-success alert-dismissable fade in">{{ session('status') }}<a
href="#" class="close" data-dismiss="alert" aria-label="close">×</a></div>
@endif
</div>
<div class="row">
<div class="col-lg-12">
<form class="form-horizontal" role="form" method="POST" enctype="multipart/form-data" action="{{ route('proposals.store') }}">
{{ csrf_field() }}
<div class="row">
<div class="col-md-12">
<div class="form-group{{ $errors->has('project_id') ? ' has-error' : '' }}">
<label for="project_id" class="col-md-4 control-label">Project Name</label>
<div class="col-md-6">
<select id="project_id" class="form-control select2" style="width: 100%;"
name="project_id">
<option value="0" disabled selected>Select Project</option>
@foreach ($projects as $project)
<option value="{{$project->id}}">{{ $project->project_name }}</option>
@endforeach
</select>
@if ($errors->has('project_id'))
<span class="help-block">
<strong>{{ $errors->first('project_id') }}</strong>
</span>
@endif
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
<br/>
<div class="form-group{{ $errors->has('site_id') ? ' has-error' : '' }}">
<label for="site_id" class="col-md-4 control-label">Site Name</label>
<div class="col-md-6">
<select id="site_id" class="form-control select2" style="width: 100%;"
name="site_id">
<option value="0" disabled selected>Select Site</option>
@foreach ($sites as $site)
<option value="{{$site->id}}">{{ $site->site_name }}</option>
@endforeach
</select>
@if ($errors->has('site_id'))
<span class="help-block">
<strong>{{ $errors->first('site_id') }}</strong>
</span>
@endif
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
<br/>
<div class="form-group{{ $errors->has('proposal_file') ? ' has-error' : '' }}">
<label for="proposal_file" class="col-md-4 control-label">Proposal File</label>
<div class="col-md-6">
<input id="proposal_file" type="file" class="form-control" name="proposal_file" required autofocus>
@if ($errors->has('proposal_file'))
<span class="help-block">
<strong>{{ $errors->first('proposal_file') }}</strong>
</span>
@endif
</div>
</div>
</div>
</div>
<div class="row">
<div class="col-md-12">
<br/>
<div class="form-group">
<div class="col-md-4">
</div>
<div class="col-md-6">
<button type="submit" class="btn col-sm-3 col-xs-5 btn-primary">Upload Proposal</button>
</div>
</div>
<br/>
</div>
</div>
</form>
</div>
</div>
</div>
</div>
@push('custom_scripts')
<script>
</script>
@endpush
@endsection
Override import controller's construct method, sending all the parameters you need, like this:
class ProposalsImport implements ToModel, WithHeadingRow
{
protected $project_id;
protected $site_id;
public function __construct($project_id, $site_id)
{
$this->project_id = $project_id;
$this->site_id = $site_id;
}
public function model(array $row)
{
return new Proposal([
'building' => $row['building'],
'project_id' => $this->project_id,
'site_id' => $this->site_id
]);
}
}
And then, call it from your Controller like this:
public function import(Request $request)
{
Excel::import(new ProposalsImport($request->project_id, $request->site_id), 'proposals.xlsx');
}