使用laravel 7中的maatwebsite将Excel/CSV导入到MySQL数据库

Question

导入/上载excel文件时，如果excel文件中已有数据，则在数据库中更新或插入数据。这意味着插入前应该检查数据库。所以，请大家帮忙解决这个问题：

--这是针对客户的导入类：

<?php

namespace App\Imports;

use App\Customer;
use Illuminate\Validation\Rule;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\Importable;

class ImportCustomers implements ToModel, WithHeadingRow, WithValidation
{
    use Importable;
    /**
    * @param array $row
    *
    * @return \Illuminate\Database\Eloquent\Model|null
    */

    public function model(array $row)
    {

        // Check mobile already exists
       /* $count = Customer::where('mobile',$row['mobile'])->count();
       dd($count);
       if($count > 0){
          return null;
       } */
        return new Customer([
            'customer_name' => $row['customer_name'],
            'mobile' => $row['mobile'],
            'email' => $row['email']
        ]);
    }

    

    public function rules(): array
    {
        return [
             '*.customer_name' => 'required',
             '*.mobile' => 'required|unique:customers',
             '*.email' => 'required',

        ];
    }
}

/*我是控制器：*/

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use App\Http\Requests\CustomerImportRequest;
use App\Imports\ImportCustomers;
use App\Exports\ExportCustomers;
use Maatwebsite\Excel\Facades\Excel;
use DB;
use App\Customer;
use Illuminate\Support\Arr;

class ImportExportExcelController extends Controller
{
    protected $customers;

    public function __construct(Customer $customers){
        $this->customers = $customers;
    }

    public function index()
    {
        $customers = $this->customers->orderBy('id', 'desc')->get();
        return view('ImportExportExcel', compact('customers'));
    }

    public function importExcel(CustomerImportRequest $request)
    {
        try {

            if ($request->hasFile('import_file')) 
            {
                $file = $request->file('import_file');
                $columnRead = (new ImportCustomers)->toArray($file);
                
                
                $customerCheck = $this->customers->where('mobile',$columnRead[0][1]["mobile"])->first(); //**here not getting result, rather shows null**
                //dd($customerCheck);
                if($customerCheck)
                {
                    $customers = $customerCheck;
                    /* 
                    **How to update if duplicates are found and display old values updated. How to achieve this?**
                    */

                }else{
                    $customers = new $this->customers;
                    Excel::import(new ImportCustomers, $file);

                    return redirect()->back()->with('success','Data imported successfully.');
                }
                
            }

        } catch (\Maatwebsite\Excel\Validators\ValidationException $e) {
             $failures = $e->failures();
             //dd($failures);
             return redirect()->back()->with('import_errors', $failures);
             
        }
        
        
    }

    public function exportExcel()
    {
        $customers = Customer::select(["customer_name", "mobile", "email"])->get();  
        return Excel::download(new ExportCustomers($customers), 'customers.xlsx');
    }
}

/This是数据库迁移模式:/

public function up()
    {
        Schema::create('customers', function (Blueprint $table) {
            $table->id();
            $table->string('customer_name');
            $table->string('mobile', 13)->unique();
            $table->string('email')->nullable();
            $table->timestamps();
        });
    }

在这里，" mobile“是唯一的，所以如果像customer_name这样的值和电子邮件在excel表中都有修改的值，则使用相同的手机编号。然后，在导入时，应该更新值。excel工作表

Answer 1

我在Laravel 6中使用过maatwebsite。

主计长：

Excel::import(new ImportCustomers(), $file);

然后，您可以在Import类为客户应用您的逻辑：

public function model(array $row)
{
try {
    $mobile =  $row[1]; //  referenced by row 
    $customer_name =  $row[0];
    $email = $row[1];
    $customer = Customer::where('mobile', $mobile)->first();
    //apply your logic
    if (!$customer) { // you may not need if else, if no customer exists then create a new record and assign mobile
        $customer = new Customer();
        $customer->mobile = $mobile;
    }
    $customer->customer_name = $customer_name;
    $customer->email = $email;        
    $customer->save();
    return $customer;
} catch (\Exception $ex) {
    dd($ex);
    return;
}
}

另外，请取消关于移动的规则，我认为这应该有效。

"*.mobile' => 'required'," 

因为你的逻辑处理手机是独一无二的。