How To Import Export to Excel In Laravel

Question

I found this code on the itsolutionstuff.com website.

I used this...

Route::post('importExcel', 'MaatwebsiteDemoController@importExcel');

And hanged it to use my route...

Route::post('barang', 'BarangController@importExcel')->name('barang');

For the Controller, I picked this code because I needed it...

public function importExcel(Request $request)
{
    $request->validate([
        'import_file' => 'required'
    ]);

    $path = $request->file('import_file')->getRealPath();
    $data = Excel::load($path)->get();

    if ($data->count()) {
        foreach ($data as $key => $value) {
            $arr[] = ['title' => $value->title, 'description' => $value->description];
        }

        if (!empty($arr)) {
            Item::insert($arr);
        }
    }

    return back()->with('success', 'Insert Record successfully.');
}

And then I changed it based on my table design...

public function importExcel(Request $request)
{
    $request->validate([
        'import_file' => 'required'
    ]);

    $path = $request->file('import_file')->getRealPath();
    $data = Excel::load($path)->get();

    if ($data->count()) {
        foreach ($data as $key => $value) {
            $arr[] = [
                'kode_barang' => $value->kode_barang,
                'nama_barang' => $value->nama_barang,
                'kategori_id' => $value->kategori_id,
                'jumlah_barang' => $value->jumlah_barang,
                'harga_satuan' => $value->harga_satuan,
                'tanggal_inputan' => $value->tanggal_inputan,
                'deskripsi' => $value->deskripsi,
                'status' => $value->status,

            ];
        }

        if (!empty($arr)) {
            Item::insert($arr);
        }
    }

    return back()->with('success', 'Insert Record successfully.');
}

I'm also adding this to my view...

<form action="{{ route('barang') }}" class="form-horizontal" method="post" enctype="multipart/form-data">
    @csrf
    @if ($errors->any())
        <div class="alert alert-danger">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a>
            <ul>
                @foreach ($errors->all() as $error)
                    <li>{{ $error }}</li>
                @endforeach
            </ul>
        </div>
    @endif
    @if (Session::has('success'))
        <div class="alert alert-success">
            <a href="#" class="close" data-dismiss="alert" aria-label="close">×</a>
            <p>{{ Session::get('success') }}</p>
        </div>
    @endif
    <input type="file" name="import_file"/>
    <button class="btn btn-primary">Import File</button>
</form>

And Here Is the result :

However, it says Record Successfully Inserted, but data does not insert into my table.

Here's my Excel CSV format...

Why didn't the data insert into the DB? Is there still something wrong/incomplete/misspelled in my code?

I even tried to change Item ...

 if(!empty($arr)){
                Item::insert($arr);
            }
        }

        return back()->with('success', 'Insert Record successfully.');
    }

...to Barang still tells me it's successful, without data being inserted into the DB.

if(!empty($arr)){
                    Barang::insert($arr);
                }
            }

            return back()->with('success', 'Insert Record successfully.');
        }

My apologies if you didn't understand some words I said. Thanks for your attention.

Answer 1

Make sure your csv has a heading.Maatwebsite\Excel\Excel will grabs the first row as a heading and then the rest will be your item.

Your csv got one line

R4B6, MONITOR LENOVO, ELEKTRNIK, 1,-,10/4/2018,-,Aktif` 

and the class thinks its a header and the following object is returned. As you can see item seems to be null.

RowCollection {#722 ▼
  #heading: array:8 [▶]
  #title: "Worksheet"
  #items: []
}

But if added heading to it

heading1,heading2,heading3,heading4,heading5,heading6,heading7,heading8,
R4B6, MONITOR LENOVO, ELEKTRNIK, 1,-,10/4/2018,-,Aktif

The following object is returned

RowCollection {#730 ▼
  #heading: array:8 [▼
  0 => "heading1"
  1 => "heading2"
  2 => "heading3"
  3 => "heading4"
  4 => "heading5"
  5 => "heading6"
  6 => "heading7"
  7 => "heading8"
]
#title: "Worksheet"
#items: array:1 [▼
  0 => CellCollection {#736 ▶}
]}

You can also make your code better by doing so in your controller

$path = $request->file('import_file')->getRealPath();

$rows = collect(Excel::load($path)->get())->map(function ($row) {
    return [
        'kode_barang' => $row->heading1,
        'nama_barang' => $row->heading2,
        'kategori_id' => $row->heading3,
        'jumlah_barang' => $row->heading4,
        'harga_satuan' => $row->heading5,
        'tanggal_inputan' => $row->heading6,
        'deskripsi' => $row->heading7,
        'status' => $row->heading8,
      ];
    });

Item::insert($rows);

return back()->with('success', 'Insert Record successfully.');

Cheers