欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

laravel 5 read excel 读 excel 文件 自己亲自做的 有大用 有大大用 有大大大用

 

下面是自己亲自做的 有大用 有大大用


先保存一下上传的excel文件
// 获取文件相关信息
$originalName = $file->getClientOriginalName(); // 文件原名
$ext = $file->getClientOriginalExtension();     // 扩展名
$realPath = $file->getRealPath();   //临时文件的绝对路径
$type = $file->getClientMimeType();     // image/jpeg
$filename  = Carbon::now()->format('Y-m-d-H-i-s').'-'.substr(sha1(mt_rand()), 0, 10).'.'.$ext;
$pathname = date('Y-m').'/'.$filename;
// 使用我们新建的uploads本地存储空间(目录)
$bool = Storage::put($pathname, file_get_contents($realPath));
if($bool) 说明保存成功 执行下面的代码




Excel::load('storage/app/'.$pathname, function($reader) {
    $reader->noHeading(); //假如不设置这一行 即有头部的话,见下面的★★★★★处
    $reader->each(function($sheet) {

        $sheet_array = $sheet->toArray();
        $row_is_array = false;
        foreach ($sheet_array as $key=>$row) {
            if(is_array($row)){ //多个sheet
                $row_is_array = true;
                if(!empty($row[0]) && !empty($row[1]) && !empty($row[2])){
                    $this->save_excel_to_tuiguang_effect($row);
                }
            }
        }
        if(!$row_is_array){ //单个 sheet

            $this->save_excel_to_tuiguang_effect($sheet_array);
        }
    });
});


// save_excel_to_tuiguang_effect 里面进行相关的保存操作 



★★★★★
$reader->noHeading(); //假如不设置这一行 即有头部的话,在config/excel.php 文件中 找到
'heading' => 'slugged',改成 'heading' => 'true',
因为 这就相当于把 每一行的数据组成的数组的键就是数字(为true时),
而不是第一行的文字 (为slugged时) (第一行英文倒也罢了,就是中文的话作为键的话,这里程序就会取不到数据了,不知道原因)






private function save_excel_to_tuiguang_effect($row) { 
    $tuiguang_effect = new TuiguangEffect();  
    $tuiguang_effect->tuiguang_effect_keyword = $row[0]; 
    $tuiguang_effect->tuiguang_effect_url = $row[1]; 
    $domain = getDomainByUrl($row[1]); 
    if(strexists($domain,'baidu.com'))
   { 
        $tuiguang_effect->tuiguang_effect_engine = '/assets/admin/images/tuiguangeffects/timg.jpg'; 
   }
   else if(strexists($domain,'360.com')){ 
        $tuiguang_effect->tuiguang_effect_engine = '/assets/admin/images/tuiguangeffects/timg_360.jpg';  
   }  else if(strexists($domain,'sougou.com')){ 
        $tuiguang_effect->tuiguang_effect_engine = '/assets/admin/images/tuiguangeffects/timg_sougou.jpg'; 
    }  else {  
         $tuiguang_effect->tuiguang_effect_engine = '/assets/admin/images/tuiguangeffects/timg.jpg'; 
    } 
    $tuiguang_effect->tuiguang_effect_sort = $row[2];  
    $tuiguang_effect->save(); 
}


Reading excel file and uploading to database Laravel 5

 

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 JohnKennedyMale which in my database corrensponds First NameLast NameGender. 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();
    } 
}


正确答案

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.

shareimprove this answer
 
   
Hi. Thanks for the help, I've already fixed my problem before I saw your answer but your User::firstOrCreate($row); helped me to have a cleaner code. than to assign each value like $customer->title = $value['title']; Thanks! I'll accept this :) – jackhammer013 Jul 13 '15 at 9:06
   
Glad I could help. – pinkal vansia Jul 13 '15 at 9:36
   
@pinkalvansia what if there are 2 or more sheets ? Because, according to your code, foreach will take only first sheet, Right ? – Saiyan Prince Jul 16 '15 at 10:05
   
@JoeneFloresca I have updated my answer. – pinkal vansia Jul 16 '15 at 10:52
   
@pinkalvansia Thank you so much. But I only have 1 sheet so it's ok, but thanks anyway, I could reference to this in the future. – jackhammer013 Jul 17 '15 at 13:16

来自 https://github.com/Maatwebsite/Laravel-Excel


普通分类: