phpexcellaravelcyberduck

Get specific cell values of Excel file in Laravel/Cyber-duck excel


Currently, I can get the data coming from excel file with this format.

enter image description here

This is my working code.

        $validator = Validator::make(
        [
            'file'      => $request->file,
            'extension' => strtolower($request->file->getClientOriginalExtension()),
        ],
        [
            'file'          => 'required|max:5000',
            'extension'      => 'required|in:,csv,xlsx,xls',
        ]
    );

    $modal = "active";
    if($validator->fails()){
        return redirect()
        ->back()
        ->with(['errors'=>$validator->errors()->all()])
        ->with('modal',$modal);
    }

    $dateTime = date('Ymd_His');
    $file = $request->file('file');
    $fileName = $dateTime . '-' . $file->getClientOriginalName();
    $savePath = public_path('/upload/projectlist/');
    $file->move($savePath, $fileName);

    $excel = Importer::make('Excel');
    $excel->hasHeader(true);
    $excel->load($savePath.$fileName);
    $collection = $excel->getCollection();

    if(sizeof($collection[1]) == 5)
    {
       $arr = json_decode($collection,true);
            foreach ($arr as $row) {
                $insert_data[] = array(
                    'first_name'  => $row['first_name'],
                    'last_name'  => $row['last_name'],
                    'email'  => $row['email'],
                    'birthdate'  => $row['birthdate']['date'],
                    'created_at'  => now(),
                    'updated_at'  => now(),
                );

            }

        DB::table('tbl_sampleimport')->insert($insert_data);
    }
    else
    {
        return redirect()
        ->back()
        ->with(['errors'=> [0=> 'Please provide date in file according to your format.']])
        ->with('modal',$modal);
    }


    return redirect()->back()
    ->with(['success'=>'File uploaded successfully!'])
    ->with('modal',$modal);

Now that the format was changed. Added some fields with single value. Like Project Name and Project Date I'm trying to get its value. Just want to get the value but not necessary to save it too to the database.

enter image description here

How can I get the value of Project Name and Project Date to declare as a variable. and still be able to save the data of j.doe17 and j.doe18 ?

I'm using cyber-duck as my laravel/excel for import and so on.


Solution

  • Solution suggested by @Dhaval Purohit

    create sheets and define setSheet($sheetNumber).

    enter image description here

    So I tried to create new sheet in the same excel file with this format only

    enter image description here

    and this will give me like this

    [{
          "project_name":"Creator Doe",
          "project_date":"7/9/2019"
    }]