Laravel excel export multiple table

800 votes

1 answers

I have two tables

1. Accessory Request
2. Accessory Details table

One accessory request can have multiple entries in accessory details table. I am trying to export the data but the data i am getting is from just accessory request table.

This is my controller code --

 public function export() 
    {
        return Excel::download(new AccessoryRequestExport, 'accessory.xlsx');
    }

and this is my AccessoryRequestExport Code --

<?php

namespace App\Exports;

use App\AccessoryRequest;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromArray;

class AccessoryRequestExport implements FromCollection, WithHeadings
{
   public function collection()
   {
       return AccessoryRequest::with('details')->get();
   }
   public function headings() : array
   {
       //Put Here Header Name That you want in your excel sheet 
       return [
           'id',
           'user_id',
           'store_id',
           'request_date',
           'status',
           'created_at',
           'updated_at', 
           'accessory_request_id',
           'vendor_id',
           'barcode',
           'description',
           'qty',
           'status'
       ]; 
   } 
}

I have listed the relation as "details" which is defined in my AccessoryRequest Model as written below.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class AccessoryRequest extends Model
{
   protected $fillable = ['user_id', 'store_id', 'request_date', 'status'];

   public function user() {
       return $this->belongsTo('App\User', 'user_id');
   }

   public function store() {
       return $this->belongsTo('App\Store', 'store_id');
   }

   public function details() {
       return $this->hasMany('App\AccessoryRequestDetail');
   }
   public function vendor() {
       return $this->belongsTo('App\AccessoryVendor', 'vendor_id');
   }

}

Please help me with this---

1. Accessory detail columns entries should be fetched while exporting the data
2. There is column name as user_id in my accessory_request table, right now user_id is being fetched while exporting, if i want to display user_name which is there in user table, How can i do it?

2021-12-17

204

votes

Answer

Solution:

I don't now this Excel library, but maybe you need to use 'details.' syntax in headings for get values from relations. I mean 'details.vendor_id', 'details.barcode' and like that. For user you need to useAccessoryRequest::with('details', 'user')->get() and then, if 'details.' syntax works, you can use 'user.user_name' heading.

Undefined answered

2021-12-17

Link to answer

People are also looking for solutions of the problem: invalid argument supplied for foreach() laravel
Source

Share

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Similar questions

Find the answer in similar questions on our website.

For web applications, importing Excel sheet data into our database and exporting data from the database into an Excel sheet, is an important feature. Because of this feature, we can easily perform batch import/export data by the web application. It is difficult in other Frameworks but easiest in laravel 8. It gives Maatwebsite/excel package to easily import/export data. In this article, we learn about, how data is exported and imported.

Features:

• Effectively send out assortments to Excel.
• Send out questions with programmed piecing for better execution.
• Line sends out for better execution.
• Effectively send out Blade perspectives to Excel.
• Effectively import to accumulations.
• Peruse the Excel record in pieces.
• Handle the import embeds in clumps.

Steps to laravel Import Export excel data: We want to follow a few steps to import and export data easily. There are 9 easy steps to follow.

1. Install new laravel project 
2. Configure Database details and model
3. Install maatwebsite/excel package
4. Create Routes 
5. Create import class for import data 
6. Create an export class for export data 
7. Create controller 
8. Create blade / view files
9. Run laravel project 

1. Install new laravel project: In step first, create a new laravel project named laravel-excel. Use the below command to create a new laravel project.

composer create-project laravel/laravel excel

2. Configure Database details and make a model: In the second step, set up database configuration in the .env file in laravel 8.

PHP

DB_CONNECTION=mysql

DB_HOST=127.0.0.1 

DB_PORT=8258

DB_DATABASE=laravel

DB_USERNAME=localhost 

DB_PASSWORD=

Make model: Then make a new model named User model to connect with the user table in the database. In user, the table contains various fields like ID, name, phone, and email.

3. Install maatwebsite/excel Package: Now, using composer we install package name maatwebsite/excel to export and import data by using composer

composer require maatwebsite/excel 

After installing the package we want to register the plugin’s service in providers and alliases.

