Home / Questions / 332152
Accepted
Rafael Monroy Rodriguez
Asked: 2020-02-26 06:40:19 +0800 CST 2020-02-26 06:40:19 +0800 CST

Read an excel file with laravel

  • 772

I need to get the rows/fields from a file that I upload excelto my Laravel project, I made the code to load my database directly with laravel excel 3.1, but what I need is to get the data and then enter it myself through a insertin the part of the values. In addition, excelthere are several data (several rows). So how could that be made insert?

I add the code of my controller (import):

namespace App\Imports;

 use App\tbl_instrumentos;
 use Maatwebsite\Excel\Concerns\ToModel;

class InstrumentosImport implements ToModel
 {

public function model(array $row)
{
    return new tbl_instrumentos([
        'ins_codigo'   => $row[0],
        'ins_observacionInicial'   => $row[1],
        'ins_claseOexactitud'    => $row[2],
    ]);
 }
}

Additionally I add the code of my main controller:

class TestController extends Controller
{

public function importExportView()
{
   $data=DB::table('tbl_instrumentos')->orderBy('ins_id','DESC')
            ->get();
    return view('importar',compact('data'));
}

/**
* @return \Illuminate\Support\Collection
*/
public function exportExcel($type) 
{
    return Excel::download(new InstrumentosExport, 'instrumentos.'.$type);
}

/**
* @return \Illuminate\Support\Collection
*/
public function importExcel(Request $request) 
{
   $this->validate($request, [
  'select_file'  => 'required|mimes:xls,xlsx'
 ]);
    Excel::import(new InstrumentosImport,$request->import_file);

     return back()->with('success', 'Datos Excel Importados Satisfactoriamente.');
}

The import_file that I have in my controller (import) comes from my view through an input which is the following:

    <input type="file" name="import_file" />
laravel

1 Answers

  1. Best Answer

    Reviewing the documentation a bit, you use the import to model, you could do an insert with query builder as now you create a model:

    namespace App\Imports;

 use App\tbl_instrumentos;
 use Maatwebsite\Excel\Concerns\ToModel;
use Illuminate\Support\Facades\DB;

class InstrumentosImport implements ToModel
 {

public function model(array $row)
{
    DB::table('Tu_Tabla')->insert([
        'columna' => 'valor'
        ...    
    ]);
 }
}

    But really seeing the use case you want, you should consider using the import of a collection ( import to collection ).

    So your example would be:

    <?php 
namespace App\Imports;

use App\tbl_instrumentos;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\ToCollection;

class InstrumentosImport implements ToModel {

    public function collection(Collection $rows)
    {
        // Opcion 1, mass assignement con eloquent:
        $preparedCollection = $this->prepareCollection($rows);
        tbl_instrumentos::insert($preparedCollection);

        // Opcion 2, con queryBuilder insertando row by row (tambien puedes hacer mass assignement):
        $preparedCollection->each(static function ($row){
            DB::table('Tu_tabla')->insert($row);
        })

    }

    private function prepareCollection(Collection $collection)
    {
        return $collection->map(static function($row){
            return [
                'columna' => $row['key']
                ...
            ];
        });
    }
}
    • 1