Laravel Excel import using Maatwebsite Excel package with additional columns from View

0 votes

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">&times;</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

Mar 30, 2022 in Database by Edureka
• 13,690 points
12,445 views

1 answer to this question.

0 votes

How to fix: I know you're fed up with this glitch, therefore we're here to help! Take a deep breath and examine the problem's explanation. We have numerous answers to this problem, but we recommend that you utilise the first way because it is tried and true and will work for you 100 percent of the time.

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');
}

 

answered Apr 1, 2022 by gaurav
• 23,260 points

Related Questions In Database

0 votes
1 answer
0 votes
0 answers

Laravel - Datatables export excel from filtered data

Hello i would like to export data ...READ MORE

Mar 28, 2022 in Database by Edureka
• 13,690 points
4,705 views
0 votes
0 answers
0 votes
1 answer

How will I get all the rows from my table where name starts with 's' and ending with 'I'?

Hi Santanu, I understand your problem, You can try ...READ MORE

answered May 28, 2019 in Database by sampriti
• 1,120 points
2,027 views
0 votes
1 answer

How to drop all tables from a database with one SQL query?

Use the INFORMATION_SCHEMA.TABLES view to get the ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
8,867 views
0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

You can use the PIVOT function to ...READ MORE

answered Feb 8, 2022 in Database by Vaani
• 7,070 points
1,551 views
0 votes
1 answer
0 votes
1 answer

How to sort dates from Oldest to Newest in Excel?

Drag down the column to select the ...READ MORE

answered Feb 23, 2022 in Database by gaurav
• 23,260 points
5,136 views
0 votes
1 answer

Importing images along with other fields (username,subjectname) from excel in laravel

$objphpexcel = PHPExcel_IOFactory::load("MyExcelFile.xls"); foreach ($objphpexcel ->getSheetByName("My Sheet1")->getDrawingCollection() as ...READ MORE

answered Apr 1, 2022 in Database by gaurav
• 23,260 points
2,853 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP