How to get excel to array in maatwebsite

Question

I am trying to convert an Excel file to an array using the latest version of Laravel-Excel (3.1.9)

The code below will download the file:

return Excel::download(new SalesOrderExport('columns'),'test.xlsx')

But I need to convert it to get an array only. I don't want to store this Excel data in a database at this point.

I tried with the code below but it did not work as the load method is not available in version 3.

Excel::load($request->file('sampledata'), function ($reader) {
    return response()->json($reader);
});

Please share your thoughts on how to get an array from Excel.

Hebron

Answer 1

I and @narayan tried hard to make requested excel file into array. Now I am able to get array properly with below code

$rows = Excel::toArray(new SalesOrderImport, $request->file('sampledata')); 

In my SalesOrderExport class I have default function only, which is required as abstract method.

namespace App\Exports;

use App\SalesOrder;
use Maatwebsite\Excel\Concerns\FromCollection;

class SalesOrderExport implements FromCollection
{
    public function collection()
    {   
        return SalesOrder::all();
    }
}

My Controller code

public function importTest(Request $request)
{
    $rows = Excel::toArray(new SalesOrderImport, $request->file('sampledata'));
    return response()->json(["rows"=>$rows]);
}

And in HTML

<input class="" type="file" name="sampledata" id="sampledata">

I already created this export by

php artisan make:import SalesOrder

Attaching related images

Hebron

Maybe you can help me. Look at this : #56522535 – Xenos

Answer 2

I'm adding it on hope that someone could get another types of solution for this. Version - "maatwebsite/excel": "^3.1"

In Controller

$data = Excel::toArray(new UsersImport(), $request->file);

In UsersImport

<?php
    namespace App\Imports;
    use Illuminate\Support\Collection;
    use Maatwebsite\Excel\Concerns\ToCollection;

    class UsersImport implements ToCollection
    {
       public function collection(Collection $rows)
       {
       }
    }

Json Array Result

array:2 [▼
  0 => array:2 [▼
           0 => "+126785XXXXX"
           1 => "Jhon"
       ]
  1 => array:2 [▼
           0 => "+126784XXXXX"
           1 => "Doe"
       ]
  ]

And One Bonus, Now suppose you just need the phone numbers, then make a custom array from that in controller-

$data = Excel::toArray(new UsersImport(), $request->file);
$phoneNumbersData= [];
foreach ($data[0] as $key => $value) {
  $phoneNumbersData[] = $value[0];
}
return $phoneNumbersData;

Hope, this will help someone.

Heldentenor

Answer 3

Here are some working examples based on importing a csv file using the usual file input element. In my case, once uploaded, the file is accessible through the request object as 'csv_file'.

public function importCSV(Request $request)
{
    // Get the csv rows as an array
    $theArray = Excel::toArray(new stdClass(), $request->file('csv_file'));

    // Get the csv rows as a collection
    $theCollection = Excel::toCollection(collect([]), $request->file('csv_file'));

    //etc
}

Veratrine

Answer 4

I tried something like this and it worked perfectly; in your import file do this

use Illuminate\Support\Collection;
use Maatwebsite\Excel\Concerns\ToCollection;

public function collection(Collection $row)
{
    return $row;
}

Then in the controller, do this;

$row = Excel::toCollection(new CourseImport, request()->file('course'));

Titustityus

since you mentioned array, please kindly use toArray() instead of toCollection() like this; $row = Excel::toArray(new CourseImport, request()->file('course')); – Titustityus

Is there any way to optimize the toArray(). In case we have say 20k records in our request? – Hypognathous

Answer 5

Here is how I achieved this:

In my UsersImport class: namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;

class UsersImport implements ToModel
{
    use Importable;

    public function model(array $row)
    {
        return new User([
           'id' => $row[0]
        ]);
    }
}

And in controller:

$imported_users = (new UsersImport)->toArray(request()->file('excel_file'))[0];

I hope it helps you.

Bergwall

Answer 6

1. Create Import File using below command.

   php artisan make:import TestImport

2. Inside TestImport make changes like this:

   namespace App\Imports;

   use App\User; use Illuminate\Support\Collection; use Maatwebsite\Excel\Concerns\ToCollection;

   class TestImport implements ToCollection { public function collection(Collection $rows) { return $rows; } }

3. In Controller make changes like this:

   // original code: $rows = Excel::import(new TestImport, 'test.xlsx');

   $rows = Excel::toCollection(new TestImport, 'test.xlsx');

Canaan

ok so this toArray function came from this docs I seen this but not understood. let me check you answer. – Hebron

I think load method is no longer in version 3 I am getting error as Call to undefined method Maatwebsite\Excel\Excel::load() – Hebron

check above again. – Canaan

my problem is get array in controller not in TestImport – Hebron

The answer had changed again it might fix your problem. – Canaan

Thanks for your answer, I really appreciate your dedication, but unfortunately your answer not worked for me, I am sharing my answer which worked for me. – Hebron

This code doesn't work as expecter; if I try to access $row[0] I get this error: Cannot use object of type Maatwebsite\Excel\Excel as array – Flavoring

The code above returns a Laravel collection if you call $rows = Excel::toCollection(new TestImport, 'test.xlsx'); in the controller – Domenicadomenico

Answer 7

You should try this:

$data = Excel::load($request->file('sampledata'), function ($reader) use($table) {
        $data = $reader->toArray();
        //here data has all excel data in array.
});

Odont

I am getting this error "message": "Call to undefined method Maatwebsite\\Excel\\Excel::load()", – Hebron

@NikleshRaut: Please see this link – Odont

@NikleshRaut: Yes something like that – Odont

unfortunately I knew about this thats why I asked here for version 3 – Hebron

@NikleshRaut: Okay – Odont

Thanks for your answer, I am sharing my answer which worked for me. – Hebron

This is for version 2, Laravel Excel Version 3 is completely different from previous version. – Heldentenor