We want to write the following code into the config/app.php file:

PHP

<?php

    'providers' => [ 

      Maatwebsite\Excel\ExcelServiceProvider::class, ], 

    'aliases' => [ 

      'Excel' => 

      Maatwebsite\Excel\Facades\Excel::class, ],

?>

To publish the above configuration execute the vendor:publish command:

php artisan vendor:publish –provider=”Maatwebsite\Excel\ExcelServiceProvider” –tag=config

This command is to create a new config file config/excel.php 

4. Create Routes: Then we create routes to handle requests from import and export files.

PHP

<?php

    use App\Http\Controllers\UserController; 

    Route::get('/file-import',[UserController::class,

            'importView'])->name('import-view'); 

    Route::post('/import',[UserController::class,

            'import'])->name('import'); 

    Route::get('/export-users',[UserController::class,

            'exportUsers'])->name('export-users');

?>

5. Create Import class import for import data: The packag maatwebsite/excel provides you to build an import class file. Using the following command, we create an import class file.

php artisan make:import ImportUser --model=User

The file will create at the app/Imports/ImportUser.php path:

PHP

<?php

namespace App\Imports;

use App\Models\User;

use Maatwebsite\Excel\Concerns\ToModel;

class ImportUser implements ToModel

{

   public function model(array $row)

   {

       return new User([

           'name' => $row[0],

           'email' => $row[1],

           'password' => bcrypt($row[2]),

       ]);

   }

}

6. Create an Export class for export data: The package maatwebsite/excel provides you to build an export class file. Using the following command, we create an export class file.

php artisan make:export ExportUser --model=User

The file will create at the app/Exports/ExportUser.php path:

PHP

<?php

    namespace App\Exports; 

    use App\Models\User;

    use Maatwebsite\Excel\Concerns\FromCollection; 

    class ExportUser implements FromCollection { 

        public function collection() 

        { 

            return User::select('name','email')->get(); 

        }

    }

?>

7. Create Controller: To display data from the database, we need to create one controller. Using the below command we create a controller named UserController.

php artisan makes: controller UserController

The above command will create a controller file UserController.php at the path app/Http/controllers directory.

Write the below code into the UserController.php file:

PHP

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use Maatwebsite\Excel\Facades\Excel;

use App\Imports\ImportUser;

use App\Exports\ExportUser;

use App\Models\User;

class UserController extends Controller

{

    public function importView(Request $request){

        return view('importFile');

    }

    public function import(Request $request){

        Excel::import(new ImportUser, 

                      $request->file('file')->store('files'));

        return redirect()->back();

    }

    public function exportUsers(Request $request){

        return Excel::download(new ExportUser, 'users.xlsx');

    }

 }

?>

8. Create Blade/View file: To handle export and import at frontend of a web application, we need to create a view or blade file. Create a blade file at resources/views/importFile.blade.php:

HTML

<!DOCTYPE html>

<html>

<head>

    <title> Import and Export Excel data to database Using Laravel 5.8 </title>

    <link rel="stylesheet"

        href=

</head>

<body>

    <h6> Import and Export Excel data to 

           database Using Laravel 5.8

    </h6>

    <div class="container">

        <div class="card bg-light mt-3">

            <div class="card-header">

                Import and Export Excel data 

                  to database Using Laravel 5.8

            </div>

            <div class="card-body">

                <form action="{{ route('import') }}" 

                      method="POST" 

                      enctype="multipart/form-data">

                    @csrf

                    <input type="file" name="file" 

                           class="form-control">

                    <br>

                    <button class="btn btn-success">

                          Import User Data

                       </button>

                    <a class="btn btn-warning" 

                       href="{{ route('export') }}">

                              Export User Data

                      </a>

                </form>

            </div>

        </div>

    </div>

</body>

</html>

9. Run laravel project: Run the below command on command prompt and check http://localhost:8000/file-import in the browser:

php artisan serve

Output:

Laravel Import Export 

How do I export multiple sheets in Excel laravel?

How do I export to Excel from laravel?

How do I save an Excel file in laravel 7?