CODEWITHSUNDEEP
phphtmllaravelmaatwebsite-excellaravel-excel
Naduni Warnakulasooriya
Naduni Warnakulasooriya

Reputation: 127

How to import specific cell value to model in Laravel excel

I want to import spreadsheet data to sql database, this is my speadsheet, Speadsheet to import

And this is my view and controller,

<?php

namespace App\Http\Controllers;

use App\DailyAttendance;
use Illuminate\Http\Request;
use App\Exports\DailyAttendanceExport;
use App\Imports\DailyAttendanceImport;
use Maatwebsite\Excel\Facades\Excel;


class DailyAttendanceController extends Controller
{
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $dailyAttendance = DailyAttendance::all();

        return view('daily_attendance.index')->with('dailyAttendance', $dailyAttendance);
    }


     /**
     * Import function
     */
    public function import(Request $request)
    {
        if ($request->file('imported_file')) {

            $import = new DailyAttendanceImport();
            $import->onlySheets('Logs');
            Excel::import($import, request()->file('imported_file'));
            return back();
        }
    }
<form id="upload_excel" method="post" enctype="multipart/form-data" action="{{ route('dailyAttendance.import') }}" autocomplete="off">
                                    @csrf                                                       
                                    @method('post')                                                               
                                    <input type="file" name="imported_file" required>         
                            <button style="margin-left: 10px;" class="btn btn-info" type="submit">Upload attendance</button>                        
                            </form>

Also this is my model class ,

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class DailyAttendance extends Model
{
   /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    protected $fillable = [
        'emp_id', 'date','start','end',
    ];
}

and I also created import class ,

<?php

namespace App\Imports;

use App\DailyAttendance;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class DailyAttendanceImport implements ToModel, WithHeadingRow
{
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */
    public function model(array $row)
    {
        //
    }
}


// change this to import data to an array instead of db...........

but according to documentation,laravel excel documentation sheet values can only import as rows, for example

return new DailyAttendance([
            'emp_id' => $row['emp_id'],
            'date' => $row['date'],
            'start' => $row['start'],
            'end' => $row['end'],
        ]);

and because of my excel sheet format I want to capture values by their cell addresses,Can anyone show me how to import cell values to the model , thanks!

Upvotes: 0

Views: 4358

Answers (1)

JR...
JR...

Reputation: 11

Meabe , you need run some macro (vba) to clean sheet logs...

File clean - sample

and import it...

macro code. (test) 

Sub PrepararInfo()
    Dim x As Integer
      ' Establecer numrows = número de filas de datos.
      NumRows = InputBox("Ingrese cantidad", "Numero de Filas")
      ' Seleccionar celda a1.
      Range("A1").Select


      If True Then


        For x = 1 To NumRows

           ' Selecciona la celda 1 fila por debajo de la celda activa.
            ' MsgBox (ActiveCell.Value)
           If x < 6 Then
                If x = 4 Then
                    ActiveCell.Offset(1, 0).Select
                Else
                    ActiveCell.EntireRow.Delete
                End If

           Else
                If ActiveCell.Value = "No :" Or ActiveCell.Value = "1" Then

                   ActiveCell.EntireRow.Delete
                Else
                   ActiveCell.Offset(1, 0).Select
                End If

           End If


        Next

      End If
End Sub

and would you import(file clean) to array ...

$data = Excel::toArray([], $request->file('select_file'));

and apply filters manually.. etc ..etc..

Upvotes: 1

Related Questions