Reading excel file and uploading to database Larav

问题:

I have this project where I should be able to upload an excel file and read the contents then upload the information to the database. So I decided to use a library to help me out which turns to be Maatwebsite/Laravel-Excel

But I tried reading the documentation http://www.maatwebsite.nl/laravel-excel/docs/import but I can't seem to find the one that I need.

For example in my excel file in the first row John, Kennedy, Male which in my database corrensponds First Name, Last Name, Gender. How can I read it and upload? Can someone help me?

Thanks!

My code as of now

public function postUploadCsv()
{
    $rules = array(
        'file' => 'required',
        'num_records' => 'required',
    );

    $validator = Validator::make(Input::all(), $rules);
    // process the form
    if ($validator->fails()) 
    {
        return Redirect::to('customer-upload')->withErrors($validator);
    }
    else 
    {
        $file = Input::file('file');
        dd($file);
        exit();
    } 
}

回答1:

given your excel sheet column names are exactly as database column names following is suffice,

add following above controller class,

use Maatwebsite\Excel\Facades\Excel;

and function code,

public function postUploadCsv()
{
    $rules = array(
        'file' => 'required',
        'num_records' => 'required',
    );

    $validator = Validator::make(Input::all(), $rules);
    // process the form
    if ($validator->fails()) 
    {
        return Redirect::to('customer-upload')->withErrors($validator);
    }
    else 
    {
        try {
            Excel::load(Input::file('file'), function ($reader) {

                foreach ($reader->toArray() as $row) {
                    User::firstOrCreate($row);
                }
            });
            \Session::flash('success', 'Users uploaded successfully.');
            return redirect(route('users.index'));
        } catch (\Exception $e) {
            \Session::flash('error', $e->getMessage());
            return redirect(route('users.index'));
        }
    } 
} 

UPDATE

Suppose you have more than one sheet in a workbook, you will have additional foreach to iterate over sheets as below,

Excel::load(Input::file('file'), function ($reader) {

     $reader->each(function($sheet) {    
         foreach ($sheet->toArray() as $row) {
            User::firstOrCreate($row);
         }
     });
});

Read More

In case you are using Laravel 5.3 and given that your excel sheet columns are not exact

Make use of the following code to suite your needs

/**
 * Import file into database Code
 *
 * @var array
 */
public function importExcel(Request $request)
{
    if($request->hasFile('import_file')){

        $path = $request->file('import_file')->getRealPath();
        $data = Excel::load($path, function($reader) {})->get();

        if(!empty($data) && $data->count()){

            foreach ($data->toArray() as $key => $value) {

                if(!empty($value)){

                    foreach ($value as $v) {        

                        $insert[] = ['title' => $v['title'], 'description' => $v['description']];

                    }
                }
            }

            if(!empty($insert)){
                Item::insert($insert);
                return back()->with('success','Insert Record successfully.');
            }
        }
    }

    return back()->with('error','Please Check your file, Something is wrong there.');

}

Check out the full tutorial here

Note that by default - Once your data is extacted from you excel sheet, all the column names are converted to lower case, and all spaces between the names are replaced with underscore.